索引键的唯一性（1/4）：堆表上的唯一与非唯一非聚集索引的区别-白红宇

索引键的唯一性（1/4）：堆表上的唯一与非唯一非聚集索引的区别

阅读量：7141 次

发布时间：2019-06-28

本文共 5268 字，大约阅读时间需要 17 分钟。

原文:

在这篇文章里，我想详细介绍下SQL Server里唯一与非唯一非聚集索引的区别。看这个文章前，希望你已经理解了聚集和非聚集索引的概念，还有在SQL Server里是如何使用的。

很多人对唯一和非唯一索引非聚集索引的认识都不是很清晰。事实上，SQL Server在存储上这2类索引有着本质的区别，这些区别会影响到索引占用空间的大小和索引的使用效率。

今天我们从SQL Server里的堆表（Heap table） ，它是没有聚集索引定义的表，在它建立唯一和非唯一非聚集索引，来开始我们的分析。下列脚本会创建我们的测试表，并插入80000条记录。每条记录需要400 bytes，因此SQL Server在每页可以放20条记录。这就是说我们的堆表包括4000个数据页和1个IAM页。

1 USE ALLOCATIONDB 2 -- Create a table with 393 length + 7 bytes overhead = 400 bytes  3 -- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24  4 CREATE TABLE CustomersHeap  5 (  6     CustomerID INT NOT NULL,  7     CustomerName CHAR(100) NOT NULL,  8     CustomerAddress CHAR(100) NOT NULL,  9     Comments CHAR(189) NOT NULL 10 ) 11 GO12 13 -- Insert 80.000 records 14 DECLARE @i INT = 1 15 WHILE (@i <= 80000) 16 BEGIN 17     INSERT INTO CustomersHeap VALUES 18     ( 19         @i, 20         'CustomerName' + CAST(@i AS CHAR), 21         'CustomerAddress' + CAST(@i AS CHAR), 22         'Comments' + CAST(@i AS CHAR) 23     )     24     SET @i += 1 25 END 26 GO27 28 -- Retrieve physical information about the heap table 29 SELECT * FROM sys.dm_db_index_physical_stats 30 ( 31     DB_ID('ALLOCATIONDB'), 32     OBJECT_ID('CustomersHeap'), 33     NULL, 34     NULL, 35     'DETAILED'36 ) 37 GO

在堆表创建和数据插入后，你就可以在我们的堆表上CustomerID列定义唯一和非唯一非聚集索引。我们把2个索引都定义在同列，这样我们就可以分析唯一和非唯一聚集索引的区别。

1 -- Create a unique non clustered index 2 CREATE UNIQUE NONCLUSTERED INDEX IDX_UniqueNCI_CustomerID 3 ON CustomersHeap(CustomerID) 4 GO  5 6 -- Create a non-unique non clustered index 7 CREATE NONCLUSTERED INDEX IDX_NonUniqueNCI_CustomerID 8 ON CustomersHeap(CustomerID) 9 GO

如果在非唯一数据的列上定义唯一非聚集索引，SQL Server会返回你一个错误信息。当你创建非聚集索引时，如果不指定UNIQUE，SQL Server会创建非唯一的非聚集索引，这点很重要！因此你创建的非聚集索引默认情况下都是非唯一的非聚集索引。

在2个索引创建后，我们可以分析它们的大小，索引深度，索引大小等。使用DMV sys.dm_db_index_physical_stats，第3个参数传入index-id值。所有非聚集索引的ID值都开始于2，因此第1个非聚集索引的ID值为2，第2个非聚集索引的ID值为3。

1 -- Retrieve physical information about the unique non-clustered index  2 SELECT * FROM sys.dm_db_index_physical_stats  3 (  4     DB_ID('ALLOCATIONDB'),  5     OBJECT_ID('CustomersHeap'),  6     2,  7     NULL,  8     'DETAILED' 9 ) 10 GO11 12 -- Retrieve physical information about the non-unique non-clustered index 13 SELECT * FROM sys.dm_db_index_physical_stats 14 ( 15     DB_ID('ALLOCATIONDB'), 16     OBJECT_ID('CustomersHeap'), 17     3, 18     NULL, 19     'DETAILED'20 ) 21 GO

从输出结果你可以看到，唯一非聚集索引的索引根页占用约24%，非唯一非聚集索引的索引根页占用约39%，因此在堆表上，唯一/非唯一非聚集索引的存储格式肯定不一样！下一步我们用一个帮助表来存储DBCC IND命令的输出，用来做进一步分析。

