MySQL索引失效的常见原因与优化实战指南-是一个基于内容分享折腾笔记的网站序与诗

引言

在日常开发中，很多MySQL性能问题的根源都来自索引失效。即使字段上建了索引，查询却依然全表扫描，导致接口响应缓慢。本文系统梳理了索引失效的常见原因，并给出对应的优化实践。

1. 最左前缀原则（Prefix）被破坏

复合索引遵循最左前缀原则，查询必须从最左边列开始，才能使用索引。

典型案例

-- 创建复合索引: (a, b, c)
CREATE INDEX idx_abc ON users(a, b, c);

-- ✅ 走索引: 用了最左列
SELECT * FROM users WHERE a = 1;

-- ✅ 走索引: 用了 (a, b)
SELECT * FROM users WHERE a = 1 AND b = 2;

-- ❌ 不走索引: 跳过了 a，从 b 开始
SELECT * FROM users WHERE b = 2;

-- ❌ 不走索引: 虽然用了 a 但加了函数
SELECT * FROM users WHERE LEFT(a, 4) = 'test';

解决方案

调整查询顺序，确保从索引最左列开始
如果业务确实需要跳过前导列，考虑建立独立的单列索引

2. 隐式类型转换导致索引失效

当字段类型与比较值类型不一致时，MySQL会自动进行隐式类型转换，导致索引失效。

-- user_id 是 BIGINT 类型
-- ❌ 字符串转数字，全表扫描
SELECT * FROM orders WHERE user_id = '12345';

-- ✅ 数字类型，走索引
SELECT * FROM orders WHERE user_id = 12345;

-- ✅ 显式转换，全表扫描（同样失效）
SELECT * FROM orders WHERE user_id = CAST('12345' AS UNSIGNED);

⚠️ 注意：即使 WHERE 条件写对了，在 JOIN 中类型不匹配同样会导致索引失效。建议始终保持关联字段类型一致。

3. 使用函数或表达式

在索引列上使用函数、计算或表达式，MySQL无法使用索引树结构。

-- ❌ 对索引列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2026;
SELECT * FROM users WHERE SUBSTRING(name, 1, 3) = '张';
SELECT * FROM users WHERE id + 1 = 100;

-- ✅ 改写为范围查询或前置计算
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- ✅ 业务层预处理，查询时直接比较
SELECT * FROM users WHERE name LIKE '张%';

4. 模糊查询以通配符开头

-- ✅ 走索引，前置匹配
SELECT * FROM products WHERE name LIKE '华为%';

-- ❌ 不走索引，以通配符开头
SELECT * FROM products WHERE name LIKE '%华为%';
SELECT * FROM products WHERE name LIKE '%华为';

如果必须做全模糊搜索，考虑使用全文索引（FULLTEXT）或 Elasticsearch 等外部搜索引擎。

5. 范围查询后缀索引失效

-- 索引: (status, created_at)
-- ✅ 走索引(status + 部分created_at)
SELECT * FROM orders WHERE status = 1 AND created_at > '2026-01-01';

-- ❌ 范围查询断了，后续列无法使用索引
SELECT * FROM orders WHERE status > 1;

当复合索引中有列使用范围条件（>、<、BETWEEN、IN）时，该列右边的列不会被用于索引查找。优化思路是将高筛选条件放在前面，范围条件放在后面，或拆分为多个索引。

6. 使用 OR 导致的索引断裂

-- ❌ OR 导致部分条件不走索引（除非两列都有索引）
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- ✅ 拆分为 UNION（两个查询都走索引）
(SELECT * FROM users WHERE name = '张三')
UNION ALL
(SELECT * FROM users WHERE age = 25 AND name != '张三');

-- ✅ 使用 IN 替代（语义相近时）
SELECT * FROM users WHERE name IN ('张三', '李四');

7. 使用 EXPLAIN 分析查询计划

优化之前，先用 EXPLAIN 查看执行计划，找到真正的瓶颈。

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

关键字段解读：

type: 性能从优到差: system > const > eq_ref > ref > range > index > ALL（ALL 表示全表扫描）
key: 显示实际使用的索引
rows: 预估扫描行数，越少越好
Extra: Using filesort / Using temporary 表示需要额外排序或临时表，性能较差

8. 常用优化SQL示例

-- 【分页优化】大数据量分页，使用游标分页代替 OFFSET
-- ❌ 慢: OFFSET越大，MySQL扫描越多
SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;

-- ✅ 快: 基于主键游标
SELECT * FROM orders
WHERE id < 100000
ORDER BY id DESC LIMIT 20;

-- 【批量插入优化】使用 INSERT 多行语法
INSERT INTO users(name, email) VALUES
('张三', 'zhang@example.com'),
('李四', 'li@example.com'),
('王五', 'wang@example.com');

-- 【UPDATE优化】批量更新，减少事务数量
UPDATE orders SET status = 1
WHERE id BETWEEN 1000 AND 2000;