mysql 联合索引结构与索引匹配原则

联合索引结构与索引匹配原则

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一棵B+树，那么联合索引的底层也就一棵B+树，只不过联合索引的B+树节点中储存的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

举例：创建一个（A,B）的联合索引，那么它的索引树就是下图的样子。

可以看到A的值是有序的 1,1，2,2，3,3，而B的值是没有顺序的1,2，1,4，1,2 。但是我们又能发现A在等值的情况下，B值又是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后再对第二个字段进行排序。所以B=2这种查询条件没有办法利用索引。

示例：

表结构：

字段	类型	描述
id	int(11)	主键
name	varchar(10)	名称
age	int(11)	年龄
sex	int(10)	性别

该表中id列，name列，age列建立了一个联合索引 idx_name_age_sex ，实际上相当于建立了三个索引：（name）（name_age）（name_age_sex）


建立索引：alter table staffs add index idx_name_age_sex(name,age,sex);


下面介绍下可能会使用到该索引的几种情况：

1. 全值匹配查询

通过观察上面的结果图可知，where后面的查询条件，无论是使用（name,age,sex）（age,name,sex）（sex,age,name）顺序，在查询时都使用到了联合索引。可能有人会疑惑，为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配，却也使用到了联合索引？ 这是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条sql语句以什么样的顺序执行效率高，最后才能生成真正的执行计划，所以无论以何种顺序都可以使用到联合索引。

2. 匹配最左边列时

该搜索是遵守最左匹配原则的，通过key字段可知，在搜索过程中使用到了联合索引，且使用的是联合索引中的（name）索引。

由于name到age是从左依次往右边匹配，这两个字段中的值都是有序的，所以也遵守最左匹配原则，通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（name_age）索引。

由于上面三个搜索都是从左边name依次向右开始匹配的，所以都用到了（name_age_sex）联合索引。

那如果不是依次匹配呢？

通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（name）索引，因为联合索引树是按照name字段创建的，但sex相对于name来说是无序的，只有name是有序的，所以他只能使用联合索引中的name索引。

通过观察发现上面key字段在搜索中也使用了（name_age_sex）索引，可能大家就会有疑惑，它并没有遵守最左匹配原则，按道理会索引失效，为什么也使用到了联合索引？ 因为没有从name开始匹配，且age单独来说是无序的，所以它确实不遵守最左匹配原则，然而从type字段可知，它虽然使用了联合索引，但是它是对整个索引树进行了扫描，正好匹配到了该索引，与最左匹配原则无关，一般只要是某联合索引的一部分，但又不遵守最左匹配原则时，都可能会采用index类型的方式扫描，但它的效率远不如最左匹配原则的查询效率高，index类型的扫描方式是从索引的第一个字段一个一个的查找，直到找到符合的某个索引，与all不同的是，index是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描。

这两个结果跟上面的是同样的道理，由于它们没有从最左边开始匹配，所以没有用到联合索引，而是使用了index类型进行全索引扫描。

3. LIKE 模糊查询

对于模糊匹配的查询，如果是前缀匹配的是索引，中缀和后缀用的是全索引扫描。