)

🌺The Begin🌺点点关注，收藏不迷路🌺

很多开发者都有这样的困惑：明明给字段建了索引，查询为什么还是慢？EXPLAIN一看，发现根本没走索引！索引并非万能药，有很多场景会导致MySQL放弃使用索引。本文将全面总结索引失效的典型场景，并提供一套完整的排查方法论。

1. 索引失效全景图

2. 十大索引失效场景详解

2.1 违反最左前缀原则

联合索引时，查询条件必须从索引最左列开始，否则索引失效。

-- 创建联合索引 (name, age, city)
CREATE INDEX idx_name_age_city ON user(name, age, city);

-- ✅ 生效：包含最左列name
EXPLAIN SELECT * FROM user WHERE name = 'Tom' AND age = 20;

-- ❌ 失效：缺少最左列name
EXPLAIN SELECT * FROM user WHERE age = 20 AND city = 'Beijing';

2.2 在索引列上使用函数

对索引列做函数计算或表达式改造，MySQL无法使用原索引。

-- ✅ 生效
EXPLAIN SELECT * FROM user WHERE name = 'Tom';

-- ❌ 失效：使用了函数
EXPLAIN SELECT * FROM user WHERE UPPER(name) = 'TOM';

-- ✅ 改写方案
EXPLAIN SELECT * FROM user WHERE name = 'TOM';  -- 应用层转大写

-- MySQL 8.0+ 函数索引解决方案
CREATE INDEX idx_upper_name ON user((UPPER(name)));

2.3 隐式类型转换

当字段类型与查询值类型不匹配时，MySQL会隐式转换，导致索引失效。

-- phone字段定义为 VARCHAR(20)
-- ✅ 生效：字符串匹配字符串
EXPLAIN SELECT * FROM user WHERE phone = '13800138000';

-- ❌ 失效：隐式转换，相当于 CAST(phone AS INT)=13800138000
EXPLAIN SELECT * FROM user WHERE phone = 13800138000;

2.4 LIKE 前导通配符

LIKE '%xxx' 或 LIKE '_xxx' 导致无法从索引头部开始匹配。

-- ✅ 生效：前缀匹配
EXPLAIN SELECT * FROM user WHERE name LIKE '张%';

-- ❌ 失效：前导通配符
EXPLAIN SELECT * FROM user WHERE name LIKE '%三';

-- ❌ 失效：中间通配符，前缀不固定
EXPLAIN SELECT * FROM user WHERE name LIKE '_三';

2.5 OR 连接不当

OR前后存在非索引列时，可能全表扫描。

-- 假设 name 有索引，age 无索引
-- ❌ 失效：age无索引
EXPLAIN SELECT * FROM user WHERE name = 'Tom' OR age = 20;

-- ✅ 方案1：两边都建索引
CREATE INDEX idx_age ON user(age);

-- ✅ 方案2：用UNION代替OR
SELECT * FROM user WHERE name = 'Tom'
UNION
SELECT * FROM user WHERE age = 20;

2.6 使用 NOT IN / NOT EXISTS

负向查询通常导致索引失效。

-- ❌ 可能失效
EXPLAIN SELECT * FROM user WHERE name NOT IN ('Tom', 'Jerry');

-- ⚠️ IN 通常有效
EXPLAIN SELECT * FROM user WHERE name IN ('Tom', 'Jerry');

-- ✅ NOT EXISTS 优化
SELECT * FROM user u1 
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.name = u1.name);

2.7 IS NULL / IS NOT NULL 的陷阱

-- ⚠️ IS NULL 通常可用
EXPLAIN SELECT * FROM user WHERE name IS NULL;

-- ⚠️ IS NOT NULL 可能失效（取决于数据分布）
EXPLAIN SELECT * FROM user WHERE name IS NOT NULL;

2.8 索引列参与计算

-- ❌ 失效
EXPLAIN SELECT * FROM user WHERE age + 10 = 30;

-- ✅ 改写
EXPLAIN SELECT * FROM user WHERE age = 20;

2.9 使用 != 或 <>

不等值查询通常导致索引失效。

-- ❌ 大概率失效
EXPLAIN SELECT * FROM user WHERE age != 20;

