後端程式員必備：攻克order by

前言

日常開發中，我們經常會使用到order by，親愛的小夥伴，你是否知道order by 的工作原理呢？order by的優化思路是怎樣的呢？使用order by有哪些注意的問題呢？本文将跟大家一起來學習，攻克order by~

一個使用order by 的簡單例子

假設用一張員工表，表結構如下：

表資料如下：

我們現在有這麼一個需求：查詢前10個，來自深圳員工的姓名、年齡、城市，并且按照年齡小到大排序。對應的 SQL 語句就可以這麼寫：

select name,age,city from staff where city = '深圳' order by age limit 10;           

這條語句的邏輯很清楚，但是它的底層執行流程是怎樣的呢？

order by 工作原理

explain 執行計劃

我們先用Explain關鍵字檢視一下執行計劃

• 執行計劃的key這個字段，表示使用到索引idx_city
• Extra 這個字段的 Using index condition 表示索引條件
• Extra 這個字段的 Using filesort表示用到排序

我們可以發現，這條SQL使用到了索引，并且也用到排序。那麼它是怎麼排序的呢？

全字段排序

MySQL 會給每個查詢線程配置設定一塊小記憶體，用于排序的，稱為 sort_buffer。什麼時候把字段放進去排序呢，其實是通過idx_city索引找到對應的資料，才把資料放進去啦。

我們回顧下索引是怎麼找到比對的資料的，現在先把索引樹畫出來吧，idx_city索引樹如下：

idx_city索引樹，葉子節點存儲的是主鍵id。 還有一棵id主鍵聚族索引樹，我們再畫出聚族索引樹圖吧：

我們的查詢語句是怎麼找到比對資料的呢？先通過idx_city索引樹，找到對應的主鍵id，然後再通過拿到的主鍵id，搜尋id主鍵索引樹，找到對應的行資料。

加上order by之後，整體的執行流程就是：

1. MySQL 為對應的線程初始化sort_buffer，放入需要查詢的name、age、city字段；
2. 從索引樹idx_city， 找到第一個滿足 city='深圳’條件的主鍵 id，也就是圖中的id=9；
3. 到主鍵 id 索引樹拿到id=9的這一行資料， 取name、age、city三個字段的值，存到sort_buffer；
4. 從索引樹idx_city 拿到下一個記錄的主鍵 id，即圖中的id=13；
5. 重複步驟 3、4 直到city的值不等于深圳為止；
6. 前面5步已經查找到了所有city為深圳的資料，在 sort_buffer中，将所有資料根據age進行排序；
7. 按照排序結果取前10行傳回給用戶端。

執行示意圖如下：

将查詢所需的字段全部讀取到sort_buffer中，就是全字段排序。這裡面，有些小夥伴可能會有個疑問,把查詢的所有字段都放到sort_buffer，而sort_buffer是一塊記憶體來的，如果資料量太大，sort_buffer放不下怎麼辦呢？

磁盤臨時檔案輔助排序

實際上，sort_buffer的大小是由一個參數控制的：sort_buffer_size。如果要排序的資料小于sort_buffer_size，排序在sort_buffer 記憶體中完成，如果要排序的資料大于sort_buffer_size，則借助磁盤檔案來進行排序

如何确定是否使用了磁盤檔案來進行排序呢？ 可以使用以下這幾個指令

可以從 number_of_tmp_files 中看出，是否使用了臨時檔案。

number_of_tmp_files 表示使用來排序的磁盤臨時檔案數。如果number_of_tmp_files>0，則表示使用了磁盤檔案來進行排序。

使用了磁盤臨時檔案，整個排序過程又是怎樣的呢？

