MySQL的alter table查询非常缓慢

Question

为什么更新这个表格只是添加一个列需要花费超过一个小时的时间？这个表有1500万行数据。它有2个索引和单键主键。ALTER TABLE查询已经在“复制到临时表”状态下进行了1小时15分钟。

ALTER TABLE `frugg`.`item_catalog_map` 
ADD COLUMN `conversion_url` TEXT NULL DEFAULT NULL

表格：

mysql> describe item_catalog_map;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| catalog_unique_item_id | varchar(255)  | NO   | PRI | NULL    |       |
| catalog_id             | int(11)       | YES  | MUL | NULL    |       |
| item_id                | int(11)       | YES  | MUL | NULL    |       |
| price                  | decimal(10,2) | YES  |     | 0.00    |       |
+------------------------+---------------+------+-----+---------+-------+

mysql> show index from item_catalog_map;
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name             | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| item_catalog_map |          0 | PRIMARY              |            1 | catalog_unique_item_id | A         |    15485115 |     NULL | NULL   |      | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184FCC3C66FC |            1 | catalog_id             | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184F126F525E |            1 | item_id                | A         |    15485115 |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+

- David Parks

---

也许这个类似问题的帖子可以帮助你...https://dev59.com/anM_5IYBdhLWcg3wfTI0 - Mudassir Hasan

请阅读此线程...https://dev59.com/PG025IYBdhLWcg3w_rJA - Mudassir Hasan

如果有某种进度条、状态消息或至少有一个旋转光标的话就太棒了，哈哈。特别是因为“修改列”已经使我的网站下线了。 - PJ Brunet

这里有一个可行的答案 - https://dev59.com/t2sz5IYBdhLWcg3w476m - Oded Ben Dov

工作答案在这里 - https://dev59.com/t2sz5IYBdhLWcg3w476m - Oded Ben Dov

Answer 1

当MySQL表非常大时，ALTER TABLE操作的性能可能会成为一个问题。MySQL通常通过创建具有所需新结构的空表，在新表中插入旧表中的所有数据，并删除旧表来执行大多数变更。这可能需要很长时间，特别是如果您的内存不足、表很大且具有许多索引。许多人都经历过需要花费几个小时甚至几天才能完成的ALTER TABLE操作。

无论如何，如果您需要进行ALTER TABLE操作，也许以下资源可以帮助您：

• https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html
• https://github.com/soundcloud/lhm
• https://githubengineering.com/gh-ost-github-s-online-migration-tool-for-mysql/

- Ernesto Campohermoso

---

如果需要这么长时间，那么您的存储系统可能存在问题，这不是MySQL的问题。 - AndreKR

我正在将一个有30行的表格中的varchar类型更改为text类型，已经等了20分钟还在进行中。这是什么问题？ - kommradHomer

VARCHAR 存储在表中，而文本则单独存储，并且引用存储在表中。因此它们非常不同。我认为更改是因为您有一个非常大的 VARCHAR 并且希望允许更大的值。在这种情况下，需要移动所有值。 - techdude

@AndreKR - 不，我认为这很明显是一个MySQL问题。向一个包含900MB数据的表中添加一列（大约25K行包含较小的BLOB）不应该需要超过3个小时的时间，但我现在已经超过了这个时间。我的存储系统没有任何问题；数据库存储在一个相当好的SSD上，并且我有足够的可用RAM进行缓存。我认为InnoDB在这种操作期间维护索引方面做得不好；我猜它没有优化的批量插入机制，因此正在逐行重建新索引。 - Jules

@Jules 你可能是对的。我认为当我写评论时，我假设它是一个MyISAM表，因为我认为“InnoDB不会发生复制到tmp表”的情况，但实际上在问题中没有给出表类型。使用InnoDB计划几天进行ALTER TABLE操作是很正常的，这就是为什么像https://github.com/facebookincubator/OnlineSchemaChange这样的软件存在的原因。 - AndreKR

表的大小与此无关。我曾经见过有60行的表需要半小时才能修改。MySQL太笨拙了，没有任何公平性或锁饥饿缓解措施，所以它会让其他事务整天锁定表格。如果有任何东西使用FOR UPDATE，你就完蛋了。 - doug65536

Answer 2

