查询优化器提示

导学

EXPLAIN 发现优化器走了全表扫描，而你明明有合适的索引？或者优化器选择了错误的 JOIN 顺序，导致查询慢如蜗牛？MySQL 5.6+ 提供了Optimizer Hints（优化器提示），让你像给 GPS 手动输入路线一样，告诉优化器"请走这条路"。同时，Index Hints（USE INDEX、FORCE INDEX、IGNORE INDEX）提供了更直接的索引干预手段。两者配合使用，是 SQL 调优的最后手段。

定义

Optimizer Hints：MySQL 5.6.5+ 引入的 SQL 注释语法，以 /*+ ... */ 形式嵌入在 SQL 语句中，指导优化器选择特定的执行策略（如索引、JOIN 顺序、子查询物化等）。Index Hints：更早期的语法（USE INDEX、FORCE INDEX、IGNORE INDEX），直接指定或排除某个索引。

核心语法

Optimizer Hints（5.6.5+）

SELECT /*+ INDEX(employees idx_dept) */ * FROM employees WHERE dept = '技术部';
SELECT /*+ JOIN_ORDER(employees, departments) */ * FROM employees e JOIN departments d ON e.dept_id = d.id;
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM employees WHERE score > 90;

Index Hints（所有版本）

-- 建议优化器使用某个索引（优化器可拒绝）
SELECT * FROM employees USE INDEX (idx_dept) WHERE dept = '技术部';

-- 强制优化器使用某个索引（不能用其他索引，但全表扫描仍可能）
SELECT * FROM employees FORCE INDEX (idx_dept) WHERE dept = '技术部';

-- 忽略某个索引（优化器不会考虑它）
SELECT * FROM employees IGNORE INDEX (idx_emp_name) WHERE emp_name = '大翔';

SQL 示例

场景一：Optimizer Hint 强制使用索引

演示数据准备：

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    INDEX idx_dept (dept),
    INDEX idx_score (score)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL),
('孔蓝', '产品部', 88),
('赵鸣', '运营部', 76);

当前 employees 表中的完整数据如下：

执行语句：

-- 不加 Hint，查看优化器选择
EXPLAIN SELECT * FROM employees WHERE dept = '技术部';

-- 加 Optimizer Hint 强制使用 idx_dept
EXPLAIN SELECT /*+ INDEX(employees idx_dept) */ * FROM employees WHERE dept = '技术部';

操作后结果（EXPLAIN 对比）：

不加 Hint：

加 Hint：

结果解读：

• 数据量只有 4 行时，优化器可能认为全表扫描（type = ALL）比索引查找更快
• /*+ INDEX(employees idx_dept) */ 强制优化器使用 idx_dept 索引
• 生产环境数据量大时，优化器通常会自动选择索引；Hint 只在优化器判断失误时使用
• 注意：Hint 是建议而非强制（Optimizer Hint），优化器在某些情况下仍会忽略

场景二：FORCE INDEX 强制使用索引（更强力）

当前数据状态：见上文 employees 表。

执行语句：

-- FORCE INDEX 强制使用指定索引，优化器不能选择其他索引或全表扫描
EXPLAIN SELECT * FROM employees FORCE INDEX (idx_dept) WHERE dept = '技术部';

操作后结果：

与 USE INDEX 对比：

-- USE INDEX 只是建议，优化器可以拒绝
EXPLAIN SELECT * FROM employees USE INDEX (idx_dept) WHERE dept = '技术部';

-- IGNORE INDEX 排除某个索引
EXPLAIN SELECT * FROM employees IGNORE INDEX (idx_score) WHERE score > 80;

结果解读：

• USE INDEX：建议优化器从列出的索引中选择，但优化器仍可全表扫描或选其他索引
• FORCE INDEX：强制使用列出的索引之一，优化器不能全表扫描（除非条件完全无法使用该索引）
• IGNORE INDEX：告诉优化器完全不要考虑列出的索引
• FORCE INDEX 比 Optimizer Hint 的 INDEX() 更强硬，但风险也更大——如果索引真的不合适，查询性能会急剧下降

场景三：Optimizer Hint 控制 JOIN 顺序

演示数据准备：

-- 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(20),
    location VARCHAR(20)
);

INSERT INTO departments VALUES
(1, '技术部', '广州'),
(2, '产品部', '深圳'),
(3, '运营部', '北京');

-- 员工表已有数据

执行语句：

-- 查看优化器默认的 JOIN 顺序
EXPLAIN SELECT * FROM employees e JOIN departments d ON e.dept = d.dept_name;

-- 强制 JOIN 顺序：先 departments，再 employees
EXPLAIN SELECT /*+ JOIN_ORDER(departments, employees) */ 
    * FROM employees e JOIN departments d ON e.dept = d.dept_name;

操作后结果（示例对比）：

默认 JOIN 顺序：

强制 JOIN 顺序后：

