JOIN 查询优化策略
JOIN 的性能瓶颈
JOIN 是关系型数据库最精华的功能，但也是性能问题的重灾区。一个缓慢的 JOIN 查询可能会消耗大量的 CPU 和内存，甚至让数据库”挂”住。
JOIN 慢的核心原因：
1. 大表驱动小表没有建好索引时，会全表扫描多次
2. 中间结果集膨胀：JOIN 产生的笛卡尔积可能非常巨大
3. 临时表：复杂 JOIN 需要在内存或磁盘上创建临时表
策略一：小表驱动大表
MySQL 使用 Nested Loop Join 算法——从驱动表中取一行，再到被驱动表中匹配。
-- 假设 user 表 100 行，order 表 1000 万行
-- 推荐：小表 user 驱动大表 order
SELECT * FROM user u JOIN order o ON u.id = o.user_id;
-- user 取 100 行，每行到 order 的 user_id 索引上查找 1 次 = 100 次索引查找
-- 不推荐：大表 order 驱动小表 user（如果 SQL 写反）
SELECT * FROM order o JOIN user u ON o.user_id = u.id;
-- order 取 1000 万行，每行到 user 的 id 索引上查找 1 次 = 1000 万次索引查找
注意：MySQL 优化器会自动选择小表作为驱动表，但了解这个原理有助于写出更好的 SQL。
策略二：确保 JOIN 列有索引
被驱动表的 JOIN 列必须有索引，否则每次匹配都要全表扫描。
-- 被驱动表 order 的 user_id 列必须加索引
CREATE INDEX idx_user_id ON `order`(user_id);
为什么：Nested Loop Join 每次从驱动表取一行，去被驱动表匹配时，理想情况是走索引（ref 级别），而不是全表扫描（ALL 级别）。
-- 用 EXPLAIN 检查
EXPLAIN SELECT * FROM user u JOIN `order` o ON u.id = o.user_id;
-- 结果中 order 表的 type 列应为 ref，表示走索引
策略三：避免笛卡尔积
不带 ON 条件或 ON 条件不充分的 JOIN 会产生笛卡尔积，结果行数 = 表 A 行数 × 表 B 行数。
-- 危险：缺少足够的 JOIN 条件
SELECT * FROM user u JOIN `order` o; -- 100 × 1000万 = 10亿条
-- 安全：明确 JOIN 条件
SELECT * FROM user u JOIN `order` o ON u.id = o.user_id;
策略四：合理选择 JOIN 类型
| JOIN 类型 | 效果 | 性能 |
|---|---|---|
| INNER JOIN | 两表交集 | 最好（可优化） |
| LEFT JOIN | 左表全部 + 右表匹配 | 一般 |
| RIGHT JOIN | 右表全部 + 左表匹配 | 一般 |
| FULL OUTER JOIN | 两表并集 | 最差 |
- 能用 INNER JOIN 就不应使用 LEFT JOIN
- LEFT JOIN 的右表如果查询字段都在 ON 条件中，驱动表是左表，查询全部左表行
策略五：使用复合索引
当 JOIN 条件 + WHERE 条件涉及多个列时，建立复合索引。
-- 查询：用户及其最近订单
SELECT u.*, o.*
FROM user u
JOIN `order` o ON u.id = o.user_id
WHERE o.create_time > '2024-01-01'
ORDER BY o.create_time DESC;
-- 在 order 表上建复合索引（user_id 做 JOIN，create_time 做排序）
CREATE INDEX idx_user_time ON `order`(user_id, create_time);
复合索引让 JOIN 和排序都能走索引，避免文件排序。
策略六：拆分复杂 JOIN
当 JOIN 超过 3 张表，或者大表之间 JOIN 时，考虑拆分为多次简单查询。
// 原：3 表 JOIN
SELECT u.name, o.order_no, p.amount
FROM user u JOIN `order` o ON u.id = o.user_id
JOIN payment p ON o.id = p.order_id
WHERE u.id = 12345;
// 改：拆为 3 次简单查询 + 应用层组装
Order order = query("SELECT * FROM `order` WHERE user_id = ?", 12345);
Payment payment = query("SELECT * FROM payment WHERE order_id = ?", order.getId());
拆分带来几个好处：
1. 每步查询可以独立优化
2. 结果集可控，不会产生中间爆炸
3. 更容易利用缓存
策略七：使用 STRAIGHT_JOIN 控制驱动表
在极端情况下，MySQL 优化器选错了驱动表，可以用 STRAIGHT_JOIN 强制指定。
-- 强制用小表 user 驱动大表 order
SELECT * FROM user u STRAIGHT_JOIN `order` o ON u.id = o.user_id;
这是一个”核武器”，仅在确认优化器选择错误时使用。
总结
JOIN 优化六步法：
- 确保小表驱动大表（优化器通常自己做，但理解原理有助于排查问题）
- 被驱动表 JOIN 列建索引（最简单的回报）
- 避免不必要的 LEFT JOIN/FULL JOIN
- 复合索引覆盖 JOIN + WHERE + ORDER BY
- 3 张表以上的 JOIN 考虑拆分
- 用 EXPLAIN 验证每一步优化效果
最核心的一条：JOIN 列的索引比什么优化都重要。
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容