对于alter table查询，MySQL非常慢

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行。它有两个索引和一个键主键。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      |         |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+

6 个解决方案

#1

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通过使用所需的新结构创建一个空表，将旧表中的所有数据插入到新表中，并删除旧表，来执行大多数更改。这可能需要很长时间，特别是如果内存不足，而且表很大，并且有很多索引。许多人都有更改表操作的经验，这些操作需要花费数小时或数天的时间才能完成。

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

#2

If you don't care about downtime, my suggestion is using three separated ALTER TABLE statements. 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.

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

   SET unique_checks=0;
   SET foreign_key_checks=0;
   

2. When create multiple secondary indexes, put them in one ALTER TABLE statement rather than multiple separated ALTER TABLE statements.

   当创建多个辅助索引时，将它们放在一个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.

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

#3

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 least take.

你的表有1500万行，这是。ALTER TABLE涉及复制表中的所有数据并重新创建索引。作为第一个度量，请尝试复制数据文件(item_catalog_map)。如果是MyISAM，则是MYD)在文件系统中，看看需要多长时间。这是ALTER TABLE至少要花费的时间。

#4

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. 创建复制表
3. create trigger to sync tables
4. 创建同步表的触发器。
5. bulk copy data
6. 批量复制数据
7. verify
8. 验证
9. swap tables
10. 交换表

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

这需要很长时间，但是谁在乎呢，因为这意味着您可以在不停机的情况下更改列。

#5

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)
• 对大表执行mysqldump操作，使它在大表中每个记录都有一个完整的insert语句(开关-c和-skip- extension -insert)
• Import this mysqldump into a different (empty) database with the empty renamed large_table.
• 将这个mysqldump导入另一个(空)数据库，并使用空重命名为large_table。
• Take a mysqldump of this new rename table from the other database and import it into the original database
• 从其他数据库中获取这个新的重命名表的mysqldump，并将其导入到原始数据库中。

• Rename large_table and large_table_new in the original database.

  在原始数据库中重命名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;
  

#6

If you alter several columns at once it might take more time than you expect. Instead try to alter once column at a time. I do not find the technical side of that but I experienced the problem and got through from it.

如果您同时更改几个列，可能会花费比您预期的更多的时间。相反，尝试一次修改一个列。我没有发现技术方面的问题，但我经历了这个问题，并从中度过了难关。