MySQL中group by优化指南

简介：MySQL中group by优化指南，mysql优化

在MySQL中group by的作用是分组，通常会结合聚合函数一起使用，例如：分组统计。最为常见的统计商品销售数量，统计商品的购买用户数等。

大致SQL如下所示：

SELECT goods_name,count(*) as num FROM order_detail GROUP BY goods_id;

注意：如果group by的字段没有在返回的字段列表可能会报错，原因是你的MySQL启用了【ONLY_FULL_GROUP_BY】模式，我们可以关闭该模式来解决返回的字段不在group by中的错误问题。

接下来我们用【EXPLAIN】来查看【group by】的分析结果：

通过上面的SQL分析，我们发现Extra中有一个Using temporary，意思就是使用了临时表。分析Extra可以得知【group by】会触发临时表创建。MySQL对于临时表的创建可能是基于内存也可能是磁盘进行创建。

GROUP BY优化

group by在使用不当的时候，很容易就会产生慢SQL 问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。

因此对于group by的优化主要有以下几点：

1. 给分组字段添加索引

alter table 表名称 add index 索引名称(字段名);

2. 通过ORDER BY null 强制不适用排序

select COUNT(*) from orders WHERE add_time>=1626278400 AND add_time<=1626364799 GROUP BY source order by null;

3. 尽量使用内存临时表

上面说过了，GROUP BY会触发临时表，而临时表有可能是在内存中也有可能在磁盘上创建。什么时候在内存什么？时候在磁盘上呢？

当mysql需要创建临时表时，选择内存临时表还是磁盘临时表取决于参数 tmp_table_size 和 max_heap_table_size，当所需临时表的容量大于两者的最小值时，mysql就会使用磁盘临时表存放数据。

tmp_table_size：临时表的内存缓存大小

max_heap_table_size：内存引擎表的最大大小（临时表也是内存引擎表）

我们可以通过MySQL的配置文件设置这两个值:

[mysqld]
max_heap_table_size = 1024M
tmp_table_size = 1024M

设置后记得重启MySQL：service mysqld restart。

重启后我们进入MySQL，通过一下命令查询是否生效：

-- 查看内存临时表的大小
SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';

-- 查看最大内存引擎表的大小
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

如果不想重启可以通过一下命令设置（注意：默认单位：字节）：

SET GLOBAL tmp_table_size=1073741824;
SET GLOBAL max_heap_table_size=1073741824;

大家可以根据自身需求设置，最好不要太大，以免影响其他应用的运行。

4. 强制使用磁盘缓存临时表

如果数据量实在过大，大到内存临时表都不够用了，这时就转向使用磁盘临时表。而发现不够用再转向这个过程也是很耗时的，因此我们可以告诉 mysql 从一开始就使用磁盘临时表。

只需要在SQL中添加【SQL_BIG_RESULT】来强制使用磁盘缓存，SQL代码如下所示：

select SQL_BIG_RESULT COUNT(*) as order_num,source from orders WHERE add_time>=1626278400 AND add_time<=1626364799 GROUP BY source;

group by的优化今天就说到这里了，希望这篇文章对大家有所帮助。如果有什么不对的地方可以在我的公众号留言哦！

有什么不对的可以在我的公众号留言