SQL Server 深入解析索引存储(下)

标签：SQL SERVER/MSSQL SERVER/数据库/DBA/索引体系结构/非聚集索引

概述

非聚集索引与聚集索引具有同样的 B 树结构。它们之间的显著区别在于下面两点：

基础表的数据行不按非聚集键的顺序排序和存储。
非聚集索引的叶层是由索引页而不是由数据页组成。

既能够使用聚集索引来为表或视图定义非聚集索引，也能够依据堆来定义非聚集索引。非聚集索引中的每一个索引行都包括非聚集键值和行定位符。此定位符指向聚集索引或堆中包括该键值的数据行。

非聚集索引行中的行定位器或是指向行的指针，或是行的聚集索引键，例如以下所述：

假设表是堆（意味着该表没有聚集索引）。则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。
整个指针称为行 ID (RID)。
假设表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。假设聚集索引不是唯一的索引，SQL Server 将加入在内部生成的值（称为唯一值）以使全部反复键唯一。此四字节的值对于用户不可见。
仅当须要使聚集键唯一以用于非聚集索引中时，才加入该值。
SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

对于索引使用的每一个分区，非聚集索引在 index_id >0 的 sys.partitions 中都有相应的一行。默认情况下，一个非聚集索引有单个分区。假设一个非聚集索引有多个分区，则每一个分区都有一个包括该特定分区的索引行的 B 树结构。

比如，假设一个非聚集索引有四个分区，那么就有四个 B 树结构。每一个分区中一个。

依据非聚集索引中数据类型的不同，每一个非聚集索引结构会有一个或多个分配单元。在当中存储和管理特定分区的数据。每一个非聚集索引至少有一个针对每一个分区的 IN_ROW_DATA 分配单元（存储索引 B 树页）。

假设非聚集索引包括大型对象 (LOB) 列，则另一个针对每一个分区的 LOB_DATA 分配单元。

此外，假设非聚集索引包括的可变长度列超过 8,060 字节行限制大小，则另一个针对每一个分区的 ROW_OVERFLOW_DATA 分配单元。有关分配单元的具体信息。请參阅表组织和索引组织。B 树的页集合由 sys.system_internals_allocation_units 系统视图中的 root_page 指针定位。

要非常好的理解这篇文章的内容之前须要先阅读我前面写的上中部分的两篇文章：

SQL Server 深入解析索引存储(中)

SQL Server 深入解析索引存储(上)

正文

非聚集索引结构

生成測试数据

CREATE TABLE Torder
(ID INT IDENTITY(1,1) NOT NULL,
NAME CHAR(100) NOT NULL,
pro VARCHAR(8000) NULL,
Statu INT NOT NULL,
IDATE DATETIME DEFAULT(GETDATE())
)
GO
---插入1000条測试数据
DECLARE @ID INT=1
WHILE(@ID<=1000)
BEGIN
INSERT INTO Torder(NAME,pro,Statu)VALUES('商品'+CONVERT(CHAR(20),@ID),REPLICATE(1,8000),LEFT(@ID,1))
SET @ID=@ID+1 
END
GO
---创建非聚集索引
CREATE INDEX IX_Torder ON Torder
(NAME,Statu
)
INCLUDE(IDATE)


SELECT DISTINCT so.name, so.object_id,sp.index_id,internals.type_desc,internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page,  first_page, root_page
FROM sys.objects so
INNER JOIN sys.partitions sp ON so.object_id = sp.object_id
INNER JOIN sys.allocation_units sa ON sa.container_id = sp.hobt_id
INNER JOIN sys.system_internals_allocation_units internals ON internals.container_id = sa.container_id
WHERE so.object_id = object_id('Torder')

因为创建的表仅仅有非聚集索引。所以整个表的页存储中有三部分数据：堆页面、溢出页面、索引页面。

堆中共同拥有20个数据页和一个IAM页。

溢出单元有1001个页面包含一个IAM页；

索引中共同拥有20个页当中18个数据页一个ROOT页和一个IAM页.

一个堆页相应多个溢出页，由于Pro有8000个字节所以一行占一页。而表的其他字段仅仅有116个字节一个堆页能够存50条记录，所以并非一个溢出页就唯一相应一个堆页

分析页的存储信息

---开启跟踪标志
DBCC TRACEON(3604,2588)
--DBCC TRACEOFF(3604,2588)
---获取对象的数据页，结构：数据库、对象、显示
DBCC IND(Ixdata,Torder,-1)

上一章中已经讲过了堆页面和溢出页面，所以如今就讲非聚集索引页

看过前面的文章应该一眼就能看出1281页是ROOT页。如今就分析1281页

分析非聚集索引根页

DBCC page(Ixdata,1,1281,3)

如今来分析行定位指针是如何的：0x6801000001002F00

除去开头的16进制标示，剩下总共8个字节，从右往左当中行号2个字节，文件标示ID2个字节，剩下的4个字节就是页号了。所以

行号（002f）=47