如果您不关心停机时间，我的建议是使用三个独立的ALTER TABLE语句。第一个语句删除所有现有的二级索引。第二个语句应用所有与列相关的更改。最后一条语句将删除的二级索引添加回来并应用其他索引更改。

另外两个提示：

1. 在应用索引更改之前，请执行以下两个语句，并在完成索引更改后将值更改回1。

   SET unique_checks=0;
   SET foreign_key_checks=0;
   

2. 创建多个二级索引时，请将它们放在一个 ALTER TABLE 语句中，而不是多个单独的 ALTER TABLE 语句中。




下图显示了性能差异。方法 1 是您的方法，方法 2 是我的方法。对于一个50m的表，方法2比方法1节省约3.47%的时间。该解决方案仅适用于MySQL（>=5.5）InnoDB引擎。

- Albert Wang

---

需要注意的是，我相信Percona MySQL的变体已经通过设置expand_fast_index_creation=ON来内置此功能。 - Brian Leishman

Answer 3

为了减少要更改的大表锁定时间，我采取以下措施：

• 基于现有表创建一个新的空表，并修改此新的空表。
• 对大表执行mysqldump，使其每个记录都具有完整的插入语句（使用-c和--skip-extended-insert开关）。
• 将此mysqldump导入到另一个（空）数据库中，并将其命名为空的large_table。
• 从其他数据库中获取此新重命名表的mysqldump，并将其导入原始数据库中。

• 在原始数据库中重命名large_table和large_table_new。

mysql> create table DATABASE_NAME.LARGE_TABLE_NEW like DATABASE_NAME.LARGE_TABLE;
mysql> alter table DATABASE_NAME.LARGE_TABLE_NEW add column NEW_COLUMN_NAME COL_DATA_TYPE(SIZE) default null;

$ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p DATABASE_NAME LARGE_TABLE > LARGE_TABLE.sql

mysql> create table test.LARGE_TABLE like DATABASE_NAME.LARGE_TABLE;

$ mysql -u root -p -D test < LARGE_TABLE.sql

mysql> rename table test.LARGE_TABLE to test.LARGE_TABLE_NEW;

$ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p test LARGE_TABLE_NEW > LARGE_TABLE_NEW.sql

$ mysql -u root -p -D DATABASE_NAME < LARGE_TABLE_NEW.sql

mysql> rename table DATABASE_NAME.LARGE_TABLE to DATABASE_NAME.LARGE_TABLE_OLD, DATABASE_NAME.LARGE_TABLE_NEW to DATABASE_NAME.LARGE_TABLE;

- gautamc

Answer 4

Percona工具对于涉及大表的技术问题是一个救星。

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

它们基本上:

1. 创建重复表格
2. 创建触发器同步表格
3. 批量复制数据
4. 验证
5. 交换表格

虽然需要很长时间，但这意味着您可以在无需停机的情况下更改列。

- jdwyah

---

如果您的数据表中有外键约束，那么在重命名两个表时无法保证原子性操作。因此，任何工具都无法解决这个问题。 - kommradHomer

显然，你不能改变外键一侧的数据类型而不改变另一侧。这是必须的。你必须先删除外键，修改两个列，然后再重新引入外键。 - Kafoso

Answer 5

您的表格有1500万行数据，这是一个很大的数字。ALTER TABLE 操作将会复制所有数据并重新创建索引。作为第一步尝试，可以在文件系统中复制数据文件（如果是MyISAM则为item_catalog_map.MYD），以查看需要多长时间。这个时间至少就是 ALTER TABLE 所需的时间。

- AndreKR

Answer 6

我曾遇到相同的问题，只需在Ubuntu中使用以下命令重新启动MySQL服务即可：sudo service mysql restart，此后ALTER TABLE命令将立即执行。

- Charles Spicer

---

这应该是被接受的答案！太神奇了，它有效。 - George Chalhoub

Answer 7

我曾经遇到过类似的问题，对于一个包含1100万行数据的MariaDB Docker容器中的表格，ALTER TABLE操作非常缓慢。但是在一个同样有1100万行数据的RDS实例上，ALTER TABLE操作却非常快。

我的电脑是Mac OS 12.4系统，Docker容器中的数据存储在文件系统卷中。瓶颈在于创建临时表，正如其他答案所指出的那样。

解决方法是在Docker实验设置中启用VirtioFS。然后ALTER TABLE操作只需要大约10分钟，而不是原本可能需要8个小时。

- Josh S