Optimizing Full‑Table Updates in MySQL with Row‑Based Binlog: Strategies and Best Practices

When a business needs to modify data across an entire MySQL table, a simple UPDATE works for small (tens of thousands) rows, but on tables with hundreds of millions of rows it creates massive binlog traffic, especially with row‑based replication, causing severe pressure on the master and slave servers.

The binlog formats are:

Format

Content

statement

Logs each SQL statement; low log volume but functions like

random()

may cause issues.

row

Logs every row change; can generate huge logs for bulk DELETE/UPDATE.

mixed

Combines both; usually statements for simple DML and rows for functions.

Because the production MySQL cluster uses row‑based binlog, a full‑table UPDATE on a table with billions of rows would flood the master’s binlog and block the slave during replication, making a direct UPDATE infeasible.

Direct UPDATE Problem

We needed to replace http:// with https:// in a user image column for a table of several tens of millions of rows. The naive approach was:

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

Deep Pagination Issue

Attempting to batch the work with LIMIT offsets leads to “deep pagination” where MySQL must traverse the B‑tree leaf nodes for each offset, resulting in near‑full‑table scans and poor performance.

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

Using IN Clause

Fetching a batch of IDs first and then updating with an IN list also proved inefficient despite MySQL’s index predictions.

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

Final Version

After discussions with DBAs we adopted a script that uses /*!40001 SQL_NO_CACHE */ to avoid caching the query pages in the InnoDB buffer pool and forces the primary index for deterministic ordering. The workflow is:

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

The SQL_NO_CACHE hint ensures the query does not pollute the buffer pool with cold data, while FORCE INDEX(PRIMARY) guarantees the optimizer uses the primary key index, allowing a range scan on id instead of a full scan.

By exposing this logic through an API, we can throttle the update rate, monitor master‑slave replication lag, IOPS, and memory usage, and optionally run multiple threads via a thread pool to increase throughput while keeping the system stable.

Other Considerations

If primary keys are generated with Snowflake or auto‑increment, the sequential order aids this approach; for UUID primary keys, a pre‑processing step is required to replace data before the bulk update.