MySQL排序速度慢而且可能不穩定

• 2019 年 11 月 12 日
• 筆記

一、具體現象

有一個功能，按照演算法得出的權重值，分頁展示一批列表數據，權重值越大越靠前。研發同學回饋查詢速度慢且排序不穩定。

排序不穩定的具體現象，有不少記錄存在相同權重值，某條記錄（假設id=100）第一頁出現了，翻到第二頁可能還有它（採用的limit控制哪一頁）。

第1頁數據

第2頁數據

一個主表A，左連接兩個表B、C，根據C的權重欄位排序。具體SQL如下

二、問題分析

查看SQL語句的執行計劃（EXPLAIN），發現有Using filesort的字樣。

趕緊搜索一下MySQL說明文檔，第一條是排序優化

文檔中有這麼一句話「如果索引不能滿足ORDERBY子句，MySQL將執行文件排序（filesort）操作，讀取數據行並對其進行排序。文件排序構成查詢執行中的額外排序階段。」

顯然，利用索引實現有序，比採用filesort更高效。filesort並不一定都通過磁碟排序，數據量不大的時候是在記憶體里完成。速度不夠快的原因找到了。

filesort的時候可能在記憶體中出現堆排序列或快速排序兩種方式，具體使用哪一種排序方式是優化器決定的，基本原則如下

• 快速排序演算法：大量排序
• 堆排序演算法：排序量不大

快速排序和堆排序是不穩定的排序演算法，對於重複值不能保證順序。Order by排序不穩定的原因也定位到了

了解一下filesort的原理

（1）根據表的索引或者全表掃描，讀取所有滿足條件的記錄。

（2）對於每一行，存儲一對值到緩衝區（排序列，行記錄指針），一個是排序的索引列的值，即order by用到的列值，和指向該行數據的行指針，緩衝區的大小為sort_buffer_size大小。

（3）當緩衝區滿後，運行一個快速排序（qsort）來將緩衝區中數據排序，並將排序完的數據存儲到一個臨時文件，並保存一個存儲塊的指針，當然如果緩衝區不滿，則不會重建臨時文件了。

（4）重複以上步驟，直到將所有行讀完，並建立相應的有序的臨時文件。

（5）對塊級進行排序，這個類似歸併排序演算法，只通過兩個臨時文件的指針來不斷交換數據，最終達到兩個文件，都是有序的。

（6）重複5直到所有的數據都排序完畢。

（7）採取順序讀的方式，將每行數據讀入記憶體（這裡讀取數據時並不是一行一行讀），並取出數據傳到客戶端，讀取快取大小由read_rnd_buffer_size來指定。

三、怎麼優化

1、利用索引達到排序目的（針對例子的優化）

針對文章開始的例子，優化原則是Use of Indexes to Satisfy ORDER BY（讓ORDER BY用上索引），即提升查詢效率，又保證穩定性（索引B+樹葉子結點的順序是唯一且一定的）

MySQL的文檔列出若干具體的case，把最主要整理出來如下。

MySQL文檔中有這麼一句話 「該查詢連接了許多表，並且ORDER BY中的列並非全部來自用於檢索行的第一個非恆定表。」，滿足這類型的SQL也不能利用索引排序。這就是文章開頭的例子。另外，使用別名，如果跟表的列名衝突可能導致索引排序失效。

看到有些文章寫到下面這條語句ORDER BY不能利用索引

這個說法顯然與MySQL官方文檔不一致。我覺得，這個語句能不能使用索引，跟資料庫引擎根據開銷決定是否檢索的階段使用索引有關。

2、優化filesort

如果確實沒辦法利用索引，可以想辦法優化filesort排序。

如果結果集太大記憶體裝不下，filesort將根據需要使用臨時磁碟文件。磁碟io速度你懂的！MySQL官方建議可以調大排序快取參數sort_buffer_size，MySQL 8.0還對快取利用率做了優化，調大一點也不浪費。以前版本的MySQL可以求助DBA。

可以這樣優化的典型SQL 語句如下

3、其他

有些ORDER BY甚至連filesort都不能用，對這類優化感覺有點超綱了，把原文貼一下

「對於不使用filesort的慢排序查詢，請嘗試將「max_length_for_sort_data」參數調低到適合觸發filesort的值（此參數的值設置得過高的一個表現是磁碟IO高和CPU負載低）」

四、實際效果

文章開頭例子的一種場景，我們巧妙利用了索引排序，達到很好的效果。

另外一個場景仍然使用filesort的排序方式

當然更好的做法是接入ES之類的搜索引擎