排序这个词,我的第一感觉是几乎所有App都有排序的地方,淘宝商品有按照购买时间的排序、B站的评论有按照热度排序的...,当然我们今天说的并不是大数据下该如何优雅的排序,如何提升排序性能的问题,我们说一说MySQL中的排序。
对于MySQL,一说到排序,你第一时间想到的是什么?关键字order by?order by的字段最好有索引?叶子结点已经是顺序的?还是说尽量不要在MySQL内部排序?
现在假设有一张用户的朋友表:
CREATE TABLE `user` ( `id` int(10) AUTO_INCREMENT, `user_id` int(10), `friend_addr` varchar(1000), `friend_name` varchar(100), PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB;
表中目前有两个点需要关注下:
有一天,有个初级开发工程师小猿,收到了来自初级产品经理小汪的需求:
小汪 :小猿同志,现在需要在后台加个功能,这个功能要支持根据用户 id 能查到他所有的朋友姓名和地址,并且要求朋友的姓名是按照字典排序的。
小猿
:好的,这个功能简单,我马上就上线。
于是小猿书写了这样的sql:
select friend_name,friend_addr from user where user_id=? order by name
在电光石火的瞬间,小猿趾高气昂的上线了,这一切都很顺利,直到有一天有个运营同学导致了这样的查询:
select friend_name,friend_addr from user where user_id=10086 order by name
然而,这个查询竟然比平时慢很多,数据库报了慢查询,小猿此时慌的一b:这是怎么回事?user_id 明明有索引啊,而且机智地我还只用了 select friend_name,friend_addr,并没有用 select *呀。小猿此时不停地安慰自己,要淡定要淡定,然后突然想到有个explain命令,用explain来查看下那条sql的执行计划吧,当小猿用了explain之后,发现extra字段里面有个看起来很危险的字眼: using filesort 。
“这个查询竟然用到了传说中的文件排序,但是如果一个人朋友不是很多,就算了用了文件排序,应该也很快吧”,除非这个user_id=10086的朋友很多,后来小猿去查了下,这个用户的朋友竟然有10w多个~。
陷入了沉思的小猿心想:这个锅看来是背定了,10w数据是有点大了,还有这个 using filesort 到底是怎么个排序原理?
有人可能说上面的问题是10w数据太大了,就算不排序也慢,这个其实是有道理的,10w数据一次性查出来,无论是MySQL内存缓冲区的占用,还是网络带宽的消耗都是非常大的,那如果我加了limit 1000呢?网络带宽的问题肯定是解决了,因为数据包整体变小了,但是 using filesort 的问题其实还是没有解决,看到这里你可能会有疑问,using filesort 难道是在文件中排序的?在文件中到底是怎么排序的?或者我这样问:如果给你来设计排序你会怎么处理?带着这些疑问和思考我们来看看 using filesort 会涉及到哪些技术难点以及是如何解决的?
一切看起来很丝滑,但是 sort_buffer 占用的是内存空间,这就尴尬了,内存本身就不是无限大的,它肯定是有上限的,当然 sort_buffer 也不能太小,太小的话,意义不大。在 InnoDB 存储引擎中,这个值是默认是256K。
mysql> show variables like 'sort_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | sort_buffer_size | 262144 | +------------------+--------+
也就是说,如果要放进 sort_buffer 中的数据是大于256K的话,那么采用在 sort_buffer 中快排的方式肯定是行不通的,这时候,你可能会问:MySQL难道不能根据数据大小自动扩充吗?额,MySQL是多线程模型,如果每个线程都扩充,那么分给其他功能buffer就小了(比如change buffer等),就会影响其他功能的质量。
这时就得换种方式来排序了,没错,此时就是真正的文件排序了,也就是磁盘的临时文件,MySQL会采用归并排序的思想,把要排序的数据分成若干份,每一份数据在内存中排序后会放入临时文件中,最终对这些已经排序好的临时文件的数据再做一次合并排序就ok了,典型的分而治之原理,它的具体步骤如下:
通过上面的排序流程我们知道,如果要排序的数据很大,超过 sort_buffer 的大小,那么就需要文件排序,文件排序涉及到分批排序与合并,很耗时,造成这个问题的根本原因是 sort_buffer 不够用 ,不知道你发现没有我们的 friend_name 需要排序,但是却把 friend_addr 也塞进了 sort_buffer 中,这样 单行数据的大小就等于 friend_name 的长度 + friend_addr 的长度 ,能否让 sort_buffer 中只存 friend_name 字段,这样的话,整体的利用空间就大了,不一定用得到到临时文件。没错,这就是接下来要说的另一种排序优化 rowid排序 。
rowid 排序的思想就是把不需要的数据不要放到 sort_buffer 中,让 sort_buffer 中只保留必要的数据,那么你认为什么是必要的数据呢?只放 friend_name?这肯定不行,排序完了之后,friend_addr 怎么办?因此还要把主键id放进去,这样排完之后,通过 id 再回次表,拿到 friend_addr 即可,因此它的大致流程如下:
这里面其实有几点需要注意的:
那么问题来了,两种方式,MySQL 该如何选择?得根据某个条件来判断走哪种方式吧,这个条件就是进 sort_buffer 单行的长度,如果长度太大(friend_name + friend_addr的长度),就会采用 rowid 这种方式,否则第一种,长度的标准是根据 max_length_for_sort_data 来的,这个值默认是1024字节:
mysql> show variables like 'max_length_for_sort_data'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | max_length_for_sort_data | 1024 | +--------------------------+-------+
其实不管是上面哪种方法,他们都需要 回表 + 排序 ,回表是因为二级索引上没有目标字段,排序是因为数据不是有序的,那如果二级索引上有目标字段并且已经是排序好的了,那不就两全其美了嘛。
没错,就是联合索引,我们只需要建立一个 (user_id,friend_name,friend_addr)的联合索引即可,这样我就可以通过这个索引拿到目标数据,并且friend_name已经是排序好的,同时还有friend_addr字段,一招搞定,不需要回表,不需要再次排序。因此对于上述的sql,它的大致流程如下:
联合索引虽然可以解决这种问题,但是在实际应用中切不可盲目建立,要根据实际的业务逻辑来判断是否需要建立,如果不是经常有类似的查询,可以不用建立,因为联合索引会占用更多的存储空间和维护开销。