MySQL的 order by 工作原理

在程序設計當中，我們很多場景下都會用 group by 關鍵字。比如在分頁讀取數據時，為了避免重複掃描記錄，這就是必須要使用 group by 了。

比如我們使用如下 DDL 創建表：

CREATE TABLE `user_info` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
 `city` varchar(16) NOT NULL COMMENT '城市',
 `name` varchar(16) NOT NULL COMMENT '姓名',
 `age` int(11) NOT NULL COMMENT '年齡',
 `addr` varchar(128) DEFAULT NULL COMMENT '地址',
 PRIMARY KEY (`id`),
 KEY `city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

並且我們會執行如下查詢語句

SELECT city,`name`,age FROM user_info WHERE city='上海' ORDER BY `name` LIMIT 1000;

全字段排序

因為上面的建表語句已經在 city 字段上面創建索引了，當我們使用 EXPLAIN 命令時，會有如下結果：

上面 Extra 字段中的 “Using filesort” 表示的就是需要排序，MySQL 會為每個線程分配一塊內存用於排序，成為 sort_buffer。下面我們看一下 index(city) 的結構示意圖。

執行流程如下：

1. 初始化 sort_buffer，確定放入 city name age 這 3 個字段；
2. 從 city 索引中獲取到第一個 city='上海' 的記錄，也就是 id_x；
3. 到主鍵索引中獲取對應的記錄，並取出 name city age 的值放入 sort_buffer；
4. 取下一條符合條件的記錄，重複 3 4 的操作，直至不符合條件為止；
5. 對 sort_buffer 中的數據按照 name 做快速排序；
6. 取出前 1000 條數據並返回。

我們暫時叫這種排序過程為“全字段排序”，如下所示：

圖中的“按 name 排序” 可能在內存中，也可能使用磁盤文件排序，這取決與排序所需要的內存和 sort_buffer_size 。sort_buffer_size 就是 MySQL 為排序開闢的內存大小，當所需內存小於 sort_buffer_size 時，就直接在內存中完成排序，如果所需要的內存 大於 sort_buffer_size ，就需要額外的磁盤空間輔助排序。

rowid 排序

上面的算法在數據量比較大的時候，可能會出現一些問題。因為在排序的時候，存放了所有的返回字段，增加了 排序空間 （sort_buffer）的壓力。

SET max_length_for_sort_data=16;

max_length_for_sort_data 是MySQL 限制排序行大小的參數。意思是，如果排序行大小超過了這個值，就會另選排序算法。上面 name city age 3 個字段的大小為 36，大於 16 ，在新的算法中將只有 name (排序字段) 和id 參與 sort_buffer 中的排序。過程如下

1. 初始化 sort_buffer，確定放入 name id 這 2 個字段；
2. 從 city 索引中獲取到第一個 city='上海' 的記錄，也就是 id_x；
3. 到主鍵索引中獲取對應的記錄，並取出 name id 的值放入 sort_buffer；
4. 取下一條符合條件的記錄，重複 3 4 的操作，直至不符合條件為止；
5. 對 sort_buffer 中的數據按照 name 做快速排序；
6. 取出前 1000 條數據，然後根據 id 取出對應記錄的 name city age 3 個字段並返回結果。

這種排序過程，我們稱為 rowid 排序，過程如下所示：

全字段排序 VS rowid 排序

從上面 2 個流程看來，如果內存足夠時，MySQL 會讓返回值中所有字段存放在排序空間。當MySQL 內存過小時，才會考慮使用rowid 排序。但是從上面的流程看來，rowid 排序在返回結果前，還會再一次的回表。因此MySQL 認為內存充足的時候，會優先採用 全字段排序。

上面的場景是：city 字段過濾後，name 字段不是有序的。其實我們可以通過聯合索引來規避掉 name 字段的排序。

alter table user_info add index idx_city_user(city, name);

下面我們看一下聯合索引的示意圖：

從上面流程圖可以看出，當我們取出 city='上海' 的記錄時，name的字段也是有序的。過程如下

1. 從 (city, name)索引中獲取到第一個 city='上海' 的記錄 id_x；
2. 到主鍵索引中獲取對應的記錄，並取出 name city age 的值作為結果集的一部分直接返回；
3. 取下一條符合條件的記錄，重複 2 3 的操作，直至不符合條件或者達到 1000 條為止；

從聯合索引看來，我們是可以不用排序操作了，那麼我們是否可以直接通過 索引就直接返回結果呢？也就是不要回表操作。答案是有的，那就是覆蓋索引。

alter table user_info add index idx_city_user_age(city, name, age);

當執行查詢語句時，不僅 name 中的字段是有序的，並且 索引中已經包含了結果集中的所有字段，過程如下：

1. 從 (city, name,age)索引中獲取到第一個 city='上海' 的記錄，並取出 name city age 的值作為結果集的一部分直接返回；
2. 取下一條符合條件的記錄，重複 1 2 的操作，直至不符合條件或者達到 1000 條為止；

參考：《極客時間：MySQL實戰》、《高性能MySQL》