Order by 優化

我們都是小白鼠發表於2020-05-28
優化

寫在前面

文章涉及到的 customer 表來源於案例庫 sakila，下載地址為 http://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 的幾種情況：

  1. order by 欄位混用 ASC 和 DESC 排序方式。
  2. SELECT * 時，如果 order by 排序欄位不是主鍵可能導致 FileSort。
  3. 聯合索引情況下，order by 多欄位排序的欄位左右順序和聯合索引的欄位左右順序不一致導致 FileSort。
  4. 聯合索引情況下，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 講》

最後

如果大家想要實時關注我更新的文章以及我分享的乾貨的話，可以關注我的公眾號 我們都是小白鼠。

相關文章