京东太狠：100W数据去重，用distinct还是group by，说说理由？

喜欢ヅ旅行 2024-03-16 13:00 4阅读 0赞

### 背景说明： ###

Mysql调优，是大家日常常见的调优工作。所以，**Mysql调优**是一个**非常、非常核心的面试知识点**。在40岁老架构师 尼恩的**读者社群**(50+)中，其相关面试题是一个非常、非常高频的交流话题。

近段时间，有小伙伴面试京东，说遇到一个去重 调优的面试题：

> 100W记录去重，是用distinct还是group by，说说理由？

MYsql 设计的时候，如何高性能进行数据去重，也是调优的重点和难点，社群中，还遇到过大概的变种：

> 形式1：distinct效率更高还是group by效率更高？？
> 
> 形式2：为啥 group by 会导致 慢 mysql 产生？
> 
> 形式3：…， 后面的变种很多，都会收入 《尼恩Java面试宝典》。

这里尼恩给大家 对数据去重的 调优，做一下系统化、体系化的梳理，使得大家可以充分展示一下大家雄厚的 “技术肌肉”，**让面试官爱到 “不能自已、口水直流”**。

也一并把这个 题目以及参考答案，收入咱们的《[尼恩Java面试宝典][Java]》V73，供后面的小伙伴参考，提升大家的 3高 架构、设计、开发水平。

> 最新《尼恩 架构笔记》《尼恩高并发三部曲》、《尼恩Java面试宝典》 的PDF文件，请到文末公号【技术自由圈】获取

接下来，我们先来看一下distinct和group by的功能、使用和底层原理。

#### 文章目录 ####

*   *  背景说明：
     *  首先：来看distinct功能和用法
     *   *  distinct用法
         *  distinct多列去重
     *  其次：来看GROUP BY 的使用场景
     *  Group by底层原理
     *  group by在去重场景的使用
     *   *  单列去重
         *  多列去重
         *  group by 多列去重 和 单列去重的区别
     *  distinct和group by去重原理分析
     *   *  Group by的隐式排序
     *  那么，为啥要优先推荐group by呢？
     *  说在最后
     *  技术自由的实现路径 PDF：
     *   *   *  实现你的 架构自由：
             *  实现你的 响应式 自由：
             *  实现你的 spring cloud 自由：
             *  实现你的 linux 自由：
             *  实现你的 网络 自由：
             *  实现你的 分布式锁 自由：
             *  实现你的 王者组件 自由：
             *  实现你的 面试题 自由：

### 首先：来看distinct功能和用法 ###

DISTINCT 是一种用于去除 SELECT 语句返回结果中重复行的关键字。在使用 SELECT 语句查询数据时，如果结果集中包含重复的行，可以使用 SELECT DISTINCT 语句来去除这些重复的行。例如，以下语句返回一个包含重复行的结果集：

SELECT name FROM users;

可以使用以下语句来去除重复的行：

SELECT DISTINCT name FROM users;

这样就可以得到一个不包含重复行的结果集。需要注意的是，DISTINCT 关键字会对查询的性能产生一定的影响，因为它需要对结果集进行排序和去重的操作。因此，在使用 DISTINCT 关键字时需要谨慎，尽可能地使用索引来优化查询，以提高查询的性能。

#### distinct用法 ####

SELECT DISTINCT columns FROM table_name WHERE where_conditions;

例如：

mysql> select distinct age from student;
    +------+
    | age  |
    +------+
    |   10 |
    |   12 |
    |   11 |
    | NULL |
    +------+
    4 rows in set (0.01 sec)

DISTINCT 关键词用于返回唯一不同的值。放在查询语句中的第一个字段前使用，且**作用于主句所有列**。

注意：DISTINCT子句将所有NULL值视为相同的值

> 如果列具有NULL值，并且对该列使用DISTINCT子句，MySQL将保留一个NULL值，并删除其它的NULL值。

