MySQL的Order by與Group by優化詳解！

在這里插入圖片描述

- 前言
- 核心思想：讓索引幫你“排好序”或“分好組”
- Part 1: ORDER BY 優化詳解
- - 1.1 什么是 Filesort？為什么它慢？
  - 1.2 如何避免 Filesort？—— 利用索引的有序性
  - 1.3 EXPLAIN 示例 (ORDER BY)
- Part 2: GROUP BY 優化詳解
- - 2.1 什么是 Using Temporary 和 Using Filesort (for GROUP BY)？
  - 2.2 如何避免 Using Temporary 和 Filesort (for GROUP BY)？—— 利用索引的有序性
  - 2.3 EXPLAIN 示例 (GROUP BY)
- Part 3: 聯合索引，同時優化 WHERE, ORDER BY, GROUP BY
- 總結 📝

🌟我的其他文章也講解的比較有趣😁，如果喜歡博主的講解方式，可以多多支持一下，感謝🤗！

其他優質專欄：【🎇SpringBoot】【🎉多線程】【🎨Redis】【?設計模式專欄（已完結）】…等

如果喜歡作者的講解方式，可以點贊收藏加關注，你的支持就是我的動力
?更多文章請看個人主頁：碼熔burning

前言

你好呀，數據庫優化路上的同行們！🚀 當我們在數據庫中查詢數據時，除了根據 WHERE 條件篩選記錄，經常還需要對結果進行排序 (ORDER BY) 或分組聚合 (GROUP BY)。這兩個操作看似簡單，但一旦數據量上來，它們就可能成為查詢的性能瓶頸，導致查詢變慢，甚至拖垮整個數據庫系統。

為什么 ORDER BY 和 GROUP BY 會慢呢？因為它們通常需要對大量數據進行排序或構建哈希表進行聚合，這個過程可能需要在內存甚至磁盤上進行，消耗大量的 CPU 和 I/O 資源。在 EXPLAIN 的輸出中，如果看到 Extra 列出現了 Using filesort 或 Using temporary，那就要警惕了，這往往是性能問題的信號！🚨

今天，我們就來詳細探討如何通過合理的索引策略，幫助 MySQL 避免這些昂貴的操作，讓 ORDER BY 和 GROUP BY 飛起來！?

核心思想：讓索引幫你“排好序”或“分好組”

優化 ORDER BY 和 GROUP BY 的核心思路是一樣的：利用索引的有序性。B+tree 索引（MySQL InnoDB 存儲引擎的默認索引類型）的一個關鍵特性就是存儲的數據是按照索引列的值有序排列的。如果查詢所需的排序或分組順序恰好與某個索引的順序一致，MySQL 就可以直接按照索引的順序讀取數據，而無需額外的排序或分組步驟。

Part 1: ORDER BY 優化詳解

ORDER BY 子句用于指定結果集的排序方式。如果不能使用索引進行排序，MySQL 就需要執行一個額外的排序步驟，這個過程稱為 Filesort。

1.1 什么是 Filesort？為什么它慢？

當 MySQL 無法利用索引來滿足 ORDER BY 的需求時，它會將查詢結果（或者至少是需要排序的列以及用于回表的主鍵/行指針）讀取出來，然后在內存中進行排序。如果內存不足，就會將數據分塊，利用磁盤進行“歸并排序”。這個過程就是 Filesort。

EXPLAIN 的 Extra 列顯示 Using filesort，就表示發生了 Filesort。

為什么 Filesort 慢？

CPU 消耗： 排序本身是一個計算密集型操作。
內存消耗： 需要分配內存緩沖區來存儲待排序的數據。
磁盤 I/O (如果內存不足)： 當數據量大到內存裝不下時，就會使用臨時文件進行排序，產生大量的磁盤讀寫，這是最慢的情況。

1.2 如何避免 Filesort？—— 利用索引的有序性

避免 Filesort 的最佳方法是創建一個索引，使其列的順序和排序方向與 ORDER BY 子句的要求一致。

條件：

要讓索引能夠用于 ORDER BY，通常需要滿足以下條件：

