MySQL有關Group By的最佳化

MySQL有關Group By的最佳化

我寫了有關MySQL的loose index scan的相關博文（），後來我發現上次提到的那個最佳化方法中主要的目的就是實現loose index scan，而在執行計畫的層面上看，Extra資訊中應該是“Using index for group-by”。這樣看來，可能MySQL在處理distinct時和group by用了同樣的最佳化手段，即走索引，進行loose index scan。那麼今天我研究了一下官方文檔，發現確實如此。

其實對於group by來講，最一般的實現方法就是進行一次全表掃描，將所有的group by的行按照順序存放在一個temporary table中，然後在進行分組識別或者進行彙總操作。這樣問題就是太複雜，時間上要好久，空間上的消耗也不小。這時，MySQL可以利用索引來最佳化group by。

這裡就可以講講什麼叫做loose index scan了，根據官方的定義，這種方法只需要掃描索引中的少部分資料，而不是所有滿足where條件的資料，所以這個方法叫做loose index scan。

下面是什麼情況下可以使用loose index scan的情況：

1 單一表查詢

2 Group by中只有最左首碼列，沒有其他列

3 只支援max和min彙總，而且，要彙總的列必須是group by中列所在的索引。

4 未被group by引用的索引其他部分必須是常量（這句我不是很理解）

5 不支援首碼索引。

假設t1(c1, c2, c3, c4)表有一個索引包括c1, c2, c3列，以下這些查詢都是可以進行loose index scan的：

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 c1 < 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;

這些都是抄的官方文檔，正確性未完全驗證。

下面這些SQL都沒有辦法使用loose index scan：

-- 因為彙總函式不是max或者min

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

-- 因為不符合最左首碼原則

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

-- 查詢涉及到了索引的一部分，緊跟group by中的列，但是沒有常量等值語句，加上 WHERE c3 = const就好了

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

另外一些彙總函式也是可以用到loose index scan的，比如：AVG(DISTINCT), SUM(DISTINCT), 和COUNT(DISTINCT)

以下這些語句也可以：

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

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

很多語句都是從5.7的文檔上摘抄下來的，正確性沒有得到驗證，雖說官方文檔是權威，但是盡信書不如無書，以及紙上得來終覺淺，絕知此事須躬行，明天周末，我逐條測試，然後再更。

本文永久更新連結地址：