MySQL性能:在哪里加入 [英] MySQL Performance: JOIN ON vs WHERE

问题描述

可能的重复项:
MySQL:内部联接与何处
显式与隐式SQL连接

Possible Duplicates:
MySQL: Inner join vs Where
Explicit vs implicit SQL joins

使用"JOIN ON"和"WHERE"子句运行联接查询时，在性能上有什么区别吗? (无论表的数量或表中的条目数量如何)

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)

不确定该主题是否已经讨论过.即使是这样，我也想知道是否使用最新版本的mySQL(5.1及更高版本)，情况是否已更改.

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.

Explain语句清楚地表明要考虑的行数存在很大差异.

An Explain statement clearly shows a lot of difference in the number of rows taken for consideration.

我正在使用的语法是:

SELECT <field names>
FROM <table1>
JOIN <table2> ON <join_condition>
AND JOIN <table3> ON <join_condition> 
AND JOIN <table4> ON <join_condition> 
.... 

在哪里使用

SELECT <field names> 
FROM <table names list separated by comma> 
WHERE <join_condition> 
AND <join_condition> 
AND <join_condition> 
.... 

因此不确定使用JOIN ON或WHERE子句是否会有所不同.请协助.

So not sure whether usage of JOIN ON or WHERE clause would make a difference. Please assist.

推荐答案

下面对这两种方法进行了很好的分析:

Here is a good analysis of these two approaches: SQL left join vs multiple tables on FROM line?

这个解释是一般性的，我不太确定MySQL在这件事上做了什么.但无论哪种方式，关键是JOIN总是更加明确和清晰，并且可以从一个引擎移动到另一个引擎，而查询逻辑没有大的变化.

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性能:在哪里加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！