MySQL optimizer hint 和 FORCE INDEX 怎么选择

MySQL 里看到执行计划没有走预期索引时，先不要直接把 FORCE INDEX 填进 SQL。更稳妥的顺序是：先用 EXPLAIN 确认优化器为什么选当前路径，再按影响范围选择传统 index hint；如果只想约束某个访问阶段或索引级行为，再使用 /*+ ... */ 形式的 optimizer hint。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html

先用 EXPLAIN 判断问题是不是“选错索引”

假设订单表同时有 idx_user_status_created(user_id,status,created_at) 和 idx_status_created(status,created_at)，查询只看某个用户最近的已支付订单：

-- 先观察优化器的自然选择，不急着添加 hint
EXPLAIN SELECT id, created_at, amount
FROM orders
WHERE user_id = 10086
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

先看四个字段：possible_keys 是候选范围，key 是实际使用的索引，rows 是估算扫描行数，Extra 用来观察是否需要额外排序或回表。若统计信息陈旧、过滤条件选择性变化或数据分布很偏，强制一个索引可能只是把今天的偶然结果写死。

USE INDEX 与 FORCE INDEX 的差别在于约束强度

USE INDEX 表示只在指定索引中选择；它仍然允许优化器在这些候选索引之间权衡。FORCE INDEX 的语义更强：它类似 USE INDEX，但把全表扫描当成非常昂贵，只有找不到可用的指定索引时才考虑表扫描。

-- 温和限制：只让 JOIN 阶段在两个索引里选择
SELECT id, amount
FROM orders USE INDEX FOR JOIN (idx_user_status_created, idx_status_created)
WHERE user_id = 10086 AND status = 'paid';

-- 明确排除一个已知不合适的索引
SELECT id, amount
FROM orders IGNORE INDEX FOR JOIN (idx_status_created)
WHERE user_id = 10086 AND status = 'paid';

-- 只有证据充分、且确实不能接受表扫描时才使用强约束
SELECT id, amount
FROM orders FORCE INDEX FOR JOIN (idx_user_status_created)
WHERE user_id = 10086 AND status = 'paid';

选择可以按下面的顺序落地：

需要精确作用域时使用 optimizer hint

optimizer hint 写在 SELECT 等语句关键字之后的 /*+ ... */ 中，可以作用于语句、查询块、表或索引。MySQL 8.4 手册列出了 JOIN_INDEX、GROUP_INDEX、ORDER_INDEX 等索引级 hint，它们适合把访问、分组、排序的控制拆开。

-- 只控制 orders 表的连接访问索引，不干预排序策略
SELECT /*+ JOIN_INDEX(orders idx_user_status_created) */
       id, created_at, amount
FROM orders
WHERE user_id = 10086 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

这里的表名必须与语句中的引用一致；如果用了别名，hint 要写别名。不要在同一个查询块堆叠相互冲突的 hint，也不要把 optimizer hint 当作“永远使用这个索引”的保证：启用某种优化只代表允许优化器采用，实际是否采用仍要看执行计划。

用 EXPLAIN 和 SHOW WARNINGS 做一次可回退验证

不要只看 SQL 能否执行。把自然计划、加入 hint 的计划和去掉 hint 的计划并排记录，至少确认实际 key 是否变化、估算 rows 是否下降、是否出现额外排序，以及真实业务参数下的耗时是否稳定。

-- EXPLAIN 可以直接观察 hint 对计划的影响
EXPLAIN SELECT /*+ JOIN_INDEX(orders idx_user_status_created) */
       id, created_at, amount
FROM orders
WHERE user_id = 10086 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

-- 紧接 EXPLAIN 查看 MySQL 识别和采用了哪些 hint
SHOW WARNINGS;

如果 hint 被忽略，先检查索引名是否写对、表别名是否一致、hint 位置是否位于语句关键字之后，再判断约束本身是否与 JOIN 或排序语义冲突。生产变更建议先灰度一小组参数，并保留删除 hint 的版本；尤其是 MySQL 8.4 已提示传统 USE INDEX、FORCE INDEX、IGNORE INDEX 未来可能弃用，长期维护应优先采用明确且可拆分的 optimizer hint。

常见问题

FORCE INDEX 会不会保证一定走指定索引？

不会。它强烈提高表扫描的代价假设，但如果指定索引无法用于当前条件，MySQL 仍可能选择其他可行路径或表扫描。

USE INDEX 应该写列名还是索引名？

写索引名，不是列名。主键名称使用 PRIMARY，可用 SHOW INDEX 查看实际名称。

为什么加了 hint，执行计划几乎没变化？

可能是自然计划本来就选了同一索引，也可能是 hint 作用域不匹配或被忽略。用 EXPLAIN 后紧接 SHOW WARNINGS 查看识别结果，再回到实际参数做耗时对比。