sql-server - order by 会大大减慢查询速度

Question

使用sql server 2014； ((SP1-CU3) (KB3094221) 2015 年 10 月 10 日 x64

我有以下查询

SELECT * FROM dbo.table1 t1

                    LEFT JOIN dbo.table2 t2 ON t2.trade_id = t1.tradeNo
                    LEFT JOIN dbo.table3 t3 ON t3.TradeReportID = t1.tradeNo                                                                                                
                    order by t1.tradeNo

在 t1、t2 和 t3 中分别有 ~70k、35k 和 73k 行。

当我省略 order by此查询在 3 秒内执行，有 73k 行。

正如所写的，查询花了 8.5 分钟来返回 ~50k 行(我已经停止了它)

切换 LEFT JOIN的顺序s 有所作为:

SELECT * FROM dbo.table1 t1

                    LEFT JOIN dbo.table3 t3 ON t3.TradeReportID = t1.tradeNo                                                                                                
                    LEFT JOIN dbo.table2 t2 ON t2.trade_id = t1.tradeNo                     
                    order by t1.tradeNo

这现在在 3 秒内运行。

我在表上没有任何索引。添加索引 t1.tradeNo和 t2.trade_id和 t3.TradeReportID没有效果。
结合 order by 仅使用一个左连接(两种情况)运行查询很快。

我可以交换 LEFT JOIN 的顺序s 但这并不能解释为什么会发生这种情况以及在什么情况下可能会再次发生

预计执行计划是:(慢)


(感叹号细节)


VS

切换左连接的顺序(快速):



我注意到它们明显不同，但我无法解释这些来解释性能差异

更新

似乎增加了 order by子句导致执行计划使用表假脱机(懒惰假脱机)与在快速查询中不使用它。
如果我通过 DBCC RULEOFF ('BuildSpool'); 关闭桌面线轴这“修复”了速度，但根据 this post不推荐这样做，无论如何都不能按查询执行此操作

更新 2

返回的列之一( table3.Text ] 的类型为 varchar(max) ) - 如果将其更改为 nvarchar(512)那么原始(慢)查询现在很快——即执行计划现在决定不使用表假脱机——还要注意，即使类型是 varchar(max)每一行的字段值都是 NULL。这现在可以解决，但我并不聪明

更新 3

执行计划中的警告说明

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[t2].[trade_id],0)) may affect "CardinalityEstimate" in query plan choice, ...

t1.tradeNo是 nvarchar(21) - 另外两个是 varchar(50) - 将后两个更改为与第一个相同后，问题就消失了! (保留 UPDATE 2 中所述的 varchar(max) 不变)

鉴于此问题在 UPDATE 2 或 UPDATE 3 得到纠正时消失，我猜它是查询优化器的组合，该查询优化器使用临时表(表假脱机)用于具有无限大小的列 - 尽管 nvarchar(max) 非常有趣没有数据的列。

Answer 1

您可能最好的解决方法是 确保联接的两侧具有相同的数据类型 .没有必要是varchar和另一个 nvarchar .

这是 DB 中经常出现的一类问题。数据库是 假设错误 关于它即将处理的数据的组成。估计执行计划中显示的成本可能与实际计划中的成本相差甚远。我们都会犯错误，如果 SQL Server 能从它自己中吸取教训，那真的很好，但目前还没有。尽管一次又一次地被立即证明是错误的，它仍将估计 2 秒的返回时间。公平地说，我不知道有任何 DBMS 有机器学习可以做得更好。

您查询速度快的地方

最难的部分是通过对 table3 进行排序来预先完成的。这意味着它可以进行高效的合并连接，这反过来意味着它没有理由对假脱机感到懒惰。

哪里慢

拥有一个将同一事物存储为两种不同类型和数据长度的 ID 不应该是必要的，并且对于 ID 来说永远不是一个好主意。在这种情况下 nvarchar一处varchar在另一个。当这使它无法获得基数估计时，这就是关键缺陷，原因如下:

优化器希望只需要 table3 中的几个唯一行。只有少数选项，如“男”、“女”、“其他”。这就是所谓的“低基数”。所以想象一下，由于某种奇怪的原因，tradeNo 实际上包含性别的 ID。请记住，是您具有情境化的人类技能，谁知道这不太可能。 DB 对此视而不见。所以这是它期望发生的事情:当它第一次遇到“Male”的 ID 时执行查询时，它会懒惰地获取关联的数据(如“Male”一词)并将其放入假脱机中。接下来，因为它已经排序，它只需要更多的雄性，并且简单地重复使用它已经放入线轴的东西。

基本上，它计划在几个大块中从表 1 和 2 中获取数据，并停止一次或两次以从表 3 中获取新的详细信息。实际上，停止并不是偶然的。事实上，它甚至可能在每一行都停止，因为这里有很多不同的 ID。 懒惰的线轴就像上楼一次拿一件小东西 .如果您认为您只需要钱包，那就太好了。如果你搬家就不太好，在这种情况下你会想要一个大盒子(急切的线轴)。

缩小表 3 中字段的大小有所帮助的可能原因是，这意味着它估计在预先进行完整排序时执行惰性假脱机的相对优势较少。与 varchar它不知道有多少数据，只知道可能有多少。需要改组的潜在数据块越大，需要做的体力工作就越多。

你可以做些什么来避免将来

使您的表架构和索引反射(reflect)数据的真实形状。

如果一个ID可以是varchar在一张表中，则不太可能需要 nvarchar 中可用的额外字符。换一张 table 。避免对 ID 进行转换的需要，并尽可能使用整数而不是字符。

问问自己这些表格中是否有任何需要填写 tradeNo
所有行。如果是这样，请使其在该表上不可为空。接下来问是否
这些表中的任何一个的 ID 应该是唯一的，并在
适当的索引。 Unique 是最大基数的定义
所以它不会再犯那个错误了。

使用连接顺序向正确的方向轻推。

您在 SQL 中加入的顺序是向数据库发出的一个信号，表明您希望每个连接的功能有多强大/有多难。 (有时作为人类，您了解更多。例如，如果查询 50 岁的宇航员，您知道过滤宇航员将是第一个应用的过滤器，但可能从搜索 50 岁上类族时的年龄开始。)重的东西应该来第一的。如果它认为它有更好的信息，它会忽略你，但在这种情况下，它依赖于你的知识。

如果一切都失败了

一个可能的修复方法是 INCLUDE TradeReportId 索引中 table3 中需要的所有字段。索引已经帮不上忙的原因是它们可以很容易地确定如何重新排序，但仍然没有物理完成。这是它希望使用惰性假脱机优化的工作，但如果包含数据，它将已经可用，因此无需优化。