MySQL InnoDB 是否总是需要每个外键约束的索引?

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

问题描述

我正在使用 phpMyAdmin.为了使用 InnoDB 设置外键约束(在结构"选项卡上的关系视图"链接下)，看来我需要为要添加约束的字段添加索引.这显然会对表上的插入/更新性能产生影响，特别是如果我想添加几个约束.是否可以在 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?

非常感谢JS，伦敦

推荐答案

来自 MySQL参考手册:

InnoDB 需要外部索引键和引用的键，以便外键检查可以快速而不是需要表扫描.在里面引用表，必须有一个外键列的索引被列为第一列相同的顺序.创建了这样的索引自动在参考表上如果它不存在.(这是在与一些旧版本相比，在必须创建哪些索引明确或创建外国关键约束会失败.)

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 是否总是需要每个外键约束的索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！