为什么MySQL中的UNION查询如此缓慢？

Question

当我将两个查询优化后，使它们的运行时间少于0.02秒，但是使用UNION连接后，结果查询需要超过1秒才能运行。而UNION ALL比UNION DISTINCT花费的时间更长。

我原本认为允许重复项会使查询速度更快，而不是更慢。 那我是不是最好单独运行这两个查询呢？ 我更倾向于使用UNION。

以一个简单的例子来说明：

SELECT name FROM t1 WHERE field1 = true

需要花费0.001秒时间

如果我执行以下操作

SELECT name FROM t1 WHERE field1 = false

需要0.1秒钟时间。

如果我接着运行

SELECT name FROM t1 WHERE field1 = true 
UNION ALL 
SELECT name FROM t1 WHERE field1 = false

需要超过1秒钟的时间。

- Greg

---

很遗憾，我不能（工作），不过我认为这是一个相当简单的查询。也许我需要一些关于MySQL如何执行UNION的见解。我注意到在我使用的连接查询的explain中，我正在使用文件排序和临时表，而每个单独的查询都没有。 - Greg

SELECT * FROM users WHERE age > 18; - ceejayoz

作为一个简单的例子，如果我执行SELECT name FROM t1 WHERE field1 = true，它需要0.001秒，而如果我执行SELECT name FROM t1 WHERE field1 = false，它需要0.1秒。如果我接着运行SELECT name FROM t1 WHERE field1 = true UNION ALL SELECT name FROM t1 WHERE field1 = false，那么它需要超过1秒钟的时间。这不是特定查询结果的问题。 - Greg

我不确定为什么这个10年前的问题有赏金。根据我的测试，MySQL没有这个问题：http://sqlfiddle.com/#!9/4668601/2 - smoore4

也许我们中的一些人期望每个SELECT都能立即查询，然后直接合并排序到客户端。 - William Entriken

Answer 1

当我将两个单独查询优化后，使它们的运行时间少于0.02秒，然后将它们联合起来时，产生的查询需要超过1秒才能运行。

您的查询是否包含ORDER BY...LIMIT子句？

如果您在UNION之后放置一个ORDER BY...LIMIT，它将应用于整个UNION，在这种情况下不能使用索引。

如果id是主键，此查询将立即完成：

SELECT  *
FROM    table
ORDER BY id
LIMIT 1

但是这个不会：

SELECT  *
FROM    table
UNION ALL
SELECT  *
FROM    table
ORDER BY id
LIMIT 1

另外，使用UNION ALL比使用UNION DISTINCT需要更长的时间。我认为允许重复项会使查询运行更快而不是更慢。

这似乎也与ORDER BY有关。对较小的数据集进行排序比对较大的数据集更快。

我是否真的最好分别运行这两个查询？我更喜欢使用UNION

你需要排序结果集吗？

如果不需要，只需删除最后的ORDER BY即可。

- Quassnoi

---

如果我需要删除 order by，查询时间为2.5秒，加上它可能会增加到45秒。 - shorif2000

@sharif：你期望什么样的回答？ - Quassnoi

不，你必须添加括号才能使 ORDER BY 和 LIMIT 应用于 UNION: (SELECT ...) UNION (SELECT ...) ORDER BY .. LIMIT.. - Rick James

Answer 2

关于UNION

• UNION DISTINCT（默认为UNION）必然较慢，因为它必须收集两个结果，然后去重。 但是由于向客户端返回的内容更少，因此可能会有一些补偿。
• 直到最近版本，所有的UNIONs都需要一个临时表来收集结果，因此UNION必然比两个独立的SELECTs慢。 最近（MySQL 5.7，MariaDB 10.1）改进了某些UNION ALL的情况，可以将数据从一个SELECT直接传递给客户端，然后再传递给其他SELECT。
• SELECT .. UNION SELECT .. ORDER BY ..等同于
  (SELECT .. UNION SELECT ..) ORDER BY .. -- 这样写
  (SELECT ..) UNION (SELECT .. ORDER BY ..) -- 不要这样写
  建议在每个SELECT周围始终使用圆括号。
• 排序（通过ORDER BY）可能会使所附加的任何语句（select或union）需要额外的时间。 它很有可能不会花费更少的时间。简而言之，优化器旨在做最快的事情，这可能碰巧是排序过的。
• 所有这些语句都适用于InnoDB; 不常使用的MyISAM可能会缺少一些最近的优化。
• 有时可以通过将单个SELECT带有一个OR变成一个UNION来加速查询，从而让两个索引被使用。

关于测试：

• 0.001秒的查询时间意味着您之前已经运行过该查询，并且结果已缓存在“查询缓存”中。通过关闭QC或添加SQL_NO_CACHE来避免这种情况。
• 选择WHERE flag = true (or false)有几种情况： flag是否有索引？flag是否几乎总是其中一个值？ 在那种情况下，索引将被使用，并且可能比另一种情况更快。
• 如果您不同意我的任何陈述，请提供相反的工作示例。

- Rick James

---

你关于 UNION .. ORDER BY 的陈述似乎不正确。请参阅：文档 "要将 ORDER BY 或 LIMIT 应用于单个 SELECT，请将该子句放在括号内，该括号包围 SELECT" 和 此 fiddle。因此，第一个查询等同于第三个查询。但也许我只是误读了你的回答 :-) - Paul Spiegel

@PaulSpiegel - 谢谢，已修复。 - Rick James

Answer 3

一个猜测： 由于您在一个表中查询了2个联合，可能是因为MySQL难以决定锁定策略，或者尝试了一些缓存，但在这里不起作用，因为您查询的是不相交的集合，尝试多线程访问（非常合理），但遇到了一些锁定/并发/文件查找问题。

联合通常也会采用更高的安全设置，因为这两个选择必须一致。如果将它们放入单独的事务中，则不会这样。

实验： 复制该表并将其联接。如果我是对的，它应该更快。

可能的解决方案： 将单个文件拆分为多个文件，以允许更好的并发策略。这不会/不应该解决锁定问题，但可以排除数据库中的多线程/搜索问题。

知道您使用哪种存储引擎会很有用。

这只是我的想法。现在无法在此处测试。

- AndreasT

---

我正在使用InnoDB和MyISAM。 - shorif2000

Answer 4

UNION ALL比UNION更快，因为普通的UNION期望在两个连接的数据集中存在需要删除的重复项。如果您可以通过内部WHERE子句确保没有重复项，则最好使用UNION ALL，并让数据库引擎优化内部选择。

在分组结果的结果上使用WHERE子句太昂贵了，因为您正在处理比所需更多的内部结果。此外，数据库引擎的优化无法处理-结果没有任何共同点。

有关详细信息，请查看此链接 https://dzone.com/articles/performance-tip-for-tuning-sql-with-union

- Mohamed El-Touny

Answer 5

你是在测量响应时间而不是检索所有数据的时间吗？

- erikkallen