在MySQL中，新建立一張表，該表有三個(gè)字段，分別是id，a，b，插入1000條每個(gè)字段都相等的記錄，如下：

mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from t1 limit 10;
+----+------+------+
| id | a  | b  |
+----+------+------+
| 1 |  1 |  1 |
| 2 |  2 |  2 |
| 3 |  3 |  3 |
| 4 |  4 |  4 |
| 5 |  5 |  5 |
| 6 |  6 |  6 |
| 7 |  7 |  7 |
| 8 |  8 |  8 |
| 9 |  9 |  9 |
| 10 |  10 |  10 |
+----+------+------+
10 rows in set (0.00 sec)

   當(dāng)我們執(zhí)行下面包含group by的SQL時(shí)，查看執(zhí)行計(jì)劃，可以看到：

mysql> explain select id%10 as m, count(*) as c from t1 group by m limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| 1 | SIMPLE   | t1  | NULL    | index | PRIMARY,a   | a  | 5    | NULL | 1000 |  100.00 | Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

最后面有：

• using index：覆蓋索引
• using temporary：使用了內(nèi)存臨時(shí)表
• using filesort：使用了排序操作

   為了更好的理解這個(gè)group by語(yǔ)句的執(zhí)行過(guò)程，我畫(huà)一個(gè)圖來(lái)表示：

對(duì)照上面這個(gè)表，我們不難發(fā)現(xiàn)，這個(gè)group by的語(yǔ)句執(zhí)行流程是下面這樣的：

a、首先創(chuàng)建內(nèi)存臨時(shí)表，內(nèi)存表里有兩個(gè)字段m和c，主鍵是m；m是id%10，而c是統(tǒng)計(jì)的count(*) 個(gè)數(shù)

b、掃描表t1的索引a，依次取出葉子節(jié)點(diǎn)上的id值，計(jì)算id%10的結(jié)果，記為x；此時(shí)如果臨時(shí)表中沒(méi)有主鍵為x的行，就插入一個(gè)記錄(x,1);如果表中有主鍵為x的行，就將x這一行的c值加1；

c、遍歷完成后，再根據(jù)字段m做排序，得到結(jié)果集返回給客戶(hù)端。（注意，這個(gè)排序的動(dòng)作是group by自動(dòng)添加的。）

 如果我們不想讓group by語(yǔ)句幫我們自動(dòng)排序，可以添加上order by null在語(yǔ)句的末尾，這樣就可以去掉order by之后的排序過(guò)程了。如下：

mysql> explain select id%10 as m, count(*) as c from t1 group by m order by null;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra            |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE   | t1  | NULL    | index | PRIMARY,a   | a  | 5    | NULL | 1000 |  100.00 | Using index; Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

   可以看到，explain最后面的using filesort字樣已經(jīng)不見(jiàn)了。再來(lái)看下結(jié)果：

mysql> select id%10 as m, count(*) as c from t1 group by m;
+------+-----+
| m  | c  |
+------+-----+
|  0 | 100 |
|  1 | 100 |
|  2 | 100 |
|  3 | 100 |
|  4 | 100 |
|  5 | 100 |
|  6 | 100 |
|  7 | 100 |
|  8 | 100 |
|  9 | 100 |
+------+-----+
10 rows in set (0.00 sec)
mysql> select id%10 as m, count(*) as c from t1 group by m order by null;
+------+-----+
| m  | c  |
+------+-----+
|  1 | 100 |
|  2 | 100 |
|  3 | 100 |
|  4 | 100 |
|  5 | 100 |
|  6 | 100 |
|  7 | 100 |
|  8 | 100 |
|  9 | 100 |
|  0 | 100 |
+------+-----+
10 rows in set (0.00 sec)

   當(dāng)我們不加order by null的時(shí)候，group by會(huì)自動(dòng)為我們進(jìn)行排序，所以m=0的記錄會(huì)在第一條的位置，如果我們加上order by null，那么group by就不會(huì)自動(dòng)排序，那么m=0的記錄就在最后面了。

   我們當(dāng)前這個(gè)語(yǔ)句，表t1中一共有1000條記錄，對(duì)10取余，只有10個(gè)結(jié)果，在內(nèi)存臨時(shí)表中還可以放下，內(nèi)存臨時(shí)表在MySQL中，通過(guò)tmp_table_size來(lái)控制。

mysql> show variables like "%tmp_table%";
+----------------+----------+
| Variable_name | Value  |
+----------------+----------+
| max_tmp_tables | 32    |
| tmp_table_size | 39845888 |
+----------------+----------+
2 rows in set, 1 warning (0.00 sec)

   當(dāng)我們的結(jié)果足夠大，而內(nèi)存臨時(shí)表不足以保存的時(shí)候，MySQL就會(huì)使用磁盤(pán)臨時(shí)表，整個(gè)訪(fǎng)問(wèn)的速度就變得很慢了。那么針對(duì)group by操作，我們?nèi)绾蝺?yōu)化？

01

