网站首页 > 文章精选 正文
MySQL索引失效是性能优化的关键问题,以下列举15+个典型场景,结合原理和示例分析,帮你全面避坑:
一、基础失效场景
- 对索引列进行计算或函数操作
- sql
- SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 索引失效
- 分析:对create_time索引列使用YEAR()函数,需逐行计算后再比较。
优化:改用范围查询: - sql
- SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
- 隐式类型转换
- sql
- SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar类型
- 分析:数字13800138000被转为字符串,相当于对索引列做CAST(phone AS signed)操作。
优化:保持类型一致: - sql
- SELECT * FROM users WHERE phone = '13800138000';
- 最左前缀原则缺失
索引:(name, age) - sql
- SELECT * FROM users WHERE age = 30; -- 失效(未用name) SELECT * FROM users WHERE name LIKE 'A%' AND age = 30; -- 有效
- 使用OR连接非索引列
- sql
- SELECT * FROM users WHERE id = 100 OR email = 'test@example.com'; -- 若email无索引,全表扫描
- 优化:拆分查询或用UNION:
- sql
- SELECT * FROM users WHERE id = 100 UNION SELECT * FROM users WHERE email = 'test@example.com';
二、复杂查询失效场景
- 范围查询阻断后续索引
索引:(age, salary) - sql
- SELECT * FROM users WHERE age > 25 AND salary = 8000; -- salary无法用索引
- 原理:age>25返回大量无序数据,无法再用索引过滤salary。
- !=或<>操作符
- sql
- SELECT * FROM users WHERE status != 1; -- 全表扫描(需排除大部分数据)
- 例外:覆盖索引可能被使用(只需查索引)。
- NOT IN / NOT EXISTS
- sql
- SELECT * FROM users WHERE id NOT IN (1,2,3); -- 通常全表扫描
- 优化:小数据集用LEFT JOIN过滤:
- sql
- SELECT u.* FROM users u LEFT JOIN (SELECT 1 AS id UNION SELECT 2 ...) t ON u.id = t.id WHERE t.id IS NULL;
- LIKE以通配符开头
- sql
- SELECT * FROM users WHERE name LIKE '%abc'; -- 索引失效
- 优化:
- 后缀匹配:LIKE 'abc%'(有效)
- 全文索引:MATCH(name) AGAINST('abc')
三、优化器行为导致失效
- 索引区分度过低
例如gender列(值仅M/F),优化器可能弃用索引。
解决方案:复合索引(如(gender, age))。 - 表数据量过小
当行数 < 全表扫描成本阈值时(如<1000行),优化器直接全表扫描。 - 统计信息不准确
现象:EXPLAIN显示错误执行计划。
修复:ANALYZE TABLE table_name; 更新统计信息。 - 强制索引合并成本高
- sql
- SELECT * FROM users WHERE name = 'John' OR age = 30; -- 可能全表扫描
- 优化器逻辑:当OR条件涉及不同索引,合并成本高时弃用索引。
四、隐式陷阱
- 字符集/排序规则不匹配
- sql
- SELECT * FROM t1 JOIN t2 ON t1.utf8_col = t2.latin1_col; -- 隐式转换失效
- 解决方案:统一字符集或显式转换:
- sql
- ON CONVERT(t1.utf8_col USING latin1) = t2.latin1_col
- JOIN列类型不一致
- sql
- SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- 若o.user_id为varchar,u.id为int,失效
- 原理:类型不匹配触发隐式转换。
- 索引列使用子查询
- sql
- SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics'); -- 可能失效
- 优化:改用JOIN:
- sql
- SELECT p.* FROM products p JOIN categories c ON p.category_id = c.id WHERE c.name = 'Electronics';
五、特殊场景
- 使用ORDER BY非索引列
- sql
- SELECT * FROM users ORDER BY create_time DESC; -- 无索引时filesort
- 优化:为排序字段添加索引。
- 覆盖索引未命中
- sql
- SELECT * FROM table WHERE indexed_col = 1; -- 需回表
- 对比:若只查索引列则无需回表(Using index)。
- 索引损坏
现象:索引存在但查询极慢。
修复:REPAIR TABLE table_name; 或重建索引。
解决方案总结
场景 | 优化策略 |
函数操作 | 重写查询避免计算 |
类型不匹配 | 统一数据类型 |
最左前缀缺失 | 调整索引顺序或新增复合索引 |
OR条件失效 | 改UNION或确保所有列有索引 |
LIKE通配符开头 | 倒排索引/ES分词 |
优化器选错索引 | FORCE INDEX(慎用) |
统计信息过期 | ANALYZE TABLE |
终极诊断工具:
使用EXPLAIN查看执行计划,关注:type列:ALL(全表扫描)、index(全索引扫描)需警惕
key列:实际使用的索引
Extra列:Using filesort、Using temporary表示性能瓶颈
通过理解这些场景,结合EXPLAIN分析,能有效规避索引失效问题,提升查询性能!
猜你喜欢
- 2025-07-10 失业程序员复习python笔记——mysql
- 2025-07-10 MySQL高性能注意事项简述(高性能mysql重点章节)
- 2025-07-10 MySQL触发器介绍(mysql触发器使用)
- 2025-07-10 2021年超详细的java学习路线总结—纯干货分享
- 2025-07-10 MySQL常见错误及解决方法(mysql常见错误提示及解决方法)
- 2025-07-10 MySQL实战:聊聊子查询 vs 联表查询应该如何选择?
- 2025-07-10 SQL 神操作:用 LEFT JOIN 干掉 NOT IN,查询速度直接飙车!
- 2025-07-10 The entangled power of BRICS(the power of dreams)
- 2025-07-10 谁再在 SQL 中写 in 和 not in,直接走人!
- 2025-07-10 SQL面试题及答案( Top20)(mysql面试题及答案)
- 最近发表
-
- Vue3+Django4全新技术实战全栈项目|高清完结
- 工厂模式+策略模式消除 if else 实战
- 每天一个 Python 库:httpx异步请求,让接口测试飞起来
- 如何高效实现API接口的自动化测试?
- 前端工程化:从“手忙脚乱”到“从容协作”的进化记
- 使用C#创建服务端Web API(c#开发web服务器)
- SpringBoot之旅第四篇-web开发(springboot做web项目)
- 一文读懂SpringMVC(一文读懂新型政策性金融工具)
- Rust Web编程:第十二章 在 Rocket 中重新创建我们的应用程序
- Apache Druid 数据摄取——本地数据和kafka流式数据 一篇文章看懂
- 标签列表
-
- newcoder (56)
- 字符串的长度是指 (45)
- drawcontours()参数说明 (60)
- unsignedshortint (59)
- postman并发请求 (47)
- python列表删除 (50)
- 左程云什么水平 (56)
- 计算机网络的拓扑结构是指() (45)
- 编程题 (64)
- postgresql默认端口 (66)
- 数据库的概念模型独立于 (48)
- 产生系统死锁的原因可能是由于 (51)
- 数据库中只存放视图的 (62)
- 在vi中退出不保存的命令是 (53)
- 哪个命令可以将普通用户转换成超级用户 (49)
- noscript标签的作用 (48)
- 联合利华网申 (49)
- swagger和postman (46)
- 结构化程序设计主要强调 (53)
- 172.1 (57)
- apipostwebsocket (47)
- 唯品会后台 (61)
- 简历助手 (56)
- offshow (61)
- mysql数据库面试题 (57)