《Microsoft Sql server 2008 Internals》读书笔记--目录索引
上节主要学习了聚集索引的物理结构以及查找数据行的方式。本节我们了解几类非聚集索引的结构以及数据存储的方式。
■非聚集索引的结构 (nonclustered Index Structures)
非聚集索引的叶级内容依赖于以下以下几个因素:非聚集索引键的定义,基表的结构(是Heap还是已经有聚集索引),任何非聚集索引内容(比如包含性列、过滤索引(Filtered Indexes)的是否存在,还有, 非聚集索引的定义是否使用Unique等。
我们继续使用上节使用过的数据库IndexInternals,不同的是,这次我们建立两个表:Employee表有一个主键约束(EmployeeID列的聚集索引),EmployeeHeap表没有聚集索引,其他内容与Employee表完全一致,但是它使用EmployeeID列作为非聚集索引。
■堆中的非聚集索引行(Nonclustered index rows on a Heap)
创建表的语句如下:
结果:
D L Count PgCnt PgPercentFull MinLen MaxLen AvgLen
1 0 80000 4000 99.3081294786261 400 400 400
下面我们来创建一个非聚集主键和一个非聚集的惟一键:
结果:
这里我们用DMV来查看:
结果:
从结果中我们看到,minLen为13。即4个字节的EmployeeID加上数据行的书签(bookmark,即the physical RID)8字节。一个固定宽度的列同时没有列允许为null。因此4+8+1=13。我们使用DBCC IND查看更详细的存储。
结果:
我们从结果中看到:root page在FileID为1的页(page 8608)。叶级页被标记,索引等级为0,因此,叶级的第一页为在FileID1的8544页上。为发更清楚地看到,我们用DBCC Page命令:
从输出的结果看,在一个Heap中非聚集索引的叶级页有一个索引键列值(要本例中是EmployeeID),加上一实际数据行的RID。最后一列KeyHashValue并没有实际存在索引行中,它是一个固定长度的字符串,衍生自一个所有键列的哈希公式,这个值被用于在某些其它工具中代表行(Row),在第十章中将会提到sys.dm_tran_locks。当一个锁(lock)被索引行保持时,最后一列表明索引行的哪个键被锁。
RID可以通过下面的function转化为FileID:PageID:SlotNumber格式:
测试一下:
结果:1:245:5
有了这个格式,我们再来看下,
在本例中,我们看到了一个非聚集索引的叶级的一个非聚集索引行的结构,(是不是有点拗口哪!)同时也了解一个书签查询(bookmark lookup)是如何(通过Heap的RID从非聚集索引到Heap)被执行的。想像这个查询:
因为表是Heap,仅有非聚集索引能被用于精确地导航这条数据,在本例中是EmployeeID上的非聚集索引,第一步是定位到root Page:
27682应该在27490与28029之间,因此如果一个27682的EmployeeID存在,它一定在特定范围定义的索引区间里,于是我们不得不继续往下导航到ChildPage(8595):
返回结果共539行。从本例中可以看出,SQL Server转换数据行的RID为FileID:PageID:SlotNumber格式,(在Heap中)继续查找合适的数据行。
■聚集表中的非聚集索引行(Nonclustered index rows on a Clustered table)
对一个有聚集索引的表来说,非聚集索引的叶级行结构和Heap非常类似。非聚集索引的叶级包括索引键和书签查找值(bookmark lookup value,即聚集键),然而,如果非聚集索引键与聚集键有某些列相同,SQL Server将只存储一次共同列在非聚集索引行。例如:如果聚集索引键是EmployeeID,同时有一个非聚集索引索引(LastName,EmployeeID,SSN),索引行只存储EmployeeID一次。
下表是非聚集索引键与非聚集叶级行的对应关系。
非聚集索引键 | 非聚集叶级行 |
a | a,b,e,h |
c,h,e | c,h,e,b |
e | e,b,h |
h | h,e,b |
b,c,d | b,c,d,e,h |
回忆我们使用Unique约束在SSN列:
结果:
IndexName index_id
EmployeePK 1
EmployeeSSNUK 2
D L Count PgCnt PgPercentFull MinLen MaxLen AvgLen
2 0 80000 179 99.3661106992834 16 16 16
2 1 179 1 44.2055843834939 18 18 18
在本例中,非聚集索引(Level 0)的叶级显示了80,000条记录数(表中有80,000行)、最低、最高、平均长度16(即固定宽度的索引行)。这很容易分解为SSN列(11字节的Charactor),表的聚集键EmployeeID对应的数据行的书签(聚集键)是4字节,同时这行是一个固定宽度行,没有列允许为Null值,行开销是1字节(11+4+1=16字节),我们用DBCC IND温习一下这个索引的叶级页:
结果:
结果中可以看出:现在我们应该已经比较熟悉这种分析了吧?过程略去,留给读者思考(如有疑问,可以联系邀月3w@live.cn).
继续分析:
结果:
注意:在上图中可以看到:对一个有聚集键的表的非聚集索引的叶级页(leaf-level page)有实际的列值包含两部分:索引键(index key,在本例中是SSN列)和数据行的书签(bookmark,本例中是EmployeeID),这个列值,被复制到非聚集索引的页级。如果聚集键变宽了,非聚集索引的叶级相应也随之变宽。
为了便于理解,我们回顾这个查询:
为了找到SSN为123-45-6789的行,SQL Server从root page开始往下导航到叶级。从前面查询可知:root page在FileID为1的4328页(记住:只要看indexLevel最高的索引级,本例中是1),我们可以执行与上篇文章相同的分析,通过B树(B-Tree),这个留给大家去做,呵呵。
限于篇幅,下篇将继续学习三类特殊的非聚集索引行:
1、非惟一的非聚集索引行(nonunique Nonclustered index rows)
2、使用包含性列的非聚集索引行(nonunique Nonclustered index rows with included Columns(using include))
3、使用过滤器的非聚集索引行(Nonclustered index rows with Filters(Filtered Indexes))
助人等于自助! 3w@live.cn