索引键的唯一性（1/4）：堆表上的唯一与非唯一非聚集索引的区别-阿里云开发者社区

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

2015-06-10 941

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS SQL Server，独享型 2核4GB

简介： 原文:索引键的唯一性（1/4）：堆表上的唯一与非唯一非聚集索引的区别在这篇文章里，我想详细介绍下SQL Server里唯一与非唯一非聚集索引的区别。看这个文章前，希望你已经理解了聚集和非聚集索引的概念，还有在SQL Server里是如何使用的。

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

在这篇文章里，我想详细介绍下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 GO
12 
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 GO
27 
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 GO
11 
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 GO
22 
23 -- Write everything in a table for further analysis 
24 INSERT INTO sp_table_pages 
25 EXEC('DBCC IND(ALLOCATIONDB, CustomersHeap, 2)') 
26 GO
27 
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 GO
3 DBCC PAGE(ALLOCATIONDB, 1, 14624, 3) 
4 GO

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

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