排查索引失效

一、第一步：定位 SQL 是否走索引（必用 EXPLAIN）

EXPLAIN SELECT * FROM t WHERE 条件; EXPLAIN UPDATE t SET xxx WHERE 条件; EXPLAIN DELETE FROM t WHERE 条件;

关键字段判断索引失效

  1. type列（优先级从优到差）
    • system > const > eq_ref > ref > range > index > ALL
    • 出现ALL：全表扫描，索引完全失效
    • index：扫全部索引树，比全表略好但仍算失效场景
  2. key列
    • NULL：没用到任何索引，失效
  3. rows列 扫描行数接近表总行数，代表索引没生效
  4. Extra列关键字
    • Using where; Using index：覆盖索引正常
    • Using temporary：临时表，索引失效
    • Using filesort：文件排序，索引排序失效
    • Using join buffer (Block Nested Loop)：关联无索引

二、12 种高频索引失效原因（线上最常见）

1. 隐式类型转换（TOP1 线上坑）

字段类型和查询参数不匹配，导致索引失效

-- user_id varchar(20) 字符串索引 SELECT * FROM user WHERE user_id = 123; -- 数字匹配字符串，隐式转换失效 正确改写 -- 正确：同类型 SELECT * FROM user WHERE user_id = '123';

判断：EXPLAIN看 type=ALL，且字段字符集和参数不一致。

2. 索引列使用函数 / 运算

索引存储原始值，运算后无法匹配索引树

-- 失效 SELECT * FROM t WHERE DATE(create_time) = '2026-07-17'; SELECT * FROM t WHERE id + 1 = 100; SELECT * FROM t WHERE SUBSTR(name,1,1)='张'; 正确改写 -- 改写（把函数放常量侧） SELECT * FROM t WHERE create_time BETWEEN '2026-07-17 00:00:00' AND '2026-07-17 23:59:59'; SELECT * FROM t WHERE id = 99;

3. 联合索引不满足最左匹配原则

联合索引idx(a,b,c)

  • 有效：where a=? /where a=? and b=? /where a=? and b=? and c=?
  • 失效：where b=? /where c=? /where b=? and c=?

跳过最左前列，整个索引无法使用

4. 索引列使用！= <> NOT IN IS NOT NULL

这类条件会放弃索引走全表扫描

-- 失效 SELECT * FROM t WHERE status != 1; SELECT * FROM t WHERE id NOT IN (1,2,3); SELECT * FROM t WHERE phone IS NOT NULL; -- 优化：反向拆分、分页过滤、业务换状态枚举

5. LIKE 前缀模糊匹配 % xxx

-- 失效，无法走索引 SELECT * FROM t WHERE name LIKE '%张三'; 索引有效写法 -- 有效，前缀固定 SELECT * FROM t WHERE name LIKE '张三%';

需求必须前后模糊：用全文索引MATCH(name) AGAINST('张三')

6. OR 左右字段只有一边有索引

-- 失效：phone无索引，整条放弃索引 SELECT * FROM t WHERE id=1 OR phone='13800000000'; 索引有效写法 -- 优化1：两边都加索引 -- 优化2：UNION ALL 拆分两条带索引查询 SELECT * FROM t WHERE id=1 UNION ALL SELECT * FROM t WHERE phone='13800000000';

7. 字符集不一致（关联查询失效）

表 Autf8mb4，表 Butf8，关联条件A.phone=B.phone字段字符集不同，自动隐式转换，索引失效 解决：统一所有表、字段字符集utf8mb4

8. 数据区分度太低（选择性差）

索引字段重复值超多，优化器判定走索引不如全表 例：status 只有 0/1，99% 数据都是 1

-- 百万级表，几乎全是1，索引失效 SELECT * FROM order WHERE status = 1;

解决：联合索引、分区、冷热数据分表

9. MySQL 优化器判断成本过高，主动放弃索引

即使有索引，数据量大、分页偏移大时优化器选全表

-- offset超大，索引失效 SELECT * FROM t LIMIT 1000000,10;

优化：主键分页WHERE id > 上次最大id LIMIT 10

10. NOT EXISTS / NOT LIKE 否定查询

否定类条件大多无法利用索引，扫描全表

11. 批量 IN 数据量过大

IN 里面上千个值，优化器放弃索引，改用全表扫描 解决：分批查询、临时表关联

12. 事务隔离级别 + 间隙锁导致索引看似失效

RR 模式下范围更新会加 Next-key 锁，表现为锁范围很大，不是索引失效，是锁机制问题，区分开。

三、线上快速排查步骤

  1. 拿慢 SQL / 死锁报错 SQL，执行EXPLAIN
  2. 看 type=ALL、key=NULL → 确认索引失效
  3. 核对是否函数、运算、模糊后缀、OR、!=、NOT IN
  4. 查看字段与参数类型是否一致（隐式转换）
  5. 联合索引核对最左前缀是否缺失
  6. 查询字段区分度：SELECT COUNT(DISTINCT col)/COUNT(*) FROM t;比值低于 0.1，索引基本无效
  7. 关联查询核对两张表字段字符集、排序规则
  8. 大分页检查 offset 是否过大

四、线上应急修复规范

  1. 禁止在索引列写函数，条件常量侧运算
  2. 字符串查询参数必须传字符串，数字传数字
  3. 联合索引业务查询优先带上最左字段
  4. 模糊查询只用前缀匹配，全模糊用全文索引
  5. OR 查询拆 UNION ALL，保证两边都走索引
  6. 低区分度字段不单独建单值索引，搭配其他字段做联合索引
  7. 分页避免大 offset，使用主键游标分页

五、辅助排查 SQL 脚本

-- 1. 查看表索引 SHOW INDEX FROM table_name; -- 2. 查看字段字符集、排序规则 DESC table_name; SELECT COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='库名' AND TABLE_NAME='表名'; -- 3. 统计字段区分度 SELECT COUNT(DISTINCT 字段)/COUNT(*) AS selectivity FROM table_name; -- 4. 查看表总行数 SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA='库名' AND TABLE_NAME='表名';

六、区分两个易混淆问题

  1. 索引失效：EXPLAIN type=ALL，完全不走索引，扫描全表，并发下极易行锁变表锁，触发死锁
  2. 索引生效但锁范围大：SQL 走索引，但使用范围条件，RR 隔离产生 Gap 间隙锁，互相等待死锁 两者排查方式不同，先 EXPLAIN 分清根源。