时间戳

首页 > 解决方案 > AWS RDS下MySQL ADD COLUMN慢

mysql - AWS RDS下MySQL ADD COLUMN慢

问题描述

我有一个具有以下设置的 RDS MySql：

然后，我想在大小约为 20 GB 的表中添加几列（根据INFORMATION_SCHEMA.files）。这是我的声明：

ALTER TABLE MY_TABLE
ADD COLUMN NEW_COLUMN_1 DECIMAL(39, 30) NULL,
ADD COLUMN NEW_COLUMN_2  DECIMAL(39, 30) NULL,
ADD COLUMN NEW_COLUMN_3 INT(10) UNSIGNED NULL,
ADD CONSTRAINT SOME_CONSTRAINT FOREIGN KEY (NEW_COLUMN_3) REFERENCES SOME_OTHER_TABLE(SOME_OTHER_PK),
ADD COLUMN NEW_COLUMN_4 DATE NULL;

此查询需要 172 分钟才能执行。大部分时间都花在将数据处理到临时表上。

在该操作期间，没有执行其他查询（读取或写入）。我只有自己的数据库。SHOW FULL PROCESSLIST说这State等于copy to tmp table我的查询。

我不明白的是，AWS RDS 控制台告诉我写入吞吐量在 30 MB/s 和 35 MB/s 之间持续了 172 分钟。

在此处输入图像描述

假设写入吞吐量为 30 MB/s，我应该能够写入 30 * 60 * 172 = 309600 MB = 302 GB。这比操作期间创建的临时表的大小 (20 GB) 大得多。

所以两个问题：

  1. 我的临时表旁边写的是什么 mysql/rds？有没有办法禁用它，以便我可以获得创建临时表的全部带宽？
  2. 有什么方法可以加速该操作吗？花 3 个小时来写入 20 GB 的数据似乎很长。

标签： mysqlamazon-web-servicesamazon-rds

解决方案

我使用的是 MySQL 5.7。根据这篇MySQL 博客文章，8.0 版改善了这种情况：“InnoDB 现在支持 Instant ADD COLUMN”。

因此，我更改了查询以使用新功能。

-- Completes in 0.375 seconds!
ALTER TABLE MY_TABLE
ADD COLUMN NEW_COLUMN_1 DECIMAL(39, 30) NULL,
ADD COLUMN NEW_COLUMN_2  DECIMAL(39, 30) NULL,
ADD COLUMN NEW_COLUMN_3 INT(10) UNSIGNED NULL,
-- 'ALGORITHM=INSTANT' is not compatible with foreign keys. 
-- The foreign key will need to be added in another statement
-- ADD CONSTRAINT SOME_CONSTRAINT FOREIGN KEY (NEW_COLUMN_3) REFERENCES SOME_OTHER_TABLE(SOME_OTHER_PK),
ADD COLUMN NEW_COLUMN_4 DATE NULL, 
-- the new option
ALGORITHM=INSTANT;

-- This completed in about 6 minutes. 
-- Adding the foreign creates an index under the hood. 
-- This index was 1.5 GB big.
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE MY_TABLE
  ADD FOREIGN KEY (NEW_COLUMN_3) REFERENCES SOME_OTHER_TABLE(SOME_OTHER_PK);
SET FOREIGN_KEY_CHECKS=1;

所以我的结论：

  • 如果可以的话，升级到 MySQL 8
  • 确保您始终使用（如果可能）该ALGORITHM=INSTANT选项。

推荐阅读