我要评论索引不是万能的！用不对反而更慢！
索引失效的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页，不如直接翻；如果目录编排不合理，还不如不用！
以上就是mysql中索引失效的8大陷阱及排查指南的详细内容，更多关于mysql索引失效解决的资料请关注代码网其它相关文章！
发表评论