MySQL 使用联合或加入 - 什么更快
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2309936/
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
Use A Union Or A Join - What Is Faster
提问by Hyman Kada
I just wonder if you had a table and you unioned it would it be more efficent then using a join??
我只是想知道你是否有一张桌子并且你联合了它会比使用连接更有效吗？？
I do know that the join creates more columns but this is more theoretical - Will the union need to do a nested loop scan of the other table like a join would have to?
我确实知道连接会创建更多列，但这更具理论性 - 联合是否需要像连接那样对另一个表进行嵌套循环扫描？
回答by Michal Ciechan
Union will be faster, as it simply passes the first SELECT statement, and then parses the second SELECT statement and adds the results to the end of the output table.
Union 会更快，因为它只是传递第一个 SELECT 语句，然后解析第二个 SELECT 语句并将结果添加到输出表的末尾。
The Join will go through each row of both tables, finding matches in the other table therefore needing a lot more processing due to searching for matching rows for each and every row.
Join 将遍历两个表的每一行，在另一个表中查找匹配项，因此由于搜索每一行的匹配行，需要更多的处理。
EDIT
编辑
By Union, I mean Union All as it seemed adequate for what you were trying to achieve. Although a normal Union is generally faster then Join.
联合，我的意思是联合所有，因为它似乎足以满足您想要实现的目标。虽然普通的 Union 通常比 Join 快。
EDIT 2 (Reply to @seebiscuit 's comment)
编辑 2（回复@seebiscuit 的评论）
I don't agree with him. Technically speaking no matter how good your join is, a "JOIN" is still more expensive than a pure concatenation. I made a blog post to prove it at my blog codePERF[dot]net. Practically speaking they serve 2 completely different purposes and it is more important to ensure your indexing is right and using the right tool for the job.
我不同意他的看法。从技术上讲，无论您的连接有多好，“JOIN”仍然比纯连接更昂贵。我在我的博客codePERF[dot]net 上发表了一篇博客文章来证明这一点。实际上，它们有两个完全不同的目的，更重要的是确保您的索引正确并使用正确的工具来完成工作。
Technically, I think it can be summed using the following 2 execution plans taken from my blog post:
从技术上讲，我认为可以使用从我的博客文章中获取的以下 2 个执行计划来总结：
UNION ALLExecution Plan
UNION ALL执行计划
JOINExecution Plan
JOIN执行计划
Practical Results
实际结果
Practically speaking the difference on a clustered index lookup is negligible:
实际上，聚集索引查找的差异可以忽略不计：
回答by Ignacio Vazquez-Abrams
JOINand UNIONhave two different purposes. JOINis used to add additional tables to a query for the purpose of adding selection criteria and possibly additional columns. UNIONis used to combine the results of two distinct queries with the same columns. Asking which is more efficient is like asking which of "a loaf of bread" and "the blowing wind" is more "orange".
JOIN并且UNION有两个不同的目的。JOIN用于向查询添加附加表，以便添加选择条件和可能的附加列。UNION用于组合具有相同列的两个不同查询的结果。问哪个更有效就像问“一条面包”和“吹的风”哪个更“橙色”。
回答by Domas Mituzas
sorry to break your party, but a well written join will be faster than a union.
很抱歉打断你的聚会，但写得好的加入会比联合更快。
- it uses more lightweight statistics collection model (based on cardinality, rather than random dives)
- query will get parsedonly once (no need for multiple subselect evaluation)
- resultset will not be materialized in a temptable (it gets even for UNION ALL)
- 它使用更轻量级的统计收集模型（基于基数，而不是随机潜水）
- 查询只会被解析一次（不需要多个子选择评估）
- 结果集不会在一个临时表中具体化（它甚至对于 UNION ALL）
回答by Neil Knight
A single SELECT will use no more than one index per table. A UNION will use no more than one index per SELECT in the union.
单个 SELECT 将在每个表中使用不超过一个索引。UNION 将在联合中的每个 SELECT 使用不超过一个索引。
The UNION will make better use of indexes which could result in a faster query.
UNION 将更好地利用索引，从而加快查询速度。
