MySQL索引如何进行优化

62次阅读
没有评论

共计 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 行业资讯频道,更多相关知识等着你来学习!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-15发表,共计4454字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)