alter查询用于添加约束的时间太长

Question

我想修改一个名为person的表，并希望使用office表向其添加外键

我使用的查询是

ALTER TABLE person
ADD CONSTRAINT person_Office_FK
FOREIGN KEY ( Office_id )
REFERENCES Office ( Office_id ) ;

表office有大约50万行，表person有大约500万行

这个问题花了很长时间，我不知道发生了什么。

Answer 1

在添加约束之前，请确保office表上的office_id上有聚集索引，person表上有office_id上的非聚集索引。

请记住，person表上的每个office_id事件都需要对照每个office_id记录进行检查。如果你不得不删除一份办公室记录，这也会加快速度。

您不希望禁用这些检查，因为您的约束将不受信任，并且您将无法获得外部密钥在查询优化器中提供的性能好处。

Answer 2

如果Office_id是Office的主键，请确保它有(主键)索引。这肯定会加快添加约束的速度。

此外，根据How to temporarily disable a foreign key constraint in MySQL?，您可以使用

SET FOREIGN_KEY_CHECKS=0;

若要禁用所有外键约束检查，可能在添加外键约束时也是如此。

Answer 3

您可以使用：

SET FOREIGN_KEY_CHECKS=0;

不过，在执行此操作之前，我通常会检查自己的外键，不过：

SELECT
  Person.*
FROM person
LEFT JOIN Office ON (person.Office_id = Office.Office_id)
WHERE Office.Office_id IS NULL
;

如果您可以安全地关闭FOREIGN_KEY_CHECKS，则该查询不应返回任何内容。