logo标签列表

不锁定表的情况下进行ALTER TABLE操作？

sqlmysqlddlalter-table
118

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

我主要关心的是MySQL的解决方案，但如果MySQL不能实现，我也对其他关系型数据库管理系统感兴趣。

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

2
不禁想问你会修改表多少次？ - Allain Lalonde
1
在我看来，数据库模式更改通常伴随着全新版本 - 它们不像其他更改那样零散地推出。这无疑是一件大事。 - dkretz
13
“@AllainLalonde - 多次询问这个问题是合理的，特别是如果系统停机会危及生命或导致大量损失。无论如何，新的软件需求有时会出现。” - Nathan Long
19个回答
66
唯一的另一个选择是手动执行许多RDBMS系统已经执行的操作...
- 创建新表
然后您可以将旧表的内容逐块复制过去。始终小心源表上的任何INSERT/UPDATE/DELETE。(可以通过触发器进行管理。虽然这会导致减速，但不会锁定...)
完成后，更改源表的名称，然后更改新表的名称。最好在事务中执行。
完成后，请重新编译使用该表的所有存储过程等。执行计划可能不再有效。
编辑：
关于此限制存在的一些评论有点不好。因此，我认为我应该提供新的视角来说明它为什么是这样...
添加一个新字段就像更改每行上的一个字段。
字段锁比行锁要困难得多，更别说表锁了。
您实际上正在更改磁盘上的物理结构，每个记录都会移动。
这确实像对整个表格的更新，但影响更大...
2
在交换之前，一定要有一个全面的测试计划。如果失败了，就重新开始。 - dkretz
2
通过触发器管理同步是个好主意。我使用MySQL已经很长时间了，以至于我一直忘记它们现在有触发器了。我使用了这种技术，现在我拥有一个功能齐全的热修改脚本。带有进度条。而且它可以在MyISAM中工作。生活很美好。 - Daniel
2
+1 这实际上就是 SQL Enterprise Manager 在 UI 中进行某些表更改时在幕后执行的操作。在 SQL 2008 中，他们实际上添加了一个警告，以便用户知道它正在执行这种激烈的操作。 - BradC
2
你没有提到外键引用正在被修改的表。这不会成为一个问题吗？ - Rafay
2
@MohammadRafayAleem - 还有AUTOINCREMENT字段、视图、触发器等等。但即便如此，这种方法仍然可行。 - MatBailie
显示剩余2条评论
44
Percona推出了一个名为pt-online-schema-change的工具，可以实现此操作。
它基本上复制了表格并修改了新表格。为了保持新表格与原始表格同步，它使用触发器进行更新。这允许在后台准备新表格的同时访问原始表格。
这类似于上面Dems建议的方法，但是这种方法是自动化的。
他们的一些工具有一个学习曲线，即连接到数据库，但一旦掌握了这个技巧，它们就是非常好的工具。
例如：
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends
1
似乎链接已经失效。我发现这个链接可以正常使用。 - Noam Ben Ari
34

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

在线DDL（数据定义语言）

这是一种对InnoDB表的性能、并发性和可用性进行改进的功能，在DDL（主要是ALTER TABLE）操作期间使用。有关详细信息，请参见第14.11节，“InnoDB和Online DDL”。

具体细节根据操作类型而异。在某些情况下，在ALTER TABLE正在进行时可以同时修改表。该操作可能无需进行表副本，或使用特别优化的表副本类型即可执行。空间使用由innodb_online_alter_log_max_size配置选项控制。

它允许您在DDL操作中调整性能和并发性之间的平衡，通过选择是完全阻止访问表（LOCK=EXCLUSIVE子句），允许查询但不允许DML（LOCK=SHARED子句），还是允许完全查询和DML访问表（LOCK=NONE子句）。如果省略LOCK子句或指定LOCK=DEFAULT，则MySQL允许尽可能多的并发性，具体取决于操作类型。

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

有关更多信息，请参见MySQL 5.6参考手册->InnoDB and Online DDL。

看起来MariaDB也支持在线DDL

或者您可以使用ALTER ONLINE TABLE来确保您的ALTER TABLE不会阻止并发操作（不锁定）。它等效于LOCK=NONE。

