0、导读
在MySQL里,什么情况下会发生排序。MySQL内部的排序怎么完成的,怎么合理分配内存,怎么避免发生额外的磁盘I/O,这里都有解,看过来!
本文由沃趣科技数据库工程师罗小波撰写,二次排版时,个别文字老叶略有调增。
友情提醒:建议阅读时间20分钟,请先找好蹲坑。
一、本文想解决什么问题
二、如何识别需要排序
三、如何利用索引优化排序
四、排序的几种模式
4.1、实际trace结果
4.2、排序模式概览
4.2.1、回表排序模式
4.2.2、不回表排序模式
4.2.3、打包数据排序模式
4.2.4、三种模式比较
五、外部排序
5.1、普通外部排序
5.1.1、两路外部排序
5.1.2、多路外部排序
5.2、MySQL外部排序
5.2.1、MySQL外部排序算法
5.2.2、关于sort_merge_passes
六、optimizer trace解读
6.1、是否存在磁盘外部排序
6.2、是否存在优先队列优化排序
七、MySQL其他相关排序参数
7.1、max_sort_length
7.2、innodb_disable_sort_file_cache
7.3、innodb_sort_buffer_size
八、MySQL排序优化总结
九、参考文献
一、主要内容简介
MySQL排序是个老生长谈的话题,这次我们想由浅入深详细说说MySQL的几种排序模式,怎么选择不同排序模式,以及如何优化排序。
同时也希望通过本文能解决大家的几个疑问:
-
MySQL什么时候做排序,怎么判断需要进行排序;
-
MySQL有几种排序模式,有什么方法让MySQL选择不同的排序模式;
-
MySQL排序跟 read_rnd_buffer_size 有啥关系,在哪些情况下增加 read_rnd_buffer_size 能优化排序效率;
-
怎么判断MySQL使用了磁盘排序,怎么避免或者优化磁盘排序;
-
排序时变长字段(varchar)数据在内存是怎么存储的,5.7有哪些改进;
-
加了LIMIT后,排序模式有哪些改进;
-
sort_merge_pass到底是什么鬼,该状态值过大说明了什么问题,可以通过什么方法解决;
-
迫不得已要进行排序的话,有什么优化手段让排序更快;
二、如何识别需要排序?
利用EXPLAIN查看执行计划候时,如果在Extra列中显示Using filesort,其实这种情况就说明MySQL需要进行排序。
Using filesort经常出现在order by、group by、distinct、join等情况下。
三、利用索引优化排序
需要排序时,首先想到的一般是:能否利用索引来优化。
InnoDB默认采用的是B+tree索引,B+tree索引本身就是有序的,以下面的查询为例:
select * from film where actor_name='苍老师' order by prod_time;
只需创建多列索引(actor_name, prod_time),就能利用B+tree的特性来避免额外排序。
如下图所示:
通过B+tree查找到actor_name=’苍老师’的数据后,只需要按序往右继续扫描即可,无需额外排序操作。
下面都是其他可以利用索引优化排序的情形:
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2; SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC; SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC; SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;
从以上例子里面我们也可以看到,如果要让MySQL使用索引优化排序应该怎么建组合索引。
四、排序模式
4.1 实际trace结果
但是还是有非常多的SQL没法使用索引进行排序,例如
select * from film where Producer like '东京热%' and prod_time>'2015-12-01' order by actor_age;
我们想查询’东京热’出品的,从去年12月1号以来,并且按照演员的年龄排序的电影信息。
(好吧,假设我这里有一个每一位男DBA都想维护的数据库:)
这种情况下,使用索引已经无法避免排序了,那MySQL排序到底会怎么做列。
笼统的来说,它会按照:
-
依据“Producer like ‘东京热%’ and prod_time>’2015-12-01’ ”过滤数据,查找需要的数据;
-
对查找到的数据按照“order by actor_age”进行排序,并按照“select *”将必要的数据按照actor_age依序返回给客户端。
空口无凭,我们可以利用MySQL的optimize trace来查看是否如上所述。
如果通过optimize trace看到更详细的MySQL优化器trace信息,可以查看阿里印风的博客初识5.6的optimizer trace
trace结果如下:
-
依据“Producer like ‘东京热%’ and prod_time>’2015-12-01’ ”过滤数据,查找需要的数据
"attaching_conditions_to_tables": { "original_condition": "((`film`.`Producer` like '东京热%') and (`film`.`prod_time` > '2015-12-01'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`film`", "attached": "((`film`.`Producer` like '东京热%') and (`film`.`prod_time` > '2015-12-01'))" } ] }
-
对查找到的数据按照“order by actor_age”进行排序,并 按照“select *”将必要的数据按照actor_age依序返回给客户端
"join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`film`", "field": "actor_age" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "rows": 1, "examined_rows": 5, "number_of_tmp_files": 0, "sort_buffer_size": 261872, "sort_mode": "<sort_key, packed_additional_fields>" } } ] }
这里,我们可以明显看到,MySQL在执行这个select的时候执行了针对film表.actor_age字段的asc排序操作。