学习2-mysql联合索引

一、组合索引
表结构如下

在这里插入代码片create table t_user
(
    user_id int auto_increment
        primary key,
    u_name  varchar(20) null,
    age     int         null,
    sex     int         null,
    city    varchar(20) null
);
--创建一个联合索引，MySQL在不指定索引类型的情况下，默认创建的是B+树索引。
create index u_name_sex_age
    on t_user(u_name, sex,age);

1、最左原则
按照创建的索引顺序查询

explain select * from t_user
where u_name='lijia' and sex=1 and age=36;

1.1交换查询顺序，依然能命中索引
这是应为mysql底层有sql优化环节自动跳转执行顺序，但是开发中不建议这样写，会增加mysql的负担。直接按照索引顺序去写。

explain select * from t_user
where  sex=1 and age=36 and u_name='lijia';

1.2 使用部分字段查询

使用前两个字段查询

explain select * from t_user
where u_name='lijia' and sex=1 

依然可以使用索引，但是其中的key_len的长度为68，比全部的长度少了5个字节，全部字段长度计算公式：203+2+1+4+1+4+1=73；
公式解释：
vachar类型长度在utf8编码格式下等于3n+2，即为203
int类型占4个字节为4
可以为空占1个字节，因此需要多加上3个1
当前为68=73-5刚好是一个可以为空的int类型长度。所以推断只用了组合索引中的前两个索引

使用后面两个字段查询

explain select * from t_user
where  sex=1 and age=36;

索引失效
原因分析：
结合上图的b+树分析，可以看出该组合索引的顺序是按照第一个字段排序的，相同则按照第二字段排序，以此类推的原则来创建这棵树的。
而我们现在查询条件为后两个字段，自然在b+树上是无法匹配到有序数据进行二分查找的。自然索引失效了。

带范围的查询

explain select * from t_user
where u_name='lijia' and sex>1 and age=36;

首先是走了索引的，但是观察索引的长度我们发现值走了前面两个字段，最后一个字段是失效的。
原因分析：依然结合上面的b+树可以分析得出结论，对一个范围内的结果集，后面的字段在这棵b+树上也是没法确定顺序的，因此范围查询后面的字段自然也无法使用索引了。

索引覆盖

explain select * from t_user
where u_name like '%li%';

执行结果必然不会走索引，like失效，从b+树分析无法确定前面字符是什么，自然也无法使用有序的索引了。

explain select u_name, sex,age from t_user
where u_name like '%li%';

使用到了索引，原因分析：
因为非聚集索引的叶子节点存放的是索引字段的值与主键的值，而这个sql的结果集能在索引字段上都能找到，那么mysql底层自然不会去全表扫描，而是直接直接搜索这棵b+树返回结果不用再去回表访问数据页，这既是索引覆盖。

索引下推

explain select * from t_user
where u_name like 'li%' and age=22 and sex=1;

使用了全部的索引字段，正常理解应该是在使用第一个字段过滤后得到的结果集在后面的字段是不能保证有序性的，那么就不能使用当前的索引的了，但是这里为什么又能用呢？
原因分析：在mysql5.6以前是第一个字段使用索引取到叶子节点的数据后就去回表拿到结果集，再匹配对后面的字段。
mysql5.6以后引入了索引下推的概念，即在第一个字段查询遍历索引的过程中同时对where条件内包含了索引的字段进行过滤，这样得到的结果集必然比只过滤一个字段的结果集要少。就减少了回表的条数。（因为b+树的聚簇索引的叶子节点包含了整行的数据，因此在直接获取第一个字段回表后过滤后面两个字段的结果集与在索引遍历时同时过滤掉包含索引的其他字段再回表得到的结果集是一致的。）

以上仅是我对联合索引的理解，欢迎各位大佬指出错误。