MySQL高级——索引面试题案例分析

一、建表语句

1、建表语句

CREATE TABLE `test` (
  `id` INT(11)  PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `a1` CHAR(10) DEFAULT NULL,
  `a2` CHAR(10) DEFAULT NULL,
  `a3` CHAR(10) DEFAULT NULL,
  `a4` CHAR(10) DEFAULT NULL,
  `a5` CHAR(10) DEFAULT NULL
);

insert into test(a1,a2,a3,a4,a5) values('a1','a2','a3','a4','a5');
insert into test(a1,a2,a3,a4,a5) values('b1','b2','b3','b4','b5');
insert into test(a1,a2,a3,a4,a5) values('c1','c2','c3','c4','c5');
insert into test(a1,a2,a3,a4,a5) values('d1','d2','d3','d4','d5');
insert into test(a1,a2,a3,a4,a5) values('e1','e2','e3','e4','e5');

2、创建复合索引语句

ALTER TABLE test ADD INDEX idx_test_a1a2a3a4(a1,a2,a3,a4);

3、查看创建的索引语句

二、示例一

问题：创建了复合索引 idx_test_a1a2a3a4,根据以下SQL分析索引使用情况。

1、 explain select * from test where a1 = ‘b1’;

• 结论：key不为null，说明实际使用到了索引；ref为const 说明使用了一个常量；key_len的长度为31。

2、explain select * from test where a1 = ‘b1’ and a2 =‘b2’;

• 结论：key不为null，说明实际使用到了索引；ref为const,const 说明使用了2个常量；key_len的长度为62。

3、explain select * from test where a1 = ‘b1’ and a2 =‘b2’ and a3 =‘b3’;

• 结论：key不为null，说明实际使用到了索引；ref为const,const ,const 说明使用了3个常量；key_len的长度为93。

4、explain select * from test where a1 = ‘b1’ and a2 =‘b2’ and a3 =‘b3’ and a4 =‘b4’;

• 结论：key不为null，说明实际使用到了索引；ref为const,const,const,const 说明使用了4个常量；key_len的长度为124。

5、explain select * from test where a1 = ‘b1’ and a2 =‘b2’ and a4 =‘b4’ and a3 =‘b3’;

• 结论：key不为null，说明实际使用到了索引；ref为const,const,const,const 说明使用了4个常量；key_len的长度为124。

6、explain select * from test where a4 =‘b4’ and a3 =‘b3’ and a2 =‘b2’ and a1 = ‘b1’ ;

• 结论：key不为null，说明实际使用到了索引；ref为const,const,const,const 说明使用了4个常量；key_len的长度为124。
• 说明：复合索引创建的顺序，最好查询时根据复合索引创建的顺序一致，避免mysql底层多进行一次转换。

二、示例二

问题：创建了复合索引 idx_test_a1a2a3a4,根据以下SQL分析索引使用情况。

1、explain select * from test where a1 = ‘b1’ and a2 =‘b2’ and a3 >‘b3’ and a4 =‘b4’;

• 结论：type为range，说明是一个范围；key不为null，说明实际使用到了索引；key_len的长度为124；使用到了a1,a2,a3三个索引，因为范围之后索引失效。

3、explain select * from test where a1 = ‘b1’ and a2 =‘b2’ and a4 >‘b4’ and a3 =‘b3’;

• 结论：type为range，说明是一个范围；key不为null，说明实际使用到了索引；key_len的长度为124；使用到了a1,a2,a3,a4四个索引，因为mysql底层会进行优化，把a3 ='b3’会调整到 a4 >'b4’前面，所以a4 >'b4’范围后的才会失效，因此使用了4个索引。

4、explain select * from test where a1 = ‘b1’ and a2 =‘b2’ and a4 = ‘b4’ order by a3;

• 结论：type为range，说明是一个范围；key不为null，说明实际使用到了索引；key_len的长度为62；red为const,const 2个常量，因此使用到了a1,a2两个索引，因为与创建索引的顺序a3 断掉了。
• 注：索引的两大作用：查询和排序。所以a3也会用到，主要用于排序而不是查找，只是没有统计在上图的查找里。

