Order by 優化
寫在前面
文章涉及到的 customer 表來源於案例庫 sakila，下載地址為 //downloads.mysql.com/docs/sakila-db.zip
MySQL 排序方式
-
通過索引順序掃描直接返回有序數據
-
通過對返回數據進行排序，即 FileSort 排序。
所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序。FileSort 並不代表通過磁盤文件進行排序，而只是說進行了一個排序操作，至於排序操作是否使用了磁盤文件或臨時表取決於 MySQL 服務器對排序參數的設置和需要排序數據的大小。
EXPLAIN 排序分析
customer DDL
CREATE TABLE `customer` (
`customer_id` smallint unsigned NOT NULL AUTO_INCREMENT,
`store_id` tinyint unsigned NOT NULL,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`email` varchar(50) DEFAULT NULL,
`address_id` smallint unsigned NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`create_date` datetime NOT NULL,
`last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`customer_id`),
KEY `idx_fk_store_id` (`store_id`),
KEY `idx_fk_address_id` (`address_id`),
KEY `idx_last_name` (`last_name`),
KEY `idx_storeid_email` (`store_id`,`email`),
CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE DEFINER=`root`@`%` TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW();
返回所有用戶記錄，根據 customer_id 進行排序。因為 customer_id 是主鍵，記錄都是按照主鍵排好序的，所以無需進行額外的排序操作，直接返回所有數據即可。
EXPLAIN SELECT * FROM customer ORDER BY customer_id;
由於默認是根據 customer_id 進行排序的，相對於上面來說，這裡需要全表掃描，然後根據 store_id 對全表掃描結果進行排序，因此這裡用到了 FileSort 排序。
EXPLAIN SELECT * FROM customer ORDER BY store_id;
store_id , email 這兩個字段是有聯合索引 idx_storeid_email 的，只查詢 store_id 和 email 兩個字段，直接通過聯合索引所在的 B+ 樹返回查詢數據（該索引樹先根據 store_id 字段先進行排序，然後再根據 email 字段排序好的），所以這裡的查詢結果就是排序好的。
EXPLAIN SELECT store_id , email FROM customer ORDER BY store_id;
和上面不同的是，排序的字段是 email，導致 FileSort 排序的原因是聯合索引 idx_storeid_email 的是先根據 store_id 字段先進行排序，然後再根據 email 字段排序好的，如果直接使用 email 排序，則無法使用 idx_storeid_email 索引樹排好的順序。
EXPLAIN SELECT store_id , email FROM customer ORDER BY email;
ORDER BY 使用聯合索引進行排序
EXPLAIN SELECT store_id , email FROM customer ORDER BY store_id , email;
ORDER BY 的字段混用 ASC 和 DESC 會導致使用 FileSort 排序。
EXPLAIN SELECT store_id , email FROM customer ORDER BY store_id ASC , email DESC;
固定 store_id = 1 情況下對 email 字段進行排序，使用 idx_storeid_email 索引即可
EXPLAIN SELECT store_id , email FROM customer WHERE store_id = 1 ORDER BY email;
where 條件先進行 store_id 範圍查詢導致 ORDER BY email 字段無法使用 idx_storeid_email 索引進行排序。
EXPLAIN SELECT store_id , email FROM customer WHERE store_id >= 1 AND store_id <= 3 ORDER BY email;
ORDER BY 可能出現 FileSort 的幾種情況：
- order by 字段混用 ASC 和 DESC 排序方式。
- SELECT * 時，如果 order by 排序字段不是主鍵可能導致 FileSort。
- 聯合索引情況下，order by 多字段排序的字段左右順序和聯合索引的字段左右順序不一致導致 FileSort。
- 聯合索引情況下，where 字段和 order by 字段的左右順序和聯合索引字段左右順序或者 where 字段出現範圍查詢都可能導致 FileSort。
FileSort 優化
通過創建合適的索引可以減少 FileSort 的出現，但是對於某些情況下，條件限制不能讓 FileSort 徹底消失，那就需要對 FileSort 進行優化。對於 FileSort，MySQL 有兩種排序算法。
-
兩次掃描算法（Two Passes）：首先根據條件取出排序字段和行指針，之後在排序區 sort buffer 中排序。如果排序區 sort buffer 不夠，則在臨時表 Temporary Table 中存儲排序結果。完成排序後根據行指針回表讀取完整記錄。該算法是 MySQL 4.1 之前採用的算法，需要兩次訪問數據，第一次獲取排序字段和行指針信息，第二次根據行指針獲取完整記錄，尤其是第二次讀取操作可能導致大量隨機 IO 操作；有點是排序的時候內存開銷比較小。
-
一次掃描算法（Single Passes）：一次性取出滿足條件的行的所有字段，然後在排序區 sort buffer 中排序後直接輸出結果集。排序的時候內存開銷比較大，但是排序效率比兩次掃描算法要高。
MySQL 通過比較系統變量 max_length_for_sort_data 的大小和 Query 語句取出的字段總大小來判斷使用哪種排序算法。如果 max_length_for_sort_data 設置足夠大，那麼會使用一次掃描算法；否則使用兩次掃描算法。適當加大系統變量 max_length_for_sort_data 的值，能夠讓 MySQL 選擇更加優化的 FileSort 排序算法。當然，假如 max_length_for_sort_data 設置過大，會造成 CPU 利用率過低和磁盤 IO 過高。
適當加大 sort_buffer_size 排序區，盡量讓排序在內存中完成，而不是通過創建臨時表放在文件中進行；當然也不能無限加大 sort_buffer_size 排序區，因為 sort_buffer_size 參數是每個線程獨佔的，設置過大會導致服務器 SWAP 嚴重，要考慮數據庫活動連接數和服務器內存的大小來適當設置排序區。
盡量只使用必要的字段，SELECT 具體的字段名稱，而不是 SELECT * 選擇所有字段，這樣可以減少排序區的使用，提高 SQL 性能。
參考
《深入淺出 MySQL 數據庫開發、優化與管理維護第 2 版》
《MySQL 實戰 45 講》
最後
如果大家想要實時關注我更新的文章以及我分享的乾貨的話，可以關注我的公眾號 我們都是小白鼠。
