MySQL会自动为外键列建立索引吗？

Question

MySQL是否会自动为外键列建立索引？

- Don

Answer 1

是的，但仅适用于InnoDB。 InnoDB目前是唯一实现外键约束的预装表格格式。

- Grant Limberg

---

你有没有任何理由相信MySQL将来会为任何其他表类型允许在非索引列上使用外键？ - Robert Gamble

我真的无法回答那个问题。你可能需要查找一下即将在MySQL 6.0中发布的Maria和Falcon存储引擎，并查看它们是否支持非索引列上的外键。 - Grant Limberg

显然这不是真的。我有一个大表（100万条记录），count(*) where fkey=? 需要15秒钟。在fkey列上添加了一个索引，现在只需要不到一秒钟就能完成了。 - AbiusX

同样的实验，使用另一张表和1000万条记录。这是MySQL 5.1 InnoDB。该表有三个字段，一个是主键整数，另一个已经建立索引。第三个是指向另一张表主键的外键。在没有添加显式索引的情况下，查找在这里需要几秒钟的时间。从表中显示的索引也没有显示它的索引。 - AbiusX

@AbiusX，5.1版本可能太旧了，请参考下面MrAlexander的回答。 - Déjà vu

Answer 2

显然，根据罗伯特发布的链接，索引会自动创建。

InnoDB需要在外键和参考键上创建索引，以便外键检查可以快速进行，而不需要进行表扫描。在引用表中，必须有一个索引，其中外键列按相同顺序列出为第一列。如果不存在这样的索引，则会自动在引用表上创建。（这与某些旧版本形成对比，在这些版本中，必须明确创建索引或外键约束的创建将失败。）如前所述，如果给出index_name，则使用它。

InnoDB和FOREIGN KEY约束

- Mr Alexander

---

好的，我会尽力进行翻译。比起被选中的答案，这个答案更好，因为它提供了文档证明。 - GWed

引用文本似乎不再包含在MySQL文档中，因此不清楚这是否仍然正确。 - Courtney Miles

@user2045006，您可以参考doc 5.0以及doc 5.6获取确切的引用文本。 - sactiw

在当前文档中，文本只有轻微的更改（我认为意思相似）：“InnoDB允许外键引用任何索引列或一组列。但是，在被引用的表中，必须存在一个索引，其中被引用的列以相同的顺序作为第一列。” - Lucas Basquerotto

Answer 3

对于那些正在寻找来自5.7文档的引用的人：

MySQL要求在外键和参考键上建立索引，以便外键检查可以快速进行且不需要表扫描。 在引用表中，必须有一个索引，其中将外键列按相同顺序列为第一列。 如果不存在这样的索引，则会自动在引用表上创建此索引。 如果创建了另一个可用于强制执行外键约束的索引，则此索引可能会被静默删除。如果提供了index_name参数，则如前所述使用它。

- Fahmi

Answer 4

如果你使用ALTER TABLE（而不是CREATE TABLE）来操作，就不会自动获取索引，至少根据文档来说（链接指向5.1版本，但5.5版本也相同）：

[...] 当你使用ALTER TABLE来向表中添加外键约束时，记得先创建所需的索引。

- Thomas Lundström

---

我也在MySQL 5.6和MariaDB 10上尝试了一下，使用ALTER TABLE创建了一个索引。有趣的是，mysqlindexcheck报告该索引为“冗余索引”。我尝试删除它，但出现了以下错误：“ERROR 1553 (HY000): Cannot drop index 'index_name': needed in a foreign key constraint”。因此，无法删除该索引并保留外键。 - Ciprian Stoica

你可能需要修改你的回答。MySQL 总是 创建索引来加速外键检查，如果不存在的话。文档试图告诉你，在创建外键约束之前创建索引可能会稍微加快速度。例如，一个可以用作外键检查索引的复合键索引可以被InnoDB使用，而不是自动生成冗余索引。 - BMiner

Answer 5

正如所述，它对InnoDB有用。一开始我认为很奇怪，许多其他数据库（尤其是MS SQL和DB2）没有使用这个功能。只有在表行非常少的情况下，表空间扫描才比索引扫描更好 - 因此，在绝大多数情况下，外键需要被索引。然后我想到了一个点 - 这并不一定意味着它必须是独立的（单列）索引 - 就像MySQL的自动FK索引一样。因此，也许这就是MS SQL、DB2（我不确定Oracle）等将其留给DBA的原因；毕竟，在大型表上有多个索引可能会影响性能和空间。

- Wolf5370

---

你提到了关于复合键索引的一个好观点；然而，如果新创建的复合键索引满足了使外键检查快速的义务，MySQL将自动/静默删除自动生成的单键索引。老实说，我不知道为什么MS SQL、DB2和其他数据库不这样做。他们没有什么借口。我想不出在外键上自动生成索引会有害的用例。 - BMiner

Answer 6

是的，Innodb提供了这个功能。你可以在FOREIGN KEY子句后面加上外键名，或者让MySQL为你创建一个名字。MySQL会自动创建一个名为foreign_key_name的索引。

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

- Navrattan Yadav

Answer 7

是的，当您定义外键约束时，MySQL会自动为外键列创建索引。如果您已经在外键列上定义了索引，MySQL将不会创建新的索引。相反，它将使用现有的索引来强制执行外键约束。

- Alekhya Reddy

Answer 8

无法自动获取索引键

ALTER TABLE (NAME OF THE TABLE) ADD INDEX (FOREIGN KEY)

您创建的表的名称，例如照片，以及外键，例如photograph_id。代码应该像这样：

ALTER TABLE photographs ADD INDEX (photograph_id);

- Ali Raza