一、第一步定位 SQL 是否走索引必用 EXPLAINEXPLAIN SELECT * FROM t WHERE 条件; EXPLAIN UPDATE t SET xxx WHERE 条件; EXPLAIN DELETE FROM t WHERE 条件;关键字段判断索引失效type列优先级从优到差system const eq_ref ref range index ALL出现ALL全表扫描索引完全失效index扫全部索引树比全表略好但仍算失效场景key列NULL没用到任何索引失效rows列 扫描行数接近表总行数代表索引没生效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看 typeALL且字段字符集和参数不一致。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 id1 OR phone13800000000; 索引有效写法 -- 优化1两边都加索引 -- 优化2UNION ALL 拆分两条带索引查询 SELECT * FROM t WHERE id1 UNION ALL SELECT * FROM t WHERE phone13800000000;7. 字符集不一致关联查询失效表 Autf8mb4表 Butf8关联条件A.phoneB.phone字段字符集不同自动隐式转换索引失效 解决统一所有表、字段字符集utf8mb48. 数据区分度太低选择性差索引字段重复值超多优化器判定走索引不如全表 例status 只有 0/199% 数据都是 1-- 百万级表几乎全是1索引失效 SELECT * FROM order WHERE status 1;解决联合索引、分区、冷热数据分表9. MySQL 优化器判断成本过高主动放弃索引即使有索引数据量大、分页偏移大时优化器选全表-- offset超大索引失效 SELECT * FROM t LIMIT 1000000,10;优化主键分页WHERE id 上次最大id LIMIT 1010. NOT EXISTS / NOT LIKE 否定查询否定类条件大多无法利用索引扫描全表11. 批量 IN 数据量过大IN 里面上千个值优化器放弃索引改用全表扫描 解决分批查询、临时表关联12. 事务隔离级别 间隙锁导致索引看似失效RR 模式下范围更新会加 Next-key 锁表现为锁范围很大不是索引失效是锁机制问题区分开。三、线上快速排查步骤拿慢 SQL / 死锁报错 SQL执行EXPLAIN看 typeALL、keyNULL → 确认索引失效核对是否函数、运算、模糊后缀、OR、!、NOT IN查看字段与参数类型是否一致隐式转换联合索引核对最左前缀是否缺失查询字段区分度SELECT COUNT(DISTINCT col)/COUNT(*) FROM t;比值低于 0.1索引基本无效关联查询核对两张表字段字符集、排序规则大分页检查 offset 是否过大四、线上应急修复规范禁止在索引列写函数条件常量侧运算字符串查询参数必须传字符串数字传数字联合索引业务查询优先带上最左字段模糊查询只用前缀匹配全模糊用全文索引OR 查询拆 UNION ALL保证两边都走索引低区分度字段不单独建单值索引搭配其他字段做联合索引分页避免大 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表名;六、区分两个易混淆问题索引失效EXPLAIN typeALL完全不走索引扫描全表并发下极易行锁变表锁触发死锁索引生效但锁范围大SQL 走索引但使用范围条件RR 隔离产生 Gap 间隙锁互相等待死锁 两者排查方式不同先 EXPLAIN 分清根源。
一、第一步定位 SQL 是否走索引必用 EXPLAINEXPLAIN SELECT * FROM t WHERE 条件; EXPLAIN UPDATE t SET xxx WHERE 条件; EXPLAIN DELETE FROM t WHERE 条件;关键字段判断索引失效type列优先级从优到差system const eq_ref ref range index ALL出现ALL全表扫描索引完全失效index扫全部索引树比全表略好但仍算失效场景key列NULL没用到任何索引失效rows列 扫描行数接近表总行数代表索引没生效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看 typeALL且字段字符集和参数不一致。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 id1 OR phone13800000000; 索引有效写法 -- 优化1两边都加索引 -- 优化2UNION ALL 拆分两条带索引查询 SELECT * FROM t WHERE id1 UNION ALL SELECT * FROM t WHERE phone13800000000;7. 字符集不一致关联查询失效表 Autf8mb4表 Butf8关联条件A.phoneB.phone字段字符集不同自动隐式转换索引失效 解决统一所有表、字段字符集utf8mb48. 数据区分度太低选择性差索引字段重复值超多优化器判定走索引不如全表 例status 只有 0/199% 数据都是 1-- 百万级表几乎全是1索引失效 SELECT * FROM order WHERE status 1;解决联合索引、分区、冷热数据分表9. MySQL 优化器判断成本过高主动放弃索引即使有索引数据量大、分页偏移大时优化器选全表-- offset超大索引失效 SELECT * FROM t LIMIT 1000000,10;优化主键分页WHERE id 上次最大id LIMIT 1010. NOT EXISTS / NOT LIKE 否定查询否定类条件大多无法利用索引扫描全表11. 批量 IN 数据量过大IN 里面上千个值优化器放弃索引改用全表扫描 解决分批查询、临时表关联12. 事务隔离级别 间隙锁导致索引看似失效RR 模式下范围更新会加 Next-key 锁表现为锁范围很大不是索引失效是锁机制问题区分开。三、线上快速排查步骤拿慢 SQL / 死锁报错 SQL执行EXPLAIN看 typeALL、keyNULL → 确认索引失效核对是否函数、运算、模糊后缀、OR、!、NOT IN查看字段与参数类型是否一致隐式转换联合索引核对最左前缀是否缺失查询字段区分度SELECT COUNT(DISTINCT col)/COUNT(*) FROM t;比值低于 0.1索引基本无效关联查询核对两张表字段字符集、排序规则大分页检查 offset 是否过大四、线上应急修复规范禁止在索引列写函数条件常量侧运算字符串查询参数必须传字符串数字传数字联合索引业务查询优先带上最左字段模糊查询只用前缀匹配全模糊用全文索引OR 查询拆 UNION ALL保证两边都走索引低区分度字段不单独建单值索引搭配其他字段做联合索引分页避免大 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表名;六、区分两个易混淆问题索引失效EXPLAIN typeALL完全不走索引扫描全表并发下极易行锁变表锁触发死锁索引生效但锁范围大SQL 走索引但使用范围条件RR 隔离产生 Gap 间隙锁互相等待死锁 两者排查方式不同先 EXPLAIN 分清根源。
