千万级数据全表更新的正确姿势

创作时间:

作者:

@小白创作中心

千万级数据全表更新的正确姿势

引用

来源

https://dbaplus.cn/news-155-6297-1.html

有些时候在进行一些业务迭代时需要我们对Mysql表中数据进行全表update，如果是在数据量比较小的情况下（万级别），可以直接执行sql语句，但是如果数据量达到一个量级后，就会出现一些问题，比如主从架构部署的Mysql，主从同步需要需要binlog来完成，而binlog格式如下，其中使用statement和row格式的主从同步之间binlog在update情况下的展示：
格式内容
statement 记录同步在主库上执行的每一条sql，日志量较少，减少io，但是部分函数sql会出现问题比如random
row 记录每一条数据被修改或者删除的详情，日志量在特定条件下很大，如批量delete、update
mixed 以上两种方式混用，一般的语句修改使用statement记录，其他函数式使用row

我们当前线上mysql是使用row格式binlog来进行的主从同步，因此如果在亿级数据的表中执行全表update，必然会在主库中产生大量的binlog，接着会在进行主从同步时，从库也需要阻塞执行大量sql，风险极高，因此直接update是不行的。本文就从一个全表update的场景开始，探讨如何优化和思考来展开说明。
直接update的问题
假设我们需要对一个包含数千万条记录的表进行全表更新，最开始可能会尝试直接执行以下SQL语句：

update tb_user_info set user_img=replace(user_img,'http://','https://')

这种做法在数据量较大时会带来很大的问题，因为会在主库中产生大量binlog，从库也需要阻塞执行大量SQL，风险极高。

深度分页问题
于是尝试使用脚本分批处理：

update tb_user_info set user_img=replace(user_img,'http://','https://') limit 1,1000;

但是仔细分析发现，MySQL的limit游标进行的范围查找原理是下沉到B+数的叶子节点进行的向后遍历查找，在limit数据量比较大的情况下，效率很低接近于全表扫描，这也就是我们常说的“深度分页问题”。

in的效率
尝试将id全部查出来，然后更新的id in这些列表：

select * from tb_user_info where id> {index} limit 100;
update tb_user_info set user_img=replace(user_img,'http','https')where id in {id1,id3,id2};

结果发现效率仍然很低，虽然MySQL对于in这些查找有一些键值预测，但是仍然是很低效。

最终版本
最终优化方案如下：

select /*!40001 SQL_NO_CACHE */ id from tb_user_info FORCE INDEX(`PRIMARY`) where id> "1" ORDER BY id limit 1000,1;
update tb_user_info set user_img=replace(user_img,'http','https') where id >"{1}" and id <"{2}";

这个方案有几个关键点：