四面阿里被問MySQL底層如何實現order by的,瞬間懵了!

需求

查詢城市是“上海”的所有用戶名，并按用戶名排序，返回前1000人的名字、年齡。

先看建表語句：

Easy！SQL隨手一寫：

為避免全表掃描，給 city 字段加個索引，再explain驗證：

explain select city, name, age from citizen where city = '上海' order by name limit 1000; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | citizen | NULL | ALL | city | NULL | NULL | NULL | 32 | 100.00 | Using where; Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)

Extra的 Using filesort 表示需要排序，Mysql會給每個線程分配一塊內存（sort_buffer）用于排序。

那么Mysql底層到底是如何執行order by的呢？

首先看city索引：

這里 id_x ~ id_(x+n) 的數據都滿足city=上海。

SQL執行流程

初始化sort_buffer，放入city, name, age三字段

從索引city找到第一個滿足city=上海的主鍵id， 即id_x

到主鍵索引取出整行，取city, name, age三個字段的值，存入sort_buffer

從索引city取下一個記錄的主鍵id

重復3、4，直到city值不滿足查詢條件，即主鍵id_y

對sort_buffer中的數據按name做快排

取排序后結果的前1000行，返回給client

這就是

全字段排序

執行流程

按name排序 這一操作可能：

在內存中完成

或需要外部排序

這取決于：

排序所需的內存

若待排序數據量 < sort_buffer_size，就在內存中排序。

參數sort_buffer_size

MySQL為排序開辟的內存（sort_buffer）的大小。若待排序數據量太大，內存放不下，則需利用磁盤臨時文件輔助排序。

產品又開始炫技了，又問到：

