《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(4
《Microsoft Sql server 2008 Internals》读书笔记订阅地址：http://www.iyunv.com/downmoon/category/230397.html/rss
《Microsoft Sql server 2008 Internals》索引目录：
《Microsoft Sql server 2008 Internal》读书笔记--目录索引
上节主要学习了聚集索引的物理结构以及查找数据行的方式。本节我们了解几类特殊的非聚集索引的结构以及数据存储的方式。
■非聚集索引的结构 （nonclustered Index Structures）
非聚集索引的叶级内容依赖于以下以下几个因素：非聚集索引键的定义，基表的结构（是Heap还是已经有聚集索引)，任何非聚集索引内容（比如包含性 列、过滤索引(Filtered Indexes)的是否存在，还有， 非聚集索引的定义是否使用Unique等。
我们继续使用上节使用过的数据库IndexInternals,不同的是，这次我们建立两个表：Employee表有一个主键约束 (EmployeeID列的聚集索引)，EmployeeHeap表没有聚集索引，其他内容与Employee表完全一致，但是它使用 EmployeeID列作为非聚集索引。
■堆 中的非聚集索引行(Nonclustered index rows on a Heap)
创建表的语句如下：
http://images.iyunv.com/OutliningIndicators/ContractedBlock.gifhttp://images.iyunv.com/OutliningIndicators/ExpandedBlockStart.gif代码
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
http://images.iyunv.com/OutliningIndicators/ContractedBlock.gifhttp://images.iyunv.com/OutliningIndicators/ExpandedBlockStart.gif代码
--与上一节的聚集索引表Employee对应：
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')
, 1
, NULL
, 'DETAILED');
go
结果：
D L Count PgCnt PgPercentFull MinLen MaxLen AvgLen
1 0 80000 4000 99.3081294786261 400 400 400
下面我们来创建一个非聚集主键和一个非聚集的惟一键：
http://images.iyunv.com/OutliningIndicators/ContractedBlock.gifhttp://images.iyunv.com/OutliningIndicators/ExpandedBlockStart.gif代码
-- 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查看这个结构：
http://images.iyunv.com/OutliningIndicators/ContractedBlock.gifhttp://images.iyunv.com/OutliningIndicators/ExpandedBlockStart.gif代码
-- 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') 结果：
http://public.blu.livefilestore.com/y1pmN1yuNmXDUReAWYZLEOGWBX8W8WqCKuySyVDzlaI_3rUXT4q4J2-fU2fMnjS6apquyu0EcGiKp850O-j3xWxGg/pageIndex20100_06.png
这里我们用DMV来查看：
http://images.iyunv.com/OutliningIndicators/ContractedBlock.gifhttp://images.iyunv.com/OutliningIndicators/ExpandedBlockStart.gif代码
-- 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 结果：
http://public.blu.livefilestore.com/y1pdrZGYxPdVYOdRf8wAOeLLK1s9wlX7EgxBmi-uSEAeNV7_0bkF6eXvWUEnRyKr3_CNuuOA4Jy_e6j71ZTmE99iQ/pageIndex20100_07.png
从结果中我们看到，minLen为13。即4个字节的EmployeeID加上数据行的书签(bookmark,即the physical RID)8字节。一个固定宽度的列同时没有列允许为null。因此4+8+1=13。我们使用DBCC IND查看更详细的存储。
http://images.iyunv.com/OutliningIndicators/ContractedBlock.gifhttp://images.iyunv.com/OutliningIndicators/ExpandedBlockStart.gif代码
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 结果：
http://public.blu.livefilestore.com/y1pdrZGYxPdVYP_gMHRwH8UTysZV_R0ZpKDTGl-ANENGbP_LzMgFeXE_iWmyifUOfq9xFWtMmO37hiDFxn8Qd0Dyw/pageIndex20100_08.png
我们从结果中看到：root page在FileID为1的页(page 8608)。叶级页被标记，索引等级为0，因此，叶级的第一页为在FileID1的8544页上。为了更清楚地看到，我们用DBCC Page命令：
http://public.blu.livefilestore.com/y1p1viOCL7rKe7WflxnG0ZSyViJxKsb32NUZR_CJXM-VufecsrFuIx9vOgxSnlQBe-0xF3VUvnJDVm2ZiPO6JW0gA/pageIndex20100_09.png
从输出的结果看，在一个Heap中非聚集索引的叶级页有一个索引键列值（本例中是EmployeeID),加上一实际数据行的RID。最后一列 KeyHashValue并没有实际存在索引行中，它是一个固定长度的字符串，衍生自一个所有键列的哈希公式，这个值被用于在某些其它工具中代表行 (Row)，在第十章中将会提到sys.dm_tran_locks。当一个锁(lock)被索引行保持时，最后一列表明索引行的哪个键被锁。
RID可以通过下面的function转化为FileID:PageID:SlotNumber格式：
http://images.iyunv.com/OutliningIndicators/ContractedBlock.gifhttp://images.iyunv.com/OutliningIndicators/ExpandedBlockStart.gif代码
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
有了这个格式，我们再来看下，
http://public.blu.livefilestore.com/y1pdgnDK5Wz0SLRiYMZc2CKmnpN5ILf20IvRezbA3gaWelVkz4qyovgiHmPxF002zb4iNqLub1oep1mJ9VAS8kV2w/pageIndex20100_10.png
在本例中，我们看到了一个非聚集索引的叶级的一个非聚集索引行的结构，(是不是有点拗口哪！)同时也了解一个书签查询(bookmark lookup)是如何（通过Heap的RID从非聚集索引到Heap）被执行的。想像一下这个查询：
SELECT e.*
FROM dbo.EmployeeHeap AS e
WHERE e.EmployeeID = 27682;
go
因为表是Heap,仅有非聚集索引能被用于精确地导航这条数据，在本例中是EmployeeID上的非聚集索引，第一步是定位到root Page
http://public.blu.livefilestore.com/y1pSAMTEZB16cBemkX79wVpLmKBttyQuRoPR_vuBo_NQAJPgtzoZXii-ktVwxWdcwCdHJX-Tw8jOfXyZkK1otqjMg/pageIndex20100_11.png
27682应该在27490与28029之间，因此如果一个27682的EmployeeID存在，它一定在特定范围定义的索引区间里，于是我们不得不继续往下导航到ChildPage
(8595)：
http://public.blu.livefilestore.com/y1p6VbZeq46_6Q8tDtIBuyWp8gj1g4OJVciYAdxfbdWhdqtht7Wa4XRdBwYC_pkrQTwvt-JykJOnFs3z6fhq4S-mQ/pageIndex20100_12.png
返回结果共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,hhh,e,bb,c,db,c,d,e,h 回忆我们使用Unique约束在SSN列：
http://images.iyunv.com/OutliningIndicators/ContractedBlock.gifhttp://images.iyunv.com/OutliningIndicators/ExpandedBlockStart.gif代码
-- 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 ('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温习一下这个索引的叶级页：
http://images.iyunv.com/OutliningIndicators/ContractedBlock.gifhttp://images.iyunv.com/OutliningIndicators/ExpandedBlockStart.gif代码
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 结果：
http://public.blu.livefilestore.com/y1pZ788CWvlsUF6Ujhwp9_QyhQsRb2WpewUtnKue6nOvQPX7mq3QyT1R70tAfaaDrtugoNh29v63A5DaSXbbVEELA/pageIndex20100_13.png
结果中可以看出：现在我们应该已经比较熟悉这种分析了吧？过程 略去，留给读者思考（如有疑问，可以联系邀月3w@live.cn).
继续分析：
DBCC TRACEON (3604)
go
DBCC PAGE (IndexInternals, 1, 4264, 3);
go 结 果：
http://public.blu.livefilestore.com/y1prtOnkXo5PonClpV8JbcovdufmTgGnGcmoHJBAPAm8uRd7OLUF_EX6VhqB-9CpmiZyWCtkw4RsJQD72SlsRg2eA/pageIndex20100_14.png
注意：在上图中可以看到：对一个有聚集键的表的非聚集索引的叶级页 (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),这个留给大家去做，呵呵。
限于篇幅，下篇将继续学习三类特殊的非聚集索引行：
1、非惟一的非聚集索引行(nonunique Nonclustered index rows)
２、使用包含性列的非聚集索引行(nonunique Nonclustered index rows with included Columns(using include))
３、使用过滤器的非聚集索引行(Nonclustered index rows with Filters(Filtered Indexes))
页:
[1]