#### distinct多列去重 ####

distinct多列的去重，则是根据指定的去重的列信息来进行，

即只有**所有指定的列信息都相同**，才会被认为是重复的信息。

SELECT DISTINCT column1,column2 FROM table_name WHERE where_conditions;
    
    mysql> select distinct sex,age from student;
    +--------+------+
    | sex    | age  |
    +--------+------+
    | male   |   10 |
    | female |   12 |
    | male   |   11 |
    | male   | NULL |
    | female |   11 |
    +--------+------+
    5 rows in set (0.02 sec)

### 其次：来看GROUP BY 的使用场景 ###

GROUP BY 主要的使用场景是 在**分组 聚合**。

具体来说，GROUP BY 子句通常用于将查询结果按照一个或多个列进行分组，然后对每个组进行聚合计算。

例如，假设一个表存储了每个人的姓名、年龄和所在城市，可以使用 GROUP BY 子句按照城市对人进行分组，并计算每个城市的平均年龄或人口数量等统计信息。

GROUP BY 子句通常与聚合函数（例如 COUNT、SUM、AVG、MAX 和 MIN）一起使用，以计算每个组的聚合值。例如，可以使用 GROUP BY 子句和 COUNT 函数来计算每个城市中的人数。

但是，除了分组 聚合，GROUP BY 还可以用来 进行数据去重，并且在某些特定场景下，**性能超过 distinct**

需要注意的是：GROUP BY 子句会对结果集进行排序，因此可能会导致使用临时文件排序。如果查询中包含 ORDER BY 子句，使用不当会产生临时文件排序，容易产生慢 SQL 问题。

*具体该怎么优化呢？ 请持续 跟踪 尼恩 的《[尼恩Java面试宝典][Java]》，后面结合其他的面试题进行介绍。*

### Group by底层原理 ###

`group by`语句根据一个或多个列对结果集进行分组。在分组的列上通常配合 COUNT, SUM, AVG等函数一起使用。

假定有个需求：统计每个城市的用户数量。

对应的 SQL 语句如下：

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

执行部分结果如下：

![6b2a446c85c37df3b5e8fd5acb68e17f.png][]

先用**Explain**查看一下执行计划

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

![7ca761819871425e7ca27cfa14cade05.png][]

在 Extra 字段里面，我们可以看到以下信息：

*  用到了**Using temporary**, 表示执行时创建了一个内部临时表。  
    注意这里的临时表可能是**内存上的临时表，也有可能是硬盘上的临时表**，当然，如果临时表比较小，就是基于内存的，可以肯定的是： **基于内存的临时表的性能高，时间消耗肯定要比基于硬盘的临时表的实际消耗小**。
 *  用到了**Using filesort**， 表示执行过程中没有使用索引的排序，而是使用临时文件。  
    "Using filesort"是MySQL的EXPLAIN输出中的一个短语，表示查询需要使用临时文件对结果集进行排序。  
    这可能发生在查询包括ORDER BY子句或GROUP BY子句时，而数据库无法使用索引满足排序顺序。  
    使用临时文件对大型结果集进行排序可能会导致磁盘I/O和内存使用方面的昂贵开销，因此最好尽可能避免“Using filesort”。  
    一些避免文件排序的策略包括使用适当的索引优化查询，限制结果集的大小或修改查询以使用不同的排序算法。

那么`group by`语句为啥会同时用到临时表和临时文件 排序呢？

首先看下整个执行流程：

1.  在执行过程中首先创建内存临时表，表里有`city`, `num`两个字段，`city`为主键。
2.  扫描 user 表，依次取出一行数据，数据中`city`字段的值为 c;

*  如果临时表中没有主键为 c 的行， 则插入一条新纪录（c ， 1）；
 *  如果存在，则更新该行为 （c, num + 1)；

1.  遍历完后，再根据`city`进行排序，最后将结果集返回给客户端。

