什么是FORCE INDEX？实战中如何用它优化慢SQL？

面试场景

面试官：”你在项目中做过SQL优化吗？能举个有亮点的例子吗？”

回答SQL优化问题，最忌讳说”给字段加索引”这类烂大街的答案。高分回答需要：

1. 有具体的业务场景
2. 说明慢在哪里（执行计划分析）
3. 为什么慢（深层原因）
4. 如何优化（解决方案）
5. 效果如何（可量化的结果）

---

业务场景

某人口档案管理系统，需要查询”全市在世的、年龄最大的100个张姓居民”。

表结构（500万+数据量）：

CREATE TABLE person (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,        -- 有索引 idx_name_age(name, age)
  age SMALLINT NOT NULL,            -- 有索引 idx_age_name(age, name)
  status TINYINT NOT NULL,          -- 0:在世 1:不在世
  -- 其他字段略
  KEY idx_name_age (name, age),
  KEY idx_age_name (age, name)
);

业务SQL：

SELECT * FROM person 
WHERE name LIKE '张%' AND status = 0 
ORDER BY age DESC 
LIMIT 100;

问题：执行耗时 12秒！

---

问题分析

执行计划

EXPLAIN SELECT * FROM person WHERE name LIKE '张%' AND status = 0 ORDER BY age DESC LIMIT 100;

type	key	rows	Extra
index	idx_age_name	5800000	Using where

问题所在

1. type = index：全索引扫描，遍历整个索引树
2. 使用了 idx_age_name：MySQL选择先按age排序，再过滤name

实际执行过程：

Step 1: 通过 idx_age_name 按 age 降序遍历
Step 2: 对每行检查 name LIKE '张%' 是否匹配
Step 3: 回表检查 status = 0
Step 4: 满100条后返回

由于”张姓”只占总人口10%左右，这种策略需要遍历大量不匹配的数据。

---

优化思路

换一个角度思考

如果先过滤name，可以直接排除90%的数据：

-- 查看过滤效果
SELECT COUNT(*) FROM person WHERE name LIKE '张%' AND status = 0;
-- 结果：52万条，只占总数据的10%

如果使用 idx_name_age(name, age) 索引：

Step 1: 通过前缀匹配 name LIKE '张%' 快速定位
Step 2: 在匹配结果内按 age 排序（索引第二列）
Step 3: 回表过滤 status = 0，取100条

---

解决方案：FORCE INDEX

SELECT * FROM person 
FORCE INDEX (idx_name_age)
WHERE name LIKE '张%' AND status = 0 
ORDER BY age DESC 
LIMIT 100;

优化结果

优化前	优化后	提升
12秒	1.5秒	8倍

---

FORCE INDEX 深度解析

语法

SELECT ... FROM table_name FORCE INDEX (index_name) WHERE ...;

使用场景

场景	说明
优化器选错索引	本例：优化器误判LIKE性能差
统计信息不准	表数据变化大，统计信息未及时更新
复杂多表JOIN	优化器难以准确评估各索引成本

注意事项

1. 不保证100%生效：指定的索引必须在候选索引中
2. 慎用：会绕过优化器的智能选择
3. 治标方案：长期应考虑优化索引设计

---

为什么MySQL会选错索引？

优化器的决策依据

MySQL优化器基于成本模型选择执行计划，考虑因素：

• 索引扫描行数预估
• 回表成本
• 排序成本

本例的误判原因

1. 优化器看到 ORDER BY age DESC，认为用 idx_age_name 可以避免排序
2. 优化器对 LIKE '张%' 的过滤效果预估不准
3. 最终选择了全索引扫描，期望通过索引有序避免排序

实际情况：避免排序的收益 < 过滤90%数据的收益

---

相关优化技巧

查看优化器成本

EXPLAIN FORMAT=JSON SELECT ...;

{
  "query_cost": "1234567.00",  // 查看不同方案的cost
  "rows_examined_per_scan": 5800000,
  "access_type": "index"
}

优化器提示（MySQL 8.0+）

SELECT /*+ INDEX(person idx_name_age) */ * 
FROM person 
WHERE name LIKE '张%' AND status = 0 
ORDER BY age DESC LIMIT 100;

更新统计信息

ANALYZE TABLE person;

---

面试答题框架

业务背景：人口系统，500万数据，查询张姓+年龄最大+100条
问题现象：执行12秒
执行计划：type=index，用了idx_age_name，全索引扫描
根因分析：优化器误判，先排序后过滤效率低
解决方案：FORCE INDEX强制使用idx_name_age
优化效果：12秒→1.5秒，提升8倍
深层理解：成本模型对LIKE过滤效果预估不准

---

总结

要点	说明
FORCE INDEX作用	强制MySQL使用指定索引
适用场景	优化器选错索引时的应急手段
核心原理	绕过成本模型，人工指定执行路径
使用原则	先分析执行计划，确认确实选错才用
替代方案	Optimizer Hints（8.0+）、优化索引设计