distinct和group by哪个更好？

Question

对于最简单的情况，我们都参考：

select id from mytbl 
group by id

和

select distinct id from mytbl

众所周知，在一些项目中，它们会生成同样的查询计划，比如像哪个更好：Distinct还是Group By。

然而在Hive中，前者只有一个reduce任务，而后者有很多。

从实验中，我发现GROUP BY比DISTINCT快10倍以上。

它们是不同的。

所以我学到的是：

GROUP-BY不会比DISTINCT差，有时甚至更好。

我想知道：

1. 如果这个结论是真的。

2. 如果是真的，我应该把DISTINCT视为一种逻辑上的便利方法，但为什么DISTINCT没有采用GROUP-BY更好的实现方式？

3. 如果是假的，我非常渴望了解其在大数据情况下的合理用法。

非常感谢！！:)

- Chiron

---

你链接的问题是关于MS SQL Server行为的。不同的引擎有不同的方法，不同的优化等。没有理由相信仅仅因为在MS SQL（某个特定版本）中group by和distinct是等价的，这也适用于Hive。你是否使用EXPLAIN来查看实际执行计划？ - Luaan

谢谢。我采用了你的方法，并花了两天时间研究了我的SQL查询计划。有趣的是，对于像上面这样简单的情况，查询计划是相同的（都使用group-by），但当我使用许多DISTINCT+UNION与GROUPBY+UNION时，它们就会不同。 - Chiron

也许这有一个很好的理由，或者可能只是疏忽（distinct 不再像过去那样广泛使用了 - 它曾经是针对设计不良的数据库的常用技巧）。你可以在 Hadoop/Hive 的问题跟踪器上发布此问题，但你仍然可能只能接受它。为什么两个类似的查询最终执行结果会大相径庭并不总是显而易见。 - Luaan

这就是重点！你让我想起了第三范式。完全同意你的观点。这太明显了，但我甚至没有意识到！我认为我从你的评论中得到了我想要的答案。DISTINCT只是一个hack。 - Chiron

Answer 1

你的经验很有趣。我没有看到使用单个reducer的distinct和group by效果之间的差异。也许这两种结构之间的优化器存在一些微妙的差异。

Hive中的一个“著名”示例是：

select count(distinct id)
from mytbl;

对比

select count(*)
from (select distinct id
      from mytbl
     ) t;

前者只使用一个reducer，而后者可以并行操作。这在我的经验中已经见过，并且有文献记录和讨论（例如，在这个演示文稿的第26页和第27页）。因此，distinct绝对可以利用并行性能。

我想随着Hive的成熟，这些问题会得到解决。然而，有趣的是Postgres也存在类似于COUNT(DISTINCT)的性能问题，尽管我认为其根本原因略有不同。

- Gordon Linoff

---

非常感谢！我花了过去两天时间研究了我的SQL查询计划。有趣的是，对于像上面这样简单的情况，查询计划是相同的（都使用group-by），但当我使用许多DISTINCT+UNION与GROUPBY+UNION时，它们是不同的。我所做的就是将每个“DISTINCT key”子句简单地改为“GROUP BY key”子句，没有进行任何其他结构修改。查询计划显示：在DISTINCT中，我的静态列，例如：<“TagString”作为标签>将被包括在组键中，但在使用“group by key”时则不会。 - Chiron

我不明白为什么一个更静态的值会影响GROUP-BY的速度。 - Chiron

非常感谢您！但我仍然觉得很难从您的回答中清楚地看到我的三个困惑的答案。如果您愿意为我解开这个问题，我会非常高兴使用您的回答。 - Chiron

Answer 2

我使用Postgres命令执行相同的任务并进行分析。 对于distinct查询：explain (analyze) select distinct product_id, size from logistic.product_stock where status = 'STOCK' 我得到了以下结果：

HashAggregate  (cost=2166.24..2232.35 rows=6611 width=23) (actual time=46.417..47.104 rows=3770 loops=1)
   Group Key: product_id, size
   Batches: 1  Memory Usage: 721kB
   ->  Seq Scan on product_stock  (cost=0.00..2050.57 rows=23133 width=23) (actual time=0.144..39.954 rows=22357 loops=1)
         Filter: ((status)::text = 'STOCK'::text)
         Rows Removed by Filter: 44930
 Planning Time: 0.126 ms
 Execution Time: 47.517 ms

对于分组查询explain (analyze) select product_id, size from logistic.product_stock where status = 'STOCK' group by product_id, size，我得到了以下结果：

HashAggregate  (cost=2166.24..2232.35 rows=6611 width=23) (actual time=40.519..41.273 rows=3775 loops=1)
   Group Key: product_id, size
   Batches: 1  Memory Usage: 721kB
   ->  Seq Scan on product_stock  (cost=0.00..2050.57 rows=23133 width=23) (actual time=0.053..34.159 rows=22362 loops=1)
         Filter: ((status)::text = 'STOCK'::text)
         Rows Removed by Filter: 44930
 Planning Time: 0.802 ms
 Execution Time: 41.768 ms

正如我们所看到的：在这两种情况下，步骤都是相同的。Seq scan -> Group key。

答案：无论您选择什么查询

附注：时间取决于缓存。

- AlexRut