亿级大表如何修改表结构【MySQL】

一、问题背景

随着数据库数据量进一步增加，最大的表目前已经达到10亿+了，虽然已经进行的数据库的分库分表（采用阿里云的polardb），但是大表要改表结构的时候，还是会出现死锁的情况，系统会收到严重影响。

二、深入讨论

那我们大表该如何修改表结构呢？网络搜索了一圈，基本都围绕了两种方法进行：第一种是在用户访问量少的时间段，进行表结构修改。第二种是采用copy替换原表的方法。

其实相对上述两种方式，还有一种方法可以从业务上面处理，就是在设计表结构的时候，根据现有业务和未来规划的业务，提前预留足够多的字段，后面表数据量大了，也不需要进行修改。但是这种方式需要对业务规划非常清楚，而且也没办法100%解决大表修改字段问题，只能说缓解。

三、原表直接修改

对于用户访问量少的时间段进行表结构修改或者类似12306这种，凌晨11点-6点进行数据维护，直接停服修改表结构。原表直接修改，最大好处就是：简单方便，不需要考虑其它的问题，直接执行DDL命令即可。

但是如果业务迭代比较快，或者改动表结构比较频繁的，系统又不允许停服（大多数系统都不允许停服），这个时候修改表结构就很痛苦了，一方面是开发人员需要经常性加班，而是如果改动是核心表，系统大部分模块都有关联到这个模块的时候，很容易改出问题，导致数据库死锁或者干脆宕机的风险。

大表虽然修改表结构会产生很多问题，但是大表本身是可以做优化的，最明显的优化就是去除不必要的索引或者将多个单个索引合并成一个索引，减少大表索引数据量大小，进而减少修改表索引的时间。

四、copy替换原表

copy替换原表方式，虽然没有原表直接修改风险那么大，但是技术实现上面很复杂。我们先来介绍一下，什么叫做copy覆盖原表。

简单的来说，就是新建一张表，然后将你需要修改的表结构先添加上去，因为是空表，所以可以瞬间完成修改。后面再通过数据同步工具，将原表的数据导入到新表中。当数据导入差不多的时候，将原表修改为原表_copy，新表修改为原表的名称，这一步也叫做表切换。

copy替换原表技术难点有3点：

数据同步问题

数据同步时间问题

切换表数据丢失问题

4.1 数据同步问题

像我们系统用的是阿里云的polardb分布式数据库，本身就提供了数据同步的功能，可以先全量同步，然后再增量同步。其它非云平台数据库，也可以用第三方成熟的工具来进行数据同步，例如 pt-online-schema-change。

https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

4.2 数据同步时间问题

同步数据切忌不要走外网，外网的带宽一般都非常小，大表的同步时间将会非常长，内网同步也需要小心，如果同步数据占用了所有的带宽，那业务请求就执行不了了。

4.3 切换表数据丢失问题

切换表名这一步，数据库层面做不了限制，首先MYSQL不支持在锁住表的情况下，再去修改表名。所以避免不了在修改表名的那一刻，应用程序涉及到这个表的所有操作都会抛出异常，会导致切换表名过程中，数据发生丢失。

针对切换表名导致数据丢失的问题，只能在应用层面做处理，如果系统的应用层有做SQL过滤器的话，就可以在SQL过滤器做特殊的处理，保证这一时刻的数据不会丢失，总之要有一个处理方案，保证数据不丢失即可。

五、总结

直接修改表结构既然有这么多问题，那为什么大多数企业都选择直接修改表结构呢，而不是copy替换原表的形式呢。其实很简单，一方面大部分公司的数据量达不到亿级，另一方面也没办法保证copy替换原表数据的完整性，所以宁愿选择用户访问量少的，也不愿意用其它的方式。

但是如果系统数据量进一步增加，用户对系统的要求又是24小时可靠，那这个时候修改原表的风险就会大大增强。特别是修改核心表结构的时候，后果可能是灾难级别。这就要求我们解决copy替换原表过程中核心难点。数据同步工具的选择、数据同步时间的保障、表名切换的数据完整性保证等。

-----------------------

公众号：林老师带你学编程

网站：www.wolzq.com

代码无非增删改查，关注老师给你Coding

  林老师带你学编程：http://www.wolzq.com