資料庫大咖丁奇：MySQL索引存儲順序和order by不一致，怎麼辦？
今天與大家分享：當索引存儲順序和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值遞增。
因此上面這個語句的執行流程就是：
從索引ab上，取最右的一個記錄，取出主鍵值ID_Z；
根據ID_Z到主鍵索引上取整行記錄，作為結果集的第一行；
在索引ab上取上一個記錄的左邊相鄰的記錄；
每次取到主鍵id值，再到主鍵索引上取到整行記錄，添加到結果集的下一行；
重複步驟3、4，直到遍歷完整個索引。
可以看到，這個流程中並不涉及到排序操作。我們也可以用explain語句來驗證這個結論。
圖2是這個語句的explain的結果，可以看到，Extra欄位中沒有Using filesort字樣，說明這個語句執行過程中，不需要用到排序。
圖2 order by 不需要排序
組合欄位排序
有了上面的分析，我們再來看看下面這個語句：
這個語句的意思是，按照a值倒序，當a的值相同時按照b值倒序。
你一定發現了，這個語句的執行邏輯和執行結果，跟前面的語句是一模一樣的，因此也不需要排序。
倒序不需要排序，正序呢？正序的語句是這麼寫的：
顯然，這個語句也是不需要排序的，執行流程上，只需要先取ab索引樹最左邊的節點，然後向右遍歷即可。
到這裡我們可以小結一下：
InnoDB索引樹以任意一個葉節點為起始點，可以向左或向右遍歷；
如果語句需要的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值是有序遞增的，我們要把這個數據特點利用起來。
執行這個語句後，應用端的邏輯改造如下：
構造一個空棧(stack)，棧中的節點可以保存數據行；
讀入第一行，入棧；
讀入下一行，
a.如果新一行中a值與上一行相同，將新一行入棧；
b.如果新一行中a值與上一行不同，則將棧中的所有數據行依次出棧並輸出，直到棧清空；然後新一行入棧。
重複步驟3直到遍歷完整個索引，將棧中的所有數據行依次出棧並輸出，直到棧清空。
下圖5是用圖1中的示例數據，執行上面的流程的效果圖。
圖5 應用端優化的執行流程
可以看到，這個過程中資料庫端沒有使用排序，在應用端也沒有使用排序。
這個過程需要在應用端構造一個棧，需要臨時內存。當然這個內存並不是憑空多出來的，因為如果不使用這個方法，就只能在MySQL端排序，這個內存就會在MySQL里創建，也就是sort_buffer。
相比之下，使用應用端的內存還是比使用MySQL的內存好些，也算是這個方案的另一個優點。
小結
接下來，總結一下今天的主要內容。
今天介紹了MySQL在有索引的情況下，處理order by請求的執行過程，也介紹了Descending Indexes的應用背景。
Descending Indexes是MySQL 8.0才支持的特性。在資料庫不支持一些特性的時候，也可以考慮通過應用端的協作來實現業務需求。方案優化並不一定只是資料庫的優化，綜合考慮系統中各個模塊的特性，可以增強我們解決問題的靈活性。
探討了那麼多，希望騰訊雲資料庫可以和大家一起構建MySQL知識網路。有一個問題留給大家討論：
最後這個演算法的一個極端情況是重複的a值太多，如果預估到業務可能出現這種情況，應用的代碼應該怎麼設計呢？
關注云加社區，回復3加讀者群
點在看，讓更多人看到！
TAG:雲加社區 |