導讀01前言剛換了新工作，用了兩周時間準備，在3天之內拿了5個offer，最后選擇了廣州某互聯網行業獨角獸offer，昨天剛入職。這幾天剛好整理下在面試中被問到....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 PROCEDUREIFEXISTS proc_buildata;CREATE PROCEDURE proc_buildata ( IN loop_times INT ) BEGINDECLARE var INT DEFAULT 0;WHILEvar < loop_times DOSET 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 變成了 <sort_key, rowid>，表示參與排序的只有 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 的四個經典面試題，希望對你有幫助。

作者：JavaFish鏈接：https://juejin.cn/post/7047433474264793095