GROUP BY

三種處理GROUP BY的方式

• 松散索引掃描（Loose Index Scan）
• 緊湊索引掃描（Tight Index Scan）
• 臨時表（Temporary table）
  三種方式的性能一次遞減

松散索引掃描

• 無需掃描滿足條件的所有索引鍵即可返回結(jié)果

我們使用如下索引

執(zhí)行SQL

select emp_no,min(salary)
from salaries
group by emp_no;

結(jié)果

當Extra出現(xiàn)Using index for group-by就說明使用了松散掃描。
上面的語句，在執(zhí)行過程中一般情況下應該是查詢出類似[person[i]，salary[j]]，[person[i]，salary[j+1]]，[person[i]，salary[j+2]]…[person[i+1]，salary[k]]，[person[i+1]，salary[k+1]]，[person[i+1]，salary[k+2]]…。然后將person[i]的進行計算得到最小的salary，再計算person[i+1]的最小salary，但是索引是有序的，查詢出來的語句已經(jīng)是先按person排序，再按salary排序，也就是說每個person的第一個salary就是最小的，中間的掃描所有每個person的salary并計算最小值的過程是可以省略的直接取每個person的第一個salary即可，這就是松散索引掃描，無需掃描所有的滿足條件的索引。

使用松散索引掃描的條件

• 查詢作用再單張表上
• GROUP BY指定的所有字段要符合最左前綴原則，且沒有其他字段
  • 比如有索引index(c1，c2，c3)，且有GROUP BYc1，c2則可以使用松散索引掃描；但GROUP BY c2，c3、GROUP BY c1，c2，c4則不能使用
• 如果存在聚合函數(shù)，只支持MIN()/MAX()，并且如果同時使用了MIN()和MAX()，則必須作用再同一個字段。集合函數(shù)作用的字段必須再索引中，并且要緊跟GROUP BY所指定的字段
  • 比如有索引index(c1，c2，c3)，SELECT c1，c2 MIN(c3)，MAX(c3) FROM t GROUP BY c1，c2可以使用松散索引掃描
• 如果查詢中存在除GROUP BY指定的列以外的其他部分，則必須以常量的形式出現(xiàn)
  • SELECT c1，c3 FROM t GROUP BY c1，c2；不能使用
  • SELECT c1，c3 FROM t WHERE c3 = 3 GROUP BY c1，c2；可以使用
• 索引必須是索引整個字段的值，不能是前綴索引

能使用松散索引掃描的SQL

假設有index(c1,c2,c3)作用再表t(c1,c2,c3,c4)上，下面這些語句都能使用松散索引掃描：

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

不能使用松散索引掃描的SQL

– 聚合函數(shù)不是MIN()或MAX()
SELECT c1,SUM(c2) from t GROUP BY c1;
– 不符合最左前綴原則
SELECT c1,c2 FROM t GROUP BY c2,c3;
– 查詢了c3字段，但c3
字段上沒有等值查詢
SELECT c1,c3 FROM t GROUP BY c1,c2;

緊湊索引掃描

• 需要掃描滿足條件的所有索引鍵才能返回結(jié)果
• 性能一般比松散索引掃描差，但是還是可以接受的

explain 
select emp_no,sum(salary)
from salaries
group by emp_no;

結(jié)果

緊湊索引掃描在Extra中是沒有特別標識的。

臨時表

• 在無法使用松散索引掃描以及緊湊索引掃描的請款下，MySQL將會讀取需要的數(shù)據(jù)，并創(chuàng)建一張臨時表，用臨時表實現(xiàn)GROUP BY操作。

explain
select max(hire_date)
from employees
group by hire_date;

結(jié)果

一旦使用了臨時表，在Extra中將會出現(xiàn)Using temporary。

GROUP BY的優(yōu)化

• 如果GROUP BY使用了臨時表，那么就需要想辦法用上松散索引掃描或者緊湊索引掃描。

DISTINCT

DISTINCT優(yōu)化

• DISTINCT實在GROUP BY操作之后，每組只取一條
• 和GROUP BY優(yōu)化思路一致