MySQL ALTER TABLE 不锁定表？

ALTER TABLE without locking the table?

提问by Daniel

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked (allowing concurrent reads, but prohibiting concurrent writes) for the duration of the statement. If it's a big table, INSERT or UPDATE statements could be blocked for a looooong time. Is there a way to do a "hot alter", like adding a column in such a way that the table is still updatable throughout the process?

在 MySQL 中执行 ALTER TABLE 语句时，整个表在语句期间被读锁定（允许并发读取，但禁止并发写入）。如果它是一个大表，INSERT 或 UPDATE 语句可能会被阻塞很长时间。有没有办法进行“热更改”，比如以这样的方式添加一列，使表格在整个过程中仍然可以更新？

Mostly I'm interested in a solution for MySQL but I'd be interested in other RDBMS if MySQL can't do it.

大多数情况下，我对 MySQL 的解决方案感兴趣，但如果 MySQL 无法做到，我会对其他 RDBMS 感兴趣。

To clarify, my purpose is simply to avoid downtime when a new feature that requires an extra table column is pushed to production. Any database schema willchange over time, that's just a fact of life. I don't see why we should accept that these changes must inevitably result in downtime; that's just weak.

澄清一下，我的目的只是为了避免在将需要额外表列的新功能推送到生产时出现停机时间。任何数据库模式都会随着时间的推移而改变，这就是生活中的事实。我不明白为什么我们应该接受这些变化必然会导致停机；那只是弱。

采纳答案by MatBailie

The only other option is to do manually what many RDBMS systems do anyway...
- Create a new table

唯一的其他选择是手动执行许多 RDBMS 系统所做的操作...
- 创建一个新表

