大咖丁奇：索引存儲順序和order by不一緻，怎麼辦？

點選上方藍字每天學習資料庫

我是林曉斌，今天作為【迪B課堂】的客串嘉賓來跟大家分享：當索引存儲順序和order by不一緻，該怎麼辦？

林曉斌

林曉斌，網名丁奇，騰訊雲資料庫負責人，資料庫領域資深技術專家。作為活躍的MySQL社群貢獻者，丁奇專注于資料存儲系統、MySQL源碼研究和改進、MySQL性能優化和功能改進，在業務場景分析、系統瓶頸分析、性能優化方面擁有豐富的經驗。其創作的《MySQL實戰45講》專欄閱聽人已逾2萬人。

根據指定的字段排序來顯示結果，是我們寫應用時最常見的需求之一了，比如一個交易系統中，按照交易時間倒序顯示交易記錄。

相信你聽說過這樣的建議：如果有order by的需求，給需要排序的字段加上索引，就可以避免資料庫排序操作。

所謂資料庫排序操作，是指資料庫在執行過程中，先将滿足條件的資料全部讀出來，放入記憶體中，再執行快排，這個記憶體就是sort_buffer。

如果臨時資料量比sort_buffer大， 就要把資料放入臨時檔案，然後做外部排序。

這個排序過程的消耗是比較大的。

所謂避免資料庫排序操作，是指執行過程中不需要快排或外部排序。

為什麼加上索引就可以避免排序呢？如果索引存儲順序和order by不一緻，還需要排序嗎? 如果是聯合索引呢？

今天我們就來說一說，建了索引以後，order by是怎麼執行的以及怎麼優化。

為了便于說明，我建立一個簡單的表，這個表裡，除了主鍵索引id外，還有一個聯合索引ab。你可以在文稿中看到這個表的定義。

我們來看看不同的業務需求下，SQL語句怎麼寫，以及在MySQL裡是怎麼執行的。

單字段排序

一個簡單的需求是将這個表的資料，按照a的大小倒序傳回。你的SQL語句可以這麼寫：

複制

我們來看看這個聯合索引ab的結構，點選可以檢視大圖。

圖1 索引(a,b)示意圖

可以看到，在這個索引上，資料存儲順序是：按照a值遞增，對于a值相同的情況，按照b值遞增。

是以上面這個語句的執行流程就是：

1. 從索引ab上，取最右的一個記錄，取出主鍵值ID_Z；
2. 根據ID_Z到主鍵索引上取整行記錄，作為結果集的第一行；
3. 在索引ab上取上一個記錄的左邊相鄰的記錄；
4. 每次取到主鍵id值，再到主鍵索引上取到整行記錄，添加到結果集的下一行；
5. 重複步驟3、4，直到周遊完整個索引。

可以看到，這個流程中并不涉及到排序操作。我們也可以用explain語句來驗證這個結論。

圖2是這個語句的explain的結果，可以看到，Extra字段中沒有Using filesort字樣，說明這個語句執行過程中，不需要用到排序。

圖2 order by 不需要排序

組合字段排序

有了上面的分析，我們再來看看下面這個語句：

複制

這個語句的意思是，按照a值倒序，當a的值相同時按照b值倒序。

你一定發現了，這個語句的執行邏輯和執行結果，跟前面的語句是一模一樣的，是以也不需要排序。

倒序不需要排序，正序呢？正序的語句是這麼寫的：

複制

顯然，這個語句也是不需要排序的，執行流程上，隻需要先取ab索引樹最左邊的節點，然後向右周遊即可。

到這裡我們可以小結一下：

1. InnoDB索引樹以任意一個葉節點為起始點，可以向左或向右周遊；
2. 如果語句需要的order by順序剛好可以利用索引樹的單向周遊，就可以避免排序操作。

Descending Indexes

接下來我們來看一種不滿足”單向周遊“的場景。

複制

這個語句要求查詢結果中的記錄排序順序是：按照a值正序，對于相同的a值，按照b值倒序。

由于不滿足單向周遊的要求，是以隻能選擇使用排序操作。

圖3是這個語句explain的結果。

圖3 order by 需要排序

extra字段中Using filesort表示使用了排序。

你一定想到了，如果可以讓InnoDB在建構索引ab的時候，相同的a裡面，b能夠從大到小排序，就又可以滿足單向周遊的要求了。

在MySQL5.7及之前的版本是不支援這麼建立索引的，在8.0版本中支援了這個功能，官方名稱是Descending Indexes。

在8.0版本中，我們可以把索引ab的定義做個修改。

我們将索引ab的定義做了修改，在字段b後面加上desc，表示對于相同的a值，字段b按照倒序存儲。