1. 從主鍵Id索引樹，拿到需要的資料，并放到sort_buffer記憶體塊中。當sort_buffer快要滿時，就對sort_buffer中的資料排序，排完後，把資料臨時放到磁盤一個小檔案中。
2. 繼續回到主鍵 id 索引樹取資料，繼續放到sort_buffer記憶體中，排序後，也把這些資料寫入到磁盤臨時小檔案中。
3. 繼續循環，直到取出所有滿足條件的資料。最後把磁盤的臨時排好序的小檔案，合并成一個有序的大檔案。

TPS: 借助磁盤臨時小檔案排序，實際上使用的是歸并排序算法。

小夥伴們可能會有個疑問，既然sort_buffer放不下，就需要用到臨時磁盤檔案，這會影響排序效率。那為什麼還要把排序不相關的字段（name，city）放到sort_buffer中呢？隻放排序相關的age字段，它不香嗎？ 可以了解下rowid 排序。

rowid 排序

rowid 排序就是，隻把查詢SQL需要用于排序的字段和主鍵id，放到sort_buffer中。那怎麼确定走的是全字段排序還是rowid 排序排序呢？

實際上有個參數控制的。這個參數就是max_length_for_sort_data，它表示MySQL用于排序行資料的長度的一個參數，如果單行的長度超過這個值，MySQL 就認為單行太大，就換rowid 排序。我們可以通過指令看下這個參數取值。

show variables like 'max_length_for_sort_data';           

max_length_for_sort_data 預設值是1024。因為本文示例中name,age,city長度=64+4+64 =132 < 1024, 是以走的是全字段排序。我們來改下這個參數，改小一點，

使用rowid 排序的話，整個SQL執行流程又是怎樣的呢？

1. MySQL 為對應的線程初始化sort_buffer，放入需要排序的age字段，以及主鍵id；
2. 從索引樹idx_city， 找到第一個滿足 city='深圳’條件的主鍵 id，也就是圖中的id=9；
3. 到主鍵 id 索引樹拿到id=9的這一行資料， 取age和主鍵id的值，存到sort_buffer；
4. 從索引樹idx_city 拿到下一個記錄的主鍵 id，即圖中的id=13；
5. 重複步驟 3、4 直到city的值不等于深圳為止；
6. 前面5步已經查找到了所有city為深圳的資料，在 sort_buffer中，将所有資料根據age進行排序；
7. 周遊排序結果，取前10行，并按照 id 的值回到原表中，取出city、name 和 age 三個字段傳回給用戶端。

執行示意圖如下：

對比一下全字段排序的流程，rowid 排序多了一次回表。

什麼是回表？拿到主鍵再回到主鍵索引查詢的過程，就叫做回表

我們通過optimizer_trace，可以看到是否使用了rowid排序的：

全字段排序與rowid排序對比

• 全字段排序： sort_buffer記憶體不夠的話，就需要用到磁盤臨時檔案，造成磁盤通路。
• rowid排序： sort_buffer可以放更多資料，但是需要再回到原表去取資料，比全字段排序多一次回表。

一般情況下，對于InnoDB存儲引擎，會優先使用全字段排序。可以發現 max_length_for_sort_data 參數設定為1024，這個數比較大的。一般情況下，排序字段不會超過這個值，也就是都會走全字段排序。

order by的一些優化思路

我們如何優化order by語句呢？

• 因為資料是無序的，是以就需要排序。如果資料本身是有序的，那就不用排了。而索引資料本身是有序的，我們通過建立聯合索引，優化order by 語句。
• 我們還可以通過調整max_length_for_sort_data等參數優化；

聯合索引優化

再回顧下示例SQL的查詢計劃

explain select name,age,city from staff where city = '深圳' order by age limit 10;           

我們給查詢條件city和排序字段age，加個聯合索引idx_city_age。再去檢視執行計劃

可以發現，加上idx_city_age聯合索引，就不需要Using filesort排序了。為什麼呢？因為索引本身是有序的，我們可以看下idx_city_age聯合索引示意圖，如下：

整個SQL執行流程變成醬紫：

