MySQL索引失效的十大罪状 🚫一、开篇故事：图书馆索引的失效之日 📚 想象图书馆有个完美的书籍索引：情景1：管理

一、开篇故事：图书馆索引的失效之日 📚

想象图书馆有个完美的书籍索引：

索引（按书名首字母）:
  A → 1号书架
  B → 2号书架
  C → 3号书架
  ...

情景1：管理员作死操作

读者："我要找《Java编程思想》"

错误做法1（索引失效）:
  管理员："我先把所有书名转成小写再查"
  → 索引是按原始书名排序的
  → 转换后无法使用索引
  → 只能全书架找！😱

错误做法2（索引失效）:
  管理员："我用计算器算一下书名长度..."
  → 索引存的是书名，不是长度
  → 无法使用索引
  → 又要全书架找！💀

错误做法3（索引失效）:
  读者："找名字以'Java'开头的书"
  管理员："我查'%Java'（任意开头）"
  → 索引无法定位起始位置
  → 全书架找！😭

这些就是MySQL索引失效的场景！

二、索引失效十大罪状 ⚠️

罪状1：在索引列上使用函数 🔨

问题

-- ❌ 索引失效（对索引列使用函数）
SELECT * FROM users WHERE YEAR(birthday) = 1990;
SELECT * FROM users WHERE SUBSTRING(phone, 1, 3) = '138';
SELECT * FROM users WHERE UPPER(name) = 'ZHANG SAN';

-- 索引：KEY idx_birthday (birthday)
-- 问题：MySQL需要对每一行的birthday计算YEAR()，无法使用索引

原理

索引结构：
  1985-06-15 → row1
  1990-03-20 → row2  ← 存的是原始值
  1990-08-10 → row3
  1995-12-05 → row4

查询 WHERE YEAR(birthday) = 1990:
  → 需要每行计算 YEAR(birthday)
  → 索引存的是原始日期，不是年份
  → 索引失效！

解决方案

-- ✅ 改写为范围查询
SELECT * FROM users 
WHERE birthday >= '1990-01-01' 
  AND birthday < '1991-01-01';

-- ✅ 使用生成列（MySQL 5.7+）
ALTER TABLE users 
ADD COLUMN birth_year INT AS (YEAR(birthday)) STORED,
ADD INDEX idx_birth_year (birth_year);

SELECT * FROM users WHERE birth_year = 1990;

罪状2：在索引列上进行运算 ➗

问题

-- ❌ 索引失效
SELECT * FROM orders WHERE amount + 10 > 100;
SELECT * FROM products WHERE price * 0.8 < 50;
SELECT * FROM users WHERE id + 1 = 100;

-- 索引：KEY idx_amount (amount)
-- 问题：对索引列进行运算

原理

索引结构：
  50  → row1
  90  → row2  ← 存的是原始值，不是amount+10
  110 → row3
  150 → row4

查询 WHERE amount + 10 > 100:
  → 需要每行计算 amount + 10
  → 索引无法直接定位
  → 全表扫描！

解决方案

-- ✅ 将运算移到右边
SELECT * FROM orders WHERE amount > 90;  -- 100 - 10 = 90
SELECT * FROM products WHERE price < 62.5;  -- 50 / 0.8 = 62.5
SELECT * FROM users WHERE id = 99;  -- 100 - 1 = 99

罪状3：隐式类型转换 🔄

问题

-- ❌ 索引失效（phone是VARCHAR，用数字查询）
SELECT * FROM users WHERE phone = 13812345678;

-- 索引：KEY idx_phone (phone)
-- MySQL会将phone转换为数字：WHERE CAST(phone AS SIGNED) = 13812345678
-- 相当于在索引列上使用函数！

原理

表结构：
  phone VARCHAR(20)  -- 字符串类型

查询：
  WHERE phone = 13812345678  -- 数字类型

MySQL转换：
  WHERE CAST(phone AS SIGNED) = 13812345678
  → 在索引列上使用CAST函数
  → 索引失效！