索引列順序： ORDER BY 子句中的所有列必須是索引中的連續的列，并且是索引的前綴。
- 例如，索引 (colA, colB, colC) 可以用于 ORDER BY colA, ORDER BY colA, colB, ORDER BY colA, colB, colC。
- 但不能用于 ORDER BY colB, ORDER BY colA, colC, ORDER BY colB, colA。
排序方向： ORDER BY 子句中所有列的排序方向（ASC 或 DESC）必須一致，并且與索引的創建方向一致，或者全部與索引創建方向相反。MySQL 可以倒序掃描索引來滿足相反方向的排序。
- 例如，索引 (colA ASC, colB ASC) 可以用于 ORDER BY colA ASC, colB ASC 和 ORDER BY colA DESC, colB DESC。
- 但不能用于 ORDER BY colA ASC, colB DESC。
WHERE 子句與索引的關系： 如果查詢有 WHERE 子句，并且 WHERE 子句使用了索引的前綴列進行等值查詢，那么 ORDER BY 子句可以使用索引中緊隨其后的列進行排序。
- 例如，索引 (colA, colB, colC)。
- 查詢 SELECT * FROM table WHERE colA = '...' ORDER BY colB, colC; 可以使用索引進行排序。
- 查詢 SELECT * FROM table WHERE colA > '...' ORDER BY colB, colC; 可能無法使用索引排序，因為 WHERE 子句在 colA 上是范圍查詢，中斷了索引的連續性。
- 查詢 SELECT * FROM table WHERE colB = '...' ORDER BY colA; 無法使用索引排序，因為 WHERE 子句沒有使用索引的前綴。
排序的列和 WHERE 子句的過濾列不能是相互沖突的范圍： 例如 WHERE colA = 1 ORDER BY colA.
沒有 LIMIT 但 ORDER BY 列不在 WHERE 子句中，或 WHERE 是范圍查詢： 這種情況下，MySQL 可能為了避免全索引掃描而選擇 Filesort。但如果有了 LIMIT，MySQL 可能會重新考慮使用索引排序。
ORDER BY RAND()： 這個是隨機排序，索引是無法滿足的，必定是 Filesort。避免在生產環境使用 ORDER BY RAND()，可以考慮其他隨機獲取數據的方法。

1.3 EXPLAIN 示例 (ORDER BY)

假設我們有表 products：

CREATE TABLE products (product_id INT PRIMARY KEY,category_id INT,price DECIMAL(10, 2),create_time DATETIME
);-- 創建一個聯合索引
CREATE INDEX idx_cat_price_time ON products (category_id, price, create_time);-- 可以自己插入一些數據來進行下面的測試！

示例 1: Filesort (排序列不在索引前綴)

EXPLAIN SELECT * FROM products ORDER BY create_time DESC;

EXPLAIN 結果可能顯示 type: ALL (全表掃描) 和 Extra: Using filesort。因為 create_time 不是索引 idx_cat_price_time 的前綴。

示例 2: 利用索引排序 (符合前綴規則)

EXPLAIN SELECT * FROM products ORDER BY category_id ASC, price ASC;

EXPLAIN 結果可能顯示 type: index (全索引掃描) 或 type: ALL (如果優化器認為全表掃描更快)，但 Extra 中沒有 Using filesort。或者如果同時有 WHERE 子句限制了掃描范圍，type 可能是 range 或 ref，且 Extra 中沒有 Using filesort。

EXPLAIN SELECT * FROM products WHERE category_id = 10 ORDER BY price ASC, create_time ASC;

EXPLAIN 結果可能顯示 type: ref，并且 Extra 中沒有 Using filesort。因為 WHERE 子句使用了索引前綴 category_id 的等值條件，ORDER BY 子句使用了索引中緊隨其后的列 price 和 create_time。

示例 3: Filesort (排序方向不一致)

EXPLAIN SELECT * FROM products WHERE category_id = 10 ORDER BY price ASC, create_time DESC;

EXPLAIN 結果很可能顯示 type: ref，但 Extra 中有 Using filesort。因為 price 是 ASC 排序，而 create_time 是 DESC 排序，與索引定義 (..., price ASC, create_time ASC) 的方向不完全一致（或者完全相反）。

優化建議 (ORDER BY):

分析慢查詢中的 ORDER BY 子句。
檢查是否有合適的索引，其列的順序和方向能匹配 ORDER BY 的需求。
如果 WHERE 和 ORDER BY 都很頻繁，考慮創建聯合索引，將 WHERE 條件中用于等值過濾的列放在前面，將 ORDER BY 中的列按順序放在后面。
使用 EXPLAIN 驗證 Filesort 是否被消除。

Part 2: GROUP BY 優化詳解

GROUP BY 子句用于將結果集按照一個或多個列進行分組，通常與聚合函數（如 COUNT(), SUM(), AVG(), MAX(), MIN()）一起使用。如果不能利用索引完成分組，MySQL 可能會創建臨時表來存儲中間結果，或者先排序再分組。

2.1 什么是 Using Temporary 和 Using Filesort (for GROUP BY)？

當 MySQL 無法直接通過索引的有序性來滿足 GROUP BY 的需求時，它可能采取以下策略：

