[mysql] mysql alter table 的时候，会锁表，导致系统无法服务，如何处理？是否应该考虑用 mysql 5.7 json data type 来解决该问题？

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 2913 天前的主题，其中的信息可能已经有所发展或是发生改变。

功能开发，需要新增字段时，会 alter table ，当表大了后（千万级别），更新会慢，会有无法服务的时间，这个大家都是如何处理的？
是否应该考虑 mysql 5.7 的 json data type 来解决上面说到的问题？

14 条回复 • 2016-11-25 09:43:03 +08:00

yangqi

2016-11-24 11:33:35 +08:00

大表肯定不能随便 alter ，也不应该频繁 alter

一般都是新建表，数据复制到新表然后 rename 切换。

mahone3297

2016-11-24 11:38:17 +08:00

@yangqi
* 确实有需求，需要新增字段怎么办？还是要加字段， alter table 啊。所以想着是否用 json 类型来处理
* 新建表，数据复制到新表的时候，要锁住老表么？复制的过程，时间是否和 alter table 差不多了？不锁表，那如何保证老表和新表数据一致？

skywayman

2016-11-24 11:42:47 +08:00

online-schema-change
or
see: [url]http://www.cnblogs.com/wangtao_20/p/3504395.html[/url]

shibingsw

2016-11-24 11:45:35 +08:00

@mahone3297 不用锁老表，复制开始的时候记录下记录的最大 ID ，然后两个表双写，当复制到最大 id 时候结束。这样两个表的的数据就一模一样了， rename 。至于如何实现双写，一般是配置触发器巴，当老表新增数据了，就触发新增一个。老表老的数据修改了且已经同步到新表了，就让新表也更新一下。

zhx1991

2016-11-24 11:47:32 +08:00

rename 复制数据切

就切的那一下会卡一点

akira

2016-11-24 11:55:04 +08:00

正常做法不是应该开个新表来放新字段么。。虽然会导致一堆历史遗留

Infernalzero

2016-11-24 12:24:38 +08:00

1.建表的时候都加上保留字段
2.shadow copy,percona toolkit 或者 facebook 的那个 online schema change 工具
3.或者提供一台不对外服务的服务器，在这台上执行 alter table 操作，跑完了再和线上库切换
4.最笨的办法就是提前给用户发通知，预约维护时间，然后停服操作，当然这是最不希望用的方法啦

shuiguyu

2016-11-24 13:13:33 +08:00

@shibingsw 正解，一般表迁移也都会用这种方式。
不建议在现有的表基础上 alter ，锁表是一个，还有重建索引的问题。
一个办法是读写分离，先在读库建新表，导数据切换，完成后再升级为写库，轮换切换表。

neoblackcap

2016-11-24 13:15:19 +08:00

https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html 这个工具能帮到你，原理还是复制之后重命名
percona 的工具有 facebook 在用，能上生产环境

7rack

2016-11-24 15:52:23 +08:00

Online DDL

wmttom

2016-11-24 17:42:47 +08:00

分享个实践中遇到的情况。实践中 MySQL online DDL innodb 很容易被 matedata lock 卡住，如果有长时间事务的话。
感觉一般 web 场景下原因都是代码写的不好，事务处理的不好，没有严格遵守开启事务 -> 执行操作 -> 出错 rollback -> 完成 commit 的流程，可能造成长时间挂着没结束的事务。

est

2016-11-24 17:45:22 +08:00

matedata lock 可以通过杀 sleep 连接解决。

julyclyde

2016-11-24 23:39:06 +08:00

facebook 那个我记得根本没法运行
percona 的可以
不过 5.7 不是直接支持 online schema change 吗？？

yangqi

2016-11-25 09:43:03 +08:00

@mahone3297 建新表，老表只需要锁写，不用锁读。另外根据表的更新情况，也可以分段复制，不用所表。