结果解读：

• JOIN_ORDER(table1, table2, ...) 强制优化器按指定顺序执行 JOIN
• 通常小表驱动大表性能更好（departments 3 行 vs employees 4 行）
• 但现代优化器的代价模型通常能做出正确选择，JOIN ORDER Hint 只在优化器明显失误时使用
• 注意：Hint 中的表名是查询中使用的别名或表名，不是数据库中的实际表名

场景四：限制最大执行时间（防止慢查询拖垮系统）

当前数据状态：基于 employees 表。

执行语句：

-- 限制查询最多执行 1 秒，超时自动报错终止
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM employees WHERE score > 80;

操作后结果：

正常执行时返回结果；如果查询超过 1000 毫秒：

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

结果解读：

• MAX_EXECUTION_TIME(N) 限制语句最大执行时间（毫秒），超时自动终止并返回错误
• 这是"熔断"机制，防止意外的大查询拖垮整个数据库
• 只对 SELECT 有效，INSERT/UPDATE/DELETE 不支持
• 适合在应用程序中作为兜底保护，或临时执行不确定耗时的分析查询时使用
• 5.7.8+ 支持，需要 MAX_EXECUTION_TIME 大于 0 才生效

场景五：控制子查询优化策略

执行语句：

-- 禁用 semijoin 优化（让子查询保持为子查询，不转为 JOIN）
EXPLAIN SELECT * FROM employees 
WHERE dept IN (SELECT dept_name FROM departments)
/*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ ;

-- 启用物化子查询（将子查询结果缓存为临时表）
EXPLAIN SELECT * FROM employees 
WHERE dept IN (SELECT dept_name FROM departments)
/*+ SUBQUERY(@subq1 MATERIALIZATION) */ ;

结果解读：

• MySQL 5.7 优化器会自动将某些子查询转为更高效的 JOIN（semijoin）或物化临时表
• NO_SEMIJOIN 禁用 semijoin 优化，强制按原始子查询执行
• SUBQUERY(MATERIALIZATION) 强制将子查询结果物化为临时表，适合子查询结果小、外层查询大的场景
• 子查询 Hint 需要配合查询块命名（@subq1），语法较复杂，日常调优较少使用

常见误区

面试考点

Q：Optimizer Hints 和 Index Hints 的区别？

Optimizer Hints（/*+ ... */）是 MySQL 5.6.5+ 引入的，语法更灵活，可以控制索引选择、JOIN 顺序、子查询策略、执行时间限制等，但只是建议，优化器可拒绝。Index Hints（USE/FORCE/IGNORE INDEX）语法更简单直接，FORCE INDEX 是强制性的，优化器不能选择其他索引或全表扫描（除非完全无法使用）。

Q：什么时候需要用 Hint？

1. 优化器统计信息过期，选择了明显错误的执行计划（如大表全表扫描）；2. 数据分布特殊，优化器的代价模型判断失误；3. 紧急线上问题，需要快速干预执行计划；4. 限制查询最大执行时间防止拖垮系统。但 Hint 是临时手段，长期应通过 ANALYZE TABLE 更新统计信息、优化索引、改写 SQL 来解决。

Q：USE INDEX 和 FORCE INDEX 的区别？

USE INDEX 是建议优化器从列出的索引中选择，但优化器仍可全表扫描或选其他索引；FORCE INDEX 是强制使用列出的索引之一，优化器不能全表扫描（除非条件完全无法匹配该索引）。FORCE INDEX 更强硬但风险更大。

Q：MAX_EXECUTION_TIME 的作用和限制？

限制 SELECT 语句的最大执行时间（毫秒），超时自动终止并返回 ERROR 3024。是防止慢查询拖垮系统的"熔断"机制。只支持 SELECT，不支持 DML。需要 MySQL 5.7.8+。

Q：Hint 写在 SQL 的什么位置？

Optimizer Hints 写在 SELECT/UPDATE/DELETE/INSERT 关键字之后，如 SELECT /*+ INDEX(t idx) */ ...。Index Hints 写在表名之后，如 FROM table_name USE INDEX (idx) WHERE ...。

小结

• Optimizer Hints（/*+ ... */）是建议性提示，控制索引选择、JOIN 顺序、子查询策略、执行时间等
• Index Hints（USE/FORCE/IGNORE INDEX）更直接，FORCE INDEX 强制使用指定索引
• MAX_EXECUTION_TIME(N) 是 SELECT 的熔断保护，防止慢查询拖垮系统
• Hint 是 SQL 调优的最后手段，不能替代索引优化和 SQL 改写
• 过度使用 Hint 会导致执行计划僵化，数据分布变化后可能性能倒退

下一章引子：Hint 干预了单条 SQL 的执行计划，而 FLUSH 和 RESET 语句则是运维层面的"刷新按钮"——清空缓存、重置统计、重新加载权限，是日常运维和故障排查的常用工具。