明明有索引却全表扫描:索引失效避坑复盘

那次事故,把我对"我明明建了索引啊"这句话的信心,彻底击碎了。现象是:一个按手机号查询用户的接口,上线一年多一直稳稳的,响应几毫秒,突然某天开始偶发性地慢到好几秒、甚至超时。这个接口的 SQL 简单到不能再简单——就是 SELECT * FROM user WHERE phone = ?,而 phone 字段上,我可是清清楚楚建了索引的。一个走索引的等值查询,几百万行的表里也该是毫秒级返回,怎么会慢到几秒?我第一反应是数据库是不是抖了,可监控显示数据库很健康;又怀疑是不是慢查询拖累,一查慢查询日志,好家伙,慢的就是这条本该飞快的查询。

真正让我脊背发凉的,是我把这条慢 SQL 拿去 EXPLAIN 看执行计划的那一刻:它居然是全表扫描(type=ALL),完全没有走 phone 上那个我精心建好的索引!索引明明在那儿,数据库却视而不见、宁可一行行地扫几百万行。这就像你给一栋大楼装了电梯,消防员却偏要一层层爬楼梯——百思不得其解。排查到最后,真相荒诞又深刻:不是索引坏了,而是有个调用方传参时,把手机号当成了数字传了进来,而 phone 这个列是 varchar(字符串) 类型——这一个小小的类型不匹配,触发了 MySQL 的"隐式类型转换",而正是这个转换,让我的索引彻底失效了。这篇文章,就从这次"明明有索引却全表扫描"的事故讲起,把数据库索引那些"建了却用不上"的失效陷阱,一个个挖透。

故障现场:被一个数字"废掉"的索引

先把现场还原清楚。user 表几百万行,phone 字段定义是 varchar(20),上面建了普通索引。出问题的调用,SQL 长这样(参数值我直接写进来方便看):

-- phone 列是 varchar(字符串), 但传进来的参数是数字 13800138000
SELECT * FROM user WHERE phone = 13800138000;     -- ← 全表扫描!

-- 正确的写法: 参数是字符串, 带引号
SELECT * FROM user WHERE phone = '13800138000';   -- ← 走索引, 毫秒级

差别就在那对引号上。当你用一个数字去和一个字符串类型的列比较时,MySQL 不会报错(它很"贴心"),而是会做隐式类型转换。可关键在于转换的方向:按照 MySQL 的规则,字符串和数字比较时,是把字符串转成数字,而不是反过来。这意味着,数据库需要把 phone 这一列里每一行的字符串值,都先转换成数字,然后再去和 13800138000 比较。也就是说,这个查询实际等价于:

-- MySQL 实际执行的, 相当于对每一行的 phone 做了函数转换:
SELECT * FROM user WHERE CAST(phone AS DECIMAL) = 13800138000;

看出问题了吗?索引是建立在 phone 这一列"原始的字符串值"上的;可现在查询条件里,phone 被一个转换函数(CAST)包裹了起来——索引存的是 '13800138000' 这个字符串,而查询要找的是 CAST(phone) 转换后的结果,两者对不上,索引自然就用不了了。数据库别无选择,只能老老实实地把每一行都取出来、做一次类型转换、再比较——这就是全表扫描。一个本可以靠索引瞬间定位的查询,因为一个小小的类型不匹配,退化成了扫描几百万行的灾难。它之所以"偶发",是因为只有那个传错类型的调用方发起的请求才会触发,其它正确传字符串的调用一切正常。

第一件事:理解"对索引列做运算/转换"会让索引失效

这次事故指向了一条索引使用最核心、也最容易被违反的铁律:一旦你在查询条件里对索引列本身做了任何运算、函数调用或类型转换,这个索引通常就用不上了。原因前面说透了——索引是基于列的"原始值"建立的一棵有序的树,它能高效查找的前提是"你拿原始值去查";而一旦你对列套了个函数(无论是显式写的,还是像这次一样隐式触发的),数据库要比较的就是"函数处理后的值",这个值索引里没有、也没法利用索引的有序性,只能逐行计算比对。

-- 这些写法都会让 idx 列上的索引失效(对列做了运算/函数):
WHERE YEAR(create_time) = 2024        -- 对列用了函数
WHERE amount + 100 > 500              -- 对列做了运算
WHERE phone = 13800138000             -- 隐式类型转换(本次事故)
WHERE LEFT(name, 3) = 'abc'           -- 对列用了函数

