我听到的关于 MySQL 的最大抱怨之一是,如果您尝试更改其模式(例如添加列或添加索引),它会锁定该表。
“锁定表”是否意味着我既不能读取也不能写入表?有时甚至要几个小时?
这似乎是一个相当严重的限制。我本来打算在新项目中使用 MySQL,但这让我犹豫了。
有解决方法吗?您如何处理更改生产 MySQL 数据库架构的任务?
顺便说一句,有人告诉我 Postgresql 没有这个问题。这是真的吗 - 我可以在更改其架构的同时读取和写入 Postgresql 表?这是否会导致性能损失?
很想听听你的经历。
答案1
是的,MySQL 在执行 ALTER TABLE 语句时会完全锁定表。大部分时间都花在物理复制表上,这就是为什么建议您将所有必要的更改放在一个 ALTER TABLE 语句中。
如果您无法获得合适的维护窗口,则有几种方法可以缓解实时数据库上的这个问题。
首先,许多环境都有几分钟的超时等待表可用于查询,并且不知道(而且很难检查)表被锁定的原因。我曾使用实时网站的这个怪癖来更改表。在我曾经负责的一个网站上,我估计我们有大约 7 分钟的等待时间,然后才会有人开始注意到。:-) 这有助于确保你的老板站在你这边。
另一种方法是使用 select-insert-rename 技巧。如果表的使用UPDATE
频率相当低,或者只是 s 的目标,这种方法很有效INSERT
。基本步骤是复制表的架构,进行必要的更改,编写一个语句来INSERT...SELECT
从旧表到新表,然后重命名表(在一个语句中执行重命名)。您还需要提前准备一个语句来复制在 和 之间添加或更新的任何“新”记录SELECT
。RENAME
我在过去的工作中也做过几次。
然而,也存在一些注意事项:
- 你将要如果源表是 MyISAM,则几乎肯定会出现问题,除非该表几乎从未被写入。这是因为 MyISAM 表锁定的方式。它与 InnoDB 表配合得更好,因为在大型数据库
INSERT...SELECT
运行时仍可以读取它。 SELECT
您需要一种万无一失的方法来找出在和之间添加或更改的记录RENAME
。对于仅用于 的表INSERT
,请使用 auto_increment 列。对于获取 的表UPDATE
,您需要一个可靠的 last-modified 列。
解决此问题的其他方法包括修改从属服务器和故障转移应用程序。这与数据库的复制方式更紧密相关。我自己也没有做过这个,所以我无法描述确切的步骤。
最后,有十几个服务器设置可以调整,还有一些更难更改的设置会影响复制表所需的时间。排序缓冲区是其中之一,MySQL 允许使用的内存量也是另一个。(请记住,您也可以为每个连接设置很多设置,而不是全局设置一些设置。)处理大量数据时,MySQL 具有“临界点”效应,即在达到一定大小之前,一切都相当线性,然后突然变得糟糕。它经常会出现处理大量数据的复杂查询,并且与内部临时表大小以及允许使用的内存量有关,但它可能会出现表更改,因为它们涉及重新索引数据。这就是为什么给数据库更多内存几乎总是一件好事的原因之一。
答案2
那是你听到过关于 MySQL 的最大抱怨是什么?天哪,我听到过比这更大的抱怨……(也许以后再讲吧)
是的,当您对表运行 ALTER TABLE 时,MySQL 会完全锁定该表;在此期间,不会进行任何读取或写入操作,并且尝试执行此操作的查询会暂停,直到完成为止。偶尔,我必须修改大型 MySQL 表的架构(小型表的更改完成得足够快,不会造成明显的问题),我通常只需安排一个维护窗口,然后执行此操作。要准确衡量副本上给定表的此类更改需要多长时间并不难。
如果你遇到了那些白痴管理层,拒绝让你有合理的维护时间(如果是这样,那就拼命地跑去另一份工作),那么我听说有人会做一些事情,比如复制一个表的架构,修改空表,然后在新表中执行复制选择(设置写锁以防止更改),然后重命名表。听起来对我来说风险太大了。Maatkit 的mk-表同步有一种模式可以做到这一点,如果你想在它死后责怪别人。
虽然 PostgreSQL 没有“巨型末日锁”,但修改 PgSQL 中的表架构仍会严重影响性能——这需要大量磁盘 IO。不过,我无法想象任何 RDBMS 能够如何避免这种情况。