MySQL索引失效七字口诀 | 模型数空运最快

模

模糊查询 + 子查询

LIKE 以通配符开头 / 不合理的子查询

核心原理

索引底层是 B+ 树，按值有序存储。LIKE '%xxx' 前缀通配符无法确定起始位置，只能从后往前扫，数据库只能全表扫描。子查询在 MySQL 5.x 中效率低下。

B+ 树索引查找示意

张三 → 李四 → 王五

LIKE '张%' ✓

→ 定位到"张"，往后扫描

LIKE '%三' ✗

→ 不知从哪开始，只能全表扫

                索引失效
                -- 前缀通配符，无法利用索引
SELECT * FROM users WHERE name LIKE '%三';
SELECT * FROM users WHERE name LIKE '%三%';
            

                正确写法
                -- 后缀通配符，可以利用索引前缀
SELECT * FROM users WHERE name LIKE '张%';
            

子查询陷阱

在 MySQL 5.7 及之前，IN 子查询可能全表扫描。推荐用 JOIN 或 EXISTS 改写。

                低效子查询
                SELECT * FROM orders WHERE user_id IN (
    SELECT id FROM users WHERE status = 1
);
            

                优化写法
                SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 1;
            

型

隐式类型转换

索引列与比较值类型不一致

核心原理

当类型不匹配时，MySQL 会进行隐式转换。例如字符串列与数字比较，字符串会被转为数字，导致索引失效。

phone (VARCHAR) 与数字比较

phone = '13800138000'

✓ 直接比较

phone = 13800138000

✗ 隐式转换 → CAST('138...' AS INT)

                索引失效
                -- phone 是 VARCHAR，传入数字
SELECT * FROM users WHERE phone = 13800138000;

-- MySQL 实际执行: CAST(phone AS UNSIGNED) = 13800138000
            

                正确写法
                -- 始终使用字符串字面量
SELECT * FROM users WHERE phone = '13800138000';
            

场景	错误	正确
字符串存数字	WHERE age = 123	WHERE age = '123'
数字列字符串值	WHERE id = 'abc'	WHERE id = 123
日期比较	WHERE dt = '2024-01-01'	WHERE dt = '2024-01-01 00:00:00'

数

使用函数/计算

对索引列进行函数运算

核心原理

B+ 树索引按原始值排序。对索引列使用函数后，数据库必须遍历每行计算函数值，无法利用索引的有序性做二分查找。

索引扫描流程对比

有索引: O(log n)

→

二分查找定位

用函数: O(n)

→

逐行计算，全表扫描

                索引失效
                -- 对日期列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2024-03-15';

-- 对字符串列使用函数
SELECT * FROM users WHERE UPPER(name) = 'ZHANGSAN';

-- 对数值列进行计算
SELECT * FROM products WHERE price / 2 > 50;
            

                正确写法
                -- 用范围查询替代函数
SELECT * FROM orders
WHERE create_time >= '2024-03-15 00:00:00'
  AND create_time < '2024-03-16 00:00:00';

-- 移项计算，在常量上运算
SELECT * FROM products WHERE price > 100;
            

MySQL 8.0+ 函数索引

可以直接在表达式上建索引：

CREATE INDEX idx_date ON orders (DATE(create_time));

空

NULL 值判断

IS NULL / IS NOT NULL 导致索引问题

核心原理

MySQL 中 NULL 值不会参与索引（InnoDB 稀疏索引特性）。使用 IS NULL/IS NOT NULL 时，优化器可能选择全表扫描。

B+ 树索引中 NULL 的位置

NULL (不在索引中)

100

200

300

NULL (不在索引中)

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

最佳实践

尽量避免 NULL，设置 NOT NULL 并给默认值：

ALTER TABLE users MODIFY email VARCHAR(255) NOT NULL DEFAULT '';

                优化建议
                -- 用空字符串替代 NULL
SELECT * FROM users WHERE email = '';  -- 可以利用索引
            

运

算术运算

对索引列进行 +、-、*、/ 运算

核心原理

与"数"（使用函数）类似，对索引列进行算术运算会破坏索引的有序性，导致无法二分查找。

                索引失效
                -- 价格打8折查询
SELECT * FROM products WHERE price * 0.8 > 100;

-- 年龄加1
SELECT * FROM users WHERE age + 1 = 30;
            

                正确写法
                -- 移项计算，在常量上运算
SELECT * FROM products WHERE price > 125;  -- 100 / 0.8 = 125
SELECT * FROM users WHERE age = 29;       -- 30 - 1 = 29
            

最

最左前缀原则

复合索引查询不遵循最左匹配

核心原理

复合索引 (a, b, c) 的结构如同按 (a) 分组的 B+ 树，每组内再按 (b) 排序。跳过前面的列直接查询后面的列，无法利用索引。

复合索引 (name, age, city) 结构

第一层: name 排序

Alice → '20岁' → '北京'

Bob → '25岁' → '上海'

Carol → '22岁' → '深圳'

                索引失效
                -- 假设有索引 (name, age, city)
SELECT * FROM users WHERE age = 25;   -- ✗ 跳过 name
SELECT * FROM users WHERE city = '北京';  -- ✗ 跳过 name, age
            

                正确写法
                SELECT * FROM users WHERE name = 'Alice';                   -- ✓ 使用 name
SELECT * FROM users WHERE name = 'Alice' AND age = 20;         -- ✓ 使用 name + age
            

查询条件	索引使用
WHERE name = 'A'	✓ 完全匹配
WHERE name = 'A' AND age = 20	✓ 完全匹配
WHERE name > 'M'	✓ 范围匹配
WHERE age = 20	✗ 无法使用

快

OR/范围查询

OR 连接与范围查询阻断后续索引

核心原理

OR 条件要求所有分支都走索引，任一分支无索引则整体失效。范围查询 (>、<、BETWEEN、IN) 会阻断后续列的索引使用。

OR 查询失效示意

id 有索引 ✓

OR

email 无索引 ✗

→ 整体变成全表扫描!

                索引失效
                -- email 列没有索引，OR 导致整体失效
SELECT * FROM users WHERE id = 1 OR email = 'test@example.com';

-- 范围查询阻断后续 city 索引
SELECT * FROM users WHERE name = 'Alice' AND age > 20 AND city = '北京';
            

                正确写法
                -- 方案1: UNION 拆分
SELECT * FROM users WHERE id = 1
UNION
SELECT * FROM users WHERE email = 'test@example.com';

-- 方案2: 调整复合索引顺序，范围列放最后
CREATE INDEX idx_name_city_age ON users (name, city, age);
            

📋

口诀总结

索引失效七字诀核心要点

模

LIKE %开头
子查询低效

型

类型不匹配
隐式转换

数

函数/计算
破坏有序

空

NULL判断
无法索引

运

算术运算
移项解决

最

最左前缀
顺序重要

快

OR/范围
阻断后续

                开发建议
                写 SQL 前先看 WHERE 条件，是否命中索引
用 EXPLAIN 分析执行计划
避免 SELECT *，只查需要的列
复合索引遵循最左前缀原则
不要在索引列上进行计算或函数操作

            

                快速检查
                -- 查看表的所有索引
SHOW INDEX FROM 表名;

-- 分析 SQL 执行计划
EXPLAIN SELECT * FROM users WHERE ...;
            

MySQL 索引失效七字口诀

核心原理

子查询陷阱

核心原理

核心原理

MySQL 8.0+ 函数索引

核心原理

最佳实践

核心原理

核心原理

核心原理

开发建议