运维总结：mysql大数据量的表结构修改

璐璐

这两年我们基本都是使用pt-ocs和gh-ost来做，尤其gh-ost 表结构修改，不停服，侵入少，可暂停，有点还是蛮多的。以下的文章内容，仅限于当时的知识所写。

因业务需要，数据库的12张表需要修改表结构+1张库表分区优化，其中有5张大表，mysql中修改大表结构是非常痛苦的一件事。

整体状况：

1、其中5张大表，总数据量在80亿以上，分布在12台服务器，66个库中，大表通常一个单库单表大的是在3000万左右。注：我们使用的是mycat中间件。

2、数据库表结构必须一次性修改，同时升级新的应用程序。

3、利用周末晚上的时间，所以修改时间定在周六0点开始，采用停服升级。

升级前的准备工作：

1、表结构修改方式，mysql5.6虽然可以在线修改表结构，但是使用alter table 的方式修改效率过慢，千万级的数据表结构修改无法满足性能要求。采用新建一张新表结构，然后从旧表里将数据查询出来插入到新表中，经过测试，单实例下3000万的数据20个进程需要8分钟左右。

2、因为表多，库多，所以需要编写批量自动升级程序，否则逐个操作容易出问题。花了2天时间写好程序脚本

3、新旧表数据检查核对脚本，怕出现迁移程序出现问题，需要进行结果检查。

4、业务程序测试和功能测试  有测试人员完成

升级过程中的问题总结：

0点停服开始进行升级，升级开始的时候还算顺利，但是往后就很快发现表插入速度变得缓慢，检查数据库发现，io繁忙，应该是日志空间来不及做check到磁盘上，查checkpoint_age已经到了临界点。说明此时速度瓶颈已经变成io随机写的速度。在测试过程中由于单个服务器上只测试了一个3000万级的表，而生产环境单服务器上需要连续插入30个，是完全不一样的。

问题总结1：测试过程我们常常只关注单个服务器单库中的内容，却忽略了实际生产环境中持续写对数据库的压力。在做测试中务必要尽可能的模拟线上服务器。同时测试时关注各个参数的比较，如果在测试过程中能够检查下checkpoint_age应该能发现这个状态值会增加很多，而在测试结束后才逐渐下降。这时就会引起关注，进行连续的测试就能发现瓶颈。

问题总结2：由于mysql单实例插入的数量非常多，对后面的从库压力很大，从库采用的是单进程方式，所以速度明显的跟不上主库。在升级过程中由于速度慢，io量大，所以当时将binlogformat从row模式改成了statement,这样可以大大减少binlog大小，输出可以减少很多。但是也给从库带来了更大的压力。

最后需要思考的问题：

大表的升级，如何才能做到快速甚至不停服的修改表结构。这个是我们日后要研究的重点。