Mysql存儲原理（2）

今天我們來講一下排序

mysql的排序方式是，我們先根據條件查詢出結果集，然後在內存中對結果集進行排序，如果結果集數量比較大，還需要將結果集寫入到多個文件中去，然後單獨進行排序，然後在文件間進行歸並排序，排序完成後在進行 limit 操作。

CREATE TABLE `person` (
  `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 person where city='武漢' order by name limit 100  ;

 使用 explain 發現該語句會使用 city 索引，並且會有 filesort . 我們分析下該語句的執行流程

• 1.初始化 sortbuffer ，用來存放結果集

• 2.找到 city  索引，定比特到 city 等於武漢的第一條記錄，獲取主鍵索引ID

• 3.根據 ID 去主鍵索引上找到對應記錄，取出 city,name,age 字段放入 sortbuffer

• 4.在 city 索引取下一個 city 等於武漢的記錄的主鍵ID

• 5.重複上面的步驟，直到所有 city 等於武漢的記錄都放入 sortbuffer

• 6.對 sortbuffer 裏的數據根據 name 做快速排序

• 7.根據排序結果取前面 1000 條返回

這裏面查詢得是city,name,age三個字段，比較少。如果查詢得字段比較多，他們都會放在sortbuffer裏面，sortbuffer會占據大量內存空間。另一個方案就是只取出待排的字段和主鍵放在sortbuffer裏面，這裏是 name 和 id ,排序完成後在根據 id 取出需要查詢的字段返回，其實就是時間換取空間的做法，這裏通過 max_length_for_sort_data 參數控制，是否采用後面的方案進行排序。 

另外如果 sortbuffer 裏的條數很多，同樣會占有大量的內存空間，可以通過參數 sort_buffer_size 來控制是否需要借助文件進行排序，這裏會把 sortbuffer 裏的數據放入多個文件裏，用歸並排序的思路最終輸出一個大的文件。

以上方案主要是 name 字段沒有加上索引，如果 name 字段上有索引，由於索引在構建的時候已經是有序的了，所以就不需要進行額外的排序流程只需要在查詢的時候查出指定的條數就可以了，這將大大提昇查詢速度。我們現在加一個 city 和 name 的聯合索引

alter table person add index city_user(city, name);

• 1.根據 city,name 聯合索引定比特到 city 等於武漢的第一條記錄，獲取主鍵索引ID

• 2.根據 ID 去主鍵索引上找到對應記錄，取出 city,name,age 字段作為結果集返回

• 3.繼續重複以上步驟直到 city 不等於武漢，或者條數大於 1000

由於聯合所以在構建索引的時候，在 city 等於武漢的索引節點中的數據已經是根據 name 進行排序了的，所以這裏只需要直接查詢就可，另外這裏如果加上 city, name, age 的聯合索引，則可以用到索引覆蓋，不用到主鍵索引上進行回錶。