时间戳

首页 > 解决方案 > 在 MySQL JOIN 中使用 ON 或 USING 或 WHERE 性能更高吗？

mysql - 在 MySQL JOIN 中使用 ON 或 USING 或 WHERE 性能更高吗？

问题描述

让我们想象一下，我有一个foo和bar表同时id作为主键。

是以下查询：

SELECT * 
FROM foo 
JOIN bar
WHERE foo.id = bar.id
AND foo.id = :id

与以下查询一样高效：

SELECT * 
FROM foo 
JOIN bar ON foo.id = bar.id
AND foo.id = :id

或者：

SELECT * 
FROM foo 
JOIN bar USING (id)
AND foo.id = :id

EXPLAINS告诉我所有这些查询都是同义词，但我想知道使用多个索引时是否存在边缘情况？我没有找到任何关于该主题的明确文档。

附件：EXPLAINS结果。

mysql> EXPLAIN
    ->     SELECT *
    ->     FROM foo
    ->     JOIN bar
    ->     WHERE foo.id = bar.id
    -> ;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | foo   | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL        |  100 |   100.00 | Using index |
|  1 | SIMPLE      | bar   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.foo.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN
    ->     SELECT *
    ->     FROM foo
    ->     JOIN bar USING (id)
    -> ;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | foo   | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL        |  100 |   100.00 | Using index |
|  1 | SIMPLE      | bar   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.foo.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> EXPLAIN
    ->     SELECT *
    ->     FROM foo
    ->     JOIN bar ON foo.id = bar.id
    -> ;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | foo   | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL        |  100 |   100.00 | Using index |
|  1 | SIMPLE      | bar   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.foo.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

标签： mysqljoin

解决方案

1）对于子句USING，您必须使用两个表中都存在且名称相同的字段。
但是在子句中ON你可以有类似的东西：

FROM foo JOIN bar ON foo.id = bar.foo_id

2）如果您需要使用特定索引 - 根据您的索引指定字段（适用于using和on）。

3）如果您使用mysql- 您可以使用或USING两者ON都正常工作（没有人拒绝本条款中的任何一个）。

推荐阅读