高效的千万级数据全表 update 正确姿势

钓友宝 （微信小程序）：一款专门为 钓友 开发的 免费的 分享钓点地图与实时天气的软件，地图中标记了所有野钓、钓场、公共水域等的精确位置，支持导航、 预测钓鱼位置的鱼情 等功能。

有些时候在进行一些业务迭代时需要我们对Mysql表中数据进行全表update，如果是在数据量比较小的情况下（万级别），可以直接执行sql语句，但是如果数据量达到一个量级后，就会出现一些问题，比如主从架构部署的Mysql，主从同步需要需要binlog来完成，而binlog格式如下，其中使用statement和row格式的主从同步之间binlog在update情况下的展示：

我们当前线上mysql是使用row格式binlog来进行的主从同步，因此如果在亿级数据的表中执行全表update，必然会在主库中产生大量的binlog，接着会在进行主从同步时，从库也需要阻塞执行大量sql，风险极高，因此直接update是不行的。

本文就从我最开始的一个全表update sql开始，到最后上线的分批更新策略，如何优化和思考来展开说明。

直接update的问题

我们前段时间需要将用户的一些基本信息存储从http转换为https，库中数据大概在几千w的级别，需要对一些大表进行全表update，最开始我试探性的跟dba同事抛出了一个简单的update语句，想着流量低的时候执行，如下:

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

上面肯定是不合理的会给主库生成binlog、从库接收binlog写数据带来很大的压力，于是就想使用脚本分批处理如下所示：写一个这样的脚本，依次分批替换，limit的游标不断增加。

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

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

既然mysql的深分页有问题，那么我就把这批id全部查出来，然后更新的id in这些列表，进行批量更新可以吗？

于是我又写了类似下面sql的脚本。结果是还不行，虽然mysql对于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};

最终在与dba的多次沟通下，我们写了如下的sql及脚本，这里有几个问题需要注意，我们在select sql中使用了这个语法法/*!40001 SQL_NO_CACHE */，这个语法的意思就是本次查询不使用innodb的buffer pool，也不会将本次查询的数据页放到buffer pool中作为热点数据的缓存。

接着对于查询强制使用主键索引 FORCE INDEX(PRIMARY)，并且根据主键索引排序，排序后的数据进行id游标的筛选。最后执行update更新时，由于我们在前面的sql中查询到的就是已经排序后的主键，因此可以对id执行范围查找。

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}";

我们可以仅关注第一个sql，如下图所示，是buffer pool大概内容，我们可以通过这个no cache的关键字，对批量处理的数据进行强制指定不走buffer pool，不把这些冷数据影响到正常使用的缓存内容，防止效率的降低，其实mysql在一些备份的动作中。

使用的数据扫描sql也会带上这个关键字，防止影响到正常的业务缓存；接着需要强制对当前查询指定的主键索引，然后进行排序，否则mysql有可能在计算io成本进行索引选择时，选择其他的索引。

使用这样的方式对数据库进行批量更新可以通过一个接口来控制速率，对于数据库主从同步、iops、内存使用率等关键属性进行观察，手动调整刷库速率。这样看是单线程阻塞的操作，其实接口也可以定义线程个数等属性，接口中根据赋予的线程个数，通过线程池并行刷数据，从而提高全表更新速率的上限，同时对速率进行控制控制。

其他问题

如果我们使用snowflake雪花算法或者自增主键来生成主键id的话，插入的记录都是根据主键id顺序插入的，如果使用uuid这种我们怎么处理？

当然是业务中就预先处理了，先把入库的数据提前进行替换，进行代码上线后再进行的全量数据更新了。

结语

刷数据本来是一个异常枯燥的工作内容，但是从这次数据量较大的数据更新从而与dba同事的多次沟通后，也对mysql有了一些新的理解，包括不限于下面几个，共同学习。

• binlog格式带来的大数据量更新的主从同步问题；

• Mysql深分页的效率问题；

• 全表扫数据如何防止对buffer pool污染到我们业务正常的热点数据。

Java精选面试题 （微信小程序）：5000+道面试题和选择题，包含Java基础、MQ、Redis、SpringBoot、Elasticsearch、Docker、K8s、Flink、Spark、架构设计、大厂真题等，在线随时刷题！

来源：https://juejin.cn/post/6897185211340029966

公众号“Java精选”所发表内容注明来源的，版权归原出处所有（无法查证版权的或者未注明出处的均来自网络，系转载，转载的目的在于传递更多信息，版权属于原作者。如有侵权，请联系，笔者会第一时间删除处理！

最近有很多人问，有没有读者或者摸鱼交流群！加入方式很简单，公众号Java精选，回复“加群”，即可入群！

文章有帮助的话，点在看，转发吧！