MySQL / 系统课时

谨慎使用索引与优化器提示

谨慎使用索引与优化器提示

当优化器选出意外计划时,提示语法可以快速验证“如果换索引或换连接顺序会怎样”。但提示会把 SQL 与当前数据分布、索引结构和版本行为绑定起来,因此更适合作为诊断工具,而不是长期替代统计信息和索引设计。

学习目标

  • 区分 USE INDEX、FORCE INDEX、IGNORE INDEX 对候选访问路径的影响
  • 会用 optimizer hints 做单条 SQL 的受控计划实验
  • 能判断什么时候应修复根因,而不是把某个偶然好计划永久固定

先分清三种索引提示

传统索引提示写在表名之后。它们都会影响优化器可选的索引集合,但作用强度不同。

提示 主要作用 典型用途
USE INDEX 指定可优先考虑的索引范围,但优化器仍可选择表扫描 验证某组索引是否足够
FORCE INDEX 提高指定索引相对于表扫描的优先级;指定索引不可用时仍可能退回表扫描 验证“若优先走这个索引”是否更合适
IGNORE INDEX 从该语句的索引候选中排除指定索引 验证某索引是否导致错误计划

还可以用 FOR JOIN、FOR ORDER BY、FOR GROUP BY 缩小作用范围。没有作用域时,应注意提示可能影响多个优化阶段,避免把实验范围放得过大。

用计划对比验证假设

下面建立一个可复现表,并对同一条查询分别查看默认计划、强制索引计划和排除索引计划。实验重点不是“哪条语法更强”,而是比较成本、扫描行数和排序代价是否真的改善。

sql
DROP TABLE IF EXISTS orders_hint_demo;

CREATE TABLE orders_hint_demo (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  customer_id BIGINT NOT NULL,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_customer (customer_id),
  INDEX idx_status_created_at (status, created_at)
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 20000;

INSERT INTO orders_hint_demo (customer_id, status, created_at)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT
  n % 1000,
  CASE WHEN n % 100 = 0 THEN 'PAID' ELSE 'NEW' END,
  TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n MINUTE
FROM seq;

ANALYZE TABLE orders_hint_demo;

EXPLAIN FORMAT=TREE
SELECT id, created_at
FROM orders_hint_demo
WHERE status = 'PAID'
ORDER BY created_at
LIMIT 20;

EXPLAIN FORMAT=TREE
SELECT id, created_at
FROM orders_hint_demo FORCE INDEX (idx_status_created_at)
WHERE status = 'PAID'
ORDER BY created_at
LIMIT 20;

EXPLAIN FORMAT=TREE
SELECT id, created_at
FROM orders_hint_demo IGNORE INDEX (idx_status_created_at)
WHERE status = 'PAID'
ORDER BY created_at
LIMIT 20;

预期结果

text
建表、插入和统计信息更新均成功完成。
默认计划由优化器自行选择访问路径。
FORCE INDEX 版本会优先使用 idx_status_created_at,前提是该索引对语句可用。
IGNORE INDEX 版本不会使用 idx_status_created_at,可能改用其他索引或表扫描,并可能增加排序代价。
成本、rows 等估算值取决于统计信息,具体数值显示为 <VALUE>。

如果 FORCE INDEX 后反而更慢,说明“更匹配谓词”不等于真实成本更低。判断提示是否有价值,应继续用 EXPLAIN ANALYZE 比较实际行数和耗时,而不是只看 key 名称。

optimizer hints 适合更精确的实验

MySQL 的 optimizer hints 写在 SELECT 后的注释中,可以针对特定优化策略提供更细粒度的控制。它们适合做单条 SQL 的 A/B 实验,尤其是在需要把提示范围限制到特定表或查询块时。

sql
EXPLAIN FORMAT=TREE
SELECT /*+ INDEX(orders_hint_demo idx_status_created_at) */
       id, created_at
FROM orders_hint_demo
WHERE status = 'PAID'
ORDER BY created_at
LIMIT 20;

SHOW WARNINGS;

预期结果

text
如果提示语法、表名和索引名都有效,执行计划会按 INDEX 提示约束索引选择。
若提示无效、冲突或无法应用,SHOW WARNINGS 可能给出相关提示信息;具体内容显示为 <VALUE>。

INDEX(table index) 不应简单等同于传统 USE INDEX 或 FORCE INDEX。两者属于不同语法体系,具体生效方式应以实际执行计划和 SHOW WARNINGS 为准;涉及版本差异时,也不要只凭提示名称判断强度。

把提示当作诊断证据

提示最有价值的用法是构造 A/B 计划。如果强制某索引后实际耗时显著下降,可以进一步检查统计信息是否陈旧、基数估算是否失真、谓词是否不利于索引、联合索引列顺序是否不合理,或者参数分布是否高度倾斜。

长期保留提示前要评估数据增长、分布变化、索引调整和 MySQL 升级。今天被强制的索引可能在半年后成为较差选择;提示越具体,未来优化器自行适应变化的空间就越小。

什么时候可以保留提示

提示并非绝对不能进生产。对于已经验证过的计划回归、短期版本缺陷绕行,或必须在明确窗口内稳定执行的关键 SQL,可以把提示作为有监控、有退出条件的临时措施。

保留前至少记录默认计划与提示计划的 EXPLAIN ANALYZE、触发问题的数据分布、预期移除条件以及回滚方式。只要能够通过更新统计信息、改写 SQL 或建立更合适的索引让默认计划恢复稳定,就应优先移除提示,让成本模型重新承担选择职责。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

谨慎使用索引与优化器提示

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通