mysql的查询优化器（Query Optimizer）如何工作

mysql查询优化器负责将select语句转换为代价最小的执行路径，其“代价”是基于I/O、内存、行扫描量等估算的抽象成本，而非实际耗时；它依序处理语法树、条件推导、JOIN重排、访问方式选择及临时表/排序决策，依赖统计信息，可能因函数使用、索引顺序不匹配或数据倾斜而误判，可通过FORCE INDEX、避免字段计算、游标分页和ANALYZE table干预。

MySQL 查询优化器到底在做什么

它不执行 SQL，也不保证返回结果正确性——它只负责把一条 SELECT 语句“翻译”成代价最小的执行路径。这个“代价”不是时间，而是 MySQL 内部估算的 I/O 次数、内存使用、行扫描量等抽象成本。

优化器怎么选执行计划：从语法树到可执行路径

收到查询后，优化器按固定顺序处理：

• 先做 WHERE 条件解析和等值传播（比如 a = b AND b = 5 会推导出 a = 5）
• 再尝试将 JOIN 顺序重排，对小表优先驱动（但受 STRaiGHT_JOIN 或 JOIN_ORDER hint 干扰）
• 接着为每个表选择访问方式：system → const → eq_ref → ref → range → index → ALL（越靠前越快）
• 最后决定是否用临时表（using temporary）或文件排序（Using filesort）

注意：这些步骤都基于统计信息（INformatION_SCHEMA.STATISTICS 和 ANALYZE TABLE 更新的数据），如果统计过期，优化器可能选错索引。

为什么 EXPLAIN 显示用了索引，但实际还是慢

常见原因不是优化器错了，而是它“算错了”：

• WHERE 中用了函数，如 WHERE YEAR(create_time) = 2023 → 索引失效，但优化器仍可能显示 type: index（走的是全索引扫描）
• 联合索引字段顺序不匹配，如索引是 (a, b, c)，但查询写成 WHERE b = 1 AND c = 2 → 实际无法使用该索引，优化器却可能误判为 type: range
• 数据倾斜严重，比如某 status 值占 95%，优化器认为走索引不如全表扫描快，直接跳过索引

验证方法：用 EXPLAIN FORMAT=jsON 查看 query_cost 字段，再对比强制索引（USE INDEX）后的 cost 是否更低。

能干预优化器行为的几个关键点

不要依赖“让它自己选”，尤其在线上复杂查询中：

• 用 FORCE INDEX 绕过错误判断，但需配合 EXPLAIN 验证效果
• 避免在 WHERE 或 ORDER BY 中对字段做计算或类型转换（如 WHERE id + 0 = 123）
• 对大表分页，慎用 LIMIT 10000, 20 —— 优化器会先扫 10020 行再丢弃前 10000，改用游标式分页（WHERE id > ? ORDER BY id LIMIT 20）
• 定期运行 ANALYZE TABLE，特别是大批量导入/删除后

最常被忽略的一点：优化器不会考虑缓存命中率、磁盘寻道延迟、网络传输这些真实耗时，它只信统计信息和内置模型。所以压测前后查 EXPLAIN 结果是否一致，比看文档更重要。