MySQL 是否自动索引外键列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/304317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does MySQL index foreign key columns automatically?
提问by Dónal
Does MySQL index foreign key columns automatically?
MySQL 是否自动索引外键列？
采纳答案by Grant Limberg
回答by Grant Limberg
Apparently an index is created automatically as specified in the link robert has posted.
显然，索引是按照robert 发布的链接中指定的自动创建的。
InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.(This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.
InnoDB 需要外键和引用键的索引，以便外键检查可以快速并且不需要表扫描。在引用表中，必须有一个索引，其中外键列以相同的顺序列为第一列。如果引用表不存在，则会在引用表上自动创建此类索引。（这与某些旧版本形成对比，在这些旧版本中必须显式创建索引，否则外键约束的创建将失败。） index_name，如果给定，则如前所述使用。
回答by Robert Gamble
Yes, see InnoDB and FOREIGN KEY Constraints.
是的，请参阅InnoDB 和 FOREIGN KEY 约束。
回答by Thomas Lundstr?m
You don't get the index automatically if you do an ALTER TABLE (instead of CREATE TABLE), at least according to the docs(the link is for 5.1 but it's the same for 5.5):
如果您执行 ALTER TABLE（而不是 CREATE TABLE），则不会自动获取索引，至少根据文档（链接适用于 5.1，但与 5.5 相同）：
[...] When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.
[...] 当您使用 ALTER TABLE 向表添加外键约束时，请记住首先创建所需的索引。
回答by Fahmi
For those who are looking for quote from 5.7docs:
对于那些从5.7文档中寻找报价的人：
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.
MySQL 需要外键和引用键的索引，以便外键检查可以快速并且不需要表扫描。在引用表中，必须有一个索引，其中外键列以相同的顺序列为第一列。如果引用表不存在，则会在引用表上自动创建此类索引。如果您创建另一个可用于强制执行外键约束的索引，则此索引可能会在稍后被悄悄删除。index_name，如果给定，则如前所述使用。
回答by Wolf5370
As stated it does for InnoDB. At first I thought it was strange that many other (in particular MS SQL and DB2) doesn't. TableSpace scans are only better than index scans when there are very few table rows - so for the vast majority of cases a foreign key would want to be indexed. Then it kind of hit me - this doesn't necessarily mean it has to be a stand alone (one column) index - where it is in MySQL's automatic FK Index. So, may be that is the reason MS SQL, DB2 (Oracle I'm not sure on) etc leave it up to the DBA; after all multiple indexes on large tables can cause issues with performance and space.
如前所述，它适用于 InnoDB。起初我觉得很奇怪许多其他（特别是 MS SQL 和 DB2）没有。只有在表行很少时，表空间扫描才比索引扫描更好 - 因此对于绝大多数情况，外键希望被索引。然后它有点打击我 - 这并不一定意味着它必须是一个独立的（一列）索引 - 它在 MySQL 的自动 FK 索引中。所以，这可能是 MS SQL、DB2（我不确定是否使用 Oracle）等将它留给 DBA 的原因；毕竟大表上的多个索引会导致性能和空间问题。
回答by Navrattan Yadav
Yes, Innodbprovide this. You can put a foreign key name after FOREIGN KEYclause or leave it to let MySQL to create a name for you. MySQL automatically creates an index with the foreign_key_namename.
是的，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
回答by giapnh
Yes, Mysql index foreign key automatically when you create a table that has a foreign key to another one.
是的，当您创建一个具有指向另一个表的外键的表时，Mysql 会自动索引外键。
回答by Ali Raza
It's not possible to get index key automatically use
无法自动获取索引键使用
ALTER TABLE (NAME OF THE TABLE) ADD INDEX (FOREIGN KEY)
Name of the table which you have created for example photographs and FOREIGN KEY for example photograph_id. The code should be like this
您创建的表的名称，例如照片和 FOREIGN KEY 例如photograph_id。代码应该是这样的
ALTER TABLE photographs ADD INDEX (photograph_id);
