MySQL 中的 UNION ALL 和 LIMIT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4008486/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UNION ALL and LIMIT in MySQL
提问by Fretre
Let's say I want to perform this query:
假设我要执行此查询：
(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION ALL
(SELECT a FROM t2 WHERE a=11 AND B=2)
UNION ALL
(SELECT a FROM t3 WHERE a=12 AND B=3)
ORDER BY a LIMIT 1000;
Is MySQL smart enough to skip "t3" if 550 results are available in "t1" and 450 in "t2"?
如果“t1”中有 550 个结果可用，“t2”中有 450 个结果可用，MySQL 是否足够聪明以跳过“t3”？
I'm looking at MySQL docs (http://dev.mysql.com/doc/refman/5.1/en/union.html) but can't seem to find the answer.
我正在查看 MySQL 文档（http://dev.mysql.com/doc/refman/5.1/en/union.html）但似乎无法找到答案。
采纳答案by Kel
As specified in UNION Syntax description (http://dev.mysql.com/doc/refman/5.1/en/union.html):
如 UNION 语法描述 (http://dev.mysql.com/doc/refman/5.1/en/union.html) 中所述：
The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.
UNION 的默认行为是从结果中删除重复的行。可选的 DISTINCT 关键字除了默认值之外没有任何效果，因为它也指定了重复行删除。使用可选的 ALL 关键字，不会发生重复行删除，结果包括来自所有 SELECT 语句的所有匹配行。
I suppose, that's the answer to your question.
我想，这就是你问题的答案。
回答by Yuval
Currently, MySQL will perform all selects on a union even if there are enough rows in the first few queries, as @Yuki Inoue mentioned in their answer. Using @user1477929's answer, you could re-write your query as:
目前，即使前几个查询中有足够的行，MySQL 也会对联合执行所有选择，正如@Yuki Inoue 在他们的回答中提到的那样。使用@user1477929 的回答，您可以将查询重写为：
(SELECT a FROM t1 WHERE a=10 AND B=1 LIMIT 1000)
UNION ALL
(SELECT a FROM t2 WHERE a=11 AND B=2 LIMIT 1000)
UNION ALL
(SELECT a FROM t3 WHERE a=12 AND B=3 LIMIT 1000)
ORDER BY a LIMIT 1000;
which will give you at most 1000 rows, and never scan more than 3000.
这最多会给你 1000 行，并且永远不会扫描超过 3000。
回答by Kel
It works for me I'm using MySQL.
它对我有用，我正在使用 MySQL。
but make sure the limit number is always the same for all
但请确保所有的限制数始终相同
in that example it gets you 3 results from each table
在那个例子中，它从每个表中得到 3 个结果
(SELECT a FROM t1 WHERE a=10 AND B=1 LIMIT 9)
UNION ALL
(SELECT a FROM t2 WHERE a=11 AND B=2 LIMIT 9)
UNION ALL
(SELECT a FROM t3 WHERE a=12 AND B=3 LIMIT 9)
回答by Yuki Inoue
If you have a very large table, then you can execute following query to see how smart MySQL optimizer is...
如果您有一个非常大的表，那么您可以执行以下查询以查看 MySQL 优化器的智能程度...
(
select * from very_large_table
union all
select * from very_large_table
) limit 1
Sadly, this query takes very long time, compared to following query.
遗憾的是，与以下查询相比，此查询需要很长时间。
select * from very_large_table limit 1
It seems that whenever result of UNIONis needed, MySQL first calculates the entire temporary table of the UNION result.
似乎每当UNION需要结果时，MySQL首先计算UNION结果的整个临时表。
version
版本
- MySQL 8.0.11
- MySQL 8.0.11
