" />
文章

查询优化器选错了索引，怎么办

Posted recently Updated recently
By Administrator
24~31 min read

查询优化器选错了索引，怎么办

面试指北 · 蔚来

题意

面试官问这个问题，一般不是在考你“会不会加 FORCE INDEX”，而是在考三层东西：

  1. 你知不知道优化器是怎么选索引的——它是基于代价（Cost Based Optimizer），代价来自统计信息 + 代价模型，不是“哪个索引看着顺眼就用哪个”。
  2. 你有没有一套排查路径——能不能从 EXPLAIN 出发，定位到到底是统计信息不准、索引设计有问题，还是 SQL 本身写得让优化器无从下手。
  3. 你懂不懂工程边界——Hint 是“止血”还是“根治”？硬编码 Hint 上线之后怎么退出？能不能安全删索引？

所以回答时最好先给一个“先确认、再定位、最后处理”的框架，再展开具体手段，而不是上来就罗列五六个技巧。

常见诱因：统计信息过时、数据分布倾斜（比如某个值占了 80% 的行）、索引太多互相干扰、SQL 写法导致索引失效、参数配置（如 eq_range_index_dive_limit）让优化器放弃了精确采样、以及版本本身的代价模型缺陷。

结论

面试里可以这样组织主线：

先用 EXPLAIN / EXPLAIN ANALYZE 证明“确实选错了”，再从“统计信息 → 索引设计 → SQL 写法 → Hint 兜底 → 参数/版本”这条链路依次收敛，最后做长期治理（索引评审、慢 SQL 巡检、执行计划回归）。

六类手段对应如下：

  1. 更新统计信息：ANALYZE TABLE，必要时为倾斜列建直方图。
  2. 加 Hint 引导：MySQL 用 USE/FORCE/IGNORE INDEX，Oracle 用 /*+ INDEX(...) */；只作为兜底，必须有退出机制。
  3. 清理干扰索引：删冗余索引、合并功能重叠的索引，MySQL 8.0 可先设 INVISIBLE 再删。
  4. 改写 SQL：避免索引列上的函数与隐式转换，把 OR 拆成 UNION ALL，把难估算的子查询改成 JOIN。
  5. 用覆盖索引 / 索引下推：减少回表，让“用好索引”的收益远大于“选错索引”的代价。
  6. 调参数或升版本：optimizer_switch 等属于最后手段，必须压测后再上。

一句话总结：统计信息准、索引和查询模式对得上，是从根上让优化器不犯错；Hint 只是给自己留的一条后路。

要点展开

0. 前置动作：先确认它到底是不是“选错了”

很多被归罪于“优化器选错索引”的慢查询，其实是锁等待、回表量太大、buffer pool 命中率低、或者统计信息之外的原因。所以第一步永远是看执行计划：

EXPLAIN SELECT ...;                 -- 看 type / key / rows / filtered / Extra
EXPLAIN ANALYZE SELECT ...;         -- MySQL 8.0.18+，能看到真实耗时与行数
SHOW STATUS LIKE 'Last_query_cost'; -- 这条 SQL 的估算代价

重点看三个信号：

  • key 显示走了 A 索引，但 rows 估算值和实际返回行数差一个数量级 → 统计信息或采样问题；
  • type 是 ALL（全表扫）而你确信有高选择性索引 → 可能是隐式转换 / 函数导致索引失效，不是优化器选错；
  • Extra 里有 Using filesort / Using temporary，且回表次数巨大 → 更可能是索引没覆盖住查询。

如果想看优化器“为什么这么选”，MySQL 可以开优化器追踪：

SET optimizer_trace = 'enabled=on';
SELECT ... ;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace = 'enabled=off';

里面会给出每个候选索引的代价估算，是面试时可以提一嘴的加分项。

1. 检查并更新统计信息

统计信息不准是最常见的原因。 优化器决定走哪个索引，靠的是索引基数（cardinality）、表的行数、数据分布这些统计量。这些数字一旦过时，代价估算就全歪了。

MySQL 里重新采集：

ANALYZE TABLE tbl_name;                                  -- 重新采集索引统计信息
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col1 WITH 64 BUCKETS;  -- 8.0：为倾斜列建直方图

几个值得说清楚的点：

  • InnoDB 的统计信息是采样出来的，默认只随机读 20 个叶子页（innodb_stats_persistent_sample_pages）。大表上这个采样比例很低，基数容易估偏，可以适当调大，代价是 ANALYZE 变慢。
  • 默认开启了 innodb_stats_auto_recalc，但它是异步、按变化比例触发的，大批量导入/删除之后不一定马上生效，所以“大批量变更后手动 ANALYZE”是个好习惯。
  • MySQL 8.0 的直方图专门解决“列上没有索引、但数据严重倾斜”的场景，比如状态列 95% 都是同一个值。这种情况下即使统计信息“新”，没有直方图也很难估准。

