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

《Microsoft Sql server 2008 Internals》读书笔记订阅地址：

http://www.cnblogs.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)

  创建表的语句如下：

代码

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对应：
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

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

代码

--  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

结果：

 

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

继续分析：

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),这个留给大家去做，呵呵。

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

转载于:https://www.cnblogs.com/downmoon/archive/2010/02/21/1670052.html