MySQL执行计划为何总选全表扫描？深入优化器的成本计算逻辑

排查慢查询时，使用EXPLAIN命令查看执行计划，发现type=ALL赫然在目，但检查表结构却发现相关查询字段上明明建有索引。这种情况是否似曾相识？先别急着质疑数据库或索引的有效性，问题的根源很可能在于查询优化器的“成本决策”机制——它并非机械地“见到索引就使用”，而更像一位精于计算的成本会计师，总是选择其预估开销最低的执行路径。

EXPLAIN显示type=ALL但有可用索引，核心原因是优化器基于成本估算认为全表扫描优于索引扫描；常见诱因包括查询匹配行数过多、索引区分度过低、回表查询开销巨大以及表统计信息过期失真。

为什么 EXPLAIN 显示 type=ALL 却有可用索引？

关键在于透彻理解MySQL优化器的决策逻辑：它始终选择其认为“执行成本最低”的访问方式。即便WHERE条件涉及的字段上存在索引，只要优化器预估“通过索引扫描定位记录+回表查询完整数据行”的总开销，高于直接进行全表顺序扫描的成本，它就会果断选择ALL（全表扫描）。

哪些典型场景容易触发优化器的这种判断呢？通常离不开以下几类核心原因：

• 查询条件匹配的行数占比过高：例如查询status != 'done'，而表中超过95%的数据行都满足此条件。此时，通过索引定位大量记录再逐一回表获取数据的随机I/O开销，可能远超直接顺序读取整张表的连续I/O代价。
• 索引列的区分度（基数）过低：例如在仅有“男”、“女”两种取值的gender字段上建立索引。由于索引键值重复率极高，通过索引筛选出的数据范围仍然很大，导致索引扫描效率大打折扣。
• 查询需要返回的列过多（非覆盖索引）：这会导致显著的“回表”开销。假设索引仅包含筛选字段，但查询使用了SELECT *，数据库引擎就不得不根据索引结果中的主键值，反复回主键索引（聚簇索引）中查找完整数据行，此过程产生的随机I/O累积成本可能远超预期。
• 表或索引的统计信息已过期：优化器依赖INFORMATION_SCHEMA.STATISTICS表中的cardinality（基数，即索引中唯一值数量的估计值）等统计信息进行成本估算。如果这些信息因未及时更新而与实际数据分布严重不符，优化器便会基于错误数据做出“全表扫描更经济”的误判。

如何探查优化器具体的成本计算过程？

仅靠推测无法定位根本原因。幸运的是，MySQL 8.0及以上版本提供了强大的optimizer_trace功能，它能完整揭示优化器决策的“心路历程”。

具体操作步骤如下：

• 首先，开启优化器追踪：SET optimizer_trace="enabled=on";
• 接着，执行需要分析的慢查询SQL语句。
• 然后，查询追踪结果：SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
• 最后，重点解析输出JSON中steps数组内的内容，特别是considered_execution_plans（被考虑过的执行计划）以及每个计划对应的cost_for_plan（计划总成本）。

通过审阅这份详细的“成本明细单”，你常会发现：即便索引扫描的read_cost（读取成本）看似较低，但加上eval_cost（评估过滤条件的成本）以及可能存在的prefix_cost（连接查询中的前缀成本）后，其总成本反而超过了简单的全表扫描。优化器正是通过这样精细的成本核算来做出最终选择的。

使用 FORCE INDEX 强制走索引一定性能更优吗？

这是一个普遍的认知误区。使用FORCE INDEX仅仅是跳过了优化器的成本比较阶段，强行指定了数据访问路径，但并未改变该路径实际的物理执行代价。在某些特定场景下，强制使用索引反而可能导致查询性能下降。

典型的“性能翻车”场景包括：

• 表数据量非常小：对于仅有几百行记录的小表，全表扫描的代价极低（可能一次I/O即可完成），而走索引则需要额外的索引树查找与随机I/O，得不偿失。
• 未使用覆盖索引：如果强制使用的索引无法覆盖查询所需的所有列（即非覆盖索引），存储引擎就需要进行大量的回表操作。相比之下，全表扫描如果是顺序读，在特定数据分布下整体耗时可能更短。
• 索引碎片化严重：通过INFORMATION_SCHEMA.TABLES中的DATA_FREE等指标可以观察，如果索引页空间利用率低、碎片多，那么遍历该索引会导致物理读放大，性能反而下降。

如何验证哪种方式更优？不要仅依赖EXPLAIN中的预估rows。更可靠的方法是使用BENCHMARK()函数进行多次执行耗时对比，或者查询performance_schema.events_statements_summary_by_digest等系统表获取实际执行统计，对比Handler_read_first、Handler_read_next等计数器，才能真实反映不同执行计划下的I/O开销差异。

哪些关键操作能真正影响优化器的成本判断？

要引导优化器做出更明智的选择，必须从其成本计算的输入源头着手。以下几项操作至关重要：

• 定期执行 ANALYZE TABLE：这是更新表和索引基数（cardinality）最直接有效的方法。尤其在对大表进行大批量增、删、改操作后，务必执行一次，确保统计信息能反映最新的数据分布。
• 合理调整统计信息采样参数：通过innodb_stats_persistent_sample_pages（默认20）等参数可以控制ANALYZE时的采样页数。增加样本页数能使统计信息更精确，但分析过程也会更耗时，需根据数据量及更新频率权衡设置。
• 避免在索引列上使用函数或表达式：诸如WHERE YEAR(created_at) = 2023或WHERE amount/100 > 10的写法，会导致索引失效，优化器无法有效利用created_at或amount列上的索引，往往只能退而求其次选择全表扫描。
• 设计高效的联合索引顺序：顺序决定效率。如果高频查询是WHERE a=1 AND b>10，那么联合索引(a,b)可以高效利用前缀匹配与范围扫描；若建成(b,a)，则可能只能利用到a的等值匹配部分，b的范围查询效率会大幅降低。

归根结底，优化器的成本模型并不理解业务逻辑，它只依据冰冷的统计数据和预设的成本规则进行计算。索引设计、实际数据分布、系统配置参数，这三者中任何一项出现不匹配，type=ALL（全表扫描）就可能频繁出现在执行计划中。这并非系统的缺陷，而是优化器在严格遵循其“成本最优”的决策原则。深入理解这套规则，方能更好地驾驭数据库性能优化。