在电光石火的瞬间,小猿趾高气昂的上线了,这一切都很顺利,直到有一天有个运营同学导致了这样的查询:
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 会涉及到哪些技术难点以及是如何解决的?
-
首先我们的 user_id 是有索引的,所以会先在 user_id 索引树上检索我们的目标数据,即 user_id=10086 的数据,但是我们要查询的是 friend_name 和 friend_addr 字段,很不幸,光靠 user_id 索引是找不到这两个字段值的
-
于是需要回表,通过 user_id 对应的主键去主键索引树上去查找,ok,我们找到了第一条 user_id=10086 的 friend_name 和 friend_addr 字段
-
这时该怎么办?直接返回回去肯定不对,因为我需要对 friend_name 排序,如何排?数据都还没找全,那么就得把查到的数据先放在一个地方,这个地方就是 sort_buffer ,看到名字我想你应该猜出来,没错,sort_buffer 就是用于这种情况下排序用的缓冲区,这里需要注意的是每个线程都会有一个单独的 sort_buffer,这么做的目的主要是为了避免多个线程对同一块内存进行操作带来锁竞争的问题。
-
当第一条数据的 friend_name 和 friend_addr 已经放入 sort_buffer 中,这当然没完,会一直重复同步的步骤,直至把所有 user_id=10086 的 friend_name 和 friend_addr 都放入到 sort_buffer 中才结束
-
sort_buffer 中的数据已经放入完毕,接下来就该排序了,这里 MySQL 会对 friend_name 进行快排,通过快排后,sort_buffer 中 friend_name 就是有序的了
-
最后返回 sort_buffer 中的前1000条,结束。
一切看起来很丝滑,但是 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 中进行排序,排序好后,写入某个临时文件中
-
当所有的数据都写入临时文件后,这时对于每个临时文件而言,内部都是有序的,但是它们并不是一个整体,整体还不是有序的,所以接下来就得合并数据了
-
假设现在存在 tmpX 和 tmpY 两个临时文件,这时会从 tmpX 读取一部分数据进入内存,然后从 tmpY 中读取一部分数据进入内存,这里你可能会好奇为什么是一部分而不是整个或者单个?因为首先磁盘是缓慢的,所以尽量每次多读点数据进入内存,但是不能读太多,因为还有 buffer 空间的限制。
-
对于 tmpX 假设读进来了的是 tmpX[0-5] ,对于 tmpY 假设读进来了的是 tmpY[0-5],于是只需要这样比较:如果 tmpX[0] < tmpY[0],那么 tmpX[0] 肯定是最小的,然后 tmpX[1] 和 tmpY[0] 比如,如果 tmpX[1] > tmpY[0],那么 tmpY[0] 肯定是第二小的…,就这样两两比较最终就可以把 tmpX 和 tmpY 合并成一个有序的文件tmpZ,多个这样的tmpZ再次合并…,最终就可以把所有的数据合并成一个有序的大文件。
文件排序很慢,还有其他办法吗
==============
通过上面的排序流程我们知道,如果要排序的数据很大,超过 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 即可,因此它的大致流程如下:
-
根据 user_id 索引,查到目标数据,然后回表,只把 id 和 friend_name 放进 sort_buffer 中
-
重复1步骤,直至全部的目标数据都在 sort_buffer 中
-
对 sort_buffer 中的数据按照 friend_name 字段进行排序
-
排序后根据 id 再次回表查到 friend_addr 返回,直至返回1000条数据,结束。
这里面其实有几点需要注意的:
-
这种方式需要两次回表的
-
sort_buffer 虽然小了,但是如果数据量本身还是很大,应该还是要临时文件排序的
那么问题来了,两种方式,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,它的大致流程如下:
-
通过联合索引找到user_id=10086的数据,然后读取对应的 friend_name 和 friend_addr 字段直接返回,因为 friend_name 已经是排序好的了,不需要额外处理
-
重复第一步骤,顺着叶子节点接着向后找,直至找到第一个不是10086的数据,结束。
联合索引虽然可以解决这种问题,但是在实际应用中切不可盲目建立,要根据实际的业务逻辑来判断是否需要建立,如果不是经常有类似的查询,可以不用建立,因为联合索引会占用更多的存储空间和维护开销。
总结
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)
言尽于此,完结
无论是一个初级的 coder,高级的程序员,还是顶级的系统架构师,应该都有深刻的领会到设计模式的重要性。
- 第一,设计模式能让专业人之间交流方便,如下:
程序员A:这里我用了XXX设计模式
程序员B:那我大致了解你程序的设计思路了
- 第二,易维护
项目经理:今天客户有这样一个需求…
程序员:明白了,这里我使用了XXX设计模式,所以改起来很快
- 第三,设计模式是编程经验的总结
程序员A:B,你怎么想到要这样去构建你的代码
程序员B:在我学习了XXX设计模式之后,好像自然而然就感觉这样写能避免一些问题
- 第四,学习设计模式并不是必须的
程序员A:B,你这段代码使用的是XXX设计模式对吗?
程序员B:不好意思,我没有学习过设计模式,但是我的经验告诉我是这样写的
从设计思想解读开源框架,一步一步到Spring、Spring5、SpringMVC、MyBatis等源码解读,我都已收集整理全套,篇幅有限,这块只是详细的解说了23种设计模式,整理的文件如下图一览无余!
搜集费时费力,能看到此处的都是真爱!
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!
VC、MyBatis等源码解读,我都已收集整理全套,篇幅有限,这块只是详细的解说了23种设计模式,整理的文件如下图一览无余!
[外链图片转存中…(img-ODDHOkj9-1713520588253)]
搜集费时费力,能看到此处的都是真爱!
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!