ALTER TABLE without locking the table?

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

我主要对 MySQL 的解决方案感兴趣，但如果 MySQL 做不到的话，我也会对其他关系数据库管理系统感兴趣。

澄清一下，我的目的只是为了避免当需要额外表列的新特性被推到生产环境中时停机。任何数据库模式 威尔都会随着时间的推移而改变，这是不争的事实。我不明白为什么我们应该接受这些改变必然会导致停机; 这是软弱的。

110628 次浏览
小开

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

这是一个如此巨大的改变，我怀疑任何 DBMS 都不会支持它。首先，能够使用表中的数据执行此操作被认为是一个好处。

小开

没有。

You ARE altering the underlying structure of the table, after all, and that's a bit of information that's quite important to the underlying system. You're also (likely) moving much of the data around on disk.

If you plan on doing this a lot, you're better off simply padding the table with "dummy" columns that are available for future use.

小开
最佳答案

另一种选择是手动完成许多 RDBMS 系统所做的事情..。
- 创建一个新表

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

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

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

编辑:

一些评论认为这个限制有点差。所以我觉得我应该换个角度来看看为什么会这样。

  • 添加一个新字段就像更改每行中的一个字段。
  • 字段锁要比行锁难得多，更不用说表锁了。

  • 你实际上改变了磁盘上的物理结构，每个记录都在移动。
  • 这实际上就像是对整个表进行更新，但是具有更大的影响..。
小开

如果可以预测虚拟列的类型(并使其可为空) ，那么虚拟列是一个好主意。检查存储引擎如何处理空值。

如果你在电话里，在机场提到桌子的名字，MyISAM 就会锁上所有的东西。它只是..。

也就是说，锁实际上并不是什么大问题; 只要你不试图为每一行添加新列的默认值，而是让它保持为 null，并且你的存储引擎足够聪明，不去写它，你应该可以接受一个锁，只是保持足够长的时间来更新元数据。如果您尝试写入一个新值，那么，您就完蛋了。

小开

既然您询问了其他数据库，这里有一些关于 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.

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

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

如果需要在新列的每一行中放入默认值或计算值，则需要运行大规模更新或填充新数据的小实用程序。这可能会很慢，特别是如果行变得更大，不再适合他们的块。在此过程中可以管理锁定。由于仍在运行的应用程序的老版本不知道这个列，因此可能需要一个隐蔽的触发器或指定一个默认值。

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

或者，您可以使用 DBMS _ REDEFINITION，它是一个设计用来做这类事情的黑盒子。

所有这些对于测试来说都是如此麻烦，以至于每当我们发布一个主版本时，我们都会在周日早上停机。

小开

使用 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).

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

小开

一般来说，答案是否定的。您正在改变表的结构，这可能需要大量更新”，我完全同意这一点。如果您希望经常这样做，那么我将提供一种替代“虚拟”列的方法——对 SELECTing 数据使用 VIEW而不是表。在 IIRC，更改视图的定义是相对轻量级的，通过视图的间接操作是在编译查询计划时完成的。费用是必须将该列添加到一个新表中，并在该列中创建视图 JOIN。

当然，这只有在您可以使用外键执行删除级联等操作时才有效。另一个好处是，您可以创建一个包含数据组合的新表，并将视图指向它，而不会影响客户机的使用。

只是个想法。

小开

我建议采取以下两种方法之一:

  1. 在设计数据库表时要考虑潜在的更改。例如，我曾经使用过内容管理系统(ContentManagementSystems) ，它定期更改内容中的数据字段。与其构建物理数据库结构来满足最初的 CMS 字段需求，不如构建一个灵活的结构。在本例中，使用 blob 文本字段(例如 varchar (max))保存灵活的 XML 数据。这使得结构性变化变得不那么频繁。结构性改变可能代价高昂，因此成本也是有好处的。

  2. 有系统维护时间。要么系统在更改期间脱机(每月等) ，并且更改被安排在一天中交通最少的时间(例如，凌晨3-5点)。更改是在生产部署之前进行的，因此您将有一个很好的停机时间的固定窗口估计。

