添加通配符后，SQL Server查询的速度显着下降（或顶部）

select top 10 * 
from animal 
where colour like 'black'  
and species like 'swan'

select top 10 * 
from animal  
where colour like 'black%' 
and species like 'swan'

select count(*) 
from animal  
where colour like 'black%' 
and species like 'swan'

select * 
from animal 
where colour like 'black%' 
and species like 'swan'

#1 楼

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

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

它类似于行目标消失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'和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不能再执行合并联接索引交集（没有添加阻塞排序运算符），而是选择执行哈希联接。哈希联接阻塞了构建输入，因此现在的成本反映了以下事实：所有匹配的行都需要从构建输入中进行处理，而不是像在第一个计划中那样假设它只需要扫描1,000。

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

（有关此处的非阻塞与阻塞迭代器的更多信息）

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

在实践中，“部分”聚簇索引扫描当然不是部分所有这些都需要遍历整个2000万行，而不是比较计划时假设的10万行。

增加TOP的值（或完全删除它）最终会遇到一个临界点，即它估计CI扫描将需要覆盖的行数使该计划看起来更昂贵，并且恢复为索引相交计划。对我来说，这两个计划的临界点是TOP (89)与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行，但是超过了关键查找的开销（估计为2,000，而不是实际的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%'                         |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+

#2 楼

引起这种兴趣的是，我进行了一些搜索并发现了这个问题。
TOP如何（以及为什么）影响执行计划？

基本上，使用TOP会改变操作员遵循该计划的成本（以不平凡的方式），这也会导致总体计划发生变化（如果您包括具有和不具有TOP 10的ExecPlans，那将是一个很好的选择），这几乎改变了查询的总体执行。

希望这对您有所帮助。

例如，我在数据库上进行了尝试，并且：
-当没有调用top时，使用了并行性
-对于TOP，不使用了并行性

因此，再次展示您的执行计划将提供更多信息。

祝您有美好的一天

#3 楼

我相信这可能是由于MSSQL 2005的基本性质以及查询优化器决定哪种执行计划最有效的方式。

如果使用SQL变量，它应该“欺骗”查询优化器使用哈希匹配而不是嵌套循环，这将导致更高程度的并行性。

尝试：

DECLARE @topn INT = 10
SELECT TOP (@topn) *
FROM    animal
WHERE   colour LIKE 'black%' 
AND species LIKE 'swan'