"这个字段明明建了索引，为什么还是慢？"答案通常不在索引本身，而在优化器算出的成本与SQL 的写法这两件事上：前者是它主动不走索引，后者是索引根本用不上，改法完全不同。

---

1. 先把"失效"分成两类

类别	本质	典型信号	改法方向
用不上	SQL 写法让索引无法定位	key 为 NULL，type 为 ALL	改写条件、调整索引列顺序
主动不用	优化器算出全表扫更便宜	possible_keys 有值但 key 为空	更新统计信息、覆盖索引、必要时 FORCE INDEX
半失效	用上了但只用于过滤而非定位	type=range 且 rows 很大	调整复合索引顺序、加覆盖列

possible_keys 有值而 key 为空是第二类；连 possible_keys 都没有，才是真正的"用不上"。

---

2. 八种情形总表

#	情形	机制	典型写法	改法
1	索引列上做函数/运算	索引存的是列值，不是函数值	where date(created_at)='2026-11-09'	改范围条件 >= ... and < ...
2	隐式类型转换	字符串列被转成数字比较	where order_no=123456（列是 varchar）	参数加引号，保持类型一致
3	前导模糊	无法确定匹配前缀	like '%abc'、like '%abc%'	改后缀匹配或走全文/搜索引擎
4	复合索引不满足最左前缀	B+ 树按列顺序排列	索引 (a,b,c) 查 where b=1	调整索引列顺序或补索引
5	or 连接未全部建索引的列	两侧都要能用索引才能合并	where a=1 or d=2（d 无索引）	拆成 union all 或给 d 建索引
6	!= / not in / is not null	选择性差时全表扫更便宜	where status!='DONE'	改正向枚举、或确认选择性后再判断
7	范围查询之后的列失效	范围列之后无法继续定位	索引 (a,b,c)，where a=1 and b>2 and c=3	把等值列放前，范围列放最后
8	排序与索引顺序不一致	无法利用索引有序性	order by b 而索引是 (a,b) 且 a 未定值	让排序字段与索引前缀一致

这八条都不是"见到就判死刑"，每条都要用 EXPLAIN 验证。

---

3. 逐条验证

CREATE TABLE t_order (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_user_status_time (user_id, status, created_at),
  KEY idx_order_no (order_no)
) ENGINE=InnoDB;

3.1 函数与运算

-- 失效：索引列被函数包裹
EXPLAIN SELECT * FROM t_order WHERE DATE(created_at) = '2026-11-09';
-- type=ALL, key=NULL, rows 接近全表

-- 有效：把函数移到常量侧
EXPLAIN SELECT * FROM t_order
 WHERE created_at >= '2026-11-09 00:00:00'
   AND created_at <  '2026-11-10 00:00:00';

substring(...)、cast(...)、列间运算都属于同一类：索引列一旦出现在表达式里，就用不上这棵 B+ 树。

3.2 隐式类型转换

-- 失效：order_no 是 varchar，右边是数字
EXPLAIN SELECT * FROM t_order WHERE order_no = 20261109001;
-- 实际执行的是 CAST(order_no AS DOUBLE) = 20261109001

-- 有效：两边都是字符串
EXPLAIN SELECT * FROM t_order WHERE order_no = '20261109001';

这条最容易忽略：结果是对的，只是慢。MyBatis 里 #{orderNo} 传 Long 就会踩到。

3.3 前导模糊

EXPLAIN SELECT * FROM t_order WHERE order_no LIKE '%0901';   -- ALL
EXPLAIN SELECT * FROM t_order WHERE order_no LIKE '2026%';   -- range

like 'x%' 前缀确定，能在 B+ 树上做区间扫描；%x 与 %x% 无起点，只能逐行比。需要中缀搜索就走全文索引或 ES。

3.4 最左前缀

-- 索引 (user_id, status, created_at)
EXPLAIN SELECT * FROM t_order WHERE user_id=9 AND status=1;          -- ref，用上前两列
EXPLAIN SELECT * FROM t_order WHERE status=1;                        -- ALL，跳过最左列
EXPLAIN SELECT * FROM t_order WHERE user_id=9 AND created_at>'2026-01-01'; -- range，只用 user_id

第三行：status 无等值条件，B+ 树无法按 created_at 连续扫描，只能扫完该 user_id 下所有 status。

3.5 or 与索引合并

-- 一侧无索引：整体退化为全表扫
EXPLAIN SELECT * FROM t_order WHERE user_id=9 OR amount>100;

-- 两侧都有索引：可能走 index_merge
EXPLAIN SELECT * FROM t_order WHERE user_id=9 OR order_no='20261109001';

-- 更稳的写法：拆开合并（两侧各自走自己的索引）
SELECT * FROM t_order WHERE user_id=9
UNION ALL
SELECT * FROM t_order WHERE order_no='20261109001';

index_merge 要做交集/并集，回表次数可能比全表扫更多，优化器常宁可不选。

3.6 !=、not in、is not null

EXPLAIN SELECT * FROM t_order WHERE status != 3;        -- 常见 ALL
EXPLAIN SELECT * FROM t_order WHERE status IN (1,2);    -- range
EXPLAIN SELECT * FROM t_order WHERE status IS NOT NULL; -- 通常 ALL

!= 不必然失效：status != 3 而 3 占 95% 时仍可能走索引，依据是 rows 与选择性；IS NOT NULL 在列基本非空时反而不如全表扫。

3.7 范围之后的列

-- 索引 (user_id, status, created_at)
EXPLAIN SELECT * FROM t_order
 WHERE user_id=9 AND status>0 AND created_at>'2026-01-01';
