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=1WHILE(@ID<=1000)BEGININSERT INTO Torder(NAME,pro,Statu)VALUES('商品'+CONVERT(CHAR(20),@ID),REPLICATE(1,8000),LEFT(@ID,1))SET @ID=@ID+1 ENDGO---创建非聚集索引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_pageFROM sys.objects soINNER JOIN sys.partitions sp ON so.object_id = sp.object_idINNER JOIN sys.allocation_units sa ON sa.container_id = sp.hobt_idINNER JOIN sys.system_internals_allocation_units internals ON internals.container_id = sa.container_idWHERE 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 READBEGIN 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 pON 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 READGO        BEGIN TRAN   SELECT        [ID]      ,[NAME]      ,[pro]      ,[Statu]      ,[IDATE]  FROM [Ixdata].[dbo].[Torder]  WHERE NAME='商品150'                                                                                       

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

索引扫描

update Torderset statu=100where id=1SET TRANSACTION ISOLATION LEVEL REPEATABLE READGO        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_pageFROM sys.objects soINNER JOIN sys.partitions sp ON so.object_id = sp.object_idINNER JOIN sys.allocation_units sa ON sa.container_id = sp.hobt_idINNER JOIN sys.system_internals_allocation_units internals ON internals.container_id = sa.container_idWHERE so.object_id = object_id('Torder') 

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

由于现在的指针比之前的16进制指针要所占有的字节要少，所以只需要17个页面就可以存下。

分析索引页148

SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN 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 pON 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 将添加在内部生成的值（称为唯一值）以使所有重复键