大家好，我是君哥。

使用 MySQL 時(shí)，group by 是我們經(jīng)常會(huì)用到的分組語(yǔ)句，可以幫我們做各種聚合統(tǒng)計(jì)工作。但有時(shí)候會(huì)發(fā)現(xiàn) group by 語(yǔ)句執(zhí)行很慢，可能是什么原因呢？今天來(lái)介紹一下。

1.簡(jiǎn)介

下面我們創(chuàng)建一張員工表：

CREATE TABLE`db_staff` (
`staff_id` i  nt(8) NOTNULL AUTO_INCREMENT COMMENT'員工編號(hào)',
`id_no`varchar(20) DEFAULTNULLCOMMENT'員工姓名',
`name`varchar(20) DEFAULTNULLCOMMENT'員工姓名',
`email`varchar(200) DEFAULTNULLCOMMENT'郵件地址',
`age`tinyint(3) DEFAULTNULLCOMMENT'年齡',
`sex`tinyint(1) DEFAULT'0'COMMENT'性別，0:男 1:女',
`address`varchar(300) DEFAULTNULLCOMMENT'家庭住址',
`create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'創(chuàng)建時(shí)間',
`update_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'更新時(shí)間',
  PRIMARY KEY (`staff_id`),
KEY`union_idno_name_email` (`id_no`,`name`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1001DEFAULTCHARSET=utf8

然后往表中插入 20 條數(shù)據(jù)：

圖片

我們用一個(gè)簡(jiǎn)單的分組函數(shù)，對(duì)年齡（age）進(jìn)行分組

SELECT age,COUNT(age) ca FROM db_staff GROUP BY age;

查詢結(jié)果如下圖：

圖片

可以看到，使用 group by 語(yǔ)句非常方便地對(duì)各年齡的員工數(shù)量做了統(tǒng)計(jì)。

2.查詢分析

那使用 group by 語(yǔ)句時(shí)為什么會(huì)執(zhí)行慢呢？ 我們看一下這條 sql 的執(zhí)行計(jì)劃：

圖片

從執(zhí)行計(jì)劃可以看到，這條 sql 沒(méi)有走索引，并且使用到了臨時(shí)表（Using temporary）和排序(Using filesort)。

注意：filesort 很容易被理解成文件排序，其實(shí)不然，MySQL 所有不走索引的排序統(tǒng)稱為 filesort，即使數(shù)據(jù)完全在內(nèi)存中排序，執(zhí)行計(jì)劃 Extra 也會(huì)顯示 Using filesort。

下面看一下這個(gè)語(yǔ)句的執(zhí)行流程： 

• 創(chuàng)建一個(gè)內(nèi)存臨時(shí)表，表里有兩個(gè)字段 age 和 ca，其中 age 字段是主鍵。
• 掃描要查詢的表中記錄，取出 age 字段；
• 如果臨時(shí)表中有這條 age（比如 ag=20） 的記錄，則 ca 值加 1，否則插入一條新的記錄，比如（age=30,ca=1）；
• 4根據(jù) age 做排序，將結(jié)果返回。

3.如何優(yōu)化

3.1 是否需要排序

在 MySQL 8.0 以前，GROUP BY 默認(rèn)是會(huì)對(duì)分組字段做排序的，即使 sql 中沒(méi)寫(xiě) ORDER BY，也會(huì)排序。

而且，內(nèi)存中排序要用到 sort_buffer，如果 sort_buffer 內(nèi)存不夠，就需要依靠磁盤(pán)臨時(shí)表輔助排序，非常影響性能。

如果想要 sql 語(yǔ)句不排序，可以在 sql 尾部加 order by null，修改后的 sql 如下：

SELECT age,COUNT(age) ca FROM db_staff GROUP BY age ORDER BY NULL;

修改后再看執(zhí)行計(jì)劃，Extra 字段中沒(méi)有了 Using filesort。

3.2 走索引

對(duì)分組字段加索引是最好的優(yōu)化方法。我們對(duì) age 字段加一個(gè)索引：

ALTER TABLE db_staff ADD KEY age_idx(age);

修改后我們?cè)倏匆幌聢?zhí)行計(jì)劃

圖片

給 age 字段加上索引后，就不走臨時(shí)表和 filesort 了。

3.3 磁盤(pán)臨時(shí)表

前面提到過(guò)，如果 sort_buffer 內(nèi)存不夠，就需要依靠磁盤(pán)臨時(shí)表輔助排序。為了避免使用磁盤(pán)臨時(shí)表，可以考慮減小結(jié)果集，或者臨時(shí)增加 sort_buffer 大小。

對(duì)于內(nèi)存臨時(shí)表也一樣，如果內(nèi)存不夠，就需要依靠磁盤(pán)臨時(shí)表，可以通過(guò)修改 tmp_table_size 參數(shù)來(lái)避免使用磁盤(pán)臨時(shí)表。

3.4 應(yīng)用層分組

對(duì)于非常復(fù)雜的聚合，可以考慮在應(yīng)用層通過(guò)代碼分批處理，利用多線程并發(fā)處理能力提高效率。這樣可以減小數(shù)據(jù)庫(kù)壓力。

3.5 物理視圖

對(duì)于超大表，可以考慮增加物理視圖來(lái)代替 sql 分組，或者使用大數(shù)據(jù)工具。這樣可以同時(shí)減少數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)的壓力，但也帶來(lái)了額外維護(hù)物理視圖的工作量，結(jié)果集時(shí)效性低也不高。

4.總結(jié)

group by 語(yǔ)句是非常好用的分組聚合函數(shù)，但如果使用不上覆蓋索引，效率可能會(huì)非常低，尤其是表中數(shù)據(jù)量比較大的情況下。可以參考本文的方法進(jìn)行優(yōu)化。