mysql执行SQL语句时的优化与查询重写

EXPLAIN 是优化慢查询的必要起点，它揭示索引使用、临时表、排序等真实执行细节；盲目改SQL易跑偏方向。

为什么 EXPLAIN 看起来没用，但必须先跑一遍

很多人在慢查询出现后直接改 SQL，跳过 EXPLAIN，结果优化方向完全跑偏。MySQL 的执行计划决定实际走哪个索引、是否临时表、是否排序、是否回表——这些无法靠肉眼判断。EXPLAIN 输出里的 type（如 ALL、index、range）、key、rows、Extra（尤其是 Using filesort 或 Using temporary）才是真实瓶颈信号。

• 对 WHERE 条件字段没索引？type 很可能为 ALL，意味着全表扫描
• key 为空但本应走索引？检查字段类型是否隐式转换（比如 varchar 字段用数字比较）
• rows 远大于结果集数量？说明索引选择性差或统计信息过期，可运行 ANALYZE TABLE
• Extra 出现 Using index condition 是好现象；Using where; Using index 表示覆盖索引；而 Using temporary; Using filesort 基本等于性能红灯

JOIN 顺序不对，STRAIGHT_JOIN 有时比优化器更靠谱

MySQL 5.7+ 的优化器通常能选较优 JOIN 顺序，但在多表关联（尤其 4 张以上）、小表驱动大表逻辑明确、或存在复杂子查询嵌套时，它可能误判驱动表。此时强制顺序反而更快。

• 默认 JOIN 是让优化器决定；STRAIGHT_JOIN 强制按 SQL 中从左到右的顺序连接
• 适用场景：已知某张表过滤后只剩几行（如 WHERE order_status = 'paid' 后仅 10 条），就该让它当驱动表
• 注意：STRAIGHT_JOIN 只影响 JOIN 顺序，不影响单表访问方式（仍依赖索引）
• 副作用：如果数据分布突变（比如某天突然有 10 万条未支付订单），硬编码的顺序会恶化性能，需配合监控

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

把 OR 拆成 UNION ALL 真的有用，但得看条件是否独立

当 WHERE 中含多个 OR 条件且涉及不同字段（如 status = 'draft' OR created_by = 123），MySQL 往往放弃使用索引，退化为全表扫描。拆成 UNION ALL 可让每个分支单独走索引。

• 必须满足：各分支返回列结构一致、无重复逻辑、结果集不需去重（否则用 UNION，但性能损失更大）
• 无效场景：两个条件共用同一字段（如 id = 1 OR id = 2），这种本就能走索引，拆了反而增加开销
• 注意 UNION ALL 不合并结果顺序，如需全局排序，必须在外层加 ORDER BY，且无法利用内层索引排序
• 若分支间有大量重叠数据，UNION ALL 会放大网络/内存传输量

SELECT id, title FROM posts WHERE status = 'published'
UNION ALL
SELECT id, title FROM posts WHERE author_id = 99;

子查询改写为 JOIN 或 LATERAL（MySQL 8.0.14+）更可控

相关子查询（如 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE region = 'CN')）容易触发 N+1 扫描；而派生表（FROM (SELECT ...) AS t）若没被物化或无法下推条件，也可能低效。

• IN / EXISTS 子查询优先转为 JOIN：只要逻辑等价且不引入重复行，JOIN 通常更稳定
• MySQL 8.0.14+ 支持 LATERAL，适合“每行调用一个动态子查询”的场景（如取每个用户的最新订单），避免传统 JOIN 的笛卡尔积膨胀
• 用 JOIN 替代子查询时，务必检查 GROUP BY 或聚合是否被意外消除——加 DISTINCT 或调整关联条件
• 子查询中含 LIMIT 或 ORDER BY？基本无法安全转 JOIN，老老实实用 LATERAL 或应用层分步查

真正棘手的不是语法改写，而是确认语义不变——比如 NULL 值处理、空结果集行为、重复键合并逻辑，这些地方一疏忽，数据就错了。