这个流程的执行示意图如下：

![1aea3d118e15baca9d899c69f03508e6.png][]

然后进入到第二阶段，进行内存排序。 其中对内存临时表的排序执行步骤，本质上和的`order by` 流程基本一致。

1.  数据从内存临时表中拷贝到`sort buffer`中
2.  `sort buffer`进行排序，根据实际情况采用全字段排序或rowid排序，
3.  排序结果写回到内存临时表中，
4.  从内存临时表中返回结果集。

流程示意图如下所示：

![e2e1ab091ae9dbd57f825c7fe895e48e.png][]

### group by在去重场景的使用 ###

分为两个去重场景进行介绍：

*  单列去重
 *  多列去重

单列去重和多列去重的区别在于去重的依据不同。

单列去重是指针对某一列数据进行去重，即将该列中重复的值只保留一个。例如，如果有一个包含重复数据的姓名列表，可以使用单列去重将重复的姓名去除，只保留一个。

多列去重是指针对多列数据进行去重，即将多列数据中重复的行只保留一行。例如，如果有一个包含姓名、年龄和所在城市的列表，可以使用多列去重将重复的姓名、年龄和城市都相同的行去除，只保留一行。

#### 单列去重 ####

GROUP BY 子句，大部分都是用于单列去重。

例如，假设有一个表包含学生姓名和所在城市，可以使用以下 SQL 语句进行单列去重，以获取不同城市的学生数量：

SELECT city, COUNT(DISTINCT name)  FROM student  GROUP BY city;

在上述语句中，使用 GROUP BY 子句对城市进行分组，并使用 COUNT 和 DISTINCT 函数计算每个城市中不同姓名的数量。这将返回一个结果集，其中每个行包含一个城市和该城市中不同姓名的数量，从而达到了单列去重的目的。

对于单列去重来说，group by的使用和distinct类似:

单列去重语法：

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

执行：

mysql> select age from student group by age;
    +------+
    | age  |
    +------+
    |   10 |
    |   12 |
    |   11 |
    | NULL |
    +------+
    4 rows in set (0.02 sec)

注意：和DISTINCT子句一样，group by将所有NULL值视为相同的值

> 如果列具有NULL值，并且对该列使用group by子句，MySQL将保留一个NULL值，并删除其它的NULL值。

#### 多列去重 ####

GROUP BY 子句也可以用于多列去重。

例如，假设有一个表包含学生姓名、所在城市和年龄，可以使用以下 SQL 语句进行多列去重，以获取不同城市、不同年龄的学生数量：

SELECT city, age, COUNT(DISTINCT name) 
    FROM student
    GROUP BY city, age;

在上述语句中，使用 GROUP BY 子句对城市和年龄进行分组，并使用 COUNT 和 DISTINCT 函数计算每个城市、年龄组合中不同姓名的数量。这将返回一个结果集，其中每个行包含一个城市、一个年龄和该城市、年龄组合中不同姓名的数量，从而达到了多列去重的目的。

多列去重语法：

SELECT column1, column2 FROM table_name WHERE where_conditions GROUP BY column1, column2;

多列去重例子：

mysql> select sex,age from student group by sex,age;
    +--------+------+
    | sex    | age  |
    +--------+------+
    | male   |   10 |
    | female |   12 |
    | male   |   11 |
    | male   | NULL |
    | female |   11 |
    +--------+------+
    5 rows in set (0.03 sec)

#### group by 多列去重 和 单列去重的区别 ####

GROUP BY 子句用于将结果集按照指定的列进行分组，并对每个组进行聚合操作。在 GROUP BY 子句中指定的列将成为聚合键，用于将结果集中的行分组。**在聚合操作之后，可以使用 GROUP BY 子句来去重**。

单列去重是指使用 GROUP BY 子句将结果集**按照单个列**进行分组，并对每个组进行聚合操作。例如，以下查询将返回一个包含不同城市名称的列表：

