這篇文章為大家帶來了關於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 欄位
一切看起来很丝滑，但是 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的数据，结束。
联合索引虽然可以解决这种问题，但是在实际应用中切不可盲目建立，要根据实际的业务逻辑来判断是否需要建立，如果不是经常有类似的查询，可以不用建立，因为联合索引会占用更多的存储空间和维护开销。
对于 order by 没有用到索引的时候，这时 explain 中 Extra 字段大概是会出现 using filesort 字眼
出现 using filesort 的时候也不用太慌张，如果本身数据量不大，比如也就几十条数据，那么在 sort buffer 中使用快排也是很快的
如果数据量很大，超过了 sort buffer 的大小，那么是要进行临时文件排序的，也就是归并排序，这部分是由 MySQL 优化器决定的
如果查询的字段很多，想要尽量避免使用临时文件排序，可以尝试设置下 max_length_for_sort_data 字段的大小，让其小于所有查询字段长度的总和，这样放入或许可以避免，但是会多一次回表操作
实际业务中，我们也可以给经常要查询的字段组合建立个联合索引，这样既不用回表也不需要单独排序，但是联合索引会占用更多的存储和开销
大量数据查询的时候，尽量分批次，提前 explain 来观察 sql 的执行计划是个不错的选择。
推荐学习：mysql视频教程
以上是你真的了解MySQL的order by嗎的詳細內容。更多資訊請關注PHP中文網其他相關文章！
