MySQL索引优化深入「建议收藏」

创建 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字段应建立索引）