SELECT city FROM mytable GROUP BY city;

多列去重是指使用 GROUP BY 子句将结果集**按照多个列**进行分组，并对每个组进行聚合操作。例如，以下查询将返回一个包含不同城市和州的列表：

SELECT city, state FROM mytable GROUP BY city, state;

group by的原理是先对结果进行分组排序，然后返回**每组中的第一条**数据。所以，区别在于：**单列去重只按照一个列进行分组，而多列去重则按照多个列进行分组**。

### distinct和group by去重原理分析 ###

在大多数例子中，DISTINCT可以被看作是特殊的GROUP BY，它们的实现都基于分组操作，且都可以通过**松散索引扫描、紧凑索引扫描**来实现。

> 松散索引扫描和紧凑索引扫描都是 MySQL 中的索引扫描方式。
> 
> 松散索引扫描（Loose Index Scan）是指 MySQL 在使用索引进行查询时，如果索引中的数据不连续，MySQL 将会扫描整个索引树，直到找到符合条件的记录。这种扫描方式会增加查询的时间复杂度，因为需要扫描整个索引树。
> 
> 紧凑索引扫描（Tight Index Scan）是指 MySQL 在使用索引进行查询时，如果索引中的数据连续，MySQL 将会按照顺序读取索引数据块，直到找到符合条件的记录。这种扫描方式会减少查询的时间复杂度，因为可以按照顺序读取索引数据块，避免了扫描整个索引树。
> 
> 通常情况下，如果使用的是 InnoDB 存储引擎，MySQL 会自动选择使用紧凑索引扫描。但是，如果使用的是 MyISAM 存储引擎，或者查询条件中包含了不等于（<>）或不包含（NOT IN）操作符，MySQL 将会使用松散索引扫描。
> 
> 总的来说，紧凑索引扫描比松散索引扫描更快，因为它可以避免扫描整个索引树。但是，如果使用的是 MyISAM 存储引擎，或者查询条件中包含了不等于或不包含操作符，MySQL 将会使用松散索引扫描，这时候就需要注意查询的效率。

DISTINCT和GROUP BY都是**可以使用索引进行扫描搜索**的。

例如以下两条sql，我们对这两条sql进行分析，可以看到，在extra中，这两条sql都使用了紧凑索引扫描`Using index for group-by`。

所以，在一般情况下，对于相同语义的DISTINCT和GROUP BY语句，我们可以对其使用相同的索引优化手段来进行优化。

但对于GROUP BY来说，在MYSQL8.0之前，GROUP Y默认会依据字段进行**隐式排序**。

可以看到，下面这条sql语句在使用了临时表的同时，还进行了filesort。

#### Group by的隐式排序 ####

对于隐式排序，我们可以参考Mysql官方的解释：

> GROUP BY 默认隐式排序（指在 GROUP BY 列没有 ASC 或 DESC 指示符的情况下也会进行排序）。然而，GROUP BY进行显式或隐式排序已经过时（deprecated）了，要生成给定的排序顺序，请提供 ORDER BY 子句。
> 
> 参考连接：
> 
> [MySQL :: MySQL 5.7 Reference Manual :: 8.2.1.14 ORDER BY Optimization][MySQL _ MySQL 5.7 Reference Manual _ 8.2.1.14 ORDER BY Optimization]

所以，在Mysql8.0之前,Group by会**默认**根据作用字段（Group by的后接字段）对结果进行**排序**。在能利用索引的情况下，Group by不需要额外进行排序操作；但当无法利用索引排序时，Mysql优化器就不得不选择通过使用临时表然后再排序的方式来实现GROUP BY了。要命的是，**当临时结果集的大小超出系统设置临时表大小时，Mysql会将临时表数据copy到磁盘上面再进行操作**，语句的执行效率会变得极低。这也是Mysql选择将此操作（隐式排序）**弃用**的原因。

