索引题分析

#建表语句

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');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

创建索引

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

1）explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c3=‘a3’ and c4=‘a4’;

2）explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c4=‘a4’ and c3=‘a3’;

3）explain select * from test03 where c4=‘a4’ and c2=‘a2’ and c3=‘a3’ and c1=‘a1’;

4）explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c3>‘a3’ and c4=‘a4’;

5）explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c4>‘a4’ and c3=‘a3’;

6）explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c4=‘a4’ order by c3;（c3的作用在于排序而不是查找，也算是用到了）

7）explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c3;

8）explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c4;

9）explain select * from test03 where c1=‘a1’ and c5=‘a5’ order by c2,c3;

9.1）explain select * from test03 where c1=‘a1’ and c5=‘a5’ order by c3,c2;

10）explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c2,c3;

11）explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c5=‘a5’ order by c2,c3;

12）explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c5=‘a5’ order by c3,c2;（查找已经用到了c2）

13）explain select * from test03 where c1=‘a1’ and c4=‘a4’ order by c2,c3;

一般性建议：

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的