php - 使用 from list 和 left join 之间的区别

Question

我有两个具有以下结构的数据库表:

行动:

action_id   int(11) primary key
action_name     varchar(255)
action_module   varchar(45)

权限:

perm_id     int(11) primary key
perm_role   int(11) 
perm_action     int(11)
perm_status     int(11)

现在我必须通过输入以下数据来检查权限表中是否存在给定角色的条目:perm_role、action_name 和 action_module。

我准备了两个查询来验证上述条件，但我不知道哪个更好。有人可以指导我如何找到最好的吗:

查询1

SELECT perm_id FROM permissions 
LEFT JOIN actions ON action_id=perm_action 
WHERE perm_role=1 AND action_name='add' AND action_module='employee';

查询2:

SELECT perm_id FROM permissions, actions 
WHERE perm_role=1 AND perm_action=action_id AND action_name='add' 
AND action_module='employee';

我需要优化这些查询，因为这必须在向服务器发出的每个请求期间执行。开发环境为PHP-5.2.10和MySQL-5.1.35

Answer 1

出于多种原因，在两个表之间基于显式字段进行显式联接的第一个选项是更好的选择。

即使您不使用 JOIN 关键字，第二个选项仍然是有效的联接，因为它是隐式联接。这意味着您的数据库引擎将有效地在您的表列表之间执行它自己的联接。

在 MySQL 中，这种隐式连接是 CROSS JOIN，在 MySQL 中相当于 INNER JOIN，但是请注意，CROSS JOIN 不等于“标准”SQL 中的 INNER JOIN，因此，虽然您的第二个查询可能如果使用正确的 where 过滤正确构建，那么工作会很好，但它是不明确的。

因此，您的第二个查询(带有隐式连接)可以有效地生成 Cartesian product两个表之间(其中一个表中的每一行实际上都与另一个表中的每一行合并)。您几乎肯定不希望这样做，这是破坏 SQL 查询性能的一种方法，特别是如果至少一个表包含相当多的行!即使您的过滤子句(即您的 WHERE 子句)可以正确过滤掉您不希望仅返回正确结果集的行，它也会比显式定义您自己的显式联接效率低(即使大多数数据库引擎会尝试优化此类隐式查询)。您的第一个查询使用显式 LEFT JOIN，因此笛卡尔积不应该是可能的(假设您正在加入“合理”字段 - 从您的问题来看，表关系似乎是“合理”)。

此外，请注意，通过列出带有逗号分隔符的表来简单地暗示表之间的联接的优先级低于实际显式 JOIN 语句(至少自 MySQL v5.x 以来)，这可能会导致不正确的查询结果，尤其是在在 3 个或更多表之间进行联接的情况下，查询表达式中的歧义再次导致难以确定优先级，因此完全相同的查询可能会在数据库版本之间产生完全不同的结果。请参阅this link了解更多信息。

有关 MySQL 中各种 JOIN 类型的最佳信息来源是 MySQL 文档本身，可以在此处找到专门与联接相关的页面:

12.2.8.1. JOIN Syntax (MySql v5)

12.2.9.1. JOIN Syntax (MySql v6)

为了速度，我引用了下面最相关的部分:

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.

--

The evaluation of multi-way natural joins differs in a very important way that affects the result of NATURAL or USING joins and that can require query rewriting. Suppose that you have three tables t1(a,b), t2(c,b), and t3(a,c) that each have one row: t1(1,2), t2(10,2), and t3(7,10). Suppose also that you have this NATURAL JOIN on the three tables:

SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;

Previously, the left operand of the second join was considered to be t2, whereas it should be the nested join (t1 NATURAL JOIN t2). As a result, the columns of t3 are checked for common columns only in t2, and, if t3 has common columns with t1, these columns are not used as equi-join columns. Thus, previously, the preceding query was transformed to the following equi-join:

SELECT ... FROM t1, t2, t3WHERE t1.b = t2.b AND t2.c = t3.c;

That join is missing one more equi-join predicate (t1.a = t3.a). As a result, it produces one row, not the empty result that it should. The correct equivalent query is this:

SELECT ... FROM t1, t2, t3WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;

If you require the same query result in current versions of MySQL as in older versions, rewrite the natural join as the first equi-join.

--

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);CREATE TABLE t2 (i2 INT, j2 INT);CREATE TABLE t3 (i3 INT, j3 INT);INSERT INTO t1 VALUES(1,1);INSERT INTO t2 VALUES(1,1);INSERT INTO t3 VALUES(1,1);SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.