那是一个再普通不过的订单查询接口。开发时我在本地点了几下,毫秒级返回;测试环境跑了一轮,也是秒回,验收顺顺当当。可上线没几周,运营那边开始零星反馈"订单列表转圈圈",再后来直接有人甩来一张截图:接口响应八九秒,偶尔干脆超时报错。诡异的是,代码一行没动,SQL 也还是那条 SQL。
我第一反应是数据库扛不住了,登上去看,CPU 不算高,连接数也正常,唯独慢查询日志里那条订单查询赫然在列,执行时间从测试时的几毫秒,变成了线上的好几秒。我把它拎出来,在前面加上 EXPLAIN 跑了一遍,结果让我心里咯噔一下:type 是 ALL,rows 是几百万,key 那一栏空空如也——明明我给查询条件那个字段建过索引,它却压根没走索引,而是老老实实把整张表从头扫到尾。
更扎心的是真凶:那个字段在表里是 varchar 类型存的手机号,而我的查询条件传进去的是一个数字。MySQL 为了让两边能比较,悄悄做了一次隐式类型转换,而这次转换恰好把我的索引变成了一堆摆设。测试环境数据才几千行,全表扫描快得让我完全没察觉;线上几百万行,同一条 SQL 立刻原形毕露。顺着这条线索复盘,我又揪出了同一类病根的好几张面孔:函数包住列、联合索引没按最左前缀用、范围查询之后的列失效……这篇文章,就把"建了索引却没走索引"这件事,一次说透。
先把几个想当然的误区摆上台面
在动手分析之前,我先承认自己曾经深信不疑、后来被现实啪啪打脸的几个念头。你不妨也对照看看,有没有踩中。
| 想当然的念头 | 残酷的真相 |
|---|---|
| "字段建了索引,查询就一定会走索引" | 索引能不能用,要看你怎么写 WHERE。函数包列、类型不匹配、最左前缀断裂,都会让优化器弃用它 |
| "反正建了索引,慢不到哪去" | 优化器若估算走索引还不如全表扫,会主动放弃索引;选择性差的索引(如性别)本就没多大意义 |
| "测试环境查得飞快,线上肯定没问题" | 小表全表扫描也很快,问题被数据量掩盖;真正的瓶颈往往等到线上数据涨起来才暴露 |
| "联合索引建了 (a,b,c),查 b 或 c 也能用" | 最左前缀原则:跳过 a 直接查 b、c,索引基本用不上 |
| "加索引是稳赚不赔的优化" | 索引会拖慢写入、占用空间;冗余、重复、低选择性的索引都是负担 |
这些念头单看都"挺有道理",但它们共同的盲区是:把索引当成了一个只要存在就生效的开关,而忽略了它本质上是一棵需要被正确"喂"查询条件才能命中的树。
第一件事:先搞懂索引凭什么快,以及"回表"是怎么回事
要理解索引为什么会失效,得先知道它工作时到底在干什么。以最常用的 InnoDB 为例,它的索引底层是一棵 B+ 树:所有真实数据行都挂在叶子节点上,非叶子节点只存键和指针,树通常只有三四层高。这意味着,哪怕几千万行数据,从根节点找到目标也只需要几次磁盘 IO,这就是索引快的根本原因——它把"一行行翻"变成了"顺着树叉跳"。
这里有个关键区分。InnoDB 的主键索引叫聚簇索引,它的叶子节点直接存着整行数据;而你自己额外建的索引叫二级索引(辅助索引),它的叶子节点存的不是整行,而是主键值。于是当你用二级索引查询、又需要返回索引里没有的字段时,数据库得先在二级索引树里找到主键,再拿这个主键回到聚簇索引树里捞出整行——这个动作就叫回表。回表多了同样会拖慢查询,这也是后面"覆盖索引"能优化的着力点。
下面这张图,描绘的就是一次"走二级索引 + 回表"的完整路径:
看懂这张图,你就明白我那次事故卡在了哪一步:本该走左边那条"能用上二级索引"的快路,却因为类型不匹配掉进了右边的 type=ALL 全表扫描。索引树明明就在那儿,查询却绕过它,把几百万行从头啃到尾。接下来,我们就从"怎么看出它没走索引"开始,一层层把这些坑挖出来。
第二件事:学会读 EXPLAIN,它会把真相直接写在脸上
排查这类问题,EXPLAIN 是第一把也是最重要的一把钥匙。把它加在 SELECT 前面,数据库就会告诉你这条查询打算怎么执行,而不真正执行它。我当时正是靠它一眼看穿了全表扫描。它的输出列不少,但真正要盯的就那么几个。
-- 在你怀疑的慢查询前面加上 EXPLAIN
EXPLAIN
SELECT id, user_id, amount, status
FROM orders
WHERE phone = 13800138000; -- 注意:这里传的是数字
-- 关注输出里这几列:
-- type : 访问类型,性能从好到坏大致是
-- system > const > eq_ref > ref > range > index > ALL
-- 看到 ALL 基本就是全表扫描,要警惕
-- key : 实际用上的索引名;为 NULL 说明没走任何索引
-- rows : 优化器预估要扫描的行数,越大越慢
-- Extra: 附加信息,Using index 是好事(覆盖索引),
-- Using filesort / Using temporary 往往是隐患
我那条出问题的查询,EXPLAIN 给出的 type=ALL、key=NULL、rows 几百万,三个信号叠在一起,等于直接在我脸上写了"我没用索引,我在扫全表"。记住一个简单的判断习惯:线上慢查询,先 EXPLAIN,先看 type 和 key。只要 type 是 ALL 而你又确信该字段有索引,那基本就是某种"索引失效"在作祟。
想看得更细,可以用 EXPLAIN ANALYZE(MySQL 8.0+),它会真正执行查询并给出每一步实际耗时和实际行数,把"优化器的估算"和"现实"摆在一起对照,误差大的地方往往就是统计信息过期或写法有问题的地方。
第三件事:隐式类型转换,我那次事故的正主
现在回到我踩的那个坑。表结构里,手机号字段是这样定义的:
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
phone VARCHAR(20) NOT NULL, -- 手机号用字符串存
amount DECIMAL(10,2),
status TINYINT,
KEY idx_phone (phone) -- 给手机号建了二级索引
);
字段是 varchar,索引也老老实实建着。问题出在查询条件:我图省事,直接拿一个数字去比。
-- 反例:列是字符串,却用数字去比
-- MySQL 会把【列】整体转成数字再比较,索引因此失效
SELECT * FROM orders WHERE phone = 13800138000; -- 全表扫描!
-- 正解:类型对齐,用字符串去比,索引正常命中
SELECT * FROM orders WHERE phone = '13800138000'; -- 走 idx_phone
这里的机制要说清楚:当字符串列和数字比较时,MySQL 不会把那个数字转成字符串,而是反过来,把整列的每一个值都转成数字再来比。一旦对索引列施加了这种"转换"操作,等价于在列上套了个函数,B+ 树里按字符串排好的顺序就用不上了,优化器只能退化成逐行计算、逐行比较,也就是全表扫描。
反向的坑同样存在:如果列是数字类型,你却用带引号的字符串去查(WHERE user_id = '123'),通常 MySQL 会把字符串常量转成数字,这种情况索引一般还能用——因为转换施加在常量上而不是列上。规律就一句话:转换一旦落到索引列身上,索引就废了;落在常量身上,通常无妨。最稳妥的做法,是从一开始就让查询条件的类型和列定义严丝合缝地对齐,别让数据库替你"猜"。
第四件事:在列上动手脚,索引就罢工
隐式类型转换只是"在列上施加操作"的一种特例。更普遍的规律是:只要你在 WHERE 里给索引列套了函数、做了运算,或者用了前导通配的模糊匹配,索引基本都会失效。原因是一致的——B+ 树是按列的原始值排序的,你一旦对列做了变换,排好的顺序就对不上了,优化器只能放弃索引、逐行计算。
-- 反例 1:对列用函数,索引失效
SELECT * FROM orders WHERE DATE(create_time) = '2026-05-29';
-- 反例 2:对列做运算,索引失效
SELECT * FROM orders WHERE amount + 10 > 100;
-- 反例 3:前导 % 的 LIKE,索引失效
SELECT * FROM orders WHERE phone LIKE '%8000';
-- 正解 1:改造成范围查询,让函数离开列
SELECT * FROM orders
WHERE create_time >= '2026-05-29 00:00:00'
AND create_time < '2026-05-30 00:00:00';
-- 正解 2:把运算挪到常量一侧
SELECT * FROM orders WHERE amount > 90;
-- 正解 3:改成前缀匹配,索引可用
SELECT * FROM orders WHERE phone LIKE '138%';
这条规律可以浓缩成一句口诀:"列要干净"。WHERE 里等号(或不等号)左边的索引列,最好保持它本来的样子,所有的函数、计算、转换,都尽量挪到右边的常量那一侧去。把这一条刻进肌肉记忆,能帮你躲掉一大半的索引失效。
第五件事:联合索引的最左前缀,和"范围之后全失效"
联合索引(组合索引)是另一个高频翻车点。假设有这样一个索引:
-- 联合索引,顺序是 (user_id, status, create_time)
ALTER TABLE orders ADD KEY idx_u_s_t (user_id, status, create_time);
-- 能用上索引(满足最左前缀)
SELECT * FROM orders WHERE user_id = 100;
SELECT * FROM orders WHERE user_id = 100 AND status = 1;
SELECT * FROM orders WHERE user_id = 100 AND status = 1 AND create_time > '2026-01-01';
-- 用不上 / 只能用一部分
SELECT * FROM orders WHERE status = 1; -- 跳过 user_id,整体失效
SELECT * FROM orders WHERE user_id = 100 AND create_time > '2026-01-01';
-- ↑ 跳过了 status,create_time 这一段用不上索引,只能定位到 user_id
联合索引可以想象成一本按"姓→名→年龄"三级排序的通讯录。你知道姓,能快速翻;知道姓和名,翻得更准;但如果你只知道名、不知道姓,这本按姓排序的通讯录就帮不上忙——这就是最左前缀原则:必须从索引最左边的列开始,连续地用,不能跳着用。
还有一个隐蔽的搭档陷阱:范围查询会"截断"后面的列。在 (user_id, status, create_time) 上,如果你对 status 用了范围(比如 status > 1),那么排在它后面的 create_time 就用不上索引了,因为范围之后顺序已经被打乱。所以建联合索引时,有个经验法则:把等值查询的列放前面,范围查询的列放最后。
第六件事:用覆盖索引和索引下推,把回表也省掉
前面说过回表的代价。如果一个查询需要的列,恰好全都包含在索引里,数据库就不用再回聚簇索引捞整行了——这叫覆盖索引,EXPLAIN 的 Extra 会显示 Using index,是性能上的一大利好。
-- 假设有覆盖所需列的联合索引 (user_id, status, amount)
-- 这条查询要的列都在索引里,无需回表
SELECT user_id, status, amount FROM orders
WHERE user_id = 100 AND status = 1;
-- EXPLAIN 的 Extra 会出现 Using index,表示走了覆盖索引
-- 对比:多要一个不在索引里的列,就得回表
SELECT user_id, status, amount, remark FROM orders
WHERE user_id = 100 AND status = 1;
-- remark 不在索引里,需要回表取
这也解释了一个老生常谈的建议:少写 SELECT *。只查你真正需要的列,不仅省网络和内存,更重要的是让覆盖索引有机会生效。此外,MySQL 5.6 引入的索引下推(ICP)会把能在索引层判断的条件尽量下推到存储引擎,减少回表次数,EXPLAIN 的 Extra 里出现 Using index condition 就是它在帮忙。
到这儿,索引失效的几张主要面孔都露脸了。我把排查的思路收成一张决策图,下次遇到慢查询,可以照着它一步步走:
把这套排查动作固化下来,大多数"建了索引却没走"的问题,十来分钟内就能定位到根因。最后,我把这些散落的经验拧成几条可以直接照做的铁律:
- 线上慢查询,第一步永远是 EXPLAIN,先看 type 和 key,而不是凭感觉猜。
- 查询条件的类型必须和列定义对齐,字符串列就用字符串比,别让数据库做隐式转换。
- 别在索引列上套函数、做运算,把变换挪到常量一侧,保持"列要干净"。
- 联合索引遵守最左前缀,从最左列开始连续使用;等值列放前、范围列放后。
- 能覆盖索引就覆盖,少用 SELECT *,只取需要的列,让 Using index 生效。
- 测试环境务必造足量数据,别让小表的全表扫描骗过你,把问题留到线上。
- 索引不是越多越好,低选择性、冗余、重复的索引都是写入负担,定期审视清理。
一张索引失效速查表,贴在工位上
把上面这些场景汇成一张表,下次写 SQL 前扫一眼,能省下不少深夜排查的时间。
| 失效场景 | 典型写法 | 正确做法 |
|---|---|---|
| 隐式类型转换 | WHERE phone = 138...(列是字符串) |
用字符串:WHERE phone = '138...' |
| 列上套函数 | WHERE DATE(t) = '...' |
改范围:t >= ... AND t < ... |
| 列上做运算 | WHERE amount + 10 > 100 |
挪到常量:WHERE amount > 90 |
| 前导通配模糊 | WHERE name LIKE '%abc' |
前缀匹配 'abc%' 或全文索引 |
| 违反最左前缀 | 索引 (a,b,c),却 WHERE b=... |
从 a 开始连续用,或调整索引顺序 |
| 范围后列失效 | WHERE a>1 AND b=2 |
等值列放前,范围列放最后 |
| OR 连接非索引列 | WHERE a=1 OR x=2(x 无索引) |
给 x 也建索引,或改用 UNION |
| 选择性太低 | 给 status/性别这类列建索引 | 组合到联合索引里,或干脆不建 |
还有一种"玄学失效":统计信息骗了优化器
有时候 SQL 写得没毛病,索引也满足最左前缀,优化器却还是固执地选了全表扫描或选错了索引。这往往不是写法问题,而是统计信息过期了。优化器是基于成本估算来决策的,它依赖每张表、每个索引的统计信息(比如数据分布、基数 cardinality)来判断"走索引划不划算"。当一张表经历了大量增删改,统计信息可能严重失真,导致它对行数的估算偏离现实,从而做出离谱的选择。
-- 重新收集统计信息,让优化器拿到接近现实的数据分布
ANALYZE TABLE orders;
-- 实在拗不过优化器、又确认某个索引更优时,可以强制(慎用)
SELECT * FROM orders FORCE INDEX (idx_phone)
WHERE phone = '13800138000';
这里要给 FORCE INDEX 一句郑重提醒:它是"最后手段",不是常规优化方式。强制索引相当于剥夺了优化器随数据变化自动调整的能力,今天最优的索引,数据涨上去之后可能就不是了。绝大多数情况下,正确的姿势是先 ANALYZE TABLE 更新统计信息、把 SQL 写法理顺,而不是一上来就 FORCE。只有当你百分百确定、且其它办法都试过之后,才把它请出来。
那些容易被忽略的边角:OR、IN、NULL 和 ORM
主干的几张面孔说完,还有几个边角场景,平时不显眼,关键时刻却同样能让索引悄悄哑火。
第一个是 OR。当 OR 两侧的条件不是都能用上索引时,优化器往往会直接退回全表扫描。比如 WHERE user_id = 100 OR remark = 'vip',如果 remark 没有索引,那么即便 user_id 有索引,这条查询也大概率走不了它。解决办法要么给 OR 涉及的列都补上索引,要么把它拆成两条能各自走索引的查询再用 UNION 合并。
第二个是 IN 与 NOT IN。IN 通常能用上索引,但当列表特别长、或者里面塞了子查询时,优化器的成本估算可能翻车,转而选择全表扫描;而 NOT IN、!= 这类"否定"条件,因为要排除的范围太广,常常也用不上索引。遇到这种情况,可以考虑改写成 JOIN,或者重新审视业务上是否真的需要这种大范围的反向查询。
第三个是 对可空列判断 IS NULL / IS NOT NULL。这本身不一定让索引失效,但 NULL 值的分布会显著影响优化器的判断;更稳妥的工程习惯,是给字段加上 NOT NULL 约束并给一个明确的默认值,既省去这类纠结,也让语义更清晰。
-- OR 拆 UNION,让两边各自走索引
SELECT * FROM orders WHERE user_id = 100
UNION
SELECT * FROM orders WHERE remark = 'vip'; -- 需 remark 也有索引
-- NOT IN 改写成 LEFT JOIN ... IS NULL,常更友好
SELECT o.* FROM orders o
LEFT JOIN blacklist b ON o.user_id = b.user_id
WHERE b.user_id IS NULL;
最后,还有一个"看不见"的坑藏在 ORM 里。用 MyBatis、Hibernate、JPA 这类框架时,真正发给数据库的 SQL 是框架生成的,你在代码里写的是对象和方法,很容易就看不到底层那条 SQL 长什么样、有没有走索引。我的建议是:把框架的 SQL 日志打开,定期捞出真实执行的语句,挑几条核心的去 EXPLAIN 一遍。很多隐藏的全表扫描,就是因为没人去看框架到底生成了什么,才一直潜伏到线上。别让 ORM 的便利,变成你对 SQL 失去掌控的借口。
写在最后
回头看这次事故,真正让我后背发凉的,不是那条慢查询本身,而是它在测试环境里"装得若无其事"。几千行数据时,全表扫描和走索引的差距小到可以忽略,验收一路绿灯;直到线上数据涨到几百万行,被掩盖的代价才骤然放大成八九秒的超时。这件事给我最深的提醒是:性能问题往往不是突然出现的,而是早就埋下了,只是在等一个足够大的数据量把它引爆。
所以"索引失效"这五个字,与其说是一类数据库知识,不如说是一种写 SQL 时的下意识警觉:每写一条带 WHERE 的查询,顺手想一想——这个条件,能让我建的索引真正用上吗?列被我套函数了吗?类型对得齐吗?联合索引的最左列我带上了吗?这些问题问一遍,花不了几秒钟,却能在数据量起来之前,就把一个潜伏的慢查询掐灭在键盘上。把对索引的这份敬畏,变成每次落键时的本能,你会发现自己被慢查询叫醒的次数,正越来越少。
如果你手上也有跑了一阵子的线上库,不妨今天就花二十分钟做三件小事:第一,打开慢查询日志,把最近最慢的几条 SQL 捞出来,逐条 EXPLAIN,重点盯 type 和 key;第二,翻一翻代码里那些拼接查询条件的地方,确认传入的参数类型和列定义对得上,尤其是手机号、订单号、各种编号这类"看着像数字其实是字符串"的字段;第三,把核心表的联合索引列顺序和高频查询条件比对一遍,看看最左前缀有没有被白白浪费。这三件事都不难,却能把一批正潜伏着、等数据量上来就发作的慢查询提前揪出来。
数据库这东西,平时不声不响,真出问题时却往往是压垮服务的那根稻草。而索引,恰恰是性价比最高的一道防线——建对了、用对了,它几乎不要你额外花钱,就能把查询从几秒压到几毫秒。可它也最娇气,一个不留神的类型不匹配、一个顺手套上的函数,就足以让它形同虚设。愿你我都能记住这次教训:写下每一条 WHERE 时,都为身后那棵默默排好序的 B+ 树,留一条干干净净、走得通的路。
—— 别看了 · 2026