复合非聚集索引——列顺序重要么？

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

单例查找（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

现在你可以在StateProvinceID和City，或City和StateProvinceID创建非聚集索引：

-- 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.sqlpassion.at/archive/2016/06/27/composite-non-clustered-indexes-does-the-sorting-order-matter