复合非聚集索引里列的顺序的重要性

当我谈论索引时,大家经常会问我在复合非聚集索引里,列的顺序是否重要?简单来说:“看情况”。我们来具体看下为啥“看情况”……

单例查找(Singleton Lookups)

当在你的表上有进行单例查找的查询时,在复合非聚集索引里列的顺序真的不重要。假设下列查询:

-- Without a supporting Non-Clustered Index we have to scan the complete Clustered Index
SELECT AddressID FROM Person.Address
WHERE StateProvinceID = 79 AND City = 'Bothell'
GO

现在你可以在StateProvinceIDCity,或CityStateProvinceID创建非聚集索引:

复制代码
-- Create a supporting Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Test ON Person.Address(StateProvinceID, City)
GO

-- SQL Server performs a Non-Clustered Index Seek operation in combination with a Seek Predicate
SELECT AddressID FROM Person.Address
WHERE StateProvinceID = 79 AND City = 'Bothell'
GO

-- Change the column ordering
CREATE NONCLUSTERED INDEX idx_Test ON Person.Address(City, StateProvinceID)
WITH (DROP_EXISTING = ON)
GO

-- The column ordering doesn't matter in the Non-Clustered Index
SELECT AddressID FROM Person.Address
WHERE StateProvinceID = 79 AND City = 'Bothell'
GO
复制代码

这里非聚集索引里的列的顺序真的不重要,因为SQL Server在执行计划里直接进行非聚集索引查找操作(在与查找谓语集合里):

范围扫描(Range Scans)

当我们讨论在表上的范围扫描时,这里你检索一组数据,就是另一回事了。假设你执行下列查询:

SELECT AddressID FROM Person.Address
WHERE StateProvinceID BETWEEN 10 AND 12 AND City = 'Bothell'
GO

这次,支持的非聚集索引,你有2个方法:

  • StateProvinceID和City列上的非聚集索引
  • City和StateProvinceID列上的非聚集索引

我们先用第一个方法:

-- Create a supporting Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Test ON Person.Address(StateProvinceID, City)
GO

这个情况下,如你在执行计划里所见,SQL在StateProvinceID列上对查询进行非聚集索引查找操作,对于City列要计算剩余谓语的值:

这真的不是个完美的执行计划,因为你读取了比你请求更多的数据。但有基于StateProvinceID列上的排序作为引导列, City作为随后列,这是唯一可能的行为,如你从下图所见:

现在我们尝试交换下列来创建非聚集索引:City作为引导列,StateProvinceID作为第二列:

复制代码
-- Change the column ordering in the Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Test ON Person.Address(City, StateProvinceID)
WITH (DROP_EXISTING = ON)
GO

-- Non-Clustered Index Seek on StateProvinceID *without* a Residual Predicate on column City
SELECT AddressID FROM Person.Address
WHERE StateProvinceID BETWEEN 10 AND 12 AND City = 'Bothell'
GO
复制代码

当你再次执行你的查询,你会看到SQL Server再次执行了非聚集索引查找操作。但这次对于你的查询,“没有”剩余谓语(Residual Predicate)。

因为你物理上读取的刚好是你逻辑上请求的数据。但这个现在这么可能呢?那就看看下面的图:在非聚集索引里数据是如何排序的:

如你所见,现在的数据按City预先排,在每个City组里,你会有在StateProvinceID列的排序。因此你可以直接获得逻辑请求的数据——不用进一步剩余谓语(Residual Predicate)的值计算就可以返回值。

小结

当你要进行范围扫描时——在复合非聚集索引里列的顺序重要的!在多次交流会上我经常提到:SQL Server里的一切几户都与索引有关,索引本身就会预排序数据!没别的!理解SQL Server是否可以直接查找逻辑请求的数据,你也需要在你的心中想象下如何使如何预排序的,你如何通过有效预排序数据来访问它。

希望这篇文章可以让你更好的理解在非聚集索引里,列排序如何影响查找操作。

感谢关注!

原文地址:https://www.cnblogs.com/lykbk/p/hgfhffhfhfhfhhfhf678786886687.html