mysql - 澄清创建临时表的连接顺序

Question

我在 mysql 中有一个大型查询，涉及将多个表连接在一起。它太慢了，所以我已经完成了“解释”并看到它正在创建一个临时表，我怀疑它占用了大部分执行时间。我找到了一些相关资料:

1. The mysql docs描述创建临时表的条件可能。 (“服务器在诸如此类的条件下创建临时表...”[强调])
2. 这个相关的 SO 问题 Using index, using temporary, using filesort - how to fix this? ，它提供了指向文档的链接并将其应用于特定情况。
3. 这个相关的 SO 问题 Order of join conditions important?讨论连接评估的顺序。

我的查询似乎不满足文档 #1 中列出的任何条件，按照我编写连接的顺序。但是，通过实验，我发现如果我删除我的 order by 子句，没有创建临时表。这让我从文档中看到了这条规则:

Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

这与上面 #2 示例中的规则相同，但在 #2 中，OP 在 order by 子句中明确包含来自多个表的列，因此这至少在表面上有所不同.

此外，当我查看 explain 的输出时，我首先列出的表似乎并没有被优化器首先使用。例如放下一个伪查询:

select * from A 
join B on A.c1=B.c1
join C on A.c2=C.c2
where A.c3='value'
order by A.c4

我会说，我的 order by 子句根据我编写查询的顺序只使用“连接队列中的第一个表”中的列。 另一方面，explain 的输出表明它首先考虑表 B，然后是 A。

下面是问题:

1. 上面引用的临时表使用规则是指我编写表的顺序还是软件选择评估它们的顺序？
2. 如果这是我编写它们的顺序，这是否意味着联接的顺序会影响性能？ (似乎与上面#3 的说法相矛盾。)
3. 如果这是软件选择评估它们的顺序，是否有任何方法可以强制或欺骗它选择不使用该表的顺序？

Answer 1

1. 它指的是优化器评估它们的顺序(加入队列)。优化器甚至可能不知道您的 sql 语句中表的顺序。

2. 不，它与#3 中的内容并不矛盾，因为 answer明确地写道(重点是我的):

has no effect on the result

结果和性能是两个不同的东西。实际上，对答案有一个赞成的评论说

But it might affect the query plan (=> performance)

1. 您可以使用 straight_join 告诉优化器首先处理哪个表:

   STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

但是，您需要小心，因为您束缚了优化者的手脚。参见 this SO关于讨论 straight_join 优缺点的主题。

记录数、条件、索引——它们都在优化器决定表的处理顺序中发挥作用。没有 Elixir ，您需要尝试一下，也许您可​​以欺骗优化器来更改表​​的顺序。