MySQL MySQL的alter table查询速度很慢

MySQL very slow for alter table query

提问by David Parks

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.

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

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

Table:

桌子：

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

回答by Ernesto Campohermoso

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.

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

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

无论如何，如果您需要继续使用 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/

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

回答by Albert Wang

If you don't care about downtime, my suggestion is using three separated ALTER TABLEstatements. The first statement removes all existing secondary indexes. The second statement applies all column related changes. The last statement adds dropped secondary indexes back and applies other index changes.

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

Another two tips:

另外两个提示：

1. Before apply index changes, execute the two following statements and change the values back to 1 after finishing the index change.

   SET unique_checks=0;
   SET foreign_key_checks=0;
   

2. When create multiple secondary indexes, put them in one ALTER TABLEstatement rather than multiple separated ALTER TABLEstatements.

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

   SET unique_checks=0;
   SET foreign_key_checks=0;
   

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

The following picture shows the performance difference. Approach 1 is your approach and approach 2 is my way. Approach 2 takes about 3.47% time comparing with approach 1 for a 50m table.The solution only works for MySQL (>=5.5) InnoDB engine.

下图显示了性能差异。方法一是你的方法，方法二是我的方法。对于 50m 的工作台，方法 2 与方法 1 相比需要大约 3.47% 的时间。该解决方案仅适用于 MySQL (>=5.5) InnoDB 引擎。

回答by jdwyah

The Percona tools are a lifesaver for this stuff w/ big tables.

Percona 工具是这种带大桌子的东西的救星。

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

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

they basically:

他们基本上：

1. create duplicate table
2. create trigger to sync tables
3. bulk copy data
4. verify
5. swap tables

1. 创建重复表
2. 创建触发器以同步表
3. 批量复制数据
4. 核实
5. 交换表

Takes forever, but who cares because this means you can change columns without downtime.

需要永远，但谁在乎，因为这意味着您可以在不停机的情况下更改列。

回答by AndreKR

Your table has 15 million rows, which is something. The ALTER TABLE involves copying over all the data from the table and recreating the indexes. As a first measurement try copying the data file (item_catalog_map.MYD if it's MyISAM) in your filesystem and see how long that takes. This is the time the ALTER TABLE will at leasttake.

您的表有 1500 万行，这很重要。ALTER TABLE 涉及从表中复制所有数据并重新创建索引。作为第一次测量，尝试在您的文件系统中复制数据文件（如果是 MyISAM，则为 item_catalog_map.MYD）并查看这需要多长时间。这是 ALTER TABLE至少需要的时间。

回答by gautamc

For minimize locking up of the large table that I want to alter, I do the following:

为了最大限度地减少我想要更改的大表的锁定，我执行以下操作：

• Create a new empty table based on the existing table and alter this new empty table.
• Do a mysqldump of the large table such that it has one complete insert statement per record in the large table (switches -c and --skip-extended-insert)
• Import this mysqldump into a different (empty) database with the empty renamed large_table.
• Take a mysqldump of this new rename table from the other database and import it into the original database

• Rename large_table and large_table_new in the original database.

  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;
  

• 基于现有表创建一个新的空表并更改这个新的空表。
• 对大表进行 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;