这是基于成本的优化器的决定。

此选择中使用的估计成本不正确，因为它假定不同列中的值之间具有统计独立性。

它类似于Row Goals Gone Rogue中描述的问题，其中偶数和奇数负相关。

它很容易复制。

CREATE TABLE dbo.animal(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    colour varchar(50) NOT NULL,
    species varchar(50) NOT NULL,
    Filler char(10) NULL
);

/*Insert 20 million rows with 1% black and 1% swan but no black swans*/
WITH T
     AS (SELECT TOP 20000000 ROW_NUMBER() OVER (ORDER BY @@SPID) AS RN
         FROM   master..spt_values v1,
                master..spt_values v2,
                master..spt_values v3)
INSERT INTO dbo.animal
            (colour,
             species)
SELECT CASE
         WHEN RN % 100 = 1 THEN 'black'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END,
       CASE
         WHEN RN % 100 = 2 THEN 'swan'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END
FROM   T 

/*Create some indexes*/
CREATE NONCLUSTERED INDEX ix_species ON  dbo.animal(species);
CREATE NONCLUSTERED INDEX ix_colour ON  dbo.animal(colour);

现在试试

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black'
       AND species LIKE 'swan' 

这给出了下面的计划，其成本为0.0563167。

该计划能够在id列上的两个索引的结果之间执行合并连接。（这里有更多关于合并连接算法的细节）。

合并连接要求两个输入都按连接键排序。

非聚集索引分别由(species, id)和排序（如果未显式添加，(colour, id)非唯一非聚集索引总是将行定位器隐式添加到键的末尾）。没有任何通配符的查询正在执行对species = 'swan'and的相等搜索colour ='black'。由于每次查找仅从前导列中检索一个精确值，匹配的行将按顺序排序，id因此该计划是可能的。

查询计划运算符从左到右执行。左操作符从其子节点请求行，而后者又从其子节点请求行（依此类推，直到到达叶节点）。一旦收到 10 行，TOP迭代器将停止向其子代请求更多行。

SQL Server 有关于索引的统计信息，告诉它有 1% 的行匹配每个谓词。它假设这些统计数据是独立的（即，既不正相关也不负相关），因此平均而言，一旦它处理了与第一个谓词匹配的 1,000 行，它将找到与第二个谓词匹配的 10 行并可以退出。（上面的计划实际上显示 987 而不是 1,000，但足够接近）。

事实上，由于谓词是负相关的，实际计划表明需要从每个索引处理所有 200,000 个匹配行，但这在一定程度上得到了缓解，因为零连接行也意味着实际上需要零查找。

与之比较

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

这给出了下面的计划，其成本为0.567943

添加尾随通配符现在已导致索引扫描。尽管对于 2000 万行表的扫描，该计划的成本仍然很低。

添加querytraceon 9130显示更多信息

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'       
OPTION (QUERYTRACEON 9130)  

可以看出，SQL Server 认为它只需要扫描大约 100,000 行，就可以找到 10 个匹配谓词并且TOP可以停止请求行。

同样，这对于独立性假设是有意义的10 * 100 * 100 = 100,000

最后让我们尝试强制执行索引交叉计划

SELECT TOP 10 *
FROM   animal WITH (INDEX(ix_species), INDEX(ix_colour))
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

这为我提供了一个并行计划，估计成本为 3.4625

这里的主要区别是colour like 'black%'谓词现在可以匹配多种不同的颜色。这意味着不再保证该谓词的匹配索引行按id.

例如，索引搜索like 'black%'可能会返回以下行

+------------+----+
|   Colour   | id |
+------------+----+
| black      | 12 |
| black      | 20 |
| black      | 23 |
| black      | 25 |
| blackberry |  1 |
| blackberry | 50 |
+------------+----+

在每种颜色中，id 都是有序的，但不同颜色的 id 可能不是。

因此，SQL Server 不能再执行合并连接索引交集（不添加阻塞排序运算符），而是选择执行散列连接。Hash Join 阻塞了构建输入，因此现在成本反映了需要从构建输入处理所有匹配行的事实，而不是假设它只需要像第一个计划中那样扫描 1,000 行。

然而，探测输入是非阻塞的，它仍然错误地估计在处理了 987 行之后它将能够停止探测。

（关于非阻塞与阻塞迭代器的更多信息在这里）

考虑到额外估计行和散列连接的成本增加，部分聚集索引扫描看起来更便宜。

当然，在实践中，“部分”聚集索引扫描根本不是部分的，它需要遍历整个 2000 万行，而不是比较计划时假设的 100000 行。

增加TOP（或完全删除）的值最终会遇到一个临界点，它估计 CI 扫描需要覆盖的行数会使该计划看起来更昂贵，并且它会恢复为索引交叉计划。对我来说，这两个计划之间的分界点是TOP (89)vs TOP (90)。

对您来说，它可能会有所不同，因为它取决于聚集索引的宽度。

删除TOP并强制 CI 扫描

SELECT *
FROM   animal WITH (INDEX = 1)
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

88.0586在我的机器上为我的示例表计费。

如果 SQL Server 知道动物园没有黑天鹅，并且需要进行全面扫描而不是仅仅读取 100,000 行，则不会选择此计划。

我已经尝试过启用多列统计信息animal(species,colour)并启用animal(colour,species)过滤统计信息，animal (colour) where species = 'swan'但这些都无法说服它不存在黑天鹅，并且TOP 10扫描需要处理超过 100,000 行。

这是由于“包含假设”，SQL Server 本质上假设如果您正在搜索它可能存在的东西。

在 2008+ 上有记录的跟踪标志 4138关闭行目标。这样做的效果是计划的成本没有假设TOP允许子运算符提前终止而不读取所有匹配的行。有了这个跟踪标志，我自然会得到更优化的索引交叉计划。

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 4138)       

该计划现在正确地在两个索引查找中读取完整的 200,000 行成本，但超过了键查找的成本（估计为 2000 与实际 0。这TOP 10将限制为最多 10，但跟踪标志阻止考虑这一点） . 该计划的成本仍然比完整的 CI 扫描便宜得多，因此被选中。

当然，这个计划对于常见的组合可能不是最佳的。比如白天鹅。

复合索引animal (colour, species)或理想情况下animal (species, colour)将允许查询在两种情况下都更加有效。

为了最有效地使用复合索引，LIKE 'swan'还需要将其更改为= 'swan'.

下表显示了所有四种排列的执行计划中显示的搜索谓词和剩余谓词。

+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
|                 WHERE clause                 |       Index       |                         Seek Predicate                         |              Residual Predicate              |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
| colour LIKE 'black%' AND species LIKE 'swan' | ix_colour_species | colour >= 'black' AND colour < 'blacL'                         | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species LIKE 'swan' | ix_species_colour | species >= 'swan' AND species <= 'swan'                        | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species = 'swan'    | ix_colour_species | (colour,species) >= ('black', 'swan')) AND colour < 'blacL'    | colour LIKE 'black%' AND species = 'swan'    |
| colour LIKE 'black%' AND species = 'swan'    | ix_species_colour | species = 'swan' AND (colour >= 'black' and colour <  'blacL') | colour like 'black%'                         |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+