MySQL对于alter table查询非常慢

Question

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      |         |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+

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/