MySQL千万级大表在线变更表结构

我接触过很多公司的数据库架构都缺乏有效合理的设计，如果早期的设计不合理，后期随着表数据量的增加就不可避免的进行表结构变更。

分享一例MySQL千万级大表在线变更表结构的案例。处理问题的思路和角度各有不同，希望这篇文章可以抛砖引玉。

 

一、问题背景

MySQL数据库存储的是结构化的数据类型，数据表记录从上万到上千万上亿，在公司业务不允许停服务的情况下，在线变更表结构是个很头疼的事情。

当单表记录数过大时，增删改查性能都会急剧下降，在线变更表结构是不得不做的事，那么如何在不停机不影响业务的情况下进行表结构变更？

 

二、难点问题

• 单实例情况下，单表量级达到千万以上，如何确保修改字段而不锁表？

• 主从情况下，单表量级达到千万以上，如何确保修改字段而不锁表？

• 如何对大表进行分库分表存储，来实现高效、稳定的数据读写？

 

三、问题分析思路

问题一：单表量级达到千万以上，如何确保修改字段而不锁表？

单实例情况，建议使用 pt-online-schema-change 或者 gh-ost 工具，二者均不会产生锁表，前者通过触发器实现，后者通过解析 binlog 实现。

 

问题二：主从情况下，单表量级达到千万以上，如何确保修改字段而不锁表？

主从情况，如果使用 pt-online-schema-change 工具，要在主库做变更；如果使用 gh-ost 工具，主从均可。

 

问题三：如何对大表进行分库分表存储，来实现高效、稳定的数据读写？

• 分区：表分区之后依靠引擎存储去保证；

• 分表：业务逻辑涉及的更多一些，这种情况下单库也相对好办；

• 分库：最大的问题就是分布式事务，目前市场有很多开源中间件可以选择，但未必能够满足需求，需要进行选择。

 

其实在数据库架构初期就要判断是否会出现大表以及数据的生命周期。对于海量数据，可以分为历史数据和现行数据考虑存储方式：

• 历史数据：可以从现行数据表中进行定期剥离，后续只是进行查询，完全可以将历史数据转存到历史库。

• 现行数据：对于频繁访问的数据，一般是整个系统的性能瓶颈点，可以考虑 SSD 的硬盘，这样能保证既有业务的快速响应。

 

对于数据生命周期的管理还是需要考虑业务实际场景：

数据库架构作为架构设计中的业务架构、应用架构、数据架构和运行架构中的架构之一，应该是与其他架构设计逻辑整合的一起的，不能把落地实现的所有功能都交给数据库。

在实践中比如不用存储过程、不用外键、尽量单表操作，这些应尽量交给应用层面去做，在应用层面进行逻辑处理；为什么要放在应用层？，因为做这种处理的服务器一般可以较好的进行扩展，提高响应能力，减少数据库层面压力。

 

四、线变更表结构方案有哪些？

• 直接 ALTER TABLE

• oak-online-alter-table

• 5.7 新增 online rename index

• pt-online-schema-change

• gh-ost

• 自定义脚本

 

五、生产环境下，变更 Mysql 的表结构步骤是什么？

1.确认表的元数据信息；

2.对需求里的改表语句进行审核，如果存在不规范的地方进行修正。

3.确认当前节点是主节点还是从节点。

4.确认主从状态是否正常。

5.根据数据量、业务场景、业务容忍度，选择变更的方案以及预估需要的时间。

6.确定操作时间点，如果数据量大，建议在低峰进行。

7.发布变更通知，告知应用观察对应业务。

8.变更过程中，留意 MySQL 监控和机器监控，观察主从状态、主从连接数、主从机器负载。

9.变更过程出现问题，及时 KILL 相关操作。

10.变更顺利完成，进行数据校验。