MySQL 性能：JOIN ON vs WHERE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5338920/
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
MySQL Performance: JOIN ON vs WHERE
提问by Vasanthakumar V
Possible Duplicates:
MySQL: Inner join vs Where
Explicit vs implicit SQL joins
可能的重复项：
MySQL：内部连接与其中
显式与隐式 SQL 连接
Is there any difference performance wise when we run join queries using "JOIN ON" and "WHERE" clause? (regardless of the number of tables or the number of entries in the tables)
当我们使用“JOIN ON”和“WHERE”子句运行连接查询时，性能上有什么不同吗？（不考虑表格的数量或表格中的条目数量）
Not sure whether this topic has already been discussed over. Even if so, I wish to know whether with the latest versions of mySQL(5.1 and above), things have changed.
不确定这个话题是否已经讨论过。即使是这样，我也想知道最新版本的 mySQL（5.1 及以上）是否发生了变化。
An Explain statement clearly shows a lot of difference in the number of rows taken for consideration.
解释语句清楚地显示了在考虑的行数方面存在很大差异。
The Syntax I am using is:
我使用的语法是：
Using JOIN ON
使用 JOIN ON
SELECT <field names>
FROM <table1>
JOIN <table2> ON <join_condition>
AND JOIN <table3> ON <join_condition>
AND JOIN <table4> ON <join_condition>
....
Using WHERE
使用哪里
SELECT <field names>
FROM <table names list separated by comma>
WHERE <join_condition>
AND <join_condition>
AND <join_condition>
....
So not sure whether usage of JOIN ON or WHERE clause would make a difference. Please assist.
所以不确定使用 JOIN ON 或 WHERE 子句是否会有所不同。请协助。
回答by DeveloperInToronto
Here is a good analysis of these two approaches: SQL left join vs multiple tables on FROM line?
这是对这两种方法的一个很好的分析：SQL 左连接与 FROM 行上的多个表？
This explanation is general, I'm not too sure what MySQL does in this matter; but either way the point is that a JOIN is always more explicit and clear and can be moved from one engine to another with no major changes in the logic of the query.
这个解释很笼统，我不太清楚MySQL在这件事上做了什么；但无论哪种方式，重点是 JOIN 总是更加明确和清晰，并且可以从一个引擎移动到另一个引擎，而不会对查询逻辑进行重大更改。
回答by Nick Clark
I suspect that using "JOIN" is doing a "LEFT JOIN". Try using an "INNER JOIN" and see if the explain statement is the same.
我怀疑使用“JOIN”是在做“LEFT JOIN”。尝试使用“INNER JOIN”并查看说明语句是否相同。
SELECT <field names>
FROM <table1>
INNER JOIN <table2> ON <join_condition>
INNER JOIN <table3> ON <join_condition>
INNER JOIN <table4> ON <join_condition>
...
When using an inner join, I would not expect there to be a performance difference between your two queries. I would worry more about having the proper indexes. Make sure the join conditions can make a match using an indexed column.
使用内部联接时，我不希望您的两个查询之间存在性能差异。我会更担心拥有适当的索引。确保连接条件可以使用索引列进行匹配。
