共计 4454 个字符,预计需要花费 12 分钟才能阅读完成。
这篇文章主要介绍了 MySQL 索引如何进行优化,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让丸趣 TV 小编带着大家一起了解一下。
创建 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 限定了。
感谢你能够认真阅读完这篇文章,希望丸趣 TV 小编分享的“MySQL 索引如何进行优化”这篇文章对大家有帮助,同时也希望大家多多支持丸趣 TV,关注丸趣 TV 行业资讯频道,更多相关知识等着你来学习!