You can then copy the contents of the old table over a chunk at a time. Whilst always being cautious of any INSERT/UPDATE/DELETE on the source table. (Could be managed by a trigger. Although this would cause a slow down, it's not a lock...)

然后，您可以一次将旧表的内容复制到一个块上。同时始终对源表上的任何 INSERT/UPDATE/DELETE 保持谨慎。（可以通过触发器管理。虽然这会导致减速，但它不是锁定......）

Once finished, change the name of the source table, then change the name of the new table. Preferably in a transaction.

完成后，更改源表的名称，然后更改新表的名称。最好在交易中。

Once finished, recompile any stored procedures, etc that use that table. The execution plans will likely no longer be valid.

完成后，重新编译使用该表的任何存储过程等。执行计划可能不再有效。

EDIT:

编辑：

Some comments have been made about this limitation being a bit poor. So I thought I'd put a new perspective on it to show why it's how it is...

一些评论说这个限制有点差。所以我想我会对它提出一个新的观点来说明为什么它是这样的......

• Adding a new field is like changing one field on every row.
• Field Locks would be much harder than Row locks, never mind table locks.
  
  
• You're actually changing the physical structure on the disk, every record moves.
• This really is like an UPDATE on the Whole table, but with more impact...

• 添加新字段就像更改每一行的一个字段。
• 字段锁比行锁要困难得多，更不用说表锁了。
  
  
• 您实际上是在更改磁盘上的物理结构，每条记录都在移动。
• 这真的就像对整个表的更新，但影响更大......

回答by SeanDowney

Percona makes a tool called pt-online-schema-changethat allows this to be done.

Percona 制作了一个名为pt-online-schema-change 的工具来实现这一点。

It essentially makes a copy of the table and modifies the new table. To keep the new table in sync with the original it uses triggers to update. This allows the original table to be accessed while the new table is prepared in the background.

它本质上是制作表的副本并修改新表。为了使新表与原始表保持同步，它使用触发器进行更新。这允许在后台准备新表时访问原始表。

This is similar to Dems suggested method above, but this does so in an automated fashion.

这类似于上面 Dems 建议的方法，但这是以自动化方式进行的。

Some of their tools have a learning curve, namely connecting to the database, but once you have that down, they are great tools to have.

他们的一些工具有一个学习曲线，即连接到数据库，但是一旦你掌握了它，它们就是很好的工具。

Ex:

前任：

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends

回答by Ivanov

This question from 2009. Now MySQL offers a solution:

这个问题来自 2009 年。现在 MySQL 提供了一个解决方案：

Online DDL (Data Definition Language)

在线 DDL（数据定义语言）

A feature that improves the performance, concurrency, and availability of InnoDB tables during DDL (primarily ALTER TABLE) operations. See Section 14.11, “InnoDB and Online DDL” for details.

The details vary according to the type of operation. In some cases, the table can be modified concurrently while the ALTER TABLE is in progress. The operation might be able to be performed without doing a table copy, or using a specially optimized type of table copy. Space usage is controlled by the innodb_online_alter_log_max_size configuration option.

在 DDL（主要是 ALTER TABLE）操作期间提高 InnoDB 表的性能、并发性和可用性的功能。有关详细信息，请参阅第 14.11 节，“InnoDB 和在线 DDL”。

详细信息因操作类型而异。在某些情况下，可以在 ALTER TABLE 进行时同时修改表。可能无需执行表复制或使用特别优化的表复制类型即可执行该操作。空间使用由 innodb_online_alter_log_max_size 配置选项控制。

It lets you adjust the balance between performance and concurrency during the DDL operation, by choosing whether to block access to the table entirely (LOCK=EXCLUSIVE clause), allow queries but not DML (LOCK=SHARED clause), or allow full query and DML access to the table (LOCK=NONE clause). When you omit the LOCK clause or specify LOCK=DEFAULT, MySQL allows as much concurrency as possible depending on the type of operation.

它允许您在 DDL 操作期间调整性能和并发之间的平衡，通过选择是完全阻止对表的访问（LOCK=EXCLUSIVE 子句）、允许查询但不允许 DML（LOCK=SHARED 子句）或允许完整查询和 DML访问表（LOCK=NONE 子句）。当您省略 LOCK 子句或指定 LOCK=DEFAULT 时，MySQL 根据操作类型允许尽可能多的并发。

Performing changes in-place where possible, rather than creating a new copy of the table, avoids temporary increases in disk space usage and I/O overhead associated with copying the table and reconstructing secondary indexes.

在可能的情况下执行就地更改，而不是创建表的新副本，可以避免临时增加磁盘空间使用量和与复制表和重建二级索引相关的 I/O 开销。

see MySQL 5.6 Reference Manual -> InnoDB and Online DDLfor more info.

有关更多信息，请参阅MySQL 5.6 参考手册 -> InnoDB 和在线 DDL。

It seems that online DDL also available in MariaDB

MariaDB 中似乎也提供了在线 DDL

Alternatively you can use ALTER ONLINE TABLE to ensure that your ALTER TABLE does not block concurrent operations (takes no locks). It is equivalent to LOCK=NONE.

或者，您可以使用 ALTER ONLINE TABLE 来确保您的 ALTER TABLE 不会阻塞并发操作（不使用锁）。它相当于 LOCK=NONE。

MariaDB KB about ALTER TABLE

MariaDB KB 关于 ALTER TABLE

回答by Steven Soroka

See Facebook's online schema change tool.

请参阅 Facebook 的在线架构更改工具。

http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

Not for the faint of heart; but it will do the job.

不适合胆小的人；但它会完成这项工作。

回答by mikelikespie

I recommend Postgres if that's an option. With postgres there is essentially no downtime with the following procedures:

如果可以的话，我推荐 Postgres。使用 postgres 基本上没有停机时间，执行以下程序：

• ALTER TABLE ADD COLUMN (if the column can be NULL)
• ALTER TABLE DROP COLUMN
• CREATE INDEX (must use CREATE INDEX CONCURRENTLY)
• DROP INDEX

• ALTER TABLE ADD COLUMN（如果该列可以为 NULL）
• 更改表删除列
• CREATE INDEX（必须同时使用 CREATE INDEX）
• 下降指数

Other great feature is that most DDL statements are transactional, so you could do an entire migration within a SQL transaction, and if something goes wrong, the entire thing gets rolled back.

另一个很棒的特性是大多数 DDL 语句都是事务性的，因此您可以在 SQL 事务中进行整个迁移，如果出现问题，整个事情都会回滚。

I wrote thisa little bit ago, perhaps it can shed some more insight on the other merits.

我之前写过这个，也许它可以对其他优点有更多的了解。

回答by WW.

Since you asked about other databases, here's some information about Oracle.

由于您询问了其他数据库，因此这里有一些有关 Oracle 的信息。

Adding a NULL column to an Oracle table is a very quick operation as it only updates the data dictionary. This holds an exclusive lock on the table for a very short period of time. It will however, invalidate any depedant stored procedures, views, triggers, etc. These will get recompiled automatically.

向 Oracle 表添加 NULL 列是一项非常快速的操作，因为它只会更新数据字典。这会在很短的时间内在表上保持独占锁。但是，它会使任何依赖的存储过程、视图、触发器等无效。这些将自动重新编译。

From there if necessary you can create index using the ONLINE clause. Again, only very short data dictionary locks. It'll read the whole table looking for things to index, but does not block anyone while doing this.

如有必要，您可以从那里使用 ONLINE 子句创建索引。同样，只有非常短的数据字典锁。它将读取整个表以查找要索引的内容，但在执行此操作时不会阻止任何人。

If you need to add a foreign key, you can do this and get Oracle to trust you that the data is correct. Otherwise it needs to read the whole table and validate all the values which can be slow (create your index first).

如果您需要添加外键，您可以这样做并让 Oracle 相信您数据是正确的。否则它需要读取整个表并验证所有可能很慢的值（首先创建索引）。

If you need to put a default or calculated value into every row of the new column, you'll need to run a massive update or perhaps a little utility program that populates the new data. This can be slow, especially if the rows get alot bigger and no longer fit in their blocks. Locking can be managed during this process. Since the old versino of your application, which is still running, does not know about this column you might need a sneaky trigger or to specify a default.

如果您需要将默认值或计算值放入新列的每一行，您将需要运行大量更新或可能是一个填充新数据的小实用程序。这可能很慢，尤其是当行变得更大并且不再适合它们的块时。在此过程中可以管理锁定。由于仍在运行的应用程序的旧版本不知道此列，因此您可能需要一个偷偷摸摸的触发器或指定默认值。

From there, you can do a switcharoo on your application servers to the new version of the code and it'll keep running. Drop your sneaky trigger.

从那里，您可以在您的应用程序服务器上切换到新版本的代码，它会继续运行。放下你偷偷摸摸的扳机。

Alternatively, you can use DBMS_REDEFINITION which is a black box designed to do this sort of thing.

或者，您可以使用 DBMS_REDEFINITION，这是一个设计用于执行此类操作的黑匣子。

All this is so much bother to test, etc that we just have an early Sunday morning outage whenever we release a major version.

所有这些都太麻烦了，等等，以至于每当我们发布一个主要版本时，我们都会在周日早上出现中断。

回答by jynus

If you cannot afford downtime for your database when doing application updates you should consider maintaining a two-node cluster for high availability. With a simple replication setup, you could do almost fully online structural changes like the one you suggest:

如果您在执行应用程序更新时无法承受数据库的停机时间，您应该考虑维护一个双节点集群以实现高可用性。通过简单的复制设置，您几乎可以进行完全在线的结​​构更改，就像您建议的那样：

• wait for all changes to be replicated on a passive slave
• change the passive slave to be the active master
• do the structural changes to the old master
• replicate changes back from the new master to the old master
• do the master swapping again and the new app deployment simultaneously

• 等待所有更改在被动从属服务器上复制
• 将被动从站更改为主动主站
• 对旧主人做结构性改变
• 将更改从新主服务器复制回旧主服务器
• 再次进行主交换并同时进行新的应用程序部署

It is not always easy but it works, usually with 0 downtime! The second node does not have to be only passive one, it can be used for testing, doing statistics or as a fallback node. If you do not have infrastructure replication can be set up within a single machine (with two instances of MySQL).

这并不总是容易，但它有效，通常停机时间为 0！第二个节点不一定是被动节点，它可以用于测试、统计或作为后备节点。如果没有基础设施，可以在一台机器内设置复制（有两个 MySQL 实例）。

回答by Balasundaram

Temporary solution...

临时解决方案...

Other solution could be, add a another table with primary key of the original table, along with your new column.

其他解决方案可能是，添加另一个带有原始表主键的表以及新列。

Populate your primary key onto the new table and populate values for new column in your new table, and modify your query to join this table for select operations and you also need to insert, update separately for this column value.

将您的主键填充到新表上并为新表中的新列填充值，并修改您的查询以加入此表以进行选择操作，您还需要为此列值单独插入、更新。

When you able to get downtime, you can alter the original table, modify your DML queries and drop your new table created earlier

当您能够停机时，您可以更改原始表、修改 DML 查询并删除之前创建的新表

Else, you may go for clustering method, replication, pt-online-schema tool from percona

否则，您可以使用 percona 的集群方法、复制、pt-online-schema 工具

回答by dkretz

Nope. If you are using MyISAM tables, to my best understanding they only do table locks - there are no record locks, they just try to keep everything hyperfast through simplicity. (Other MySQL tables operate differently.) In any case, you can copy the table to another table, alter it, and then switch them, updating for differences.

不。如果您使用的是 MyISAM 表，据我所知，它们只做表锁——没有记录锁，它们只是试图通过简单性来保持一切超快。（其他 MySQL 表的操作不同。）在任何情况下，您都可以将表复制到另一个表，更改它，然后切换它们，更新差异。

This is such a massive alteration that I doubt any DBMS would support it. It's considered a benefit to be able to do it with data in the table in the first place.

这是一个如此巨大的改变，我怀疑任何 DBMS 会支持它。能够首先对表中的数据进行处理被认为是一种好处。

回答by MarkR

Using the Innodb plugin, ALTER TABLE statements which only add or drop secondary indexes can be done "quickly", i.e. without rebuilding the table.

使用 Innodb 插件，只添加或删除二级索引的 ALTER TABLE 语句可以“快速”完成，即无需重建表。

Generally speaking however, in MySQL, any ALTER TABLE involves rebuilding the entire table which can take a very long time (i.e. if the table has a useful amount of data in it).

然而，一般来说，在 MySQL 中，任何 ALTER TABLE 都涉及重建整个表，这可能需要很长时间（即，如果表中有大量有用的数据）。

You really need to design your application so that ALTER TABLE statements do not need to be done regularly; you certainly don't want any ALTER TABLE done during normal running of the application unless you're prepared to wait or you're altering tiny tables.

您确实需要设计您的应用程序，以便不需要定期执行 ALTER TABLE 语句；您当然不希望在应用程序的正常运行期间完成任何 ALTER TABLE，除非您准备等待或正在更改小表。