sql-server – 添加通配符(或顶部)时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%'                         |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+

（编辑：东莞站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!