在MySQL中,新建立一张表,该表有三个字段,分别是id,a,b,插入1000条每个字段都相等的记录,如下:
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)
当我们执行下面包含group by的SQL时,查看执行计划,可以看到:
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:使用了内存临时表
- using filesort:使用了排序操作
为了更好的理解这个group by语句的执行过程,我画一个图来表示:
对照上面这个表,我们不难发现,这个group by的语句执行流程是下面这样的:
a、首先创建内存临时表,内存表里有两个字段m和c,主键是m;m是id%10,而c是统计的count(*) 个数
b、扫描表t1的索引a,依次取出叶子节点上的id值,计算id%10的结果,记为x;此时如果临时表中没有主键为x的行,就插入一个记录(x,1);如果表中有主键为x的行,就将x这一行的c值加1;
c、遍历完成后,再根据字段m做排序,得到结果集返回给客户端。(注意,这个排序的动作是group by自动添加的。)
如果我们不想让group by语句帮我们自动排序,可以添加上order by null在语句的末尾,这样就可以去掉order by之后的排序过程了。如下:
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字样已经不见了。再来看下结果:
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)
当我们不加order by null的时候,group by会自动为我们进行排序,所以m=0的记录会在第一条的位置,如果我们加上order by null,那么group by就不会自动排序,那么m=0的记录就在最后面了。
我们当前这个语句,表t1中一共有1000条记录,对10取余,只有10个结果,在内存临时表中还可以放下,内存临时表在MySQL中,通过tmp_table_size来控制。
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)
当我们的结果足够大,而内存临时表不足以保存的时候,MySQL就会使用磁盘临时表,整个访问的速度就变得很慢了。那么针对group by操作,我们如何优化?
01
group by优化之索引
从上面的描述中不难看出,group by进行分组的时候,创建的临时表都是带一个唯一索引的。如果数据量很大,group by的执行速度就会很慢,要想优化这种情况,还得分析为什么group by 需要临时表?
这个问题其实是因为group by的逻辑是统计不同的值出现的次数,由于每一行记录做group by之后的结果都是无序的,所以就需要一个临时表存储这些中间结果集。如果我们的所有值都是排列好的,有序的,那情况会怎样呢?
例如,我们有个表的记录id列是:
0,0,0,1,1,2,2,2,2,3,4,4,
当我们使用group by的时候,就直接从左到右,累计相同的值即可。这样就不需要临时表了。
上面的结构我们也不陌生,当我们以在某个数据列上创建索引的时候,这个列本身就是排序的,当group by是以这个列为条件的时候,那么这个过程就不需要排序,因为索引是自然排序的。为了实现这个优化,我们给表t1新增一个列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之后的值,并且创建索引,再通过group by对这个z列进行分组,可以看到,结果中已经没有临时表了。
所以,使用索引可以帮助我们去掉group by依赖的临时表
02
group by优化---直接排序
如果我们已经知道表的数据量特别大,内存临时表肯定不足以容纳排序的时候,其实我们可以通过告知group by进行磁盘排序,而直接跳过内存临时表的排序过程。
其实在MySQL中是有这样的方法的:在group by语句中加入SQL_BIG_RESULT这个提示(hint),就可以告诉优化器:这个语句涉及的数据量很大,请直接用磁盘临时表。当我们使用这个语句的时候,MySQL将自动利用数组的方法来组织磁盘临时表中的字段,而不是我们所周知的B+树。关于这个知识点,这里给出官方文档的介绍:
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.
整个group by的处理过程将会变成:
a、初始化sort_buffer,确定放入一个整型字段,记为m;
b、扫描表t1的索引a,依次取出里面的id值, 将 id%100的值存入sort_buffer中;
c、扫描完成后,对sort_buffer的字段m做排序(如果sort_buffer内存不够用,就会利用磁盘临时文件辅助排序);
d、排序完成后,就得到了一个有序数组。类似0,0,0,1,1,2,2,3,3,3,4,4,4,4这样
e、根据有序数组,得到数组里面的不同值,以及每个值的出现次数。
昨天的文章中我们分析了union 语句会使用临时表,今天的内容我们分析了group by语句使用临时表的情况,那么MySQL究竟什么时候会使用临时表呢?
MySQL什么时候会使用内部临时表?
1、如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就需要额外的内存,来保存中间结果;
2、如果执行逻辑需要用到二维表特性,就会优先考虑使用临时表。比如union需要用到唯一索引约束, group by还需要用到另外一个字段来存累积计数。
以上就是MySQL group by语句如何优化的详细内容,更多关于MySQL group by优化的资料请关注其它相关文章!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]