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語(yǔ)句可以這樣寫(xiě)：

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

全字段排序

用 explain 命令來(lái)看看這個(gè)語(yǔ)句的執(zhí)行情況。

其中Using index condition是索引下推優(yōu)化(索引下推簡(jiǎn)介)，Using filesort 表示的就是需要排序，MySQL 會(huì)給每個(gè)線程分配一塊內(nèi)存用于排序，稱(chēng)為 sort_buffer。

city索引的示意圖。

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

通常情況下，這個(gè)語(yǔ)句執(zhí)行流程如下所示 ：

• 初始化 sort_buffer，確定放入 name、city、age 這三個(gè)字段；
• 從索引 city 找到第一個(gè)滿(mǎn)足 city='杭州’條件的主鍵 id，也就是圖中的 ID_X；
• 到主鍵 id 索引取出整行，取 name、city、age 三個(gè)字段的值，存入 sort_buffer 中；
• 從索引 city 取下一個(gè)記錄的主鍵 id；
• 重復(fù)步驟 3、4 直到 city 的值不滿(mǎn)足查詢(xún)條件為止，對(duì)應(yīng)的主鍵 id 也就是圖中的 ID_Y；
• 對(duì) sort_buffer 中的數(shù)據(jù)按照字段 name 做快速排序；
• 按照排序結(jié)果取前 1000 行返回給客戶(hù)端。

這個(gè)是它的排序過(guò)程，叫做全字段排序，執(zhí)行流程示意圖如下所示。

按 name 排序”這個(gè)動(dòng)作，可能在內(nèi)存中完成，也可能需要使用外部排序，這取決于排序所需的內(nèi)存和參數(shù) sort_buffer_size。

sort_buffer_size，就是 MySQL 為排序開(kāi)辟的內(nèi)存（sort_buffer）的大小。

• 要排序的數(shù)據(jù)量小于 sort_buffer_size，排序就在內(nèi)存中完成。
• 要排序數(shù)據(jù)量太大，內(nèi)存放不下，利用磁盤(pán)臨時(shí)文件輔助排序。

可以用下面介紹的方法，來(lái)確定一個(gè)排序語(yǔ)句是否使用了臨時(shí)文件。

/* 打開(kāi)optimizer_trace，只對(duì)本線程有效 */
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';

/* 執(zhí)行語(yǔ)句 */
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的當(dāng)前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 計(jì)算Innodb_rows_read差值 */
select @b-@a;

這個(gè)方法是通過(guò)查看 OPTIMIZER_TRACE 的結(jié)果來(lái)確認(rèn)的，你可以從 number_of_tmp_files 中看到是否使用了臨時(shí)文件。

number_of_tmp_files 表示的是，排序過(guò)程中使用的臨時(shí)文件數(shù)。

為什么需要12個(gè)文件呢？

外部排序一般使用歸并排序算法?？梢赃@么簡(jiǎn)單理解，MySQL 將需要排序的數(shù)據(jù)分成 12 份，每一份單獨(dú)排序（快速排序）后存在這些臨時(shí)文件中。然后把這 12 個(gè)有序文件再合并成一個(gè)有序的大文件。

小結(jié)：

如果 sort_buffer_size 超過(guò)了需要排序的數(shù)據(jù)量的大小，number_of_tmp_files 就是 0，表示排序可以直接在內(nèi)存中完成。sort_buffer_size 越小，需要分成的份數(shù)越多，number_of_tmp_files 的值就越大。

rowid排序

在上面這個(gè)算法過(guò)程里面，只對(duì)原表的數(shù)據(jù)讀了一遍，剩下的操作都是在 sort_buffer 和臨時(shí)文件中執(zhí)行的。但這個(gè)算法有一個(gè)問(wèn)題，就是如果查詢(xún)要返回的字段很多的話，那么 sort_buffer 里面要放的字段數(shù)太多，這樣內(nèi)存里能夠同時(shí)放下的行數(shù)很少，要分成很多個(gè)臨時(shí)文件，排序的性能會(huì)很差。

如果當(dāng)行很大，這個(gè)全字段排序并不是很好。

SET max_length_for_sort_data = 16;

這個(gè)語(yǔ)句的意思是：如果單行太大，超過(guò)所設(shè)定的數(shù)值的時(shí)候，比如現(xiàn)在是超過(guò)16，MySQL就認(rèn)為單行太大，換一種算法。

city、name、age 這三個(gè)字段的定義總長(zhǎng)度是 36，我把 max_length_for_sort_data 設(shè)置為 16。

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

但這時(shí)，排序的結(jié)果就因?yàn)樯倭?city 和 age 字段的值，不能直接返回了（最后收集結(jié)果之前要回表），整個(gè)執(zhí)行流程就變成如下所示的樣子：

• 初始化 sort_buffer，確定放入兩個(gè)字段，即 name 和 id；
• 從索引 city 找到第一個(gè)滿(mǎn)足 city='杭州’條件的主鍵 id，也就是圖中的 ID_X；
• 到主鍵 id 索引取出整行，取 name、id 這兩個(gè)字段，存入 sort_buffer 中；
• 從索引 city 取下一個(gè)記錄的主鍵 id；
• 重復(fù)步驟 3、4 直到不滿(mǎn)足 city='杭州’條件為止，也就是圖中的 ID_Y；
• 對(duì) sort_buffer 中的數(shù)據(jù)按照字段 name 進(jìn)行排序；
• 遍歷排序結(jié)果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三個(gè)字段返回給客戶(hù)端。

這個(gè)執(zhí)行流程的示意圖如下，叫做 rowid 排序。

對(duì)比全字段排序流程圖發(fā)現(xiàn)，rowid 排序多訪問(wèn)了一次表 t 的主鍵索引，就是步驟 7。

注意：最后的**“結(jié)果集”是一個(gè)邏輯概念，實(shí)際上 MySQL 服務(wù)端從排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 這三個(gè)字段的結(jié)果，不需要在服務(wù)端再耗費(fèi)內(nèi)存存儲(chǔ)結(jié)果，是直接返回給客戶(hù)端的**。

全字段排序和rowid排序應(yīng)該如何去選擇呢？

如果 MySQL 實(shí)在是擔(dān)心排序內(nèi)存太小，會(huì)影響排序效率，才會(huì)采用 rowid 排序算法，這樣排序過(guò)程中一次可以排序更多行，但是需要再回到原表去取數(shù)據(jù)。

如果 MySQL 認(rèn)為內(nèi)存足夠大，會(huì)優(yōu)先選擇全字段排序，把需要的字段都放到 sort_buffer 中，這樣排序后就會(huì)直接從內(nèi)存里面返回查詢(xún)結(jié)果了，不用再回到原表去取數(shù)據(jù)。

這也就體現(xiàn)了 MySQL 的一個(gè)設(shè)計(jì)思想：如果內(nèi)存夠，就要多利用內(nèi)存，盡量減少磁盤(pán)訪問(wèn)。對(duì)于 InnoDB 表來(lái)說(shuō)，rowid 排序會(huì)要求回表多造成磁盤(pán)讀，因此不會(huì)被優(yōu)先選擇。

其實(shí)以上說(shuō)的都是無(wú)序的時(shí)候，如果在條件有索引，索引中數(shù)據(jù)是有序的，省掉了上述步驟，直接在索引上找到主鍵id，然后回表找到要查找的數(shù)據(jù)直接返回給客戶(hù)端。

到此這篇關(guān)于MySQL中order by排序語(yǔ)句的原理的文章就介紹到這了,更多相關(guān)MySQL中order by排序語(yǔ)句的原理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論