MySQL數據庫索引order by排序精講

Posted on by

排序這個詞，我的第一感覺是幾乎所有App都有排序的地方，淘寶商品有按照購買時間的排序、B站的評論有按照熱度排序的…

對於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 會涉及到哪些技術難點以及是如何解決的？

  1. 首先我們的 user_id 是有索引的，所以會先在 user_id 索引樹上檢索我們的目標數據，即 user_id=10086 的數據，但是我們要查詢的是 friend_name 和 friend_addr 字段，很不幸，光靠 user_id 索引是找不到這兩個字段值的
  2. 於是需要回表，通過 user_id 對應的主鍵去主鍵索引樹上去查找，ok，我們找到瞭第一條 user_id=10086 的 friend_name 和 friend_addr 字段
  3. 這時該怎麼辦？直接返回回去肯定不對，因為我需要對 friend_name 排序，如何排？數據都還沒找全，那麼就得把查到的數據先放在一個地方，這個地方就是 sort_buffer，看到名字我想你應該猜出來，沒錯，sort_buffer 就是用於這種情況下排序用的緩沖區，這裡需要註意的是每個線程都會有一個單獨的 sort_buffer，這麼做的目的主要是為瞭避免多個線程對同一塊內存進行操作帶來鎖競爭的問題。
  4. 當第一條數據的 friend_name 和 friend_addr 已經放入 sort_buffer 中，這當然沒完，會一直重復同步的步驟，直至把所有 user_id=10086 的 friend_name 和 friend_addr 都放入到 sort_buffer 中才結束
  5. sort_buffer 中的數據已經放入完畢，接下來就該排序瞭，這裡 MySQL 會對 friend_name 進行快排，通過快排後，sort_buffer 中 friend_name 就是有序的瞭
  6. 最後返回 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瞭，典型的分而治之原理，它的具體步驟如下：

  1. 先將要排序的數據分割，分割成每塊數據都可以放到 sort_buffer 中
  2. 對每塊數據在 sort_buffer 中進行排序，排序好後，寫入某個臨時文件中
  3. 當所有的數據都寫入臨時文件後，這時對於每個臨時文件而言，內部都是有序的，但是它們並不是一個整體，整體還不是有序的，所以接下來就得合並數據瞭
  4. 假設現在存在 tmpX 和 tmpY 兩個臨時文件，這時會從 tmpX 讀取一部分數據進入內存，然後從 tmpY 中讀取一部分數據進入內存，這裡你可能會好奇為什麼是一部分而不是整個或者單個？因為首先磁盤是緩慢的，所以盡量每次多讀點數據進入內存，但是不能讀太多，因為還有 buffer 空間的限制。
  5. 對於 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 即可，因此它的大致流程如下：

  1. 根據 user_id 索引，查到目標數據，然後回表，隻把 id 和 friend_name 放進 sort_buffer 中
  2. 重復1步驟，直至全部的目標數據都在 sort_buffer 中
  3. 對 sort_buffer 中的數據按照 friend_name 字段進行排序
  4. 排序後根據 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的數據，結束。

聯合索引雖然可以解決這種問題，但是在實際應用中切不可盲目建立，要根據實際的業務邏輯來判斷是否需要建立，如果不是經常有類似的查詢，可以不用建立，因為聯合索引會占用更多的存儲空間和維護開銷。

總結

  1. 對於 order by 沒有用到索引的時候，這時 explain 中 Extra 字段大概是會出現 using filesort 字眼
  2. 出現 using filesort 的時候也不用太慌張，如果本身數據量不大，比如也就幾十條數據，那麼在 sort buffer 中使用快排也是很快的
  3. 如果數據量很大，超過瞭 sort buffer 的大小，那麼是要進行臨時文件排序的，也就是歸並排序，這部分是由 MySQL 優化器決定的
  4. 如果查詢的字段很多，想要盡量避免使用臨時文件排序，可以嘗試設置下 max_length_for_sort_data 字段的大小，讓其小於所有查詢字段長度的總和，這樣放入或許可以避免，但是會多一次回表操作
  5. 實際業務中，我們也可以給經常要查詢的字段組合建立個聯合索引，這樣既不用回表也不需要單獨排序，但是聯合索引會占用更多的存儲和開銷
  6. 大量數據查詢的時候，盡量分批次，提前 explain 來觀察 sql 的執行計劃是個不錯的選擇。

以上就是MySQL數據庫order by排序精講的詳細內容，更多關於MySQL數據庫order by排序的資料請關註WalkonNet其它相關文章！

推薦閱讀：