联合索引使用规则详解数据库

问题：

假设某个表有一个联合索引（c1,c2,c3,c4）以下只能使用该联合索引的c1,c2,c3部分

A where c1=x and c2=x and c4>x and c3=x

B where c1=x and c2=x and c4=x order by c3

C where c1=x and c4= x group by c3,c2

D where c1=? and c5=? order by c2,c3

E where c1=? and c2=? and c5=? order by c2,c3

一、创建测试表和联合索引

创建表t1，有c1到c5共5个字段，特别说明一下，字段类型都是定长char(1) 类型，并且非空，字符集是utf8（与计算索引使用字节数有关）。

create table t1( 
c1 char(1) not null default '', 
c2 char(1) not null default '', 
c3 char(1) not null default '', 
c4 char(1) not null default '', 
c5 char(1) not null default '' 
)engine myisam charset utf8;

 创建联合索引：

alter table t1 add index c1234(c1,c2,c3,c4);

插入3条数据：

insert into t1 values('a','b','c','d','e'),('A','b','c','d','e'),('a','B','c','d','e');

 二、分析

1、A选项

    我们看解析A这条sql的结果，与索引有关的主要是possible_keys,key,key_len这三项，possible_keys是指可能会用到的索引，key是当前sql使用到的索引，key_len是索引的使用字节数。key的值是c1234表示联合索引用上了，那是不是c1,c2,c3,c4全用上了咧，我们得从key_len分析一下。

    因为字段类型是char(1)，字符集是utf8，所以每个字段的key_len 是 1*3=3，key_len现在等于12表示c1,c2,c3,c4这四个字段都用上了索引，（如果字段类型是null，那单个字段的索引字节数需要 +1，如果字段类型为非定长类型，比如varchar，那字节数需要再 +2，这里方便理解，统一定义成了定长char）

2、B选项

    我们看到key=c1234，表示B使用了联合索引，key_len=6表示有两个字段使用了索引，这两个字段就是C1和c2，这个sql里面有一个order by c3，order by不能使用索引，但是却利用了索引，为什么这么说咧，如果我们改成order by c5，看下面的：

    与上面的结果对比，发现在Extra中的值使用了Using filesort。Using filesort表示在索引之外，需要额外进行外部的排序动作。因为c5的顺序是没有规律的，所以需要对其进行一次排序，而在order by c3的时候，c3其实在索引表里面已经是排好序的了，不需要再排序，所以说其实他利用上了索引。

3、C选项

     key=c1234，表示B使用了联合索引，key_len=3表示有1个字段使用了索引，这个字段就是C1，与B语句不一样的是 Extra的值，C语句里面使用了临时表(Using temporary) 和 排序（filesort），因为组合索引是需要按顺序执行的，比如c1234组合索引，要想在c2上使用索引，必须先在c1上使用索引，要想在c3上使用索引，必须先在c2上使用索引，依此类推。回到B语句中，因为c2字段已经使用了索引，所以在order by c3的时候 c3其实在索引表里面已经是排好序的了，不需要建临时表，不需要再排序，所以说其实他利用上了索引。
而C语句中，group by 的顺序是先c3，再c2，在对c3进行group by的时候，c2字段上的索引并没用使用，所以索引在这里就断了，只用上了c1一个字段的索引。

如果group by 的顺序改成c2,c3，会是什么样？

从结果中看没有用到临时表和filesort，因为c2,c3在索引表中本身就是有序的。

4、D选项

    从结果中看到key=c1234，表示B使用了联合索引，key_len=3表示有一个字段使用了索引，即C1字段。而c2，c3字段在order by中是顺序执行，所以也利用了索引。这里没有使用filesort就是因为c2，c3本身在索引表中就是有序的，所以不需要对其再排序。那如果反之，先按c3排序，再按c2排序，会是什么情况，看下面：

从结果中看到使用了filesort，这里就无法合理的使用索引了。举个例子来说，好比中国下的省是有序的，如果按照先找国家再找省那自然是顺序的，而如果反过来，先找省再找国家，那肯定是乱序的，自然也就不能利用索引了。

5、E选项

E语句c1和c2使用了索引，c3在order by中利用了索引。如果再反之，先按c3排序，再按c2排序会是什么情况？

order by c3,c2，按理说应该使用filesort，但从结果中看并没有使用，这是为什么呢？注意仔细看查询条件，c1=’a’ and c2=’b’，那么在所有查询出来的行中，c2其实只有一个值，永远等于’b’，也就是说c2是一个常量，也就意味着order by c3,c2 相当于对c3和一个常量排序，可以写成这样order by c3,’b’，所以order by c3,’b’说白了还是对c3进行排序，因为后面的‘b’永远都是一样的，他是一个常量，而常量对排序并没有影响，可以忽略不计，因此这里并没有使用filesort。

如果将条件c2=’b’去掉，那自然就要用到filesort了，看下面的结果：