类型转换规则

-- 字符串 vs 数字：字符串转数字（索引失效）
WHERE varchar_column = 123  -- ❌ varchar转数字，索引失效
WHERE int_column = '123'     -- ✅ '123'转数字，索引有效

-- 字符集不同：转换为utf8mb4（可能失效）
WHERE utf8_column = utf8mb4_value  -- ⚠️ 可能失效

解决方案

-- ✅ 使用正确的类型
SELECT * FROM users WHERE phone = '13812345678';  -- 加引号

-- ✅ 或者修改表结构
ALTER TABLE users MODIFY COLUMN phone BIGINT;

罪状4：LIKE以通配符开头 🌟

问题

-- ❌ 索引失效（以%开头）
SELECT * FROM users WHERE name LIKE '%张三';
SELECT * FROM users WHERE name LIKE '%san%';

-- ⚠️ 部分使用索引（以%结尾）
SELECT * FROM users WHERE name LIKE '张三%';  -- ✅ 可以使用索引

-- 索引：KEY idx_name (name)

原理

索引结构（B+树，按字母顺序）:
  A开头...
  B开头...
  张三    → row1
  张三丰  → row2
  张四    → row3
  Z开头...

查询分析：
  LIKE '张三%':  ← ✅ 可以定位到"张三"开始
    → 从"张三"向后扫描
    → 可以使用索引

  LIKE '%张三':  ← ❌ 无法定位起始位置
    → 不知道从哪开始
    → 需要扫描所有数据
    → 索引失效！

解决方案

-- ✅ 尽量使用前缀匹配
SELECT * FROM users WHERE name LIKE '张%';

-- ✅ 使用全文索引（中文需要分词插件）
ALTER TABLE users ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张三' IN NATURAL LANGUAGE MODE);

-- ✅ 使用ElasticSearch
POST /users/_search
{
  "query": {
    "match": {
      "name": "张三"
    }
  }
}

罪状5：OR条件中有非索引列 ❌

问题

-- ❌ 索引失效
SELECT * FROM users WHERE name = '张三' OR age = 30;

-- 索引情况：
--   KEY idx_name (name)  ← 有索引
--   age 无索引            ← 无索引
-- 结果：整个查询索引失效，全表扫描

原理

查询逻辑：
  name = '张三'  → 可以用索引找到部分数据
  age = 30       → 无索引，需要全表扫描

OR条件：
  → 结果是两部分的并集
  → 既然age要全表扫描
  → MySQL直接全表扫描一次
  → name的索引也不用了

解决方案

-- ✅ 方案1：给所有OR列都建索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);

SELECT * FROM users WHERE name = '张三' OR age = 30;
-- 使用index_merge优化：分别用两个索引，再合并结果

-- ✅ 方案2：改写为UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 30;

-- ✅ 方案3：改写为IN（如果可以）
SELECT * FROM users WHERE name IN ('张三', '李四');  -- 而不是name='张三' OR name='李四'

罪状6：NOT、!=、<> 不等于 🚫

问题

-- ❌ 通常索引失效
SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE status <> 1;
SELECT * FROM users WHERE name NOT LIKE '张%';

-- 索引：KEY idx_status (status)

原理

status取值：
  0: 未激活 (90%)
  1: 已激活 (10%)

查询 WHERE status != 1:
  → 需要返回90%的数据
  → MySQL优化器评估：
    - 用索引：先索引扫描 + 回表 (90%的数据要回表)
    - 全表扫描：直接扫描
  → 全表扫描更快！
  → 放弃索引

何时会使用索引？

-- ✅ 当不等于的数据量很小时，会使用索引
SELECT * FROM users WHERE status != 0;
-- status=0占90%，status!=0只占10%
-- MySQL评估后可能使用索引

-- 可以用EXPLAIN验证
EXPLAIN SELECT * FROM users WHERE status != 0;

解决方案

-- ✅ 改写为具体值
SELECT * FROM users WHERE status IN (0, 2, 3);  -- 明确列出所有不等于1的值

