开发者日记

外观

执行计划走偏急救：FORCE INDEX 实战避坑录 ​

问题背景：SQL 没变，执行计划为什么突然走偏 ​

线上 SQL 调优中，最棘手的问题往往不是“没有索引”，而是“明明有合适索引，优化器却不用”。典型表现是：某条查询昨天响应 200ms，今天变成 3s；EXPLAIN 发现 key 从高选择性的联合索引切换成了低区分度索引，甚至退化为全表扫描。

MySQL 优化器基于成本估算选择访问路径，主要参考索引基数、记录长度、回表成本、范围扫描比例、表统计信息等。数据分布倾斜、统计信息过期、参数变化、数据量跨越成本临界点，都可能让优化器误判。此时，优化器 HINT 是一种直接的执行计划干预手段，但它不是“银弹”：用得好可以立即止血，用不好会把未来数据变化后的风险固化进 SQL。

本文聚焦三个实战问题：如何确认优化器选错索引；USE INDEX、FORCE INDEX 的边界是什么；多表 JOIN 时如何干预表顺序与驱动表。

方案对比：统计信息、SQL 改写与 HINT 怎么选 ​

遇到执行计划走偏，不建议第一反应就加 FORCE INDEX，而应按成本从低到高排查。

EXPLAIN 定位type/key/rows刷新统计信息ANALYZE TABLESQL 与索引改写谓词/覆盖索引使用 HINT 止血FORCE/STRAIGHT_JOIN

常见方案各有适用范围：

  • ANALYZE TABLE：成本最低，适合统计信息明显过期，但 InnoDB 的采样估算并不保证计划永久稳定。
  • 补充或调整索引：适合长期治理，尤其是缺少覆盖索引、联合索引顺序不合理时。
  • SQL 改写：通过拆分 OR、改写深分页、避免函数包裹字段等方式让谓词可索引。
  • 优化器 HINT：适合紧急止血或数据分布长期固定的场景，需要明确注释、监控和回滚方案。

可运行示例：构造一次优化器误判 ​

下面建立订单表并写入倾斜数据。绝大多数订单状态为 PAID，只有少量 REFUND。业务查询退款单时，理论上应使用状态索引，但如果同时按时间排序并限制返回行数，优化器可能认为沿时间索引扫描更少行，从而放弃状态索引。

