MySQL INSERT INTO 带外键的表速度很慢

Question

我正在尝试将大约 1000 万条记录插入(通过 grails 从 postgres)到一个带有主键和 2 个外键的表中。如果我保留所有主键和外键以及随这些键自动生成的索引，大约需要 7.5 小时才能完成。如果我在插入之前删除所有键和索引，则执行所有插入只需 10 分钟。但是，当我使用 ALTER TABLE 重新添加 key 时，它需要很长时间(超过 7 小时)才能执行。有没有办法提高性能？

这个表链接到的概念表有大约 100 万条记录。

这是 CREATE TABLE 语句:

CREATE TABLE `concept_relationship` (
  `concept_id_1` int(11) NOT NULL,
  `concept_id_2` int(11) NOT NULL,
  `relationship_id` int(11) NOT NULL,
  `valid_start_date` date NOT NULL,
  `valid_end_date` date NOT NULL DEFAULT '2099-12-31',
  `invalid_reason` char(1) DEFAULT NULL,
  PRIMARY KEY (`concept_id_1`,`concept_id_2`,`relationship_id`),
  KEY `concept_id_1` (`concept_id_1`),
  KEY `concept_id_2` (`concept_id_2`),
  KEY `relationship_id` (`relationship_id`),
  CONSTRAINT `FK_CONCEPT_REL_child` FOREIGN KEY (`concept_id_2`) REFERENCES `concept` (`concept_id`),
  CONSTRAINT `FK_CONCEPT_REL_Parent` FOREIGN KEY (`concept_id_1`) REFERENCES `concept` (`concept_id`),
  CONSTRAINT `FK_CONCEPT_REL_REL_TYPE` FOREIGN KEY (`relationship_id`) REFERENCES `relationship` (`relationship_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

谢谢你的帮助

Answer 1

首先，不需要索引 concept_id_1。主键完全覆盖了这个索引。

我的建议是创建没有键或外部引用的表，主键除外。插入表时，请确保输入数据按主键的键排序。然后通过显式索引创建添加其他键:

create index concept_relationship_idx1 on concept_relationship(concept_id_1);

等等。

如果这不能有效地工作，那么重新考虑主键。数据实际上是按主键排序的，这对于插入来说可能是计算密集型的。添加一个自动递增的主键。插入数据。然后为现在的主键创建一个唯一索引，并为其他键创建索引。