MySQL联合索引深度解析
假设某个表有一个联合索引(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.wherre c1=? and c2=? and c5=? order by c2,c3
创建一张表并插入数据：
root@db5.7.18[test]> use test;
Database changed
root@db5.7.18[test]> create table t(c1 char(10),c2 char(10),c3 char(10),c4 char(10),c5 char(10)) engine innodb charset utf8;
Query OK, 0 rows affected (0.07 sec)
root@db5.7.18[test]> insert into t values('a1','a2','a3','a4','a5');
Query OK, 1 row affected (0.00 sec)
root@db5.7.18[test]> insert into t values('b1','b2','b3','b4','b5');
Query OK, 1 row affected (0.00 sec)
root@db5.7.18[test]> select * from t;
+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+------+------+------+------+------+
| a1 | a2 | a3 | a4 | a5 |
| b1 | b2 | b3 | b4 | b5 |
+------+------+------+------+------+
2 rows in set (0.00 sec)
root@db5.7.18[test]> root@db5.7.18[test
-> \c
root@db5.7.18[test]> alter table t add index c1234(c1,c2,c3,c4);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
看一下题目第一个选项：
A.where c1=x and c2=x and c4>x and c3=x
root@db5.7.18[test]> explain select * from t where c1='a1' and c2='a2' and c4>'a4' and c3='a3'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: c1234
key: c1234
key_len: 124
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
优化器会将上面的SQL转换为以下等值查询：
select * from t where c1='a1' and c2='a2' and c3='a3' and c4>'a4' \G;
说明A选项使用了联合索引，进行范围查询，说明C4的索引使用到了,索引长度为124
索引长度计算方法：
如果列是字符型,则还需要考虑字符集,如某列的定义是char(10),且是utf8,不能为空,则key_len为10 * 3 ,可以为空则key_len为10*3+1
key_len=(10*3+1)+(10*3+1)+(10*3+1)+(10*3+1)=124
如果在联合索引中，将C2不放到查询条件中，会是什么情况?
root@db5.7.18[test]> explain select * from t where c1='a1' and c4>'a4' and c3='a3'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 31
ref: const
rows: 1
filtered: 50.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
索引长度：key_len=(10*3+1) 说明只使用了联合索引的C1
索引长度由124变成31，说明只使用了c1的索引，后面的C2,C3,C4索引都没有用到，因为C2变成了一个未知数,导致后成的所有索引都无法使用
如果C3没有出现在WHERE条件中，会是什么情况?
root@db5.7.18[test]> explain select * from t where c1='a1' and c2='a2' and c4>'a4' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 62
ref: const,const
rows: 1
filtered: 50.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
索引长度：key_len=(10*3+1) +(10*3+1) 说明只使用了联合索引的C1,C2,其它索引没有使用到
看一下题目第二个选项：
B.where c1=x and c2=x and c4=x order by c3
root@db5.7.18[test]> explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 62
ref: const,const
rows: 1
filtered: 50.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
索引长度：key_len=(10*3+1) +(10*3+1) 说明只使用了联合索引的C1,C2,其它索引没有使用到，没有用到全部的联合索引
这个里order by c3为什么没有用到临时表排序？
因为在联合索引中，C3的数据是有序的，所有排序不需要通过临时表，直接在索引上就可以完成排序，所有不需要使用临时表
看一下题目第三个选项：
C.where c1=x and c4=x group by c3,c2
root@db5.7.18[test]> explain select c3,c2 from t where c1='a1' and c4='a4' group by c3,c2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 31
ref: const
rows: 1
filtered: 50.00
Extra: Using where; Using index; Using temporary; Using filesort
索引长度：key_len=(10*3+1) 说明只使用了联合索引的C1,其它索引没有使用到，没有用到全部的联合索引
看到Using temporary; Using filesort,这是为什么呢？
看一下联合索引是有序的，它的顺序是C1,C2,C3,C4,而SQL执行过程是先执行group by 分组函数，再通过分组后返回值
而分组的顺序不是联合索引的顺序，就里要通过临时表进行排序。
如果分组的前台调换一下会怎么样？
root@db5.7.18[test]> explain select c3,c2 from t where c1='a1' and c4='a4' group by c2,c3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 31
ref: const
rows: 1
filtered: 50.00
Extra: Using where; Using index
索引长度：key_len=(10*3+1) 说明只使用了联合索引的C1,其它索引没有使用到，没有用到全部的联合索引
可以看到，现在没有使用临时表排序，说明执行计划是直接通过联合索引排序，因为索引是有序的，可以一边扫描索引，一边进行分组
不会使用到中间的临时表
看一下题目第四个选项：
D.where c1=? and c5=? order by c2,c3
root@db5.7.18[test]> explain select * from t where c1='a1' and c5='a5' order by c2,c3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 31
ref: const
rows: 1
filtered: 50.00
Extra: Using index condition; Using where
索引长度：key_len=(10*3+1) 说明只使用了联合索引的C1,其它索引没有使用到，没有用到全部的联合索引
按C2,C3联合索引的顺序来排序，所有不会使用到中间临时表，直接在联合索引上进行排序，直接输出
看一下题目第五个选项：
E.where c1=? and c2=? and c5=? order by c2,c3
root@db5.7.18[test]> explain select * from t where c1='a1' and c2='a2' and c5='a5' order by c2,c3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 62
ref: const,const
rows: 1
filtered: 50.00
Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)
索引长度：key_len=(10*3+1) +(10*3+1) 说明只使用了联合索引的C1 C2,其它索引没有使用到，没有用到全部的联合索引
按C2,C3联合索引的顺序来排序，没有使用到中间的临时表.
再看下面的SQL,order by c3,c2，与联合索引的顺序不一样，看一下会发生什么？
root@db5.7.18[test]> explain select * from t where c1='a1' and c2='a2' and c5='a5' order by c3,c2\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 62
ref: const,const
rows: 1
filtered: 50.00
Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)
索引长度：key_len=(10*3+1) +(10*3+1) 说明只使用了联合索引的C1 C2,其它索引没有使用到，没有用到全部的联合索引
--注意：key_len是表示得到结果集所使用的选择的索引的长度，但不包括order by,也就是说，如果order by也使用了索引则key_len则不计算在内。
为什么这次没有用到中间表，而是直接通过索引排序呢？
原因是：WHERE条件中，C2='a2'的值已确定，所有在排序的时候，C2就变成了一个常量，而C3在联合索引中又是有序的，所有可以直接通过索引排序，不需要使用临时表来排序
总结：
索引左前缀规则：
如(f1,f2,f3,fN)的复合索引
where 条件按建立的顺序来使用索引(不代表and条件必须要按顺序来写)
如果中间某列没有条件，或like条件，导致后面的列索引使用不了
索引也能用于排序和分组
所有，我们的order by ,group by，如果能针对有顺序的表进行，可以避免使用临时表或文件排序，即，我们的order by ,group by 按顺序使用索引的列，可发挥索引的作用。
