《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(4)

《Microsoft Sql server 2008 Internals》索引目录：

《Microsoft Sql server 2008 Internals》读书笔记--目录索引

 

上节主要学习了聚集索引的物理结构以及查找数据行的方式。本节我们了解几类非聚集索引的结构以及数据存储的方式。

■非聚集索引的结构 （nonclustered Index Structures）

非聚集索引的叶级内容依赖于以下以下几个因素：非聚集索引键的定义，基表的结构（是Heap还是已经有聚集索引)，任何非聚集索引内容（比如包含性列、过滤索引(Filtered Indexes)的是否存在，还有， 非聚集索引的定义是否使用Unique等。

我们继续使用上节使用过的数据库IndexInternals,不同的是，这次我们建立两个表：Employee表有一个主键约束(EmployeeID列的聚集索引)，EmployeeHeap表没有聚集索引，其他内容与Employee表完全一致，但是它使用EmployeeID列作为非聚集索引。

 ■堆中的非聚集索引行(Nonclustered index rows on a Heap)

  创建表的语句如下：

CREATE TABLE EmployeeHeap( EmployeeID INT NOT NULL IDENTITY, LastName NCHAR(30) NOT NULL, MiddleInitial NCHAR(1) NULL, SSN CHAR(11) NOT NULL, OtherColumns CHAR(258) NOT NULL DEFAULT 'Junk');go--与上一节的聚集索引表Employee对应： FirstName NCHAR(29) NOT NULL,SELECT index_depth AS D , index_level AS L , record_count AS 'Count' , page_count AS PgCnt , avg_page_space_used_in_percent AS 'PgPercentFull' , min_record_size_in_bytes AS 'MinLen' , max_record_size_in_bytes AS 'MaxLen' , avg_record_size_in_bytes AS 'AvgLen'FROM sys.dm_db_index_physical_stats (DB_ID ('IndexInternals') , OBJECT_ID ('IndexInternals.dbo.EmployeeHeap') , 0 , NULL , 'DETAILED');go
结果：

D    L    Count    PgCnt    PgPercentFull    MinLen    MaxLen    AvgLen
1    0    80000    4000    99.3081294786261    400    400    400

下面我们来创建一个非聚集主键和一个非聚集的惟一键：

-- Add a nonclustered PRIMARY KEY for EmployeeHeapALTER TABLE EmployeeHeap ADD CONSTRAINT EmployeeHeapPK PRIMARY KEY NONCLUSTERED (EmployeeID);go-- Add the nonclustered UNIQUE KEY on SSN for EmployeeHeapALTER TABLE EmployeeHeap ADD CONSTRAINT SSNHeapUK UNIQUE NONCLUSTERED (SSN);go可以用sys.indexes查看这个结构：-- To see all of the index IDs for a table, query sys.indexes:SELECT object_name(object_id) AS 'Object Name' , index_id AS 'Index ID' , name AS 'Index Name' , type_desc AS 'Type Description'FROM sys.indexesWHERE object_id = object_id('EmployeeHeap')

结果：

 

这里我们用DMV来查看：
-- For nonclustered indexes, use the index ID for parameter 3.SELECT index_depth AS D , index_level AS L , record_count AS 'Count' , page_count AS PgCnt , avg_page_space_used_in_percent AS 'PgPercentFull' , min_record_size_in_bytes AS 'MinLen' , max_record_size_in_bytes AS 'MaxLen' , avg_record_size_in_bytes AS 'AvgLen'FROM sys.dm_db_index_physical_stats (DB_ID ('IndexInternals') , OBJECT_ID ('IndexInternals.dbo.EmployeeHeap') , 2 , NULL , 'DETAILED');go

结果：

 

 从结果中我们看到，minLen为13。即4个字节的EmployeeID加上数据行的书签(bookmark,即the physical RID)8字节。一个固定宽度的列同时没有列允许为null。因此4+8+1=13。我们使用DBCC IND查看更详细的存储。

TRUNCATE TABLE sp_tablepages;INSERT sp_tablepagesEXEC ('DBCC IND (IndexInternals, EmployeeHeap, 2)');goSELECT IndexLevel , PageFID , PagePID , PrevPageFID , PrevPagePID , NextPageFID , NextPagePIDFROM sp_tablepagesORDER BY IndexLevel DESC, PrevPagePID;GO

 结果：

 

我们从结果中看到：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格式：

