联合索引使用规则

2/13/2017来源:SQL技巧人气:3718

假设某个表有一个联合索引(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 有谁知道下面A-E能否可以使用索引!!为什么?
接下来我建表来帮你解答一下哈定义表t,有c1到c5 5个字段,特别说明一下 字段类型都是定长char(1)类型,并且非空,字符集是utf8(与计算索引使用字节数有关) QQ截图20131109233034.png  创建联合索引 QQ截图20131109234601.png  插入2条测试数据 QQ截图20131109234733.png  好,我们先来看A选项 QQ截图20131109234954.png  我们看解析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)

再接着看B这条sql语句 QQ截图20131110000049.png  我们看到key=c1234,表示B使用了联合索引,key_len=6表示有两个字段使用了索引,这两个字段就是C1和c2,这个sql里面有一个order by c3,order by不能使用索引,但是却利用了索引,为什么这么说咧,先看C这条sql QQ截图20131110000731.png  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一个字段的索引
D语句c1字段使用了索引,c2,c3字段在order by中是顺序执行 所以也利用了索引 QQ截图20131110002133.png  E语句c1和c2使用了索引,c3在order by中利用了索引 QQ截图20131110002358.png 

2013-11-10 00:24:19 上传 下载附件 (6.78 KB)