个人中心
gpt4 book ai didi

MySQL group by语句如何优化

转载 作者：qq735679552 更新时间：2022-09-27 22:32:09 27 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章MySQL group by语句如何优化由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

   在MySQL中，新建立一张表，该表有三个字段，分别是id，a，b，插入1000条每个字段都相等的记录，如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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时，查看执行计划，可以看到:

?
1
2
3
4
5
6
7
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语句的执行过程，我画一个图来表示:

MySQL 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之后的排序过程了。如下:

?
1
2
3
4
5
6
7
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字样已经不见了。再来看下结果:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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来控制.

?
1
2
3
4
5
6
7
8
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，如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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优化的资料请关注我其它相关文章！ 。

原文链接：https://cloud.tencent.com/developer/article/1639458 。

最后此篇关于MySQL group by语句如何优化的文章就讲到这里了,如果你想了解更多关于MySQL group by语句如何优化的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

27 4 0
文章推荐： mysql实现从导出数据的sql文件中只导入指定的一个表
文章推荐： MySQL之导出整个及单个表数据的操作
文章推荐： NumPy 如何生成多维数组的方法
文章推荐： python生成器，可迭代对象，迭代器区别和联系
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com