基于上述原因，Mysql在8.0时，对此进行了优化更新：

> 从前（Mysql5.7版本之前），Group by会根据确定的条件进行隐式排序。
> 
> 在mysql 8.0中，已经移除了这个功能，所以不再需要通过添加order by null 来禁止隐式排序了，但是，查询结果可能与以前的 MySQL 版本不同。
> 
> 如果要生成给定顺序的结果，请按通过ORDER BY指定需要进行排序的字段。

[MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization][MySQL _ MySQL 8.0 Reference Manual _ 8.2.1.16 ORDER BY Optimization]

因此，我们的结论也出来了：

*  在语义相同，有索引的情况下：group by和distinct都能使用索引，效率相同。因为group by和distinct近乎等价，distinct可以被看做是特殊的group by。
 *  在语义相同，无索引的情况下：distinct效率高于group by。原因是： distinct 和 group by都会进行分组操作，但在Mysql8.0之前group by会进行隐式排序，导致触发filesort，sql执行效率低下。从Mysql8.0开始，Mysql就删除了隐式排序，**所以，此时在语义相同，无索引的情况下，group by和distinct的执行效率也是近乎等价的**。

总之，从Mysql8.0 开始， 不管有索引 、还是索引， group by和distinct的执行效率也是近乎等价的

但是， 100W级数据去重场景，优先推荐使用 group by。

### 那么，为啥要优先推荐group by呢？ ###

相比于distinct来说，group by的语义明确。

1.  group by语义更为清晰
2.  group by可对数据进行更为复杂的一些处理
3.  由于distinct关键字会对所有字段生效，在进行复合业务处理时，group by的使用灵活性更高，
4.  group by能根据分组情况，对数据进行更为复杂的处理，例如通过having对数据进行过滤，或通过聚合函数对数据进行运算。

所以，不论是100W级数据去重场景，还是普通数据去重场景，建议优先选用 group by

### 说在最后 ###

本文收录于 《尼恩Java面试宝典》 V73版PDF，请到文末公号【技术自由圈】获取

基本上，把尼恩的 《尼恩Java面试宝典》吃透，大厂offer很容易拿到。

最终，让面试官爱到 “不能自已、口水直流”。offer， 也就来了。

学习过程中，如果有啥问题，大家可以来 找 40岁老架构师尼恩交流。

### 技术自由的实现路径 PDF： ###

##### 实现你的 架构自由： #####

《[吃透8图1模板，人人可以做架构][8_1]》

《[10Wqps评论中台，如何架构？B站是这么做的！！！][10Wqps_B]》

《[阿里二面：千万级、亿级数据，如何性能优化？ 教科书级 答案来了][Link 1]》

《[峰值21WQps、亿级DAU，小游戏《羊了个羊》是怎么架构的？][21WQps_DAU]》

《[100亿级订单怎么调度，来一个大厂的极品方案][100]》

《[2个大厂 100亿级 超大流量 红包 架构方案][2_ 100_ _ _]》

*… 更多架构文章，正在添加中*

##### 实现你的 响应式 自由： #####

《[响应式圣经：10W字，实现Spring响应式编程自由][10W_Spring]》

这是老版本 《[Flux、Mono、Reactor 实战（史上最全）][Flux_Mono_Reactor]》

##### 实现你的 spring cloud 自由： #####

《[Spring cloud Alibaba 学习圣经][Spring cloud Alibaba]》

《[分库分表 Sharding-JDBC 底层原理、核心实战（史上最全）][Sharding-JDBC]》

《[一文搞定：SpringBoot、SLF4j、Log4j、Logback、Netty之间混乱关系（史上最全）][SpringBoot_SLF4j_Log4j_Logback_Netty]》

##### 实现你的 linux 自由： #####

《[Linux命令大全：2W多字，一次实现Linux自由][Linux_2W_Linux]》

##### 实现你的 网络 自由： #####

