本文翻譯自MySQL 8.0 Reference Manual 8.2.1.17 GROUP BY Optimization

執(zhí)行group by語句最一般的方式就是掃描整張表，并且創(chuàng)建一張臨時表來保存所有的分組字段值，然后通過這張臨時表整理分組并執(zhí)行聚集函數(shù)。在某些情況下MySQL可以通過使用索引訪問避免創(chuàng)建臨時表，依次提高查詢性能。

通過索引來執(zhí)行group by查詢的一個最重要的前提條件是group by子句中所有的列都來自同一個索引，并且該索引按順序保存列值（比如BTREE索引，HASH索引則不滿足此約束）。是否使用索引訪問而不是臨時表也取決于使用索引中的哪些列，使用的條件類型以及使用的聚集函數(shù)等。

MySQL支持兩種訪問方式來通過索引執(zhí)行group by語句，下面的小結將進行詳細介紹。第一種方法在所有的范圍條件上執(zhí)行分組操作，第二種方法首先進行范圍掃描，然后對獲得的元組進行分組操作。

• Loose Index Scan（松散的索引掃描）
• Tight Index Scan（緊湊的索引掃描）

下面將詳細介紹著兩種索引掃描方法。

1. Loose Index Scan（松散的索引掃描）
   處理group by最高效的方式就是使用索引直接獲取分組的列。當使用索引訪問方法時，MySQL利用索引按照順序組織列值的特性（比如BTREE索引）。這種特性可以被用來查找索引中滿足條件的分組而不需要訪問索引中的所有值。這種訪問方法僅僅考慮索引中的部分值，所以稱為松散的索引掃描（Loose Index Scan）。當查詢語句中沒有任何條件子句時，松散的索引掃描僅僅讀取索引中分組數(shù)量個索引值，而不需要讀取索引中的所有值，這大大地減少了讀取的索引值數(shù)據(jù)。如果WHERE子句中包含范圍條件子句（jion type為range），松散的索引掃描會讀取滿足條件的所有分組中的第一個key值，然后盡可能少的讀取其他的key值。

要使用松散的所有掃描，必須滿足如下條件：

• 查詢僅僅在一張表上進行。

• group by使用的列必須來自一個索引的最左前綴列，并且沒有其他列（除了group by，對于有DISTINCT子句的查詢，當所有的distinct列都來自一個索引的最左前綴列時，也滿足條件）。例如，如果表t1在(c1, c2, c3)上定義了索引，可以對GROUP BY c1, c2使用松散的索引掃描，但是對于GROUP BY c2, c3卻不可以使用松散的所有掃描，因為group by子句中的列不是索引的最左前綴列，語句GROUP BY c1, c2, c4也不能使用松散的所有掃描，因為c4不是索引中的列。

• 查詢中只能使用MIN()或者MAX()聚集函數(shù)，并且查詢中使用的多個聚集函數(shù)只能指定相同的列。聚集函數(shù)中使用的列必須是使用的索引中的列，并且是GROUP BY子句中出現(xiàn)的第一個列。（譯者注：這條約束可以從BTREE索引的實現(xiàn)原理上理解）。

• 索引中其他沒有在GROUP BY子句中指定的列必須是常數(shù)（即必須和常數(shù)相等），除非是用于聚集函數(shù)MIN()或者MAX()的列。

• 對于索引中的列，這些列必須是完整索引，而不是僅僅對列的部分值進行索引。例如：對于列c1 VARCHAR(20), INDEX(c1(10))，這個索引僅僅使用了c1列的前面10個字符進行索引，因此不能使用松散的索引掃描進行優(yōu)化。

如果可用使用松散的索引掃描處理查詢語句，那么EXPLAIN的Extra會顯示Using index for group-by。

假設在表t1(c1, c2, c3, c4)有索引idx(c1, c2, c3)，下面的查詢語句可以使用松散的索引掃描：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(C2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE t1 < const GROUP BY c1, c2;
SELECT MAX(C3), MIN(C3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

下面的語句則不能使用松散的索引掃描：

SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

上述語句使用了SUM()函數(shù)，而松散的索引掃描只支持MIN()及MAX()函數(shù)。

SELECT c1, c2 FROM t1 GROUP BY c2, c3;

上述語句GROUP BY子句中的列不是索引的最左前綴列。

SELECT c1, c3 FROM t1 GROUP BY c1, c2;

上述語句中使用了同一索引中沒有用于GROUP BY子句中的列，但是該列沒有和常數(shù)相等的條件。如果查詢有WHERE c3 = const的條件子句，則可以使用松散的索引掃描。

某些情況下，除了MIN()及MAX()函數(shù)，松散的索引掃描也可以使用其他的聚集函數(shù)：

• AVG(DISTINCT)，SUM(DISTINCT)以及COUNT(DISTINCT)也是可以支持的，其中AVG(DISTINCT)和SUM(DISTINCT)只支持單個參數(shù)，而COUNT(DISTINCT)則支持多個列作為參數(shù)。
• 查詢語句中不能有GROUP BY及DISTINCT子句。

假設在表t1(c1, c2, c3, c4)有索引idx(c1, c2, c3)，下面的查詢語句可以使用松散的索引掃描：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

2. Tight Index Scan（緊湊的索引掃描）

緊湊的索引掃描可以是全索引掃描或者是范圍索引掃描，這取決于查詢的條件。

當松散索引掃描的條件沒有滿足時，仍然有避免創(chuàng)建臨時表處理GROUP BY查詢的可能。如果WHERE子句中有范圍條件，這種訪問方法僅僅讀取那些滿足條件的keys，否則會進行一次索引掃描。因為這種訪問方法讀取滿足WHERE子句條件的所有keys或者當沒有WHERE子句時則讀取所有的keys，因此稱其為緊湊的索引掃描。在使用緊湊的索引掃描時，僅當所有滿足條件的keys都被讀取之后才會進行分組操作（譯者注：松散的索引掃描在掃描（或者讀取）的過程中就進行了分組）。

為了使用緊湊的索引掃描，在GROUP BY子句之前或之間的所有列都具有等于常數(shù)的條件表達式即可，即需要等于常數(shù)的條件來填充所有不滿足最左前綴列的約束即可。這些被填充、沒有間隙的索引可以被用來進行數(shù)據(jù)查找。如果GROUP BY需要排序，則MySQL也可以通過此方法避免額外的排序操作。

假設在表t1(c1, c2, c3, c4)有索引idx(c1, c2, c3)下面的查詢不能使用松散的索引掃描，但是依然可以使用緊湊的索引掃描：

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

上述語句中在使用索引時具有“間隙”，但是可以被條件c2 = 'a'填充這個間隙。

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

上述語句沒有滿足最左前綴索引列的條件，但是條件c1 = 'a'使得使用的索引的最左列被覆蓋。