-- ✅ 使用覆盖索引（不需要回表）
CREATE INDEX idx_status_id ON users(status, id);
SELECT id FROM users WHERE status != 1;  -- 覆盖索引，不需要回表

罪状7：IS NULL / IS NOT NULL 🈳

问题

-- ⚠️ 可能索引失效
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 索引：KEY idx_email (email)

原理

取决于NULL值的分布：

场景1（大量NULL）:
  email IS NULL      → 返回90%的数据 → 全表扫描
  email IS NOT NULL  → 返回10%的数据 → 可能使用索引 ✅

场景2（少量NULL）:
  email IS NULL      → 返回10%的数据 → 可能使用索引 ✅
  email IS NOT NULL  → 返回90%的数据 → 全表扫描

解决方案

-- ✅ 避免NULL值（设计表时）
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL DEFAULT '',  -- 不允许NULL
    phone VARCHAR(20) NOT NULL DEFAULT ''
);

-- ✅ 使用默认值代替NULL
UPDATE users SET email = '' WHERE email IS NULL;
SELECT * FROM users WHERE email = '';  -- 可以使用索引

罪状8：违反联合索引最左匹配原则 ⬅️

问题

-- 联合索引：KEY idx_abc (a, b, c)

-- ✅ 使用索引
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;

-- ❌ 索引失效（跳过了a）
SELECT * FROM t WHERE b = 2;
SELECT * FROM t WHERE c = 3;
SELECT * FROM t WHERE b = 2 AND c = 3;

-- ⚠️ 部分使用（只用a）
SELECT * FROM t WHERE a = 1 AND c = 3;  -- 只用到a，c用不到

原理

联合索引结构 (a, b, c):
  → 先按a排序
  → a相同的，按b排序
  → b相同的，按c排序

(1, 1, 1)
(1, 1, 2)
(1, 2, 1)
(1, 2, 2)
(2, 1, 1)  ← 注意：b和c单独看不是有序的
(2, 1, 2)
(2, 2, 1)

查询 WHERE b = 2:
  → b的值在整体上不是有序的
  → 无法使用索引定位
  → 索引失效！

解决方案

-- ✅ 建立合适的索引
CREATE INDEX idx_abc ON t(a, b, c);  -- 常用组合
CREATE INDEX idx_b ON t(b);          -- 单独查b时用
CREATE INDEX idx_c ON t(c);          -- 单独查c时用

-- ✅ 调整WHERE条件顺序（不影响，MySQL会优化）
WHERE a = 1 AND b = 2  -- ✅
WHERE b = 2 AND a = 1  -- ✅ MySQL会自动调整

罪状9：范围查询后的列无法使用索引 📏

问题

-- 联合索引：KEY idx_abc (a, b, c)

SELECT * FROM t WHERE a = 1 AND b > 10 AND c = 3;

-- 索引使用情况：
--   a = 1    → ✅ 使用索引
--   b > 10   → ✅ 使用索引（范围查询）
--   c = 3    → ❌ 无法使用索引（范围查询后）

原理

联合索引 (a, b, c) 的结构:
(1, 5,  1)
(1, 5,  3)
(1, 11, 2)  ← b > 10 从这里开始
(1, 11, 5)
(1, 15, 1)
(1, 15, 3)  ← c的值不是有序的！
(1, 20, 2)

查询 WHERE a = 1 AND b > 10 AND c = 3:
  → a = 1: 定位到a=1的范围 ✅
  → b > 10: 在a=1的范围内，找b>10 ✅
  → c = 3: 但b>10的范围内，c不是有序的 ❌
  → 只能遍历b>10的所有数据，过滤c=3

解决方案

-- ✅ 调整索引顺序（把范围查询列放最后）
CREATE INDEX idx_acb ON t(a, c, b);  -- 范围查询的b放最后

SELECT * FROM t WHERE a = 1 AND c = 3 AND b > 10;
-- 索引使用：a=1 ✅, c=3 ✅, b>10 ✅