-- 改成"不动列、只动值"的等价写法, 才能走索引:
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
WHERE amount > 400
WHERE phone = '13800138000'
WHERE name LIKE 'abc%'

核心口诀是:"索引列要保持'干净',别在它身上动手脚。"所有的运算、转换,都尽量挪到查询条件的另一边(常量那一边)去做,让索引列以最原始的样子出现在条件里。比如要查"2024 年创建的",别写 YEAR(create_time) = 2024(对列用函数),而要写成范围 create_time >= '2024-01-01' AND create_time < '2025-01-01'(让列保持原样,把计算放到常量边)。理解了"对索引列做任何加工都会让索引失效"这条原理,你就能预判一大类索引用不上的情况。

第二件事:学会用 EXPLAIN 看穿索引到底有没有走

这次能破案,头号功臣是 EXPLAIN。它能让你看到一条 SQL 的"执行计划"——数据库打算怎么执行它、用没用索引、要扫多少行。不会看 EXPLAIN,索引优化就只能靠猜;会看 EXPLAIN,索引问题就从"玄学"变成了"一眼可见"。用法极简单,在 SQL 前面加 EXPLAIN 就行:

EXPLAIN SELECT * FROM user WHERE phone = 13800138000;
-- 重点看这几列:
-- type:  ALL=全表扫描(最差!)  ref/range=走了索引  const=主键/唯一索引命中
-- key:   实际用到的索引名; 如果是 NULL, 说明没走任何索引
-- rows:  预估要扫描的行数; 越大越慢
-- Extra: Using index=覆盖索引(好); Using filesort/temporary=要额外排序(注意)

排查索引问题,最该盯的是 type 和 key 这两列:type=ALL 就是全表扫描的铁证,key=NULL 说明压根没用上索引——我那次一看,type 赫然是 ALL、key 是 NULL,rows 是几百万,瞬间就确认了"索引没生效"这个方向,接下来才顺藤摸瓜查到了类型转换。反过来,正确传字符串的那条 SQL,EXPLAIN 出来 type 是 ref、key 是 phone 索引、rows 是 1——清清楚楚走了索引。所以,任何一条你关心性能的查询,都应该养成用 EXPLAIN 看一眼执行计划的习惯,别想当然地以为"我建了索引它就一定会走"。索引建了是一回事,SQL 写得能不能用上它,是另一回事——EXPLAIN 就是验证后者的唯一可靠手段。我把"查一个慢查询为什么不走索引"的排查路径画成图:

这张图其实是一份"索引为什么没生效"的排查清单:确认 EXPLAIN 显示没走索引后,顺着"列上有没有建索引→建了为什么不走"这条线查下去,而"建了不走"的常见原因,无非就是图里那几条——对列做了运算、隐式类型转换、不满足联合索引最左前缀、前导通配的 LIKE、OR 连接了非索引列等。把这张图记在心里,绝大多数索引失效问题,你都能快速定位。

第三件事:联合索引的"最左前缀"原则

除了"对列动手脚",另一个超高频的索引失效原因,是没搞懂联合索引(多列索引)的最左前缀原则。当你在 (a, b, c) 三列上建了一个联合索引时,它能不能被用上,有严格的规则:查询条件必须从最左边的列开始、连续地使用,中间不能"跳过"。

-- 假设建了联合索引 idx(a, b, c)
WHERE a = 1                       -- 走索引(用了最左的 a)
WHERE a = 1 AND b = 2             -- 走索引(用了 a, b)
WHERE a = 1 AND b = 2 AND c = 3   -- 走索引(全用上)
WHERE a = 1 AND c = 3             -- 部分走(只能用到 a, 跳过 b 后 c 用不上)
WHERE b = 2                       -- 不走索引! 没从最左的 a 开始
WHERE b = 2 AND c = 3             -- 不走索引! 同样没有 a 打头

为什么会这样?可以把联合索引想象成一本按"姓、再名、再年龄"排序的电话簿:你知道"姓",能很快翻到;你知道"姓+名",更精确;但如果你只知道"名"、不知道"姓",这本按姓排序的电话簿就帮不上你了,只能一页页翻——因为它的有序性是"先按姓"建立的,跳过姓直接找名,有序性就用不上了。所以联合索引 (a,b,c),本质上能高效支持的是"以 a 开头的连续前缀"查询:a、(a,b)、(a,b,c);而 b、c、(b,c) 这些"不带 a 打头"的查询,是用不上它的。这条原则直接影响你该怎么设计联合索引的列顺序——把最常用作查询条件、区分度最高的列放在最左边。很多人建了联合索引却发现没生效,十有八九就是踩了最左前缀这条线。

