MySQL有關Group By的優化，mysql有關groupby

MySQL有關Group By的優化，mysql有關groupby

    昨天我寫了有關MySQL的loose index scan的相關博文（http://www.cnblogs.com/wingsless/p/5037625.html），後來我發現上次提到的那個優化方法中主要的目的就是實現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的文檔上摘抄下來的，正確性沒有得到驗證，雖說官方文檔是權威，但是盡信書不如無書，以及紙上得來終覺淺，絕知此事須躬行，明天周末，我逐條測試，然後再更。