MySQL:连接的表的两个字段都需要索引吗？

Question

当我执行这样的 SQL 时；

SELECT * 
  FROM table_foo 
  JOIN table_bar
    ON table_foo.foo_id = table_bar.bar_id

我需要仅在 table_foo.foo_id 上建立索引吗？

或者 MySQL 是否同时使用 table_foo.foo_id 和 table_bar.bar_id 上的索引？

EXPLAIN的结果是这样的。

Answer 1

此查询有多种可能的执行计划:

SELECT f.*, b.* 
FROM table_foo f JOIN
     table_bar b
     ON f.foo_id = b.bar_id;

以下是一些示例:

1. 您想要避免(大概)的是嵌套循环连接，它逐行循环遍历一个表，然后对每一行循环遍历第二个表。
2. 扫描 foo 并使用 table_bar(bar_id) 上的索引查找 bar 中的每个值。从 bar 索引中的行 ID 获取每个匹配行的关联列。
3. 扫描bar并使用table_foo(foo_id)上的索引查找foo中的每个值。从 foo 索引中的行 ID 获取每个匹配行的关联列。
4. 使用合并联接扫描两个索引并查找每个表中的关联行。

这省略了其他选项，例如通常不使用索引的哈希连接。

因此，可能会使用其中一个或两个索引，具体取决于优化器实现的算法。也就是说，一个索引通常足以获得您想要的性能。但是，如果两个表上都有索引，则可以为优化器提供更多选项。