MySQL中order by排序語句的原理解析

order by 是怎麼工作的？

表定義

CREATE TABLE `t1` (  
	`id` int(11) NOT NULL,  
	`city` varchar(16) NOT NULL,  
	`name` varchar(16) NOT NULL,  
	`age` int(11) NOT NULL,  
	`addr` varchar(128) DEFAULT NULL,  
	PRIMARY KEY (`id`),  
	KEY `city` (`city`)) ENGINE=InnoDB;

SQL語句可以這樣寫：

select city,name,age from t1 where city='杭州' order by name limit 1000

全欄位排序

用 explain 命令來看看這個語句的執行情況。

其中Using index condition是索引下推優化(索引下推簡介)，Using filesort 表示的就是需要排序，MySQL 會給每個執行緒分配一塊記憶體用於排序，稱為 sort_buffer。

city索引的示意圖。

從圖中可以看到，滿足 city='杭州’條件的行，是從 ID_X 到 ID_(X+N) 的這些記錄。

通常情況下，這個語句執行流程如下所示 ：

• 初始化 sort_buffer，確定放入 name、city、age 這三個欄位；
• 從索引 city 找到第一個滿足 city='杭州’條件的主鍵 id，也就是圖中的 ID_X；
• 到主鍵 id 索引取出整行，取 name、city、age 三個欄位的值，存入 sort_buffer 中；
• 從索引 city 取下一個記錄的主鍵 id；
• 重複步驟 3、4 直到 city 的值不滿足查詢條件為止，對應的主鍵 id 也就是圖中的 ID_Y；
• 對 sort_buffer 中的資料按照欄位 name 做快速排序；
• 按照排序結果取前 1000 行返回給使用者端。

這個是它的排序過程，叫做全欄位排序，執行流程示意圖如下所示。

按 name 排序”這個動作，可能在記憶體中完成，也可能需要使用外部排序，這取決於排序所需的記憶體和引數 sort_buffer_size。

sort_buffer_size，就是 MySQL 為排序開闢的記憶體（sort_buffer）的大小。

• 要排序的資料量小於 sort_buffer_size，排序就在記憶體中完成。
• 要排序資料量太大，記憶體放不下，利用磁碟臨時檔案輔助排序。

可以用下面介紹的方法，來確定一個排序語句是否使用了臨時檔案。

/* 開啟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, name,age from t 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 中看到是否使用了臨時檔案。

number_of_tmp_files 表示的是，排序過程中使用的臨時檔案數。

為什麼需要12個檔案呢？

外部排序一般使用歸併排序演演算法。可以這麼簡單理解，MySQL 將需要排序的資料分成 12 份，每一份單獨排序（快速排序）後存在這些臨時檔案中。然後把這 12 個有序檔案再合併成一個有序的大檔案。

小結：

如果 sort_buffer_size 超過了需要排序的資料量的大小，number_of_tmp_files 就是 0，表示排序可以直接在記憶體中完成。sort_buffer_size 越小，需要分成的份數越多，number_of_tmp_files 的值就越大。

rowid排序

在上面這個演演算法過程裡面，只對原表的資料讀了一遍，剩下的操作都是在 sort_buffer 和臨時檔案中執行的。但這個演演算法有一個問題，就是如果查詢要返回的欄位很多的話，那麼 sort_buffer 裡面要放的欄位數太多，這樣記憶體裡能夠同時放下的行數很少，要分成很多個臨時檔案，排序的效能會很差。

如果當行很大，這個全欄位排序並不是很好。

SET max_length_for_sort_data = 16;

這個語句的意思是：如果單行太大，超過所設定的數值的時候，比如現在是超過16，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 三個欄位返回給使用者端。

這個執行流程的示意圖如下，叫做 rowid 排序。

對比全欄位排序流程圖發現，rowid 排序多存取了一次表 t 的主鍵索引，就是步驟 7。

注意：最後的**“結果集”是一個邏輯概念，實際上 MySQL 伺服器端從排序後的 sort_buffer 中依次取出 id，然後到原表查到 city、name 和 age 這三個欄位的結果，不需要在伺服器端再耗費記憶體儲存結果，是直接返回給使用者端的**。

全欄位排序和rowid排序應該如何去選擇呢？

如果 MySQL 實在是擔心排序記憶體太小，會影響排序效率，才會採用 rowid 排序演演算法，這樣排序過程中一次可以排序更多行，但是需要再回到原表去取資料。

如果 MySQL 認為記憶體足夠大，會優先選擇全欄位排序，把需要的欄位都放到 sort_buffer 中，這樣排序後就會直接從記憶體裡面返回查詢結果了，不用再回到原表去取資料。

這也就體現了 MySQL 的一個設計思想：如果記憶體夠，就要多利用記憶體，儘量減少磁碟存取。對於 InnoDB 表來說，rowid 排序會要求回表多造成磁碟讀，因此不會被優先選擇。

其實以上說的都是無序的時候，如果在條件有索引，索引中資料是有序的，省掉了上述步驟，直接在索引上找到主鍵id，然後回表找到要查詢的資料直接返回給使用者端。

到此這篇關於MySQL中order by排序語句的原理的文章就介紹到這了,更多相關MySQL中order by排序語句的原理內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！