《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(4)
《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 EmployeeHeap ALTER TABLE EmployeeHeap ADD CONSTRAINT EmployeeHeapPK PRIMARY KEY NONCLUSTERED (EmployeeID); go -- Add the nonclustered UNIQUE KEY on SSN for EmployeeHeap ALTER 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.indexes WHERE 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_tablepages EXEC ('DBCC IND (IndexInternals, EmployeeHeap, 2)'); go SELECT IndexLevel , PageFID , PagePID , PrevPageFID , PrevPagePID , NextPageFID , NextPagePID FROM sp_tablepages ORDER 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) AS BEGIN 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 e WHERE 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,e | c,h,e,b |
| e | e,b,h
|
| h | h,e,b
|
| b,c,d | b,c,d,e,h
|
回忆我们使用Unique约束在SSN列：
-- Add the nonclustered UNIQUE KEY on SSN for EmployeeHeap ALTER TABLE EmployeeHeap ADD CONSTRAINT SSNHeapUK UNIQUE NONCLUSTERED (SSN); go SELECT name AS IndexName, index_id FROM sys.indexes WHERE [object_id] = OBJECT_ID ('Employee'); go 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.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_tablepages EXEC ('DBCC IND (IndexInternals, Employee, 2)'); go SELECT IndexLevel , PageFID , PagePID , PrevPageFID , PrevPagePID , NextPageFID , NextPagePID FROM sp_tablepages ORDER BY IndexLevel DESC, PrevPagePID; go
结果：
结果中可以看出：现在我们应该已经比较熟悉这种分析了吧？过程 略去，留给读者思考（如有疑问，可以联系邀月[email protected]).
继续分析：
DBCC TRACEON (3604)
go
DBCC PAGE (IndexInternals, 1, 4264, 3);
go
结
果：
注意：在上图中可以看到：对一个有聚集键的表的非聚集索引的叶级页 (leaf-level page)有实际的列值包含两部分：索引键(index key,在本例中是SSN列)和数据行的书签(bookmark,本例中是EmployeeID)，这个列值，被复制到非聚集索引的页级。 如果聚 集键变宽了，非聚集索引的叶级相应也随之变宽。
为了便于理解，我们回顾这个查询：
SELECT e.*
FROM dbo.Employee AS e
WHERE 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))