大家好，我是君哥。

使用 MySQL 時，group by 是我們經常會用到的分組語句，可以幫我們做各種聚合統計工作。但有時候會發現 group by 語句執行很慢，可能是什么原因呢？今天來介紹一下。

1.簡介

下面我們創建一張員工表：

CREATE TABLE`db_staff` (
`staff_id` i  nt(8) NOTNULL AUTO_INCREMENT COMMENT'員工編號',
`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'創建時間',
`update_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'更新時間',
  PRIMARY KEY (`staff_id`),
KEY`union_idno_name_email` (`id_no`,`name`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1001DEFAULTCHARSET=utf8

然后往表中插入 20 條數據：

圖片

我們用一個簡單的分組函數，對年齡（age）進行分組

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

查詢結果如下圖：

圖片

可以看到，使用 group by 語句非常方便地對各年齡的員工數量做了統計。

2.查詢分析

那使用 group by 語句時為什么會執行慢呢？ 我們看一下這條 sql 的執行計劃：

圖片

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

注意：filesort 很容易被理解成文件排序，其實不然，MySQL 所有不走索引的排序統稱為 filesort，即使數據完全在內存中排序，執行計劃 Extra 也會顯示 Using filesort。

下面看一下這個語句的執行流程： 

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

3.如何優化

3.1 是否需要排序

在 MySQL 8.0 以前，GROUP BY 默認是會對分組字段做排序的，即使 sql 中沒寫 ORDER BY，也會排序。

而且，內存中排序要用到 sort_buffer，如果 sort_buffer 內存不夠，就需要依靠磁盤臨時表輔助排序，非常影響性能。

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

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

修改后再看執行計劃，Extra 字段中沒有了 Using filesort。

3.2 走索引

對分組字段加索引是最好的優化方法。我們對 age 字段加一個索引：

ALTER TABLE db_staff ADD KEY age_idx(age);

修改后我們再看一下執行計劃

圖片

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

3.3 磁盤臨時表

前面提到過，如果 sort_buffer 內存不夠，就需要依靠磁盤臨時表輔助排序。為了避免使用磁盤臨時表，可以考慮減小結果集，或者臨時增加 sort_buffer 大小。

對于內存臨時表也一樣，如果內存不夠，就需要依靠磁盤臨時表，可以通過修改 tmp_table_size 參數來避免使用磁盤臨時表。

3.4 應用層分組

對于非常復雜的聚合，可以考慮在應用層通過代碼分批處理，利用多線程并發處理能力提高效率。這樣可以減小數據庫壓力。

3.5 物理視圖

對于超大表，可以考慮增加物理視圖來代替 sql 分組，或者使用大數據工具。這樣可以同時減少數據庫和應用服務的壓力，但也帶來了額外維護物理視圖的工作量，結果集時效性低也不高。

4.總結

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