MySQL非常慢的更改表查询 [英] MySQL very slow for alter table query

问题描述

为什么简单地更新此表以添加列会花费一个多小时?该表有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/

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

这篇关于MySQL非常慢的更改表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！