索引键的唯一性（4/4）：非唯一聚集索引上的唯一和非唯一非聚集索引

在上一篇文章里，我谈了唯一聚集索引上的唯一和非唯一非聚集索引的区别。在这篇文章里，我想谈下非唯一聚集索引上的唯一和非唯一聚集索引的区别。我们都知道，SQL Server内部把非唯一聚集索引当作唯一聚集索引处理的。如果你定义了一个非唯一聚集索引，SQL Server会增加叫做uniquifier到你的索引记录，它导致你聚集索引的导航结构（B树的非叶子层）里，每条索引行都要用到4 bytes的开销。

下列代码再次创建我们的Customers表，这次在它上面定义非唯一聚集索引，最后定义2个非聚集索引，1个是唯一的，另1个是非唯一的。

-- 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 Customers 
( 
    CustomerID INT NOT NULL, 
    CustomerName CHAR(100) NOT NULL, 
    CustomerAddress CHAR(100) NOT NULL, 
    Comments CHAR(189) NOT NULL 
) 
GO 
 
-- Create a non unique clustered index on the previous created table 
CREATE CLUSTERED INDEX idx_Customers ON Customers(CustomerID) 
GO

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

-- Create a unique non clustered index on the clustered table 
CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueNCI_CustomerID 
ON Customers(CustomerName) 
GO 
 
-- Create a non-unique non clustered index on the clustered table 
CREATE NONCLUSTERED INDEX idx_NonUniqueNCI_CustomerID 
ON Customers(CustomerName) 
GO

我们通过DMV sys.dm_db_index_physical_stats查看索引的相关信息。

-- Retrieve physical information about the unique non-clustered index 
SELECT * FROM sys.dm_db_index_physical_stats 
( 
    DB_ID('ALLOCATIONDB'), 
    OBJECT_ID('Customers'), 
    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('Customers'), 
    3, 
    NULL, 
    'DETAILED'
) 
GO

从图中我们可以看到，在导航层的唯一非聚集索引每个索引行占用107 bytes，而非唯一非聚集索引每个索引行平均占用117 bytes（最小111 bytes，最大119 bytes）。我们用DBCC PAGE分析下各自的根页。

TRUNCATE TABLE dbo.sp_table_pages
INSERT INTO dbo.sp_table_pages
EXEC('DBCC IND(ALLOCATIONDB, Customers, -1)') 

SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC

首先是唯一非聚集索引，它的根页是21058。

DBCC PAGE(ALLOCATIONDB, 1, 21058, 3) 
GO

DBCC PAGE(ALLOCATIONDB, 1,21057,3)
GO

从图中我们可以看出，唯一非聚集索引在索引根层（还有中间层）只保存了唯一非聚集键，因为非聚集键本身就已经唯一了。

我们换参数1再来看看根页信息：

DBCC TRACEON(3604)
DBCC PAGE(ALLOCATIONDB, 1, 21058, 1) 
GO

这107 bytes包含下列信息：

• 1 byte： 状态位
• n bytes：非唯一聚集索引键——这里是CustomerName列，100 bytes
• 4 bytes：页ID（PageID）
• 2 bytes：文件ID（FileID）

我们再来看看唯一非聚集索引的叶子页。

DBCC PAGE(ALLOCATIONDB, 1,21322,3)
GO

我们可以看出SQL Server通过非唯一聚集键和uniquifier来指向聚集表的对应记录。

这里我们可以得出结论：在非唯一聚集索引上的唯一非聚集索引只在叶子层使用4 bytes的uniquifier，因为这里SQL Server使用聚集键和uniquifier直接指向对应的记录。这个4 bytes的uniquifier在唯一非聚集索引的非叶子层（根层和中间层）不存在。

我们再来看看非唯一非聚集索引的根页，它的根页是22986。

DBCC PAGE(ALLOCATIONDB, 1, 22986, 3) 
GO

这里的根页输出信息非常有意思！索引记录的键必须设计为唯一。SQL Server如何让非唯一非聚集索引键唯一呢？非常简单——加下聚集键（CustomerID (key)）（4 bytes）。但是聚集键这里默认还是不唯一的，因此SQL Server又加了uniquifier（4 bytes），因次当你uniquifier不为0的时候，每个索引行都有8 bytes的开销。当uniquifier为0时，你只要4 bytes的开销，因为这个情况下uniquifier并不物理保存在索引记录里，0是SQL Server自动假定的值。

我们再看看参数为1的信息：

DBCC TRACEON(3604)
DBCC PAGE(ALLOCATIONDB, 1, 22986, 1) 
GO

这111 bytes 包含下列信息：

• 1 byte：状态位
• n bytes：非唯一非聚集索引键——这里是CustomerName列，100 bytes
• n bytes：非唯一聚集索引键——这里是CustomerID列，4 bytes
• 4 bytes：页ID（PageID）
• 2 bytes：文件ID（FileID）
• 4 bytes：uniquifier用到的字节
• 4 bytes：uniquifier本身值，如果uniquifier非0的话

刚才我们通过sys.dm_db_index_physical_stats知道非唯一非聚集索引的索引记录是111 bytes，最长是117 bytes。

我们来看看非唯一非聚集索引的叶子页：

DBCC PAGE(ALLOCATIONDB, 1,23308,3)
GO

这和非唯一聚集索引上定义的唯一非聚集索引是一样的。叶子层通过聚集键（CustomerID）和uniquifier指向聚集表的对应记录。从这个例子我们可以看出，当你在非唯一聚集索引上定义非唯一非聚集索引时，会有巨大的开销（每个索引行 8 bytes），因为SQL Server内部要保证非聚集键唯一，这就需要大量的存储开销。  

参考文章：

https://www.sqlpassion.at/archive/2010/09/07/unique-and-non-unique-non-clustered-indexes-on-a-non-unique-clustered-index/