Group by 优化

最新推荐文章于 2024-01-29 13:56:45 发布

Mor_

最新推荐文章于 2024-01-29 13:56:45 发布

阅读量228

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_43420030/article/details/132301265

版权

CREATE TABLE `sale_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_code` varchar(64) NOT NULL COMMENT '用户编号',
  `goods_name` varchar(64) NOT NULL COMMENT '商品名称',
  `order_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  `city` varchar(64) DEFAULT NULL COMMENT '下单城市',
  `order_num` int(10) NOT NULL COMMENT '订单数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='商品订单表';

1、一个简单的例子

还是创建商品订单表来演示。建表语句：

数据如下，导入准备好的：

数据准备好了，需求也来了。现在产品要求统计表中每个城市的下单人数，这个需求是不是很简单？sql 语句也可以很快给出：

这条 sql 的结果也很快就出来了：

sql 会写，结果也很快就出来了。但是原理知道吗？执行流程又是怎样的呢？

2、group by 的原理

2.1 explain 分析

想要知道 sql 的性能咋样，怎么执行的，都要用 explain 分析。

注意到最后一列 Extra ，这列代表的是 sql 执行过程中会做什么？上图中这列有两个值，一个是 Using temporary，一个是 Using filesort。

Using temporary：代表需要用到临时表。
Using filesort：需要排序。

要想搞明白为什么需要临时表和排序，就得分析 group by 的执行流程了。

2.2 执行流程

根据 explain 分析，可以知道执行过程中肯定有创建临时表和排序两个步骤，下面来分析一下：

创建内存临时表，表里面有两个字段：city 和 num；
全表扫描 sale_order 表，取出 city = 某城市（比如广州、深圳、上海，囊括表里涉及到的城市）的记录

- 临时表没有 city = 某城市的记录，直接插入，并记为 (某城市，1)；
- 临时表里有 city = 某城市的记录，直接更新，把 num 值 +1

重复步骤 2 直至遍历完成，根据 city 字段做排序，然后把结果集返回客户端。

至此整个过程就完事了。这样不直观，所以画个图，方便理解：

图中最后一步，对内存临时表的排序。

3、group by 中使用 where & having

如果加上 where 或者 having 或者两者都加上的时候的执行过程是怎样的呢？

3.1 group by + where

现在产品又改需求统计每个城市下的下单人数，且下的订单量要大于 2。

按照惯例，看到 where 一般想到怎么优化？没错，加索引。

加索引：

alter table sale_order add index idx_order_num (order_num);

最终语句：

select city, count(*) as num from sale_order where order_num > 2 group by city;

结果：

explain 分析：

从上图得知，加上索引之后。这条语句命中了索引 idx_order_number，并且此时的 Extra 多了 Using index Condition 的执行计划。type 变成了 range 说明不用全表扫描。

解释下 Using index Condition：会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，常见于 where 中有 between > < 等条件的 sql 语句。

它的出现说明这个语句先走索引过滤掉不符合 where 条件的数据，再去统计，然后排序，最后返回客户端。流程如下：

创建内存临时表，表里面有两个字段：city 和 num；
根据索引 idx_order_num 找到大于 2 的数据的主键 ID；
通过主键 ID 取出 city = 某城市（比如广州、深圳、上海，囊括表里涉及到的城市）的记录；

- 临时表没有 city = 某城市的记录，直接插入，并记为 (某城市，1)；
- 临时表里有 city = 某城市的记录，直接更新，把 num 值 +1。

重复 2、3 步骤，直至找到所有吗，满足 order_num > 2 的记录。根据 city 字段做排序，然后把结果集返回客户端。

3.2 group by + having

现在产品又改需求统计每个城市的下单的人数，且总的下单人数需要在 100 以上。

根据需求很快写出 sql 语句：

select city, count(*) as num from sale_order group by city having num > 100;

再用 explain 分析一下，得出如下结果：

跟没加 having 的执行流程一样的？没看错，其实 having 不直接参与到执行计划中去，它是对结果集操作的，所以这里的加的 having 跟没加是一样的执行计划。画个图，大概就是这样的：

3.3 group by + where + having

现在产品又改需求统计每个城市的下单超过两单的人数，且总的人数需要在 100 以上。

按照惯例，给 where 条件加上索引：

alter table sale_order add index idx_order_num (order_num);

根据需求很快写出 sql 语句：

select city, count(*) as num from sale_order where order_nunm > 2 group by city having num > 100;

explain 结果：

执行流程：

创建内存临时表，表里面有两个字段：city 和 num；
根据索引 idx_order_num 找到大于 2 的数据的主键 ID；
通过主键 ID 取出 city = 某城市（比如广州、深圳、上海，囊括表里涉及到的城市）的记录；

- 临时表没有 city = 某城市的记录，直接插入，并记为 (某城市，1)；
- 临时表里有 city = 某城市的记录，直接更新，把 num 值 +1。

重复 2、3 步骤，直至找到所有吗，满足 order_num > 2 的记录。根据 city 字段做排序。
having 对结果集进行过滤，并返回客户端

不难看出这里的执行流程跟 4.1 一样就多了个 having 过滤

4、group by 优化

根据上面的分析，可以知道 group by 是需要创建临时表并且排序的。耗时也应该在这两个步骤，那应该从这两个步骤入手优化。

如果分组字段本身就是有序的，是不是就不用排序了？或者需求并没有要求排序是不是就可以优化了？如果必须使用临时表，是不是可以只用内存临时表呢？如果数据量实在是太大，是不是可以直接用磁盘临时表，而不是发现内存临时表不够大才用它呢？

以上可以总结出四个优化方案：

分组字段加索引
order by null 不排序
尽量使用内存临时表
SQL_BIG_RESULT

4.1 分组字段加索引

上面的 sql 中，city 没加索引，所以这时的 group by 还是要使用临时表的。那可不可以个组合索引 idx_city，结果如下所示：

select city, count(*) as num from sale_order group by city;

加索引：

alter table sale_order add index idx_city (city);

结果：

Extra 是不是 Using temporary 和 Using filesort 都没了？所以不用排序也不用临时表啦。那有 where 条件怎么办？那就加组合索引：

alter table sale_order add index idx_order_num_city(order_num,city);

但是这种情况只适用于 where 条件是等值的，如果有大于、小于的情况还是避免不了排序和使用临时表。适用情况：

select city, count(*) as num from sale_order where order_num = 2 group by city;

不适用情况：

select city, count(*) as num from sale_order where order_num > 2 group by city;

4.2 order by null 避免排序

如果需求是不用排序，就可以这样做。在 sql 末尾加上 order by null

select city, count(*) as num from sale_order where order_num > 2 group by city order by null;

从分析结果看，还是需要使用临时表的。

4.3 尽量使用内存临时表

内存临时表是啥？其实 mysql 临时表分内存临时表和磁盘临时表。

group by 在执行过程中使用内存临时表还是不够用，那就会使用磁盘临时表。内存临时表的大小是有限制的，mysql 中 tmp_table_size 代表的就是内存临时表的大小，默认是 16M。当然可以自定义社会中适当大一点，这就要根据实际情况来定了。

比如：可以设置成 32M，也就是 33554432 字节。

set tmp_table_size=33554432;

4.4 SQL_BIG_RESULT

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

有的，在 group by 语句中加入 SQL_BIG_RESULT 提示 MySQL 优化器直接用磁盘临时表。优化器分析，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以直接用数组存储。用法如下：

select SQL_BIG_RESULT city, count(*) as num from sale_order where group by city;

此时的执行过程就不需要创建临时表：

初始化 sort_buffer（排序缓冲区），放入 city 字段；
扫描 sale_order 表，取出 city 的值存入 sort_buffer 中；
扫描完成后，对 sort_buffer 的字段 city 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；
排序完成后，就得到了一个有序数组。
根据有序数组，得到数组里面的不同值，以及每个值的出现次数

5、group by 面试题

5.1 group by 一定要配合聚合函数使用吗？

不一定，以下 sql 语句，用 MySQL 5.7.13 运行是报错的；但是 MySQL 8.0 版本是没有问题的。

出现这个错误的原因是 mysql 的 sql_mode 开启了 ONLY_FULL_GROUP_BY 模式。查看 sql_mode：

select @@GLOBAL.sql_mode;

如果想要不做限制的话，直接重新设置 sql_mode 的值，把 ONLY_FULL_GROUP_BY 去掉即可。当然，开启这个要慎重，有可能会造成一些意想不到的错误，一般情况下还是加上这个设置比较稳妥。

5.2 group by 后面的一定要出现在 select 中吗？

不一定，有的就没报错。当然，这个还跟版本有关系。可以实践下。

select max(order_num) from sale_order group by city;

5.1 where & having 的区别？

where 用于条件筛选，having 用于分组后筛选
where 条件后面不能跟聚合函数，having 一般配合 group by 或者聚合函数（min、max、avg、count、sum）使用
where 用在 group by 之前，having 用在 group by 之后