ALTER TABLE没有锁定表？

在MySQL中执行ALTER TABLE语句时,整个表在语句的持续时间内被读锁定.如果它是一个大表,那意味着插入或更新语句可能会被锁定一段时间.有没有办法做一个"热改变",比如添加一个列,使得表在整个过程中仍然可以更新？

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

为了澄清,我的目的只是为了避免在需要额外表格列的新功能推向生产时停机.任何数据库架构都会随着时间而改变,这只是生活中的一个事实.我不明白为什么我们应该接受这些变化必然导致停工; 那只是弱者.

1> MatBailie..：

---

唯一的另一个选择是手动执行许多RDBMS系统所做的事情......
- 创建一个新表

然后,您可以一次在一个块上复制旧表的内容.虽然始终对源表上的任何INSERT/UPDATE/DELETE保持谨慎.(可以通过触发器进行管理.虽然这会导致速度变慢,但它不是锁定...)

完成后,更改源表的名称,然后更改新表的名称.最好是在交易中.

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

编辑:

关于这种限制的一些评论有点差.所以我想我会给它一个新的视角来说明它是怎么回事......

添加新字段就像更改每一行上的一个字段.

Field Locks比Row锁更难,更别提了表锁.

实际上,您正在更改磁盘上的物理结构,每个记录都会移动.

这真的就像整个桌子上的UPDATE,但影响更大......

---

在交换之前有一个彻底的测试计划.如果失败,请重新开始.

---

通过触发器管理同步是一个不错的主意.我一直在使用MySQL这么长时间以来我一直忘记它们有触发器.我已经使用过这种技术,现在我有了一个功能性的热变更脚本.有进度条.它适用于MyISAM.生活很好.

---

+1这就是当您在UI中进行某些类型的表更改时,SQL Enterprise管理器在幕后所做的事情.在SQL 2008中,他们实际上添加了一个警告,以便用户知道它执行这个激烈的操作.

---

您没有提到任何有关引用正在更改的表的外键的内容.这不是问题吗？

---

@MohammadRafayAleem - 和AUTOINCREMENT字段,视图和触发器等等.但即便如此,*方法*仍然可行.

2> SeanDowney..：

---

Percona创建了一个名为pt-online-schema-change的工具,允许这样做.

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

这类似于上面提出的Dems建议的方法,但这是以自动方式进行的.

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

例如:

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

3> Ivanov..：

---

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

在线DDL

在DDL(主要是ALTER TABLE)操作期间改进InnoDB表的性能,并发性和可用性的功能.有关详细信息,请参见第14.11节"InnoDB和在线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和在线DDL.

似乎在线DDL也可以在MariaDB中使用

或者,您可以使用ALTER ONLINE TABLE来确保ALTER TABLE不会阻止并发操作(不进行锁定).它相当于LOCK = NONE.

关于ALTER TABLE的MariaDB KB

---

遗憾的是，除了投票之外，别无选择，只能将其浮动到顶部，因为它几乎完全否定了所有其他答案，因为它们不再引用MySQL的当前版本。

4> Steven Sorok..：

---

查看Facebook的在线架构更改工具.

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

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

5> mikelikespie..：

---

如果这是一个选项,我推荐Postgres.使用postgres,以下程序基本上没有停机时间:

ALTER TABLE ADD COLUMN(如果列可以为NULL)

ALTER TABLE DROP COLUMN

CREATE INDEX(必须同时使用CREATE INDEX)

DROP INDEX

其他一些很棒的功能是大多数DDL语句都是事务性的,所以你可以在SQL事务中进行整个迁移,如果出现问题,整个事件都会被回滚.

我之前写过这篇文章,也许它可以对其他优点有所了解.

---

Postgres仍然在alter上创建一个独占锁,阻止其他人从该表中读取.

---

我不同意"基本上没有停机时间"这一点.正如clofresh所说,ALTER TABLE在表上抓取一个独占锁,阻止所有并发读写.根据我的经验,对于活动表,大多数时候你甚至都不会得到锁(ALTER TABLE会饿死).如果你不是非常小心的话,通过交易你很容易就会遇到死锁.因此,我现在总是在改变Postgres中的现有表时设置停机时间.

---

是的,在postgres中更改表会获取独占锁,但由于操作本身在几毫秒内完成,因此在大多数情况下这几乎无关紧要.我个人在工作日中间为数亿行表添加了列,因此停机时间为零.

---

@cobbzilla是的,DROP COLUMN同样快.在引擎盖下它基本上做的是将列标记为隐藏.之前在该列中存在的值仍然在数据文件中(并且对其他事务可见),并且将保持这样,除非并且直到您执行VACUUM FULL.

6> WW...：

---

由于您询问了其他数据库,以下是有关Oracle的一些信息.

将NULL列添加到Oracle表是一种非常快速的操作,因为它只更新数据字典.这可以在很短的时间内对桌子进行独占锁定.但是,它将使任何depedant存储过程,视图,触发器等无效.这些将自动重新编译.

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

如果需要添加外键,可以执行此操作并让Oracle信任数据是否正确.否则,它需要读取整个表并验证所有可能很慢的值(首先创建索引).

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

从那里,您可以在应用程序服务器上执行switcharoo到新版本的代码,它将继续运行.放下偷偷摸摸的触发器.

或者,您可以使用DBMS_REDEFINITION,它是一个黑盒子,用于执行此类操作.

所有这些都非常麻烦,以至于每当我们发布一个主要版本时,我们就会在周日早上停电.