假设我有一个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)
1(对于子句USING
,您必须使用两个表中存在且具有相同名称的字段。
但是在第ON
条中,您可以有类似以下内容:
FROM foo JOIN bar ON foo.id = bar.foo_id
2(如果您需要使用特定索引 - 根据您的索引指定字段(适用于using
和on
(。
3(如果您使用mysql
- 您可以使用或USING
其中任何一个ON
它们都可以正常工作(没有人拒绝任何此条款(。