-- ✅ 或者拆分索引
CREATE INDEX idx_ab ON t(a, b);
CREATE INDEX idx_ac ON t(a, c);

罪状10：SELECT * 导致无法使用覆盖索引 📋

问题

-- 联合索引：KEY idx_name_age (name, age)

-- ❌ 需要回表（因为SELECT *）
SELECT * FROM users WHERE name = '张三';

-- ✅ 覆盖索引（不需要回表）
SELECT name, age FROM users WHERE name = '张三';

原理

索引结构 idx_name_age (name, age):
  索引叶子节点存储：name + age + 主键id

查询 SELECT * FROM users WHERE name = '张三':
  1. 在idx_name_age索引中找到name='张三'的记录
  2. 获取到主键id
  3. 回表：根据id查主表，获取所有字段（*, 包括phone, email等）

查询 SELECT name, age FROM users WHERE name = '张三':
  1. 在idx_name_age索引中找到name='张三'的记录
  2. 索引中已经有name和age
  3. 直接返回，不需要回表！（覆盖索引）

性能对比

-- 测试数据：100万行
-- 查询name='张三'的数据（1000行）

-- SELECT * （需要回表）
执行时间：50ms
  → 索引扫描：5ms
  → 回表1000次：45ms

-- SELECT name, age （覆盖索引）
执行时间：5ms
  → 索引扫描：5ms
  → 回表：0ms

性能提升：10倍！

解决方案

-- ✅ 只查询需要的字段
SELECT id, name, age FROM users WHERE name = '张三';

-- ✅ 建立包含常用字段的索引
CREATE INDEX idx_name_age_phone ON users(name, age, phone);
SELECT name, age, phone FROM users WHERE name = '张三';  -- 覆盖索引

三、索引失效检测工具 🔍

3.1 EXPLAIN详解

EXPLAIN SELECT * FROM users WHERE YEAR(birthday) = 1990;

-- 关键字段：
--   type: ALL（全表扫描，最差）
--   key: NULL（未使用索引）
--   rows: 1000000（扫描100万行）
--   Extra: Using where（使用WHERE过滤）

3.2 type类型（性能从好到差）

性能排序：
  system > const > eq_ref > ref > range > index > ALL

✅ 好的type:
  const:   主键或唯一索引等值查询
  eq_ref:  主键或唯一索引关联查询
  ref:     普通索引等值查询
  range:   范围查询

❌ 差的type:
  index:   全索引扫描
  ALL:     全表扫描（最差）

3.3 实战案例

-- 案例1：函数导致索引失效
EXPLAIN SELECT * FROM users WHERE YEAR(birthday) = 1990;

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

分析：
  type: ALL         ← ❌ 全表扫描
  key: NULL         ← ❌ 未使用索引
  rows: 100000      ← ❌ 扫描10万行

-- 优化后
EXPLAIN SELECT * FROM users 
WHERE birthday >= '1990-01-01' AND birthday < '1991-01-01';

+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | idx_birthday  | idx_birthday | 4       | NULL | 2500 | Using where |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+

分析：
  type: range          ← ✅ 范围查询
  key: idx_birthday    ← ✅ 使用索引
  rows: 2500           ← ✅ 只扫描2500行（从10万减少到2500）

四、索引失效场景总结表 📊

罪状	示例	原因	解决方案
使用函数	`WHERE YEAR(date) = 2020`	对索引列计算	改为范围查询
列运算	`WHERE id + 1 = 10`	对索引列运算	移到右边：`id = 9`
类型转换	`WHERE varchar = 123`	隐式转换	使用正确类型
%开头LIKE	`WHERE name LIKE '%张'`	无法定位起始位置	改为前缀匹配或全文索引
OR非索引列	`WHERE a = 1 OR b = 2`	b无索引	都加索引或改UNION
!=不等于	`WHERE status != 1`	返回数据量大	改为IN或覆盖索引
IS NULL	`WHERE col IS NULL`	取决于NULL分布	避免NULL值
违反最左	`WHERE b = 2 AND c = 3`	跳过了a	调整索引或单独建索引
范围查询后	`WHERE a=1 AND b>10 AND c=3`	c不有序	调整索引顺序
SELECT *	`SELECT *`	无法覆盖索引	只查需要的字段