2. 使用查询提示（Hints）

当统计信息短期无法修正、或者这条 SQL 极其关键且优化器反复选错时，用 Hint 强制指定索引。

Oracle 风格：

SELECT /*+ INDEX(tbl idx_name) */ col1, col2 
FROM tbl 
WHERE col3 = 'xxx';

MySQL 实际的强制索引语法（表级提示，写在表名后面）：

SELECT col1, col2
FROM tbl
FORCE INDEX(idx_name)
WHERE col3 = 'xxx';

区别要记住：

  • USE INDEX(...) 是“建议只从这些索引里选”，优化器仍可能选择全表扫描；
  • FORCE INDEX(...) 是“只能用这个索引，并且让全表扫描的代价看起来高到不可接受”；
  • IGNORE INDEX(...) 用于排除某个索引，多用于绕开某个“看起来更便宜、实际更差”的索引。

Hint 必须谨慎使用，原因有三：

  1. 数据分布变了，硬编码的索引可能从“最优”变成“最差”，而 Hint 不会自适应；
  2. 索引一旦被重命名或删除，SQL 直接报错；
  3. Hint 藏在代码或 MyBatis 映射文件里，很容易被后人遗忘。

工程上的做法是：Hint 只给极少数核心 SQL，配合注释说明原因和责任人，同时设定复查时间点；一旦统计信息修好、索引调整到位，就把 Hint 摘掉。Oracle 里更规范的做法是用 SQL Plan Management（DBMS_SPM）固定执行计划，而不是往 SQL 文本里写 Hint。

3. 重新设计或移除干扰性索引

表上索引越多，优化器要评估的候选计划就越多，出现“两个索引代价接近、结果选到了不理想那个”的概率也越高。

先看索引到底有没有被用。 MySQL 可以查 sys 库的视图（依赖 performance_schema）：

SELECT * FROM sys.schema_unused_indexes;      -- 从未被使用过的索引
SELECT * FROM sys.schema_redundant_indexes;   -- 冗余/重复索引
SHOW INDEX FROM tbl_name;                     -- 看基数 Cardinality

删索引不要一步到位。 MySQL 8.0 提供了不可见索引，可以先把索引设为 INVISIBLE，观察一段时间确认业务没有退化，再真正 DROP：

ALTER TABLE tbl_name ALTER INDEX idx_name INVISIBLE;
-- 观察若干天后
ALTER TABLE tbl_name ALTER INDEX idx_name VISIBLE;  -- 回滚
-- 或者
DROP INDEX idx_name ON tbl_name;

索引设计要贴着查询走：高频查询优先建能覆盖 WHERE + ORDER BY 的联合索引，遵循最左前缀；避免在同一个列上建功能重叠、只是顺序不同的多个索引（比如 (a,b) 和 (a) 通常只需要前者）。

4. 改写查询，帮优化器把题出简单

同一条语义，不同写法可能得到完全不同的执行计划。常见的几类改写：

  • OR 拆 UNION ALL：当 OR 两侧命中不同索引、或者一侧根本无法用索引时，拆开分别走索引再合并，通常比优化器自己处理更稳定。注意用 UNION ALL 而不是 UNION，除非确实需要去重。
  • 子查询改 JOIN：尤其是相关子查询、IN (SELECT ...) 这类，优化器对结果集大小的估算经常失准。
  • 别在索引列上做函数运算：
-- 走不了 create_time 上的索引
WHERE DATE(create_time) = '2025-01-01'

-- 改成范围查询，可以正常用索引
WHERE create_time >= '2025-01-01 00:00:00'
  AND create_time <  '2025-01-02 00:00:00'

如果业务上确实必须按“日期”维度查，两个选择：一是上面这种范围改写；二是 MySQL 8.0 建函数索引（INDEX ((DATE(create_time)))）或生成列 + 索引。

  • 注意隐式转换的方向：varchar 列和数字比较（WHERE code = 123）会把列转成数字，索引直接失效；反过来 int 列和字符串比较（WHERE id = '123'）是把常量转成数字，索引仍然可用。所以统一参数类型，尤其是 ORM 传参时别把字符串传给数值列。

5. 覆盖索引与索引下推（ICP）

这两个是“让好计划更划算”的手段，间接也能减少优化器的误判空间。

覆盖索引：查询涉及的列全在索引里，引擎只扫索引、不回表。EXPLAIN 的 Extra 显示 Using index。比如：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status, amount);
SELECT user_id, status, amount FROM orders WHERE user_id = 1 AND status = 2;

