建表語句

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語句會得到下圖

分析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）按照結(jié)果返回1000行給客戶端；

2.在MySQL中根據(jù)字段排序是在哪里進(jìn)行的？

排序既可能只在內(nèi)存中進(jìn)行，也可能內(nèi)存和磁盤同時進(jìn)行；

在哪里進(jìn)行是由什么決定的？
sort_buffer_size：排序時MySQL開辟的內(nèi)存大?。?br> 如果sort_buffer_size大于需要排序參數(shù)的內(nèi)存，就只在內(nèi)存排序；反之，就既在內(nèi)存排序，也會用磁盤空間輔助進(jìn)行；

怎么看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的當(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這步可以看出下圖：

OPTIMIZER_TRACE.png

examined_rows=4000表示什么意思？
examined_rows=4000，表示參與排序的行數(shù)是 4000 行

sort_mode 里面的 packed_additional_fields 的意思是？
說明排序的過程中對字符串做了緊湊處理；按字符串的實際長度來分配空間。

select @b-@a 的返回結(jié)果是 4000表示什么？
表示整個執(zhí)行過程掃描的行數(shù)

怎么把internal_tmp_disk_storage_engine 設(shè)置成 MyISAM？
internal_tmp_disk_storage_engine 這個參數(shù)是用來空值MySQL內(nèi)部在創(chuàng)建臨時表的時候，使用的默認(rèn)存儲引擎是什么，這個參數(shù)可以配置為MyISAM或者InnoDB兩個。 5.7之后，默認(rèn)的參數(shù)為InnoDB。

為什么InnoDB引擎select @b-@a 的返回結(jié)果是 4001？

因為查詢 OPTIMIZER_TRACE 這個表時，需要用到臨時表，而 internal_tmp_disk_storage_engine 的默認(rèn)值是 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ù)主鍵再查一次，最終把結(jié)果返回給客戶端。

2.為什么select @b-@a 這個語句的值變成 5000 了？

因為rowid排序比全字段排序多了回表里查詢的過程。由于limit是1000，所以多了1000。

3.sort_mode 變成了 <sort_key, rowid>？

因為只用了這兩個字段。

4.number_of_tmp_files 變成 10 了？

因為需要進(jìn)行排序的字段小了，所需要分配的空間也變小了。

三、全字段排序和rowid排序的比較？

MySQL的原則是能盡量使用內(nèi)存排序就不要用磁盤。如果實在是覺得內(nèi)存不夠用了才會用rowid排序，因為rowid排序會有增加的讀盤操作，所以不推薦使用。

四、優(yōu)化方案

1.優(yōu)化方案1，使用city、name的聯(lián)合索引

使用該方案的時候，只需要查找到滿足city='杭州'的記錄即可，不需要進(jìn)行排序，最后再用主鍵進(jìn)行查找<=1000條的記錄即可。

引入 (city,name) 聯(lián)合索引后，查詢語句的執(zhí)行計劃

Extra 字段中沒有 Using filesort 了，也就是不需要排序了

2.進(jìn)一步優(yōu)化方案，使用city、name、age的聯(lián)合索引

這個時候用到了覆蓋索引的優(yōu)化方案，覆蓋索引的概念是索引就已經(jīng)滿足查詢，不需要在回到主鍵索引去取數(shù)據(jù)。

引入 (city,name,age) 聯(lián)合索引后，查詢語句的執(zhí)行計劃

Extra 字段里面多了“Using index”，表示的就是使用了覆蓋索引

五、思考題

假設(shè)你的表里面已經(jīng)有了 city_name(city, name) 這個聯(lián)合索引，然后你要查杭州和蘇州兩個城市中所有的市民的姓名，并且按名字排序，顯示前 100 條記錄。如果 SQL 查詢語句是這么寫的 ：

    mysql> select * from t where city in ('杭州',"蘇州") order by name limit 100;

那么，這個語句執(zhí)行的時候會有排序過程嗎，為什么？如果業(yè)務(wù)端代碼由你來開發(fā)，需要實現(xiàn)一個在數(shù)據(jù)庫端不需要排序的方案，你會怎么實現(xiàn)呢？進(jìn)一步地，如果有分頁需求，要顯示第 101 頁，也就是說語句最后要改成 “l(fā)imit 10000,100”， 你的實現(xiàn)方法又會是什么呢？