mysql数据库的表连接类型与查询效率优化

INNER JOIN 可自由选择小表驱动并利用索引优化，LEFT JOIN 左表固定驱动且右表缺索引易致全表扫描；连接字段需类型一致、均有索引，避免函数操作；多表JOIN应优先过滤、ON中写强条件； EXISTS 比 LEFT JOIN + IS NULL 更高效；GROUP BY/ORDER BY 需覆盖索引；隐式类型转换会使索引失效。

INNER JOIN 和 LEFT JOIN 的执行差异直接影响查询速度

MySQL 在执行 INNER JOIN 时可自由选择驱动表（即先扫描哪张表），优化器通常会选小表做驱动，配合索引快速过滤；而 LEFT JOIN 的左表固定为驱动表，右表必须全量匹配，若右表缺少关联字段索引，就会触发全表扫描 —— 这是慢查询最常见诱因之一。

• 检查执行计划：用 EXPLAIN SELECT ... 看 type 是否为 ref 或 range，避免出现 ALL
• 连接字段必须有索引：不仅左表的 ON 字段要索引，右表对应字段也得有，且类型、字符集、是否允许 NULL 都需一致
• 避免在 ON 条件里对字段做函数操作，比如 ON YEAR(t1.create_time) = YEAR(t2.time) 会让索引失效

JOIN 多张表时顺序和条件位置决定性能上限

MySQL 5.7+ 默认使用 BNL（Block Nested-Loop）算法，但表越多、中间结果集越大，内存缓冲越容易溢出到磁盘，性能断崖式下降。真正关键的是：把能最快过滤数据的表放在前面，并把强过滤条件尽量写进 ON 而非 WHERE。

• ON 是连接时生效，能减少临时连接结果集大小；WHERE 是连接完成后再过滤，可能已生成百万行中间数据
• 三表连接如 t1 JOIN t2 ON ... JOIN t3 ON ...，优先让 t1 和 t2 先产出最小结果集，再连 t3；可通过 STRAIGHT_JOIN 强制顺序（慎用）
• 如果某张表只是用来取少量字段（如字典表），考虑用 (SELECT ... FROM dict WHERE id = t1.type_id LIMIT 1) 替代 JOIN，避免扩大结果集

用 EXISTS 替代 LEFT JOIN + IS NULL 判断空关联

想查“在 A 表中存在、但在 B 表中无匹配记录”的数据时，很多人写 LEFT JOIN ... WHERE b.id IS NULL，这会导致 MySQL 先做全连接再过滤，效率极低。改用 EXISTS 可让优化器提前终止搜索。

SELECT a.* FROM user a
WHERE NOT EXISTS (
  SELECT 1 FROM order b WHERE b.user_id = a.id
);

• NOT EXISTS 在找到第一个匹配就停止，适合“是否存在”类逻辑
• 确保子查询中的关联字段（如 b.user_id）有索引，否则仍是全表扫
• 不要写 SELECT * 在子查询里，只用 SELECT 1 即可，语义清晰且不增加解析开销

JOIN 查询中 GROUP BY 和 ORDER BY 容易引发临时表和文件排序

当 JOIN 后跟 GROUP BY 或 ORDER BY，MySQL 常常需要创建内部临时表并排序，尤其在没命中索引时会用 Using filesort 或 Using temporary —— 这两个提示几乎等于性能红灯。

• 复合索引要覆盖所有 GROUP BY 字段，且顺序与语句中一致；如 GROUP BY a, b，索引应建为 (a, b)，而非 (b, a)
• 如果 ORDER BY 字段来自关联表（如 ORDER BY t2.updated_at），且该字段不在驱动表上，基本无法避免文件排序
• 考虑在应用层分页：先用主键范围查询（如 WHERE id > ? LIMIT 20），再按需 JOIN，比直接 LIMIT + JOIN 更可控

实际调优时最容易被忽略的，是连接字段的隐式类型转换 —— 比如 user.id 是 BIGINT，而 order.user_id 是 VARCHAR，MySQL 会把整列转成数字比较，索引完全失效。这类问题不会报错，只会默默变慢。