- 建表插入数据：
-
create table test03(
-> id int primary key not null auto_increment,
-> c1 char(10),
-> c2 char(10),
-> c3 char(10),
-> c4 char(10),
-> c5 char(10)); - insert into test03(c1,c2,c3,c4,c5) values(‘a1‘,‘a2‘,‘a3‘,‘a4‘,‘a5‘);
-
- 建立索引:alter table test03 add index idx_test03_c1234 (c1,c2,c3,c4);
- sql语句分析：
-
explain select * from test03 where c1=‘a1‘;
-
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘;
-
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘ and c3=‘a3‘;
-
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘ and c3=‘a3‘ and c4=‘a4‘;
-
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘ and c4=‘a4‘ and c3=‘a3‘;
-
-
explain select * from test03 where C1=‘a1‘ and c2=‘a2‘ and C3>‘a3‘ and c4=‘a4‘;
-
-
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘ and c4>‘a4‘ and c3=‘a3‘;
-
explain select * from test03 where C1=‘a1‘ and c2=‘a2‘ and c4=‘a4‘ order by c3;
c3作用在排序而不是查找 -
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘ order by c3;
-
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘ order by c4;
-
explain select * from test03 where c1=‘a1‘ and c5=‘a5‘ order by c2,c3;
只用c1一个字段索引，但是c2、c3用于排序，无filesort
-
-
explain select * from test03 where C1=‘a1‘ and c5=‘a5‘ order by c3,c2;
出现了filesort,我们建的索引是1234， 它没有按照顺序来,T 32颠倒了 -
-
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘ order by c2,c3;
-
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘ and c5=‘a5‘ order by c2,c3;
用c1、c2两个字段索引，但是c2、c3用于排序,无filesort -
-
explain select * from test03 where c1=‘a1‘ and c2=‘a2‘ and c5=‘a5‘ order by c3,c2;
本例有常量c2的情况，和8.2对比 -
-
explain select * from test03 where c1=‘a1‘ and c5=‘a5‘ order by c3,c2;
-
explain select * from test03 where c1=‘a1‘ and c5=‘a5‘ order by C3,c2;
-
-
explain select * from test03 where c1=‘a1‘ and c4=‘a4‘ group by c2,c3;
-
-