📉 MySQL 索引失效的8大陷阱及排查指南
🎯 一句话明白
索引不是万能的！用不对反而更慢！
🚫 索引失效的8大常见场景
1. 索引列上做计算（最常见）
-- ❌ 错误：在索引列上计算
SELECT * FROM users WHERE YEAR(created_time) = 2024;
SELECT * FROM products WHERE price * 2 > 100;-- ✅ 正确：把计算移到右边
SELECT * FROM users WHERE created_time >= '2024-01-01' AND created_time < '2025-01-01';
SELECT * FROM products WHERE price > 50;
原因：数据库必须对每一行都计算才能比较，无法用索引快速定位。
2. 索引列使用函数
-- ❌ 错误：使用函数包裹索引列
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
SELECT * FROM logs WHERE DATE(create_time) = '2024-01-01';-- ✅ 正确：使用范围查询
SELECT * FROM users WHERE name = 'john'; -- 存入时统一小写
SELECT * FROM logs WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';
3. 模糊查询开头用通配符
-- ❌ 错误：以 % 开头
SELECT * FROM users WHERE name LIKE '%张%'; -- 全表扫描
SELECT * FROM users WHERE name LIKE '%明';-- ✅ 正确：以确定字符开头
SELECT * FROM users WHERE name LIKE '张%'; -- 可以使用索引
SELECT * FROM users WHERE name LIKE '张_明'; -- _匹配一个字符
生活类比：
张%→ 知道姓张，直接翻到张姓区域 ✅%张→ 不知道姓什么，只能一页页翻 ❌
4. OR 条件使用不当
-- ❌ 错误：OR 条件中有的列没索引
SELECT * FROM users WHERE age = 25 OR salary > 10000;
-- 如果 salary 没索引，整个查询都无法用索引-- ✅ 正确：确保 OR 两边都有索引
-- 或者分开查询
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE salary > 10000;
5. 类型不匹配（隐式转换）
-- ❌ 错误：字符串列用数字查询
CREATE TABLE users (id VARCHAR(20) PRIMARY KEY, -- 字符串类型name VARCHAR(50)
);
SELECT * FROM users WHERE id = 123; -- 字符串和数字比较-- ✅ 正确：类型一致
SELECT * FROM users WHERE id = '123';
测试隐式转换：
-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE id = 123;
-- 注意看 key 列是否为 NULL（没走索引）
6. 联合索引没用最左列
-- 索引：idx_abc (a, b, c)-- ✅ 有效：用了最左列
SELECT * FROM table WHERE a = 1;
SELECT * FROM table WHERE a = 1 AND b = 2;
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;-- ❌ 无效：没从最左开始
SELECT * FROM table WHERE b = 2; -- 跳过 a
SELECT * FROM table WHERE c = 3; -- 跳过 a,b
SELECT * FROM table WHERE b = 2 AND c = 3; -- 跳过 a
记忆技巧：就像查字典，必须知道第一个字母才能快速查找。
7. 数据量太少，不如全表扫描
-- 假设表只有 100 行数据
SELECT * FROM small_table WHERE id = 50;-- 数据库会想：走索引要查索引树 + 回表，不如直接全表扫描更快
8. 索引列选择性太低
-- 比如"性别"列，只有"男/女"两种值
CREATE INDEX idx_gender ON users(gender);SELECT * FROM users WHERE gender = '男';
-- 可能失效，因为要返回接近一半的数据，不如全表扫描
🔍 如何排查索引失效？
第1步：用 EXPLAIN 快速诊断
-- 在查询前加 EXPLAIN
EXPLAIN SELECT * FROM users WHERE name LIKE '%张%';-- 关键看这几列：
-- 1. type: ALL → 全表扫描（最差）
-- 2. key: NULL → 没走索引
-- 3. rows: 数值很大 → 要扫描很多行
-- 4. Extra: Using filesort, Using temporary → 性能警告
第2步：看懂 EXPLAIN 结果
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';-- 理想结果：
+----+-------------+--------+------+---------------+---------+---------+-------------+------+
| id | select_type | table | type | possible_keys | key | key_len | rows | Extra|
+----+-------------+--------+------+---------------+---------+---------+-------------+------+
| 1 | SIMPLE | orders | ref | idx_user | idx_user| 8 | 10 | NULL |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-- type 从好到差：
-- system > const > eq_ref > ref > range > index > ALL
-- 至少要到 range 级别才合格
第3步：使用性能分析工具
-- 1. 开启 profiling（查看详细耗时）
SET profiling = 1;
SELECT * FROM users WHERE ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;-- 2. 查看索引使用统计
SELECT * FROM sys.schema_index_statistics
WHERE table_name = 'users';-- 3. 找出从未使用的索引（该删除了！）
SELECT * FROM sys.schema_unused_indexes;
第4步：慢查询日志分析
-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 超过2秒记录-- 2. 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';-- 3. 使用工具分析（推荐）
-- mysqldumpslow 或 pt-query-digest
🛠️ 实战排查案例
案例1：为什么这个查询慢？
-- 原始查询（很慢）
SELECT * FROM orders
WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01'
ORDER BY amount DESC;-- 步骤1：EXPLAIN分析
EXPLAIN SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01';-- 发现：type=ALL, key=NULL（没走索引）
-- 原因：对 create_time 使用了函数-- 优化方案：
SELECT * FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'
ORDER BY amount DESC;-- 再 EXPLAIN：type=range, key=idx_create_time ✅
案例2：联合索引问题
-- 查询：经常按城市和年龄搜索用户
SELECT * FROM users WHERE city = '北京' AND age > 25;-- 现有索引：idx_age_city (age, city)
-- 问题：最左列是 age，但查询先过滤 city-- 解决方案：
-- 1. 调整查询顺序（如果业务允许）
SELECT * FROM users WHERE age > 25 AND city = '北京';-- 2. 或创建新索引
CREATE INDEX idx_city_age ON users(city, age);
📊 索引效果评估表
| 检查项 | 合格标准 | 如何检查 |
|---|---|---|
| 索引使用率 | > 90% | SHOW STATUS LIKE 'Handler_read%' |
| 查询响应时间 | < 100ms | 慢查询日志 |
| 扫描行数 | rows < 1000 | EXPLAIN 的 rows 列 |
| 临时表使用 | 尽量避免 | EXPLAIN 的 Extra 列 |
| 文件排序 | 尽量避免 | EXPLAIN 的 Extra 列 |
🎯 最佳实践清单
创建索引前问自己：
- ✅ 这个查询真的需要索引吗？（数据量小不需要）
- ✅ 索引列的选择性高吗？（唯一值多吗？）
- ✅ 会频繁更新这个列吗？（更新频繁的列不适合建索引）
- ✅ 已经有类似的索引了吗？（避免重复索引）
创建索引后要检查：
-- 1. 验证索引被使用
EXPLAIN SELECT ...;-- 2. 监控索引大小
SELECT table_name,index_name,ROUND(SUM(index_length)/1024/1024, 2) AS '索引大小(MB)'
FROM information_schema.TABLES
WHERE table_schema = '你的数据库'
GROUP BY table_name, index_name;-- 3. 定期清理无用索引
-- 查看使用频率低的索引
SELECT * FROM sys.schema_unused_indexes;
💡 简单记忆口诀
索引失效八大坑：
- 计算函数不用想
- 模糊查询百分左
- 类型转换隐式藏
- OR 条件要全防
- 最左前缀不能忘
- 数据量少全表爽
- 选择性低是白忙
- 不等号<>也够呛
排查四步曲：
- EXPLAIN 看计划
- 慢日志 抓真凶
- PROFILE 查明细
- 统计表 清无用
记住：索引就像书的目录，但翻目录本身也需要时间。如果书只有10页，不如直接翻；如果目录编排不合理，还不如不用！