在MySQL中将SELECT DISTINCT与UNION DISTINCT结合使用-有效果吗？ [英] Combining SELECT DISTINCT with UNION DISTINCT in MySQL - any effect?

问题描述

以下两个SQL语句在功能上相同：

The following two SQL statements are functionally identical:

SELECT DISTINCT a,b,c FROM table1
UNION DISTINCT
SELECT DISTINCT a,b,c FROM table2

和

SELECT a,b,c FROM table1
UNION DISTINCT
SELECT a,b,c FROM table2

...因为 DISTINCT应用于整个工会，因此在单个<$ c $中是多余的c> SELECT 。

...because "DISTINCT" is applied to the union as a whole, and so is redundant within the individual SELECT's.

（注意： UNION DISTINCT 等同于 UNION 本身，但为了清晰起见，我加入了 DISTINCT 关键字）

(NOTE: UNION DISTINCT is identical to just UNION by itself, but I included the DISTINCT keyword for clarity)

我的问题是，在MySQL中两者之间是否存在性能差异或执行计划差异？还是优化器将 SELECT DISTINCT 变成常规的 SELECT ？

My question here is, is there a performance difference, or execution-plan difference between the two in MySQL? Or are the SELECT DISTINCTs turned into regular SELECT's by the optimizer?

推荐答案

您需要检查执行计划。但是，我希望执行计划有所不同-或至少在某些情况下应该如此。

You need to check the execution plans. However, I would expect that the execution plans are different -- or at least they should be in some circumstances.

第一个查询：

SELECT DISTINCT a, b, c FROM table1
UNION DISTINCT
SELECT DISTINCT a, b, c FROM table2

可以很容易地利用 table1（a，b，c）和 table2（a，b，c） 之前做最后的 UNION 。这样可以通过减少数据大小来加快最终合并的速度。第二个查询没有这个优点。

can readily take advantage of indexes on table1(a, b, c) and table2(a, b, c) before doing the final UNION. This should speed the final union by reducing the size of the data. The second query doesn't have this advantage.

实际上，写此查询的最有效方法可能是拥有两个索引并使用：

In fact, the most efficient way to write this query would probably be to have the two indexes and use:

SELECT DISTINCT a, b, c FROM table1 t1
UNION ALL
SELECT DISTINCT a, b, c
FROM table2 t2
WHERE NOT EXISTS (SELECT 1 FROM table1 t1 WHERE t2.a = t1.a and t2.b = t1.b and t2.c = t1.c)

这几乎相同，尽管它可以处理第二个表中的 NULL 值有点不同。

This is almost identical, although it might handle NULL values in the second table a bit differently.

这篇关于在MySQL中将SELECT DISTINCT与UNION DISTINCT结合使用-有效果吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！