MySQL InnoDB是否总是需要每个外键约束的索引？ [英] Does MySQL InnoDB always require an index for each foreign key constraint?

问题描述

我正在使用phpMyAdmin。为了使用InnoDB建立一个外键约束（在Structure选项卡的Relation View链接下），看起来我需要为要添加约束的字段添加一个索引。这显然会影响表上插入/更新的性能，特别是如果我想添加几个约束。是否有可能在InnoDB中指定外键约束或关系完整性，而无需为必填字段创建索引？

非常感谢
JS，伦敦

解决方案

从 MySQL参考手册：

lockquote
InnoDB需要索引外国
键和引用键，以便
外键检查可以很快，而不是
需要一个表扫描。在
引用表中，必须有
索引，其中外键列
被列为
相同顺序中的第一列。这样的索引在引用表上自动创建
，如果不存在，则自动创建
。 （这与
相反，在一些老版本中，在
中，索引必须明确地创建
，否则创建外部
的关键约束将失败。）

I am using phpMyAdmin. In order to set up a foreign key constraint with InnoDB (under the "Relation View" link on the Structure tab) it appears that I need to add an index for the field to which I want to add the restraint. This obviously has an impact on performance of inserts/updates on the table, particularly if there are several constraints I want to add. Is it possible to specify a foreign key constraint or relational integrity in InnoDB without the need to create an Index for the required field?

Many thanks JS, London

解决方案

From the MySQL reference manual:

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.)

这篇关于MySQL InnoDB是否总是需要每个外键约束的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！