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 缩小作用范围。没有作用域时,应注意提示可能影响多个优化阶段,避免把实验范围放得过大。
用计划对比验证假设
下面建立一个可复现表,并对同一条查询分别查看默认计划、强制索引计划和排除索引计划。实验重点不是“哪条语法更强”,而是比较成本、扫描行数和排序代价是否真的改善。
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;预期结果
建表、插入和统计信息更新均成功完成。
默认计划由优化器自行选择访问路径。
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 实验,尤其是在需要把提示范围限制到特定表或查询块时。
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;预期结果
如果提示语法、表名和索引名都有效,执行计划会按 INDEX 提示约束索引选择。
若提示无效、冲突或无法应用,SHOW WARNINGS 可能给出相关提示信息;具体内容显示为 <VALUE>。INDEX(table index) 不应简单等同于传统 USE INDEX 或 FORCE INDEX。两者属于不同语法体系,具体生效方式应以实际执行计划和 SHOW WARNINGS 为准;涉及版本差异时,也不要只凭提示名称判断强度。
把提示当作诊断证据
提示最有价值的用法是构造 A/B 计划。如果强制某索引后实际耗时显著下降,可以进一步检查统计信息是否陈旧、基数估算是否失真、谓词是否不利于索引、联合索引列顺序是否不合理,或者参数分布是否高度倾斜。
长期保留提示前要评估数据增长、分布变化、索引调整和 MySQL 升级。今天被强制的索引可能在半年后成为较差选择;提示越具体,未来优化器自行适应变化的空间就越小。
什么时候可以保留提示
提示并非绝对不能进生产。对于已经验证过的计划回归、短期版本缺陷绕行,或必须在明确窗口内稳定执行的关键 SQL,可以把提示作为有监控、有退出条件的临时措施。
保留前至少记录默认计划与提示计划的 EXPLAIN ANALYZE、触发问题的数据分布、预期移除条件以及回滚方式。只要能够通过更新统计信息、改写 SQL 或建立更合适的索引让默认计划恢复稳定,就应优先移除提示,让成本模型重新承担选择职责。