group by優(yōu)化之索引

   從上面的描述中不難看出，group by進(jìn)行分組的時(shí)候，創(chuàng)建的臨時(shí)表都是帶一個(gè)唯一索引的。如果數(shù)據(jù)量很大，group by的執(zhí)行速度就會(huì)很慢，要想優(yōu)化這種情況，還得分析為什么group by 需要臨時(shí)表？

   這個(gè)問(wèn)題其實(shí)是因?yàn)間roup by的邏輯是統(tǒng)計(jì)不同的值出現(xiàn)的次數(shù)，由于每一行記錄做group by之后的結(jié)果都是無(wú)序的，所以就需要一個(gè)臨時(shí)表存儲(chǔ)這些中間結(jié)果集。如果我們的所有值都是排列好的，有序的，那情況會(huì)怎樣呢？

例如，我們有個(gè)表的記錄id列是：

0,0,0,1,1,2,2,2,2,3,4,4，

當(dāng)我們使用group by的時(shí)候，就直接從左到右，累計(jì)相同的值即可。這樣就不需要臨時(shí)表了。

   上面的結(jié)構(gòu)我們也不陌生，當(dāng)我們以在某個(gè)數(shù)據(jù)列上創(chuàng)建索引的時(shí)候，這個(gè)列本身就是排序的，當(dāng)group by是以這個(gè)列為條件的時(shí)候，那么這個(gè)過(guò)程就不需要排序，因?yàn)樗饕亲匀慌判虻摹榱藢?shí)現(xiàn)這個(gè)優(yōu)化，我們給表t1新增一個(gè)列z，如下：

mysql> alter table t1 add column z int generated always as(id % 10), add index(z);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select z as m, count(*) as c from t1 group by z;
+------+-----+
| m  | c  |
+------+-----+
|  0 | 100 |
|  1 | 100 |
|  2 | 100 |
|  3 | 100 |
|  4 | 100 |
|  5 | 100 |
|  6 | 100 |
|  7 | 100 |
|  8 | 100 |
|  9 | 100 |
+------+-----+
10 rows in set (0.00 sec)

mysql> explain select z as m, count(*) as c from t1 group by z;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE   | t1  | NULL    | index | z       | z  | 5    | NULL | 1000 |  100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

   我們新增字段z，z的值是id%10之后的值，并且創(chuàng)建索引，再通過(guò)group by對(duì)這個(gè)z列進(jìn)行分組，可以看到，結(jié)果中已經(jīng)沒(méi)有臨時(shí)表了。

   所以，使用索引可以幫助我們?nèi)サ鬵roup by依賴(lài)的臨時(shí)表

02

group by優(yōu)化---直接排序

   如果我們已經(jīng)知道表的數(shù)據(jù)量特別大，內(nèi)存臨時(shí)表肯定不足以容納排序的時(shí)候，其實(shí)我們可以通過(guò)告知group by進(jìn)行磁盤(pán)排序，而直接跳過(guò)內(nèi)存臨時(shí)表的排序過(guò)程。

  其實(shí)在MySQL中是有這樣的方法的：在group by語(yǔ)句中加入SQL_BIG_RESULT這個(gè)提示（hint），就可以告訴優(yōu)化器：這個(gè)語(yǔ)句涉及的數(shù)據(jù)量很大，請(qǐng)直接用磁盤(pán)臨時(shí)表。當(dāng)我們使用這個(gè)語(yǔ)句的時(shí)候，MySQL將自動(dòng)利用數(shù)組的方法來(lái)組織磁盤(pán)臨時(shí)表中的字段，而不是我們所周知的B+樹(shù)。關(guān)于這個(gè)知識(shí)點(diǎn)，這里給出官方文檔的介紹：

SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if they are created, and prefers sorting to using a temporary table with a key on the GROUP BY elements. For SQL_SMALL_RESULT, MySQL uses in-memory temporary tables to store the resulting table instead of using sorting. This should not normally be needed.

   整個(gè)group by的處理過(guò)程將會(huì)變成：

a、初始化sort_buffer，確定放入一個(gè)整型字段，記為m；

b、掃描表t1的索引a，依次取出里面的id值, 將 id%100的值存入sort_buffer中；

c、掃描完成后，對(duì)sort_buffer的字段m做排序（如果sort_buffer內(nèi)存不夠用，就會(huì)利用磁盤(pán)臨時(shí)文件輔助排序）；

d、排序完成后，就得到了一個(gè)有序數(shù)組。類(lèi)似0,0,0,1,1,2,2,3,3,3,4,4,4,4這樣

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

昨天的文章中我們分析了union 語(yǔ)句會(huì)使用臨時(shí)表，今天的內(nèi)容我們分析了group by語(yǔ)句使用臨時(shí)表的情況，那么MySQL究竟什么時(shí)候會(huì)使用臨時(shí)表呢？

MySQL什么時(shí)候會(huì)使用內(nèi)部臨時(shí)表？

1、如果語(yǔ)句執(zhí)行過(guò)程可以一邊讀數(shù)據(jù)，一邊直接得到結(jié)果，是不需要額外內(nèi)存的，否則就需要額外的內(nèi)存，來(lái)保存中間結(jié)果；

2、如果執(zhí)行邏輯需要用到二維表特性，就會(huì)優(yōu)先考慮使用臨時(shí)表。比如union需要用到唯一索引約束， group by還需要用到另外一個(gè)字段來(lái)存累積計(jì)數(shù)。

以上就是MySQL group by語(yǔ)句如何優(yōu)化的詳細(xì)內(nèi)容，更多關(guān)于MySQL group by優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論