MySQL：内连接 vs Where

MySQL: Inner join vs Where

提问by Victor

Is there a difference in performance (in mysql) between

之间的性能（在mysql中）是否存在差异

Select * from Table1 T1 
Inner Join Table2 T2 On T1.ID = T2.ID

And

和

Select * from Table1 T1, Table2 T2 
Where T1.ID = T2.ID

?

?

回答by Patrick

As pulled from the accepted answer in question 44917:

从问题 44917 中已接受的答案中提取：

Performance wise, they are exactly the same (at least in SQL Server) but be aware that they are deprecating the implicit outer join syntax.

在性能方面，它们完全相同（至少在 SQL Server 中），但请注意，它们正在弃用隐式外连接语法。

In MySql the results are the same.

在 MySql 中，结果是一样的。

I would personally stick with joining tables explicitly... that is the "socialy acceptable" way of doing it.

我个人会坚持明确加入表格......这是“社会可接受”的做法。

回答by Ewan Heming

They are the same. This can be seen by running the EXPLAINcommand:

他们是一样的。这可以通过运行以下EXPLAIN命令看到：

mysql> explain Select * from Table1 T1 
    -> Inner Join Table2 T2 On T1.ID = T2.ID;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | T1    | index | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using index                                 |
|  1 | SIMPLE      | T2    | index | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain Select * from Table1 T1, Table2 T2 
    -> Where T1.ID = T2.ID;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | T1    | index | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using index                                 |
|  1 | SIMPLE      | T2    | index | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------------+
2 rows in set (0.00 sec)

回答by kuldeep.kamboj

Well one late answer from me, As I am analyzing performance of a older application which uses comma based join instead of INNER JOINclause.

我的一个迟到的答案，因为我正在分析使用基于逗号的连接而不是INNER JOIN子句的旧应用程序的性能。

So here are two tables which have a join (both have records more than 1 lac). When executing query which has a comma based join, it takes a lot longer than the INNER JOINcase.

所以这里有两个有连接的表（都有超过 1 lac 的记录）。当执行具有基于逗号的连接的查询时，它需要比这种INNER JOIN情况更长的时间。

When I analyzed the explain statement, I found that the query having comma join was using the join buffer. However the query having INNER JOINclause had 'using Where'.

当我分析解释语句时，我发现具有逗号连接的查询正在使用连接缓冲区。但是，具有INNER JOIN子句的查询具有“使用地点”。

Also these queries are significantly different, as shown in rows column in explain query. These are my queries and their respective explain results.

这些查询也有很大不同，如解释查询中的行列所示。这些是我的查询及其各自的解释结果。

explain select count(*) FROM mbst a , his_moneypv2 b 
        WHERE b.yymm IN ('200802','200811','201001','201002','201003') 
        AND a.tel3 != '' 
        AND a.mb_no = b.mb_no 
        AND b.true_grade_class IN (3,6)
        OR b.grade_class IN (4,7);

+----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+------+--------+---------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                                  | key                                  | key_len | ref  | rows   | Extra                                                               |
+----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+------+--------+---------------------------------------------------------------------+
|  1 | SIMPLE      | b     | index_merge | PRIMARY,mb_no,yymm,yymm_2,idx_true_grade_class,idx_grade_class | idx_true_grade_class,idx_grade_class | 5,9     | NULL |  16924 | Using sort_union(idx_true_grade_class,idx_grade_class); Using where |
|  1 | SIMPLE      | a     | ALL         | PRIMARY                                                        | NULL                                 | NULL    | NULL | 134472 | Using where; Using join buffer                                      |
+----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+------+--------+---------------------------------------------------------------------+

v/s

比/秒