1 -- Create a helper table  2 CREATE TABLE sp_table_pages  3 (  4    PageFID TINYINT,  5    PagePID INT,  6    IAMFID TINYINT,  7    IAMPID INT,  8    ObjectID INT,  9    IndexID TINYINT, 10    PartitionNumber TINYINT, 11    PartitionID BIGINT, 12    iam_chain_type VARCHAR(30), 13    PageType TINYINT, 14    IndexLevel TINYINT, 15    NextPageFID TINYINT, 16    NextPagePID INT, 17    PrevPageFID TINYINT, 18    PrevPagePID INT, 19    PRIMARY KEY (PageFID, PagePID) 20 ) 21 GO22 23 -- Write everything in a table for further analysis 24 INSERT INTO sp_table_pages 25 EXEC('DBCC IND(ALLOCATIONDB, CustomersHeap, 2)') 26 GO27 28 -- Write everything in a table for further analysis 29 INSERT INTO sp_table_pages 30 EXEC('DBCC IND(ALLOCATIONDB, CustomersHeap, 3)') 31 GO

现在我们可以用DBCC PAGE命令分下聚集索引页，使用这个命令前我们需要运行 DBCC TRACEON(3604)。在此之前，我们先找下根页。

1 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC,IndexID

可以看到，唯一非聚集索引的根页是14624；非唯一非聚集的根页是14608。

1 DBCC TRACEON(3604) 2 GO3 DBCC PAGE(ALLOCATIONDB, 1, 14624, 3) 4 GO

从输出结果我们可以看到，SQL Server存储者B树的子页信息，即非聚集索引最小键值位置。例如，14537页包含最小键值540到1078值的记录。当你使用参数1的DBCC PAGE时，你就获得了索引根页上，所有索引记录字节显示内容：

1 DBCC TRACEON(3604) 2 GO3 DBCC PAGE(ALLOCATIONDB, 1, 14624, 1) 4 GO

SQL Server这里需要11个字节来存储索引行，这些11个字节存储下列信息：

1 byte：状态位

4 bytes：索引键值（CustomerID），例如 540

4 bytes：子页ID值（ChildPageId），例如 14537

2 bytes：字段ID值（FileId），例如 1

索引行的长度取决于非聚集键的长度。这就是说如果你选择更短的非聚集键，SQL Server就可以保存更多的索引行。如果你选择了CHAR(100)类型字段作为非聚集索引键，SQL Server就需要更多的索引页来保存你的非聚集索引，因此使用长度短的索引键更高效。

最后我们看看子页14537的内容：

1 DBCC TRACEON(3604) 2 GO3 DBCC PAGE(ALLOCATIONDB, 1, 14537, 3) 4 GO

从图中，我们可以看到，SQL Server保存了数据页的索引键（CustomerID (key)）和用于定位对应记录的槽号（HEAP RID）。因为我们在表上没有定义聚集索引，SQL Server这里使用RID来指向数据页的记录。在堆表上的叶子层的索引页和聚集表上叶子层的索引页是不一样的。如果你用参数1来使用DBCC PAGE时，你就得到如下显示：

1 DBCC TRACEON(3604) 2 GO3 DBCC PAGE(ALLOCATIONDB, 1, 14537, 1) 4 GO

SQL Server需要13字节来保存每个索引行：

1 byte 状态位

4 bytes 索引键ID（CustomerID），例如540

4 bytes 页ID（PageID）

2 bytes 文件ID（FileID）

2 bytes 槽号（Slot number）

手头有了这些信息，我们就很容易定位页上的记录，因为知道了页号，文件号，还有槽号，页上的记录就可以很容易定位到。

我们再来看看非唯一的非聚集索引。根页号是14608，index id是3。我们来看下14608页的内容。

1 DBCC TRACEON(3604) 2 GO3 DBCC PAGE(ALLOCATIONDB, 1, 14608, 3) 4 GO

我们看到了不同的东西！！用DBCC PAGE输出非唯一非聚集索引的根页内容是不一样的。这里SQL Server额外增加了“ HEAP RID（key） ”列。这列的值是为了让你的非唯一非聚集索引唯一。在你索引行里HEAP RID列使用8个额外字节来存储下列信息，用来保证堆表索引键的唯一：

4 bytes：页号（PageID）

2 bytes：文件号（FileID）

2 bytes：槽号（Slot number）

在堆表上非唯一非聚集索引上，所有索引层的每个索引行都会增加8个额外字节占用，不包括叶子层，因为叶子层都会保存HEAP RID。因此在你创建非唯一非聚集索引时，请记住索引行的8字节的额外占用。因为我已经说过，默认创建的非聚集索引都是非唯一的。

这个例子，我们的非唯一索引非聚集索引占用空间是唯一非聚集索引的2倍，因为唯一索引需要11 bytes，而非唯一索引需要19 bytes（包括8 bytes的HEAP RID）。我们回头看下DMV sys.dm_db_index_physical_stats的信息输出，唯一非聚集索引的根页，页面空间使用率约24%，而非唯一非聚集索引的根页，页面空间使用率是39%。在大的非聚集索引上会更加明显。