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排序語句的原理內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- MySQL中order by的執行過程
- 詳解MySQL中Order By排序和filesort排序的原理及實現
- Mysql數據庫group by原理詳解
- MySQL數據庫索引order by排序精講
- MySQL order by與group by查詢優化實現詳解