explain select count(*) FROM mbst a inner join his_moneypv2 b 
        on a.mb_no = b.mb_no 
        WHERE b.yymm IN ('200802','200811','201001','201002','201003') 
        AND a.tel3 != '' 
        AND b.true_grade_class IN (3,6) 
        OR b.grade_class IN (4,7);

 +----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+--------------------+-------+---------------------------------------------------------------------+
 | id | select_type | table | type        | possible_keys                                                  | key                                  | key_len | ref                | rows  | Extra                                                               |
 +----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+--------------------+-------+---------------------------------------------------------------------+
 |  1 | SIMPLE      | b     | index_merge | PRIMARY,mb_no,yymm,yymm_2,idx_true_grade_class,idx_grade_class | idx_true_grade_class,idx_grade_class | 5,9     | NULL               | 16924 | Using sort_union(idx_true_grade_class,idx_grade_class); Using where |
 |  1 | SIMPLE      | a     | eq_ref      | PRIMARY                                                        | PRIMARY                              | 62      | shaklee_my.b.mb_no |     1 | Using where                                                         |
 +----+-------------+-------+-------------+----------------------------------------------------------------+--------------------------------------+---------+--------------------+------

回答by DRapp

Actually they are virtually the same, The JOIN / ON is newer ANSI syntac, the WHERE is older ANSI syntax. Both are recognized by query engines

实际上它们几乎相同，JOIN / ON 是较新的 ANSI 语法，WHERE 是较旧的 ANSI 语法。两者都被查询引擎识别

回答by krtek

The first query is easier to understand for MySQL so it is likely that the execution plan will be better and that the query will run faster.

对于 MySQL，第一个查询更容易理解，因此执行计划可能会更好并且查询将运行得更快。

The second query without the where clause, is a cross join. If MySQL is able to understand the where clause good enough, it will do its best to avoid cross joining all the rows, but nothing guarantee that.

没有 where 子句的第二个查询是交叉连接。如果 MySQL 能够很好地理解 where 子句，它会尽力避免交叉连接所有行，但不能保证。

In a case as simple as yours, the performance will be strictly identical.

在像您这样简单的情况下，性能将完全相同。

Performance wise, the first query will always be better or identical to the second one. And from my point of view it is also a lot easier to understand when rereading.

性能方面，第一个查询总是与第二个查询更好或相同。从我的角度来看，重读时也更容易理解。

回答by Piotr Salaciak

The comma in a FROM clause is a CROSS JOIN. We can imagine that SQL server has a select query execution procedure which somehow should look like that: 1. iterate through every table 2. find rows that meet join predicate and put it into result table. 3. from the result table, get only those rows that meets the where condition.

FROM 子句中的逗号是 CROSS JOIN。我们可以想象 SQL Server 有一个 select 查询执行过程，它应该看起来像这样： 1. 遍历每个表 2. 找到满足连接谓词的行并将其放入结果表中。3.从结果表中，只获取那些满足where条件的行。

If it really looks like that, then using a CROSS JOIN on a table that has a few thousands rows could allocate a lot of memory, when every row is combined with each other before the where condition is examined. Your SQL server could be quite busy then.

如果看起来确实如此，那么在检查 where 条件之前，当每一行彼此组合时，在具有数千行的表上使用 CROSS JOIN 可能会分配大量内存。那时您的 SQL 服务器可能会很忙。

回答by Alistair Laing

I would think so because the first example explicitly tells mysql which columns to join and how to join them where the second one mysql has to try and figure out where you want to join.

我会这么认为是因为第一个示例明确告诉 mysql 要加入哪些列以及如何将它们加入到第二个 mysql 必须尝试找出要加入的位置。

回答by oezi

the second query is just another notation for an inner join, so if there is a difference in porformance it's only because one query can be parsed faster than the other one - and that difference, if it exists, will be so tiny that you won't notice it.

第二个查询只是内连接的另一种表示法，所以如果性能上存在差异，那只是因为一个查询的解析速度比另一个查询快 - 而这种差异，如果存在，将非常小，你不会'没有注意到它。

for more information you could try to take a look at this question(and use the search on SO next time before asking a question that already is answered)

有关更多信息，您可以尝试查看此问题（并在下次提出已经回答的问题之前使用 SO 上的搜索）