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

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

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

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

USE ALLOCATIONDB
-- Create a table with 393 length + 7 bytes overhead = 400 bytes 
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24 
CREATE TABLE CustomersHeap 
( 
    CustomerID INT NOT NULL, 
    CustomerName CHAR(100) NOT NULL, 
    CustomerAddress CHAR(100) NOT NULL, 
    Comments CHAR(189) NOT NULL 
) 
GO

-- Insert 80.000 records 
DECLARE @i INT = 1 
WHILE (@i <= 80000) 
BEGIN 
    INSERT INTO CustomersHeap VALUES 
    ( 
        @i, 
        'CustomerName' + CAST(@i AS CHAR), 
        'CustomerAddress' + CAST(@i AS CHAR), 
        'Comments' + CAST(@i AS CHAR) 
    )     
    SET @i += 1 
END 
GO

-- Retrieve physical information about the heap table 
SELECT * FROM sys.dm_db_index_physical_stats 
( 
    DB_ID('ALLOCATIONDB'), 
    OBJECT_ID('CustomersHeap'), 
    NULL, 
    NULL, 
    'DETAILED'
) 
GO

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

-- Create a unique non clustered index 
CREATE UNIQUE NONCLUSTERED INDEX IDX_UniqueNCI_CustomerID 
ON CustomersHeap(CustomerID) 
GO  

-- Create a non-unique non clustered index 
CREATE NONCLUSTERED INDEX IDX_NonUniqueNCI_CustomerID 
ON CustomersHeap(CustomerID) 
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。

-- Retrieve physical information about the unique non-clustered index 
SELECT * FROM sys.dm_db_index_physical_stats 
( 
    DB_ID('ALLOCATIONDB'), 
    OBJECT_ID('CustomersHeap'), 
    2, 
    NULL, 
    'DETAILED'
) 
GO

-- Retrieve physical information about the non-unique non-clustered index 
SELECT * FROM sys.dm_db_index_physical_stats 
( 
    DB_ID('ALLOCATIONDB'), 
    OBJECT_ID('CustomersHeap'), 
    3, 
    NULL, 
    'DETAILED'
) 
GO

 

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

-- Create a helper table 
CREATE TABLE sp_table_pages 
( 
   PageFID TINYINT, 
   PagePID INT, 
   IAMFID TINYINT, 
   IAMPID INT, 
   ObjectID INT, 
   IndexID TINYINT, 
   PartitionNumber TINYINT, 
   PartitionID BIGINT, 
   iam_chain_type VARCHAR(30), 
   PageType TINYINT, 
   IndexLevel TINYINT, 
   NextPageFID TINYINT, 
   NextPagePID INT, 
   PrevPageFID TINYINT, 
   PrevPagePID INT, 
   PRIMARY KEY (PageFID, PagePID) 
) 
GO

-- Write everything in a table for further analysis 
INSERT INTO sp_table_pages 
EXEC('DBCC IND(ALLOCATIONDB, CustomersHeap, 2)') 
GO

-- Write everything in a table for further analysis 
INSERT INTO sp_table_pages 
EXEC('DBCC IND(ALLOCATIONDB, CustomersHeap, 3)') 
GO

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

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

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

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

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

DBCC TRACEON(3604) 
GO
DBCC PAGE(ALLOCATIONDB, 1, 14624, 1) 
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的内容：

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

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

DBCC TRACEON(3604) 
GO
DBCC PAGE(ALLOCATIONDB, 1, 14537, 1) 
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页的内容。

DBCC TRACEON(3604) 
GO
DBCC PAGE(ALLOCATIONDB, 1, 14608, 3) 
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%。在大的非聚集索引上会更加明显。

因此当你用下列脚本定义非聚集索引时：

CREATE NONCLUSTERED INDEX ...

如果不考虑下你数据的唯一性，你的非聚集索引就在浪费大量的存储空间，降低你的索引性能，并增加日后索引维护难度。

这个系列的下篇文章我们会看下唯一和非唯一非聚集索引之间的区别，请继续关注！ 

参考文章：

https://www.sqlpassion.at/archive/2010/08/18/unique-and-non-unique-sql-server-indexes-on-a-heap-table/