select top 10 * from animal where colour like 'black' and species like 'swan'
(是的,不经意的是那些字段是自由文本,但它们都被编入索引).事实证明,我们没有这样的动物,因为查询在大约300毫秒内返回一个空集.如果我使用’=’而不是’喜欢’,它的速度大约是原来的两倍,但我有预感,后者即将为我节省一些打字.
事实证明,头部动物园管理员认为他可能已将某些天鹅输入为“黑色”,因此我相应地修改了查询：
select top 10 * from animal where colour like 'black%' and species like 'swan'
事实证明,这些都没有(事实上除了’黑’之外没有’黑％’动物),但查询现在需要大约30秒才能返回空.
似乎只是“顶部”和“喜欢％”的组合造成了麻烦,因为
select count(*) from animal where colour like 'black%' and species like 'swan'
很快就会返回0,甚至
select * from animal where colour like 'black%' and species like 'swan'
在几分之一秒内返回空.
有没有人知道为什么’top’和’％’会合谋导致如此巨大的性能损失,特别是在空结果集中？
编辑：只是为了澄清,我没有使用任何FreeText索引,我只是意味着这些字段在入口点是自由文本,即在数据库中没有规范化.对不起,混淆,我的措辞不好.
解决方法
此选择中使用的估计成本不正确,因为它假设不同列中的值之间存在统计独立性.
它类似于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列上的两个索引的结果之间执行合并连接. (More details of merge join algorithm here).
合并连接要求两个输入都由连接键排序.
非聚簇索引分别按(种类,id)和(颜色,id)排序(如果未明确添加,则为非唯一非聚簇索引always have the row locator added in to the end of the key implicitly).没有任何通配符的查询执行等式搜索到物种=’天鹅’和颜色=’黑色’.由于每个搜索仅从前导列中检索一个精确值,因此匹配的行将按ID排序,因此该计划是可能的.
查询计划运算符execute from left to right.左操作符从其子节点请求行,子节点又从子节点请求行(依此类推,直到到达叶节点).收到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万行表,但该计划的成本仍然很低.
SELECT TOP 10 *
FROM animal
WHERE colour LIKE 'black%'
AND species LIKE 'swan'
OPTION (QUERYTRACEON 9130)
可以看出,sql Server认为它只需要扫描大约100,然后才能找到与谓词匹配的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
这里的主要区别是像’black％’谓词这样的颜色现在可以匹配多种不同的颜色.这意味着不再保证该谓词的匹配索引行按id的顺序排序.
例如,像’black％’那样的索引搜索可能会返回以下行
+------------+----+ | Colour | id | +------------+----+ | black | 12 | | black | 20 | | black | 23 | | black | 25 | | blackberry | 1 | | blackberry | 50 | +------------+----+
在每种颜色中,ID都是有序的,但不同颜色的ID可能不是.
因此,sql Server无法再执行合并连接索引交集(不添加阻塞排序运算符),而是选择执行散列连接.散列连接阻塞了构建输入,所以现在成本反映了所有匹配的行都需要从构建输入处理而不是假设它只需要扫描1,000,就像在第一个计划中一样.
但探测器输入是非阻塞的,但它仍然错误地估计在处理完987行之后它将能够停止探测.
(Further info on Non-blocking vs. blocking iterators here)
鉴于额外估计行和散列连接的成本增加,部分聚集索引扫描看起来更便宜.
当然,在实践中,“部分”聚集索引扫描根本不是局部的,它需要突破整个2000万行而不是比较计划时假设的10万行.
增加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,则不会选择此计划.
我尝试了动物(种类,颜色)和动物(颜色,种类)的多列统计数据和动物(颜色)的过滤统计数据,其中物种=’天鹅’,但这些都没有帮助说服黑天鹅不存在TOP 10扫描需要处理超过100,000行.
这是由于“包含假设”,sql Server基本上假定如果您正在搜索可能存在的内容.
在2008年有一个documented trace flag 4138关闭了行目标.这样做的结果是计划的成本是在没有假设TOP允许子运算符提前终止而不读取所有匹配行的情况下计算的.有了这个跟踪标志,我自然会得到更优的索引交叉计划.
SELECT TOP 10 *
FROM animal
WHERE colour LIKE 'black%'
AND species LIKE 'swan'
OPTION (QUERYTRACEON 4138)
这个计划现在正确地用于读取两个索引搜索中的全部20万行但是超过成本的关键查找成本(估计为2千对比实际值为0. TOP 10会将此约束为最大值10但是跟踪标记可防止将其用于帐户).该计划的成本仍然比完整的CI扫描便宜得多,因此被选中.
当然,这个计划对于常见的组合可能不是最佳的.如白天鹅.
动物(颜色,物种)或理想动物(物种,颜色)的综合指数将使查询对两种情景都更有效.
为了最有效地利用复合索引,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%' |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
