主页 / dba / 问题 / 23773
Accepted
peter
Asked: 2012-09-06 18:27:34 +0800 CST

优化器未选择索引联合计划

  • 772

为什么下面的查询很慢？

select count(*) 

from [dbo].[mt_dispatch_link] 
   , [dbo].[_mt_dispatch] [_mt_dispatch]

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

在此处输入图像描述

这需要超过 10 分钟，然后我倾向于在那个时候停止。我的问题更多是关于如何理解查询计划。

查看查询计划我可以看到底部聚簇索引扫描返回了大约 250000 条记录，但成本为 0% 并且被放入临时表中。

顶部索引扫描大约是 25000 条记录。

但 95% 的成本来自嵌套连接。我应该从中得出什么结论？

上面的查询计划显示了两次索引扫描，那是说它在做 25000 + 250000 次索引扫描，还是说它在做 25000 * 250000 次索引扫描？

如果我将查询更改为此（添加FORCESEEK）：

select count(*) 

from [dbo].[mt_dispatch_link] 
   , [dbo].[_mt_dispatch] [_mt_dispatch] 

    WITH (FORCESEEK)

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

我最终得到了一个更好的计划，查询立即运行：

在此处输入图像描述

我在两个表上都运行了更新统计信息。不幸的是没有修好。表设计不是很好，所以我认为 SQL Server 并没有真正理解，因此提出了一个糟糕的查询计划。有关表设计的更多信息，请参见如何优化查询。

为什么查询优化器没有提出最佳计划？

sql-server optimization

1 个回答

  1. Best Answer

    除非指定了or提示，否则优化器并不总是考虑索引联合计划（如第二张图中所示的计划）来解决析取（OR谓词） 。这是基于一些实际考虑的启发式*：FORCESEEKINDEX

    1. 对于一般查询，索引联合通常不是一个好的计划选择。
    2. 可以组合索引的方式数量呈指数增长。

    使用提示会改变优化器搜索可能计划空间的方式。它禁用了一些一般的启发式方法，并追求更以目标为导向的策略。

    优化器通常的主要目标是快速找到一个好的计划。它不会详尽地搜索“最佳”计划（如果确实如此，即使是相对简单的查询也可能需要数年时间才能编译）。

    以多个条件分隔的连接OR长期以来一直存在问题。多年来，优化器增加了一些新技巧，例如将它们转换为等价UNION形式，但可用的转换是有限的，因此很容易卡住。

    就查询计划而言：

    1. DispatchLink 的第一行导致对 Dispatch 表的全面扫描
    2. 扫描结果存储在内部tempdb工作表（表假脱机）中
    3. OR连接根据完整谓词检查工作表中的每一行
    4. 下一行是从 DispatchLink 中获取的，该过程从步骤 3 开始重复

    如果 Dispatch Link 表中有 25,000 行，则假脱机将被完全扫描 25,000 次。这当然是一场灾难（没有索引交集，优化器能做的最好的事情就是在多个线程上运行整个事情）。

    查询计划中的百分比成本只是优化器的估计。它们从不反映实际执行成本，并受优化器模型的影响，通常与在特定硬件上执行计划的“真实”成本几乎没有相似之处。

    成本核算数字是为了提供信息，但不应按字面意思理解。优化器使用的特定模型恰好为世界上大多数系统上的大多数查询生成了非常好的计划——这并不意味着该模型接近任何人的现实，只是它在实践中恰好工作得相当好。

    更改设计，使 (Dispatch, Contract) 对存储在行中而不是跨列重复，这将使整个索引交叉问题消失。具有有用约束和索引的关系设计几乎总能从优化器中获得最大收益。

    * 这可以用未记录的跟踪标志 8726 覆盖

    • 13

相关问题