sql
-- 建立演示表
CREATE TABLE t_order (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  status VARCHAR(16) NOT NULL,
  buyer_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_status_created (status, created_at),
  KEY idx_created (created_at),
  KEY idx_buyer_status (buyer_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 写入 10 万条已支付订单
INSERT INTO t_order(order_no, status, buyer_id, amount, created_at)
SELECT LPAD(seq, 10, '0'),
       'PAID',
       1 + FLOOR(RAND(seq) * 1000),
       99.00,
       DATE_ADD('2025-01-01', INTERVAL seq MINUTE)
FROM (
  SELECT @i := @i + 1 AS seq
  FROM information_schema.columns a
  JOIN information_schema.columns b
  JOIN (SELECT @i := 0) init
  LIMIT 100000
) t;

-- 只写入 300 条退款订单，并集中在近期
INSERT INTO t_order(order_no, status, buyer_id, amount, created_at)
SELECT CONCAT('R', LPAD(seq, 8, '0')),
       'REFUND',
       1 + FLOOR(RAND(seq + 100000) * 1000),
       19.90,
       DATE_ADD('2025-06-01', INTERVAL seq MINUTE)
FROM (
  SELECT @j := @j + 1 AS seq
  FROM information_schema.columns
  JOIN (SELECT @j := 0) init
  LIMIT 300
) x;

ANALYZE TABLE t_order;

执行目标查询并查看计划：

sql
EXPLAIN
SELECT id, order_no, amount, created_at
FROM t_order
WHERE status = 'REFUND'
ORDER BY created_at DESC
LIMIT 20;

重点关注这些列：

  • type：range、ref 通常优于 ALL；
  • key：实际选中的索引；
  • rows：预估扫描行数，不代表真实返回行数；
  • filtered：过滤后剩余比例；
  • Extra：是否出现 Using where、Using filesort、Using index。

如果计划选择 idx_created，数据库会从最新时间倒序扫描，直到找到 20 条退款记录。在退款占比极低的历史月份，这个策略可能很快；但当业务大促后退款集中出现或数据分布变化时，也可能扫描大量 PAID 订单。

FORCE INDEX 实战：强制选择高选择性索引 ​

USE INDEX 只是“建议”，优化器仍可能选择全表扫描；FORCE INDEX 的约束更强，表示只有在无法使用该索引时才考虑其他路径。对上述查询，可以强制使用 (status, created_at) 联合索引：

sql
EXPLAIN
SELECT id, order_no, amount, created_at
FROM t_order FORCE INDEX (idx_status_created)
WHERE status = 'REFUND'
ORDER BY created_at DESC
LIMIT 20;

该索引同时满足等值条件和排序：先用 status = 'REFUND' 精确定位，再在同一状态范围内按 created_at 有序读取，通常可以避免 Using filesort。正式上线前，建议用以下方式对比实际执行情况：

sql
-- 打开执行计时与行数统计
SET profiling = 0; -- MySQL 8.0 已移除 profiling，可改用 performance_schema 或客户端计时

-- 对比缓冲池冷启动不现实，至少连续执行多次，减少缓存偶发影响
SELECT SQL_NO_CACHE id, order_no, amount, created_at
FROM t_order
WHERE status = 'REFUND'
ORDER BY created_at DESC
LIMIT 20;

SELECT SQL_NO_CACHE id, order_no, amount, created_at
FROM t_order FORCE INDEX (idx_status_created)
WHERE status = 'REFUND'
ORDER BY created_at DESC
LIMIT 20;

注意 MySQL 8.0 中 SQL_NO_CACHE 已不再具有查询缓存意义，部分版本会产生警告，因此更推荐使用 EXPLAIN ANALYZE：

sql
-- MySQL 8.0.18+：查看真实执行耗时、实际行数与循环次数
EXPLAIN ANALYZE
SELECT id, order_no, amount, created_at
FROM t_order FORCE INDEX (idx_status_created)
WHERE status = 'REFUND'
ORDER BY created_at DESC
LIMIT 20;

如果需要更稳妥的写法，可以先使用 USE INDEX 观察优化器是否接受；只有确认该索引在所有主要业务分布下都更优，再升级为 FORCE INDEX。

JOIN 顺序干预：不要让小表判断停留在经验层面 ​

多表关联时，优化器还可能选错驱动表。原则上，驱动表应经过过滤后结果集较小，并能让被驱动表通过索引做 ref 或 eq_ref 查询。可以用 STRAIGHT_JOIN 强制按 FROM 子句顺序关联：

sql
-- 先过滤买家，再关联订单；适用于买家过滤结果很小、订单关联索引明确的场景
EXPLAIN
SELECT STRAIGHT_JOIN o.id, o.order_no, o.amount, o.created_at
FROM t_buyer b
JOIN t_order o
  ON o.buyer_id = b.id
 AND o.status = 'REFUND'
WHERE b.vip_level = 3
ORDER BY o.created_at DESC
LIMIT 20;

但 STRAIGHT_JOIN 会同时冻结所有表的连接顺序，风险比单表索引 HINT 更大。MySQL 8.0 更推荐使用索引级 HINT，或通过调整索引、更新统计信息解决。若必须使用，应在 SQL 旁注明数据依据，例如“买家过滤后约 50 行，订单可命中 idx_buyer_status”。

避坑总结：HINT 是止血钳，不是长期替代品 ​

坑 1：索引改名或删除后，FORCE INDEX 直接报错 ​

常见错误现象是发布索引变更后，业务 SQL 抛出 Key 'idx_xxx' doesn't exist in FORCE/index clause。这是因为索引 HINT 中的名称是硬编码，不会随索引重建自动更新。

修正方法：把 HINT SQL 纳入数据库变更清单；索引改名采用“新建索引、验证、切换、删除旧索引”的流程；上线前用 EXPLAIN 检查而不是只验证语法。

坑 2：只看 rows 不看回表成本，强制索引反而更慢 ​

二级索引查到主键后还要回表取 order_no、amount 等字段。若条件命中大量行，随机回表成本可能高于顺序全表扫描。错误表现是 key 看似正确，但响应时间更长，EXPLAIN ANALYZE 中实际循环行数很高。

修正方法：尽量设计覆盖索引，或缩小过滤范围。例如将查询字段收敛，并评估是否需要把 amount 纳入索引，但也要控制索引宽度：

sql
-- 该索引可支持状态过滤、时间排序，并覆盖 buyer_id；是否加入更多字段需权衡写入成本
ALTER TABLE t_order
ADD INDEX idx_status_created_buyer (status, created_at, buyer_id);

坑 3：参数从“高选择性常量”变成“低选择性常量”，HINT 固化坏计划 ​

今天 status = 'REFUND' 很少，FORCE INDEX(status, created_at) 很快；明天运营查询 status = 'PAID' 却复用了同一段 SQL 模板，导致扫描 10 万行并大量回表。

修正方法：不要对所有参数值无差别套 HINT。应为低频状态与高频状态拆分查询路径，或基于业务场景固定 SQL；同时记录不同参数的 EXPLAIN ANALYZE 结果。

坑 4：统计信息过期时只改 SQL，不处理根因 ​

批量导入、大版本升级、主备切换后，优化器可能因采样变化选择不同计划。仅靠 HINT 虽然能短期恢复，却会掩盖索引基数异常。

修正方法：先执行 ANALYZE TABLE t_order;，再复查计划；核心表在大批量数据变更后纳入统计信息维护窗口。若计划仍频繁抖动，再考虑 HINT 或绑定执行计划。

坑 5：把 STRAIGHT_JOIN 当作万能提速开关 ​

很多老 SQL 习惯加 STRAIGHT_JOIN，但随着表数据量变化，原本的小表可能变成大表，关联顺序不再合理。表现为 SQL 在测试库很快，生产库随数据增长持续变慢。

修正方法：只在明确驱动表过滤基数、被驱动表关联索引和排序方式后使用；优先调整索引和统计信息；保留未加 HINT 的版本，定期对比优化器自主选择是否已经更优。

最终建议是：执行计划干预要遵循“先解释计划，再证明成本，最后强制路径”的顺序。FORCE INDEX 可以作为故障止血手段，但每次使用都应附带原因、验证数据、适用参数范围和回滚方案，否则今天救下的慢查询，可能会成为明天数据增长后的新故障。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。