1. 從索引idx_city_age找到滿足city='深圳’ 的主鍵 id
2. 到主鍵 id索引取出整行，拿到 name、city、age 三個字段的值，作為結果集的一部分直接傳回
3. 從索引idx_city_age取下一個記錄主鍵id
4. 重複步驟 2、3，直到查到第10條記錄，或者是不滿足city='深圳’ 條件時循環結束。

流程示意圖如下：

從示意圖看來，還是有一次回表操作。針對本次示例，有沒有更高效的方案呢？有的，可以使用覆寫索引：

覆寫索引：在查詢的資料列裡面，不需要回表去查，直接從索引列就能取到想要的結果。換句話說，你SQL用到的索引列資料，覆寫了查詢結果的列，就算上覆寫索引了。

我們給city，name，age 組成一個聯合索引，即可用到了覆寫索引，這時候SQL執行時，連回表操作都可以省去啦。

調整參數優化

我們還可以通過調整參數，去優化order by的執行。比如可以調整sort_buffer_size的值。因為sort_buffer值太小，資料量大的話，會借助磁盤臨時檔案排序。如果MySQL伺服器配置高的話，可以使用稍微調整大點。

我們還可以調整max_length_for_sort_data的值，這個值太小的話，order by會走rowid排序，會回表，降低查詢性能。是以max_length_for_sort_data可以适當大一點。

當然，很多時候，這些MySQL參數值，我們直接采用預設值就可以了。

使用order by 的一些注意點

沒有where條件，order by字段需要加索引嗎

日常開發過程中，我們可能會遇到沒有where條件的order by，那麼，這時候order by後面的字段是否需要加索引呢。如有這麼一個SQL，create_time是否需要加索引：

select * from A order by create_time;           

無條件查詢的話，即使create_time上有索引,也不會使用到。因為MySQL優化器認為走普通二級索引，再去回表成本比全表掃描排序更高。是以選擇走全表掃描,然後根據全字段排序或者rowid排序來進行。

如果查詢SQL修改一下：

select * from A order by create_time limit m;           

• 無條件查詢,如果m值較小,是可以走索引的.因為MySQL優化器認為，根據索引有序性去回表查資料,然後得到m條資料,就可以終止循環,那麼成本比全表掃描小,則選擇走二級索引。

分頁limit過大時，會導緻大量排序怎麼辦?

假設SQL如下：

select * from A order by a limit 100000,10           

• 可以記錄上一頁最後的id，下一頁查詢時，查詢條件帶上id，如： where id > 上一頁最後id limit 10。
• 也可以在業務允許的情況下，限制頁數。

索引存儲順序與order by不一緻，如何優化？

假設有聯合索引 idx_age_name, 我們需求修改為這樣：查詢前10個員工的姓名、年齡，并且按照年齡小到大排序，如果年齡相同，則按姓名降序排。對應的 SQL 語句就可以這麼寫：

select name,age from staff  order by age ,name desc limit 10;           

我們看下執行計劃，發現使用到Using filesort。

這是因為，idx_age_name索引樹中，age從小到大排序，如果age相同，再按name從小到大排序。而order by 中，是按age從小到大排序，如果age相同，再按name從大到小排序。也就是說，索引存儲順序與order by不一緻。

我們怎麼優化呢？如果MySQL是8.0版本，支援Descending Indexes，可以這樣修改索引：

使用了in條件多個屬性時，SQL執行是否有排序過程

如果我們有聯合索引idx_city_name，執行這個SQL的話，是不會走排序過程的，如下：

select * from staff where city in ('深圳') order by age limit 10;           

但是，如果使用in條件，并且有多個條件時，就會有排序過程。

explain select * from staff where city in ('深圳','上海') order by age limit 10;           

這是因為:in有兩個條件，在滿足深圳時，age是排好序的，但是把滿足上海的age也加進來，就不能保證滿足所有的age都是排好序的。是以需要Using filesort。

最後

• 如果覺得有收獲，幫忙點贊關注，轉發下哈，感謝感謝