5、explain select * from test where a1 = ‘b1’ and a2 =‘b2’ order by a3;

• 结论：type为range，说明是一个范围；key不为null，说明实际使用到了索引；key_len的长度为62；red为const,const 2个常量，因此使用到了a1,a2两个索引。
• 注：索引的两大作用：查询和排序。所以a3也会用到，主要用于排序而不是查找，只是没有统计在上图的查找里。

6、explain select * from test where a1 = ‘b1’ and a2 =‘b2’ order by a4;

• 结论：key不为null，说明实际使用到了索引；key_len的长度为62；red为const,const 2个常量，因此使用到了a1,a2两个索引；Extra为 Using filesort ，说明产生了内排序，导致性能下降。

三、示例三

问题：创建了复合索引 idx_test_a1a2a3a4,根据以下SQL分析索引使用情况。

1、 EXPLAIN SELECT * FROM test WHERE a1 = ‘b1’ AND a5 =‘b5’ ORDER BY a2,a3;

• 结论：key不为null，说明实际使用到了索引；red为const 使用了1个常量，因此使用到了a1一个查询索引，但是a2,a3索引用于排序；

2、EXPLAIN SELECT * FROM test WHERE a1 = ‘b1’ AND a5 =‘b5’ ORDER BY a3,a2;

• 结论：key不为null，说明实际使用到了索引；red为const 使用了1个常量，因此使用到了a1一个查询索引，但是a3,a2索引用于排序；Extra为 Using filesort ，说明产生了内排序，导致性能下降，我们创建的索引顺序是1,a2,a3,a4,order by的时候没有按照创建索引的顺序，索引产生了文件内排序。

3、EXPLAIN SELECT * FROM test WHERE a1 = ‘b1’ AND a2 =‘b2’ ORDER BY a2,a3;

• 结论：key不为null，说明实际使用到了索引；red为const,const 使用了2个常量，因此使用到了a1,a2两个查询索引，a2,a3索引用于排序；

4、EXPLAIN SELECT * FROM test WHERE a1 = ‘b1’ AND a2 =‘b2’ AND a5 =‘b5’ ORDER BY a2,a3;

• 结论：key不为null，说明实际使用到了索引；red为const,const 使用了2个常量，因此使用到了a1,a2两个查询索引，a2,a3索引用于排序；

5、EXPLAIN SELECT * FROM test WHERE a1 = ‘b1’ AND a2 =‘b2’ AND a5 =‘b5’ ORDER BY a3,a2;

• 结论：key不为null，说明实际使用到了索引；red为const,const 使用了2个常量，因此使用到了a1,a2两个查询索引，a3,a2索引用于排序；Extra为 Using where，没有文件内排序，因为order by a3,a2时，条件查询中a2='b2’是一个常量，一个固定值，因此通过a2排序时没有产生文件内排序。

四、示例四

问题：创建了复合索引 idx_test_a1a2a3a4,根据以下SQL分析索引使用情况。

1、EXPLAIN SELECT * FROM test WHERE a1 = ‘b1’ AND a4 =‘b4’ GROUP BY a2,a3;

• 结论：type为ref，没有产生全表扫描；key不为null，说明实际使用到了索引；red为const使用了1个常量，因此使用到了a1一个查询索引。

2、EXPLAIN SELECT * FROM test WHERE a1 = ‘b1’ AND a4 =‘b4’ GROUP BY a3,a2;

• 结论：type为ref，没有产生全表扫描；key不为null，说明实际使用到了索引；red为const使用了1个常量，因此使用到了a1一个查询索引。Extra为 Using temporary; Using filesort，说明使了用临时表保存中间结果和文件内排序。
• goup by表面上叫分组，实际是分组之前比排序。

五、示例结论

• 索引分析时： 定值为常量，范围之后是失效，最终看排序，一般order by 是给定范围。
• 索引分析时：group by 基本上都需要进行排序，会有临时表产生。