第四件事:其它常见的索引失效场景

顺着这次事故,我把索引"建了却用不上"的常见场景系统梳理了一遍。除了前面讲的"对列做运算/转换"和"违反最左前缀",还有几个高频坑也值得记牢:

-- 1. LIKE 以 % 开头: 索引失效(有序性从左到右, 左边不定就没法用)
WHERE name LIKE '%abc'     -- 失效(前导%)
WHERE name LIKE 'abc%'     -- 走索引(后缀%, 前缀确定)

-- 2. OR 连接了未建索引的列: 整条可能退化为全表扫描
WHERE phone = '...' OR address = '...'   -- address 没索引, 整体可能全表扫
-- 改: 给 OR 涉及的列都建索引, 或用 UNION 拆开

-- 3. 对 NULL 的判断、!= / NOT IN 等: 往往用不上索引
WHERE status != 1          -- 不等于, 通常不走索引
WHERE deleted IS NOT NULL  -- 视情况, 区分度低时优化器也可能放弃索引

-- 4. 区分度太低的列: 优化器算了算, 觉得走索引还不如全表扫
WHERE gender = 1           -- 性别只有两个值, 走索引意义不大, 可能直接全表

这里有个反直觉但重要的点:索引"用不用",最终是数据库的查询优化器根据成本估算来决定的——并不是"建了索引、写法也对"就一定会走。比如最后那个 gender 的例子,如果一个列区分度极低(全表一半数据都满足条件),优化器会算出"走索引(先查索引再回表取数据)反而比直接全表扫描更慢",于是干脆放弃索引。这也提醒我们:索引适合建在"区分度高"的列上(比如手机号、订单号这种几乎唯一的),建在"区分度低"的列(性别、状态、是否删除)上往往收效甚微。我把这些索引失效场景汇成一张速查表:

这张表里我最想强调"隐式类型转换"这一行——因为它最隐蔽。前面那些(对列用函数、前导%)你在 SQL 里一眼能看出来,而类型转换往往是调用方传参时类型不对悄悄引入的,SQL 文本里看着一切正常,你得 EXPLAIN 了才发现没走索引。所以,确保"传给查询的参数类型,和列定义的类型严格一致"(字符串列就传字符串、数字列就传数字),是一条特别容易被忽视、却极其重要的纪律。

第五件事:读懂 EXPLAIN 的 type,给优化指方向

既然索引问题离不开 EXPLAIN,那 EXPLAIN 里最关键的 type 列(访问类型),就值得专门弄懂。它直观地告诉你这次查询的"档次"——从最差的全表扫描,到最好的常量命中,优化的目标就是把 type 往好里推。我把常见的几档从差到好列出来:

看 EXPLAIN 时,一条朴素的判断是:大表查询如果 type 是 ALL 或 index,基本就该警惕和优化了;能优化到 range、ref 及更好,通常就达标了;能到 const(主键/唯一键命中)自然最理想。除了 type,还有两个 Extra 里的信号值得留意:Using index 表示这是"覆盖索引"(查询要的列都在索引里,不用回表取数据,很高效);而 Using filesort 或 Using temporary 则提示有额外的排序或临时表开销,排序字段可能也需要纳入索引来优化。把 type 的等级和这几个 Extra 信号结合起来看,你就能比较准确地判断一条 SQL 的索引用得好不好、还有没有优化空间。

一张"索引怎么建、怎么用"的决策图

把这次踩坑沉淀的索引知识拧成一条主线,做成一张设计/排查时可以照着走的决策图:

这张图想传达两层意思:上半部分是"该不该建、怎么建"——索引要建在高区分度的列上,常一起查的列建联合索引并把高区分度列放最左;下半部分是"建了怎么用对"——SQL 写法要让列保持干净、参数类型匹配、满足最左前缀,最后一定用 EXPLAIN 验证它真的走了索引。"建对索引"和"用对索引"是两件事,缺一不可——很多性能问题,不是因为没建索引,而是因为建了却用错了,而 EXPLAIN 是连接这两件事的验证闭环。

我立下的几条索引使用铁律