《[TCP协议详解 (史上最全)][TCP_]》

《[网络三张表：ARP表, MAC表, 路由表，实现你的网络自由！！][ARP_ MAC_]》

##### 实现你的 分布式锁 自由： #####

《[Redis分布式锁（图解 - 秒懂 - 史上最全）][Redis_ - _ -]》

《[Zookeeper 分布式锁 - 图解 - 秒懂][Zookeeper _ - _ -]》

##### 实现你的 王者组件 自由： #####

《[队列之王： Disruptor 原理、架构、源码 一文穿透][Disruptor _]》

《[缓存之王：Caffeine 源码、架构、原理（史上最全，10W字 超级长文）][Caffeine _10W_]》

《[缓存之王：Caffeine 的使用（史上最全）][Caffeine]》

《[Java Agent 探针、字节码增强 ByteBuddy（史上最全）][Java Agent _ ByteBuddy]》

##### 实现你的 面试题 自由： #####

[4000页《尼恩Java面试宝典 》 40个专题][Java]

以上尼恩 架构笔记、面试题 的PDF文件更新，▼请到下面【技术自由圈】公号取 ▼

[Java]: https://blog.csdn.net/crazymakercircle/article/details/124790425
[6b2a446c85c37df3b5e8fd5acb68e17f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/41130a78c7c24c1cb058ba578695e906.png
[7ca761819871425e7ca27cfa14cade05.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/87b855ee29cd403d883ab753abce6b52.png
[1aea3d118e15baca9d899c69f03508e6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/ec41546ef2ae494091da164dd72b7c45.png
[e2e1ab091ae9dbd57f825c7fe895e48e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/06a1f6f8e0344687a3354bf0a5a6cc0a.png
[MySQL _ MySQL 5.7 Reference Manual _ 8.2.1.14 ORDER BY Optimization]: https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
[MySQL _ MySQL 8.0 Reference Manual _ 8.2.1.16 ORDER BY Optimization]: https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
[8_1]: https://blog.csdn.net/crazymakercircle/article/details/129204455
[10Wqps_B]: https://blog.csdn.net/crazymakercircle/article/details/129410795
[Link 1]: https://blog.csdn.net/crazymakercircle/article/details/128848309
[21WQps_DAU]: https://blog.csdn.net/crazymakercircle/article/details/128725701
[100]: https://blog.csdn.net/crazymakercircle/article/details/129145200
[2_ 100_ _ _]: https://blog.csdn.net/crazymakercircle/article/details/128697096
[10W_Spring]: https://blog.csdn.net/crazymakercircle/article/details/129022714
[Flux_Mono_Reactor]: https://blog.csdn.net/crazymakercircle/article/details/124120506
[Spring cloud Alibaba]: https://blog.csdn.net/crazymakercircle/article/details/129559428
[Sharding-JDBC]: https://blog.csdn.net/crazymakercircle/article/details/123420859
[SpringBoot_SLF4j_Log4j_Logback_Netty]: https://blog.csdn.net/crazymakercircle/article/details/125135726
[Linux_2W_Linux]: https://blog.csdn.net/crazymakercircle/article/details/128932396
[TCP_]: https://blog.csdn.net/crazymakercircle/article/details/114527369
[ARP_ MAC_]: https://blog.csdn.net/crazymakercircle/article/details/129334254
[Redis_ - _ -]: https://blog.csdn.net/crazymakercircle/article/details/116425814
[Zookeeper _ - _ -]: https://blog.csdn.net/crazymakercircle/article/details/85956246
[Disruptor _]: https://blog.csdn.net/crazymakercircle/article/details/128264803
[Caffeine _10W_]: https://blog.csdn.net/crazymakercircle/article/details/128123114
[Caffeine]: https://blog.csdn.net/crazymakercircle/article/details/113751575
[Java Agent _ ByteBuddy]: https://blog.csdn.net/crazymakercircle/article/details/126579528