17370845950

JOIN性能优化核心是被驱动表连接字段必须建索引，否则触发嵌套循环导致I/O暴增；需遵循最左前缀、类型一致、条件移至ON子句等原则，并通过EXPLAIN验证type/key/rows三列。

JOIN字段必须有索引，否则性能断崖式下跌

MySQL执行 JOIN 时，如果连接字段（如 ON t1.id = t2.user_id）没有索引，优化器大概率会走嵌套循环（Nested Loop），对小表驱动大表的场景尤其危险——哪怕小表只有100行，大表10万行，也可能触发100×10万次磁盘I/O。用 EXPLAIN 查看执行计划，若 type 是 ALL 或 index（全表/全索引扫描），基本可以确认缺失关键索引。

实操建议：

• 驱动表（通常是 FROM 后的第一个表）的连接字段不一定非要索引，但被驱动表（JOIN 后的表）的连接字段必须建索引，否则无法利用索引快速定位匹配行
• 复合索引要遵循最左前缀原则：若 ON a.x = b.y AND b.status = 'active'，则 b 表上推荐建 (y, stat

  

  us) 而非单独 y，让索引同时覆盖连接和过滤
• 注意字段类型严格一致：INT 和 BIGINT、VARCHAR(50) 和 VARCHAR(100) 关联时，即使都有索引，也可能因隐式转换导致索引失效

驱动表选择影响索引是否生效

MySQL默认采用“小结果集驱动大结果集”策略，但这个“小”是基于预估行数（rows 列），不是物理大小。如果优化器误判，选错驱动表，会导致本该走索引的被驱动表被迫全表扫描。

实操建议：

• 用 STRAIGHT_JOIN 强制指定驱动顺序，例如 SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.id = t2.t1_id，让 t1 永远为驱动表
• 在 WHERE 条件中给驱动表加强过滤（如 t1.created_at > '2025-01-01'），降低其预估行数，提高优化器选对的概率
• 避免在连接字段上使用函数或表达式：ON YEAR(t2.date) = t1.year 会让 t2.date 索引完全失效

LEFT JOIN 的右表索引失效常见陷阱

LEFT JOIN 中，左表全部保留，右表只匹配存在记录的部分。但很多人忽略：右表若带 WHERE 条件（如 WHERE t2.status = 'paid'），MySQL会把该条件下推到 JOIN 过程中，实际变成类似 INNER JOIN 的语义——此时若右表无索引，就又回到全表扫描。

实操建议：

• 把右表的过滤条件移到 ON 子句里：LEFT JOIN t2 ON t1.id = t2.t1_id AND t2.status = 'paid'，确保索引能参与连接过程
• 确认执行计划中 Extra 字段不含 Using where（针对右表），否则说明条件被下推且未走索引
• 对右表做 IS NULL 判断（查左表有而右表无匹配的记录）时，仍需右表连接字段有索引，否则无法高效定位“无匹配”状态

覆盖索引可减少回表，但JOIN多层时要谨慎

当 SELECT 的所有字段都包含在某个索引中（即覆盖索引），MySQL无需回主键索引查数据行。这对单表查询提升明显，但在 JOIN 场景下，仅对被驱动表有效，且多表关联时容易误判收益。

实操建议：

• 优先保证连接字段索引，再考虑覆盖：比如 t2(t1_id, status, amount) 可覆盖 SELECT t2.status, t2.amount，但若还需 t2.name，就得权衡是否扩大索引宽度
• 联合索引字段顺序很重要：连接字段必须放最左，否则无法用于 ON 匹配；例如 (status, t1_id) 对 ON t1.id = t2.t1_id 完全无效
• 索引不是越多越好：每多一个索引，写入开销增加，且优化器在复杂 JOIN 中可能因索引过多反而选错执行路径

EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.registered_at > '2025-01-01';

查完 EXPLAIN 输出后，重点盯三列：type（是否为 ref 或 eq_ref）、key（是否命中预期索引）、rows（预估扫描行数）。只要其中一列异常，就要回溯索引定义和字段类型一致性。