創建臨時表 (Using temporary): MySQL 會創建一個內存或磁盤上的臨時表，將需要分組的列和聚合所需的列存入其中。然后遍歷所有符合 WHERE 條件的行，將數據插入臨時表，并在插入時進行聚合（如果臨時表上有主鍵或唯一索引）或插入后進行聚合。
排序后分組 (Using filesort): MySQL 會將結果集按照 GROUP BY 的列進行排序，然后遍歷排序后的結果進行分組聚合。這個排序過程也可能導致 Filesort。

EXPLAIN 的 Extra 列顯示 Using temporary 或 Using filesort（有時兩者都會出現），就表示 GROUP BY 過程不夠優化。

為什么慢？

臨時表： 創建和維護臨時表有開銷，尤其是當臨時表溢寫到磁盤時，會產生大量磁盤 I/O。
Filesort： 同 ORDER BY 中的 Filesort，消耗 CPU 和 I/O。

2.2 如何避免 Using Temporary 和 Filesort (for GROUP BY)？—— 利用索引的有序性

類似于 ORDER BY，利用索引的有序性可以幫助 MySQL 直接按分組所需的順序掃描數據，從而避免臨時表和額外的排序。

條件：

要讓索引能夠用于 GROUP BY，通常需要滿足以下條件：

索引列順序： GROUP BY 子句中的所有列必須是索引中的連續的列，并且是索引的前綴。
- 例如，索引 (colA, colB, colC) 可以用于 GROUP BY colA, GROUP BY colA, colB, GROUP BY colA, colB, colC。
- 但不能用于 GROUP BY colB, GROUP BY colA, colC, GROUP BY colB, colA。
WHERE 子句與索引的關系： 如果查詢有 WHERE 子句，并且 WHERE 子句使用了索引的前綴列進行等值查詢，那么 GROUP BY 子句可以使用索引中緊隨其后的列進行分組。
- 例如，索引 (colA, colB, colC)。
- 查詢 SELECT colA, colB, COUNT(*) FROM table WHERE colA = '...' GROUP BY colA, colB; 可以使用索引進行分組。
- 查詢 SELECT colA, colB, COUNT(*) FROM table WHERE colA > '...' GROUP BY colA, colB; 可能無法使用索引分組，原因同 ORDER BY。
- 查詢 SELECT colA, colB, COUNT(*) FROM table WHERE colB = '...' GROUP BY colA, colB; 無法使用索引分組，因為 WHERE 子句沒有使用索引的前綴。
GROUP BY 列的順序很重要： 必須嚴格按照索引列的順序進行分組。
沒有 DISTINCT 或 MIN/MAX 在非索引列上： 某些復雜的聚合函數組合可能阻止索引用于分組。COUNT(DISTINCT ...) 也經常導致無法使用索引進行分組。

2.3 EXPLAIN 示例 (GROUP BY)

還是使用上面的 products 表和 idx_cat_price_time (category_id, price, create_time) 索引。

示例 4: Using Temporary / Filesort (分組列不在索引前綴)

EXPLAIN SELECT price, COUNT(*) FROM products GROUP BY price;

EXPLAIN 結果可能顯示 type: ALL 和 Extra: Using temporary; Using filesort。因為 price 不是索引 idx_cat_price_time 的前綴。

示例 5: 利用索引分組 (符合前綴規則)

EXPLAIN SELECT category_id, COUNT(*) FROM products GROUP BY category_id;

EXPLAIN 結果可能顯示 type: index (全索引掃描) 或 type: ALL，但 Extra 中沒有 Using temporary 和 Using filesort。或者如果同時有 WHERE 子句限制了掃描范圍，type 可能是 range 或 ref，且 Extra 中沒有 Using temporary 和 Using filesort。

EXPLAIN SELECT category_id, price, COUNT(*) FROM products WHERE category_id = 10 GROUP BY category_id, price;

EXPLAIN 結果可能顯示 type: ref，并且 Extra 中沒有 Using temporary 和 Using filesort。因為 WHERE 子句使用了索引前綴 category_id 的等值條件，GROUP BY 子句使用了索引中緊隨其后的列 category_id 和 price（盡管 category_id 在 WHERE 里已經限制了，但在 GROUP BY 里再次出現并不影響索引的使用）。

優化建議 (GROUP BY):

分析慢查詢中的 GROUP BY 子句。
檢查是否有合適的索引，其列的順序能匹配 GROUP BY 的需求。
如果 WHERE 和 GROUP BY 都很頻繁，考慮創建聯合索引，將 WHERE 條件中用于等值過濾的列放在前面，將 GROUP BY 中的列按順序放在后面。
注意 GROUP BY 列的順序必須和索引前綴嚴格匹配。
對于 COUNT(DISTINCT ...) 或復雜聚合，可能難以用索引優化分組，需要考慮其他方案（如子查詢、匯總表等）。
使用 EXPLAIN 驗證 Using temporary 和 Using filesort 是否被消除。

