MySQL中LEFT JOIN比INNER JOIN快的执行计划差异原因探究

这事儿核心在于数据库优化器对两种连接类型的逻辑优先级判断不一样，再加上你的过滤条件全集中在表X上，刚好踩中了优化器的决策差异点，咱们一步步理清楚：

1. LEFT JOIN 快的原因 #

当你用LEFT JOIN时，优化器明确知道：必须先从表X里筛选出符合所有WHERE条件的行，再去关联表Y——因为左连接要保留X的所有匹配行，哪怕Y里没有对应记录。

而你创建的复合索引(X.a, X.b, X.c, X.d)刚好完美匹配WHERE里的所有过滤条件：

• 先按X.a IN (1,2,3)做范围筛选
• 接着匹配X.b IS NULL（NULL在索引里是可准确定位的）
• 再按X.c IN (4,5,6)缩小结果范围
• 最后用X.d IN (7,8,9)做最终过滤

整个过程直接走Index Scan (range)，从复合索引里快速捞出符合条件的少量行（多条件过滤后，百万级表剩下的行数应该很少），然后再和只有少量记录的Y表做关联，自然速度飞快。

2. INNER JOIN 慢的原因 #

INNER JOIN的逻辑是只保留两边都匹配的行，这时候优化器有两种可选的执行路径：

• 路径A：先筛选X的符合条件的行，再关联Y（和LEFT JOIN逻辑一致）
• 路径B：先扫Y表，找出Y.e IN (7,8,9)的行（因为X.d要等于Y.e，且X.d在(7,8,9)范围内），再用这些Y.e的值去X里找匹配且符合其他过滤条件的行

问题就出在优化器错误地选择了路径B，或者对路径A的成本估算不准：

• 如果Y表中Y.e IN (7,8,9)的行数很少，优化器可能觉得先扫Y再关联X更高效，但它忽略了X的其他过滤条件（a、b、c）会大幅缩小结果集——或者它对X的复合索引利用率估算错误，误以为全表扫描比走索引更快。
• 另一种可能是，优化器认为INNER JOIN的关联条件X.d=Y.e优先级高于X的过滤条件，导致它先尝试用Y的e值去X里查找，而不是先应用X的所有过滤条件。这时候单独的X.d索引效率远不如复合索引，最终就触发了全表扫描。

3. 让INNER JOIN变快的小技巧 #

如果想让INNER JOIN也用上高效的索引扫描，可以试试这两种方法：

• 强制优化器先筛选X的条件：用子查询先捞出X的符合条件的行，再和Y关联，示例SQL如下：

  SELECT * 
  FROM (
      SELECT * FROM X 
      WHERE X.a IN (1,2,3) AND X.b IS NULL AND X.c IN (4,5,6) AND X.d IN (7,8,9)
  ) AS filtered_X
  INNER JOIN Y ON filtered_X.d = Y.e
  

• 更新数据库统计信息：如果统计信息过时，优化器可能对X表过滤后的行数估算错误，导致选错执行计划。更新统计信息后再查看执行计划的变化。

内容的提问来源于stack exchange，提问作者benjamin.d