你真的了解MySQL的order by吗

本篇文章给大家带来了关于mysql中order by排序的相关知识，希望对大家有帮助。

排序这个词，我的第一感觉是几乎所有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;

表中目前有两个点需要关注下：

用户的 user_id ，朋友的姓名 friend_name、朋友的地址 friend_addr

user_id 是有索引的

有一天，有个初级开发工程师小猿，收到了来自初级产品经理小汪的需求：

小汪：小猿同志，现在需要在后台加个功能，这个功能要支持根据用户 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 会涉及到哪些技术难点以及是如何解决的？

• 首先我们的 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 中进行排序，排序好后，写入某个临时文件中

• 当所有的数据都写入临时文件后，这时对于每个临时文件而言，内部都是有序的，但是它们并不是一个整体，整体还不是有序的，所以接下来就得合并数据了

  一览妙笔

  自媒体、编剧、营销人员写作工具

  50 查看详情

• 假设现在存在 tmpX 和 tmpY 两个临时文件，这时会从 tmpX 读取一部分数据进入内存，然后从 tmpY 中读取一部分数据进入内存，这里你可能会好奇为什么是一部分而不是整个或者单个？因为首先磁盘是缓慢的，所以尽量每次多读点数据进入内存，但是不能读太多，因为还有 buffer 空间的限制。

• 对于 tmpX 假设读进来了的是 tmpX[0-5] ,对于 tmpY 假设读进来了的是 tmpY[0-5]，于是只需要这样比较：

如果 tmpX[0] 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的数据，结束。

联合索引虽然可以解决这种问题，但是在实际应用中切不可盲目建立，要根据实际的业务逻辑来判断是否需要建立，如果不是经常有类似的查询，可以不用建立，因为联合索引会占用更多的存储空间和维护开销。

总结

• 对于 order by 没有用到索引的时候，这时 explain 中 Extra 字段大概是会出现 using filesort 字眼

• 出现 using filesort 的时候也不用太慌张，如果本身数据量不大，比如也就几十条数据，那么在 sort buffer 中使用快排也是很快的

• 如果数据量很大，超过了 sort buffer 的大小，那么是要进行临时文件排序的，也就是归并排序，这部分是由 MySQL 优化器决定的

• 如果查询的字段很多，想要尽量避免使用临时文件排序，可以尝试设置下 max_length_for_sort_data 字段的大小，让其小于所有查询字段长度的总和，这样放入或许可以避免，但是会多一次回表操作

• 实际业务中，我们也可以给经常要查询的字段组合建立个联合索引，这样既不用回表也不需要单独排序，但是联合索引会占用更多的存储和开销

• 大量数据查询的时候，尽量分批次，提前 explain 来观察 sql 的执行计划是个不错的选择。

推荐学习：mysql视频教程

以上就是你真的了解MySQL的order by吗的详细内容，更多请关注其它相关文章！

# 都是  # 长春seo教程案例公司  # 新推广渠道营销策划案例  # 网站推广关键词推荐乐云seo  # 淄博网站建设的概述  # 河源当地的免费网站优化  # 黄石外包网站推广哪个好  # 岳塘区海外营销推广项目  # 天门包年网站推广怎么做  # 广州seo软件靠谱乐云seo  # 沈阳网站seo排名公司网站推广  # mysql  # 就得  # 你真  # 不需要  # 多个  # 是有  # 有个  # 镜像  # 临时文件  # 的是 

相关栏目： 【 Google疑问12 】 【 Facebook疑问10 】 【 优化推广96088 】 【 技术知识133117 】 【 IDC资讯59369 】 【 网络运营7196 】 【 IT资讯61894 】

相关推荐： 《领英》查看屏蔽名单方法  CodeIgniter 3 中基于 MySQL 数据高效生成动态图表教程  iPhone12是否要更新ios16  c++中的const关键字用法大全_c++ const正确使用指南  J*aScript深度克隆：实现高效、健壮与安全的复杂对象复制  TikTok网页版入口快速访问 TikTok官网账号登录方法  酷狗音乐多音轨设置教程  C++中std::thread和std::async的区别_C++并发编程与线程与异步任务比较  《宝可梦大集结》S4冠军之路开始时间介绍  邮政快递寄件查询入口 邮政快递收件查询入口  在React中正确处理HTML input type="number"的数值类型  edge浏览器怎么修改语言为中文_Edge界面语言切换教程  163邮箱在线登录 163邮箱网页版在线入口  智云Q3和Q2有什么升级_智云Q3与Q2手持云台功能与性能对比分析  房产|直播|视频号怎么认证开通？|直播|需要什么资质？  如何在Golang中处理表单文件上传_Golang 表单文件上传示例  C++ virtual析构函数作用_C++基类虚析构函数防止内存泄漏  win11资源管理器标签页怎么用 Win11文件管理器多标签高效操作【新功能】  米侠浏览器插件无法启用怎么办 米侠浏览器扩展兼容性修复  解决Flex容器横向滚动内容截断与偏移问题  《猎聘》筛选猎头岗位方法  《画加》约稿流程  Keras中Convolution2D层及其核心辅助层详解  c++如何实现观察者设计模式_c++行为型设计模式实战  《sketchbook》选中部分图案移动方法  《搜书吧》阅读书籍方法  三星M34录音变声问题_Samsung M34麦克风调整  《王者荣耀世界》英雄获取攻略  Golang如何初始化module项目_Golang module init使用说明  微博网页版访问入口 微博网页版网页端使用指南  Python项目中的条件导入：解决跨模块依赖问题  西瓜视频怎么查看访客记录_西瓜视频访客记录查看方法  如何自定义苹果手机铃声  晨报|开发商暗示《空洞骑士：丝之歌》DLC开发中 《合金装备4》有望重制  mysql数据库索引类型有哪些_mysql索引类型解析  教资成绩怎么查询  VS Code如何设置默认配置  PHP魔术方法__set与__isset：设计考量、性能权衡与静态分析的视角  Symfony路由参数转换器：实体存在性验证与错误处理策略  智学网成绩单查询系统网_智学网学生平台登录  vivo手机视频通话美颜怎么设置_vivo视频通话美颜开启方法  掌握CSS :has() 选择器：父选择器、嵌套限制与常见陷阱解析  使用Python和GBGB API高效抓取指定日期范围和赛道比赛结果教程  192.168.1.1路由器后台入口 192.168.1.1默认登录入口  可米酷漫画在线阅读入口_ 可米酷漫画官网直达链接  《新三国志曹操传》游历事件袁尚突围攻略  Yandex浏览器官方入口_Yandex搜索引擎中文版  C++如何将字符串转换为大写或小写_C++ transform函数的使用技巧  j*a中ArrayBlockingQueue的使用  Dagster资产间数据传递与用户配置管理教程 

 2022-02-01