一句话总结
优先使用JOIN替代子查询，MySQL对子查询优化有限。IN子查询改写为JOIN可提升数倍性能；EXISTS适合外表小内表大的场景；避免相关子查询，确保子查询结果集尽量小。
初级理解
为什么子查询慢？MySQL对子查询的优化有限（尤其旧版本），相关子查询会导致外层每行都执行一次子查询。
-- 差：相关子查询，外层每行都执行一次子查询
SELECT * FROM orders o
WHERE o.amount > (SELECT AVG(amount) FROM orders WHERE user_id = o.user_id);
-- 好：改写为JOIN，只执行一次子查询
SELECT o.* FROM orders o
JOIN (SELECT user_id, AVG(amount) avg_amt FROM orders GROUP BY user_id) t
ON o.user_id = t.user_id WHERE o.amount > t.avg_amt;
一句话总结：能用JOIN就不用子查询，能用EXISTS就不用IN（大表对小表）。
中级深入
IN子查询改写为JOIN：
-- 差
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 好
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;
EXISTS vs IN：
• IN：适合子查询结果集小的场景，先执行子查询，再用结果过滤外层
• EXISTS：适合外表小、内表大的场景，利用内表索引快速返回
-- EXISTS写法（外表小内表大时更快）
SELECT * FROM users u WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
注意：MySQL 8.0+ 对子查询优化有了很大提升，但改写为JOIN仍然是最稳妥的方案。
高级拓展
派生表物化：MySQL 5.6+ 支持将子查询结果物化为临时表并加索引，但仍不如显式JOIN可控。
子查询结果集控制：在子查询内部先过滤、聚合，再与外层JOIN，确保结果集尽量小。
-- 优化：先过滤再聚合
SELECT * FROM orders o
WHERE o.amount > (
SELECT AVG(amount) FROM orders
WHERE create_time > '2024-01-01' -- 先过滤，缩小范围
GROUP BY user_id
HAVING user_id = o.user_id
);
面试加分项：能说出IN/EXISTS的区别、相关子查询的危害、派生表物化机制，说明你对MySQL查询优化有深入理解。
实战场景
场景：用户订单统计查询优化
-- 问题：查询下过单的用户及其最近订单
-- 优化前：IN子查询
SELECT * FROM users WHERE id IN (
SELECT user_id FROM orders WHERE status = 'PAID'
);
-- 优化后：JOIN
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'PAID';
场景： EXISTS vs IN 选择
-- 场景1：子查询结果集小（< 外表10%）→ 用IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM vip_orders);
-- 场景2：子查询结果集大 → 用EXISTS
SELECT * FROM users u WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);
面试模拟
Q：IN和EXISTS的区别是什么？什么时候用哪个？
A：IN先执行子查询，将结果集作为临时表，再与外层匹配；EXISTS对外层每行执行一次子查询，利用内表索引快速判断。子查询结果集小用IN，外表小内表大用EXISTS。
Q：为什么MySQL子查询性能差？
A：主要原因：1. 相关子查询会导致外层每行都执行一次子查询；2. 旧版本MySQL对子查询优化不足，可能生成不好的执行计划；3. 子查询结果集无法利用索引。建议改写为JOIN。