2.10 索引选择性过低

若优化器判断全表扫描更快，会放弃索引。

-- 假设status只有'0'和'1'，'1'占比90%
-- ❌ 优化器可能不走索引
EXPLAIN SELECT * FROM orders WHERE status = '1';

3. 索引效果排查流程图

4. EXPLAIN 核心字段解读

EXPLAIN SELECT * FROM user WHERE name = 'Tom';

字段	含义	好	差
type	访问类型	const/eq_ref/ref/range	ALL/index
possible_keys	可能使用的索引	有值	NULL
key	实际使用的索引	有值	NULL
key_len	索引使用长度	越大越好	越小
rows	预估扫描行数	越小越好	大
Extra	额外信息	Using index	Using filesort

type类型性能排序：

const > eq_ref > ref > range > index > ALL
 最快                             最慢

5. 实战排查案例

案例1：明明有索引，type却是ALL

-- 表结构
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_no VARCHAR(32),
    status TINYINT,
    INDEX idx_order_no(order_no)
);

-- 问题查询
EXPLAIN SELECT * FROM orders WHERE order_no = 12345;
-- type: ALL, key: NULL

-- 原因：隐式类型转换，order_no是VARCHAR，传入INT
-- ✅ 修复
EXPLAIN SELECT * FROM orders WHERE order_no = '12345';
-- type: ref, key: idx_order_no

案例2：联合索引只用了部分

CREATE INDEX idx_name_age ON user(name, age);

EXPLAIN SELECT * FROM user WHERE name = 'Tom' AND age = 20;
-- key_len: 验证使用了完整索引

EXPLAIN SELECT * FROM user WHERE name = 'Tom';
-- key_len: 只用name部分

EXPLAIN SELECT * FROM user WHERE age = 20;
-- key: NULL 完全失效

案例3：统计信息过期

-- 查看统计信息
SHOW INDEX FROM user;

-- 更新统计信息
ANALYZE TABLE user;

-- 查看优化器追踪（MySQL 8.0+）
SET optimizer_trace = 'enabled=on';
SELECT * FROM user WHERE name = 'Tom';
SELECT * FROM information_schema.OPTIMIZER_TRACE;

6. 强制使用索引的方法

-- FORCE INDEX：强制使用指定索引
SELECT * FROM user FORCE INDEX(idx_name) WHERE name = 'Tom';

-- USE INDEX：建议使用（优化器可能忽略）
SELECT * FROM user USE INDEX(idx_name) WHERE name = 'Tom';

-- IGNORE INDEX：忽略索引
SELECT * FROM user IGNORE INDEX(idx_name) WHERE name = 'Tom';

-- MySQL 8.0+ 优化器提示
SELECT /*+ INDEX(user idx_name) */ * FROM user WHERE name = 'Tom';

7. 索引失效自检清单

检查项	SQL示例	是否使用索引
最左前缀	WHERE age=20 (索引name,age)	❌
无函数	WHERE UPPER(name)='TOM'	❌
类型一致	WHERE phone=138... (phone是VARCHAR)	❌
LIKE非前导	WHERE name LIKE '%Tom'	❌
OR全索引列	WHERE name='Tom' OR age=20 (age无索引)	❌
非负向查询	WHERE name NOT IN (...), WHERE name != 'Tom'	⚠️

8. 总结与记忆口诀

索引失效排查口诀：

最左前缀不能少，函数计算要不了。
隐式转换坑最大，LIKE前导全扫描。
OR连接两边建，负向查询需谨慎。
NULL/NOT NULL看分布，统计信息常更新。
EXPLAIN是照妖镜，type extra仔细瞧。

优化优先级：
1️⃣ 先看 type：ALL/index → 必须优化
2️⃣ 再看 key：NULL → 索引失效
3️⃣ 三看 rows：太大 → 过滤性差
4️⃣ 最后 Extra：filesort/temporary → 需优化

---

📌 本文要点

索引不是万能的：10+种场景会导致索引失效
EXPLAIN是核心工具：type、key、Extra三字段是重点
定期维护：ANALYZATE TABLE更新统计信息
应用层配合：类型一致、避免函数、遵循最左前缀

🌺The End🌺点点关注，收藏不迷路🌺