MariaDB关于ALTER TABLE的知识库

4
很遗憾，除了投票之外，没有其他的方式可以让这个问题浮现到最顶部，这几乎否定了所有其他答案，因为它们不再参考当前版本的MySQL。 - Burhan Ali
17
14
7
Postgres 在进行 alter 操作时仍会创建独占锁，阻止其他人从该表中读取数据。 - clofresh
5
我不同意“几乎没有停机时间”的说法。正如clofresh所说，ALTER TABLE会在表上获取独占锁，从而阻止所有并发的读写操作。以我的经验来看，对于活跃的表，大多数情况下你甚至都无法获得锁（ALTER TABLE将会饥饿）。如果你不非常小心，使用事务时很容易出现死锁。因此，在Postgres中修改现有表时，我现在总是设置停机时间。 - Pankrat
1
更详细的解释请参考：http://dba.stackexchange.com/questions/27153/alter-table-on-live-production-databases#27156，它提到了独占锁的影响以及一些解决方法。 - John Douthat
4
是的，在Postgres中修改表会获取独占锁，但由于操作本身只需要毫秒级别的完成时间，在大多数情况下这几乎不会产生影响。我曾亲自在营业时间内向亿级行数的表添加列，而没有造成任何停机时间。 - Noah Yetter
2
@cobbzilla 是的，DROP COLUMN 也同样快。在底层，它基本上是将列标记为隐藏。在删除该列之前存在的值仍然存在于数据文件中（并且对其他事务可见），除非您执行 VACUUM FULL，否则这些值将保持不变。 - Noah Yetter
显示剩余2条评论
7

既然您询问了其他数据库，这里提供一些有关Oracle的信息。

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

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

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

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

从那里开始，您可以在应用程序服务器上切换到新版本的代码，它将继续运行。删除您的隐秘触发器。

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

所有这些都需要测试等许多麻烦，因此我们只在发布主要版本时进行早期的星期日早晨停机。

3
如果你在应用程序更新时不能容忍数据库的停机时间，你应该考虑维护一个双节点集群以实现高可用性。通过简单的复制设置，您可以进行几乎完全在线的结构更改，就像您建议的那样：
- 等待所有更改在被动从库上复制 - 将被动从库更改为活动主库 - 对旧主库进行结构更改 - 从新主库到旧主库中复制更改 - 再次进行主节点交换和新应用程序部署
虽然这不总是容易，但它确实有效，通常可以实现0停机时间！ 第二个节点不必仅仅是被动节点，它可以用于测试、统计或作为备用节点。如果您没有基础设施，可以在单台机器上设置复制（带有两个MySQL实例）。
1
老的主节点是在集群外还是在集群内？ - John Chornelius
2

临时解决方案是...

另一个解决方案可能是，在原始表的主键之外添加另一个表，同时添加你的新列。

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

当你能够获得停机时间时，可以更改原始表，修改DML查询并删除之前创建的新表。

否则，你可以采用聚集方法、复制、来自Percona的pt-online-schema工具。

2
不行。如果您使用的是MyISAM表，据我所知它们只执行表锁定 - 没有记录锁定，它们只是通过简单保持一切超级快速来尝试。 （其他MySQL表的操作方式不同。）无论如何，您可以将表复制到另一个表中，对其进行更改，然后切换它们，并根据差异进行更新。
这是如此大的变化，以至于我怀疑任何DBMS都不支持它。能够首先在表中使用数据进行此操作被认为是一种好处。
InnoDB使用行锁 - http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html - Eran Galperin
是的，MySQL 是个例外。这就是为什么我特别强调“标准”表的原因。 - dkretz
所有提到的存储引擎都是默认安装的。您可以通过表创建查询来确定存储引擎。 回答你的问题，我已经使用MySQL超过7年了。 - Eran Galperin
关于锁定的那句话完全不正确，你应该把它删除。 - Eran Galperin
通过说“标准”，您意味着不仅使用默认存储引擎 - 而是使用“标准”存储引擎。 我在我的应用程序中使用InnoDB作为标准存储引擎，大多数需要事务的MySQL生产数据库也是如此。 因为您已经解释清楚了，所以我取消了我的踩票。 - Eran Galperin
显示剩余7条评论
2
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接