準(zhǔn)備工作！

1.本文章MySQL使用的是5.7，引擎使用的是innodb
2. 使用的表結(jié)構(gòu)（t1），字段a上有一個(gè)索引，

1. group by常用方法：

group by的常規(guī)用法是配合聚合函數(shù)，利用分組信息進(jìn)行統(tǒng)計(jì)，常見的是配合max等聚合函數(shù)篩選數(shù)據(jù)后分析，以及配合having進(jìn)行篩選后過濾。
聚合函數(shù)：

• count()，返回指定列中數(shù)據(jù)的個(gè)數(shù)
• sum()，返回指定列中數(shù)據(jù)的總和
• avg()，返回指定列中數(shù)據(jù)的平均值
• min()，返回指定列中數(shù)據(jù)的最小值
• max()，返回指定列中數(shù)據(jù)的最大值

示例1： 查詢t1表，按照字段b進(jìn)行分組，并求出分組后b字段a的總和。

SELECT  MAX(a) from t1 GROUP BY b

實(shí)例2：查詢t1表，按照字段b進(jìn)行分組，拿到b<100的所有數(shù)據(jù)，求出a的總和。

SELECT  MAX(a) from t1 GROUP BY b HAVING b<100

where和having區(qū)別

where子句將單個(gè)行過濾到查詢結(jié)果中，而having子句將分組過濾到查詢結(jié)果中 having子句中使用的列名必須出現(xiàn)在group by子句列表中，或包括在聚集函數(shù)中。

having子句的條件運(yùn)算至少包括一個(gè)聚集函數(shù)，否則可以把查詢條件移到where字句中來過濾單個(gè)行（注意聚集函數(shù)不可以用在where子句中）

2. group by語(yǔ)句執(zhí)行流程：

我們執(zhí)行以下語(yǔ)句

SELECT  b, count(*) as c from t1 GROUP BY b HAVING b<100

查看explain執(zhí)行情況，由于b字段上沒有索引，所以進(jìn)行全表掃描。

Using temporary; 表示使用了臨時(shí)表；
Using filesort ，表示需要排序。

由于字段b上沒有索引，它的執(zhí)行順序是這樣的

• 創(chuàng)建內(nèi)存臨時(shí)表，表里有兩個(gè)字段 b 和 c，主鍵是 b；
• 對(duì)t1表進(jìn)行全表掃描，并取出第一條數(shù)據(jù)，判斷b是否小于100。如果b<100，就記錄b的值為X,并存入臨時(shí)表。
  1.如果臨時(shí)表中沒有主鍵為b的行，就插入一條記錄 b,c(x,1)
  2.如果臨時(shí)表中有主鍵為b的行，就更新主鍵為b的這一行，并把c的值進(jìn)行加1
• 遍歷完成后，再根據(jù)字段 b做排序，得到結(jié)果集返回給客戶端。

在MySQL當(dāng)中排序有兩種
第一種是 全字段排序 ，第二種是 rowid 排序。 具體可以通過我的這篇文章去了解一篇文章搞懂MySQL的order by

3.使用group by會(huì)有哪些問題：

我們執(zhí)行以下語(yǔ)句

SELECT  b, count(*) as c from t1 GROUP BY b HAVING b<100

在上述中，由于b字段沒有索引，索引MySQL默認(rèn)會(huì)對(duì)此語(yǔ)句進(jìn)行排序。相信你也看了我的一篇文章搞懂MySQL的order by這篇文章，也了解了MySQL什么時(shí)候會(huì)用全字段排序，什么時(shí)候用 rowid 排序。

1. 使用磁盤臨時(shí)文件進(jìn)行排序

在此語(yǔ)句中，優(yōu)化器使用的是全字段排序，那么使用全字段排序會(huì)有哪些問題？
在全字段排序中，它是通過sort_buffer進(jìn)行排序，定義如下。

sort_buffer_size：就是 MySQL 為排序開辟的內(nèi)存（sort_buffer）的大小。如果要排序的數(shù)據(jù)量小于sort_buffer_size，排序就在內(nèi)存中完成。但如果排序數(shù)據(jù)量太大，內(nèi)存放不下，則不得不利用磁盤臨時(shí)文件輔助排序。

