与交叉连接相比，内连接的性能 [英] Performance of inner join compared to cross join
问题描述
发出内部连接的效果与在 WHERE 子句中使用连接条件声明交叉连接相同.我注意到我公司中的很多人都使用交叉联接，而我会在其中使用内部联接.在更改其中一些查询后，我没有注意到任何显着的性能提升，并且想知道这是否只是巧合，或者 DBMS 是否透明地优化了这些问题(在我们的例子中是 MySql).这里有一个具体的例子供讨论:
The effect of issuing an inner join is the same as stating a cross join with the join condition in the WHERE-clause. I noticed that many people in my company use cross joins, where I would use inner joins. I didn't notice any significant performance gain after changing some of these queries and was wondering if it was just a coincidence or if the DBMS optimizes such issues transparently (MySql in our case). And here a concrete example for discussion:
SELECT User.*
FROM User, Address
WHERE User.addressId = Address.id;
SELECT User.*
FROM User
INNER JOIN Address ON (User.addressId = Address.id);
推荐答案
交叉联接产生的结果由来自两个或多个表的行的每个组合组成.这意味着如果表 A 有 6 行而表 B 有 3 行，则交叉联接将产生 18 行.这两个表之间没有建立任何关系 - 您实际上只是生成所有可能的组合.
Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has 6 rows and table B has 3 rows, a cross join will result in 18 rows. There is no relationship established between the two tables – you literally just produce every possible combination.
通过内连接，来自表的一行的列值与来自另一个(或相同)表的另一行的列值组合以形成单行数据.
With an inner join, column values from one row of a table are combined with column values from another row of another (or the same) table to form a single row of data.
如果将 WHERE 子句添加到交叉联接中，则它表现为内部联接，因为 WHERE 强加了限制因素.
If a WHERE clause is added to a cross join, it behaves as an inner join as the WHERE imposes a limiting factor.
只要您的查询符合常识和特定于供应商的性能指南 (i)，我喜欢将使用哪种连接类型的决定视为一个简单的品味问题.
As long as your queries abide by common sense and vendor specific performance guidelines (i), I like to think of the decision on which type of join to use to be a simple matter of taste.
(i) 特定于供应商的绩效指南
这篇关于与交叉连接相比，内连接的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
