为什么要创建联合索引

多个单列索引联合使用，同样可以达到联合索引的功能，并且联合索引还会有索引失效的情况，禁不住一探为什么还要创建联合索引？总结下来有4个优点，分别时降低维护索引的开销、使用索引下推减少回表查询记录数、使用索引覆盖减少回表以及减少避免死锁频率。

联合索引数据结构

联合索引创建索引，非叶子节点只会存储联合索引的第一个关键字，叶子节点存储联合索引的所有字段值+集索引索引Id,查询的时候，先比较第一个关键字，第一个关键字相同 ，比较第二个关键字，第一个关键字，则按照后面的关键字排序，后面关键仅局部有序。

联合索引进行范围查询后，为什么后面的索引字段会失效：

select * from where a > 1 and b = 2;

因为a>1的所有记录中，b是整体无序的，只局部有序，只有联合索引第一个字段时整体有序的。

优点1：降低索引维护开销

索引字段的更新，需要修改对应的索引，两个索引只需维护一个B+Tree树，分开的索引，需要对每个B+Tree树进行修改。

优点2：索引下推

所谓索引下推就是当使用索引多个字段作为查询条件时，当根据联合索引第一个字段查询筛选到记录是，会继续使用联合索引后面字段你对该记录进行筛选，筛选结束后，得到聚集索引值，可以减少回表时查询的记录数。

例子：从account表中根据查询户名和密码满足条件、并且依然生效的用户所有信息（is_deleted= 0），account表中有一个联合主键（user_name,password,is_deleted）；

select * from account wehre user_name= ‘张三’ and password = ‘123456’ and is_deleted = 0;

关闭索引下推查询过程：

首先根据user_name =”张三”找到所有条件的聚集索引id，然后回表，得到所有索引记录，然后再再得到的记录中使用查询条件对回表得到的索引记录进行筛选过滤。

开启索引下推查询过程：

首先根据user_name=”张三”确定所有满足条件的叶子条件，然后使用联合索引中其它列对叶子节点进行筛选，拿着筛选到的聚集索引进行回表。这样减少回表查询数据记录数。

可以通过设置SET optimizer_switch = ‘index_condition_pushdown=off’;关闭或者开启索引下推。

优点3：索引覆盖

select password ，username from account where phone = “13456707045” ；

在一个表中如果查询条件字段和select 字段在一个索引中，那么就不需要回表，就可以直接获得所需要的值。

优点4：降低死锁发生概率

非聚集索作为查询条件的加锁顺序为先对所有要回表记录的非聚集索引加锁，然后回表对聚集索引加锁。并发环境下，在一条sql语句的where条件中同时使用多个非聚集单列索引，需要执行多组非聚集聚集索引和聚集索引加锁，在不同事务中，每组执行顺序不一致是，增加了产生死锁的原因；使用联合索引，第一个事务先对非聚集索引加锁，然后对所有要回表记录非聚集索引加锁，再对聚集索引加锁，执行顺序只有一种，死锁产生的概率大大降低，虽然mysql有自动死锁检测和修复功能，但高频率的死锁会影响查询效率。

Conclusion

合理创建和使用联合索引可以减少查询数据时回表次数或者减少回表查询时不必要数据量，从而优化查询效率。