多列索引实验

回顾：简单介绍多列索引生效规则

先建表

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 '',    key(c1,c2,c3,c4))engine myisam charset utf8;insert into t1 values ('a','b','c','d','e');insert into t1 values ('a','B','c','d','e');insert into t1 values ('a','b','C','d','e');insert into t1 values ('a','b','c','D','e');insert into t1 values ('a','b','c','D','E');

c1,c2,c3,c4列有一个复合索引。

A.

mysql> explain select * from t1 where c1='a' and c2='b' and c4>'a' and c3='c'\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 9          ref: const,const,const         rows: 2     filtered: 33.33        Extra: Using index condition1 row in set, 1 warning (0.02 sec)

B.

mysql> explain select * from t1 where c1='a' and c2='b' and c4='a' order by c3\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 6          ref: const,const         rows: 4     filtered: 20.00        Extra: Using index condition1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where c1='a' and c2='b' and c4='a' order by c5\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 6          ref: const,const         rows: 4     filtered: 20.00        Extra: Using index condition; Using filesort1 row in set, 1 warning (0.00 sec)

可以看出order by c5的时候Using filesort，要二次排序

mysql> explain select * from t1 where c1='a' and c5='a'  order by c2,c3\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 3          ref: const         rows: 4     filtered: 20.00        Extra: Using index condition; Using where1 row in set, 1 warning (0.00 sec)

这里为什么没有Using filesort？，是因为order by c2,c3 是排序好的。

那么要是order by c3,c2，优先安装c3来排序呢？

mysql> explain select * from t1 where c1='a' and c5='a'  order by c3,c2\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 3          ref: const         rows: 4     filtered: 20.00        Extra: Using index condition; Using where; Using filesort1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where c1='a' and c2='b' and c5='a'  order by c3,c2\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 6          ref: const,const         rows: 4     filtered: 20.00        Extra: Using index condition; Using where1 row in set, 1 warning (0.00 sec)

为什么上面没有用到filesrot？因为order by c3,c2 其实就是order by c3,'b', where条件里 c2=’b’。

C、