%{7J9W QtnL7z
Q+E0 理解索引的内部结构对于整体的理解索引是至关重要的,只有理解了索引的内部结构以及SQLServer是如何维护索引的,你才能理解数据插入,删除,更新,索引的创建、修改、删除所带来的成本。
S5QzgC]0;]@(IGYi e0i0 叶子层级和非叶子层级
~2U5}`h
L051Testing软件测试网a!cbyP{:J"Q&j|!h 所有的索引都是由叶子层级和非叶子层级组成的。前面的文章主要关注了索引的叶子层级。对于聚集索引来说,叶子节点就是索引本身,每一个叶子节点所包含的条目其实就是表中的行。对于非聚集索引来说,叶子节点每一个叶子包含的一行就是一个条目。每一个条目由索引键列,可选的包含列以及书签构成,而书签又由聚集索引键或RID构成。
^4@vc4zsc0
k'x*`z-z0l
i_0 无论索引中条目是来自表行(聚集索引叶子节点),指向表行(非聚集索引叶子节点)或是指向低层级的节点(非叶子节点),索引条目都可以被称为索引行。
U4b&K0G:L8X0&]7rO p
kt6sih0 非叶子节点是叶子节点层级的上层,SQL Server使用非叶子节点来:51Testing软件测试网W3N9P5Wvs
zq K3S*c&M%N3a
y0 ● 使得索引按照索引键聚集有序51Testing软件测试网 p(gfaS
o;j moEyP
51Testing软件测试网krmgf]
^}C ● 根据索引键快速找到叶子节点
U^7N"pu3L051Testing软件测试网Q#mer]n` F
在本系列第一篇文章中,我们使用电话本的类比来解释为什么索引能够带来性能的提升。用户知道电话本是按照姓氏进行排序的,因此如果需要找”Meyer,
Helen”根据首字母M知道这个人大概在电话本的中间位置,用户直接翻开大约一半电话本开始查找。但对于SQL
Server来说,可并不知道什么是按字母表排序,也不知道哪一些页是所谓的中间页,除非把索引中的所有页扫描一遍。为了不用扫描所有的页来找到所需条
目,SQL Server为在叶子节点之上增加了额外的页。51Testing软件测试网&J#XcP[4Px
H3HZg7J*@A4o0 非叶子层级51Testing软件测试网I3b@'vkF!M
|U%`u(wL2uj.em0 这些额外的页也就是所谓的非叶子节点,或被称为索引的节点层级,是建立在叶子层级之上的层级。非叶子层级的作用是使得SQL Server对于特定的索引进行查找时,不仅有了统一的入口页,并且不再需要扫描所有的页。51Testing软件测试网x;bv.G,wh$D
monD,x*l;bO]0 在索引中的所有页,无论哪个层级,都包含索引条目。正如文章中不断重复说的,对于聚集索引来说,叶子节点的条目包含实际的行,所以如果一个表中包含了10亿行,那么叶子节点包含了10亿个条目。
'~
XQ"Zb8vH051Testing软件测试网i)c[3{,YsjE+~
在叶子节点之上的层级,也就是非叶子节点的最底层,非叶子节点的最底层每一个索引条目都指向叶子节点。如果说表中每一页能容纳100个条目,那么刚才的
十亿行需要1000000000/100=1000万个页,与之对应的是,那么最底层的非叶子节点就包含了1000万的条目,也就是分布在1000万
/100=10万个页中。(译者注:原作者这里没说全,通常来说非叶子节点只包含索引键,因此每个条目的大小会远远小于叶子节点的条目大小,因此每页可以
容纳更多的行,所以这里非叶子节点应该远远小于10W个页,后面的段落我们先不管这个,还是按照10W个页算)。
!k\e/?hR051Testing软件测试网n8HpC
z 再上一层的非叶子节点包含了指向这10万个页的条目,也就是10万个条目,这10W个索引条目分布在10万/1000=1000页中。根据这个规律,我们知道再上一层包含10个页,直至最上层的节点只有一个页了。
!`b,{4MA+BXH"Wr051Testing软件测试网t-f+Yw?cD 索引中最上层的节点被称为根页。剩下的除了根页和叶子之外的层级就是所谓的中间层级。层级的编号是从叶子节点以0开始向上增长的,因此中间层级是以1开始的。
+}$Sb0pRf5U"|051Testing软件测试网Hez.K6I.h~ 非叶子节点仅仅包含索引键,对于拥有包含列的索引来说,包含列仅仅存在于叶子节点。
'Uza4wlC.TmS051Testing软件测试网'bm Y?1K&P7e[%}7q5_R 索引中的页,除了根页之外,都含有两个额外的指针,分别指向按照索引顺序当前页之前和之后的页。这种双向链表结构使得SQL Server在索引扫描的时候更加有效。51Testing软件测试网k
]\PG3C4Y%C
;N
Cxg!J&gV}
\4w0M0 一个简单的例子
tVVL$?#ZxaF0.@vo#@|,V
l-R0 让我们通过一个简单的图示来真正理解索引的内部结构吧,如下图1所示。我们在Personnel.Employee表上创建了一个非聚集索引,代码如下:51Testing软件测试网wm#x#{&M.pQ
o
CREATE NONCLUSTERED INDEX IX_Full_Name ON Personnel.Employee ( LastName,51Testing软件测试网9B/X8nyM!Bc9E6~uc FirstName, ) GO |
51Testing软件测试网V8g;@9U;|1?,\x 图例注释:51Testing软件测试网m5f2Pk/p.R/J,}'~
51Testing软件测试网2`c4WC0^@I 指向页的指针包含了文件号和页号。比如说5:4567指向的就是第5个文件的4567个页。51Testing软件测试网s+s j{\ b+qS&z
2` nVMs&t4O0图1.索引的竖切图
'Fi&dS.b"]/w5[0qJ.[}1l6Gh#`.W0 值得说明的是,上面的图只是一个样子,正常的情况下一个页中会包含远多于上面例子的行,并且页也会远远多于上面的例子。51Testing软件测试网_ Y5n&G6h3@P
51Testing软件测试网jyKnR/L 实际在页中索引条目并不是有序的,而是靠偏移指针进行定位的,这个页尾的偏移表是有序的。51Testing软件测试网W(t[v.RM
&O4Um-O[@0 很多情况下,页中并不像上面图中所展现的那样,页之间物理上是连续的,但它们之间逻辑上是连续的,逻辑和物理上的差异被称之为碎片。
Th3S&^#a,N_4Y0
X;J'nPJ_0 正如我们之前所说,每一个索引可以包含不止一层的中间页。51Testing软件测试网*y
pK6Px
51Testing软件测试网:Y&C_&qf-}vt ? 继续使用我们之前电话本的类比。比如你查找名为Helen Meyer的联系人,打开电话本找到第一页,对于在区间 “Fernandez,
Zelda”和 “Olsen, Karl”之间的名字,去看页5:431.然后你找到431页,这页告诉你对于Kumar,
Kevin”和“Nara, Alison”之间的名字,去找页5:2006。然后你找到5:2006就找到了你所需的联系人。
o/D9b#P
I#|051Testing软件测试网eLR#i0W'X$BS 索引深度51Testing软件测试网dX(? g\\"k(x-B
51Testing软件测试网%T%aJc a 索引的根页以及相关信息是存在系统表中的。每当SQL Server进行页查找时,SQL
Server都会从根页开始查找,经过中间节点,直到找到叶子节点,然后从叶子中找到需要的索引条目。对于我们10亿行的表来说,从根节点到叶子节点共需
要读取5层。而对图1所示的节点来说,只需要读取3次IO。51Testing软件测试网6H [1ad'|Ea p!V5{
(V3Bfr O;q(rM] X0 上面所说的层数,也被成为索引深度。取决于索引键的大小和数量。在AdventureWorks示例数据库中,没有哪个索引的层级超过3层。但对于其它索引键宽或是数据量大的表,就会有更深的层级。
f7TgT7\'V*?Cy051Testing软件测试网IVU
GG] sys.dm_db_index_physical_stats函数可以展示索引的详细信息,深度和大小。这是一个表值函数,比如下面代码我们可以找到SalesOrderDetai表相关的索引信息。51Testing软件测试网MpFj ^3a4N$U
!Q;^ j3lH6l#Hh
c9|051Testing软件测试网3|7BlwszW
s3b&z#at[
SELECT OBJECT_NAME(P.OBJECT_ID) AS 'Table' ,51Testing软件测试网h _!B G@4H2b I.name AS 'Index' ,
V/M m.Nsy0pGK0 P.index_id AS 'IndexID' ,51Testing软件测试网OS.S@ rm6S:G P.index_type_desc ,51Testing软件测试网
|/\m6BkRzGr
Ql P.index_depth , 7r$p&~5^
s z _0 P.page_count51Testing软件测试网/p-] [H%mw[.~f5m2x[ FROM sys.dm_db_index_physical_stats(DB_ID(),51Testing软件测试网,Q3_,E}4AA OBJECT_ID('Sales.SalesOrderDetail'), aJCF`.s}0 NULL, NULL, NULL) P JOIN F@RRm3go8t0sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID 6N[4LvR:D|0 AND I.index_id = P.index_id ; |
51Testing软件测试网Q\,ic7{_d 得到的结果如图2所示。
B1aU)mV(sL?T051Testing软件测试网!GT%i%^8D1ojp3W
图2.查询sys.dm_db_index_physical_stats函数得到的结果51Testing软件测试网}7xhoR4N)E0u
通过如下代码我们可以看到更详细的层级信息51Testing软件测试网"i:x(We#l*v,_H
n*kU~|$WL:Vp0SELECT OBJECT_NAME(P.OBJECT_ID) AS 'Table' ,51Testing软件测试网!ZU
S+e;GI$\1v:IJ
I.name AS 'Index' ,
B&I Zio5T%Dw-f0 P.index_id AS 'IndexID' ,
#ErhO'If(?0 P.index_type_desc ,
G2nS9v:Q^EL?y0 P.index_level ,51Testing软件测试网%T Q9u6A/]1v
P.page_count
@[8e}RH0FROM sys.dm_db_index_physical_stats(DB_ID(),
f4d&G
k.]|F w
R0 OBJECT_ID('Sales.SalesOrderDetail'), 2,51Testing软件测试网*Dznm's
NULL, 'DETAILED') P JOIN
9vIWxD$_UD0sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID
0H7o6s5WOb2D;}0 AND I.index_id = P.index_id ;51Testing软件测试网A f5FnaVD
得到的结果如图3所示。51Testing软件测试网kO8x$])Ze3N/|
图3.查询索引的详细信息
+T xaEV+ezn0 通过图3所示结果,可以看出:51Testing软件测试网)o#]/y-]Av.vk0F1A(q9?
● 叶子节点的条目分布在407页中
w/C
}QA6NB_0 ● 中间节点仅仅需要2页51Testing软件测试网q8|K&A^,]B
● 根节点只有1页
ns@"P
^ bE0 根据索引键的选择,书签的大小的不同,叶子节点通常是非叶子节点大小的上百倍。根据具体的数据不同而不同。
WY[
qvBE6\0 记住包含列仅仅适用在非聚集索引并且只存在于叶子节点中,包含列对于上层的层级是透明的,这也是为什么包含列不会增加非叶子节点键的大小。
_,f
R/W4r0aR6C&Ny0 因为聚集索引的叶子节点是表数据本身,所以除了叶子节点的数据是表数据本身之外,还需要存储一些额外的非叶子层级。因为无论是否有聚集索引数据本身都是存在的,所以创建聚集索引的时候不仅需要花费一些时间和资源,创建成功后还需要一些额外的空间存储非叶子节点。
A?;fMH$]-Z&D.]0 总结51Testing软件测试网)eUbi.bY#_ P
索引的结构使得SQL Server可以根据键值快速找到所需的列,一旦找到所需的列之后,SQL Server可以:51Testing软件测试网8y
zX
F%Q
● 直接访问所需的行51Testing软件测试网6}GQ0jp
● 从找到的数据位置开始,根据双向链表找相邻的页51Testing软件测试网
y&AUnf^\
索引树结构早已经在没有关系数据库时就开始被使用了,事实证明,这是一种优秀的结构。51Testing软件测试网
Nl pR$E C6_7C
相关链接:51Testing软件测试网T8E-k5]$V|
SQL Server索引进阶之书签
P.\Dm$?_fu:~3y ~0SQL Server索引进阶之页和区
o$J}y&K
}G0s0
N!puf;u3v#nP'W;i(n0