关于java:京东一面MySQL-中的-distinct-和-group-by-哪个效率更高问倒一大遍

4次阅读

共计 6256 个字符，预计需要花费 16 分钟才能阅读完成。

先说大抵的论断（残缺论断在文末）：

  • 在语义雷同，有索引的状况下：group by和 distinct 都能应用索引，效率雷同。
  • 在语义雷同，无索引的状况下：distinct 效率高于 group by。起因是 distinct 和 group by 都会进行分组操作，但 group by 可能会进行排序，触发 filesort，导致 sql 执行效率低下。

基于这个论断，你可能会问：

  • 为什么在语义雷同，有索引的状况下，group by和 distinct 效率雷同？
  • 在什么状况下，group by会进行排序操作？

带着这两个问题找答案。接下来，咱们先来看一下 distinct 和 group by 的根底应用。

举荐一个开源收费的 Spring Boot 最全教程：

https://github.com/javastacks/spring-boot-best-practice

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 关键词用于返回惟一不同的值。放在查问语句中的第一个字段前应用，且作用于主句所有列。

如果列具备 NULL 值，并且对该列应用 DISTINCT 子句，MySQL 将保留一个 NULL 值，并删除其它的 NULL 值，因为 DISTINCT 子句将所有 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的应用和 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)
多列去重

语法：

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

执行：

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 的后接字段进行去重的。

例如：

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

distinct 和 group by 原理

在大多数例子中，DISTINCT能够被看作是非凡的 GROUP BY，它们的实现都基于分组操作，且都能够通过涣散索引扫描、紧凑索引扫描(对于索引扫描的内容会在其余文章中具体介绍，就不在此粗疏介绍了) 来实现。

DISTINCT和 GROUP BY 都是能够应用索引进行扫描搜寻的。例如以下两条 sql（只单单看表格最初 extra 的内容），咱们对这两条 sql 进行剖析，能够看到，在 extra 中，这两条 sql 都应用了紧凑索引扫描Using index for group-by。

所以，在个别状况下，对于雷同语义的 DISTINCT 和GROUP BY语句，咱们能够对其应用雷同的索引优化伎俩来进行优化。

mysql> explain select int1_index from test_distinct_groupby group by int1_index;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_distinct_groupby | NULL       | range | index_1       | index_1 | 5       | NULL |  955 |   100.00 | Using index for group-by |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set (0.05 sec)

mysql> explain select distinct int1_index from test_distinct_groupby;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_distinct_groupby | NULL       | range | index_1       | index_1 | 5       | NULL |  955 |   100.00 | Using index for group-by |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set (0.05 sec)

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

能够看到，上面这条 sql 语句在应用了长期表的同时，还进行了 filesort。

mysql> explain select int6_bigger_random from test_distinct_groupby GROUP BY int6_bigger_random;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                           |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|  1 | SIMPLE      | test_distinct_groupby | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97402 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1 row in set (0.04 sec)
隐式排序

对于隐式排序，咱们能够参考 Mysql 官网的解释：

  • https://dev.mysql.com/doc/ref…

GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators for GROUP BY columns). However, relying on implicit GROUP BY sorting (that is, sorting in the absence of ASC or DESC designators) or explicit sorting for GROUP BY (that is, by using explicit ASC or DESC designators for GROUP BY columns) is deprecated. To produce a given sort order, provide an ORDER BY clause.

大抵解释一下：

GROUP BY 默认隐式排序（指在 GROUP BY 列没有 ASC 或 DESC 批示符的状况下也会进行排序）。然而，GROUP BY 进行显式或隐式排序曾经过期（deprecated）了，要生成给定的排序程序，请提供 ORDER BY 子句。

所以，在 Mysql8.0 之前,Group by会默认依据作用字段（Group by的后接字段）对后果进行排序。在能利用索引的状况下，Group by不须要额定进行排序操作；但当无奈利用索引排序时，Mysql 优化器就不得不抉择通过应用长期表而后再排序的形式来实现 GROUP BY 了。

且当后果集的大小超出零碎设置长期表大小时，Mysql 会将长期表数据 copy 到磁盘下面再进行操作，语句的执行效率会变得极低。这也是 Mysql 抉择将此操作（隐式排序）弃用的起因。

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

  • https://dev.mysql.com/doc/ref…

Previously (MySQL 5.7 and lower), GROUP BY sorted implicitly under certain conditions. In MySQL 8.0, that no longer occurs, so specifying ORDER BY NULL at the end to suppress implicit sorting (as was done previously) is no longer necessary. However, query results may differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.

大抵解释一下：

从前（Mysql5.7 版本之前），Group by 会依据确定的条件进行隐式排序。在 mysql 8.0 中，曾经移除了这个性能，所以不再须要通过增加order by null 来禁止隐式排序了，然而，查问后果可能与以前的 MySQL 版本不同。要生成给定程序的后果，请按通过 ORDER BY 指定须要进行排序的字段。

因而，咱们的论断也进去了：

  • 在语义雷同，有索引的状况下：

group by和 distinct 都能应用索引，效率雷同。因为 group by 和 distinct 近乎等价，distinct 能够被看做是非凡的group by。

  • 在语义雷同，无索引的状况下：

distinct 效率高于 group by。起因是 distinct 和 group by 都会进行分组操作，但 group by 在 Mysql8.0 之前会进行隐式排序，导致触发 filesort，sql 执行效率低下。

但从 Mysql8.0 开始，Mysql 就删除了隐式排序，所以，此时在语义雷同，无索引的状况下，group by和 distinct 的执行效率也是近乎等价的。

举荐 group by 的起因

  1. group by语义更为清晰
  2. group by可对数据进行更为简单的一些解决

相比于 distinct 来说，group by的语义明确。且因为 distinct 关键字会对所有字段失效，在进行复合业务解决时，group by的应用灵活性更高，group by能依据分组状况，对数据进行更为简单的解决，例如通过 having 对数据进行过滤，或通过聚合函数对数据进行运算。

版权申明：本文为 CSDN 博主「猾枭」的原创文章，遵循 CC 4.0 BY-SA 版权协定，转载请附上原文出处链接及本申明。原文链接：https://blog.csdn.net/weixin_…

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿(2022 最新版)

2. 劲爆！Java 协程要来了。。。

3.Spring Boot 2.x 教程，太全了！

4. 别再写满屏的爆爆爆炸类了，试试装璜器模式，这才是优雅的形式！！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！

正文完
java
发表至： java
2023-02-07
 0
关于java:手把手教你IDEA创建SSM项目结构
面试阿里字节跳动90会被问到的微服务你确定不进来看看吗
springboot+jpa 整合与基本应用
关于java:Bridge-桥接模式

站内搜索

-「」