索引面试题分析
===============
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 相同
