MySQL的alter table查询速度很慢

2021-11-27 00:00:00 sql mysql alter-table

为什么简单地更新这个表添加一列需要一个多小时?该表有 1500 万行.它有 2 个索引和一个单键主键.ALTER TABLE 查询现在已处于复制到 tmp 表"状态 1 小时 15 分钟.

Why is it taking more than an hour to simply update this table to add a column? This table has 15M rows. It has 2 indexes and a single key primary key. The ALTER TABLE query has been in "copy to tmp table" state for 1 hour 15 minutes now.

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

推荐答案

MySQL 的 ALTER TABLE 性能可能成为非常大的表的问题.MySQL 执行大多数更改是通过创建一个具有所需新结构的空表，将旧表中的所有数据插入到新表中，然后删除旧表.这可能需要很长时间，尤其是当您的内存不足并且表很大并且有很多索引时.许多人都曾经历过需要数小时或数天才能完成的 ALTER TABLE 操作.

MySQL’s ALTER TABLE performance can become a problem with very large tables. MySQL performs most alterations by making an empty table with the desired new structure, inserting all the data from the old table into the new one, and deleting the old table. This can take a very long time, especially if you’re short on memory and the table is large and has lots of indexes. Many people have experience with ALTER TABLE operations that have taken hours or days to complete.

无论如何，如果您需要继续使用 alter table，也许以下资源可以帮助您:

Anyway if you need to proceed with alter table, maybe the following resources could help you:

  • 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/

相关文章