MySQL查询优化：索引失效的10个坑，90%的程序员都踩过！

原创于 2025-07-22 14:33:53 发布 · 242 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #android #数据库

MySQL面试专栏专栏收录该内容

56 篇文章

订阅专栏

一、开场白：一条慢SQL引发的“血案”

上周半夜被运维同事的电话惊醒：“线上数据库CPU飙到100%，快看看！”
排查发现，一条看似普通的查询：

SELECT * FROM order WHERE user_id = 123 AND create_time > '2023-01-01'

竟然执行了30秒！明明给user_id建了索引，为什么还是全表扫描？

今天就来聊聊MySQL索引那些事儿：为什么精心设计的索引会失效？如何用Explain诊断慢SQL？纯干货，建议收藏！

二、索引失效？先搞懂MySQL怎么“查字典”

索引就像字典的目录，正常情况下MySQL会：

先查目录（索引）找到对应页码（数据位置）
再翻到对应页码找到内容（数据）

但如果索引失效，MySQL就会变成“逐字逐句翻字典”，也就是全表扫描，速度慢100倍！

三、索引失效的10大经典场景（附避坑指南）

1. 隐式转换：最容易踩的坑！

-- user_id是varchar类型
SELECT * FROM user WHERE user_id = 123;  -- 失效！
SELECT * FROM user WHERE user_id = '123'; -- 有效

原理：数字和字符串比较会触发类型转换，相当于在索引字段上做运算

2. 使用函数/表达式

SELECT * FROM order WHERE SUBSTR(create_time, 1, 7) = '2023-01'; -- 失效
SELECT * FROM order WHERE create_time >= '2023-01-01' AND create_time < '2023-02-01'; -- 有效

3. LIKE以%开头

SELECT * FROM user WHERE username LIKE '%小明'; -- 失效
SELECT * FROM user WHERE username LIKE '小明%'; -- 有效

4. OR连接非索引字段

SELECT * FROM product WHERE id = 123 OR price = 99; -- price无索引则整体失效

5. 违背最左匹配原则

-- 联合索引(a,b,c)
SELECT * FROM table WHERE b=2 AND c=3; -- 失效
SELECT * FROM table WHERE a=1 AND c=3; -- 只用到a索引
SELECT * FROM table WHERE a=1 AND b=2 AND c=3; -- 全索引生效

6. 使用NOT IN / NOT EXISTS

SELECT * FROM user WHERE id NOT IN (1,2,3); -- 可能失效

建议用LEFT JOIN替代：

SELECT a.* FROM user a LEFT JOIN blacklist b ON a.id = b.id WHERE b.id IS NULL;

7. 索引字段使用IS NULL / IS NOT NULL

SELECT * FROM user WHERE phone IS NULL; -- 可能失效

优化：业务允许的话用默认值代替NULL

8. 全表扫描更快的情况

当查询结果超过30%的数据时，MySQL会认为全表扫描比索引查询更快

9. 使用!=或<>运算符

SELECT * FROM product WHERE price != 100; -- 可能失效

10. 索引字段被更新频繁

写入频繁的字段建索引会导致更新性能下降

四、Explain执行计划：SQL诊断神器

学会看Explain，就能像医生一样诊断SQL问题！

1. 怎么用？

EXPLAIN SELECT * FROM order WHERE user_id = 123;

2. 关键字段解读

字段	含义	重要值
type	访问类型	ref（索引查找）、range（范围查找）、ALL（全表扫描）
key	使用的索引	NULL表示未使用索引
rows	预估行数	数值越小越好
Extra	额外信息	Using index（覆盖索引）、Using filesort（文件排序）、Using temporary（临时表）

3. 实战分析：从ALL到ref的优化

优化前：

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | order | ALL  | NULL          | NULL | NULL    | NULL | 100w | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

优化后：

+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | order | ref  | idx_user_id   | idx_user_id | 4       | const | 100  | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+