Mysql慢SQL优化—索引8大原则
很多同学写SQL的时候，明明建了索引，但查询还是很慢，这是为什么呢？其实，索引失效是最常见的性能杀手。
今天我会用B+树的底层原理，带大家深入理解：
- 为什么有些写法会让索引失效？
- 怎么写SQL才能充分利用索引？
掌握这8大原则，你的SQL性能至少提升50%以上！让我们开始吧！
在正式开始之前，先给大家看一下今天要讲的8大索引优化原则：
1️⃣ 全值匹配 - 索引利用率最高的写法
2️⃣ 最左前缀 - 复合索引的黄金法则
3️⃣ 少计算 - 千万不要在索引列上做运算
4️⃣ 范围失效 - 范围查询后面的索引都会失效
5️⃣ LIKE百分右 - 通配符位置很关键
6️⃣ 覆盖索引 - 避免回表是性能优化的核心
7️⃣ 避免失效 - 不等、空值、OR都是索引杀手
8️⃣ 加引号 - 字符串不加引号会导致隐式转换
这8条原则，每一条背后都有B+树的底层逻辑支撑。接下来我们逐一详细讲解。
原则① - 全值匹配我最爱
首先来看第一条原则：全值匹配我最爱。
假设我们有一个复合索引：(name, age, pos)
先看一下这个索引在B+树中是怎么存储的。大家注意看这棵树：
- 根节点按name分组，比如"李四"、"张三"
- 第二层在name相同的情况下，再按age排序
- 叶子节点存储完整数据，包含name、age、pos三个字段
重点来了！这些数据是三级排序的：
- 首先按name排序
- name相同时按age排序
- name和age都相同时按pos排序
所以，当我们写全值匹配的查询：
WHERE name='李四' AND age=25 AND pos='开发'MySQL会怎么做呢？
- 先定位到name='李四'的范围 ✅
- 在这个范围内定位age=25 ✅
- 再精确匹配pos='开发' ✅
三层索引全部生效，直接命中目标数据，完全不需要扫描其他行！这就是全值匹配效率最高的原因。
当然，如果你只用到部分条件，比如只有name和age，索引仍然有效，只是利用率没有那么高而已。
但记住！如果你跳过了name，比如只查age和pos，那索引就完全失效了！为什么？我们马上在下一条原则中讲解。
原则② - 最左前缀要遵守
接下来是最重要的原则：最左前缀法则。
我把它总结成一句话：带头大哥不能死，中间兄弟不能断。
什么意思呢？看这张图：
- name是带头大哥 👑 必须存在
- age是中间兄弟 🤝 不能跳过
- pos是小弟 👶 依赖前面两个
为什么呢？我们从B+树的存储结构来看：
索引数据在叶子节点中是这样排列的：
李四,25,开发 → 李四,30,测试 → 张三,22,前端注意！这个顺序是按照 name → age → pos 依次排列的。
场景1：跳过带头大哥
如果你查询 WHERE age=25 AND pos='开发'，会发生什么？
因为没有name条件，MySQL不知道去哪个name分组里找age=25的数据：
- 李四下面可能有age=25
- 王五下面也可能有age=25
- 张三下面还可能有age=25
每个name分组下，age才是有序的！没有name，age就是完全无序的，无法定位，只能全表扫描！
场景2：跳过中间兄弟
如果你查询 WHERE name='李四' AND pos='测试'：
- name索引✅ 可以定位到"李四"的范围
- age被跳过❌
- pos索引❌ 失效了！
为什么pos失效？因为pos的排序依赖age！在"李四"这个分组下：
李四,25,开发
李四,25,测试
李四,30,运维
李四,30,测试跳过age后，pos是分散无序的，无法利用索引，只能在"李四"的范围内逐行过滤！
记住这个口诀：带头大哥不能死，中间兄弟不能断！
原则③ - 索引列上少计算
第三条原则：索引列上少计算。
很多同学喜欢这样写SQL：
WHERE id + 1 = 10
WHERE YEAR(create_time) = 2024
WHERE UPPER(name) = 'ZHANG'这些写法全都会导致索引失效！为什么？
我们来看B+树的原理。假设id字段有索引，在B+树中是这样存储的：
id=5 → id=8 → id=9 → id=12 ...这些数据按照原始值有序排列，所以查 WHERE id=9 可以快速定位。
但是！如果你写 WHERE id+1=10，会发生什么？
MySQL必须对每一行的id都加1再判断：
5+1=6 ❌
8+1=9 ❌
9+1=10 ✅ 找到了！
12+1=13 ❌计算后的结果是 6, 9, 10, 13，完全打乱了原来的顺序！B+树的有序性被破坏了，索引就无法使用，只能逐行计算，变成全表扫描！
正确的写法是什么？
把计算移到右侧：
❌ WHERE id + 1 = 10
✅ WHERE id = 9
❌ WHERE YEAR(create_time) = 2024
✅ WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'
❌ WHERE salary * 1.1 > 10000
✅ WHERE salary > 9090.9记住：左侧保持原样，计算放右边！
原则④ - 范围之后全失效
第四条原则：范围之后全失效。
假设有索引 (a, b, c)，我们查询：
WHERE a=1 AND b>5 AND c=3大家猜一下，这三个条件，哪些能用上索引？
答案是：
- a=1 ✅ 精确匹配，索引有效
- b>5 ⚡ 范围查询，索引有效
- c=3 ❌ 索引失效！
为什么c会失效？我们看B+树的存储结构：
在 a=1 的范围内，b>5 包含了多个值：b=6, 8, 10, 12...
对于每个不同的b值，对应的c是独立排序的：
a=1, b=6: c=1, c=5, c=9
a=1, b=8: c=2, c=7, c=3 ✓
a=1, b=10: c=3 ✓, c=6, c=8
a=1, b=12: c=4, c=3 ✓, c=11注意看！c=3 分散在各个b值对应的分组中，没有集中在一起！
因为b是范围查询，包含多个不同的值，c在全局范围内是无序的，无法利用索引定位，只能逐行过滤判断！
对比一下精确查询：
WHERE a=1 AND b=5 AND c=3- a=1 ✅ 定位范围
- b=5 ✅ 精确定位到b=5这一组
- c=3 ✅ 在b=5这一组内，c是有序的，可以精确定位
三层索引全部生效！
优化建议：
如果查询条件中既有精确匹配又有范围查询，建议把范围查询的字段放在索引的最后面：
❌ 索引(a, b, c) → b是范围，c失效
✅ 索引(a, c, b) → 把范围列b放最后原则⑤ - LIKE百分写最右
第五条原则：LIKE百分写最右。
LIKE模糊查询时，通配符 % 的位置非常关键：
✅ LIKE '张%' - %在右侧，可以使用索引
❌ LIKE '%张' - %在左侧，全表扫描
❌ LIKE '%张%' - %两侧，全表扫描
为什么？我们看B+树中name索引的存储：
这些数据按照字典序排列，就像字典一样！
LIKE '张%' - 前缀确定
- 前缀是"张"，MySQL可以快速定位到"张"开头的范围
- 顺序扫描：张一 → 张三 → 张五
- 遇到"李"就停止，因为后面肯定不是"张"开头了
- 利用了B+树的有序性 ✅
LIKE '%张' - 前缀不确定
- 可能匹配：一张、三张、老张、小张...
- 前缀完全不确定，可能以任何字符开头
- 无法利用B+树的有序性定位
- 只能逐行扫描全部数据 ❌
记忆技巧：查字典找"张三"
- ✅ 知道姓"张" → 直接翻到张字部
- ❌ 只知道名"三" → 全书翻遍
所以记住：百分号写右边，前缀确定能用索引！
原则⑥ - 覆盖索引不写*
第六条原则：覆盖索引不写*。
这条非常重要！很多同学的SQL慢，就是因为回表次数太多！
什么是回表？我们来看一个例子：
假设有索引 (name)，执行查询：
SELECT * FROM staff WHERE name='张三'MySQL会进行两次查询：
第1步：扫描二级索引
- 在name索引树中找到 name='张三'
- 拿到主键ID：1001
第2步：回表查主键索引
- 用ID=1001去主键索引树中查询
- 读取完整的行数据
这就是回表！每匹配一行就要回表一次：
- 匹配1000行 → 回表1000次
- 需要访问两棵B+树
- 产生大量随机IO
- 性能慢50%以上！
怎么优化？使用覆盖索引！
如果我们建立索引 (name, age, pos)，然后查询：
SELECT name, age, pos FROM staff WHERE name='张三'这时候，索引中已经包含了所需的全部列！
- name、age、pos都在索引中
- 直接从索引树返回数据 ✅
- 无需回表，只访问一棵树！
- 性能提升50%以上！
对比一下：
❌ 未使用覆盖索引
SELECT * FROM staff WHERE name='张三'- 扫描索引：1次
- 回表次数：N次
- 磁盘IO：1+N次
✅ 使用覆盖索引
SELECT name, age, pos FROM staff WHERE name='张三'- 扫描索引：1次
- 回表次数：0次
- 磁盘IO：1次
所以，**不要偷懒写SELECT ***，明确指定需要的列，让索引覆盖查询的所有字段！
原则⑦ - 不等空值还有OR
第七条原则：不等空值还有OR - 这是索引失效的三大杀手！
1️⃣ 不等判断（!= 或 <>）
WHERE age != 25为什么失效？我们看一下数据分布：
- 假设有10000条数据
- age=25 只有10条
- age≠25 有9990条
如果使用索引：需要扫描9990次
如果全表扫描：只需要扫描1次
MySQL优化器会判断：扫描索引的成本 > 全表扫描的成本，所以直接放弃索引！
✅ 优化方案：用范围代替不等
WHERE age < 25 OR age > 252️⃣ 空值判断（IS NULL / IS NOT NULL）
WHERE name IS NULLNULL值一般不会存储在索引中，无法通过索引定位，只能全表扫描。
✅ 优化方案：设计时避免NULL
-- 建表时设置默认值
name VARCHAR(50) NOT NULL DEFAULT ''
-- 查询时
WHERE name = ''3️⃣ OR连接
WHERE a=1 OR b=2如果a和b都有索引，MySQL需要分别扫描两个索引再合并结果，成本很高。
如果a或b有一个没有索引，那就只能全表扫描。
✅ 优化方案：用UNION拆分
SELECT * FROM table WHERE a=1
UNION
SELECT * FROM table WHERE b=2记住：不等、空值、OR，都是索引杀手！
原则⑧ - VARCHAR加引号
最后一条原则：VARCHAR加引号。
这是很多新手最容易犯的错误！看这个例子：
假设phone字段类型是 VARCHAR(20)，你写了这样的SQL：
WHERE phone = 13800000000看起来没问题，但实际上索引失效了！
为什么？因为发生了隐式类型转换！
MySQL的执行过程：
第1步：你写的SQL
WHERE phone = 13800000000第2步：MySQL自动转换成
WHERE CAST(phone AS UNSIGNED) = 13800000000第3步：对索引列使用了函数，索引失效！
还记得原则③吗？索引列上不能做计算！类型转换也是一种计算！
原理：
- 字符串和数字比较时，MySQL会把字符串转成数字
- 转换发生在索引列上
- 破坏了B+树的有序性
- 索引失效，全表扫描！
正确的写法：
✅ VARCHAR类型 - 加引号
WHERE phone = '13800000000'✅ CHAR类型 - 加引号
WHERE code = 'A123'记住口诀：字符串类型，必须加引号！不加引号 = 隐式转换 = 索引失效！
总结
好了，8大原则我们全部讲完了！我们来回顾一下：
1️⃣ 全值匹配我最爱 - 复合索引全部用上，效率最高
2️⃣ 最左前缀要遵守 - 带头大哥不能死，中间兄弟不能断
3️⃣ 索引列上少计算 - 函数、运算都会破坏有序性
4️⃣ 范围之后全失效 - 范围查询后面的索引无法使用
5️⃣ LIKE百分写最右 - 前缀确定才能用索引
6️⃣ 覆盖索引不写星 - 避免回表是性能优化的核心
7️⃣ 不等空值还有OR - 三大索引杀手要避免
8️⃣ VARCHAR加引号 - 避免隐式类型转换