文件页（0001）=1

页号（00000168）=360页

如今查看360页的信息

DBCC page(Ixdata,1,360,3)

47行的记录正好是“商品150”

分析非聚集索引索引页

通过对照会发现索引页比根页多出了索引包括列值和键的哈希值,这个里面的keyhashvalue应该是NAME字段的值通过某种方法算出来的，应该不包括statu字段，这里仅仅是推測通过平时的查询你会产生这种推測，当你where条件仅仅有statu的时候是不走索引查找的。

測试简单的查询

这里的'商品150'和'商品153'都是1280页中的记录。1280页是索引页，当中'商品150'是该页的第一条记录

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN  
SELECT [ID]
      ,[NAME]
      ,[pro]
      ,[Statu]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Torder]
  WHERE NAME='商品153'

--COMMIT 

另开一个窗体
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
 WHERE c.[dbid]=DB_ID('Ixdata') AND a.[request_session_id]=58  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]

从上面的查询过程能够知道页面总共读取了三次(索引叶一次堆页一次溢出页一次)。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO        
BEGIN TRAN  
 SELECT 
       [ID]
      ,[NAME]
      ,[pro]
      ,[Statu]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Torder]
  WHERE NAME='商品150'

通过对照查询'商品150'和'商品153'能够看到假设查找的页面的第一条记录，它须要再读取该索引页的前一个页面，假设该索引页是第一页就无需再读除本身的其它索引页了，文章写到后面反过来思考才知道为什么非聚集索引还须要多查找一个页面了。由于非聚集索引是同意存在反复值所以才须要再往前查找。假设前面一个页查找不到则结束。假设前面一个页还没查完会再往前一个页进行查。当然查询商品153的时候就已经推断了前一条记录的键值是不一样的否则也是要再查询前一个页，这也是非聚集索引的一个特殊情况吧！

索引扫描

update Torder
set statu=100
where id=1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO        
BEGIN TRAN  
 SELECT 
       [ID]
      ,[NAME]
      ,[pro]
      ,[Statu]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Torder]
  WHERE [Statu]=100

该查询总共扫描了18个索引页+1个堆页+1个溢出页.

创建聚集索引

ALTER TABLE dbo.Torder ADD CONSTRAINT
    PK_Torder PRIMARY KEY CLUSTERED 
    (
    ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


SELECT DISTINCT so.name, so.object_id,sp.index_id,internals.type_desc,internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page,  first_page, root_page
FROM sys.objects so
INNER JOIN sys.partitions sp ON so.object_id = sp.object_id
INNER JOIN sys.allocation_units sa ON sa.container_id = sp.hobt_id
INNER JOIN sys.system_internals_allocation_units internals ON internals.container_id = sa.container_id
WHERE so.object_id = object_id('Torder')

非聚集索引数据页比之前少了一页

因为如今的指针比之前的16进制指针要所占有的字节要少。所以仅仅须要17个页面就能够存下。

分析索引页148

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN  
SELECT [ID]
      ,[NAME]
      ,[pro]
      ,[Statu]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Torder]
  WHERE NAME='商品152'


在还有一个窗体打开
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
 WHERE c.[dbid]=DB_ID('Ixdata') AND a.[request_session_id]=58  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]

从上面的逻辑读取和查询步骤能够证实前面的推測。应该是隐藏了一张行定位表。

假设表有聚集索引或索引视图上有聚集索引。则行定位器是行的聚集索引键。假设聚集索引不是唯一的索引，SQL Server 将加入在内部生成的值（称为唯一值）以使全部反复键唯一。此四字节的值对于用户不可见。

仅当须要使聚集键唯一以用于非聚集索引中时，才加入该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

查看索引统计信息

DBCC SHOW_STATISTICS ("dbo.Torder", IX_Torder);

前面建的包括索引有这三种组合方式，所以组合索引的第二个字段不被用来单独做查找。

总结

非聚集索引和聚集索引不一样，聚集索引索引页的键值指向数据页的详细行。而非聚集索引不存在数据页，非聚集索引的索引页中的记录行指向聚集索引或者堆的详细数据页的数据行然后来获取记录，假设堆或聚集索引还存在溢出的话，从堆或者聚集索引的数据记录还有指向溢出页面的指针。

补充一下在非聚集索引中存在聚集索引与堆的优点，看完上文你会发现非聚集索引的数据页记录的行定位指针分别指向聚集索引或堆的行。可是指向聚集索引的行定位是逻辑值而指向堆的是实际的rid值，逻辑值的优点就是在聚集索引发生分页的情况下。逻辑值不用改变也就无需更新非聚集索引的指针。

花了四天时间最终把这个系列的写完了。又一次去理解一遍把曾经的一些不理解的知识点给弄明确了。还是收获非常多。

假设文章对大家有帮助。希望大家能给个推荐，谢谢！。！

备注：

作者：pursuer.chen

博客：http://www.cnblogs.com/chenmh

本网站全部随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。

《欢迎交流讨论》