你的位置:首页 > 数据库

[数据库]索引深入浅出:非唯一列上的非聚集索引


在索引深入浅出:非聚集索引的B树结构在聚集表里,我们讨论了非聚集索引。我们提到,唯一非聚集索引可以让所讨论的问题变得简单很多。我们已经知道了非聚集索引的通用结构,现在我们来看下在非唯一列上的非聚集索引的存储结构。

在索引深入浅出:选择正确并合适的聚集索引键里,我们讨论了在非唯一列上如何管理聚集键。我们知道SQL Server会给所有发生重复的聚集键增加4 bytes的值。同样,非聚集索引在B树的所有层增加聚集键,让在下一层的记录标识唯一。至于聚集索引,uniquifier 只在重复时增加。对于非聚集索引,如果创建索引不唯一时,聚集键会在所有记录增加。如果非聚集索引是以唯一定义的,SQL Server只在叶子层增加聚集键,用做书签查找(bookmark lookup)操作。

我们来看一个例子。

1 SELECT * INTO dbo.SalesOrderDetail FROM AdventureWorks2008r2.Sales.SalesOrderDetail2 GO3 CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetail ON dbo.SalesOrderDetail(SalesOrderDetailID)4 GO5 CREATE INDEX Ix_ProductId ON SalesOrderDetail(ProductId,Salesorderid)

我们创建了SalesOrderDetail表的副本,在SalesOrderDetailId 列定义了唯一聚集索引,在ProductId和SalesOrderId列定义了非聚集索引。注意,在创建非聚集索引的时候,我有意回避了使用Unique关键字,即使这个非聚集索引键是唯一的。

我们用DBCC IND看下非聚集索引的页面分配情况,并找出根页。

1 TRUNCATE TABLE dbo.sp_table_pages2 INSERT INTO sp_table_pages EXEC('DBCC IND(IndexDB,SalesOrderDetail,2)')3 GO4 5 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根节点/索引页

可以看出,5128页是我们的根页。我们用DBCC PAGE看下5128页信息还有叶子页的信息。

1 DBCC TRACEON(3604)2 DBCC PAGE(IndexDB,1,5128,3)--根页3 4 DBCC TRACEON(3604)5 DBCC PAGE(IndexDB,1,3760,3)--叶子页

我们来看上半部分的根页,聚集键(SalesOrderDetailid)被加到了根页。如果你和索引深入浅出:非聚集索引的B树结构在聚集表里的根页比较,会发现那里的根页里没有聚集键,只有在叶子页里才可以找到聚集键。在你把非聚集索引定义为唯一或非唯一(unique or non unique)时,叶子层的页结构不会发生改变。

我们看下堆表的情况。

 1 SELECT * INTO dbo.SalesOrderDetailHeap FROM AdventureWorks2008R2.Sales.SalesOrderDetail 2 GO 3 CREATE INDEX Ix_ProductId ON SalesOrderDetailHeap (ProductId,Salesorderid) 4 GO 5 SELECT index_id FROM sys.indexes WHERE name='Ix_ProductId' AND  6 OBJECT_NAME(OBJECT_ID)='SalesOrderDetailHeap' 7 GO 8 DBCC ind('IndexDB','SalesOrderDetailHeap',2) 9 GO10 TRUNCATE TABLE dbo.sp_table_pages11 INSERT INTO sp_table_pages EXEC('DBCC IND(IndexDB,SalesOrderDetailHeap,2)')12 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根节点/索引页13 DBCC TRACEON(3604)14 DBCC PAGE(IndexDB,1,1192,3)--根页15 16 DBCC TRACEON(3604)17 DBCC PAGE(IndexDB,1,1096,3)--叶子页

在根页我们看到多了Heap RID列,如果你回去看看索引深入浅出:非聚集索引的B树结构在堆表,你会发现Heap RID列只在叶子层里的页出现,不在根页出现。在你把非聚集索引定义为唯一或非唯一(unique or non unique)时,叶子层的页结构不会发生改变。

你可能已经注意到,在我们上述的例子里,即使非聚集索引键是唯一的,SQL Server还是只当它是非聚集索引,因为当我们创建非聚集索引时没加unique关键字。在索引所有层增加聚集键(或Heap RID)可能会增加更多的索引层IO操作,这个看聚集键的大小而定。因此在所有情况下,我们定义非聚集索引时,考虑选择唯一列(或多列)作为非聚集键非常重要。