MySQL 优化器是一个基于成本（Cost-Based Optimizer，CBO）的决策引擎。对于一条查询，它会评估所有可能的执行计划，估算每个计划的 I/O 和 CPU 开销，最终选择总成本最低的那个。对索引而言，关键在于“走哪个索引代价更小，甚至不走索引全表扫描是否更快”。作为开发者，理解这层逻辑能帮你更好地设计索引，也能在优化器“犯傻”时快速纠偏。
5.7.1 优化器的成本模型基础
MySQL 5.7 的成本模型主要由两类成本构成：
- I/O 成本：从磁盘读取一个数据页（默认 16KB）的开销，记为 1.0（基准单位）。这是最核心的代价，因为磁盘 I/O 常是瓶颈。
- CPU 成本：处理一行数据的开销，包括判断条件、对比值等，记为 0.2（默认）。显然，扫描的行数越多，CPU 成本也越高。
实际计算一条索引扫描的成本时，优化器会分别估算：
- 索引扫描成本：从 B+ 树中定位到符合条件的索引记录需要访问的页数。
- 回表成本：如果索引不包含查询需要的全部字段（非覆盖索引），还需要通过索引记录中的主键回聚簇索引读完整行，这也会产生额外的 I/O 和 CPU 成本。
- 结果处理成本：对筛选出的行进行排序、分组、临时表等额外操作的成本。
所有这些都被换算成统一的“成本单位”，然后比较。你可以用 EXPLAIN FORMAT=JSON 查看查询计划对应的 cost_info，直观看到各项成本数值。
5.7.2 统计信息：代价估算的基石
优化器不能凭空估算，它依赖表和索引的统计信息。这些信息主要由 innodb_stats_persistent 控制的持久化统计维护（5.7 默认开启），也可以通过 ANALYZE TABLE 手动刷新。
最重要的几个统计值：
- 表行数（
rows）：不是精确值，而是采样估算出来的。这个值和实际行数可能有一定偏差，尤其是刚做大量删除或插入后。 - 索引基数（
cardinality）：索引列中不同值的数量。基数越高，索引的区分度越好。优化器用cardinality/rows判断某列值的分散程度。若基数不准，索引可能被放弃。 - 平均每行字节长度：用来推算一个数据页能存放多少行，进而估算扫描页数。
5.7 版本没有 8.0 的直方图统计，只依赖基数。这意味着对于数据分布不均的列（例如 90% 的行状态都是“已完成”），优化器可能估算不到这种倾斜，从而错误地选择索引或放弃索引。
5.7.3 索引扫描代价的计算逻辑
简单来说，优化器评估一个索引的扫描成本是这样做的：
- 定位起点：根据查询条件，确定需要在 B+ 树叶子节点上从哪一行开始扫描（例如等值
=或第一个范围值>=）。 - 估算扫描行数：如果条件是
key = 5，它会用1 / cardinality作为选择率，乘以总行数得到预估扫描行数。对于范围条件（如key > 100 AND key < 200），选择率按范围占总键值区间的比例估算。组合多个条件时，选择率通常假设各列独立，直接相乘（可能高估或低估）。 - 计算 I/O 页数：根据扫描行数，除以每页平均存放的索引记录数，得出大概需要顺序读取多少个索引叶子页。对于大范围扫描，这部分成本会显著上升。
- 计算回表 I/O：若不是覆盖索引，则每个扫描到的索引记录都可能触发一次随机回表读（除非优化器认为数据页会被缓冲多次命中而降低权重）。当扫描行数较多时，回表成本常常成为压倒全表扫描的最后一根稻草。
临界点例子：一张百万行的表，主键索引 id，另有索引 idx_status (status)。假设 status 只有 0 和 1 两种值，基数为 2。查询 WHERE status = 1 预估会扫描约 50 万行。优化器算出：扫描这么多索引记录 + 50 万次回表的成本，远高于直接全表扫描（顺序读整张表），因此它大概率会选择全表扫描。这就是“基数太小导致索引失效”的典型情况。
5.7.4 覆盖索引与索引下推对成本的影响
在 5.7 中，有两个特性会降低某些索引的成本，使优化器更倾向选择它们：
- 覆盖索引：如果查询的所有字段都出现在索引里，则无需回表，
Extra显示Using index。此时回表成本为 0，整体成本可能大幅低于全表扫描。 - 索引下推（ICP）：当有回表且
WHERE中有可在索引中判断的条件时，MySQL 会把条件先用在索引上过滤记录，减少真正回表的次数。这实际上降低了回表成本。优化器在评估时会将 ICP 纳入考虑，使得一些原本性价比不高的索引变得可用。
5.7.5 多索引选择与索引合并
当查询有多个索引可用时，优化器会逐一评估各索引的成本，并可能采用索引合并策略，用多个索引结果做交集或并集。5.7 支持的三种索引合并：
- Intersection（交集）：对多个索引各自扫描出主键列表，求交集后再回表。适用于
WHERE key1 = a AND key2 = b，单个索引的扫描结果都较大，但交集很小。 - Union（并集）：求并集后回表，适用于
OR连接。 - Sort-Union：先对每个索引的结果排序，再合并去重，适用于范围条件
OR。
比如有索引 idx_a (a) 和 idx_b (b)，查询 WHERE a=10 AND b=20，可能两个索引单用都会扫很多行，但交集后只有极少行需要回表，优化器会选择 index_merge 以避免全表扫描。当然，这也受 optimizer_switch 中 index_merge* 等标志控制。
5.7.6 优化器也可能“错判”
由于依赖估算，优化器当然会出偏差，常见原因：
- 基数不准：统计信息过期或采样不均。可用
ANALYZE TABLE手动刷新，或增大innodb_stats_persistent_sample_pages提高采样精度。 - 选择率估算错误：多个过滤条件可能数据相关，但优化器假设独立。导致预估行数远小于或远大于实际。
- 回表成本被低估或高估：缓冲池命中率高时，回表成本应更低，但优化器可能假设为全磁盘随机读。
- 没考虑数据在磁盘上的物理顺序：全表扫描实际是顺序读，而回表可能是随机读，这种差距在 SSD 和 HDD 上表现悬殊，但成本模型默认未区分存储介质。
发现问题后，你可以用 FORCE INDEX 强制索引，但更好的做法是让优化器自己修正。5.7 提供了 OPTIMIZER_TRACE 来跟踪优化器决策全过程，能清晰看到每个候选索引的估算成本和选择原因。当执行计划不理想时，打开 optimizer_trace 执行查询，然后从 information_schema.OPTIMIZER_TRACE 查看输出，这比凭空猜测高效得多。
总之，5.7 的优化器是一个实用但仍有盲区的成本计算器。理解它怎样估算索引代价，你就知道为什么某个索引没被使用，以及该如何调整统计信息、改写 SQL 或添加更优的索引来纠正它。在实际工作中，索引优化的核心其实就是帮助优化器做出更准确的选择。