关于|关于 group by 和 distinct 的优化之争关于groupby和distinct的优化之争

一直能在网上看到很多小视频和小文章，上来就讲sql优化，要用 group by，不要用 distinct，但是事实果真是 group by 优于 distinct 吗？对所有数据库和查询引擎来说都是这样吗？显然不是，这篇文章就梳理一下在 hive 和 mysql 中二者的区别！

hive 中 group by 和 distinct 孰优孰劣？
首先声明一下，hive是什么：
hive 不是数据库，hive 只是一个数据仓库工具，可以用来查询、转化和加载数据，是可以调用 mapreduce 任务、用类 mysql 语法查询HDFS数据的一个工具。
再来说 mapreduce 是什么，mapreduce 是分而治之的一种编程模型，适用于大规模数据集的并行计算，当处理一个查询任务时，先调用 map 任务并行处理，最后用 reduce 任务归约结果。
那么对于一张200w+的表，我们来看一下group by 和 distinct 的执行过程：

set mapreduce.map.java.opts="-Dfile.encoding=UTF-8"; set mapreduce.reduce.java.opts="-Dfile.encoding=UTF-8"; select province,city from dulux_dataset_67 group by province,city

【关于|关于 group by 和 distinct 的优化之争】

文章图片

set mapreduce.map.java.opts="-Dfile.encoding=UTF-8"; set mapreduce.reduce.java.opts="-Dfile.encoding=UTF-8"; select distinct province,city from dulux_dataset_67

文章图片

这里你可能就要问了，不是通常说 group by 的效率高于distinct吗？为什么distinct 和 group by 都调用了 18 个 reduce 任务，distinct 的时间还花费的少一些，其实在数据量不大的情况下，distinct 和 group by的差别不是很大，但是对于count(distinct *) 来说会发生数据倾斜，因为 hive 默认在处理COUNT这种“全聚合(full aggregates)”计算时，它会忽略用户指定的Reduce Task数，而强制使用 1，会发生数据倾斜

select count(0) from dulux_dataset_67

文章图片

但是仍然要看数据量的大小，在数据量小的情况下去重计数，虽然count(distinct *) 会发生数据倾斜，但是只有执行一次 mapreduce任务，而 select count(0) from(select field from table group by field) 这种要执行两遍 mapreduce 任务，总的时间花费可能不比前者少，如下例子：

select count(0) from (select sales_order_no from dulux_dataset_67 group by sales_order_no) a

文章图片

select count(distinctsales_order_no) from dulux_dataset_67

文章图片

但是针对上亿的数据量，数据倾斜就会浪费很多时间，甚至由于机器资源紧张导致运行失败，这种情况就建议使用group by了，不仅可以分组，还能配合聚合函数一起使用
那么对于mysql数据库来说，哪个性能更好呢？
在 Mysql8.0 之前 group by 会进行隐式排序，导致触发 filesort，sql 执行效率低下， distinct 效率高于 group by。但从 Mysql8.0 开始，Mysql 就删除了隐式排序，所以在语义相同，无索引的情况下，group by 和 distinct 的执行效率也是近乎等价的。
那为什么，大家都更推崇使用group by？

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