📖 本篇简介：索引建了一大堆，查询却还是全表扫——问题往往不在"有没有索引"，而在"写 SQL 的方式让索引用不上"。本章汇总索引使用阶段最经典的 11 个坑，覆盖函数包裹、隐式转换、最左前缀、OR、前导通配、范围中断、回表、FORCE INDEX 与 IS NULL。示例基于 MySQL 8.0，并用 EXPLAIN 说明索引为何失效。读完你能写出"让优化器乖乖走索引"的 SQL。
目录
- 坑 1：对索引列包函数导致索引失效
- 坑 2：隐式类型转换让索引失效
- 坑 3：联合索引违反最左前缀
- 坑 4：用 OR 把索引搞失效
- 坑 5：LIKE 前导通配
- 坑 6：范围条件后面的列用不上联合索引
- 坑 7：索引列参与算术运算
- 坑 8：明明有索引却还在回表
- 坑 9：FORCE INDEX 用反了
- 坑 10：对 != / NOT IN 期望走索引
- 坑 11：以为 IS NOT NULL 一定走索引
- 坑 12：联合索引里把低区分度列放最左
- 速查表：索引失效场景速记
- 注意事项：EXPLAIN 关键字段怎么读
- 自测：你能避坑吗
坑 1：对索引列包函数导致索引失效
场景 / 错误示范：
-- create_time 上建了索引，却用 YEAR() 包裹它
SELECT * FROM orders
WHERE YEAR(create_time) = 2026;
-- EXPLAIN: type=ALL，全表扫！索引 idx_ct 完全没用上
为什么是坑：B+ 索引存的是列的原始值。一旦在列外套函数（YEAR、DATE、SUBSTRING、UPPER...），优化器无法直接用索引里的有序值去定位，必须把每一行都算一遍函数再比较，等于全表扫。索引是按"原值"排序的，你把它算成另一个值，排序关系就断了。
正确做法：
-- 把函数挪到右边：对常量侧计算，让左边保持裸列
SELECT * FROM orders
WHERE create_time >= '2026-01-01'
AND create_time < '2027-01-01';
-- EXPLAIN: type=range，正常走 idx_ct 索引
-- 其他常见改法：
-- 错误：WHERE DATE(create_time) = '2026-10-07'
-- 正确：WHERE create_time >= '2026-10-07' AND create_time < '2026-10-08'
加分点：通用原则——"索引列要裸着写，函数/运算都搬到等号右边"。8.0 有函数索引（CREATE INDEX ... ((YEAR(create_time)))）能救这种写法，但优先还是改写 SQL。
坑 2：隐式类型转换让索引失效
场景 / 错误示范：
-- phone 是 VARCHAR(20) 且有索引，查询却传了数字
SELECT * FROM user WHERE phone = 13800001111;
-- EXPLAIN: type=ALL，索引失效！
-- 本质：MySQL 把 phone 转成数字再比较，等价于
-- WHERE CAST(phone AS UNSIGNED) = 13800001111; -- 列上套了函数
为什么是坑：字符串列和数字比较时，MySQL 会把字符串列隐式转成数字（CAST(phone AS SIGNED)），等于在索引列上套了函数，索引立刻失效。这是最隐蔽的坑之一——SQL 看起来没毛病，EXPLAIN 才露馅。应用里"传参类型和列类型不一致"是高发原因。
正确做法：
-- 查询条件的字面量类型要和列类型一致
SELECT * FROM user WHERE phone = '13800001111'; -- 字符串加引号
-- EXPLAIN: type=ref，正常走 idx_phone
-- 根治：应用层参数类型与表结构对齐，VARCHAR 列就传字符串
加分点：经验法则——"字符串列必须用引号包起来查"。反过来数字列传字符串一般不会触发转换失效（MySQL 会把字符串转数字），但仍建议类型严格一致。
坑 3：联合索引违反最左前缀
场景 / 错误示范：
-- 建了联合索引 idx_a_b_c (a, b, c)
CREATE INDEX idx_a_b_c ON t(a, b, c);
-- 错误一：跳过 a 直接用 b
SELECT * FROM t WHERE b = 1 AND c = 2; -- a 没用上
-- 错误二：范围条件用在了 b，导致 c 用不上
SELECT * FROM t WHERE a = 1 AND b > 10 AND c = 5; -- c 用不上索引
为什么是坑：联合索引按 (a,b,c) 顺序排序，先按 a 排、a 相同再按 b 排。查询不从最左列 a 开始，索引就无从定位；一旦中间某列用了范围（>、<、between、like），它右边的列就无法再利用索引有序性。这就是"最左前缀"和"范围之后全失效"两条铁律。
正确做法：
-- 正确：从最左列开始，等值列在前、范围列放最后
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; -- 三列全用上
-- 如果业务就是要按 b、c 查，那就为它单独建索引
CREATE INDEX idx_b_c ON t(b, c);
-- 范围列放联合索引最后，别让它挡住后面的列
加分点：建联合索引时把"等值查询列"放前面、"范围查询列"放最后。判断走没走全列，看 EXPLAIN 的 key_len——用了几列、多长，一目了然。
坑 4：用 OR 把索引搞失效
场景 / 错误示范：
-- id 有索引、name 也有索引，但用 OR 连起来
SELECT * FROM user WHERE id = 1 OR name = '张三';
-- name 没索引时，OR 会逼优化器退化成全表扫
-- 更糟：OR 两边一边有索引一边没有
SELECT * FROM user WHERE id = 1 OR age = 20;
-- age 无索引 -> 整个查询 type=ALL
为什么是坑：OR 的语义是"满足任一即可"，优化器要保证两边条件都被高效过滤。只要 OR 中有一侧的列没有可用索引，它就无法走索引合并，干脆全表扫。很多人以为"id 有索引就能走"，其实 OR 是整体评估的。
正确做法：
-- 方案一：OR 的两边都建索引，让优化器用 index_merge
CREATE INDEX idx_age ON user(age);
-- 方案二：改成 UNION（各自走索引再合并结果）
SELECT * FROM user WHERE id = 1
UNION
SELECT * FROM user WHERE age = 20;
加分点：能用 IN 就别用 OR：WHERE id IN (1,2,3) 对索引友好得多。OR 频繁出现时，先 EXPLAIN 看是 index_merge 还是全表扫，别想当然。
坑 5：LIKE 前导通配
场景 / 错误示范：
-- name 有索引，却用前导通配
SELECT * FROM user WHERE name LIKE '%三';
SELECT * FROM user WHERE name LIKE '%三%';
-- EXPLAIN: type=ALL，索引失效
为什么是坑：B+ 索引按值从左到右排序。LIKE '三%' 能定位到以"三"开头的区间，可以走索引；LIKE '%三' 或 '%三%' 不知道以什么开头，无法利用索引的有序性，只能逐行扫。前导通配 = 放弃索引定位能力。
正确做法：
-- 正确：通配符放右边
SELECT * FROM user WHERE name LIKE '三%'; -- 走 idx_name
-- 必须做全文模糊搜索（如 '%手机%'）时，别靠 B+ 索引硬撑
-- 改用专门的全文索引 FULLTEXT 或外部搜索引擎（ES）
ALTER TABLE article ADD FULLTEXT INDEX ft_title(title);
SELECT * FROM article WHERE MATCH(title) AGAINST ('手机' IN NATURAL LANGUAGE MODE);
加分点：前缀索引（KEY(name(10))）和"通配符在右"是配套的。业务上的模糊搜索需求，超出 B+ 索引能力范围时，正确答案是上 ES，而不是让数据库扛。
坑 6：范围条件后面的列用不上联合索引
场景 / 错误示范：
-- 联合索引 idx(a, b, c)
EXPLAIN SELECT * FROM t
WHERE a = 1 AND b > 10 AND c = 5;
-- 实际只用到了 a 和 b，c 用不上（b 是范围，c 被"断"在后面）
为什么是坑：联合索引里，一旦某列出现范围条件（>、<、between、like 'x%'），它之后的列在索引里就不再是"连续有序"的，优化器无法继续用后面的列精确定位。很多人以为联合索引三列都写在 WHERE 里就都能用上，忽略了范围列会"截断"后面的列。
正确做法：
-- 把范围列放到联合索引的最后
CREATE INDEX idx_a_c_b ON t(a, c, b); -- a、c 等值在前，b 范围在后
SELECT * FROM t WHERE a = 1 AND c = 5 AND b > 10; -- 三列都用上
加分点：设计联合索引时，把"等值条件列"放前面、"范围条件列"放最后，是避免这个坑的根本。EXPLAIN 的 key_len 和 Extra 里的 "Using index condition" 能帮你确认到底用上了几列。
坑 7：索引列参与算术运算
场景 / 错误示范：
-- age 有索引
SELECT * FROM user WHERE age + 1 = 30;
-- EXPLAIN: type=ALL，索引失效
为什么是坑：和包函数同理——age + 1 后的值和索引里存的 age 原值不再一一对应有序，优化器无法用索引定位。这是坑 1 的近亲：只要索引列出现在运算符左边参与计算，索引就废。
正确做法：
-- 把运算挪到常量侧
SELECT * FROM user WHERE age = 30 - 1; -- 等价 age = 29，走索引
-- 其他常见：
-- 错误：WHERE id / 2 = 100
-- 正确：WHERE id = 100 * 2
加分点：口诀——"左边裸列，右边算账"。等号左边永远是索引列本身，任何函数、运算、类型转换都搬到右边去。
坑 8：明明有索引却还在回表
场景 / 错误示范：
-- idx_name(name) 单列索引
EXPLAIN SELECT * FROM user WHERE name = '张三';
-- type=ref 走了索引，但 Extra 里有 "Using where"，且 rows 不大却很慢
-- 原因：查到 name 后还要回主键索引取整行数据（回表）
为什么是坑：二级索引叶子存的是"索引列值 + 主键值"，用二级索引查到主键后，还要再回主键聚簇索引取整行数据，这就是回表。SELECT * 把所有列都要回来，回表次数 = 命中行数，几千次随机 IO 就慢了。索引用了，但效率没榨干。
正确做法：
-- 覆盖索引：把查询需要的列都塞进联合索引，无需回表
-- 只查 name 和 age，就建 (name, age)
ALTER TABLE user ADD INDEX idx_name_age(name, age);
EXPLAIN SELECT name, age FROM user WHERE name = '张三';
-- Extra 出现 "Using index" = 覆盖索引，不回表，快很多
加分点："Using index" 是覆盖索引的标志，面试高频考点。高频查询列设计成联合索引让它"覆盖"，是消除回表、把性能再提一档的常用手段。
坑 9：FORCE INDEX 用反了
场景 / 错误示范：
-- 优化器选错索引，开发直接加 FORCE INDEX
SELECT * FROM user FORCE INDEX(idx_age) WHERE name = '张三' AND age = 20;
-- 结果：idx_age 区分度极低（age 重复值多），强制它反而更慢
为什么是坑：FORCE INDEX 是"强制指定索引"，等于绕过优化器判断。数据量、数据分布变化后，当初对的索引可能不再最优，但 FORCE INDEX 把选择焊死了。更糟的是它会掩盖真实问题——根因通常是统计信息过期或索引设计不合理，FORCE 只是硬盖盖子。
正确做法：
-- 先 ANALYZE TABLE 更新统计信息，让优化器自己选对
ANALYZE TABLE user;
-- 真要干预，用 USE INDEX 给建议而非强制，仍允许优化器选别的
SELECT * FROM user USE INDEX(idx_name) WHERE name = '张三' AND age = 20;
-- 根治：重新设计更合适的联合索引 (name, age)，让优化器自然选对
加分点：FORCE INDEX 是应急手段不是常态。上线前 EXPLAIN 验证；优化器选错时，先查统计信息（SHOW INDEX FROM user 看 Cardinality），而不是第一反应加 FORCE。
坑 10：对 != / NOT IN 期望走索引
场景 / 错误示范：
-- status 有索引
SELECT * FROM order_list WHERE status != 1;
SELECT * FROM order_list WHERE status NOT IN (1, 2);
-- 以为 != 也能走索引，结果往往是全表扫
为什么是坑：!=、NOT IN、NOT EXISTS 是"排除性"条件，要找的是"不等于某值的所有行"，通常命中表中大部分数据。优化器估算后发现"走索引再回表"还不如直接全表扫便宜，就放弃索引。这不是索引失效，而是优化器基于成本的理性选择。
正确做法：
-- 接受现实：!= 命中大部分行时全表扫是合理的，别硬逼它走索引
-- 优化思路是缩小范围：
SELECT * FROM order_list
WHERE status != 1 AND create_time > '2026-01-01'; -- 加时间范围收窄
-- 或用覆盖索引避免回表
ALTER TABLE order_list ADD INDEX idx_status_ct(status, create_time);
加分点：判断"该不该走索引"看命中比例：命中很少（<10%）走索引划算；命中大半，全表扫反而更快。!= 天然命中多半表，别和优化器对着干。
坑 11：以为 IS NOT NULL 一定走索引
场景 / 错误示范：
-- phone 有索引且大量行为 NULL
SELECT * FROM user WHERE phone IS NOT NULL;
-- 期望走索引，实际却全表扫（因为大部分行 phone 都不是 NULL）
为什么是坑：MySQL 索引确实能用于 IS NULL / IS NOT NULL，但走不走还要看成本。如果表里大部分行 phone 都非 NULL，IS NOT NULL 命中绝大多数行，走索引+回表比全表扫还慢，优化器就放弃。反过来 IS NULL 若命中很少（多数 phone 都填了），就可能走索引。
正确做法：
-- IS NULL 命中少 -> 通常走索引
SELECT * FROM user WHERE phone IS NULL; -- 走 idx_phone
-- IS NOT NULL 命中多 -> 别指望索引，靠覆盖索引或收窄条件
ALTER TABLE user ADD INDEX idx_phone_uid(phone, id);
SELECT id FROM user WHERE phone IS NOT NULL; -- 覆盖索引不回表
加分点：这也呼应第 42 章——业务列尽量 NOT NULL DEFAULT，别留一堆 NULL，既避免三值逻辑坑，也让 IS [NOT] NULL 的选择性更可控。
坑 12：联合索引里把低区分度列放最左
场景 / 错误示范：
-- 联合索引 (status, user_id)，status 只有 0/1/2 三种值
CREATE INDEX idx_status_uid ON t(status, user_id);
-- 查询：WHERE status = 1 AND user_id = 100
-- status=1 可能命中表里 60% 的行，再叠加 user_id，索引"过滤力"很弱
EXPLAIN SELECT * FROM t WHERE status = 1 AND user_id = 100;
-- rows 预估很大，因为最左列 status 区分度太低
为什么是坑：联合索引最左列负责第一波过滤。把区分度极低的列（性别、状态）放最左，第一波就捞出一大片，后面的列即使区分度高，也只能在这一大片里慢慢筛，索引整体效率被最左列拖垮。索引列的顺序直接决定了这棵索引"先滤掉多少行"。
正确做法：
-- 把区分度高的列放最左
CREATE INDEX idx_uid_status ON t(user_id, status);
EXPLAIN SELECT * FROM t WHERE user_id = 100 AND status = 1;
-- user_id 先精准定位到这个用户的少量行，再用 status 过滤，rows 很小
加分点：区分度（cardinality/总行数）越高越该放前面。SELECT COUNT(DISTINCT status)/COUNT(*) FROM t 就能量出区分度。索引设计的详细讨论见下一章。
速查表：索引失效场景速记
| 写法 | 是否走索引 | 原因 | 正确改法 |
|---|---|---|---|
| WHERE YEAR(ct)=2026 | 失效 | 列包函数 | 改范围 ct>=.. AND ct<.. |
| phone 列传数字 | 失效 | 隐式类型转换 | 传字符串加引号 |
| 联合索引跳过最左列 | 部分失效 | 违反最左前缀 | 从最左列开始查 |
| OR 一侧无索引 | 失效 | 整体退化为全表扫 | 改 UNION 或补索引 |
| LIKE '%xx' | 失效 | 前导通配 | 改右通配或全文索引 |
| 范围列后还有列 | 后列失效 | 范围截断 | 范围列放索引最后 |
| age + 1 = 30 | 失效 | 列参与运算 | 改 age = 30-1 |
| SELECT * 命中索引列 | 走但回表 | 要回主键取整行 | 建覆盖索引 |
| FORCE INDEX | 强制但可能更慢 | 焊死选择 | ANALYZE 后让优化器选 |
| status != 1 | 多半不走 | 命中大半表 | 加范围收窄/覆盖索引 |
| phone IS NOT NULL | 看命中比例 | 命中多就不走 | 覆盖索引 |
注意事项：EXPLAIN 关键字段怎么读
排查"索引用没用上"，最终都要落到 EXPLAIN 输出上。下面这张表把最常看的字段对照说明，配合本章各坑使用。
| EXPLAIN 字段 | 怎么看 | 命中本章哪个坑 |
|---|---|---|
| type=ALL | 全表扫，最危险，立刻查原因 | 函数/隐式转换/OR |
| type=ref | 等值命中，走了索引，正常 | — |
| type=range | 索引范围扫，正常 | 坑 6 范围查询 |
| key | 实际用到的索引，NULL 表示没用索引 | 全局 |
| key_len | 用了联合索引的前几列 | 坑 3、坑 6 |
| rows | 预估扫描行数，越大越慢 | 坑 12 区分度 |
| Extra=Using index | 覆盖索引，好事 | 坑 8 |
| Extra=Using filesort | 排序没走索引，需优化 | 下一章 |
| Extra=Using temporary | 用了临时表，需警惕 | 聚合/分组篇 |
口诀：先看 type 是不是 ALL；再看 key 是不是 NULL；最后看 key_len 用了几列、rows 大不大。一条 EXPLAIN 看完这四样，索引问题基本定位。
常见误区提醒：
- 建了索引 ≠ 查询一定会走索引，走不走由优化器按成本决定。
- 索引不是越多越好，写多读少的表索引过多会拖慢写入（下一章展开）。
- EXPLAIN 的 rows 是预估值，不是精确值，但足够用来判断"走没走上正道"。
- 改完索引或大批量导入数据后，记得
ANALYZE TABLE更新统计信息，否则优化器拿着过时数据做错误判断。
自测：你能避坑吗
题 1：WHERE DATE(create_time) = '2026-10-07' 为什么索引失效？怎么改？
<details>
<summary>参考答案</summary>
对索引列套了 DATE() 函数，B+ 索引存的是原始时间值，函数包裹后无法用有序性定位，退化为全表扫。改写为 create_time >= '2026-10-07' AND create_time < '2026-10-08'，让左边保持裸列即可走索引。
</details>
题 2：联合索引 (a,b,c)，查询 WHERE a=1 AND b>10 AND c=5 用到了哪几列？
<details>
<summary>参考答案</summary>
只用了 a 和 b。b 是范围条件，会"截断"后面的列，c 无法再利用索引有序性。若希望 c 也用上，应把 c 这种等值列放范围列之前，即调整索引顺序。
</details>
题 3：字符串列 phone 有索引，WHERE phone = 13800001111 为什么慢？
<details>
<summary>参考答案</summary>
传入的是数字而列是 VARCHAR，MySQL 隐式把 phone 转成数字比较，等价于在列上套 CAST 函数，索引失效。改成 phone = '13800001111'（加引号）即可。
</details>
题 4：EXPLAIN 里 Extra 出现 "Using index" 代表什么？是好事还是坏事？
<details>
<summary>参考答案</summary>
是好事，表示覆盖索引——查询需要的列都在这棵二级索引里，无需再回主键聚簇索引取整行，避免了回表随机 IO。设计高频查询时，应尽量让它走覆盖索引。
</details>
题 5：WHERE age + 1 = 30 和 WHERE age = 30 - 1 等价吗？索引表现为何不同？
<details>
<summary>参考答案</summary>
两者结果等价，但索引表现不同：前者索引列 age 参与了运算，无法用有序索引定位，全表扫；后者运算在常量侧，age 保持裸列，能正常走索引。原则是"左边裸列，右边算账"。
</details>
题 6：EXPLAIN 里 type=ALL、key=NULL、rows 巨大，说明什么？
<details>
<summary>参考答案</summary>
全表扫描，完全没用到索引。优先排查：是不是索引列包了函数、是不是隐式类型转换、是不是 OR 一侧无索引、是不是前导通配、是不是违反了联合索引最左前缀。对照本章速查表逐条核对 WHERE 写法。
</details>
题 7：LIKE '手机%' 和 LIKE '%手机' 索引表现为何不同？必须做全文模糊搜索时怎么办？
<details>
<summary>参考答案</summary>
'手机%' 通配符在右，知道以什么开头，能走 B+ 索引；'%手机' 通配符在左，无法利用索引有序性，全表扫。必须做 '%手机%' 这类全文模糊时，不要硬撑 B+ 索引，改用 FULLTEXT 全文索引或把数据同步到 ES 专门做检索。
</details>
题 8：优化器选错了索引，开发同学直接加 FORCE INDEX，这个做法有什么问题？正确顺序是什么？
<details>
<summary>参考答案</summary>
FORCE INDEX 把索引选择焊死，数据分布变化后可能更慢，且掩盖了统计信息过期或索引设计不合理的根因。正确顺序：先 ANALYZE TABLE 更新统计信息让优化器重新选；仍不对再考虑 USE INDEX 给建议；最后才是应急 FORCE INDEX，并尽快用更合理的联合索引从根本上解决。
</details>
下一篇预告：第 45 章 索引设计踩坑
文章回复
0 条公开回复