一、全字段排序：
1、
??explain命令中顯示的Extra這個(gè)字段中的“Using filesort”表示的就是需要排序，MySQL會(huì)給每個(gè)線程分配一塊內(nèi)存用于排序，稱為sort_buffer。
2、“select city,name,age from t where city='杭州' order by name limit 1000 ;”語句全字段排序執(zhí)行流程如下：
??1、初始化sort_buffer，確定放入name、city、age這三個(gè)字段；
??2、從索引city找到第一個(gè)滿足city='杭州’條件的主鍵id。
??3、到主鍵id索引取出整行，取name、city、age三個(gè)字段的值，存入sort_buffer中；
??4、從索引city取下一個(gè)記錄的主鍵id；
??5、重復(fù)步驟3、4直到city的值不滿足查詢條件為止；
??6、對(duì)sort_buffer中的數(shù)據(jù)按照字段name做快速排序；
??7、按照排序結(jié)果取前1000行返回給客戶端。
3、快速排序這個(gè)動(dòng)作，可能在內(nèi)存中完成，也可能需要使用外部排序，這取決于排序所需的內(nèi)存和參數(shù)sort_buffer_size。
(1)、“sort_buffer_size”含義：
??表示的就是MySQL為排序開辟的內(nèi)存（sort_buffer）的大小。如果要排序的數(shù)據(jù)量小于sort_buffer_size，排序就在內(nèi)存中完成。如果排序數(shù)據(jù)量太大，內(nèi)存放不下，則利用磁盤臨時(shí)文件輔助排序。
4、查看一個(gè)排序語句是否使用了臨時(shí)文件的方法：
??(1)、可以通過查看 OPTIMIZER_TRACE 的結(jié)果來確認(rèn)的，從 “number_of_tmp_files”中看到是否使用了臨時(shí)文件?！皀umber_of_tmp_files”表示的是，排序過程中使用的臨時(shí)文件數(shù)。內(nèi)存放不下時(shí)，就需要使用外部排序，外部排序一般使用歸并排序算法。假設(shè)“number_of_tmp_files” = X。可以這么簡單理解，MySQL將需要排序的數(shù)據(jù)分成X份，每一份單獨(dú)排序后存在這些臨時(shí)文件中。然后把這X個(gè)有序文件再合并成一個(gè)有序的大文件。如果“sort_buffer_size”超過了需要排序的數(shù)據(jù)量的大小，“number_of_tmp_files”就是0，表示排序可以直接在內(nèi)存中完成。否則就需要放在臨時(shí)文件中排序。“sort_buffer_size”越小，需要分成的份數(shù)越多，“number_of_tmp_files”的值就越大。
??(2)、查詢語句：
/* 打開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í)行語句 */
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;
5、 OPTIMIZER_TRACE 結(jié)果含義：
(1)、“number_of_tmp_files”表示的是，排序過程中使用的臨時(shí)文件數(shù)
(2)、 “examined_rows”表示參與排序的行數(shù)。
(3)、“sort_mode”里面的“packed_additional_fields”的意思是，排序過程對(duì)字符串做了“緊湊”處理。在排序過程中要按照實(shí)際長度來分配空間。
(4)、語句“select @b-@a” 的返回結(jié)果表示整個(gè)執(zhí)行過程只掃描了多少行。
這里需要注意的是，為了避免對(duì)結(jié)論造成干擾，把“internal_tmp_disk_storage_engine”設(shè)置成MyISAM。否則，select @b-@a的結(jié)果會(huì)顯示為4001。這是因?yàn)?strong>查詢OPTIMIZER_TRACE這個(gè)表時(shí)，需要用到臨時(shí)表，而“internal_tmp_disk_storage_engine”的默認(rèn)值是InnoDB。如果使用的是InnoDB引擎的話，把數(shù)據(jù)從臨時(shí)表取出來的時(shí)候，會(huì)讓Innodb_rows_read的值加1。
二、rowid排序：
1、“max_length_for_sort_data”含義：
??是MySQL中專門控制用于排序的行數(shù)據(jù)的長度的一個(gè)參數(shù)。它的意思是，如果單行的長度超過這個(gè)值，MySQL就認(rèn)為單行太大，要換一個(gè)算法。新的算法放入sort_buffer的字段，只有要排序的列和主鍵。
2、“select city,name,age from t where city='杭州' order by name limit 1000 ;”語句rowid排序執(zhí)行流程如下：
??1、初始化sort_buffer，確定放入兩個(gè)字段，即name和id；
??2、從索引city找到第一個(gè)滿足city='杭州’條件的主鍵id；
??3、到主鍵id索引取出整行，取name、id這兩個(gè)字段，存入sort_buffer中；
??4、從索引city取下一個(gè)記錄的主鍵id；
??5、重復(fù)步驟3、4直到不滿足city='杭州’條件為止；
??6、對(duì)sort_buffer中的數(shù)據(jù)按照字段name進(jìn)行排序；
??7、遍歷排序結(jié)果，取前1000行，并按照id的值回到原表中取出city、name和age三個(gè)字段返回給客戶端。
?注意：
??①:排序的結(jié)果因?yàn)樯倭薱ity和age字段的值，不能直接返回，需要按照id回原表取值再返回給客戶端。
??②:“結(jié)果集”是一個(gè)邏輯概念，實(shí)際上MySQL服務(wù)端從排序后的sort_buffer中依次取出id，然后到原表查到city、name和age這三個(gè)字段的結(jié)果，不需要在服務(wù)端再耗費(fèi)內(nèi)存存儲(chǔ)結(jié)果，是直接返回給客戶端的。
??③:此時(shí)“select @b-@a”這個(gè)語句的值就變成5000了。因?yàn)檫@時(shí)候除了排序過程外，在排序完成后，還要根據(jù)id去原表取值。由于語句是“l(fā)imit 1000”，因此會(huì)多讀1000行。
??④:sort_mode變成了<sort_key, rowid>，表示參與排序的只有name和id這兩個(gè)字段。
??⑤:number_of_tmp_files變成10了，是因?yàn)檫@時(shí)候參與排序的行數(shù)雖然仍然是4000行，但是每一行都變小了，因此需要排序的總數(shù)據(jù)量就變小了，需要的臨時(shí)文件也相應(yīng)地變少了。
三、全字段排序和rowid排序的對(duì)比：
??1、如果MySQL擔(dān)心排序內(nèi)存太小，會(huì)影響排序效率，才會(huì)采用rowid排序算法，這樣排序過程中一次可以排序更多行，但是需要再回到原表去取數(shù)據(jù)。
??2、如果MySQL認(rèn)為內(nèi)存足夠大，會(huì)優(yōu)先選擇全字段排序，把需要的字段都放到sort_buffer中，這樣排序后就會(huì)直接從內(nèi)存里面返回查詢結(jié)果了，不用再回到原表去取數(shù)據(jù)。
??3、這體現(xiàn)了MySQL的一個(gè)設(shè)計(jì)思想：如果內(nèi)存夠，就要多利用內(nèi)存，盡量減少磁盤訪問。
??4、對(duì)于InnoDB表來說，rowid排序會(huì)要求回表多造成磁盤讀，因此不會(huì)被優(yōu)先選擇。
四、排序的優(yōu)化：
1、保證從city這個(gè)索引上取出來的行，天然就是按照name遞增排序的話，就可以不用再排序了。
(1)、所以可以在這個(gè)表上創(chuàng)建一個(gè)city和name的聯(lián)合索引，對(duì)應(yīng)的SQL語句是：
alter table t add index city_user(city, name);
(2)、此時(shí)的查詢流程：
??1、從索引(city,name)找到第一個(gè)滿足city='杭州’條件的主鍵id；
??2、到主鍵id索引取出整行，取name、city、age三個(gè)字段的值，作為結(jié)果集的一部分直接返回；
??3、從索引(city,name)取下一個(gè)記錄主鍵id；
??4、重復(fù)步驟2、3，直到查到第1000條記錄，或者是不滿足city='杭州’條件時(shí)循環(huán)結(jié)束。
?注意：①:這個(gè)查詢過程不需要臨時(shí)表，也不需要排序。
????②:這個(gè)查詢也不用把4000行全都讀一遍，只要找到滿足條件的前1000條記錄就可以退出了。
2、進(jìn)一步的，采用覆蓋索引：
(1)、對(duì)這個(gè)查詢，我們可以創(chuàng)建一個(gè)city、name和age的聯(lián)合索引，對(duì)應(yīng)的SQL語句就是：
alter table t add index city_user_age(city, name, age);
(2)、此時(shí)的查詢流程：
??1、從索引(city,name,age)找到第一個(gè)滿足city='杭州’條件的記錄，取出其中的city、name和age這三個(gè)字段的值，作為結(jié)果集的一部分直接返回；
??2、從索引(city,name,age)取下一個(gè)記錄，同樣取出這三個(gè)字段的值，作為結(jié)果集的一部分直接返回；
??3、重復(fù)執(zhí)行步驟2，直到查到第1000條記錄，或者是不滿足city='杭州’條件時(shí)循環(huán)結(jié)束。
?注意：①:此時(shí)的explain的結(jié)果中的Extra字段里面多了“Using index”，表示的就是使用了覆蓋索引，性能上會(huì)快很多。
????②:并不是說要為了每個(gè)查詢能用上覆蓋索引，就要把語句中涉及的字段都建上聯(lián)合索引，畢竟索引還是有維護(hù)代價(jià)的。這是一個(gè)需要權(quán)衡的決定。
