阿里面试题: (a,b,c)组合索引, 查询语句select…from…where a=.. and c=..走索引吗?
面试官:(a,b,c)组合索引,查询语句select…from…where a=.. and c=..走索引吗应聘者:
-
最佳左前缀法,如果索引了多列,要遵守最左前缀法则,否则索引失效
-
按最左前缀原则,a能走索引,c走不了,只能用到a部分索引
接下来,我们具体来分析一下
1、组合索引在树中是如何排序的
在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这种查询条件没有办法利用索引。
2、真实案例分析
我们来建立一张用户表,在user_name,age,user_sex上建立组合索引
按照最左原则,我们按以下方式查询数据:
通过观察上面的结果图可知,where后面的查询条件,不论是使用(user_name)、(user_name,age) 还是 (user_name,age,user_sex),在查询时都使用到了联合索引。
那我们打乱查询的顺序,又会是什么效果呢?
由上图可知,即便我们打乱了查询顺序,仍然可以使用到索引,这是因为MySQL中有查询优化器explain,所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同,查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高,最后才能生成真正的执行计划,所以不论以何种顺序都可使用到联合索引。
接下来我们再来看问题的场景:
由上图可知,我们也用到了索引,但是只用了部分索引,也就是user_name部分的索引,还可以从另外一个字段ref看到,使用的常量const只有一个。联合索引树是按照user_name字段创建的,但user_sex相对于user_name来说是无序的,只有user_name是有序的,所以只能使用联合索引中的user_name索引。
接下来我们再来看因为不满足最左原则导致的索引失效场景:
由上图可知,因为不满足最左原则,本来要以user_name排序开始,现在user_name断层了,没办法使用后面的索引了,故变成全表扫描了。
那我们有什么办法可以解决这种失效问题呢?
由上图可知,我们查询的不是全表字段,而是索引字段,通过观察发现上面key字段在搜索中也使用了idx_user_nameAgeSex索引,可能许多同学就会疑惑它并没有遵守最左匹配原则,按道理会索引失效,为什么也使用到了联合索引?因为没有从age始匹配,且age单独来说是无序的,所以它确实不遵循最左匹配原则,然而从type字段可知,它虽然使用了联合索引,但是它是对整个索引树进行了扫描,正好匹配到该索引,与最左匹配原则无关,一般只要是某联合索引的一部分,但又不遵循最左匹配原则时,都可能会采用index类型的方式扫描,但它的效率远不如最做匹配原则的查询效率高,index类型类型的扫描方式是从索引第一个字段一个一个的查找,直到找到符合的某个索引,与all不同的是,index是对所有索引树进行扫描,而all是对整个磁盘的数据进行全表扫描。