索引面试题分析

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

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) 编辑收藏举报

刷新页面返回顶部

cyhbyw

索引面试题分析

公告