当前位置：首页 > article >正文

MySQL 加字段锁表怎么解决？？

article 2024/11/19 3:17:42

哈喽，各位小伙伴们，你们好呀，我是喵手。运营社区：C站/掘金/腾讯云/阿里云/华为云/51CTO；欢迎大家常来逛逛

今天我要给大家分享一些自己日常学习到的一些知识点，并以文字的形式跟大家一起交流，互相学习，一个人虽可以走的更快，但一群人可以走的更远。

我是一名后端开发爱好者，工作日常接触到最多的就是Java语言啦，所以我都尽量抽业余时间把自己所学到所会的，通过文章的形式进行输出，希望以这种方式帮助到更多的初学者或者想入门的小伙伴们，同时也能对自己的技术进行沉淀，加以复盘，查缺补漏。

小伙伴们在批阅的过程中，如果觉得文章不错，欢迎点赞、收藏、关注哦。三连即是对作者我写作道路上最好的鼓励与支持！

在 MySQL 中，向表中添加字段可能会导致表锁（特别是在使用 MyISAM 存储引擎的情况下），这是因为 MySQL 需要修改表的元数据和重新组织表中的数据。这种操作会在表上加一个锁，阻止其他操作，直到修改完成。对于大表，这种操作可能会持续较长时间，从而影响系统的正常使用。

以下是一些解决**“MySQL 添加字段导致锁表”**问题的解决方案和优化策略：

1. 使用 `ALTER TABLE` 的在线模式 (`ALGORITHM=INPLACE` 或 `ALGORITHM=COPY`)

在 MySQL 5.6 及更高版本中，可以使用 INPLACE 算法来避免在添加字段时锁表。使用 ALTER TABLE 的 ONLINE 选项，可以让表在操作期间依然保持读写操作。

示例：

ALTER TABLE your_table 
ADD COLUMN new_column VARCHAR(255) 
ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE：表示该操作尽可能使用在线方式进行，不会完全锁住表。如果不支持 INPLACE，MySQL 会自动回退到 COPY。
LOCK=NONE：允许读写操作继续进行，确保不会锁住表。

注意：并不是所有的 ALTER TABLE 操作都支持 INPLACE，特别是某些复杂的变更，如修改主键或改变字段类型，可能还是需要加表锁。

2. 使用 `pt-online-schema-change` 工具

Percona Toolkit 中的 pt-online-schema-change 是一个流行的开源工具，它通过创建表的副本来实现对表结构的修改，并在修改过程中将新插入的数据同步到副本中，最后切换表名，从而避免锁表问题。

使用方法：

pt-online-schema-change --alter "ADD COLUMN new_column VARCHAR(255)" D=your_database,t=your_table --execute

D=your_database：数据库名。
t=your_table：表名。
--alter "ADD COLUMN..."：你想要执行的表结构变更。

pt-online-schema-change 可以避免长时间锁表，并且可以在生产环境中安全地对大表进行修改操作。

3. 尽量减少对大表的操作

如果表非常大，即使是在线的 ALTER TABLE 操作或使用 pt-online-schema-change 工具，都会需要较长的时间。为了减少潜在的性能影响，可以考虑以下策略：

分批添加字段：如果你需要添加多个字段，考虑一次只添加一个字段，避免过多的元数据和数据拷贝。
避免高峰时段操作：尽量在数据库的低负载时段进行表结构的变更，以避免影响正常的业务操作。
分区表操作：如果表是分区表，可以对每个分区进行单独操作，减少全表锁定的风险。

4. 在高可用集群上进行变更

如果你的数据库系统是基于主从复制或其他高可用架构，可以考虑以下方案：

在从库上先行操作：你可以在从库上进行表结构的变更，完成后切换主从角色，将有新结构的从库切换为主库。这样，主库的停机时间可以大大减少。
短暂停机操作：对于一些极端情况，如无法使用在线工具，可能需要短时间的停机来进行表结构修改。在停机窗口期间，执行表变更并迅速重启服务。

5. 考虑使用 `ALTER TABLE` 的低优先级锁

如果你的操作环境中允许较长时间的修改，可以通过设置低优先级的锁定方式来减小对其他 SQL 语句的影响。

示例：

ALTER TABLE your_table 
ADD COLUMN new_column VARCHAR(255) 
LOCK=SHARED;

LOCK=SHARED：表示允许同时进行读操作，但写操作会被阻塞，直到修改完成。

尽管这样不会完全解决锁表的问题，但可以让读操作得以继续执行，减少业务中断。

6. 使用不同的存储引擎

如果你使用的是 MyISAM 存储引擎，考虑迁移到 InnoDB。InnoDB 支持更多的在线操作，而且它的行锁机制可以更好地处理并发操作。MyISAM 的表锁机制相对更容易导致锁表问题。

修改存储引擎为 `InnoDB` 的示例：

ALTER TABLE your_table ENGINE=InnoDB;

迁移到 InnoDB 后，许多 ALTER TABLE 操作可以更高效地进行，而且不会像 MyISAM 那样容易导致锁表。

7. 检查 MySQL 版本和配置

不同版本的 MySQL 在 ALTER TABLE 操作时有不同的行为。建议使用较新的 MySQL 版本（5.7 或以上），因为新版本优化了许多表操作，并且支持更多在线修改选项。此外，可以通过优化数据库配置来减少锁表的可能性，例如调整 innodb_online_alter_log_max_size 以支持更大的在线 ALTER TABLE 操作。

总结

MySQL 添加字段锁表的问题可以通过多种方式解决，具体方案取决于你使用的 MySQL 版本、存储引擎以及表的大小等因素。最推荐的方案是使用 在线操作，如 ALGORITHM=INPLACE 或 pt-online-schema-change，这样可以在不停机的情况下进行表结构修改。此外，合理选择操作时间段、分库分表操作也是避免影响生产环境的重要措施。

… …