转载

索引深入浅出(8/10):覆盖索引或列包含

在 索引深入浅出:非聚集索引的B树结构在聚集表 里,在聚集表里,我们看到非聚集索引的叶子层只包含非聚集索引键和聚集索引键。从聚集表结构或堆表结构里拿到剩下列,SQL Server需要进行书签/键查找操作。很多情况下书签或键查找非常消耗资源。我们来看个例子。

在这里我们打开了IO统计信息,还有点击工具栏的 索引深入浅出(8/10):覆盖索引或列包含 显示包含实际的执行计划。

 1 USE IndexDB  2 GO  3 DROP TABLE dbo.SalesOrderDetail                                 4 GO                                 5 SELECT * INTO dbo.SalesOrderDetail FROM AdventureWorks2008r2.Sales.SalesOrderDetail  6 GO  7 CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetail ON dbo.SalesOrderDetail(SalesOrderDetailID)  8 GO  9 CREATE UNIQUE NONCLUSTERED INDEX ix_Productid ON dbo.SalesOrderDetail(ProductId,SalesOrderId) 10 GO  11 SET STATISTICS IO ON 12 GO 13 SELECT SalesOrderDetailid,productid,salesorderid,orderqty,unitprice  14 FROM SalesOrderDetail  15 WHERE productid=707 AND SalesOrderID=43680

索引深入浅出(8/10):覆盖索引或列包含

索引深入浅出(8/10):覆盖索引或列包含

在IO统计信息里,SQL Server拿每条记录需要进行5个IO操作。在执行计划里,我们看到,查询开销的50%贡献给了书签查找(键查找)操作。

注意,这个表的非聚集索引有229个页,非聚集索引的B树结构深度为2。在这个非聚集索引里只要进行2个IO操作就可以完成查找操作,另外3个IO贡献给了书签查找。你可以用DBCC IND命令验证下或者参考下 索引深入浅出(4/10):非聚集索引的B树结构在聚集表 。

假设这个查询(用不同的参数)在应用程序里经常用到,现在你需要优化它。我们该怎么做?我们对此唯一能优化的话就是避免键查找(Key lookup)操作。因此我们修改下非聚集索引,把剩下2列(OrderQty, UnitPrice),不是聚集索引键,也不是非聚集索引键,也加入非聚集索引键。

1 DROP INDEX ix_Productid ON dbo.SalesOrderDetail 2 GO 3 CREATE UNIQUE NONCLUSTERED INDEX ix_Productid ON dbo.SalesOrderDetail(ProductId,SalesOrderId,OrderQty ,UnitPrice) 4 GO 5 SELECT SalesOrderDetailid,productid,salesorderid,orderqty,unitprice FROM SalesOrderDetail WHERE productid=707 AND SalesOrderID=43680

索引深入浅出(8/10):覆盖索引或列包含

索引深入浅出(8/10):覆盖索引或列包含

从执行计划里我们可以看到,我们已经回避了键查找操作,把IO操作从5个降到了3次。但是我们如果用DBCC IND看下非聚集索引,我们发现由于还这个改动,我们的非聚集索引深度增加了。因为索引层的增加,非聚集索引需要进行3次IO来完成这个操作。这将是最糟糕的,如果选择列更多的话,我们就需要在非聚集索引里增加更多的列来避免键查找操作。

覆盖索引(covering index )就是用来解决这个问题的。覆盖索引帮助我们在非聚集索引的叶子层增加非主键列,最小可能增加B树结构的深度。可以用CREATE INDEX语句增加包含列完成。

当索引包含查询列是,这个为称为覆盖索引。当我们创建非聚集索引去覆盖一个查询时,我们可以在索引里包含非主键列来覆盖查询列,这些覆盖列在主查询列里不会用到。这样查询性能会提升,因为查询优化器在索引里就可以定位到需要列的数据,表或聚集索引不会被访问。

1 DROP INDEX ix_Productid ON dbo.SalesOrderDetail 2 GO 3 CREATE UNIQUE NONCLUSTERED INDEX ix_Productid ON dbo.SalesOrderDetail(ProductId,SalesOrderId)  4 include(OrderQty ,UnitPrice) 5 GO 6 SELECT SalesOrderDetailid,productid,salesorderid,orderqty,unitprice FROM SalesOrderDetail  7 WHERE productid=707 AND SalesOrderID=43680

索引深入浅出(8/10):覆盖索引或列包含

索引深入浅出(8/10):覆盖索引或列包含

可以发现,我们已经避免了键查找操作,并把IO操作降到了2次。IO操作清楚的告诉我们聚集索引的深度是2。我们用DBCC IND和DBCC PAGE命令验证下。

 1 SELECT index_id FROM sys.indexes WHERE name='ix_Productid' AND OBJECT_ID= OBJECT_ID('SalesOrderDetail')  2 GO  3 DBCC ind('IndexDB','SalesOrderDetail',2)   4   5 TRUNCATE TABLE dbo.sp_table_pages  6 INSERT INTO sp_table_pages EXEC('DBCC IND(IndexDB,SalesOrderDetail,2)')  7 GO  8   9 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根节点/索引页 10  11 DBCC TRACEON(3604) 12 DBCC PAGE(IndexDB,1,2800,3)--根页 13  14 DBCC TRACEON(3604) 15 DBCC PAGE(IndexDB,1,2736,3)--叶子页

索引深入浅出(8/10):覆盖索引或列包含

include语句提到的列已经加到了叶子层的页,没有对非页层页造成影响。

列包含非常有用,我们可以把不能在索引键里加的列,在列包含里加入。另外避免超过当前索引大小的限制(最大键列数为 16 ,最大索引键大小为 900 字节)。 我们可以包含除去text,ntext和image类型的其他列,列包含也同样支持计算列(computed column)。

正文到此结束
Loading...