2a.有冗余的服务器，这样当系统停机时，整个站点不会关闭。这将允许您以交错的方式“滚动”您的更新，而不用关闭整个站点。

备选方案2和2a 可能不可行; 它们往往只适用于较大的场地/业务。但是，它们是有效的选项，我个人已经使用了这里提供的所有选项。

小开

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

  • 等待所有更改被复制到被动从机上
  • 把被动的奴隶变成主动的主人
  • 给老主人做结构上的改变
  • 将变更从新主机复制回旧主机
  • 再次进行主交换，同时进行新的应用程序部署

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

小开

参见 Facebook 的在线模式更改工具。

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

胆小的人不会这么做，但它会起作用的。

小开

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

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

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

小开

如果有人还在读这篇文章，或者碰巧来到这里，这就是使用像 mongodb 这样的 NoSQL 数据库系统的最大好处。我在处理修改表时遇到了同样的问题，要么为额外的特性添加列，要么为具有数百万行和高写操作的大型表添加索引。它最终会锁定很长一段时间，所以在 LIVE 数据库上这样做会使我们的用户感到沮丧。在小桌子上，你可以逃脱惩罚。

I hate the fact that we have to "design our tables to avoid altering them". I just don't think that works in today's website world. You can't predict how people will use your software that's why you rapidly change things based on user feedback. With mongodb, you can add "columns" at will with no downtime. You don't really even add them, you just insert data with new columns and it does it automatically.

Worth checking out: www.mongodb.com

小开

TokuDB 可以添加/删除列和“ hot”添加索引，整个过程中表是完全可用的。可通过 www.tokutek.com 购买

小开

Temporary solution...

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

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

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

否则，您可以从 percona 中寻找聚类方法、复制、 pt-online-schema 工具

小开

Percona 制作了一个名为 Pt-online-schema-change的工具，允许这样做。

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

这类似于上面提到的 Dems 方法，但是是以自动方式完成的。

他们的一些工具有一个学习曲线，即连接到数据库，但一旦你有下来，他们是伟大的工具有。

例如:

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

The difference between Postgres and MySQL in this regard is that in Postgres it doesn't re-creates a table, but modifies data dictionary which is similar to Oracle. Therefore, the operation is fast, while it's still requires to allocate an exclusive DDL table lock for very short time as stated above by others.

在 MySQL 中，这个操作会在阻塞事务的同时将数据复制到一个新的表中，这在 v.5.6之前一直是 MySQL DBA 的主要痛苦。

好消息是，自从 MySQL 5.6发布以来，限制一直是 大部分都被解除了，您现在可以享受 MYSQL DB 的真正威力。

小开

正如 SeanDowney 所提到的，pt-online-schema-change是完成您在本问题中所描述的工作的最佳工具之一。我最近在一个 live DB 上做了很多模式更改，效果非常好。你可以在我的博客上看到更多关于它的内容: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/。

小开

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

在线数据定义语言

A feature that improves the performance, concurrency, and availability 在 DDL (主要是 ALTERTABLE)操作期间删除 InnoDB 表 Section 14.11, “InnoDB and Online DDL” for details.

细节因操作类型而异。在某些情况下, the table can be modified concurrently while the ALTER TABLE is in 操作可以在不执行 表复制，或者使用特殊优化的表复制类型 使用量由 inodb _ online _ alter _ log _ max _ size 控制 configuration option.

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

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

有关详细信息，请参阅 MySQL 5.6 Reference Manual -> InnoDB and Online DDL。

看来在线 DDL 在 MariaDB 中也是可用的

或者，您可以使用 ALTERONLINETABLE 来确保您的 ALTER TABLE 不阻止并发操作(不接受锁) 相当于 LOCK = NONE。

关于 ALTER 表的 MariaDB 知识库

小开

You should definitely try pt-online-schema-change. I have been using this tool to do migrations on AWS RDS with multiple slaves and it has worked very well for me. I wrote an elaborate blog post on how to do that which might be helpful for you.

Blog: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/博客: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/


提交答案