哪种MySQL JOIN查询更有效？

小编典典

哪种MySQL JOIN查询更有效？

sql

给定以下表结构：

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

效率相差多少？

阅读 159

2021-04-15

共1个答案

小编典典

第一种语法通常更有效。

MySQL缓冲派生查询，因此使用派生查询user_profile消除了成为联接中驱动表的可能性。

即使user_profile领先，子查询结果也应首先进行缓冲，这将对内存和性能产生影响。

将ALIMIT应用于查询会使第一个查询快得多，而第二个查询则不然。

这是示例计划。(val, nid)表格中有一个索引t_source：

第一个查询：

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）的处理要好得多。

2021-04-15