记一次Mysql大批量数据更新

背景

因公司业务架构变更，需要对原有账户累计收益进行备份，削弱老业务对新业务的影响。而原有用户累计收益数据约为7000w，因为处于业务过渡阶段，所以希望以一种临时的手段去存储数据，最终讨论得出，在用户表新增一个字段old_balance来存储这个数据。

大表加字段

从图中看到，member表数据空间占用11.12G。另外也发现索引占用的空间比数据还大，可见索引的创建需要慎重。

一开始以为加字段会锁表，使得线上服务不可用。但后来发现是不需要的。
Mysql支持在增加列的过程中并发DML。

Mysql5.6官方文档 https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html#online-ddl-column-operations

大致流程是：重建临时表，数据拷贝到临时表中，然后删除原表，将临时表的表名更改为原表。
可惜，十几个G的member大表增加bigInt字段，花了两个多小时，最终算是成功了，有惊无险。

批量数据更新

需要更新的数据是通过原有接口获取的，然后再更新到member表中，虽然一条数据花费时间不会很久，但当数据量到千万级，累计起来还是很可观的。

相信很多同学都想到了，并行执行。

如果单线程执行，更新一条数据大于20ms，则7000w * 20ms / 1000 * 86400 约等于16天，为了尽快完成任务，推动账户迁移项目的进度，
我就加大线程数，将数据加到线程池中处理，一开始单机起了40个线程，处理速度还是太慢。

然后线程数40 -> 逐步改成了400，批量处理速度的确比较快，1分钟能处理10w数据。
但一看数据库：

cpu已经飙到了100%，线上系统也出现了告警

后来发现，这除了和并行执行有关，还和分页查询的性能有关。

类似于对全量数据的分页扫描，可以采用主键Id进行分页，如：

select * from tableName where id between (pageNo-1)*pageSize and pageNo*pageSize;

不要使用

// 随着数据量的增多，limit后面会越来越大，性能急剧降低，损耗性能
select * from tableName order by id limit (pageNo-1)*pageSize ,pageSize;

这种方法，随着数据量的增多，limit后面会越来越大，扫描的数据越来越大，性能急剧降低，损耗性能

小贴士
select * from tableName order by id limit pageSize offset (pageNo-1)*pageSize;
等价于
select * from tableName order by id limit (pageNo-1)*pageSize ,pageSize;
都表示从(pageNo-1)*pageSize开始往后查找pageSize个

所以后来修改了查询sql，同时根据Mysql压力情况调整并发线程数。

使得CPU控制在安全范围内。

小结

批量处理超大数量的数据，我们会考虑使用多线程。同时需要考虑系统的性能状况合理控制并发度。