mysql联合索引的使用规则

联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

从一道有趣的题目开始分析：

假设某个表有一个联合索引（c1,c2,c3,c4）以下选项哪些字段使用了该索引：
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

下面我们开始：

首先创建表：

CREATE TABLE t(c1 CHAR(1) not null,c2 CHAR(1) not null,c3 CHAR(1) not null,c4 CHAR(1) not null,c5 CHAR(1) not null)ENGINE myisam CHARSET UTF8;

创建索引：

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

使用MySql Explain开始分析题目结果：

A选项：

结果可以看出，c1,c2,c3,c4均使用到了该索引，而我们对A结果稍作更改：

将c2条件去掉后：

根据索引最左原则，c2字段没有使用索引，c2之后的字段都不能使用索引。下面2图我们对比下索引最左原则：

上图结果显示直接使用c3是全表查询，无法使用该索引的，所以c3字段使用索引的前提是c1，c2两字段均使用了索引。

即是索引的最左原则（左前缀原则）。

B选项：

key_len长度说明c1，c2字段用到了该索引，Extra显示并没有使用临时表进行排序，说明排序是使用了索引的，但并没有计算在key_len值中，也没有起到连接c4的作用，说明索引到c3这里是断掉的。

排序其实是利用联合索引直接完成了的，即：使用了c1234联合索引，就已经使得c1下c2，c2下c3，c3下c4是有序的了，所以实际是排序利用了索引，c3字段并没有使用该索引。（这段写的时候总感觉有点别扭，不知道我理解的对不对，还有待更深层次的研究）

C选项：

使用group by 一般先生成临时文件，再进行排序，但是字段顺序为c2，c3时，并没有用临时表进行排序，而是利用索引排序好的；当group by字段为c3，c2时，由于与索引字段顺序不一致，所以分组和排序并没有利用到索引。

由key_len长度确定，只有c1一个字段使用了索引。

D选项：

order by 和group by 类似，字段顺序与索引一致时，会使用索引排序；字段顺序与索引不一致时，不使用索引。

由key_len长度确定，只有c1一个字段使用了索引。

E选项：

其实选项E的结果分析在上述ABCD的结果中都分析过了，这里只有c1，c2字段使用了该索引。

综上所述问题答案：

A:四个字段均使用了该索引

B:c1，c2字段使用了该索引

C:c1字段使用该索引

D:c1字段使用该索引

E:c1，c2字段使用了该索引

总结：

索引的最左原则（左前缀原则），如（c1,c2,c3,c4....cN）的联合索引，where 条件按照索引建立的字段顺序来使用（不代表and条件必须按照顺序来写），如果中间某列没有条件，或使用like会导致后面的列不能使用索引。

索引也能用于分组和排序，分组要先排序，在计算平均值等等。所以在分组和排序中，如果字段顺序可以按照索引的字段顺序，即可利用索引的有序特性。

总结：

更多相关文章

随机推荐