order by 有兩種排序方式：全字段排序、rowid排序。
1、全字段排序
CREATE TABLE `t` (
? `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;
select city,name,age from t where city='杭州' order by name limit 1000 ;
以上語句在city上添加索引后，執(zhí)行以上的查詢語句的執(zhí)行過程如下：
1、初始化sort_buffer,確定放入name、city、age這三個字段；
2、從索引city找到第一個滿足city=‘杭州’ 條件的主鍵id；
3、到主鍵id索引取出整行，取name、city、age三個字段的值，存入sort_buffer中；
4、從索引city取下一個記錄的主鍵id；
5、重復(fù)步驟3、4直到city的值不滿足查詢條件為止；
6、對sort_buffer中的數(shù)據(jù)按照字段name做快速排序；
7、按照排序結(jié)果取前1000行返回給客戶端。
圖中，“按name排序”這個動作，可能在內(nèi)存中完成，也可能需要使用外部排序，這取決于排序所需要的內(nèi)存和參數(shù)sort_buffer_size.
sort_buffer_size,就是MySQL為排序開辟的內(nèi)存(sort_buffer)的大小。如果要排序的數(shù)據(jù)量小于sort_buffer_size,排序就在內(nèi)存中完成。但如果排序數(shù)據(jù)量太大，內(nèi)存放不下，則不得不利用磁盤臨時文件輔助排序。
可以用以下的方法來確定是否使用了臨時排序：
/* 打開 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';
/* 執(zhí)行語句 */
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';
/* 計算 Innodb_rows_read 差值 */
select @b-@a;
這個方法是通過查看OPTIMIZER_TRACE的結(jié)果來確認的，從number_of_tmp_files中看到是否使用了臨時文件。number_of_tmp_files表示的是，排序過程中使用的臨時文件數(shù)。
內(nèi)存放不下時，就需要使用外部排序，外部排序一般使用歸并排序算法。上圖的結(jié)果中可以簡單理解：MySQL將需要排序的數(shù)據(jù)分成12份，每一份單獨排序后存在這些臨時文件中。然后把這12個有序文件再合并成一個有序的大文件。
如果sort_buffer_size超過了需要排序的數(shù)據(jù)量的大小，number_of_tmp_files就是0，表示排序可以直接在內(nèi)存中完成。否則就需要放在臨時文件中排序。sort_buffer_size越小，需要分成的份數(shù)越多，number_of_tmp_files的值就越大。
rowid排序
在全字段排序算法里面，只對原表的數(shù)據(jù)讀了一遍，剩下的操作都是在sort_buffer和臨時文件中執(zhí)行的。但這個算法有一個問題，就是如果查詢要返回的字段很多的話，那么sort_buffer里面要放的字段數(shù)太多，這樣內(nèi)存里能夠同時放下的行數(shù)很少，要分成很多個臨時文件，排序的性能會很差。
如果單行很大，這個方法效率不夠好，那么如果MySQL認為排序的單行長度太大會怎么做尼？
SET max_length_for_sort_data = 16;
max_length_for_sort_data,是MySQL中專門控制用于排序的行數(shù)據(jù)的長度的一個參數(shù)。它的意思是，如果單行的長度超過這個值，MySQL就認為單行太大，要換一個算法。
city、name、age這三個字段的定義總長度是36，把max_length_for_sort_data設(shè)置為16，我們再來看看計算過程有什么改變。
新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主鍵id。但這時，排序的結(jié)果就因為少了city和age字段的值，不能直接返回了，整個執(zhí)行流程就變成如下所示的樣子：
1、初始化sort_buffer,確定放入兩個字段，即name和id；
2、從索引city找到第一個滿足city=‘杭州’ 條件的主鍵id；
3、到主鍵id索引取出整行，取name、id這兩個字段，存入sort_buffer中；
4、從索引city取下一個記錄的主鍵id；
5、重復(fù)步驟3、4直到不滿足city=‘杭州’ 條件為止；
6、對sort_buffer中的數(shù)據(jù)按照字段name進行排序；
7、遍歷排序結(jié)果，取前1000行，并按照id的值回到原表中取出city、name和age三個字段返回給客戶端。
對比全字段排序流程圖，rowid排序多訪問了一次表t的主鍵索引，就是步驟7.
需要說明的是，最后的“結(jié)果集”是一個邏輯概念，實際上MySQL服務(wù)端從排序后的sort_buffer中依次取出id，然后到原表查到city、name和age這三個字段的結(jié)果，不需要在服務(wù)端再耗費內(nèi)存存儲結(jié)果，是直接返回給客戶端的。
全字段排序與rowid排序的區(qū)別
如果mysql的排序內(nèi)存內(nèi)存太小，會影響排序效率，才會采用rowid排序算法，這樣排序過程中一次可以排序更多行，但是需要再回到原表去取數(shù)據(jù)；
如果mysql內(nèi)存足夠大，會優(yōu)先選擇全字段排序，把需要的字段都放到sort_buffer中，這樣排序后就會直接從內(nèi)存里面返回查詢結(jié)果了，不用再回到原表去取數(shù)據(jù)。
對于InnoDB表來說，rowid排序會要求回表多造成磁盤讀，因此不會被優(yōu)先選擇。
是否所有的order by都需要排序操作？
并非所有的order by語句，都需要排序操作的，mysql之所以需要生成臨時表，并且在臨時表上做排序操作，其原因是原來的數(shù)據(jù)都是無序的。
如果能夠保證從city這個索引上取出來的行，天然就是按照name遞增排序的話，是可以不用再排序。
alter table t add index city_user_age(city, name, age);
select city, name,age from t where city='杭州' order by name limit 1000;
以上的查詢在city, name, age字段上建立了聯(lián)合索引，所以在查詢只有city, name, age字段的查詢語句中，會使用到覆蓋索引，因此這條語句是不需要做排序了的。
覆蓋索引是指，索引上的信息足夠滿足查詢的請求，不需要再回到主鍵索引上去取數(shù)據(jù)。
