MySQL 索引失效与慢查询优化：我被这些SQL坑了3次后总结的保命指南

MySQL 索引失效与慢查询优化我被这些SQL坑了3次后总结的保命指南大家好我是小柚。今天来聊聊我在MySQL索引上踩过的那些坑相信很多同学和我一样觉得只要加了索引查询就会快结果实际项目上线后某些SQL还是慢得像蜗牛查了半天才发现是索引失效了。今天就把我的踩坑经历整理出来分享给同样被索引问题困扰的同学们。踩坑现场一索引列上做函数操作索引直接失效问题描述我记得第一次优化慢SQL时信心满满地给created_at字段加了索引结果查询还是用了3秒。SQL是这样的SELECT*FROMordersWHEREYEAR(created_at)2026ANDMONTH(created_at)3;原因分析这就是典型的索引列上做函数操作导致索引失效的例子。MySQL的B树索引存储的是字段的原始值而YEAR()和MONTH()函数需要对索引列进行计算后才能比较计算过程中索引无法被使用。解决方案不要在索引列上直接使用函数可以改用范围查询或者虚拟列索引-- 方案1改用范围查询利用索引SELECT*FROMordersWHEREcreated_at2026-03-01ANDcreated_at2026-04-01;-- 方案2利用函数索引MySQL 8.0-- 先创建函数索引ALTERTABLEordersADDINDEXidx_year_created((YEAR(created_at)));-- 然后查询就能用上索引了SELECT*FROMordersWHEREYEAR(created_at)2026;注意事项尽量避免在 WHERE 条件中对索引字段使用函数MySQL 8.0 支持函数索引可以根据业务场景合理使用如果必须使用函数可以考虑生成一个冗余字段来存储计算结果踩坑现场二字符串不加引号隐式类型转换毁索引问题描述有一次我写了这样一个查询SELECT*FROMusersWHEREphone13800138000;phone字段是VARCHAR类型我居然没加引号这条SQL执行了3秒而表中数据才10万条。原因分析这就是隐式类型转换的问题。当VARCHAR类型的字段与数字比较时MySQL会自动把VARCHAR转换成数字这相当于在索引列上做了函数操作导致索引失效。解决方案一定要记得给字符串字段加引号-- 正确写法SELECT*FROMusersWHEREphone13800138000;-- 如果业务上必须用数字比较可以加一个数字类型字段ALTERTABLEusersADDCOLUMNphone_numBIGINTUNSIGNEDGENERATED ALWAYSAS(CAST(phoneASUNSIGNED))STORED;ALTERTABLEusersADDINDEXidx_phone_num(phone_num);-- 然后用数字字段查询SELECT*FROMusersWHEREphone_num13800138000;注意事项养成良好的编码习惯字符串字面量加引号避免在代码中拼接SQL使用参数化查询可以开启慢查询日志监控发现类似的隐式转换问题踩坑现场三like 模糊匹配以 % 开头索引无法使用问题描述我想查用户名包含小柚的用户SELECT*FROMusersWHEREusernameLIKE%小柚%;这条SQL查了2秒用户体验极差。原因分析B树索引是最左前缀匹配原则%在左边会导致索引无法定位必须全表扫描。解决方案根据业务场景选择合适的方案-- 方案1右模糊匹配可以利用索引如果需要前后都模糊考虑全文索引SELECT*FROMusersWHEREusernameLIKE小柚%;-- 方案2使用全文索引MySQL 5.6 InnoDB支持ALTERTABLEusersADDFULLTEXTINDEXft_username(username);SELECT*FROMusersWHEREMATCH(username)AGAINST(小柚INNATURALLANGUAGEMODE);-- 方案3使用第三方搜索引擎Elasticsearch处理复杂搜索场景注意事项模糊查询尽量使用右匹配%xxx而非%xxx%对于频繁的全文搜索需求建议引入 Elasticsearch可以利用前缀索引减少索引体积踩坑现场四复合索引不遵循最左前缀原则问题描述我给用户表建了一个复合索引(status, type, created_at)然后这样查询SELECT*FROMusersWHEREtype1ANDcreated_at2026-01-01;查询还是慢原来以为有索引就快了结果还是too young。原因分析复合索引有最左前缀原则必须从左到右依次使用索引字段。查询从type开始跳过了status导致索引无法使用。解决方案调整SQL顺序或索引结构-- 方案1调整SQL顺序遵循最左前缀SELECT*FROMusersWHEREstatus1ANDtype1ANDcreated_at2026-01-01;-- 方案2根据实际查询创建合适的索引-- 如果经常按 type created_at 查询建这个索引ALTERTABLEusersADDINDEXidx_type_created(type,created_at);-- 如果经常按 status created_at 查询建这个索引ALTERTABLEusersADDINDEXidx_status_created(status,created_at);注意事项创建复合索引时考虑实际查询的字段顺序可以使用EXPLAIN分析查询计划确认索引是否被使用不要创建过多的索引会影响写性能踩坑现场五or 连接的条件有一个没索引就全表扫描问题描述我想查状态为1或者类型为2的用户SELECT*FROMusersWHEREstatus1ORtype2;status有索引type没有查询还是慢。原因分析OR 运算符要求两边的条件都必须有索引否则就会全表扫描。只要有一个字段没有索引MySQL优化器就会放弃使用索引。解决方案使用 UNION 代替 OR或者给没有索引的字段加索引-- 方案1使用 UNION每个条件可以单独使用索引SELECT*FROMusersWHEREstatus1UNIONALLSELECT*FROMusersWHEREtype2;-- 方案2给 type 字段加索引ALTERTABLEusersADDINDEXidx_type(type);-- 方案3如果业务允许改用 INSELECT*FROMusersWHEREstatusIN(1,2);注意事项尽量避免使用 OR改用 UNION 或 IN确保 OR 两边的字段都有索引可以用EXPLAIN确认查询计划总结以上就是我在MySQL索引上踩过的5个坑总结一下索引列上不要做函数操作- 会导致索引失效字符串比较要加引号- 避免隐式类型转换模糊查询%放右边- 遵循最左前缀原则复合索引要按顺序使用- 从左到右依次使用OR两边都要有索引- 否则全表扫描最后给大家一个建议永远用 EXPLAIN 分析你的SQL不要凭感觉判断索引是否生效。MySQL优化器有时候的选择可能和你想的不一样。如果觉得这篇文章有帮助欢迎点赞收藏我会持续更新更多实战踩坑经历。有什么问题也欢迎在评论区留言讨论延伸思考索引优化只是SQL优化的一部分除了索引还需要注意什么比如避免 SELECT *只查询需要的字段合理使用覆盖索引避免回表大数据量分页要用延迟关联考虑使用查询缓存或Redis缓存热点数据这些话题我们以后有机会再详细聊~