优化器：执行计划生成与索引选择

当一条 SQL 经过解析器检查无误后，就进入了优化器（Optimizer）的地盘。优化器的使命可以归结为一句话：为同一个查询找到成本最低的执行路径。它是影响查询性能最关键的环节之一——你写的 SQL 可能完全一样，优化器能不能选对索引、选对连接顺序，性能可能差出几个数量级。

优化器的核心工作：从逻辑计划到物理计划

你可以把优化过程理解为两道工序：

1. 逻辑优化（查询重写）：在语义不变的前提下，把 SQL 转换成更高效的形式。比如把 IN 子查询改写为半连接（Semi-Join），把 NOT EXISTS 改写为反连接（Anti-Join），移除无用的 DISTINCT，或者把外层 WHERE 条件下推到子查询内部。这些重写规则是硬编码在优化器中的，不需要统计信息，几乎总能提升效率。

1. 物理优化（执行计划生成）：决定具体用什么方式执行。比如：

• 用哪个索引去访问表（type 可能为 ref、range 还是全表扫描 ALL）
• 多表连接时，哪张表作为驱动表，连接顺序是什么（决定 EXPLAIN 中表的排列）
• 是否使用索引条件下推、是否延迟物化、是否采用哈希连接等

优化器会枚举多种可能的执行计划，然后估算每个计划的“成本”，选出成本最低的那个生成最终的物理执行计划。

成本模型：优化器是如何“算账”的

优化器并不是真的去执行一遍 SQL 来比较耗时，而是使用成本模型来估算。

成本的核心指标是 Cost，它由两个部分组成：

• I/O 成本：从磁盘（或缓冲池）读取数据页的开销。每读取一个页算 1.0 成本（这个基数可配置，但通常不调整）。
• CPU 成本：处理每一行数据所消耗的 CPU 开销，比如比较条件、排序等。每处理一行，通常算 0.2 成本。

优化器在计算一个索引扫描的代价时，会根据统计信息估算需要读取多少页（I/O 成本）和需要检查多少行（CPU 成本），然后加总得到总成本。

统计信息的准确性直接决定了成本估算是否靠谱。主要的统计信息包括：

• 表级统计：总行数、平均行长度、数据页数等（information_schema.TABLES 中可见）
• 索引统计：基数（Cardinality），即索引列不同值的数量，值越高说明索引区分度越高，越值得使用
• 列值分布（直方图，MySQL 8.0 引入）：对特定列，给出数据在各值区间的分布，让优化器能更精准估算 WHERE column BETWEEN 10 AND 20 这样的范围条件会过滤掉多少行

执行 ANALYZE TABLE 会主动更新统计信息。InnoDB 也会在后台自动抽样更新，但有时可能过时，导致优化器做出错误选择。当一条 SQL 突然性能急剧下降，且 EXPLAIN 显示使用了不合理的索引时，更新统计信息往往是最先尝试的手段。

索引选择的具体逻辑

面对一条 SELECT * FROM users WHERE age >= 25 AND city = 'Beijing'，可能有三个候选索引：idx_age、idx_city、idx_city_age 联合索引。优化器会逐一估算每个选项的成本：

• 估算扫描行数：利用索引统计信息（如基数、直方图）估算满足 city = 'Beijing' 的行数，再估算 age >= 25 的行数，最终估算出通过该索引需要扫描的行数 rows。
• 估算索引读取代价：估算需要通过索引树获取的页数，再加上如果需要“回表”取索引不包含的列，额外读取主键索引树的代价。
• 比较成本：选出总成本最低的那个索引。如果没有任何索引能显著降低扫描行数，优化器会直接选择全表扫描（全表扫描也有成本，但相对可控）。

MySQL 8.0 引入的不可见索引特性，在这里特别实用。你可以将某个索引设为 INVISIBLE，优化器在生成计划时会忽略它，但数据更新依然维护该索引。这让你可以在实际负载下安全地测试删除索引的影响，确认无用后再彻底删除。

导致优化器“选错”的常见原因及应对

尽管优化器大多数时候表现良好，但以下几个情况容易让它做出次优决策：

1. 统计信息不准：行数、基数与实际情况偏离大。解决：定期 ANALYZE TABLE，或适当调高 innodb_stats_persistent_sample_pages 增加采样页数。
2. 索引区分度低但优化器高估：比如 status 字段只有 0/1 两个值，优化器可能觉得用索引过滤好，但实际上只过滤一半数据，回表开销巨大。解决：通过 FORCE INDEX 提示（但这应该作为最后手段），或者调整索引设计（建联合索引让查询可以覆盖索引）。
3. 多表连接时选择错误的驱动表：可能因为统计信息不准，把大结果集当小结果集来驱动。解决：可以用 STRAIGHT_JOIN 强制连接顺序，或者用 JOIN_FIXED_ORDER 优化器 hint，但优先检查统计信息。
4. 查询中使用变量或函数，优化器无法预估值：如 WHERE id = @var，优化器看不到 @var 的值，只能按固定比例估算。显著影响越大，越需要避免，或者用其他方式改写。

开发者实用技巧：观察优化器工作

• 用 EXPLAIN FORMAT=JSON 代替普通 EXPLAIN，可以看到详细的成本估算、重写后查询、候选索引评估等信息，是调试复杂查询优化器行为的有力工具。
• 用 OPTIMIZER_TRACE（SET optimizer_trace='enabled=on' 后执行查询，再查询 information_schema.OPTIMIZER_TRACE）能看到优化器内部的每一步决策过程。当怀疑优化器选错计划时，这是最彻底的诊断方法。

优化器是 MySQL 的智慧核心，你无需完全理解它的内部规则，但学会观察它的决策，理解成本估算的基础逻辑，就能在绝大多数场景下写出让优化器“轻松理解”的 SQL，从而获得预期的执行计划。