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

前言：

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

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

准备数据：
本篇数据都基于上一篇，传送门如下：

MySQL–索引优化实战篇（1）

案例一：

explain select * from user where age=25 and address='广东';

执行计划：

分析执行计划：

• 我们知道表中存在的索引是 index_name，index_name_age_address，age、address列明明是建立了索引的，而我们却没有用到任何索引，为什么？

最左前缀法则：
最左前缀法则指的是要从联合索引的最左侧列开始，如果最左侧的列没有作为查询条件，那就无法使用这个联合索引。

案例二：

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

执行计划：

分析执行计划：

• 我们知道可能用到的索引是 index_name，index_name_age_address，我们的SQL语句遵循了最左前缀法则，这次我们用到了索引index_name_age_address，没有问题。
• 再来看看key_len：1006，我们的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，没有问题。
• rows：1，user_name为张三，age为25，address 为广东，只有一条数据，没有问题。
• filtered：100，经过过滤后只有一条，且只需要一条，没有问题。

案例三：
因为我们的 user 表中有索引 index_name，index_name_age_address，为了避免 index_name 对我们的结果有影响，我们先隐藏 index_name 索引。

#隐藏 user 的索引 index_name ，避免 index_name  对结果有影响
alter table user alter index index_name INVISIBLE;

执行如下SQL：

explain select * from user where user_name='张三';

执行结果：

根据执行计划可知，使用的索引是index_name_age_address，在上一篇中我们验证过，同样的SQL，使用到的索引是index_name，
证明隐藏索引生效了。

取消索引隐藏SQL语法：

#取消tablename 的索引index_name 的隐藏
ALTER TABLE tablename ALTER  INDEX  index_name VISIBLE; 

案例三的重点来了，执行如下SQL：

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

执行计划：

分析执行计划：

• 我们知道可能用到的索引是 index_name_age_address，这次我们用到的索引是 index_name_age_address，没有问题。
• 再来看看key_len：202，我们的user_name 字段是 varchar(50) utf8mb4编码，50 * 4 + 2 = 202，证明只用了user_name 字段 。
• rows：2，user_name 为张三， 有2条数据，没有问题。
• filtered：33.33，这个没有看明白，欢迎各位大佬评论指导。
• Using index condition，查询条件虽然用到了索引列，但是有部分条件无法使用索引列，先会使用索引列的条件搜索一遍，在使用其他条件搜索。

案例四：

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

执行计划：

分析执行计划：

• 没有用到索引，因为address、age都不是联合索引 index_name_age_address 最左的列，不符合最左前缀法则，没有问题。

总结：最左前缀法则会根据联合索引字段顺序依次去更where 后的筛选条件进行对比，直到出现跳字段为止，比如本案例跳过了age字段，就只能使用 index_name_age_address 索引中的 user_name 字段，where 条件中打乱联合索引的字段顺序是没有问题的，这里就不做演示了，有兴趣的可以去验证一下。

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