何时在 MySQL 中使用 STRAIGHT_JOIN

如果没有充分的理由，我不建议使用 STRAIGHT_JOIN。我自己的经验是 MySQL 查询优化器比我想要的更频繁地选择一个糟糕的查询计划，但不够频繁，以至于您通常应该绕过它，如果您总是使用 STRAIGHT_JOIN，您会这样做。

我的建议是将所有查询保留为常规 JOIN。如果您发现一个查询正在使用次优查询计划，我建议您首先尝试重写或重新构造查询，看看优化器是否会选择更好的查询计划。此外，至少对于 innodb，请确保不仅仅是您的索引统计信息已过时 (ANALYZE TABLE)。这可能会导致优化器选择一个糟糕的查询计划。优化器提示通常应该是您最后的手段。

不使用查询提示的另一个原因是您的数据分布可能会随着时间的推移而变化，或者您的索引选择性可能会随着表的增长而变化，等等。您现在最佳的查询提示可能会随着时间的推移变得次佳。但是由于您现在已经过时的提示，优化器将无法调整查询计划。如果您允许优化器做出决定，您将保持更灵活。

---

这是最近在工作中出现的一个场景。

考虑三个表，A、B、C。

A 有 3,000 行； B 有 300,000,000 行； C 有 2,000 行。

外键定义：B(a_id), B(c_id)。

假设您有一个如下所示的查询：

select a.id, c.id
from a
join b on b.a_id = a.id
join c on c.id = b.c_id

根据我的经验，在这种情况下，MySQL 可能会选择 C -> B -> A。 C小于A，B很大，它们都是等值的。

问题是 MySQL 不一定会考虑（C.id 和 B.c_id）与（A.id 和 B.a_id）之间的交集的大小。如果 B 和 C 之间的连接返回的行数与 B 一样多，那么这是一个非常糟糕的选择；如果从 A 开始将 B 过滤到与 A 一样多的行，那么这将是一个更好的选择。 straight_join 可用于强制执行此命令，如下所示：

select a.id, c.id
from a
straight_join b on b.a_id = a.id
join c on c.id = b.c_id

现在必须在 b 之前加入 a。

通常，您希望以最小化结果集中行数的顺序进行连接。因此，从一个小表开始并加入使得结果连接也很小，是理想的。如果从一张小桌子开始，然后将它连接到一张更大的桌子上，结果就和大桌子一样大，事情就会变成梨形。

不过，这取决于统计数据。如果数据分布发生变化，计算可能会发生变化。它还取决于连接机制的实现细节。

我见过的 MySQL 最糟糕的情况是，除了需要 straight_join 或积极的索引提示之外，所有查询都是以严格的排序顺序对大量数据进行分页的查询，并带有轻度过滤。 MySQL 强烈倾向于对任何过滤器和连接使用索引而不是排序；这是有道理的，因为大多数人不是试图对整个数据库进行排序，而是对查询做出响应的行的有限子集，并且对有限子集进行排序比过滤整个表要快得多，无论它是排序的还是不是。在这种情况下，将直接连接放在具有我想要对固定事物进行排序的索引列的表之后。

---

从 MySQL JOIN reference：

“STRAIGHT_JOIN 类似于 JOIN，除了左表总是在右表之前读取。这可用于连接优化器以错误顺序放置表的那些（少数）情况。”

---

MySQL 不一定擅长在复杂查询中选择连接顺序。通过将复杂查询指定为 straight_join，查询会按照指定的顺序执行连接。通过首先将表放置为最小公分母并指定 Straight_join，您可以提高查询性能。

---

STRAIGHT_JOIN，使用该子句，可以控制JOIN顺序：外循环扫描哪张表，内循环扫描哪一张。

---

我会告诉你为什么我必须使用 STRAIGHT_JOIN ：

我在查询时遇到了性能问题。

简化查询，查询一下子变得更有效率了

试图找出导致问题的具体部分，我就是做不到。 （2 个左连接在一起很慢，每个都独立快）

然后我使用慢速和快速查询执行 EXPLAIN（添加左连接之一）

令人惊讶的是，MySQL 完全改变了 2 个查询之间的 JOIN 顺序。

因此，我强制其中一个连接为 straight_join 以强制首先读取前一个连接。这阻止了 MySQL 更改执行顺序并像魅力一样工作！

---

在我短暂的经验中，STRAIGHT_JOIN 将我的查询从 30 秒减少到 100 毫秒的情况之一是执行计划中的第一个表不是按列排序的表

-- table sales (45000000) rows
-- table stores (3) rows
SELECT whatever
FROM 
    sales 
    INNER JOIN stores ON sales.storeId = stores.id
ORDER BY sales.date, sales.id 
LIMIT 50;
-- there is an index on (date, id)

如果优化器选择点击 stores first，它将导致 Using index; Using temporary; Using filesort，因为

如果 ORDER BY 或 GROUP BY 包含来自连接队列中第一个表以外的表的列，则会创建一个临时表。

source

这里优化器需要一点帮助，告诉他先点击 sales 使用

sales STRAIGHT_JOIN stores

---

如果您的查询以 ORDER BY... LIMIT... 结尾，则可能最好重新编写查询以诱使优化器在 JOIN 之前执行 LIMIT。

（此答案不仅适用于关于 STRAIGHT_JOIN 的原始问题，也不适用于 STRAIGHT_JOIN 的所有情况。）

从 example by @Accountantم 开始，这在大多数情况下应该运行得更快。 （并且它避免了需要提示。）

SELECT  whatever
    FROM  ( SELECT id FROM sales
                ORDER BY  date, id
                LIMIT  50
          ) AS x
    JOIN  sales   ON sales.id = x.id
    JOIN  stores  ON sales.storeId = stores.id
    ORDER BY  sales.date, sales.id;

笔记：

首先，获取 50 个 ID。使用 INDEX(date, id) 会特别快。

然后加入到销售中让您只获得 50 个“随便什么”，而无需将它们拖到临时表中。

因为根据定义，子查询是无序的，所以 ORDER BY 必须在外部查询中重复。 （优化器可能会找到一种方法来避免实际执行另一种排序。）

是的，它更混乱。但它通常更快。

我反对使用命中，因为“即使今天更快，明天也可能无法更快。”

---

我知道它有点旧，但这是一个场景，我一直在做批处理脚本来填充某个表。在某些时候，查询运行得很慢。特定记录上的连接顺序似乎不正确：

以正确的顺序

https://i.stack.imgur.com/oIOsX.png

将 id 增加 1 会打乱顺序。注意“额外”字段

https://i.stack.imgur.com/Vfpvv.png

使用 straight_join 解决了这个问题

https://i.stack.imgur.com/BdIaU.png

不正确的顺序运行大约 65 秒，而使用 straight_join 以毫秒为单位运行

---

--use 120s, 18 million data
    explain SELECT DISTINCT d.taid
    FROM tvassist_recommend_list_everyday_diverse d, tvassist_taid_all t
    WHERE d.taid = t.taid
      AND t.client_version >= '21004007'
      AND t.utdid IS NOT NULL
      AND d.recommend_day = '20170403'
    LIMIT 0, 10000

--use 3.6s repalce by straight join
 explain SELECT DISTINCT d.taid
    FROM tvassist_recommend_list_everyday_diverse d
    STRAIGHT_JOIN 
      tvassist_taid_all t on d.taid = t.taid 
    WHERE 
     t.client_version >= '21004007'
       AND d.recommend_day = '20170403'

      AND t.utdid IS NOT NULL  
    LIMIT 0, 10000

---