MySQL查询优化：索引失效的10个坑

Tags: /MySQL/ Date Created:

一、开场白：一条慢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 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

五、查询优化最佳实践

1. 索引设计三原则

高频查询字段优先建索引
避免过度索引（影响写入性能）
联合索引遵循最左匹配原则

2. SQL编写规范

避免SELECT *，只查需要的字段
控制JOIN表数量（建议不超过3张）
大表分页用主键自增ID：WHERE id > 1000 LIMIT 100

3. 定期维护

分析慢查询日志（slow_query_log）
定期优化表结构和索引
使用pt-index-usage工具分析索引使用情况

六、总结

索引失效就像“隐形的性能杀手”，稍不注意就会让系统变慢。记住：

写SQL时多思考：这个查询会走索引吗？
养成用Explain分析SQL的习惯
没有万能的优化方案，要结合业务场景

最后送大家一句口诀：“全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上不计算，范围之后全失效”