🔍MySQL索引失效的8大坑，90%开发都踩过！附最全优化实战指南

✅ 1. 对索引字段使用函数或运算

📉 原因：索引值被转换，无法命中 B+ 树。

-- ❌ 索引失效
WHERE DATE(created_at) = '2024-04-21'
WHERE price + 10 > 100

-- ✅ 改写方式
WHERE created_at BETWEEN '2024-04-21' AND '2024-04-22'
WHERE price > 90

✅ 2. 索引字段使用不等于（<> / !=）

📉 原因：MySQL 优化器通常不会使用索引做不等判断。

-- ❌
WHERE age != 18
WHERE name <> '张三'

✅ 3. LIKE 模糊匹配左边有通配符

📉 原因：无法使用索引中的前缀匹配。

-- ❌ 索引失效
WHERE name LIKE '%三'

-- ✅ 索引有效
WHERE name LIKE '张%'

✅ 4. 联合索引未遵守最左前缀原则

📉 原因：联合索引需要从最左字段开始连续命中。

-- 联合索引 (name, age)

-- ✅ 使用 name，命中索引
WHERE name = '张三'

-- ✅ name + age，命中索引
WHERE name = '张三' AND age = 18

-- ❌ age 单独使用，索引失效
WHERE age = 18

✅ 5. 使用 OR 连接多个字段时，部分字段无索引

📉 原因：OR 查询必须每个条件都命中索引，才可能用索引。

-- ❌ 假如只有 name 有索引，age 没有
WHERE name = '张三' OR age = 18

-- ✅ 拆成 UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 18

✅ 6. 使用 IS NULL、IS NOT NULL 不当

• IS NULL 通常可以使用索引 ✅
• IS NOT NULL 可能无法用上索引 ❌（取决于 MySQL 版本）

-- ✅ 可能使用索引
WHERE deleted_at IS NULL

-- ❌ 通常不使用索引
WHERE deleted_at IS NOT NULL

✅ 7. 数据类型不一致（隐式类型转换）

📉 原因：类型不一致会导致隐式转换，索引失效。

-- 假如 id 是 int 类型
-- ❌ 索引失效（字符串类型）
WHERE id = '100'

-- ✅ 索引有效（int 类型）
WHERE id = 100

✅ 8. 强制使用了不合理的索引或全表扫描（比如数据量太小）

📉 原因：优化器判断“索引不如直接扫表快”

• 小表：几十行的数据，直接全表扫描可能更快
• 错误的 USE INDEX、FORCE INDEX 可能导致反效果

🧠 小技巧：用 EXPLAIN 检查索引是否命中

EXPLAIN SELECT * FROM users WHERE name = '张三';

看这些字段：

• type 是否为 ref, range（好）
• Extra 中有无 Using index, Using where, Using filesort
• key 是否命中你的索引

✅ 总结一句话：

索引不是万能的，写 SQL 时要避免函数、运算、类型转换、错误顺序、模糊匹配等操作，才能让索引乖乖发挥作用！

🚀 一、SQL 优化的核心目标

简单说就是两个字：快、省

• 快：让查询时间尽可能短，毫秒级最好；
• 省：资源消耗小（CPU、内存、磁盘 IO），同时避免锁冲突。

🧩 二、我在实际项目中总结的优化步骤（干货）

✅ 1. 优先找出慢 SQL

• 使用 SHOW PROCESSLIST 发现当前执行慢的 SQL；
• 使用 slow_query_log 慢查询日志定位；
• 配合 APM（如阿里云 DBA助手、腾讯 DBbrain）查看执行耗时最多的 SQL。

✅ 2. 使用 EXPLAIN 分析执行计划

• 看是否走索引？是否出现 type=ALL（全表扫描）？
• rows 是否过多？
• 是否出现 Using temporary、Using filesort（开销大）？

举例分析：

EXPLAIN SELECT * FROM users WHERE name = '张三';

• key 显示命中索引：说明有效；
• type 显示为 ref、range：优秀；
• rows 显示扫描行数：尽可能小；
• Extra 不要出现 Using temporary 或 Using filesort

✅ 3. SQL 结构本身的优化经验

📌 写法层面

• 避免 SELECT *，明确字段
• 尽量避免子查询，尤其是 IN (SELECT ...)，改成 JOIN 更高效
• LIMIT + WHERE + ORDER BY 配合用，减少内存压力

📌 语法替换

• OR 尽量替换为 UNION
• 大量 IN 优化为临时表 JOIN
• 用 EXISTS 替换 IN（在子表大、主表小的情况下）

✅ 4. 索引策略优化经验

🔍 索引使用技巧

• 使用覆盖索引（避免回表）
  👉 SELECT id, name FROM users WHERE name = '张三' （假如 name 有索引）
• 使用联合索引，遵循最左前缀原则
• 避免在索引字段上使用函数、运算、!=、LIKE '%xx'
• 注意数据分布情况，低选择度字段（如性别）单独建索引没意义

🧠 动态观察

• SHOW INDEX FROM table 查看哪些索引被建了，是否被误建或遗漏
• ANALYZE TABLE + OPTIMIZE TABLE 有助于索引统计信息刷新

✅ 5. 分库分表、缓存、读写分离（进阶）

• 热点数据缓存（Redis）：频繁查询的数据不查库
• 读写分离：主库写，从库读，减轻主库压力
• 水平分表：表数据量超千万时（比如订单表），做分库分表
• 垂直拆表：一个表字段太多，可以按功能模块拆成多个表

✅ 6. 事务与锁优化

• 尽量缩小事务范围，降低锁的冲突
• 用合适的隔离级别（READ COMMITTED 性能更好）
• 关注是否出现锁等待、死锁（SHOW ENGINE INNODB STATUS）

📦 实战例子：一个慢 SQL 的优化过程

原始 SQL：

SELECT * FROM orders WHERE DATE(create_time) = '2024-04-21';

问题：

• 使用了函数 DATE()，索引失效；
• 全表扫描，慢！

优化后：

SELECT * FROM orders WHERE create_time BETWEEN '2024-04-21 00:00:00' AND '2024-04-21 23:59:59';

效果：

• 命中 create_time 索引
• 查询速度提升数十倍

📌 总结：我的 SQL 优化经验口诀

“查慢因、看执行、调结构、用索引、搞缓存、分冷热、控事务”

这几个字基本就是 SQL 优化的大纲思路。不同项目不同瓶颈，优化策略要结合业务和数据特性，逐层深入。