建表語句
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;
執(zhí)行sql
select city,name,age from t where city='杭州' order by name limit 1000 ;
一、全字段排序的原理
用explain sql語句會得到下圖
圖中的Extra中的 Using filesort表示需要排序抖苦，MySQL會給每個線程分配一塊內(nèi)存用作排序叫做sort_buffer
1.上述排序sql在用全字段排序的流程
（1）初始化sort_buffer静陈，確定可以放入city击吱、name篮条、age三個字段；
（2）根據(jù)索引city性宏，找到第一個city=‘杭州’的主鍵id；
（3）根據(jù)主鍵id找到該行數(shù)據(jù)状飞，返回city毫胜、name、age到sort_buffer中昔瞧；
（4）依次尋找下一個主鍵id指蚁；
（5）循環(huán)執(zhí)行步驟3、4自晰，直到city != '杭州'為止凝化；
（6）把sort_buffer的數(shù)據(jù)根據(jù)name做快速排序；
（7）按照結果返回1000行給客戶端酬荞；
2.在MySQL中根據(jù)字段排序是在哪里進行的搓劫？
排序既可能只在內(nèi)存中進行瞧哟，也可能內(nèi)存和磁盤同時進行；
在哪里進行是由什么決定的枪向？
sort_buffer_size：排序時MySQL開辟的內(nèi)存大星诳；
如果sort_buffer_size大于需要排序參數(shù)的內(nèi)存秘蛔，就只在內(nèi)存排序陨亡；反之，就既在內(nèi)存排序深员，也會用磁盤空間輔助進行负蠕；
怎么看MySQL是否使用了磁盤空間輔助排序呢？
/* 打開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的當前值 */
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表示什么意思遮糖？
理解為什么是12，因為外部排序一般會使用歸并排序的算法叠赐，MySQL把需要排序的數(shù)據(jù)分成12份欲账，把每份單獨排序后存入磁盤的12個文件，最后合并成一個文件返回給客戶端芭概。
如果number_of_tmp_files的值為0赛不，則表示排序只在內(nèi)存進行。
examined_rows=4000表示什么意思谈山？
examined_rows=4000俄删，表示參與排序的行數(shù)是 4000 行
sort_mode 里面的 packed_additional_fields 的意思是？
說明排序的過程中對字符串做了緊湊處理奏路；按字符串的實際長度來分配空間畴椰。
select @b-@a 的返回結果是 4000表示什么？
表示整個執(zhí)行過程掃描的行數(shù)
怎么把internal_tmp_disk_storage_engine 設置成 MyISAM鸽粉？
internal_tmp_disk_storage_engine 這個參數(shù)是用來空值MySQL內(nèi)部在創(chuàng)建臨時表的時候斜脂，使用的默認存儲引擎是什么，這個參數(shù)可以配置為MyISAM或者InnoDB兩個触机。 5.7之后帚戳，默認的參數(shù)為InnoDB。
為什么InnoDB引擎select @b-@a 的返回結果是 4001儡首？
因為查詢 OPTIMIZER_TRACE 這個表時片任，需要用到臨時表，而 internal_tmp_disk_storage_engine 的默認值是 InnoDB蔬胯。如果使用的是 InnoDB 引擎的話对供，把數(shù)據(jù)從臨時表取出來的時候，會讓 Innodb_rows_read 的值加 1。
二产场、rowid排序
rowid排序會限制排序時單條數(shù)數(shù)據(jù)字段的總長度：例如
SET max_length_for_sort_data = 16鹅髓；
意思就是如果返回的單條字段長度大于16，那么只會返回需要排序的字段和主鍵京景；以上例子中只會返回name窿冯、id；
1.rowid排序的執(zhí)行流程和全字段排序流程有什么區(qū)別确徙？
rowid排序的流程比全字段排序醒串，在數(shù)據(jù)庫取字段的時候只取name、id米愿；在排完序后厦凤，還會再到表里根據(jù)主鍵再查一次鼻吮，最終把結果返回給客戶端育苟。
2.為什么select @b-@a 這個語句的值變成 5000 了？
因為rowid排序比全字段排序多了回表里查詢的過程椎木。由于limit是1000违柏，所以多了1000。
3.sort_mode 變成了 <sort_key, rowid>香椎？
因為只用了這兩個字段漱竖。
4.number_of_tmp_files 變成 10 了？
因為需要進行排序的字段小了畜伐，所需要分配的空間也變小了馍惹。
三、全字段排序和rowid排序的比較玛界？
MySQL的原則是能盡量使用內(nèi)存排序就不要用磁盤万矾。如果實在是覺得內(nèi)存不夠用了才會用rowid排序，因為rowid排序會有增加的讀盤操作慎框，所以不推薦使用良狈。
四、優(yōu)化方案
1.優(yōu)化方案1笨枯，使用city薪丁、name的聯(lián)合索引
使用該方案的時候，只需要查找到滿足city='杭州'的記錄即可馅精，不需要進行排序严嗜，最后再用主鍵進行查找<=1000條的記錄即可。
Extra 字段中沒有 Using filesort 了漫玄，也就是不需要排序了
2.進一步優(yōu)化方案，使用city沦疾、name称近、age的聯(lián)合索引
這個時候用到了覆蓋索引的優(yōu)化方案第队，覆蓋索引的概念是索引就已經(jīng)滿足查詢，不需要在回到主鍵索引去取數(shù)據(jù)刨秆。
Extra 字段里面多了“Using index”，表示的就是使用了覆蓋索引
五衡未、思考題
假設你的表里面已經(jīng)有了 city_name(city, name) 這個聯(lián)合索引尸执，然后你要查杭州和蘇州兩個城市中所有的市民的姓名，并且按名字排序缓醋，顯示前 100 條記錄如失。如果 SQL 查詢語句是這么寫的 ：
mysql> select * from t where city in ('杭州',"蘇州") order by name limit 100;
那么，這個語句執(zhí)行的時候會有排序過程嗎送粱，為什么褪贵？如果業(yè)務端代碼由你來開發(fā)，需要實現(xiàn)一個在數(shù)據(jù)庫端不需要排序的方案抗俄，你會怎么實現(xiàn)呢脆丁？進一步地，如果有分頁需求动雹，要顯示第 101 頁槽卫，也就是說語句最后要改成 “l(fā)imit 10000,100”， 你的實現(xiàn)方法又會是什么呢胰蝠？
