如何在具有数百万条目的表上有效地更改MySQL表结构？

I have a MySQL database that is up to about 17 GB in size and has 38 million entries. At the moment, I need to both increase the size of one column (varchar 40 to varchar 80) and add more columns.

我有一个MySQL数据库,大小约为17 GB,有3800万条目。目前,我需要增加一列的大小(varchar 40到varchar 80)并添加更多列。

Many of the fields are indexed including the one that I need to change. It is part of a unique pair that is necessary for the applications to work. In attempting to just make the change yesterday, the query ran for almost four hours without finishing, when I decided to cut our outage and just bring the service back up.

许多字段都被编入索引,包括我需要更改的字段。它是应用程序工作所必需的唯一对的一部分。在昨天尝试进行更改时,查询运行了将近四个小时而没有完成,当我决定停止中断并将服务恢复时。

What is the most efficient way to make changes to something of this size?

对这种尺寸的东西进行更改的最有效方法是什么?

Many of these entries are also old and if there is a good way to sort of shard off entries but still have them available that might help with this problem by making the table a much more manageable size.

这些条目中的许多条目也是旧的,如果有一种很好的方法可以对条目进行分类,但仍然可以通过使表格更容易管理来解决这个问题。

3 个解决方案

#1

With MySQL 5.1 and again with 5.5 certain alter statements were enhanced to just modify the structure without rewriting the entire table ( http://dev.mysql.com/doc/refman/5.5/en/alter-table.html - search for in-place). The availability of this though varies by the type of change you are making and the engine in use, the most value comes from InnoDB Plugin. In the case of your specific changes though the entire table would be rewritten.

使用MySQL 5.1并再次使用5.5,某些alter语句被增强,只需修改结构而无需重写整个表(http://dev.mysql.com/doc/refman/5.5/en/alter-table.html - 搜索in -地点)。这种情况的可用性因您所做的更改类型和使用的引擎而异,最大的价值来自InnoDB插件。在您进行特定更改的情况下,将重写整个表。

When we encounter these issues, we typically try to leverage replica databases. As long as you are adding and not removing you can run your DDL against the replica first and then schedule a brief outage for promoting the replica to the master role. If you happen to be on RDS this is even one of their suggested uses for their replica instances http://aws.amazon.com/about-aws/whats-new/2012/10/11/amazon-rds-mysql-rr-promotion/.

当我们遇到这些问题时,我们通常会尝试利用副本数据库。只要您要添加而不是删除,您可以先对副本运行DDL,然后安排短暂停机以将副本提升为主角色。如果您碰巧在RDS上,这甚至是他们的副本实例的建议用途之一http://aws.amazon.com/about-aws/whats-new/2012/10/11/amazon-rds-mysql-rr -promotion /。

Some other alternatives include:

其他一些选择包括:

Selecting out a subset of records into a new table with the desired structure (use INTO OUTFILE to avoid a table lock). Once complete you can schedule a maintenance window and REPLACE INTO or UPDATE any records that have changed in the origin table since the initial data copy. Once the update is complete a RENAME TABLE... of both tables wraps the changes up.

选择具有所需结构的新表中的记录子集(使用INTO OUTFILE以避免表锁定)。完成后,您可以安排维护窗口并更新INTO或更新自初始数据复制以来源表中已更改的任何记录。更新完成后,两个表的RENAME TABLE ...将更改包装起来。

Using a tool like Percona's pt-online-schema-change: http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html. This tool works with triggers so if you already have triggers on the tables you want to change this may not fit your needs.

使用像Percona的pt-online-schema-change这样的工具:http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html。此工具适用于触发器,因此如果您想要更改表上的触发器,则可能无法满足您的需求。

#2

You have some choices.

你有一些选择。

In any case you should take a backup before you do this stuff.

在任何情况下,你应该在做这些事情之前进行备份。

One possibility is to take your service offline and do it in place, as you have tried. If you do that you should disable key checks and constraints.

一种可能性是让您的服务离线并按原样执行,就像您尝试过的那样。如果这样做,您应该禁用密钥检查和约束。

ALTER TABLE bigtable DISABLE KEYS;
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE (whatever);
ALTER TABLE (whatever else);
...
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE bigtable ENABLE KEYS;

This will allow the ALTER TABLE operation to go faster. It will regenerate the indexes all at once when you do ENABLE KEYS.

这将允许ALTER TABLE操作更快。当您执行ENABLE KEYS时,它将一次性重新生成索引。

Another possibility is to create a new table with the new schema you want, then disable the keys on the new table, then do as @Bader suggested and insert the contents of the old table.

另一种可能性是使用所需的新模式创建一个新表,然后禁用新表上的键,然后按@Bader建议并插入旧表的内容。

After your new table is built you will re-enable the keys on it, then rename the old table to some name like "old_bigtable" then rename the new table to "bigtable".

构建新表后,您将重新启用其上的键,然后将旧表重命名为“old_bigtable”之类的名称,然后将新表重命名为“bigtable”。

It's possible that you can keep your service online while you're populating the new table. But that might work poorly.

在填充新表时,您可以将服务保持在线状态。但这可能效果不佳。

A third possibility is to dump your giant table (to a flat file) and then load it to a new table with the new layout. That is pretty much like the second possibility except that you get a table backup for free. You can make this go pretty fast with SELECT DATA INTO OUTFILE and LOAD DATA INFILE. You'll need to have access to your server machine's file system to do this.

第三种可能性是将您的巨型表(转换为平面文件)转储,然后将其加载到具有新布局的新表中。这非常类似于第二种可能性,除了您免费获得表备份。您可以使用SELECT DATA INTO OUTFILE和LOAD DATA INFILE快速完成此操作。您需要访问服务器计算机的文件系统才能执行此操作。

In all cases, disable, then re-enable, the constraints and keys to get things to go fast.

在所有情况下,禁用,然后重新启用约束和键以使事情变得更快。

#3

Create a new table with the new structure you want with a different name for example NewTable.

使用不同的名称创建一个包含所需新结构的新表,例如NewTable。

Then insert data into this new table from the old table using the following query:

然后使用以下查询从旧表中将数据插入到此新表中:

INSERT INTO NewTable (field1, field2, etc...) SELECT field1, field2, ... FROM OldTable

After this is done, you can drop the old table and rename the new table to the original name

完成此操作后,您可以删除旧表并将新表重命名为原始名称

DROP TABLE `OldTable`;
RENAME TABLE `NewTable` TO `OldTable` ;

I have tried this approach on a very large table and it's much much faster than altering the table.

我在一个非常大的表上尝试过这种方法,它比改变表快得多。

#1