這個表對應的索引ab的結構圖如下，點選可以檢視大圖。

圖4 索引(a, b desc) 示意圖 和 explain的結果

這樣從左到右周遊這個索引的時候，就剛好滿足a正序，然後b逆序的要求。

Descending Indexes可以避免這種情況下的排序操作，語句的執行性能自然就提升了。

應用優化

前面說過，Descending Indexes這個功能是在MySQL 8.0才支援的。那如果你的生産環境上使用的還是低于8.0的版本，有沒有不需要排序的方法呢？

答案是有的，接下來我給大家介紹一種應用端協作的優化方案。

假設我們現在的需求就是在MySQL 5.7版本下，要求按照”a值正序，然後b值逆序”的順序，傳回所有行a和b的值。

首先，為了避免資料庫排序，我們直接執行下面這個語句：

複制

當然，這個語句傳回的結果集是不滿足業務要求的，但是我們知道，對于相同的a值，b值是有序遞增的，我們要把這個資料特點利用起來。

執行這個語句後，應用端的邏輯改造如下：

1. 構造一個空棧(stack)，棧中的節點可以儲存資料行；
2. 讀入第一行，入棧；

3. 讀入下一行，

   a.如果新一行中a值與上一行相同，将新一行入棧；

   b.如果新一行中a值與上一行不同，則将棧中的所有資料行依次出棧并輸出，直到棧清空；然後新一行入棧。

4. 重複步驟3直到周遊完整個索引，将棧中的所有資料行依次出棧并輸出，直到棧清空。

下圖5是用圖1中的示例資料，執行上面的流程的效果圖。

圖5 應用端優化的執行流程

可以看到，這個過程中資料庫端沒有使用排序，在應用端也沒有使用排序。

這個過程需要在應用端構造一個棧，需要臨時記憶體。當然這個記憶體并不是憑空多出來的，因為如果不使用這個方法，就隻能在MySQL端排序，這個記憶體就會在MySQL裡建立，也就是sort_buffer。

相比之下，使用應用端的記憶體還是比使用MySQL的記憶體好些，也算是這個方案的另一個優點。

小結

接下來，我給你總結一下今天的主要内容。

今天我給你介紹了MySQL在有索引的情況下，處理order by請求的執行過程，也介紹了Descending Indexes的應用背景。

Descending Indexes是MySQL 8.0才支援的特性。在資料庫不支援一些特性的時候，也可以考慮通過應用端的協作來實作業務需求。方案優化并不一定隻是資料庫的優化，綜合考慮系統中各個子產品的特性，可以增強我們解決問題的靈活性。

探讨了那麼多，希望騰訊雲資料庫實戰課程【迪B課堂】可以和大家一起建構MySQL知識網絡。有一個問題留給大家讨論：

最後這個算法的一個極端情況是重複的a值太多，如果預估到業務可能出現這種情況，應用的代碼應該怎麼設計呢？

歡迎在評論區留下你對讨論題的想法，資料君将挑選最精彩的回答送上騰訊雲資料庫100元無門檻代金券一張。

往期推薦

掃描下方二維碼關注騰訊雲資料庫

回複“實戰課程”即可學習往期迪B課程

免費試用

包括雲資料庫MySQL在内的40+款熱門雲産品，實名認證的企業使用者可免費試用！1000M記憶體50G資料盤的MySQL可免費體驗30天，點選左下角“閱讀原文”立即領取~

↓↓點“閱讀原文”免費試用

好文和朋友一起看！

var first_sceen__time = (+new Date());if ("" == 1 && document.getElementById('js_content')) { document.getElementById('js_content').addEventListener("selectstart",function(e){ e.preventDefault(); }); } (function(){ if (navigator.userAgent.indexOf("WindowsWechat") != -1){ var link = document.createElement('link'); var head = document.getElementsByTagName('head')[0]; link.rel = 'stylesheet'; link.type = 'text/css'; link.href = "//res.wx.qq.com/mmbizwap/zh_CN/htmledition/style/page/appmsg_new/winwx45ba31.css"; head.appendChild(link); } })();

林曉斌

贊賞

長按二維碼向我轉賬

受蘋果公司新規定影響，微信 iOS 版的贊賞功能被關閉，可通過二維碼轉賬支援公衆号。

閱讀原文

閱讀

分享 在看

已同步到看一看

取消 發送

我知道了

朋友會在“發現-看一看”看到你“在看”的内容

确定

已同步到看一看寫下你的想法

最多200字，目前共字 發送

已發送

朋友将在看一看看到

确定

寫下你的想法...

取消

釋出到看一看

确定

最多200字，目前共字

發送中

微信掃一掃

關注該公衆号

微信掃一掃

使用小程式

即将打開""小程式

取消 打開