MySQL--索引优化实战篇（3）

前言：

我们常说的SQL优化，简单来说就是索引优化，通过合理创建索引，调整SQL语法等，来提升查询效率，想要进行SQL优化，就必须知道索引的原理，而且能够看懂SQL的执行计划。

MySQL–索引底层数据结构详解
MySQL–索引类型详解
MySQL–explain执行计划详解

准备数据：
本篇数据都基于上一篇，传送门如下：
MySQL–索引优化实战篇（1）
MySQL–索引优化实战篇（2）

案例一：

explain select * from user where user_name='张三' and age>21 and address='广东';

执行计划：

分析执行计划：

• 很明显使用了索引 index_name_age_address，复合最左前缀原则，没有问题。
• 再来看看key_len：204，我们的user_name 字段是 varchar(50) utf8mb4 编码，50 * 4 + 2 = 202，age字段 tinyint 类型占用一个字节，且age字段可以为空，再占用一个字节，共占用2个字节，我们的 address 字段是 varchar(200) utf8mb4 编码，200 * 4 + 2 = 802，合计应该是 202 + 2 + 802 = 1006，但实际上是204，很明显只用到了 user_name、 age，明明三个字段的联合索引为什么只用了两个字段呢 ?

原因：最左前缀法则，如果某一列出现了范围查询，那么从这一列之后的字段就无法再走索引了。

案例二：

explain select * from user where user_name='张三' and age=28 and address='广东';

执行计划：

explain select user_name,age,address from user where user_name='张三' and age=28 and address='广东';

执行计划：

两个SQL的区别：

• 第一个SQL 使用了 select * from，而第二个SQL指定返回了三个字段。
• 第二个SQL指定返回的字段，恰好是 联合索引 index_name_age_address 的三个字段。

对比执行计划：

• 除了Extra项不同之外，其他项完全相同。
• 第二个SQL是 Using Index，属于使用了覆盖索引。

案例三：

explain select * from user where user_name like '张%' and age=28 and address='广东';

执行结果：

分析执行计划：

• 很明显使用了 index_name_age_address 索引，符合最左前缀法则，表明字段后缀模糊查询可以走索引。
• 再来看看key_len：204，我们的user_name 字段是 varchar(50) utf8mb4 编码，50 * 4 + 2 = 202，age字段 tinyint 类型占用一个字节，且age字段可以为空，再占用一个字节，共占用2个字节，我们的 address 字段是varchar(200) utf8mb4 编码，200 * 4 + 2 = 802，合计202 + 2 + 802 = 1006，没有问题。

案例四：

explain select * from user where user_name like '%张' and age=28 and address='广东';

执行结果：

分析执行计划：

• 没有走索引。

原因：字段前面加模糊查询无法走索引，字段后面加模糊查询可以走索引。

案例五：

explain select user_name  from user where user_name like '%张' and age=28 and address='广东';

执行结果：

分析执行计划：

• 同样是字段前面加模糊查询，但是这次走了索引。
• 跟案例四对比，返回由 select * 变为了只 返回 user_name ，是覆盖索引的范围。

原因：命中了覆盖索引。

覆盖索引：
索引中包含了查询返回列的所有字段，就叫做覆盖索引。

为什么使用了覆盖索引，字段前模糊查询就可以命中索引？

覆盖索引中包含了全部的的返回字段了，就无需回表查询，只需要扫描覆盖索引树就能到的查询结果，这里要结合覆盖索引的底层数据结构去理解，才会比较容易理解。

如有不正确的地方请各位指出纠正。