-- key=idx_user_status_time, 但 created_at 只参与过滤（Using index condition）

范围列之后的列不能用于定位，但能用于过滤（ICP）与覆盖；要让 created_at 定位，只能把范围列放最后。

3.8 排序与索引顺序

EXPLAIN SELECT * FROM t_order WHERE user_id=9 ORDER BY created_at;   -- Using filesort
EXPLAIN SELECT * FROM t_order WHERE user_id=9 ORDER BY status, created_at; -- 与索引顺序一致

WHERE user_id=9 固定最左列后，ORDER BY status, created_at 正好是索引后续顺序，可直读有序结果；只按 created_at 排会断层，必须 filesort。

---

4. EXPLAIN 读四个字段就够

字段	看什么	误判风险
type	访问方式，ALL 最差，const/eq_ref/ref/range 依次可用	只看 type 不看 rows 会漏掉"走了索引但扫了很多行"
key	实际使用的索引	为空不代表没索引，可能是主动不用
rows	预估扫描行数	估算值受统计信息影响，可能与实际差一个量级
Extra	Using index、Using index condition、Using filesort、Using temporary	有 Using index 才是不回表

-- 优化器为什么不用索引：看它算了什么
SET optimizer_trace='enabled=on';
SELECT * FROM t_order WHERE status != 3;
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace='enabled=off';

optimizer_trace 里看 rows_estimation 与 considered_access_paths：前者是各路径的行数估算，后者说明它为何选了 table_scan。估算与实际差得远，问题在统计信息。

---

5. 覆盖索引与索引条件下推能救哪些场景

Extra 显示	含义	代价
Using index	覆盖索引，查询列全在索引里，不回表	索引变宽，写入变慢
Using index condition	索引条件下推，在存储引擎层先过滤	仍需回表，但回表次数减少
Using where	服务层再过滤	说明部分条件没用上索引
Using filesort	额外排序	内存或磁盘排序
Using temporary	临时表（group by、distinct、union）	内存/磁盘临时表

-- 8.0 默认开启 ICP，可用开关确认影响
SET optimizer_switch='index_condition_pushdown=on';
EXPLAIN SELECT user_id, status, created_at FROM t_order
 WHERE user_id=9 AND status>0 AND created_at>'2026-01-01';
-- Extra: Using where; Using index  ← 三列都在索引里，不回表

把 SELECT * 改成只取索引里已有的列是最省事的一次提速。

---

6. 统计信息与 FORCE INDEX

ANALYZE TABLE t_order;                          -- 更新索引基数
SHOW INDEX FROM t_order;                        -- 看 Cardinality
ANALYZE TABLE t_order UPDATE HISTOGRAM ON status WITH 32 BUCKETS;  -- 8.0 直方图

现象	原因	处理
rows 估算比实际小一个量级	统计信息过期（大量写入后未 analyze）	ANALYZE TABLE，必要时加定时任务
两个索引基数接近，选了差的	估算偏差	直方图、改写 SQL、最后才 FORCE INDEX
昨天还快今天慢	数据分布变了，计划缓存/统计未更新	复跑 EXPLAIN 对比，不要凭记忆

SELECT * FROM t_order FORCE INDEX (idx_user_status_time)
 WHERE user_id=9 AND status=1 ORDER BY created_at LIMIT 20;

FORCE INDEX 边界很窄：确认估算失真、数据分布稳定时才用。它把计划钉死，分布一变就成了负担。

---

7. 排错与常见坑

现象	原因	处理
建了索引 key 仍为空	写法让索引用不上，或选择性太差	逐条对照第 2 节的八种情形
type=range 但很慢	范围过大，rows 几十万	收窄范围，或改成等值 + 覆盖索引
possible_keys 有多个，选了最差的	基数估算偏差	ANALYZE TABLE、直方图，再考虑 FORCE INDEX
分页越翻越慢	limit 100000,20 扫完再丢	改成按主键游标 where id > ?

常见坑：

坑	表现	正确做法
只看 type 不看 rows	以为走了索引就没问题	type=range + rows=500000 一样慢
加了索引没生效	区分度太低（如性别、状态）	用复合索引或改查询路径
把 != 一律当失效	误改 SQL、白费工夫	看选择性，用 EXPLAIN 验证
复合索引列顺序随便排	只有第一列被用上	等值列在前、范围列在后、排序列跟上
拿 EXPLAIN 当性能结论	估算不是实测	用 EXPLAIN ANALYZE（8.0.18+）看实际行数

---

8. 边界

场景	更合适的做法	理由
需要中缀/多词搜索	全文索引或 ES	B+ 树无法做中缀定位
实时聚合大宽表	预聚合表 / 列存 / OLAP	行存 + 索引不适合扫全列
超高并发点查	缓存挡一层	索引解决扫描量，不解决并发
数据量到单表上限	归档、分区、分表	索引减少扫描量，不减少总量
需要看真实执行过程	EXPLAIN ANALYZE、performance_schema	EXPLAIN 只有估算

---

9. 自测

1. possible_keys 有值而 key 为空，说明优化器在做什么判断？
2. where order_no = 20261109001（列是 varchar）为什么会慢，执行时发生了什么转换？
3. 索引 (user_id, status, created_at) 下，where user_id=9 and created_at>'2026-01-01' 用到几列，为什么？
4. Using index 与 Using index condition 的区别是什么？
5. type=range 且 rows 很大时，下一步该改 SQL 还是改索引？
6. 什么情况下才考虑 FORCE INDEX，用之前应先做什么？