代价从“索引命中 N 次 + N 次随机回表”降到“只扫索引”，收益往往是一个数量级。

索引下推（ICP）：MySQL 5.6+ 的特性，把能在索引上判断的条件交给存储引擎层过滤，减少回传给 Server 层的行数。Extra 显示 Using index condition。它依赖 optimizer_switch 里的 index_condition_pushdown（默认开）。

注意区分：Using index 是覆盖索引（不回表），Using index condition 是索引条件下推（仍然回表，只是过滤提前了）。这两个在面试里经常被混答。

6. 升级数据库版本或调整优化器参数

版本层面：不同版本的优化器算法、代价模型确实有差异。MySQL 8.0 相比 5.7 在 join 顺序、hash join、倒序索引、直方图、不可见索引上都有改进，很多“5.7 上要加 Hint”的场景到 8.0 就自己好了。看 release notes 里 optimizer 相关的条目，是有价值的。

参数层面，几个和“选错索引”直接相关的：

参数 作用
optimizer_switch 控制 index_merge、index_condition_pushdown、mrr 等特性开关，粒度高但风险也高
eq_range_index_dive_limit 默认 200。等值条件对应的区间数超过它时，优化器从“逐值精确采样（index dive）”退化为“用索引统计信息估算”，很容易估偏导致选错索引
join_buffer_size 影响 join 的执行代价与 BNL/BKA 的选择，间接影响计划
innodb_stats_persistent_sample_pages 统计信息采样页数，影响基数准确度

这些参数必须先在预发环境验证，改完要观察一段时间再全量。全局参数尤其要谨慎，因为它影响的是一整批 SQL，不是一条。

易错点与对比

① 把 OPTIMIZE TABLE 当成更新统计信息的命令。
ANALYZE TABLE 才是专门重采统计信息的；OPTIMIZE TABLE 在 InnoDB 下主要做表重建与空间回收（会顺带更新统计信息），成本高得多，大表上不要随手执行。

② 以为 MySQL 支持 /*+ INDEX(t idx) */ 这种注释 Hint。
那是 Oracle 的写法。MySQL 中这种注释会被当作普通注释忽略；MySQL 8.0 的 /*+ ... */ Hint 体系里也没有“指定单表索引”的写法，强制索引仍然要用 FORCE INDEX。

③ USE INDEX 不等于“一定用这个索引”。
USE INDEX 只是缩小候选范围，优化器仍可能选全表扫描；FORCE INDEX 才是真正意义上“逼着它用”。反过来，FORCE INDEX 也有坑：当索引实际回表行数极大时，它会让一个本来全表扫描更快的查询变得非常慢。

④ 把“索引失效”和“优化器选错索引”混为一谈。
索引列上有函数、隐式类型转换、LIKE '%x'、OR 里混了非索引列——这些是索引根本用不上，不是优化器选错。前者要改 SQL，后者才轮到统计信息和 Hint，排查方向完全不同。

⑤ 统计信息准了，就一定能选对？
不一定。优化器用的是代价模型，模型里包含“随机读一页的代价”“顺序读一页的代价”这类常量假设。在 SSD、或者热点数据全在 buffer pool 的场景下，这套假设可能失真。这也是为什么“改 SQL 让它在任何计划下都不算太差”往往比“死磕选对索引”更划算。

⑥ 各处数据库的差异要分清楚。

手段 MySQL Oracle PostgreSQL
更新统计 ANALYZE TABLE DBMS_STATS.GATHER_TABLE_STATS ANALYZE / autovacuum
强制索引 FORCE INDEX /*+ INDEX(...) */ 原生不支持，需 pg_hint_plan 扩展
关闭某类计划 optimizer_switch 多种 Hint enable_indexscan 等会话级开关

在 PostgreSQL 里说“加个 Hint”而不知道要装扩展，是很容易被面试官抓的点。

小结

“优化器选错索引”本质上是代价估算与实际执行对不上。所以处理思路永远是：先证伪（是不是真的选错），再修根因（统计信息、索引设计、SQL 写法），实在不行才用 Hint 和参数“打补丁”。

按优先级排下来：

  1. 更新统计信息，倾斜列考虑直方图；
  2. 精简索引，删冗余、用不可见索引降低删除风险；
  3. 改写 SQL，消除函数、隐式转换、难估算的子查询与 OR；
  4. 用覆盖索引 / ICP 降低“选错”的代价；
  5. Hint 兜底，只给极少数核心 SQL，并留好退出机制；
  6. 最后才考虑调参与升版本，且必须压测。

面试里如果能把“先 EXPLAIN 确认 → 判断是估算偏差还是索引失效 → 分别对应手段”这条链路讲清楚，比背六条结论要加分得多。

面试指北
License:  CC BY 4.0