2024 年我们一个查询接口在上线后突然变慢,慢查询日志里它每次都要跑一两秒。我打开那条 SQL 一看,WHERE 条件用到的列上明明建了索引——这个索引是上个月专门为这个查询加的。可 EXPLAIN 一跑,执行计划里赫然写着 type: ALL,全表扫描,索引就像不存在一样。这件事让我意识到,"建了索引"和"索引生效"完全是两码事。索引失效有一大堆很隐蔽的触发条件,一不小心就踩中,而且踩中之后数据库不会报错,它只是默默地走全表扫描,等慢查询日志攒到一定程度才被你发现。投了几天把索引失效的各种场景彻底梳理了一遍,本文复盘这次实战。
问题背景
业务:用户中心,user 表 800 万行,多个查询接口
事故现象:
- 某查询接口上线后慢查询日志频繁告警,单次 1~2 秒
- WHERE 用到的列上明明建了索引
- 同样的表,有的查询飞快,有的查询全表扫描
现场排查:
# 1. 看那条慢 SQL
SELECT * FROM user
WHERE DATE(create_time) = '2024-06-01';
# 2. EXPLAIN 看执行计划
+----+-------+------+---------------+------+---------+---------+
| id | table | type | possible_keys | key | rows | Extra |
+----+-------+------+---------------+------+---------+---------+
| 1 | user | ALL | idx_ctime | NULL | 8003221 | Using |
| | | | | | | where |
+----+-------+------+---------------+------+---------+---------+
# possible_keys 有 idx_ctime,但 key 是 NULL
# -> 优化器知道有这个索引,但【没用】它,走了全表扫描
# 3. 再看另几条也慢的 SQL
SELECT * FROM user WHERE phone = 13800138000; -- phone 是 varchar
SELECT * FROM user WHERE name LIKE '%志强'; -- 前导通配符
SELECT * FROM user WHERE status != 1; -- 不等于
根因:
1. 在索引列上套了函数 DATE(),索引彻底用不了
2. phone 是 varchar 却传了数字,触发隐式类型转换
3. LIKE 以 % 开头,无法用索引定位起点
4. 团队对"什么操作会让索引失效"缺乏系统认知修复 1:别在索引列上做任何"加工"
-- === 失效场景:索引列上套函数 ===
-- 索引存的是【列原本的值】排好序的结构。
-- 一旦你对列套了函数,数据库要拿到的是
-- "函数计算之后的值",而索引里根本没有这个值 ——
-- 它只能把每一行都取出来、逐行算一遍 DATE(),
-- 这就退化成了全表扫描。
-- 错误写法:对索引列 create_time 套了 DATE()
SELECT * FROM user WHERE DATE(create_time) = '2024-06-01'; -- 全表扫描
-- 正确写法:改写成"范围查询",让索引列保持"裸露"
SELECT * FROM user
WHERE create_time >= '2024-06-01 00:00:00'
AND create_time < '2024-06-02 00:00:00'; -- 走索引
-- === 失效场景:索引列上做运算 ===
-- 错误:对索引列 age 做了 +1 运算
SELECT * FROM user WHERE age + 1 = 30; -- 失效
-- 正确:把运算挪到等号右边的常量上
SELECT * FROM user WHERE age = 30 - 1; -- 走索引,即 age = 29
-- === 一条总原则 ===
-- 索引列必须"干干净净"地出现在 WHERE 条件里 ——
-- 不能套函数、不能参与运算、不能拼接。
-- 任何对索引列的"加工",都会让索引失效。
-- 需要加工,就把加工动作转移到【条件的另一边】。修复 2:隐式类型转换——最隐蔽的失效
-- === 失效场景:列是字符串,却用数字去比 ===
-- phone 字段是 varchar 类型,但代码里传了个数字:
SELECT * FROM user WHERE phone = 13800138000; -- 失效!
-- 为什么失效:当字符串和数字比较时,
-- MySQL 的规则是【把字符串转成数字】再比。
-- 于是这条 SQL 实际等价于:
SELECT * FROM user WHERE CAST(phone AS DECIMAL) = 13800138000;
-- 看到没?phone 这个索引列,被 CAST 函数包住了 ——
-- 这就回到了"修复 1"说的:索引列套函数 = 失效。
-- === 正确写法:类型对齐,字符串就传字符串 ===
SELECT * FROM user WHERE phone = '13800138000'; -- 走索引
-- === 反过来的情况:列是数字,传字符串 ===
-- user_id 是 bigint,传了字符串 '12345'
SELECT * FROM user WHERE user_id = '12345';
-- 这种【一般不失效】:MySQL 把字符串常量转成数字,
-- 转换发生在【常量】那边,没碰索引列,索引仍可用。
-- === 关键认知 ===
-- 隐式转换是否让索引失效,取决于"被转换的是哪一边":
-- - 转换发生在【索引列】上 -> 失效
-- - 转换发生在【常量】上 -> 通常不失效
-- 字符串列被数字比较,转的恰恰是列那边,所以失效。
-- 最稳妥的做法:别依赖隐式转换,
-- 让查询参数的类型,和数据库字段类型严格一致。修复 3:联合索引的最左前缀原则
-- === 假设有一个联合索引 ===
-- CREATE INDEX idx_s_c_a ON user (status, city, age);
-- 这个索引,可以理解成数据先按 status 排序,
-- status 相同的再按 city 排序,city 相同的再按 age 排序 ——
-- 像电话簿先按姓、再按名排。
-- === 能用到索引的查询(满足最左前缀)===
WHERE status = 1 -- 用到 (status)
WHERE status = 1 AND city = 'BJ' -- 用到 (status, city)
WHERE status = 1 AND city = 'BJ' AND age = 30 -- 用到 (status,city,age)
-- === 用不到 / 用不全索引的查询 ===
WHERE city = 'BJ' -- 失效:跳过了最左列 status
WHERE age = 30 -- 失效:跳过了 status、city
WHERE status = 1 AND age = 30 -- 只能用到 status 这一段,
-- 因为中间断了 city,age 用不上
-- === 一个重要的细节:范围查询会"截断"后面的列 ===
WHERE status = 1 AND city > 'BJ' AND age = 30
-- status 用上了;city 是范围查询(>),它能用索引,
-- 但范围之后的 age 就【用不到索引】了 ——
-- 一旦某一列是范围查询,它后面的列就在索引里断了。
-- === 实战经验:联合索引的列顺序怎么定 ===
-- 1. 把【等值查询】的列放前面,【范围查询】的列放后面
-- 2. 选择性高(不同值多)的列尽量靠前
-- 3. 让一个联合索引尽量能服务多个查询(靠最左前缀复用)
-- 注意:WHERE 里 AND 条件的【书写顺序】不影响索引使用,
-- 优化器会自己调整;真正要紧的是索引【定义时】的列顺序。修复 4:LIKE、OR、NOT 这些"危险写法"
-- === LIKE 的前导通配符 ===
-- 索引是按值【从左到右】排序的,所以它擅长
-- "确定了左边、往右找"。
SELECT * FROM user WHERE name LIKE '张%'; -- 走索引(左边确定)
SELECT * FROM user WHERE name LIKE '%志强'; -- 失效(左边不确定)
SELECT * FROM user WHERE name LIKE '%志%'; -- 失效
-- 需要"包含"式的模糊搜索,别硬用 LIKE '%x%',
-- 该上全文索引(FULLTEXT)或专门的搜索引擎(ES)。
-- === OR:只要有一个条件的列没索引,整体就可能失效 ===
-- name 有索引,remark 没索引
SELECT * FROM user WHERE name = '张三' OR remark = 'vip';
-- remark 没索引 -> 为了 remark 这个条件必须全表扫,
-- 那 name 这边的索引也就没意义了 -> 整体全表扫描
-- 解法:给 OR 涉及的【每个】列都建索引;
-- 或者用 UNION 把两个条件拆成两条能各自走索引的查询。
-- === != 、NOT IN、IS NOT NULL ===
SELECT * FROM user WHERE status != 1; -- 通常失效
SELECT * FROM user WHERE id NOT IN (1,2,3); -- 通常失效
-- "不等于"意味着要找的是"几乎所有行",
-- 优化器一算:既然要的行这么多,走索引还得频繁回表,
-- 不如直接全表扫 —— 于是它主动放弃了索引。
-- 解法:能换成"等值/范围的正向表达"就换,
-- 比如 status != 1 若 status 只有 1/2/3,
-- 改成 status IN (2,3) 就能走索引。
-- === IS NULL / IS NOT NULL ===
-- IS NULL 在 MySQL 里是可以走索引的(NULL 值也进索引);
-- IS NOT NULL 是否走索引,同样取决于"满足的行多不多"。修复 5:索引明明能用,优化器却不用
-- === 现象:索引没失效,但优化器"主动"不选它 ===
-- 有时索引完全可用,EXPLAIN 里它也在 possible_keys,
-- 但 key 还是 NULL。这不是 bug,是优化器算过账后的选择。
-- === 原因:回表代价 + 索引选择性 ===
-- 走二级索引查到的是主键,要拿完整行还得【回表】。
-- 如果一个查询命中的行占了全表很大比例(比如 30%),
-- 优化器会算:回表 240 万次的随机 IO,
-- 还不如直接顺序全表扫 800 万行来得快 ——
-- 于是它理性地放弃了索引。
-- 这种"索引选择性差"的列(如 status 只有 2~3 个值),
-- 单独建索引往往意义不大。
-- === 看清优化器的算账过程 ===
EXPLAIN FORMAT=JSON
SELECT * FROM user WHERE status = 1;
-- JSON 里的 cost_info 会列出它对各方案的成本估算
-- === 应对办法 ===
-- 1. 用覆盖索引消灭回表:让索引把查询要的列都包含进去,
-- 查到索引就够了、不用回表,优化器就更愿意用它。
CREATE INDEX idx_cover ON user (status, name, phone);
SELECT status, name, phone FROM user WHERE status = 1; -- 不回表
-- 2. 统计信息过期会让优化器误判,重新分析表:
ANALYZE TABLE user;
-- 3. 实在确认优化器选错了,用 FORCE INDEX 强制(慎用):
SELECT * FROM user FORCE INDEX(idx_ctime)
WHERE create_time >= '2024-06-01';
-- FORCE INDEX 是最后手段,它写死了执行计划,
-- 数据分布一变可能反而更慢,优先改 SQL 和索引设计。修复 6:索引排查的方法论
=== 第一步:永远先 EXPLAIN ===
怀疑一条 SQL 慢,第一动作就是 EXPLAIN。
重点看这几个字段:
【type】访问类型,性能从好到差:
system > const > eq_ref > ref > range > index > ALL
- ref/range/const:走了索引,通常 OK
- index:扫了整个索引树,比 ALL 略好但也要警惕
- ALL:全表扫描,大表上出现 ALL 基本就是要优化的
【key】实际用的索引
- key 是 NULL = 没用任何索引
- possible_keys 有、key 是 NULL = 有索引但优化器没选
【rows】预估要扫描的行数
- 这个数越接近"最终结果行数"越好
- 它远大于结果行数,说明扫了很多无用行
【Extra】额外信息,几个危险信号:
- Using filesort:排序没走索引,在内存/磁盘额外排序
- Using temporary:用了临时表,常见于 group by
- Using where:存储引擎返回后还要再过滤(不一定是坏事)
- Using index:覆盖索引,不用回表 —— 这个是【好】信号
=== 第二步:对照失效清单逐条排查 ===
SQL 走了 ALL,就对照检查:
索引列套函数了吗?类型对齐了吗?LIKE 前导 % 了吗?
联合索引最左前缀断了吗?是不是 != / NOT IN?
=== 第三步:开慢查询日志,主动发现 ===
slow_query_log = ON
long_query_time = 1 # 超过 1 秒记下来
log_queries_not_using_indexes = ON # 没走索引的也记
别等用户投诉,让慢查询日志替你巡逻。优化效果
指标 治理前 治理后
=============================================================
DATE(create_time) 查询 全表扫 800 万,1.8s 范围查询走索引,12ms
phone 查询 隐式转换失效,1.2s 类型对齐走索引,5ms
联合索引利用 常跳最左列,半失效 按最左前缀重排,生效
LIKE '%x%' 模糊搜索 全表扫描 迁移到 ES 全文检索
status 等低选择性列 单独建索引也没用 并入覆盖索引才有意义
慢查询日志告警 每天数十条 个位数
未走索引的查询监控 无 开 log_queries_not_using
索引设计评审 无 SQL 上线前过 EXPLAIN
治理过程:
- 梳理慢查询日志、EXPLAIN 全部慢 SQL:1 天
- 改写索引失效的 SQL(函数/类型/最左前缀):1.5 天
- 模糊搜索迁移 ES + 覆盖索引改造:1.5 天
- 慢查询日志 + 未用索引日志接入告警:0.5 天
- 索引规范沉淀 + SQL 上线评审机制:0.5 天避坑清单
- 在索引列上套函数(如 DATE())或做运算会让索引彻底失效,改写成范围查询
- 需要对索引列加工时,把加工动作转移到条件的另一边(常量那边)
- 字符串列用数字比较会触发隐式转换,转换套在索引列上导致失效,参数类型要对齐
- 隐式转换是否失效取决于被转的是哪一边:转索引列才失效,转常量通常不失效
- 联合索引遵守最左前缀,跳过最左列则失效,中间断列则后面的列用不上
- 联合索引中范围查询会截断:某列是范围条件,它后面的列就用不到索引
- LIKE 以 % 开头无法用索引,"包含"式模糊搜索该用全文索引或 ES
- OR 条件中只要有一个列没索引,整条查询就可能退化为全表扫描
- != 、NOT IN 命中行太多,优化器会算账后主动放弃索引,改成正向等值表达
- 排查先 EXPLAIN 看 type/key/rows/Extra,并开慢查询日志主动发现未走索引的 SQL
总结
这次索引失效的排查,纠正了我一个很普遍、也很危险的错觉——以为"给列加了索引"就等于"这个查询会变快"。这两件事之间,其实隔着一道很深的鸿沟,而这道鸿沟最阴险的地方在于:你掉下去的时候,数据库一声不吭。它不会报错,不会警告,它只是默默地把执行计划从"走索引"换成了"全表扫描",然后任由你的查询从十几毫秒退化到一两秒。如果不是慢查询日志攒够了量、告警响了,你甚至不知道自己早就掉进去了。所以这次复盘下来,我把索引失效的场景在脑子里彻底过了一遍,而当我把它们排在一起看的时候,发现它们背后其实只有一个共同的道理。索引的本质,是把某一列的值预先排好序、组织成一棵便于查找的树。它之所以快,全靠"有序"这两个字——因为有序,数据库才能用类似二分查找的方式,快速定位到你要的范围,而不必一行一行地翻。理解了这个本质,几乎所有的失效场景就都能自己推导出来了。为什么在索引列上套一个 DATE() 函数索引就废了?因为索引树里排好序的是 create_time 这一列的原始值,而你现在要找的是 DATE(create_time) 这个"加工后的值"——索引里根本没有这个值的有序结构,数据库别无选择,只能把每一行都捞出来、挨个算一遍 DATE() 再比对,这不就是全表扫描吗。为什么字符串字段用数字去比较会失效?因为 MySQL 的比较规则会把字符串列转成数字,这个转换等价于在索引列外面套了一层 CAST 函数,本质和套 DATE() 是一模一样的。为什么联合索引必须遵守最左前缀?因为联合索引是"先按第一列排,第一列相同再按第二列排"的,就像电话簿先按姓排再按名排,你要是不知道姓、只知道名,这本电话簿对你就没用了。为什么 LIKE 不能以百分号开头?因为索引擅长的是"左边确定、往右找","张%"是左边确定的,而"%志强"左边完全不确定,无从下手。你看,一旦抓住了"索引依赖有序"这个根,函数、类型转换、最左前缀、前导通配符这些看起来杂乱无章的失效场景,就都串成了同一条线——任何破坏了"我能利用这个有序结构去定位"的写法,都会让索引失效。除了这些"语法层面"的失效,这次我还重新认识了另一类情况:索引明明完好可用,优化器却"主动"不用它。一开始我以为这是数据库的 bug,后来才明白这恰恰是它聪明的体现。走二级索引查到的只是主键,要拿到完整的一行还得回表做一次随机 IO,如果一个查询命中的行占了全表很大的比例,优化器一算账:回表几百万次的随机 IO,成本反而比老老实实顺序扫一遍全表还高,那它当然选择全表扫描。这件事教会我,像"状态"这种只有两三个取值的低选择性列,孤零零地给它建一个索引常常是徒劳的,它真正能发挥价值,是被纳入一个覆盖索引——让索引本身就包含了查询所需要的全部列,查到索引就够了、根本不用回表,这样优化器才会心甘情愿地选择它。这次治理之后,我给团队定下的最重要的一条规矩,不是某条具体的索引规则,而是一个动作:任何一条要上线的、查大表的 SQL,提交评审前必须先跑一次 EXPLAIN,亲眼确认它的 type 不是 ALL、key 不是 NULL、rows 没有大得离谱。EXPLAIN 这个命令,就是数据库摊开给你看的"作战计划",它把优化器到底打算怎么执行这条查询、用不用索引、要扫多少行,全都明明白白地写在那里。索引失效之所以能屡屡得手,根本原因就是大家写完 SQL 从来不看这份计划,全凭"我加了索引"的一厢情愿在裸奔。把 EXPLAIN 变成一个像写单元测试一样自然的习惯,索引失效这个潜伏的敌人,就再也没有机会在你不知情的时候,悄悄把你的查询拖垮了。
—— 别看了 · 2026