order by語句何時會使用臨時文件？

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, name,age from citizen where city='上海' order by name limit 1000; /* 查看 OPTIMIZER_TRACE 輸出 */ SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G /* 使用 @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 結果中的 number_of_tmp_files 字段確認是否使用臨時文件。

"filesort_execution": [ ], "filesort_summary": { "rows": 4000 "examined_rows": 4000, "number_of_tmp_files": 12, "sort_buffer_size": 32664 , "sort_mode": ""

number_of_tmp_files

排序過程中使用的臨時文件數。為啥需要12個文件？內存放不下時，就需要使用外部排序，外部排序一般使用歸并排序。

MySQL將需要排序的數據分成12份，每一份單獨排序后存在這些臨時文件中。然后把這12個有序文件再合并成一個有序的大文件。

若 sort_buffer_size 超過需排序的數據量大小，則 number_of_tmp_files 就是0，即排序可直接在內存完成。

否則就需要放在臨時文件中排序。sort_buffer_size越小，需要分成的份數越多，number_of_tmp_files的值就越大。

examined_rows

參與排序的行數。測試表有4000條滿足city='上海’的記錄，所以該參數為4000。

sort_mode 的packed_additional_fields

排序過程對字符串做了“緊湊”處理。即使name字段的定義是varchar(16)，在排序過程中還是要按實際長度分配空間。

select @b-@a 的結果4000，即整個執行過程只掃描了4000行。

為避免對結論造成干擾，我把internal_tmp_disk_storage_engine設置成MyISAM。否則，select @b-@a的結果會顯示為4001。

因為查詢OPTIMIZER_TRACE表時，需要用到臨時表，而internal_tmp_disk_storage_engine的默認值是InnoDB。若使用InnoDB，把數據從臨時表取出時，會讓Innodb_rows_read的值加1。

rowid排序

上面的算法，只是讀了一遍原表數據，剩下的操作都是在sort_buffer和臨時文件中執行。

這就存在問題：若查詢要返回的字段很多，則：

sort_buffer要放的字段數就會很多=》

內存能放下的行數就會變少=》

就要分成很多臨時文件=》

排序性能就會很差。

所以若單行很大，該算法的效率可不夠行哦。

產品老大又開始發難，那你知道若MySQL認為排序的單行長度太大，它會咋樣？

現在修改個參數，讓MySQL采用另外一種算法。

SET max_length_for_sort_data = 16;

max_length_for_sort_data

MySQL用于控制用于排序的行數據的長度。若單行長度超過該值，MySQL就認為單行太大，要換個算法。

city、name、age 三字段的定義總長度36，那你看我把max_length_for_sort_data設為16會咋樣？

新的算法放入sort_buffer的字段，只有待排序列（name字段）和主鍵id。

但這時，排序的結果就因少了city和age字段值，不能直接返回了，整個執行流程變成如下：

初始化sort_buffer，確定放入兩個字段，即name和id

從city找到第一個滿足 city=上海 的主鍵id，即id_x

到id取出整行，取name、id這倆字段，存入sort_buffer

從city取下一個記錄的主鍵id

重復3、4，直到不滿足city=上海，即id_y

對sort_buffer數據按name排序

遍歷排序結果，取前1000行，并按照id的值回到原表中取出city、name和age三個字段返回給client

聽到這里，感覺明白了一些：產品你別急，你看我畫下這個rowid排序執行過程的示意圖，對不對？

你看這個和你之前畫的全字段排序示意圖對比，其實就是多訪問了一次表citizen的主鍵索引，即step7。

resultSet是個邏輯概念，實際上MySQL服務端從排序后的sort_buffer中依次取出id，然后到原表查到city、name和age這三字段的結果，無需在服務端再耗費內存存儲結果，而是直接返回給client。

這時查看rowid排序的OPTIMIZER_TRACE結果：

"filesort_execution": [ ], "filesort_summary": { "rows": 4000 "examined_rows": 4000, "number_of_tmp_files": 10, "sort_buffer_size": 32728 , "sort_mode": ""

select @b-@a結果變成5000

因為這時除了排序過程，在排序完成后，還要根據id去原表取值。由于語句是limit 1000，因此會多讀1000行。

sort_mode 變成了

表示參與排序的只有name和id字段

number_of_tmp_files 變成10

因為這時參與排序的行數雖然還是4000，但每行都變小了，因此需排序的總數據量就小了，需要的臨時文件也就少咯。

產品最后總結到：

若MySQL認為排序內存太小，會影響排序效率，就會采用rowid排序

這樣排序過程中一次可以排序更多行，但最后需要回表取數據

若MySQL認為內存夠大，會優先選擇全字段排序

把需要字段都放到sort_buffer，這樣排序后就直接從內存返回查詢結果，不用回表。

所以MySQL就是：若內存夠，就多利用內存，盡量減少磁盤訪問。

對InnoDB，rowid排序會要求回表，多造成了磁盤讀，因此不會被優先選擇。

所以MySQL排序是個高成本操作。

是不是所有order by都需排序呢？若不排序就能得到正確的結果，那對系統的消耗會小很多，語句的執行時間也會變得更短。

并非所有order by都需排序操作。MySQL之所以需要生成臨時表，并且在臨時表上做排序，是因為原來的數據都是無序的。

如果能保證從city索引上取出來的行，天生就是按name遞增排序，是不是就可以不用再排序了？

是的。

所以可以創建一個city,name聯合索引：

alter table t add index citizen(city, name);

該索引的示意圖

依然可以用樹搜索定位到第一個滿足city='上海’的記錄，并且能確保接下來按順序取“下一條記錄”的遍歷過程，只要city是上海，name值一定有序。

這樣整個查詢過程的流程就變成：

從索引(city,name)找到第一個滿足city='上海’條件的主鍵id

到主鍵id索引取出整行，取name、city、age三個字段的值，作為結果集的一部分直接返回

從索引(city,name)取下一個記錄主鍵id

重復步驟2、3，直到查到第1000條記錄，或者是不滿足city='上海’條件時循環結束

引入(city,name)聯合索引后，查詢語句的執行計劃

可見，該查詢過程無需臨時表，也無需排序。

使用 explain 查看(city,name)聯合索引，查詢語句的執行計劃

explain select city, name, age from citizen where city = '上海' order by name limit 1000; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | citizen | NULL | ref | city,name | name | 51 | const | 4000 | 100.00 | Using index condition | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)

可見Extra字段中沒有Using filesort了，也就是不需要排序了。而且由于(city,name)這個聯合索引本身有序，所以該查詢也不用把4000行全都讀一遍，只要找到滿足條件的前1000條記錄即可退出。在這個例子里，只需掃描1000次。

該語句的執行流程有沒有可能進一步簡化呢？

覆蓋索引

索引上的信息足夠滿足查詢請求，不需要再回到主鍵索引上去取數據。

按覆蓋索引，可以再優化一下這個查詢語句的執行流程。

針對這個查詢，可以創建一個city、name和age的聯合索引，對應的SQL語句就是：

alter table t add index city_user_age(city, name, age);

這時，對于city字段的值相同的行來說，還是按照name字段的值遞增排序的，此時的查詢語句也就不再需要排序了。這樣整個查詢語句的執行流程就變成了：

從索引(city,name,age)找到第一個滿足city='上海’條件的記錄，取出其中的city、name和age這三個字段的值，作為結果集的一部分直接返回

從索引(city,name,age)取下一個記錄，同樣取出這三個字段的值，作為結果集的一部分直接返回

重復2，直到查到第1000條記錄或不滿足city=‘上海’

引入 (city,name,age) 聯合索引，查詢語句的執行流程

explain查看(city,name,age)聯合索引查詢語句的執行計劃

explain select city, name, age from citizen where city = '上海' order by name limit 1000; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | citizen | NULL | ref | city,name,age | age | 51 | const | 4000 | 100.00 | Using where; Using index | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)

Extra字段里面多了“Using index”，說明使用了覆蓋索引，性能上會快很多。

但這并非說要為了每個查詢能用上覆蓋索引，就要把語句中涉及的字段都建上聯合索引，畢竟索引也很占空間，而且修改新增都會導致索引改變，還是具體業務場景具體分析。

參考

“order by”是怎么工作的？

https://blog.csdn.net/Linuxhus/article/details/112672434?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-0&spm=1001.2101.3001.4242

天天寫order by，你知道Mysql底層執行原理嗎？

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。