索引面试题分析

 

Mysql 系列文章主页 

 

===============

 

1 准备数据

1.1 建表

DROP TABLE IF EXISTS test03;
CREATE TABLE test03 (
    id INT PRIMARY KEY auto_increment,
    c1 char(10),
    c2 char(10),
    c3 char(10),
    c4 char(10),
    c5 char(10)
);

1.2 插入数据

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');

2 测试&Explain分析

2.1 建立索引

CREATE INDEX idx_c1234 ON test03(c1, c2, c3, c4);

2.2 测试

Case#1:用到一个索引(注意:key_len=31)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1';

Case#2:用到两个索引(注意:key_len=62)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2';

Case#3:用到三个索引(注意:key_len=93)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c3 = 'a3';

Case#4:用到四个索引(注意:key_len=124)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c3 = 'a3' AND c4 = 'a4';

Case#5:用到四个索引(注意:这里的查询条件顺序是 1 2 4 3 而不是 1 2 3 4)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 = 'a4' AND c3 = 'a3';

Case#6:用到四个索引(这里的顺序是 4 3 2 1)

EXPLAIN SELECT * FROM test03 WHERE c4 = 'a4' AND c3 = 'a3' AND c2 = 'a2' AND c1 = 'a1';

Case#7:用到了三个索引(注意:第三个查询条件是大于)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c3 > 'a3' AND c4 = 'a4';

结果:

  • key_len=93
  • type=range
  • ref=Null
  • 由以上三点可推知:使用了三个索引(使用到了 1 2 3 三个索引;由于范围之后全失效,所以第四个索引没有被使用到)

Case#8:用到了四个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 > 'a4' AND c3 = 'a3';

结果:

  • key_len=124
  • type=range
  • ref=Null
  • 使用到了四个索引

分析:Mysql很聪明,查询优化器会分析,得出 1 2 3 是常量,4 是范围查询的结论,于是,四个索引都能被使用,且 type=range。

Case#9:用到了 1 2 两个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' ORDER BY c3;

说明:索引有两个功能(查找和排序),所以这里的 c3 实际上还是使用上了索引的,只是它的用处是在排序(而不是查找;对比 Case#11 的结果更容易理解)。

Case#10:用到了 1 2 两个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 = 'a4' ORDER BY c3;

Case#11:用到了 1 2 两个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' ORDER BY c4;

结果:key_len=62 说明确实用到了两个索引,但是,由于建立的索引是 1 2 3 4 而此Sql是查询 1 2 然后按照 4 来排序(中间断掉了 3),于是出现了 Using filesort!对比 Case#9 的结果,它的Sql是查询 1 2 然后按照 3 来排序,所以没有出现 Using filesort。

Case#12:用到了一个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c5 = 'a5' ORDER BY c2, c3;

说明:这里只用到了 c1 一个索引,同时 c2 c3 用于排序,没有出现 Using filesort(可以和 Case#11 进行对比)。

Case#13:用到了一个索引(注意:ORDER BY 后面是 c3 在前,c2 在后)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c5 = 'a5' ORDER BY c3, c2;

说明:用到了 c1 一个索引,但是之后的ORDER BY 是 c3 在前,c2 在后,这就相当于是直接 ORDER BY c3,于是,中间 c2 断掉了,出现 Using filesort!此 Case 和 Case#11 效果差不多,都是索引中断了一个中间兄弟。

Case#14:用到了两个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' ORDER BY c2, c3;

说明:查询字段是 1 2 而排序字段是 2 3,是连续的。

Case#15:用到了两个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c5 = 'a5' ORDER BY c2, c3;

说明:比 Case#14 多了一个 c5 的查询字段,但是对结果无影响。

Case#16:用到了两个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c5 = 'a5' ORDER BY c3, c2;

说明:ORDER BY c3, c2 居然没有出现 Using filesort!

原因是:在 WHERE 中,c2 = 'a2' 已经是个常量了,所以 ORDER BY c3, c2 就直接等价于(简化为)ORDER BY c3,故不会出现 Using filesort。

可以和 Case#13 进行对比,此例和 Case#13 的区别就是在于,多了一个 c2 = 'a2',这就把 c1 c2 c3 给连续起来了,没有断掉,故不会出现 Using filesort。

Case#17:用到了一个索引(注意:此例是 GROUP BY)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' GROUP BY c2, c3;

说明:分组必排序,所以 GROUP BY c2, c3 等价于 ORDER BY c2, c3,同时,WHERE 条件中有 c1,所以索引连续,能够正常被使用上。

Case#18:用到了一个索引(注意:GROUP BY 中的 c3 在前,c2 在后)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' GROUP BY c3, c2;

说明:

  • 能够用到 c1 这个索引
  • GROUP BY c3, c2 等价于 ORDER BY c3, c2,而之前只有 c1 于是索引断掉了,所以出现 Using filesort(九死一生)
  • GROUP BY 还需要对结果进行分组,所以产生了 Using temporary(十死无生)

Case#19:用到了两个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 > 'a2' AND c3 = 'a3';

结果:用到了两个索引,且type=range

Case#20:用到了三个索引(注意:中间的 c2 是 LIKE 查询,且%在右边)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 LIKE 'a2%' AND c3 = 'a3';

结果:和 Case#19 不同的是,此例用到了三个索引,且 type=range,有点难以理解,,,,,,,,,,,,待以后再来补充吧,,TODO

Case#21:用到了一个索引(注意:中间的 c2 是 LIKE 查询,且%在左边)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 LIKE '%a2' AND c3 = 'a3';

说明:type=ref 且 key_len=31,只用到了一个索引。也能说明,Like 以 % 开头时无法使用索引。

Case#22:用到了一个索引(注意:中间的 c2 是 LIKE 查询,且%在两边)

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 LIKE '%a2%' AND c3 = 'a3';

说明:和 Case#21 相同,也只用到了一个索引。

Case#23:用到了三个索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 LIKE 'a2%a2%' AND c3 = 'a3';

说明:结果和 Case#20 相同

 

posted @ 2018-04-24 15:12  cyhbyw  阅读(2630)  评论(2编辑  收藏  举报