这次"明明有索引却全表扫描"的事故后,团队的数据库规范里加了这么几条:

1. 参数类型严格匹配列类型:字符串列就传带引号的字符串、数字列就传数字,杜绝隐式类型转换导致的索引失效。
2. 别对索引列做运算/函数:保持索引列"干净",把计算和转换都挪到条件的常量一侧。
3. 联合索引遵循最左前缀:从最左列开始连续使用,设计时把高区分度、最常用的列放最左。
4. 关键查询必 EXPLAIN:任何关心性能的 SQL,上线前用 EXPLAIN 确认 type 和 key,别假设"建了索引就会走"。
5. 索引建在高区分度列:手机号、订单号这类近乎唯一的列适合;性别、状态这类低区分度列单独建意义不大。
6. LIKE 避免前导 %:模糊查询尽量用后缀通配 'abc%';确需前导模糊用全文索引或搜索引擎。
7. 慢查询纳入监控:开启慢查询日志并告警,让"突然变慢的 SQL"第一时间浮现,而非等用户投诉。

这几条里,第一条是直接堵死这次事故的,第四条"关键查询必 EXPLAIN"则是贯穿一切的总纲。我尤其想强调最后一条"慢查询监控"——我们这次的坑能被发现,一半靠运气(恰好有人反馈慢)。如果当时有完善的慢查询告警,那条突然从几毫秒变几秒的 SQL,会第一时间被捕捉、被报警,而不是默默拖垮接口直到有人投诉。性能问题和功能 bug 不一样:功能坏了会报错、会被立刻发现,而性能退化往往是"还能用、只是慢了",极易被忽视,直到它累积成一场事故。一套慢查询监控,就是给这类"沉默的退化"装上的警报器。

写在最后:索引是"约定",数据库会"较真"

这次被一个数字废掉索引的经历,纠正了我对数据库索引一个挺天真的认知。在那之前,我以为索引是个"智能"的东西——我建了它,数据库就会"聪明地"在该用的时候用上它,我大概不用太操心。可这次事故让我看清:数据库其实一点都不"善解人意",它极其较真、极其字面——你的 SQL 写成什么样,它就严格按规则去匹配能不能用索引,差一个引号、多一个函数,它都绝不会"猜你的意图"帮你走索引,而是冷冰冰地退化成全表扫描。索引不是一种"智能",而是一份严格的"约定":你得严格按照它生效的规则来写查询,它才会为你所用;你稍微违反一点(哪怕是无意的类型不匹配),它就默默失效,而且不报错、不提醒。

想通这一点,我对数据库的态度从"信任它会聪明"变成了"理解它的规则、并严格遵守"。我不再想当然地以为"我建了索引、查询应该会走",而是养成了"用 EXPLAIN 去验证"的习惯——因为我明白,在数据库这个极度较真的世界里,"我以为"是最不可靠的,"EXPLAIN 给我看的执行计划"才是唯一的事实。这种从"凭感觉信任"到"靠工具验证"的转变,其实是工程素养成熟的一个普遍标志:对任何一个你依赖的、行为由明确规则决定的系统(数据库的优化器、编译器、协议栈……),不要凭直觉去假设它的行为,而要去理解它的规则、并用工具去验证它实际怎么做——因为这些系统不会迁就你的"想当然",它们只忠实于自己的规则。

所以,如果你也常和数据库打交道,我想把这次踩坑最想说的话送给你:别把索引当成会替你着想的"智能管家",把它当成一份需要你严格履约的"契约"——你按规则写查询,它高效;你违反规则(哪怕无心),它失效。而连接"你的意图"和"它的实际行为"之间那道鸿沟的桥,就是 EXPLAIN:写完关心性能的查询,顺手 EXPLAIN 一下,看看它到底走没走索引、扫了多少行。这个小小的习惯,能帮你在性能问题酿成事故之前,就把它扼杀在执行计划里。愿你建的每一个索引,都不只是"建了",而是真正"用上了"——别像我一样,被一个小小的数字,废掉一个精心建好的索引,还查了大半天才回过神来。

数据库是后端工程师每天打交道最多的伙伴之一,而索引又是它性能的命脉。把"理解规则、用工具验证、严格履约"这套心法用在它身上,你会发现很多曾经玄学般的慢查询,都变得有迹可循、可解可控。这,大概就是那个被数字废掉的索引,留给我最实在的一份礼物了。

—— 别看了 · 2026