哪种MySQL JOIN查询更有效率？

Question

哪种MySQL JOIN查询更有效率？

4

给定以下表结构：

CREATE TABLE user (
   uid INT(11) auto_increment,
   name VARCHAR(200),
   PRIMARY KEY(uid)
);
CREATE TABLE user_profile(
   uid INT(11),
   address VARCHAR(200),
   PRIMARY KEY(uid),
   INDEX(address)
);

哪种连接查询更有效：#1，

SELECT u.name FROM user u INNER JOIN user_profile p ON u.uid = p.uid WHERE p.address = 'some constant'

或者 #2:

SELECT u.name FROM user u INNER JOIN (SELECT uid FROM user_profile WHERE p.address = 'some constant') p ON u.uid = p.uid

效率差别有多大？

- Leonth

我很好奇 #2 和这个查询之间的区别，或者是否有任何区别：SELECT name FROM user WHERE uid IN (SELECT uid FROM user_profile WHERE address = 'some constant'); - Drew

2

我猜测前者，因为我从未在MySQL中看到过子查询的出色性能。 - Greg K

4个回答

1

观察这些查询的解释，我们得到了以下内容：（行标题为id、select_type、table、type、possible_keys、key、key_len、ref、rows、extra）

1   SIMPLE  u   system  PRIMARY NULL    NULL    NULL    1   
1   SIMPLE  p   const   PRIMARY,address PRIMARY 4   const   1

而第二个的EXPLAIN是...

1   PRIMARY u   system  PRIMARY NULL    NULL    NULL    1   
1   PRIMARY <derived2>  system  NULL    NULL    NULL    NULL    1    
2   DERIVED p   ref address address 201     1   Using where

所以，第一个查询更简单，而简单通常更有效率。

然而，从你的CREATEs来看，将地址字段添加到用户表中会大大提高效率。由于个人资料与用户表（在uid上）是1对1的关系，因此可以合并表格并仍保持模式规范化。

然后，您的查询将是

SELECT u.name FROM user u WHERE u.address = 'some constant'

而且说明显示

1   SIMPLE  u   ref address address 201 const   1   Using where, using filesort

奇怪的是，简化模式使用文件排序，如果有大量行会很慢。

更多关于解释：http://dev.mysql.com/doc/refman/5.0/en/explain.html

- davethegr8

我在MySQL中从未见过这种查询中的文件排序，但在其他系统中，它被用于加速索引查找。它会从索引中获取行指针，并对它们进行排序，以便行查找是连续的。顺序访问表的好处胜过对索引结果进行排序所带来的成本。 - Quassnoi

我很惊讶它也进行了文件排序，但如果你有一个大表的话，这可能会成为性能瓶颈。如果你有大约1000个结果，那么将其写入文件、对文件中的行进行排序，然后继续处理需要一些时间。有趣的是，这是一种优化。我对数据库内部不是很熟悉。 - davethegr8

1

在这种情况下，filesort 不会写入文件。它确实可以写入文件（在计划中会反映为 using temporary），但在这种情况下不会写入文件。Filesort 的命名不当，应该只是一个 sort。 - Quassnoi

1

我应该说，我很惊讶在这里看到它，因为这是一个常量扫描，行指针已经按单个索引键排序。更甚的是，它应该被优化掉了，因为你的表中只有1行（如果你的计划正确且统计数据是最新的）。你使用的是哪个版本的MySQL？ - Quassnoi

我当时实际上有两行。显然我的本地主机上是4.1.20版本。天啊...我需要升级了。感谢您提供关于文件排序的详细信息，这很有用。 - davethegr8

0

不确定MySQL的查询引擎如何处理这个问题，但我的假设是第一个查询会更好地执行并且更有效率。

第一个查询也更标准，更易于阅读，因此更可取。

- Justin Niessner

0

答案通常取决于数据库收集的统计信息。第一种形式似乎更容易优化。

据我所知，MySQL 不太适合使用 IN... 查询和子查询。

- Dmitry

第一种形式确实更好，但是IN在MySQL中是一个特殊情况，而且它被优化得非常好。你可能想阅读这篇文章：http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ - Quassnoi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Quassnoi · Accepted Answer

第一种语法通常更高效。

MySQL缓冲派生查询，因此使用派生查询会剥夺user_profile作为连接中的驱动表的可能性。

即使user_profile是主导表，子查询结果也应首先进行缓冲，这意味着会对内存和性能产生影响。

对查询应用LIMIT将使第一个查询速度更快，而对第二个查询则不适用。

以下是示例计划。在表t_source中有一个索引(val, nid):

第一个查询：

EXPLAIN
SELECT  *
FROM    t_source s1
JOIN    t_source s2
ON      s2.nid = s1.id
WHERE   s2.val = 1

1, 'SIMPLE', 's1', 'ALL', 'PRIMARY', '', '', '', 1000000, ''
1, 'SIMPLE', 's2', 'ref', 'ix_source_val,ix_source_val_nid,ix_source_vald_nid', 'ix_source_val_nid', '8', 'const,test.s1.id', 1, 'Using where'

第二个查询：

EXPLAIN
SELECT  *
FROM    t_source s1
JOIN    (
        SELECT  nid
        FROM    t_source s2
        WHERE   val = 1
        ) q
ON      q.nid = s1.id

1, 'PRIMARY', '<derived2>', 'ALL', '', '', '', '', 100000, ''
1, 'PRIMARY', 's1', 'ref', 'PRIMARY', 'PRIMARY', '4', 'q.nid', 10000, 'Using where'
2, 'DERIVED', 's2', 'ref', 'ix_source_val,ix_source_val_nid,ix_source_vald_nid', 'ix_source_vald_nid', '4', '', 91324, 'Using index'

正如您所看到的，第二种情况只使用了索引的一部分，并且q被强制成为前导。

更新：

派生查询（这是此问题所涉及的内容）不应与子查询混淆。

虽然MySQL无法优化派生查询（在FROM子句中使用的查询），但子查询（在IN或EXISTS中使用的查询）则得到了更好的处理。

有关更多详细信息，请参见我的博客中的这些文章：