CREATE FUNCTION convert_RIDs (@rid BINARY(8))RETURNS VARCHAR(30)ASBEGIN RETURN ( CONVERT (VARCHAR(5), CONVERT(INT, SUBSTRING(@rid, 6, 1) + SUBSTRING(@rid, 5, 1)) ) + ':' + CONVERT(VARCHAR(10), CONVERT(INT, SUBSTRING(@rid, 4, 1) + SUBSTRING(@rid, 3, 1) + SUBSTRING(@rid, 2, 1) + SUBSTRING(@rid, 1, 1)) ) + ':' + CONVERT(VARCHAR(5), CONVERT(INT, SUBSTRING(@rid, 8, 1) + SUBSTRING(@rid, 7, 1)) ) )END;go

测试一下：

-- Using this function you can find EmployeeID of 6 because its-- hexadecimal RID is 0xF500000001000500:SELECT dbo.convert_RIDs (0xF500000001000500);go
结果：1:245:5

有了这个格式，我们再来看下，

 

在本例中，我们看到了一个非聚集索引的叶级的一个非聚集索引行的结构，(是不是有点拗口哪！)同时也了解一个书签查询(bookmark lookup)是如何（通过Heap的RID从非聚集索引到Heap）被执行的。想像这个查询：

SELECT e.*FROM dbo.EmployeeHeap AS eWHERE e.EmployeeID = 27682;go

因为表是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,ec,h,e,bee,b,h
hh,e,b
b,c,db,c,d,e,h

 回忆我们使用Unique约束在SSN列：

-- Add the nonclustered UNIQUE KEY on SSN for EmployeeHeapALTER TABLE EmployeeHeap ADD CONSTRAINT SSNHeapUK UNIQUE NONCLUSTERED (SSN);goSELECT name AS IndexName, index_idFROM sys.indexesWHERE [object_id] = OBJECT_ID ('Employee');goSELECT index_depth AS D , index_level AS L , record_count AS 'Count' , page_count AS PgCnt , avg_page_space_used_in_percent AS 'PgPercentFull' , min_record_size_in_bytes AS 'MinLen' , max_record_size_in_bytes AS 'MaxLen' , avg_record_size_in_bytes AS 'AvgLen'FROM sys.dm_db_index_physical_stats (DB_ID ('IndexInternals') , OBJECT_ID ('IndexInternals.dbo.Employee') , 2 , NULL , 'DETAILED');GO

结果：

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温习一下这个索引的叶级页：

TRUNCATE TABLE sp_tablepages;INSERT sp_tablepagesEXEC ('DBCC IND (IndexInternals, Employee, 2)');goSELECT IndexLevel , PageFID , PagePID , PrevPageFID , PrevPagePID , NextPageFID , NextPagePIDFROM sp_tablepagesORDER BY IndexLevel DESC, PrevPagePID;go

结果：

 

结果中可以看出：现在我们应该已经比较熟悉这种分析了吧？过程略去，留给读者思考（如有疑问，可以联系邀月[e-mail]).

继续分析：

DBCC TRACEON (3604)goDBCC PAGE (IndexInternals, 1, 4264, 3);go
结果：

 

 注意：在上图中可以看到：对一个有聚集键的表的非聚集索引的叶级页(leaf-level page)有实际的列值包含两部分：索引键(index key,在本例中是SSN列)和数据行的书签(bookmark,本例中是EmployeeID)，这个列值，被复制到非聚集索引的页级。如果聚集键变宽了，非聚集索引的叶级相应也随之变宽。

为了便于理解，我们回顾这个查询：

SELECT e.*FROM dbo.Employee AS eWHERE e.SSN = '123-45-6789';go
为了找到SSN为123-45-6789的行，SQL Server从root page开始往下导航到叶级。从前面查询可知：root page在FileID为１的4328页（记住：只要看indexLevel最高的索引级，本例中是1）,我们可以执行与上篇文章相同的分析，通过B树(B-Tree),这个留给大家去做，呵呵。

 限于篇幅，下篇将继续学习三类特殊的非聚集索引行：
１、非惟一的非聚集索引行(nonunique Nonclustered index rows)
２、使用包含性列的非聚集索引行(nonunique Nonclustered index rows with included Columns(using include))
３、使用过滤器的非聚集索引行(Nonclustered index rows with Filters(Filtered Indexes))

 

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!   [e-mail]