在 MySQL 上更新索引值的速度非常慢

Question

我有三个表，一个在数据库db1中，两个在数据库db2中，都在同一个MySQL服务器上：

CREATE TABLE `db1`.`user` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) NOT NULL,
`password_hash` varchar(71) DEFAULT NULL,
`email_address` varchar(100) NOT NULL,
`registration_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`registration_hash` char(16) DEFAULT NULL,
`active` bit(1) NOT NULL DEFAULT b'0',
`public` bit(1) NOT NULL DEFAULT b'0',
`show_name` bit(1) NOT NULL DEFAULT b'0',
PRIMARY KEY (`id`),
UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `db2`.`ref` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `db2`.`combination` (
`ref_id` bigint(20) UNSIGNED NOT NULL,
`user_id` bigint(20) UNSIGNED NOT NULL,
`arbitrary_number` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`figurine_id`,`user_id`),
KEY `combination_user` (`user_id`),
KEY `combination_number` (`user_id`,`arbitrary_number`),
CONSTRAINT `combination_ref` FOREIGN KEY (`ref_id`) REFERENCES `ref` (`id`) ON UPDATE CASCADE,
CONSTRAINT `combination_user` FOREIGN KEY (`user_id`) REFERENCES `db1`.`user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

该表db1.user大约有 600 条记录，该表db2。ref大约有 800 条记录，表格db2.combination有大约 300K 条记录。

现在使用 Perl 和 DBD：：mysql，我执行以下查询：

UPDATE `db1`.`user` SET `id` = (`id` + 1000)
ORDER BY `id` DESC

但是，此查询始终停止提及与MySQL服务器的连接丢失。同样通过 PhpMyAdmin 执行相同的查询也会导致超时。不知何故，查询只需要很长时间才能执行。我想这是因为所有外键值都需要更新。

将变量设置为OFFFOREIGN_KEY_CHECKS不会更新db.combination表中的user_id列，这些列确实需要更新。

我还尝试操纵不同的超时(正如互联网上所建议的那样)，如下所示：

SET SESSION net_read_timeout=3000;
SET SESSION net_write_timeout=3000;
SET SESSION wait_timeout=6000;

我已经通过再次检索值来验证新值是否已实际设置。但是，即使有这些长时间的超时，查询仍然无法执行，大约 30 秒后，与 MySQL 服务器的连接再次丢失(在执行UPDATE查询时)

非常欢迎有关如何加快此查询的任何建议。

顺便说一句：PK 列具有非常大的整数类型。我还将使此类型变小(更改为INT)。这种类型的变化是否也能显着提高速度？

更新我还对查询执行了EXPLAIN，它在Extra列中提到查询正在执行文件排序。我本来以为由于表上的索引(添加它们，因为它们首先不存在)，不会发生文件排序。

Answer 1

300KCASCADEs可能是任务中真正缓慢的部分。 所以，让我们避免它。 (但是，可能会检查验证生成的链接;这应该不会太慢。

1. 禁用FOREIGN KEY处理

2. 创建新表而不FOREIGN KEYs。 new_user，new_combination。 (我不知道是否需要new_ref。

3. 执行此操作以填充表：

   INSERT INTO new_xx (user_id, ...)
   SELECT user_id + 1000, ...;
   

4. ALTER TABLE new_xx ADD FOREIGN KEY ...;(对于每个 xx)

5. '将表 xx 重命名为 old_xx，new_xx重命名为 xx;

6. '放下表old_xx;

7. 启用FOREIGN KEY处理