首页 > 数据库 >MySQL 中的 distinct 和 group by 哪个效率更高？

MySQL 中的 distinct 和 group by 哪个效率更高？

时间：2024-01-31 14:14:28浏览次数：24  
标签：排序 GROUP distinct 索引 MySQL group NULL

先说大致的结论（完整结论在文末）：

  • 在语义相同，有索引的情况下：group by和distinct都能使用索引，效率相同。
  • 在语义相同，无索引的情况下：distinct效率高于group by。原因是distinct 和 group by都会进行分组操作，但group by可能会进行排序，触发filesort，导致sql执行效率低下。
基于这个结论，你可能会问：
  • 为什么在语义相同，有索引的情况下，group by和distinct效率相同？
  • 在什么情况下，group by会进行排序操作？
带着这两个问题找答案。接下来，我们先来看一下distinct和group by的基础使用。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/refman/5.7/en/order-by-optimization.html
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/refman/8.0/en/order-by-optimization.html
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_42615847/article/details/118342524

标签：排序,GROUP,distinct,索引,MySQL,group,NULL
From： https://www.cnblogs.com/ataoxz/p/17999121

相关文章

  • MySQL常用函数
    一）字符函数①length(str)函数获取参数值的字节个数对于utf-8字符集来说，一个英文占1个字节；一个中文占3个字节；对于gbk字符集来说，一个英文占1个字节；一个中文占2个字节；演示如下：selectlength('关注作者')as长度fromdual; ②concat(str1,str2,...)函数将字符串拼接，通过输入......
  • mysql 创建数据库、创建用户
    --设置密码SHOWVARIABLESLIKE'validate_password%';setglobalvalidate_password_check_user_name='ON';setglobalvalidate_password_policy='LOW';setglobalvalidate_password_length=6; --创建数据库createdatabaseifnotex......
  • Active Directory Group Policy Objects | 活动目录组策略对象 | 介绍 | 配置
    介绍ActiveDirectory（AD）的组策略对象（GroupPolicyObjects，简称GPO），在Windows环境中使用GPO来配置、管理和强制执行网络中的计算机和用户设置。组策略对象（GPO）是策略设置的虚拟集合，GPO具有唯一的名称，例如GUID。组策略设置包含在GPO中，GPO可以表示文档系统和ActiveDirectory......
  • 面试官：请说一下Mysql中count(1)、count(*)以及count(列)的区别？
    近期在Review项目代码时，发现同事们在查询MySQL行数时存在多样的方式，有的使用COUNT(1),有的用COUNT(id),还有人选择了COUNT(*)。这混杂的选择引发了我的思考。当然这三种count的方式也是众说纷纭，其中最大的分歧点就是COUNT(*)和COUNT(1)查询性能上，有人觉得COUNT(*)需要转换为COUN......
  • 在阿里云服务器上部署mysql，初始化出现问题
    mysql初始化出现问题：解决：参考链接：https://www.cnblogs.com/Roobbin/p/17772445.htmlhttps://blog.csdn.net/qq_35896718/article/details/131803958libm.so.6、libc.so.6错误由于glibc版本较低导致，从错误来看至少需要2.28的版本先查询版本：ll|greplibc.so.6wgethttp......
  • Debug: mysql_real_connect failed: errno: , error
    [ERROR：mysql_real_connectfailed:errno:,error]kubectllogstransform-pod-name-nkubeflow-->INFO:absl:MetadataStorewithgRPCconnectioninitializedWARNING:absl:mlmdclientInternalError:mysql_real_connectfailed:errno:,error:ERROR:absl:......
  • MySQL事务
    MySQL事务是一个执行单元，在mysql中，对数据的一次操作或多次操作可以组合成一个事务，这些操作要么同时成功执行，要么同时失败。一、事务的性质事务有四个基本特性，通常被称为ACID属性。原子性（Atomicity）:如果事务中的所有操作都成功，则事务被提交。如果事务中的任何操作失败，则事务......
  • Visual Studio 配置 MySQL
    1.将MySQL中的include文件和lib文件资源粘贴到VisualStudioinclude文件下和lib文件下找到MySQL的lib和include位置找到VisualStudio的include和bin文件位置将其放进去2.找到两个文件，放在创建的项目目录里在项目里，点击这个，打开文件夹位置......
  • Mysql存储过程设计
    存储过程的创建使用​--复制world.`user`所在库的表，复制名为user1CREATETABLEuser1asSELECT*fromworld.`user`DELETEfromuser1​--存储过程delimiter$CREATEPROCEDUREselect_all_user1()BEGINSELECT*fromuser1;END$delimiter;--存储过程调用​cal......
  • Zabbix升级MySQL数据库的故障
    收到一个MySQL安全漏洞告警，需要修复OracleMySQLJAN2024CriticalPatchUpdate因此想着升级MySQL，步骤如下：sudoapt-getupdatesudoapt-getupgrademysql-serversudosystemctlrestartmysqlmysql--version完成后Zabbix网页界面能打开，但是提示错误如下：TheZabbixdatabaseve......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99