MySQL 数据库 group by 语句怎么优化？

一、一个简单使用示例

我这里创建一张订单表

CREATE TABLE `order_info` (  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',  `order_no` int NOT NULL COMMENT '订单号',  `goods_id` int NOT NULL DEFAULT '0' COMMENT '商品id',  `goods_name` varchar(50) NOT NULL COMMENT '商品名称',  `order_status` int NOT NULL DEFAULT '0' COMMENT '订单状态：1待支付，2成功支付，3支付失败，4已关闭',  `pay_type` int NOT NULL DEFAULT '0' COMMENT '支付方式：1微信支付，2支付宝支付',  `price` decimal(11,2) DEFAULT NULL COMMENT '订单金额',  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单信息表';

复制代码

同时也在表里插了一些数据

现在我们这里执行 group by 语句

select goods_name, count(*) as numfrom order_infogroup by goods_name

复制代码

很明显，这里就可以统计出来 每件商品一共有多少订单数据！

二、group by 原理分析

2.1、explain 分析

不同的数据库版本，用 explain 执行的结果并不一致,同样是上面 sql 语句

「MySQL 5.7 版本」

• Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表

• Extra 这个字段的 Using filesort 表示使用了排序

「MySQL 8.0 版本」

❝

我们通过对比可以发现：mysql 8.0 开始 group by 默认是没有排序的了！

❞

接下来我们来解释下，什么是临时表。

2.2、聊一聊 Using temporary

Using temporary 表示由于排序没有走索引、使用union、子查询连接查询,group_concat（）或count（distinct）表达式的求值等等会创建了一个内部临时表。

注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小。

但不是说多大临时数据都可以直接存在内存的临时表，而是当超过最大内存临时表的最大容量就是转为存入磁盘临时表

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

用户可以在 mysql 的配置文件里修改该两个参数的值，两者的默认值均为 16M。