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

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

👉 欢迎加入小哈的星球，你将获得: 专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

• 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17..., 点击查看项目介绍；

• 《从零手撸：前后端分离博客项目（全栈开发）》 2期已完结,演示链接：http://116.62.199.48/;

截止目前，累计输出 54w+ 字，讲解图 2330+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，Spring Cloud Alibaba 等等，戳我加入学习，解锁全部项目，已有1900+小伙伴加入

有些时候在进行一些业务迭代时需要我们对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 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;

in的效率

既然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这种我们怎么处理？当然是业务中就预先处理了，先把入库的数据提前进行替换，进行代码上线后再进行的全量数据更新了。

👉 欢迎加入小哈的星球，你将获得: 专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

• 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17..., 点击查看项目介绍；

• 《从零手撸：前后端分离博客项目（全栈开发）》 2期已完结,演示链接：http://116.62.199.48/;

截止目前，累计输出 54w+ 字，讲解图 2330+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，Spring Cloud Alibaba 等等，戳我加入学习，解锁全部项目，已有1900+小伙伴加入

1. 我的私密学习小圈子~
2. TikTok二面：“聊聊二维码扫码登录的原理”
3. 效率爆表！EasyExcel合并单元格这样实现才是yyds
4. 深入理解Nginx工作原理及优化技巧

最近面试BAT，整理一份面试资料《Java面试BATJ通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。
获取方式：点“在看”，关注公众号并回复 Java 领取，更多内容陆续奉上。

PS：因公众号平台更改了推送规则，如果不想错过内容，记得读完点一下“在看”，加个“星标”，这样每次新文章推送才会第一时间出现在你的订阅列表里。
点“在看”支持小哈呀，谢谢啦