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

大家好，我是小柚🐾。今天来聊聊我在MySQL索引上踩过的那些坑相信很多同学和我一样，觉得只要加了索引查询就会快，结果实际项目上线后，某些SQL还是慢得像蜗牛，查了半天才发现是索引失效了。今天就把我的踩坑经历整理出来，分享给同样被索引问题困扰的同学们。

踩坑现场一：索引列上做函数操作，索引直接失效

问题描述

我记得第一次优化慢SQL时，信心满满地给 created_at 字段加了索引，结果查询还是用了3秒。SQL是这样的：

SELECT * FROM orders 
WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 3;

原因分析

这就是典型的索引列上做函数操作导致索引失效的例子。MySQL的B+树索引存储的是字段的原始值，而 YEAR() 和 MONTH() 函数需要对索引列进行计算后才能比较，计算过程中索引无法被使用。

解决方案

不要在索引列上直接使用函数，可以改用范围查询或者虚拟列索引：

-- 方案1：改用范围查询，利用索引
SELECT * FROM orders 
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

-- 方案2：利用函数索引（MySQL 8.0+）
-- 先创建函数索引
ALTER TABLE orders ADD INDEX idx_year_created ((YEAR(created_at)));
-- 然后查询就能用上索引了
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

注意事项

• 尽量避免在 WHERE 条件中对索引字段使用函数
• MySQL 8.0+ 支持函数索引，可以根据业务场景合理使用
• 如果必须使用函数，可以考虑生成一个冗余字段来存储计算结果

---

踩坑现场二：字符串不加引号，隐式类型转换毁索引

问题描述

有一次我写了这样一个查询：

SELECT * FROM users WHERE phone = 13800138000;

phone 字段是 VARCHAR 类型，我居然没加引号！这条SQL执行了3秒，而表中数据才10万条。

原因分析

这就是隐式类型转换的问题。当 VARCHAR 类型的字段与数字比较时，MySQL会自动把 VARCHAR 转换成数字，这相当于在索引列上做了函数操作，导致索引失效。

解决方案

一定要记得给字符串字段加引号：

-- 正确写法
SELECT * FROM users WHERE phone = '13800138000';

-- 如果业务上必须用数字比较，可以加一个数字类型字段
ALTER TABLE users ADD COLUMN phone_num BIGINT UNSIGNED GENERATED ALWAYS AS (CAST(phone AS UNSIGNED)) STORED;
ALTER TABLE users ADD INDEX idx_phone_num (phone_num);
-- 然后用数字字段查询
SELECT * FROM users WHERE phone_num = 13800138000;

注意事项

• 养成良好的编码习惯，字符串字面量加引号
• 避免在代码中拼接SQL，使用参数化查询
• 可以开启慢查询日志监控，发现类似的隐式转换问题

---

踩坑现场三：like 模糊匹配以 % 开头，索引无法使用

问题描述

我想查用户名包含"小柚"的用户：

SELECT * FROM users WHERE username LIKE '%小柚%';

这条SQL查了2秒，用户体验极差。

原因分析

B+树索引是最左前缀匹配原则，% 在左边会导致索引无法定位，必须全表扫描。

解决方案

根据业务场景选择合适的方案：

-- 方案1：右模糊匹配可以利用索引（如果需要前后都模糊，考虑全文索引）
SELECT * FROM users WHERE username LIKE '小柚%';

-- 方案2：使用全文索引（MySQL 5.6+ InnoDB支持）
ALTER TABLE users ADD FULLTEXT INDEX ft_username (username);
SELECT * FROM users WHERE MATCH(username) AGAINST('小柚' IN NATURAL LANGUAGE MODE);

-- 方案3：使用第三方搜索引擎（Elasticsearch）处理复杂搜索场景

注意事项

• 模糊查询尽量使用右匹配 %xxx 而非 %xxx%
• 对于频繁的全文搜索需求，建议引入 Elasticsearch
• 可以利用前缀索引减少索引体积

---

踩坑现场四：复合索引不遵循最左前缀原则

问题描述

我给用户表建了一个复合索引 (status, type, created_at)，然后这样查询：

SELECT * FROM users WHERE type = 1 AND created_at > '2026-01-01';

查询还是慢，原来以为有索引就快了，结果还是too young。

原因分析

复合索引有最左前缀原则，必须从左到右依次使用索引字段。查询从 type 开始跳过了 status，导致索引无法使用。

解决方案

调整SQL顺序或索引结构：

-- 方案1：调整SQL顺序，遵循最左前缀
SELECT * FROM users WHERE status = 1 AND type = 1 AND created_at > '2026-01-01';

-- 方案2：根据实际查询创建合适的索引
-- 如果经常按 type + created_at 查询，建这个索引
ALTER TABLE users ADD INDEX idx_type_created (type, created_at);
-- 如果经常按 status + created_at 查询，建这个索引
ALTER TABLE users ADD INDEX idx_status_created (status, created_at);

注意事项

• 创建复合索引时，考虑实际查询的字段顺序
• 可以使用 EXPLAIN 分析查询计划，确认索引是否被使用
• 不要创建过多的索引，会影响写性能

---

踩坑现场五：or 连接的条件，有一个没索引就全表扫描

问题描述

我想查状态为1或者类型为2的用户：

SELECT * FROM users WHERE status = 1 OR type = 2;

status 有索引，type 没有，查询还是慢。

原因分析

OR 运算符要求两边的条件都必须有索引，否则就会全表扫描。只要有一个字段没有索引，MySQL优化器就会放弃使用索引。

解决方案

使用 UNION 代替 OR，或者给没有索引的字段加索引：

-- 方案1：使用 UNION（每个条件可以单独使用索引）
SELECT * FROM users WHERE status = 1
UNION ALL
SELECT * FROM users WHERE type = 2;

-- 方案2：给 type 字段加索引
ALTER TABLE users ADD INDEX idx_type (type);

-- 方案3：如果业务允许，改用 IN
SELECT * FROM users WHERE status IN (1, 2);

注意事项

• 尽量避免使用 OR，改用 UNION 或 IN
• 确保 OR 两边的字段都有索引
• 可以用 EXPLAIN 确认查询计划

---

总结

以上就是我在MySQL索引上踩过的5个坑，总结一下：

1. 索引列上不要做函数操作 - 会导致索引失效
2. 字符串比较要加引号 - 避免隐式类型转换
3. 模糊查询%放右边 - 遵循最左前缀原则
4. 复合索引要按顺序使用 - 从左到右依次使用
5. OR两边都要有索引 - 否则全表扫描

最后给大家一个建议：永远用 EXPLAIN 分析你的SQL，不要凭感觉判断索引是否生效。MySQL优化器有时候的选择可能和你想的不一样。

如果觉得这篇文章有帮助，欢迎点赞收藏，我会持续更新更多实战踩坑经历。有什么问题也欢迎在评论区留言讨论！

---

延伸思考：索引优化只是SQL优化的一部分，除了索引，还需要注意什么？比如：

• 避免 SELECT *，只查询需要的字段
• 合理使用覆盖索引，避免回表
• 大数据量分页要用延迟关联
• 考虑使用查询缓存或Redis缓存热点数据

这些话题我们以后有机会再详细聊~