假如我們?nèi)缦耂QL語(yǔ)句查出有1億行，這時(shí)由于我們查詢數(shù)據(jù)大小超過了定義的 臨時(shí)排序文件（ sort_buffer_size）的大小時(shí)，

SELECT  b, count(*) as c from t1 GROUP BY b HAVING b<100000000

那么，這時(shí)就需要使用外部排序，外部排序一般使用歸并排序算法。可以這么簡(jiǎn)單理解，MySQL 將需要排序的數(shù)據(jù)分成 n份，每一份單獨(dú)排序后存在這些臨時(shí)文件中。然后把這n個(gè)有序文件再合并成一個(gè)有序的大文件。由于分成的的臨時(shí)文件很多，就會(huì)造成排序的性能很差。

2. 查詢數(shù)據(jù)超過臨時(shí)文件大小（tmp_table_size）

這個(gè)例子里由于臨時(shí)表只有 100 行，內(nèi)存可以放得下，因此全程只使用了內(nèi)存臨時(shí)表。但是，內(nèi)存臨時(shí)表的大小是有限制的，參數(shù) tmp_table_size 就是控制這個(gè)內(nèi)存大小的，默認(rèn)是 16M。接下來我把tmp_table_size大小改成1kb。

假如我們?nèi)缦耂QL語(yǔ)句查出有1億行，這時(shí)由于我們查詢數(shù)據(jù)大小超過了定義的 臨時(shí)文件（ tmp_table_size）的大小時(shí)。

SELECT  b, count(*) as c from t1 GROUP BY b HAVING b<100000000

那么，這時(shí)就會(huì)把內(nèi)存臨時(shí)表轉(zhuǎn)成磁盤臨時(shí)表（磁盤臨時(shí)表默認(rèn)使用的引擎是 InnoDB）。由于我們數(shù)據(jù)量很大，很可能這個(gè)查詢需要的磁盤臨時(shí)表就會(huì)占用大量的磁盤空間。

4.group by的優(yōu)化方法：

我們還是以這條SQL語(yǔ)句為準(zhǔn)

SELECT  b, count(*) as c from t1 GROUP BY b HAVING b<100

1. 如果你的需求并不需要對(duì)結(jié)果進(jìn)行排序，那你可以在 SQL 語(yǔ)句末尾增加 order by null

explain SELECT  b, count(*) as c from t1 GROUP BY b HAVING b<100 ORDER BY null 

查看explain 結(jié)果

Using temporary; 表示使用了臨時(shí)表；

2. 可以對(duì)group by字段建立索引

眾所周知，由于我們MySQL的InnoDB引擎使用的數(shù)據(jù)結(jié)構(gòu)是B+樹，而B+樹相比于B樹最顯著的特征就是B+樹葉子節(jié)點(diǎn)是一個(gè)有序的雙向列表。既然他已經(jīng)有序了，那么我們是不是可以直接不進(jìn)行排序了。

我們執(zhí)行以下SQL語(yǔ)句，并在字段b上建立索引

SELECT  b, count(*) as c from t1 GROUP BY b HAVING b<100

查看explain執(zhí)行情況，沒有進(jìn)行排序，沒有使用臨時(shí)文件

Using index；使用了覆蓋索引。
由于字段b上有索引，它的執(zhí)行順序是這樣的

執(zhí)行流程：

• 掃描t1表的索引b，讀取磁盤塊1，將磁盤塊加載到內(nèi)存中判斷50是否小于100，是就走左邊，不是就走右邊。

• 掃描磁盤塊2，將磁盤塊加載到內(nèi)存中判斷判斷30是否小于100，是就走左邊，不是就走右邊。

• 讀取磁盤塊3，將磁盤塊加載到內(nèi)存中判斷判斷20是否小100，是就走左邊，不是就走右邊。

• 讀取磁盤塊4也就是葉子節(jié)點(diǎn)，它是一個(gè)有序的鏈表。從鍵值10開始向后遍歷篩選所有符合篩選條件的數(shù)據(jù)，并將符合篩選條件的data值數(shù)據(jù)緩存到結(jié)果集。（因?yàn)槭请p向有序的，所以會(huì)依次讀取，并不需要回到父節(jié)點(diǎn)。因此當(dāng)讀取到20后會(huì)直接讀取磁盤塊5）
  1.當(dāng)碰到第一個(gè) 10 的時(shí)候，結(jié)果集里的第一行就是 (10,1);
  2.當(dāng)碰到第二個(gè) 10 的時(shí)候，已經(jīng)知道累積了 1 個(gè) 10，修改結(jié)果集里的第一行為(10,2);