五、实战优化案例 💼

案例：慢查询优化

原始SQL

SELECT * FROM orders 
WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01'
  AND status != 3
  OR amount > 1000;

-- 执行时间：5秒
-- 扫描行数：100万行

问题分析

1. DATE_FORMAT(create_time, ...)  ← ❌ 函数导致索引失效
2. status != 3                     ← ❌ 不等于可能失效
3. OR amount > 1000                ← ❌ OR条件，如果amount无索引则全表扫描
4. SELECT *                        ← ❌ 无法覆盖索引

优化SQL

-- 优化1：去除函数
SELECT id, order_no, amount, status, create_time
FROM orders 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2024-02-01'
  AND status IN (0, 1, 2, 4, 5)  -- 列出所有不等于3的值
UNION
SELECT id, order_no, amount, status, create_time
FROM orders 
WHERE amount > 1000;

-- 执行时间：50ms（从5秒降到50ms，提升100倍！）
-- 扫描行数：5万行（从100万降到5万）

索引优化

-- 建立合适的索引
CREATE INDEX idx_time_status ON orders(create_time, status);
CREATE INDEX idx_amount ON orders(amount);

-- 或者建立覆盖索引
CREATE INDEX idx_time_status_cover ON orders(create_time, status, id, order_no, amount);

六、面试高频问题 🎤

Q1: 列举索引失效的场景？

答：

在索引列上使用函数（WHERE YEAR(date) = 2020）
在索引列上进行运算（WHERE id + 1 = 10）
隐式类型转换（WHERE varchar_col = 123）
LIKE以%开头（WHERE name LIKE '%张'）
OR条件中有非索引列
!=、NOT等不等于操作（数据量大时）
IS NULL / IS NOT NULL（取决于NULL分布）
违反联合索引最左匹配原则
范围查询后的列无法使用索引
SELECT * 导致无法使用覆盖索引

Q2: 为什么在索引列上使用函数会导致索引失效？

答：因为索引存储的是原始值，不是函数计算后的值。例如：

索引存储的是birthday原始日期
查询WHERE YEAR(birthday) = 1990需要对每行计算YEAR()
无法使用索引定位，只能全表扫描

Q3: 如何判断索引是否失效？

答：使用EXPLAIN分析：

type: ALL → 全表扫描，索引失效
key: NULL → 未使用索引
rows: 很大 → 扫描行数多
Extra: Using where → 需要WHERE过滤（可能失效）

Q4: 联合索引(a,b,c)，WHERE a=1 AND c=3能用到索引吗？

答：部分使用，只用到a，c用不到。因为联合索引是先按a排序，a相同再按b排序，b相同再按c排序。跳过b直接查c，c的值不是有序的，无法使用索引。

Q5: 如何优化LIKE '%keyword%'的查询？

答：

改为前缀匹配：LIKE 'keyword%'
使用全文索引：MATCH(col) AGAINST('keyword')
使用ElasticSearch等搜索引擎
业务上避免这种查询

七、总结口诀 📝

索引失效十大罪，
函数运算要记牢。
类型转换要注意，
LIKE通配符别乱搞。

OR条件要小心，
不等于要慎用。
NULL值有陷阱，
最左匹配莫违反。

范围查询有讲究，
后面的列用不上。
SELECT星号要少用，
覆盖索引性能强！

EXPLAIN常分析，
type和key要看好。
优化查询有技巧，
性能提升错不了！

参考资料 📚

下期预告： 148-联合索引的最左匹配原则和索引下推优化 ⬅️

编写时间：2025年
作者：技术文档小助手 ✍️
版本：v1.0

愿你的索引永不失效！ 🚀✨