Part 3: 聯合索引，同時優化 WHERE, ORDER BY, GROUP BY

最理想的情況是，一個聯合索引能夠同時支持 WHERE 子句過濾、GROUP BY 分組和 ORDER BY 排序。這需要精心設計索引列的順序。

索引列順序的考慮優先級（通常）：

WHERE 子句中的等值條件列： 放在索引最前面，能最有效地縮小掃描范圍。
WHERE 子句中的范圍條件列： 放在等值條件列后面。范圍條件會終止索引后續列用于進一步的索引查找優化，但可能可以用于 ICP。
GROUP BY 子句中的列： 放在 WHERE 條件列后面，且順序要和 GROUP BY 的順序一致。
ORDER BY 子句中的列： 放在 GROUP BY 列后面（如果 GROUP BY 和 ORDER BY 使用的列不同），且順序和方向要一致。
查詢中需要返回的列 (用于索引覆蓋)： 如果可能，將查詢中 SELECT 的其他列也加入到索引中，形成覆蓋索引，徹底避免回表。這部分列通常放在索引的最后。

示例 6: 一個嘗試同時優化 WHERE, GROUP BY, ORDER BY 的聯合索引

假設我們有一個查詢：

SELECT category_id, price, COUNT(*) as total_count
FROM products
WHERE category_id = 10 AND create_time >= '2023-01-01'
GROUP BY category_id, price
ORDER BY price ASC, category_id ASC; -- 注意這里的ORDER BY順序

根據上述優先級和規則，我們可以嘗試創建索引：

-- category_id 是等值條件，放最前
-- create_time 是范圍條件，放 category_id 后面
-- GROUP BY 是 category_id, price，所以 price 放 create_time 后面
-- ORDER BY 是 price ASC, category_id ASC，這與 GROUP BY 的列順序一致，可以考慮合并
CREATE INDEX idx_optimal ON products (category_id, create_time, price);

執行 EXPLAIN 看看效果：

EXPLAIN SELECT category_id, price, COUNT(*) as total_count
FROM products
WHERE category_id = 10 AND create_time >= '2023-01-01'
GROUP BY category_id, price
ORDER BY price ASC, category_id ASC;

理想情況下，如果優化器認為這個索引合適：

WHERE category_id = 10 利用索引前綴進行等值查找。
WHERE create_time >= '2023-01-01' 利用索引的 create_time 部分進行范圍掃描（可能伴隨 ICP）。
GROUP BY category_id, price 由于 category_id 在 WHERE 中已固定，且 price 緊隨 create_time 之后，MySQL 可以利用索引的有序性進行分組。
ORDER BY price ASC, category_id ASC 由于 GROUP BY 通常會隱含排序，且這里的 ORDER BY 列和方向與 GROUP BY 以及索引的后續列順序一致，MySQL 可以直接使用索引的順序，避免 Filesort。

EXPLAIN 結果中可能顯示 type: range，并且 Extra 中沒有 Using temporary 和 Using filesort。?

重要的注意事項：

ORDER BY 和 GROUP BY 的列和方向必須嚴格匹配索引的順序和方向（或完全相反）才能利用索引避免 Filesort/Using temporary。
在一個查詢中，ORDER BY 和 GROUP BY 有時會“爭搶”索引的使用。如果一個索引能同時滿足兩者，MySQL 優化器會選擇最有利的方式。
GROUP BY 如果能使用索引，通常也意味著結果是按照 GROUP BY 的列排序的，所以如果 ORDER BY 的列和方向與 GROUP BY 完全一致，ORDER BY 就可以被“優化掉”或者說融入到分組過程中。
EXPLAIN 是唯一的真理！任何索引優化猜想都需要通過 EXPLAIN 來驗證。

總結 📝

優化 ORDER BY 和 GROUP BY 的核心在于讓 MySQL 能夠利用索引的有序性來完成排序和分組，從而避免代價高昂的 Filesort 和 Using temporary 操作。

ORDER BY 優化： 關注索引列的順序和排序方向是否與 ORDER BY 子句匹配，特別是與 WHERE 子句結合時的“最左前綴”規則。目標是消除 EXPLAIN 中的 Using filesort。
GROUP BY 優化： 關注索引列的順序是否與 GROUP BY 子句匹配，同樣要考慮與 WHERE 子句的結合。目標是消除 EXPLAIN 中的 Using temporary 和 Using filesort。
聯合索引： 精心設計的聯合索引可以同時優化 WHERE、GROUP BY 和 ORDER BY。索引列的順序通常按照等值過濾、范圍過濾、分組、排序的優先級來考慮。
EXPLAIN 神器： 永遠使用 EXPLAIN 來分析查詢的執行計劃，確認 Filesort 和 Using temporary 是否被避免，并評估索引的使用情況。