01 前言
哈喽,我是狗哥,好久不见呀!是的,我又又换了工作。最近一直在面试这几天刚好整理下在面试中被问到有意思的问题,也借此机会跟大家分享下。
这家企业的面试官有点意思,一面是个同龄小哥,一起聊了两个小时(聊到我嘴都干了)。二面是个从阿里出来的架构师,视频面试,我做完自我介绍之后,他一开场就问我:
对 MySQL 熟悉吗?
我一愣,随之意识到这是个坑。他肯定想问我某方面的原理了,恰好我研究过索引。就回
答:
对索引比较熟悉。
他:
group by 是怎么实现分组的?
还好我又复习,基本上 group by 用法、工作原理、怎么优化之类的都答到点子上。今天也跟大家盘一盘 group by,我将从原理讲到最终优化,给大家聊聊 group by,希望对你有所帮助。
国际惯例,先上思维导图。PS:文末有福利
02 一个简单的例子
还是借我们之前讲 order by 时创建的商品订单表来演示。建表语句:
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='商品订单表';
数据如下,我之前就导入准备好的:
数据准备好了,需求也来了。现在产品要求统计表中每个城市的下单人数,这个需求是不是很简单?sql 语句我们也可以很快给出:
select city, count(*) as num from sale_order group by city;
这条 sql 的结果也很快就出来了:
sql 我们会写,结果也很快就出来了。但是原理你知道么?执行流程又是怎样的呢?
03 group by 的原理
3.1 explain 分析
废话不多说,遇事不决 explain。想要知道 sql 的性能咋样,怎么执行的,都要用 explain 分析。想要知道 explain 的每个指标代表啥意思,可以看我之前的文章:《explain 很重要吗?》
注意到最后一列 Extra ,这列代表的是 sql 执行过程中会做什么?上图中这列有两个值,
一个是 Using temporary,一个是 Using filesort。
- Using temporary:代表需要用到临时表。OS:这是个啥???
- Using filesort:需要排序。OS:挖草,还需要排序???
要想搞明白为什么需要临时表和排序,我们就得分析 group by 的执行流程了。
3.2 执行流程
根据 explain 分析,我们知道执行过程中肯定有创建临时表和排序两个步骤,下面来分析一下:
- 创建内存临时表,表里面有两个字段:city 和 num;
- 全表扫描 sale_order 表,取出 city = 某城市(比如广州、深圳、上海,囊括你表里涉及到的城市)的记录
- 临时表没有 city = 某城市的记录,直接插入,并记为 (某城市,1);
- 临时表里有 city = 某城市的记录,直接更新,把 num 值 +1
- 重复步骤 2 直至遍历完成,根据 city 字段做排序,然后把结果集返回客户端。
至此整个过程就完事了。我知道这样不直观,所以我又画个图,方便你们理解:
图中最后一步,对内存临时表的排序,具体的细节在之前的 《order by 是怎么排序的?》一文章中已经有过介绍,欢迎点击跳转。同样是非常细节的一个 mysql 关键字,强烈推荐你去看下。