主页 / dba / 问题 / 115793
Accepted
Alex Shelemin
Asked: 2015-09-23 08:05:35 +0800 CST

为什么在我添加连接提示时 SQL Server 行估计会发生变化？

  • 772

我有一个查询，它连接了几个表并且执行得非常糟糕 - 行估计偏离了（1000 次）并且选择了嵌套循环连接，导致多个表扫描。查询的形式相当简单，看起来像这样：

SELECT t1.id
FROM t1
INNER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id
LEFT OUTER JOIN t4 ON t3.t4_id = t4.id 
WHERE t4.id = some_GUID

研究查询时，我注意到当我提示它对其中一个连接使用 Merge 连接时，它的运行速度快了许多倍。我可以理解这一点 - 合并连接对于连接的数据是一个更好的选择，但 SQL Server 只是没有正确选择嵌套循环。

我不完全理解的是为什么这个连接提示会改变所有计划运营商的所有估计？通过阅读不同的文章和书籍，我假设基数估计是在构建计划之前执行的，因此使用提示不会改变估计，而是明确告诉 SQL Server 使用特定的物理连接实现。

然而，我看到的是 Merge 提示使所有估计变得非常完美。为什么会发生这种情况，是否有任何通用技术可以使查询优化器在没有提示的情况下做出更好的估计——考虑到统计数据显然允许这样做？

UPD：匿名执行计划可以在这里找到： https ://www.dropbox.com/s/hchfuru35qqj89s/merge_join.sqlplan?dl=0 https://www.dropbox.com/s/38sjtv0t7vjjfdp/no_hints_join.sqlplan?dl =0

我使用 TF 3604、9292 和 9204 检查了两个查询使用的统计信息，它们是相同的。但是，扫描/查找的索引在查询之间有所不同。

除此之外，我尝试运行查询OPTION (FORCE ORDER)- 它比使用合并连接运行得更快，为每个连接选择 HASH MATCH。

sql-server t-sql

2 个回答

  1. Best Answer

    通过阅读不同的文章和书籍，我假设基数估计是在制定计划之前执行的。

    不完全是。推导出初始基数估计（在简化和其他工作之后），这会影响优化器选择的初始连接顺序。

    然而，后续探索（在基于成本的优化期间）可以并且经常会导致计算新的基数估计。这些后来的 CE 可能或多或少是“准确的”。如果低估结果，优化器可能会选择一个看起来更便宜但实际上运行时间更长的计划。

    通常，不能保证语义相同的子树的基数估计会产生相同的结果。毕竟，这是一个统计过程，并且某些操作比其他操作具有更深入的 CE 支持。

    在您的情况下，似乎还有另一个因素 - 优化器引入（或四处移动）一个顶部，它在其下方的子树上设置一个行目标：

    计划片段

    如果您要启用跟踪标志 4138（在 2008 R2 或更高版本上），您可能会发现估计值更符合预期，甚至优化器可能不再选择嵌套循环。

    然而，我看到的是 Merge 提示使所有估计变得非常完美。

    这里有运气成分。人们倾向于按照他们期望的物理执行顺序来编写查询，或者至少是连接。使用连接提示带有隐含的FORCE ORDER，从而固定连接顺序以匹配文本形式，并关闭许多可能导致基数重新估计的优化器探索规则。

    除此之外，我尝试运行查询OPTION (FORCE ORDER)- 它比使用合并连接运行得更快，为每个连接选择 HASH MATCH。

    这与提示连接相同，但不限制物理连接运算符的选择。同样，如果您碰巧以合乎逻辑的方式编写了查询连接顺序，那么您很可能会得到一个合理的计划。当然，您会以这种方式错过优化器的大部分功能，在更一般的情况下可能不会产生最佳结果。

    您可能不想FORCE ORDER经常使用，因为它是一个非常强大的提示（指令），比简单地强制连接顺序具有更广泛的影响；例如，它可以防止优化器四处移动聚合并引入部分聚合。我强烈建议不要使用此提示，除非在非常特殊的情况下，并且由真正的专业调谐器进行。

    详细分析需要比我现在更多的时间，并且需要访问数据库的仅统计副本。

    • 22

  2. 哪里否定了左边
    为什么要让优化器变得困难？
    在 3 个或更多连接时，优化器将趋向于防御并进入循环连接，因为它可以保护内存连接中
    的一个或条件它也将倾向于进入循环连接 - 我是否有确凿证据表明它每次都会发生 - 不 -仍然是现实
    有多个连接时，您可以将条件从 where 拉入连接 

    SELECT t1.id
  FROM t1
  JOIN t2 
        ON t1.id = t2.t1_id
  JOIN t3 
        ON t2.id = t3.t2_id
  JOIN t4 
        ON t3.t4_id = t4.id 
       AND t4.id = some_GUID

    甚至更好 - 我敢打赌这会满足或击败你的提示或力量 

    SELECT t1.id
  FROM t1
  JOIN t2 
        ON t1.id = t2.t1_id
  JOIN t3 
        ON t2.id = t3.t2_id
       AND t3.t4_id = some_GUID

    提示的问题在于它们针对特定状态的数据。编写一个干净的查询，让优化器完成它的工作。有时它只需要更多的统计数据来做正确的事情，但随后它就会锁定。

    为什么不同的估计。一个不同的计划。从为优化器提供战斗机会的查询开始。

    • -10

相关问题