Mysql中的order by 是到底該怎麼最佳化？
01 前言
剛換了新工作，用了兩週時間準備，在 3 天之內拿了 5 個 offer，最後選擇了廣州某網際網路行業獨角獸 offer，昨天剛入職。這幾天剛好整理下在面試中被問到有意思的問題，也藉此機會跟大家分享下。
這家企業的面試官有點意思，一面是個同齡小哥，一起聊了兩個小時（聊到我嘴都幹了）。二面是個從阿里出來的架構師，影片面試，我做完自我介紹之後，他一開場就問我：
對 MySQL 熟悉嗎？
我一愣，隨之意識到這是個坑。他肯定想問我某方面的原理了，恰好我研究過索引。就回答：
對索引比較熟悉。
他：
order by 是怎麼實現排序的？
還好我又複習，基本上排序緩衝區、怎麼最佳化之類的都答到點子上。今天也跟大家盤一盤 order by，我將從原理講到最終最佳化，給大家聊聊 order by，希望對你有所幫助。
1。1 先舉個栗子
現在有一張訂單表，結構是這樣的：
CREATE TABLE `order` （
id INT （ 11 ） NOT NULL AUTO_INCREMENT COMMENT ‘主鍵’，
user_code VARCHAR （ 16 ） NOT NULL COMMENT ‘使用者編號’，
goods_name VARCHAR （ 64 ） NOT NULL COMMENT ‘商品名稱’，
order_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘下單時間’，
city VARCHAR （ 16 ） DEFAULT NULL COMMENT ‘下單城市’，
order_num INT （ 10 ） NOT NULL COMMENT ‘訂單號數量’，
PRIMARY KEY （ `id` ）
） ENGINE = INNODB AUTO_INCREMENT = 100 DEFAULT CHARSET = utf8 COMMENT = ‘商品訂單表’；
造點資料：
// 第一步：建立函式
delimiter //
DROP PROCEDURE
IF
EXISTS proc_buildata；
CREATE PROCEDURE proc_buildata （ IN loop_times INT ） BEGIN
DECLARE var INT DEFAULT 0；
WHILE
var < loop_times DO
SET var = var + 1；
INSERT INTO `order` （ `id`， `user_code`， `goods_name`， `order_date`， `city` ， `order_num`）
VALUES
（ var， var + 1， ‘有線耳機’， ‘2021-06-20 16：46：00’， ‘杭州’， 1 ）；
END WHILE；
END // delimiter；
// 第二步：呼叫上面生成的函式，即可插入資料，建議大家造點隨機的資料。比如改改城市和訂單數量
CALL proc_buildata（4000）；
我生成的資料是這樣的：
現有需求：
查出 618 期間，廣州的小夥伴的訂單數量和使用者編號，並按照訂單數量升序，只要 1000 條
。
根據需求可以得出以下 SQL，相信小夥伴都很熟悉了。
select
city
，
order_num
，
user_code
from
`
order
`
where
city
=
‘
廣州
’
order
by
order_num
limit
1000
；
那這個語句是怎麼執行的呢？有什麼引數可以影響它的行為嗎？
02 全欄位排序
得到這個需求，我第一反應是先給 city 欄位加上索引，避免全表掃描：
ALTER
TABLE
`
order
`
ADD
INDEX
city_index
（
`
city
`
）；
用 explain 看看執行情況
注意到最後一個 extra 欄位的結果是：
Using filesort
，表示需要排序。
其實 MySQL 會給每個執行緒分配一塊記憶體用於排序，稱為 sort_buffer
。
為了更直觀瞭解排序的執行流程，我粗略畫了個 city 索引的圖示：
可見，現在滿足 sql 條件的就是 ID-3 到 ID-X 這一段資料。sql 的整個流程是這樣的：
1、初始化 sort_buffer，放入 city、order_num、user_code 這三個欄位；
2、從索引 city 找到第一個滿足 city=‘ 廣州’條件的主鍵 id，也就是圖中的 ID_3；
3、到主鍵 id 索引取出整行，取 city、order_num、user_code 三個欄位的值，存入 sort_buffer 中；
4、從索引 city 取下一個記錄的主鍵 id；
5、重複步驟 3、4 直到 city 的值不滿足查詢條件為止，對應的主鍵 id 也就是圖中的 ID_X；
6、對 sort_buffer 中的資料按照欄位 order_num 做快速排序；
7、按照排序結果取前 1000 行返回給客戶端。
這個過程稱之為全欄位排序，畫個圖，長這樣：
其中，
按 order_num 排序
這個步驟，可能在記憶體中完成，也可能需要使用外部排序，這取決於排序所需的記憶體和引數
sort_buffer_size
。
也就是 MySQL 為排序開闢的記憶體（sort_buffer）的大小。如果要排序的資料量小於 sort_buffer_size，排序就在記憶體中完成。但如果排序資料量太大，記憶體頂不住，就得磁碟臨時檔案輔助排序。
當然，在 MySQL5。7 以上版本可以用下面介紹的
檢測方法（後面都有用到）
，來檢視一個排序語句是否使用了臨時檔案。PS：
這裡的語句直接複製到 navicat 執行即可，要一起執行（都複製進去，點下執行）
/* 開啟optimizer_trace，只對本執行緒有效 */
SET
optimizer_trace
=
’
enabled
=
on
‘
；
/* @a儲存Innodb_rows_read的初始值 */
select
VARIABLE_VALUE
into
@a
from
performance_schema
。
session_status
where
variable_name
=
’
Innodb_rows_read
‘
；
/* 執行語句 */
select
city
，
order_num
，
user_code
from
`
order
`
where
city
=
’
廣州
‘
order
by
order_num
limit
1000
；
/* 檢視 OPTIMIZER_TRACE 輸出 */
SELECT
*
FROM
`
information_schema
`
。
`
OPTIMIZER_TRACE
`
；
/* @b儲存Innodb_rows_read的當前值 */
select
VARIABLE_VALUE
into
@b
from
performance_schema
。
session_status
where
variable_name
=
’
Innodb_rows_read
‘
；
/* 計算Innodb_rows_read差值 */
select
@b
-
@a
；
執行完之後，可從 OPTIMIZER_TRACE 表的 TRACE 欄位得到以下結果：
其中 examined_rows 表示需要排序的行數 6883；sort_buffer_size 就是排序緩衝區的大小；sort_buffer_size 就是我 MySQL 的排序緩衝區大小 256 KB。
另外，sort_mode 的值是 packed_additional_fields，它表示排序過程對資料做了最佳化，也就是資料佔用多少就算多少記憶體。舉個栗子：不存在資料定義長度 16，就按這個長度算，如果資料只佔 2，只會按長度 2 分配記憶體。
number_of_tmp_files 代表的是用了幾個外部檔案來輔助排序。我這裡是用了兩個，記憶體放不下時，就使用外部排序，外部排序一般使用
歸併排序
演算法。可以這麼簡單理解，
MySQL 將需要排序的資料分成 2 份，每一份單獨排序後存在這些臨時檔案中。然後把這 2 個有序檔案再合併成一個有序的大檔案
。
最後一個查詢語句，select @b-@a 的值是 6884，表示整個過程只掃描了 6883 行，為啥顯示 6884？
因為查詢 OPTIMIZER_TRACE 表時，需用到臨時表；而 InnDB 引擎把資料從臨時表取出時，Inndb_rows_read 值會加 1。
所以，把 internal_tmp_disk_storage_engine 設定為 MyISAM 可解決此問題。
03 rowid 排序
上面的全欄位排序其實會有很大的問題，你可能發現了。
我們需要查詢的欄位都要放到 sort_buffer 中，如果查詢的欄位多了起來，記憶體佔用升高，就會很容易打滿 sort_buffer
。
這時，就要用很多的臨時檔案輔助排序，導致效能降低。
那問題來了：
我們思考的方向應該是降低排序的單行長度，哪有沒有方法能做到呢？
肯定是有的，MySQL 之所以走全欄位排序是由 max_length_for_sort_data 控制的，它的 預設值是 1024。
show variables like ’max_length_for_sort_data‘；
因為本文示例中 city，order_num，user_code 長度 = 16+4+16 =36 < 1024， 所以走的是全欄位排序。我們來改下這個引數，改小一點，
SET max_length_for_sort_data = 16；
當單行的長度超過這個值，MySQL 就認為單行太大，要換一個演算法。原來 city、user_code、order_num 佔用的長度是 36，顯然放不下全部查詢欄位了。這時就要換演算法：
sort_buffer 只存 order_num 和 id 欄位
。
這時的流程應該是這樣的：
1、初始化 sort_buffer，確定放入兩個欄位，即 order_num 和 id；
2、從索引 city 找到第一個滿足 city=’ 廣州’條件的主鍵 id，也就是圖中的 ID_3；
3、回表，取 order_num、id 這兩個欄位，存入 sort_buffer 中；
4、從索引 city 取下一個記錄的主鍵 id；
5、重複步驟 3、4 直到不滿足 city=‘ 廣州’條件為止，也就是圖中的 ID_X；
6、對 sort_buffer 中的資料按照欄位 order_num 進行排序；
7、遍歷排序結果，取前 1000 行，再次回表取出 city、order_num 和 user_code 三個欄位返回給客戶端。
圖示：由圖可見，這種方式其實多了一次回表操作、但 sort_buffer_size 佔用卻變小了。
此時，執行上面的檢測方法，可以發現 OPTIMIZER_TRACE 表中的資訊變了。
sort_mode 變成了 ，
表示參與排序的只有 order_num 和 id 這兩個欄位
。
number_of_tmp_files 變成 0 了，是因為這時參與排序的行數雖然仍然是 6883 行，但是每一行都變小了，因此需要排序的總資料量就變小了，sort_buffer_size 能滿足排序用的記憶體，所以臨時檔案就不需要了。
examined_rows 的值還是 6883，表示用於排序的資料是 6883 行。但是 select @b-@a 這個語句的值變成 7884 了。因為這時候除了排序過程外，在排序完成後，還要回表一次。由於語句是 limit 1000，所以會多讀 1000 行。
3。1 做個小結
rowid 排序中，
排序過程一次可以排序更多行，但是需要回表取資料
。
如果記憶體足夠大，MySQL 會優先選擇全欄位排序，把需要的欄位都放到 sort_buffer 中，這樣排序後就會直接從記憶體返回查詢結果了，不用回表。
這也就體現了 MySQL 的一個設計思想：
如果記憶體夠，就要多利用記憶體，儘量減少磁碟訪問
。
對於 InnoDB 表來說，rowid 排序會要求回表多造成磁碟讀，因此不會被優先選擇
。
這兩種都是因為資料本身是無序的，才要放到 sort_buffer 並生成臨時檔案才能做排序。
哪有沒有辦法，讓資料本身就有序呢？回想下，我們學過的索引就是有序的。
04 索引最佳化
這時，要是我把 city、order_num 建一個組合索引，得出的資料是不是就是天然有序的了？比如：
alter table `order` add index city_order_num_index（city， order_num）；
此時，order 表的索引長這樣：
文章開頭的 sql 執行語句。執行流程長這樣：
1、從索引 （city，order_num） 找到第一個滿足 city=’ 廣州’條件的主鍵 id；
2、回表，取 city、order_num、user_code 三個欄位的值，作為結果集的一部分直接返回；
3、從索引 （city，order_num） 取下一個記錄主鍵 id；
4、重複步驟 2、3，直到查到第 1000 條記錄，或者是不滿足 city=‘ 廣州’條件時迴圈結束。
用 explain 看下，這個過程不需要排序，更不需要臨時表。
只需要一次回表
：
從圖中可以看到，
Extra 欄位中沒有 Using filesort 了，也就是不需要排序了
。而且由於 （city，order_num） 這個聯合索引本身有序，只要找到滿足條件的前 1000 條記錄就可以退出了，再回表一次。也就是說，只需要掃描 2000 次。
問題來了，還有沒有更優解呢？
05 終極最佳化
上面的方法，還是有一次回表，主要是因為索引中不包括 user_code。回顧下我們之前學過的 sql 最佳化，是怎麼避免回表的？
查詢欄位，加到組合索引中呀
，對應到這張表，就是把 user_code 也加到組合索引中：
alter table `order` add index city_order_num_user_code_index（city， order_num， user_code）；
此時的流程長這樣，直接取資料就完事了：
explain 看下執行情況：
從圖中可知，
Extra 欄位中多了 Using index 了，也就是使用了索引覆蓋
。連回表都不需要了，只需掃描 1000 次。
完美~
5。1 引數調優
除此以外，還可以透過調整引數最佳化 order by 的執行。
比如調整 sort_buffer_size 儘量大點
，因為 sort_buffer 太小，排序資料量大的話，會藉助磁碟臨時檔案排序。如果 MySQL 伺服器配置高的話，可以稍微調大點。
再
比如把 max_length_for_sort_data 的值調大點
。如果該值過小，則會增加回表次數、降低查詢效能。
06 order by 常見面試題
1、查詢語句有 in 多個屬性時，SQL 執行是否有排序過程？
假設現在有聯合索引 （city，order_num，user_code），執行以下 SQL 語句：
select
city
，
order_num
，
user_code
from
`
order
`
where
city
in
（
’
廣州
‘
）
order
by
order_num
limit
1000
in 單個條件，毫無疑問是不需要排序的。explain 一下：
但是，in 多個條件時；就會有排序過程，比如執行以下語句
select
city
，
order_num
，
user_code
from
`
order
`
where
city
in
（
’
廣州
‘，’
深圳
‘
）
order
by
order_num
limit
1000
explain 以下，看到最後有 Using filesort 就說明有排序過程。這是為啥呢？
因為 order_num 本來就是組合索引，滿足 “city=廣州” 只有一個條件時，它是有序的。滿足 “city=深圳” 時，它也是有序的。但是兩者加到一起就不能保證 order_num 還是有序的了。
2、分頁 limit 過大，導致大量排序。咋辦？
select * from `user` order by age limit 100000，10
可以記錄上一頁最後的 id，下一頁查詢時，查詢條件帶上 id，如：where id > 上一頁最後 id limit 10。
也可以在業務允許的情況下，限制頁數。
3、索引儲存順序與 order by 不一致，如何最佳化？
假設有聯合索引 （age，name）， 我們需求修改為這樣：
查詢前 10 個學生的姓名、年齡，並且按照年齡小到大排序，如果年齡相同，則按姓名降序排
。對應的 SQL 語句應該是：
select name， age from student order by age， name desc limit 10；
explain 一下，
extra 的值是 Using filesort
，走了排序過程：
這是因為，（age，name） 索引樹中，age 從小到大排序，
如果 age 相同，再按 name 從小到大排序
。而 order by 中，是按 age 從小到大排序，如果 age 相同，再按 name 從大到小排序。也就是說，索引儲存順序與 order by 不一致。
我們怎麼最佳化呢？如果 MySQL 是 8。0 版本，支援
Descending Indexes
，可以這樣修改索引：
CREATE TABLE `student` （
`id` bigint（11） NOT NULL AUTO_INCREMENT COMMENT ’主鍵id‘，
`student_id` varchar（20） NOT NULL COMMENT ’學號‘，
`name` varchar（64） NOT NULL COMMENT ’姓名‘，
`age` int（4） NOT NULL COMMENT ’年齡‘，
`city` varchar（64） NOT NULL COMMENT ’城市‘，
PRIMARY KEY （`id`），
KEY `idx_age_name` （`age`，`name` desc） USING BTREE
） ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT=’學生表‘；
4、沒有 where 條件，order by 欄位需要加索引嗎
日常開發中，可能會遇到沒有 where 條件的 order by，這時候 order by 後面的欄位是否需要加索引呢。如有這麼一個 SQL，create_time 是否需要加索引：
select * from student order by create_time；
無條件查詢的話，即使 create_time 上有索引，也不會使用到。
因為 MySQL 最佳化器認為走普通二級索引，再去回表成本比全表掃描排序更高
。所以選擇走全表掃描，然後根據全欄位排序或者 rowid 排序來進行。
如果查詢 SQL 修改一下：
select * from student order by create_time limit m；
無條件查詢，如果 m 值較小，是可以走索引的。因為 MySQL 最佳化器認為，根據索引有序性去回表查資料，然後得到 m 條資料，就可以終止迴圈，那麼成本比全表掃描小，則選擇走二級索引。
07 總結
這篇文章跟你聊了聊 order by 的執行流程，以及全欄位排序和 rowid 排序的區別，從而得知，
MySQL 更願意用記憶體去換取效能上的提升
。
與此同時，透過組合索引的索引覆蓋小技巧，我們還可以減少回表的次數。
以後設計索引的時候如果業務有涉及排序的欄位，儘量加到索引中，並且把業務中其餘的查詢欄位（比如文中的 city、user_code）加到組合索引中，更好地實現索引覆蓋
。
當然，索引也有缺點。它佔空間，有維護的代價。所以大家設計的時候還是需要根據自己的實際業務去考慮。
最後，我還跟你探討了關於 order by 的四個經典面試題，希望對你有幫助。
7。1 參考
http：//
blog。csdn。net/weixin_28
917279/article/details/113424610
http：//
time。geekbang。org/colum
n/article/73479
http：//
zhuanlan。zhihu。com/p/38
0671457
作者：JavaFish 連結：
https：//
juejin。cn/post/70474334
74264793095