• 當(dāng)依次讀取導(dǎo)磁盤塊12后，將磁盤塊加載到內(nèi)存中判斷100是否小于100，不是；就不需再向后查找，查詢終止。將結(jié)果集返回給用戶。

因此當(dāng)我們掃描到整個(gè)輸入的數(shù)據(jù)結(jié)束，就可以拿到 group by 的結(jié)果，不需要臨時(shí)表，也不需要再額外排序。

3. group by字段無法建立索引時(shí)

如果可以通過加索引來完成 group by 邏輯就再好不過了。但是，如果碰上不適合創(chuàng)建索引的場(chǎng)景，我們還是要老老實(shí)實(shí)做排序的。那么，這時(shí)候的 group by 要怎么優(yōu)化呢？

如果我們明明知道，一個(gè) group by語(yǔ)句中需要放到臨時(shí)表上的數(shù)據(jù)量特別大，卻還是要按照“先放到內(nèi)存臨時(shí)表，插入一部分?jǐn)?shù)據(jù)后，發(fā)現(xiàn)內(nèi)存臨時(shí)表不夠用了再轉(zhuǎn)成磁盤臨時(shí)表”，這樣看上去就有點(diǎn)兒傻。

我們執(zhí)行以下SQL語(yǔ)句，并使用 SQL_BIG_RESULT（SQL_BIG_RESULT告訴mysql的分組語(yǔ)句必須使用磁盤臨時(shí)表）

SELECT  SQL_BIG_RESULT b, count(*) as c from t1 GROUP BY b HAVING b<100

查看explain執(zhí)行情況

Using filesort 表示需要排序。

執(zhí)行流程：

• .初始化 sort_buffer，確定放入一個(gè)整型字段，記為 b；
• . 掃描表 t1 ，依次取出里面b<100的值, 將b的值存入 sort_buffer 中；
• . 掃描完成后，對(duì) sort_buffer 的字段 b 做排序（如果 sort_buffer 內(nèi)存不夠用，就會(huì)利用磁盤臨時(shí)文件輔助排序）；
• . 排序完成后，就得到了一個(gè)有序數(shù)組。

在根據(jù)有序數(shù)組，得到數(shù)組里面的不同值，以及每個(gè)值的出現(xiàn)次數(shù)。

5. MySQL8.0之后版本的group by

我們還是以這條SQL語(yǔ)句為準(zhǔn)（MySQL版本8.0.26）

SELECT  b, count(*) as c from t1 GROUP BY b HAVING b<100

查看explain執(zhí)行情況

Using temporary; 表示使用了臨時(shí)表；

group by 在 MySQL5.7 版本會(huì)自動(dòng)排序，但是在MySQL8 .0之后版本就去掉了自動(dòng)排序功能。

6.總結(jié)

1.如果對(duì) group by 語(yǔ)句的結(jié)果沒有排序要求，要在語(yǔ)句后面加 order by null；

2.盡量讓 group by 過程用上表的索引，確認(rèn)方法是 explain 結(jié)果里沒有 Using temporary 和 Using filesort；

3.如果 group by 需要統(tǒng)計(jì)的數(shù)據(jù)量不大，盡量只使用內(nèi)存臨時(shí)表；也可以通過適當(dāng)調(diào)大 tmp_table_size 參數(shù)，來避免用到磁盤臨時(shí)表；

4.如果數(shù)據(jù)量實(shí)在太大，使用 SQL_BIG_RESULT 這個(gè)提示，來告訴優(yōu)化器直接使用排序算法得到 group by 的結(jié)果。

5.group by 在 MySQL5.7 版本會(huì)自動(dòng)排序，但是在MySQL8 .0之后版本就去掉了排序功能。

文章來源地址http://www.zghlxwxcb.cn/news/detail-451305.html

今晚我支持總監(jiān)，法國(guó)沖沖沖?。。?/h3>

到了這里，關(guān)于一篇文章了解MySQL的group by的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！