共计 4915 个字符，预计需要花费 13 分钟才能阅读完成。
创建 test 测试表
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` varchar(10) DEFAULT NULL,
`c2` varchar(10) DEFAULT NULL,
`c3` varchar(10) DEFAULT NULL,
`c4` varchar(10) DEFAULT NULL,
`c5` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_test_c1234` (`c1`,`c2`,`c3`,`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values(‘1′,’a1′,’a2′,’a3′,’a4′,’a5’);
insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values(‘2′,’b1′,’b2′,’b3′,’b4′,’b5’);
insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values(‘3′,’c1′,’c2′,’c3′,’c4′,’c5’);
insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values(‘4′,’d1′,’d2′,’d3′,’d4′,’d5’);
insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values(‘5′,’e1′,’e2′,’e3′,’e4′,’e5’);
分析以下 Case 索引使用情况
Case 1：
执行以下 SQL 语句：
① EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ AND c3=’a3′ AND c4=’a4′
② EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c3=’a3′ AND c2=’a2′ AND c4=’a4′
③ EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c4=’a4′ AND c3=’a3′ AND c2=’a2′
④ EXPLAIN SELECT * FROM test WHERE c4=’a4′ AND c2=’a2′ AND c3=’a3′ AND c1=’a1′
分析：创建联合索引的顺序为 c1,c2,c3,c4，上述四组 explain 执行结果都一样：type=ref，key_len=132，ref=const,const,const,const。
结论： 在执行常量等值查询时，改变索引列的顺序并不会更改 explain 的执行结果，因为 MySQL 底层优化器会自动进行优化 ，但还是推荐按照索引顺序列编写 SQL 语句。
Case 2：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ AND c3>’a3′ AND c4=’a4′
分析：当出现范围的时候，type=range，key_len=99，比不用范围 key_len=66 增加了，说明使用上了索引，但对比 Case 1 中的执行结果，说明 c4 上 s 索引失效。
结论： 范围右边索引列失效，但是范围当前位置（c3）的索引是有效的 ，从 key_len=99 可证明。
Case 2.1：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ AND c4>’c4′ AND c3=’a3′
分析：与上面 explain 执行结果对比，key_len=132 说明索引用到了 4 个，因此对此 SQL 语句 MySQL 底层优化器会进行优化（优化成 WHERE c1=’a1′ AND c2=’a2′ AND c3=’a3′ AND c4>’c4’）：范围右边索引列失效（c4 右边已经没有索引列了），注意索引的顺序（c1,c2,c3,c4），所以 c4 右边不会出现失效的索引列，因此 4 个索引全部用上。
结论： 范围右边索引列失效，是有顺序的 ：c1,c2,c3,c4，如果 c3 有范围，则 c4 失效；如果 c4 有范围，则没有失效的索引列，从而会使用全部索引。
Case 2.2：（声明：这个 Case 的解释有待考察）
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1>’a1′ AND c2=’a2′ AND c3=’c3′ AND c4=’a4′
分析：如果在 c1 处使用范围，则 type=ALL，key=NULL，索引失效，全表扫描，这里违背了最佳左前缀原则，带头大哥已死，因为 c1 主要用于范围，而不是查询。
解决方式：使用覆盖索引。
结论：在索引最佳左前缀原则中，如果最左前列（带头大哥）的索引失效，则后面的索引失效。
Case 3：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ AND c4=’a4′ ORDER BY c3
分析：利用最佳左前缀原则：中间兄弟不能断，因此用到了 c1 和 c2 索引（查找），从 key_len=66，ref=const,const 可以看出来，c3 索引列也用在 order by 排序过程中（即也用到了 c3 索引）。
提问 ：如何证明 order by c3 也用到了索引？
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ AND c4=’a4′ ORDER BY c5
分析：因为 c5 非索引字段，当用 order by c5 排序时，extra 列出现了 Using filesort，用到了文件排序，代表没有使用索引排序，性能低。
Case 3.1：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ ORDER BY c3
分析：从 key_len=66，ref=const,const 可以看出来，查找只用到了 c1 和 c2 索引，c3 索引用于排序。
Case 3.2：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ ORDER BY c4
分析：从 key_len=66，ref=const,const 可以看出来，查询使用了 c1 和 c2 索引，由于使用了 c4 进行排序，跳过了 c3，中间断了，也无法使用 c4 的索引进行排序，出现了 Using filesort。
Case 4：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c5=’a5′ ORDER BY c2,c3
分析：查找只用到索引 c1，c2 和 c3 索引用于排序，无 Using filesort。
Case 4.1：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c5=’a5′ ORDER BY c3,c2
分析：和 Case 4 中 explain 的执行结果一样，但是出现了 Using filesort，因为索引的创建顺序为 c1,c2,c3,c4，但是排序的时候 c2 和 c3 颠倒位置了。
Case 4.2：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ ORDER BY c2,c3
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ AND c5=’a5′ ORDER BY c2,c3
分析：在查询时增加了 c5，但是 explain 的执行结果一样，因为 c5 并未创建索引。
Case 4.3：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c2=’a2′ AND c5=’a5′ ORDER BY c3,c2
分析：与 Case 4.1 相比，在 Extra 中并未出现 Using filesort，因为 c2 是常量，在排序中被优化，所以索引未颠倒，不会出现 Using filesort。
Case 5：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c4=’a4′ GROUP BY c2,c3
分析： 如果 gourp by 没有使用索引，会导致生成临时表（Using temporary），底层会先用 order by 排序，要想 group by 使用索引分组，前提条件是满足 order by 使用索引排序 。上面只用到 c1 上的索引进行查询，因为 c4 中间断了，根据索引最左前缀原则，索引 key_len=33，ref=const，表示只用到一个索引。
Case 5.1：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1=’a1′ AND c4=’a4′ GROUP BY c3,c2
分析：对比 Case 5，在 group by 时交换了 c2 和 c3 的位置，导致无法满足 order by（Using filesort），即无法满足 group by（Using temporary），极度恶劣。原因：c3 和 c2 与索引创建顺序相反。
Case 6：
执行 SQL 语句：EXPLAIN SELECT * FROM test WHERE c1>’a1′ ORDER BY c1
分析：
① 在 c1,c2,c3,c4 上创建了索引，直接在 c1 上使用范围，导致了索引失效，全表扫描：type=ALL，ref=NULL。因为此时 c1 主要用于排序，并不是查询。
② 使用 c1 进行排序，出现了 Using filesort。
③ 解决方法：使用覆盖索引。
执行 SQL 语句：EXPLAIN SELECT c1 FROM test WHERE c1>’a1′ ORDER BY c1
Case 7：
执行 SQL 语句：
EXPLAIN SELECT c1 FROM test ORDER BY c1 ASC, c2 DESC
分析：虽然排序的字段列与索引顺序一样，且 order by 默认升序，这里 c2 DESC 变成了降序，导致与索引的排序方式不同，从而产生 Using filesort。
Case 8：
执行 SQL 语句：EXPLAIN SELECT c1 FROM test WHERE c1 IN(‘a1′,’b1’) ORDER BY c2,c3
分析：对于排序来说，多个相等条件也是范围查询。
总结 ：
① MySQL 支持两种方式的排序 filesort 和 index，Using index 是指 MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。
② order by 满足两种情况会使用 Using index：
a. order by 语句使用索引最左前列。
b. 使用 where 子句与 order by 子句条件列组合满足索引最左前列。
③ 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀原则。
④ 如果 order by 的条件不在索引列上，就会产生 Using filesort。
⑤ group by 与 order by 很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀原则。注意 where 高于 having，能写在 where 中的限定条件就不要去 having 限定了。
通俗理解口诀 ：
全值匹配我最爱，最左前缀我的菜；
带头大哥不能死，中间兄弟断狗带；
索引列上少计算，范围之后全完蛋；
覆盖索引不写星，Like 百分右边站；
不等空值还有 or，索引失效要少用。
补充：in 和 exists 优化
原则： 小表驱动大表 ，即小的数据集驱动大的数据集。
in：当 B 表的数据集小于 A 表的数据集时，in 优于 exists
select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id=B.id
exists：当 A 表的数据集小于 B 表的数据集时，exists 优于 in
select * from A where exists (select 1 from B where B.id=A.id)
等价于：
for select * from A
for select * from B where B.id=A.id
（A 表与 B 表的 id 字段应建立索引）
：
