千万级数据的全表更新的正确方式
在处理业务迭代时，有时候需要对MySQL表中的数据进行全表更新。当数据量较小时（万级别），可以直接执行SQL语句，但当数据量达到亿级别时，问题随之而来。尤其是主从架构的MySQL数据库，在进行主从同步时会依赖binlog，其格式如下：
- Statement: 记录主库上执行的每一条SQL，日志量较小，但一些函数（如
random）可能会出现问题。 - Row: 记录每条数据被修改或删除的详细信息，日志量较大。
- Mixed: 混合使用
statement和row两种方式，常规SQL使用statement，其他复杂SQL使用row。
如果在亿级数据表上执行全表update，将产生大量binlog，会导致主库负载剧增，并影响主从同步的性能。因此，直接执行全表update并不可行。
直接update带来的问题
例如，某次需要将用户的基本信息中的HTTP链接转换为HTTPS，涉及到上千万条记录，初步尝试了直接执行以下SQL：
update tb_user_info set user_img = replace(user_img, 'http://', 'https://');
这种方式生成的binlog对主库和从库都会带来巨大的压力。
深度分页问题
为避免这种压力，可以通过分批处理来更新数据。常见的方式是使用limit分页：
update tb_user_info set user_img = replace(user_img, 'http://', 'https://') limit 1, 1000;
然而，MySQL的limit操作在分页较深时效率会急剧下降，原因是MySQL需要从B+树的叶子节点开始进行遍历，导致性能问题。
in操作的低效
另一种常见方法是将待更新的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, id2, id3});
虽然MySQL对in操作有一定的优化，但在面对大量数据时，效率依然不理想。
解决方案：分批更新与索引优化
经过与DBA的多次沟通，最终确定了以下的优化策略：
- 使用
/*!40001 SQL_NO_CACHE */语法来避免缓存，防止本次查询影响buffer pool。 - 强制使用主键索引
FORCE INDEX(PRIMARY)，并按主键顺序进行排序。 - 分批更新数据，使用已排序的主键范围进行批量更新。
优化后的SQL示例如下：
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}";
通过这种方式，可以避免影响缓存数据，同时对数据库主从同步的性能产生较小影响。在执行批量更新时，可以通过接口来控制更新速率，动态调整刷库的速度，以保障数据库的正常运行。
其他注意事项
如果业务使用UUID作为主键而非自增ID，数据的顺序性将无法保证。对此，建议在数据入库时提前处理，代码上线后再进行全量数据更新。
通过这些优化措施，可以有效地解决千万级数据表的全表更新问题。