MySQL优化器为何选错索引及强制干预方法

MySQL优化器选错索引的根因与自救指南 ## ANALYZE TABLE 为何能快速修复索引选择错误 先纠正一个常见误区：优化器选择索引并非依赖“猜测”，而是基于统计值——主要是 `CARDINALITY`、`ROWS` 等字段，来估算不同执行计划的代价。问题在于，这些统计值来自采样而非全量计算。当表经历大量写入或删除后，采样结果极易严重偏离真实数据分布。 举一个典型场景：`city_id = 565` 实际仅匹配 200 行，但统计信息误判该值为“高频出现”，优化器于是认为走 `idx_city_id_type` 索引不划算，转而选择扫描 8000 万行。这本质上是统计噪音导致的性能灾难。 `ANALYZE TABLE` 的作用非常直接：重新随机采样大约 10–20 个索引页（具体数量由 `innodb_stats_persistent_sample_pages` 控制），刷新 `information_schema.STATISTICS` 中的统计字段，使优化器能够看到真实的数据分布。多数情况下，执行此命令后，慢查询即可从秒级降至毫秒级。 ## ANALYZE TABLE 不生效的三个常见原因 有时执行完 `ANALYZE TABLE`，用 `EXPLAIN` 查看发现执行计划依然令人头疼。不必急于怀疑人生，先排查以下三个问题： - **统计信息未持久化**：如果表设置 `innodb_stats_persistent = OFF`，同时 `innodb_stats_on_metadata = OFF`，那么统计信息仅存在于内存中。重启或执行某些元数据操作后，这些信息会完全消失。 - **查询中使用了函数**：例如 `WHERE DATE(log_dt) = '2024-01-01'`，这种写法相当于在索引列上套了一层函数，导致索引无法被利用。即使执行 `ANALYZE`，也无法解决索引失效问题。 - **隐式类型转换**：`city_id` 是 `INT` 类型，但传入参数却写成字符串 `'565'`。优化器为完成比较，被迫在列上添加转换函数，结果同样导致索引失效。 ## FORCE INDEX 是止血措施，并非根治方案 线上告警压力巨大时，`SELECT * FROM t FORCE INDEX (idx_city_id_type) WHERE city_id = 565` 确实是一根救命稻草，能迅速让查询恢复正常。但这更像“止痛药”而非“治疗方案”。 存在的问题包括： - 强制走索引绕过了优化器的正常决策流程。如果某个 `city_id` 的数据量增长十倍，强制走索引反而可能比全表扫描更慢。 - SQL 中硬编码了索引名称，后续一旦删除或修改索引名，会直接报错 `ERROR 1176 (HY000): Key 'xxx' doesn't exist in table 't'`。 - 其他未添加 `FORCE` 的查询仍然受错误统计信息影响，问题仅被局部压制，根本原因并未得到解决。 ## 统计不准：无日志、无报错、无告警的隐形杀手 这是最令人棘手的地方。统计偏差不会产生任何错误信息，只会安静地拖垮性能，直到慢查询如雪崩般涌现。最危险的是，你总以为索引仍在正常工作，但优化器早已悄悄弃用了它。 真正稳定的解决方案是：将 `ANALYZE TABLE` 纳入运维巡检脚本，同时监控 `information_schema.STATISTICS.CARDINALITY` 的波动趋势——一旦偏差超过 30%，便自动触发重采样。这才是从根源解决问题的思路。