Knex/MySQL如何分块高效更新千万级超大表

1. SELECT单批拉取行数最优配置 #

• 常规场景基准值：4核8G规格实例、表单行平均大小1KB以内、无text/blob大字段的场景，单批拉取5000~20000行为最优
• 边界规则：
  • 不要低于1000行/批：网络IO往返、SQL解析、连接调度的固定开销占比会超过30%，整体执行效率极低，千万级表跑完要几十个小时
  • 不要高于50000行/批：一是MySQL服务端会把整批结果集加载到内存，容易挤占InnoDB缓冲池的热数据空间，影响线上业务查询；二是Node端Knex拿到大结果集做计算时，容易突破默认堆内存限制触发进程OOM，容错直接失效；三是单批查询时间超过1s后，碰到长事务kill、MDL锁等待的概率陡增，整批失败的重试成本极高
  • 场景化调整：如果表单行平均大小超过4KB（带长文本、JSON大字段），把批大小压到1000~3000行；如果用只读从库拉取数据、完全不影响线上业务，且实例内存在16G以上，最多可以把批大小提到30000行，再高没有收益
• 必做优化：拉取必须按主键有序翻页，不要用LIMIT offset分页，固定写法为

  SELECT id, [计算新value依赖的字段列表] FROM 表名 WHERE id > ? ORDER BY id ASC LIMIT ?
  

  这种写法全程走主键索引顺序扫盘，性能是offset分页的几十倍，也不会因为表数据增删出现漏拉、重复拉的问题。

2. UPSERT单批写入更新行数最优配置 #

• 常规场景基准值：1000~5000行/批为最优，不要和拉取批大小强绑定，拉取的大批次要拆成小批次写入
• 边界规则：
  • 不要低于500行/批：单条upsert的SQL解析、事务提交、binlog刷盘固定开销占比过高，写入速度上不去，还会增加磁盘IO次数
  • 不要高于10000行/批：一是单条SQL的包大小很容易触发max_allowed_packet参数限制直接报错；二是单事务持行锁时间过长，会扩大间隙锁范围，提升死锁概率，还可能阻塞同表的线上正常写入；三是单批失败后的回滚成本极高，大事务回滚可能占用满CPU导致实例卡几秒到几十秒。最优状态是单批upsert执行时间稳定在200ms~500ms，超过1s必须调小批大小
• 必做优化：每批upsert单独开短事务，不要给整表更新开长事务，批处理失败只需要重试当前小批次，不需要回滚全量进度，容错成本最低。Knex操作时注意每批写完及时释放连接，不要占满连接池。

3. 数据库配置调整建议与可行性 #

• 绝大多数场景不需要升配：只要按上面的规则设置批大小，4核8G的常规MySQL实例跑千万级表更新，CPU峰值不会超过30%，内存占用不会超过实例规格的40%，对线上业务的影响可以控制到最低
• 可以临时调整的场景：
  • 如果必须在业务高峰跑任务，且实例日常CPU负载已经超过60%，可以临时提升CPU规格到8核，任务跑完后即时降配，目前云数据库的规格变配基本是秒级生效，按小时计费成本极低
  • 如果表带较多大字段，可以临时把innodb_buffer_pool_size参数调整到实例物理内存的70%，该参数支持动态生效不需要重启实例，不需要额外升内存
• 禁止调整的参数：不要为了追求速度把sync_binlog、innodb_flush_log_at_trx_commit改成非安全值，否则实例宕机会直接丢数据，完全失去容错能力。

4. 额外效率与容错优化 #

• 每批处理完成后，把当前处理到的最大主键ID持久化到本地或Redis，进程崩溃、服务重启后直接从上次记录的位置继续跑，不需要从头开始
• SELECT时只拉取计算新value必须的字段+主键，不要拉全字段，减少网络传输和内存占用
• Knex连接配置合理的超时时间，建议设置timeout: 30000，避免慢查询占满整个连接池
• 多实例多表批量更新时，同一时间并行跑的表任务不要超过2个，避免磁盘IO被打满。

内容的提问来源于stack exchange，提问作者Tzachi