小胖问我:group by 怎么优化?(上)

简介: 哈喽,我是狗哥,好久不见呀!是的,我又又换了工作。最近一直在面试这几天刚好整理下在面试中被问到有意思的问题,也借此机会跟大家分享下。

01 前言


哈喽,我是狗哥,好久不见呀!是的,我又又换了工作。最近一直在面试这几天刚好整理下在面试中被问到有意思的问题,也借此机会跟大家分享下。


这家企业的面试官有点意思,一面是个同龄小哥,一起聊了两个小时(聊到我嘴都干了)。二面是个从阿里出来的架构师,视频面试,我做完自我介绍之后,他一开场就问我:


对 MySQL 熟悉吗?


我一愣,随之意识到这是个坑。他肯定想问我某方面的原理了,恰好我研究过索引。就回

答:


对索引比较熟悉。


他:


group by 是怎么实现分组的?


还好我又复习,基本上 group by 用法、工作原理、怎么优化之类的都答到点子上。今天也跟大家盘一盘 group by,我将从原理讲到最终优化,给大家聊聊 group by,希望对你有所帮助。


国际惯例,先上思维导图。PS:文末有福利


640.png


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='商品订单表';


数据如下,我之前就导入准备好的:


640.png


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


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


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


640.png


sql 我们会写,结果也很快就出来了。但是原理你知道么?执行流程又是怎样的呢?


03 group by 的原理


3.1 explain 分析


废话不多说,遇事不决 explain。想要知道 sql 的性能咋样,怎么执行的,都要用 explain 分析。想要知道 explain 的每个指标代表啥意思,可以看我之前的文章:《explain 很重要吗?》


640.png


注意到最后一列 Extra ,这列代表的是 sql 执行过程中会做什么?上图中这列有两个值,

一个是 Using temporary,一个是 Using filesort。


  • Using temporary:代表需要用到临时表。OS:这是个啥???
  • Using filesort:需要排序。OS:挖草,还需要排序???


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


3.2 执行流程


根据 explain 分析,我们知道执行过程中肯定有创建临时表和排序两个步骤,下面来分析一下:


  1. 创建内存临时表,表里面有两个字段:city 和 num;


  1. 全表扫描 sale_order 表,取出 city = 某城市(比如广州、深圳、上海,囊括你表里涉及到的城市)的记录


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


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


至此整个过程就完事了。我知道这样不直观,所以我又画个图,方便你们理解:


640.png


图中最后一步,对内存临时表的排序,具体的细节在之前的 《order by 是怎么排序的?》一文章中已经有过介绍,欢迎点击跳转。同样是非常细节的一个 mysql 关键字,强烈推荐你去看下。

相关文章
|
SQL 数据采集 NoSQL
One ID中的核心技术ID-Mapping究竟是怎么实现的?by彭文华
One ID中的核心技术ID-Mapping究竟是怎么实现的?by彭文华
|
SQL 关系型数据库 MySQL
深聊MySQL,从入门到入坟之:如何让order by、group by查询速度飞起来
深聊MySQL,从入门到入坟之:如何让order by、group by查询速度飞起来
127 0
|
存储 分布式计算 流计算
离线数据Join我懂,但是实时数据怎么做Join的? by彭文华
离线数据Join我懂,但是实时数据怎么做Join的? by彭文华
|
Linux
2020第一场 NOI Online 能力测试提高组试题
2020第一场 NOI Online 能力测试提高组试题
2020第三场 NOI Online 能力测试提高组试题
2020第三场 NOI Online 能力测试提高组试题
|
SQL 关系型数据库 MySQL
面试突击62:group by 有哪些注意事项?
面试突击62:group by 有哪些注意事项?
168 0
|
数据可视化 数据挖掘 BI
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(三)
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(三)
83 0
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(三)
|
算法 前端开发 数据挖掘
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(四)
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(四)
109 0
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(四)
|
SQL 机器学习/深度学习 关系型数据库
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(二)
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(二)
313 0
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(二)
|
关系型数据库 MySQL C语言
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(六)
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(六)
297 0
《趣味知识博文》小W与小L带你聊天式备考CDA Level Ⅰ(六)