Laravel如何高效为百万级MySQL用户表批量更新thirdparty_id

现有百万级数据量的users表，包含id、competed_date、thirdparty_id等字段，其中thirdparty_id为新增字段，需要为全量用户填充值。
填充规则如下：

• 对每个用户，查询关联的订单表，取该用户最新创建的订单金额
• 基于订单金额通过自定义公式计算得到rate值
• 到thirdparty表中匹配满足start_rate > rate 且 end_rate < rate条件的记录id，即为需要填充的thirdparty_id
  目前单用户的计算更新逻辑已调试完成，基于Laravel框架开发，原有逻辑是查询所有thirdparty_id is null的用户，逐个调用计算函数更新，但一次性拉取百万级数据存在性能风险，需要解决两个问题：

1. 采用limit分批查询方式时，单批可设置的合理limit最大值是多少
2. 有哪些其他方案可以高效完成百万级数据更新
   原有代码逻辑如下：

-- 拉取待更新用户
select id as userid from users where thirdparty_id is null limit {some limit}

-- 遍历每个用户执行以下操作
-- 1. 查询用户最新订单金额
select amount from orders where user_id = userid order by created desc limit 1
-- 2. 基于金额通过自定义公式计算rate
-- 3. 匹配对应的第三方id
select id from thirdparty where start_rate > rate and end_rate < rate
-- 4. 更新用户字段
update users set thirdparty_id=id where id=userid

没有通用的固定数值，结合常规PHP+MySQL生产环境配置，单批limit的合理区间在1000~5000条，上限不建议超过10000。
判断逻辑很直接：

• 单批条数过少（如低于100）：大量开销浪费在数据库连接、网络IO上，整体执行效率极低
• 单批条数过多（如超过10000）：Laravel ORM会将查询结果全量加载到进程内存，极易触发PHP内存上限，同时单批执行时间过长会产生慢查询、长事务，挤占线上业务的数据库资源
  实操时无需死卡数值，可先从1000条开始压测，观察数据库CPU、内存占用以及PHP进程的内存消耗，若资源余量充足可逐步上调批次大小，只要不出现慢查询、内存告警即可。另外不要用limit N offset M的写法做分批，offset到数十万条后深分页性能会急剧下降，建议改成基于主键id的游标分页：每次记录当前批处理到的最大用户id，下一批查询条件加where id > 上次处理的最大id，查询性能全程稳定。

现有代码的核心性能瓶颈是N+1查询，单条用户处理要执行3次SQL，百万用户对应300万次数据库请求，效率极低，可根据业务场景选择以下优化方案：

• 优化现有分批逻辑，消除N+1查询
  首先thirdparty表通常数据量不大，可在脚本启动时将所有rate区间规则全量加载到内存，计算完rate后直接在内存匹配对应id，省掉循环内查询thirdparty表的请求。
  其次不要逐用户查询最新订单，单批拿到用户id集合后，用一次关联查询拉取整批用户的最新订单金额，参考SQL：

  select o.user_id, o.amount 
  from orders o
  join (
      select user_id, max(created) as latest_created 
      from orders 
      where user_id in (/* 当前批的用户id集合 */) 
      group by user_id
  ) tmp on o.user_id = tmp.user_id and o.created = tmp.latest_created
  

  拿到整批用户的金额后，在内存中完成rate计算、thirdparty_id匹配，最后不要循环单条执行update，可拼接批量更新语句或使用Laravel的upsert方法一次更新整批数据。优化后单批处理仅需2~3次SQL请求，性能比原逐行查改方案提升数十倍。
• 计算逻辑下推到数据库，减少PHP层开销
  如果计算rate的自定义公式逻辑不复杂，可通过MySQL内置函数（加减乘除、条件判断等）实现，完全可以跳过PHP循环处理：
  1. 先创建临时表存储用户id和对应的thirdparty_id
  2. 写一条关联SQL，直接关联users、orders、thirdparty三张表，按规则计算出每个用户对应的thirdparty_id，批量插入临时表
  3. 最后用临时表关联users表做批量更新，每次更新1万_{5万条即可，避免大事务锁表。这种方案比PHP逐行处理快1}2个数量级。
• 异步队列平滑处理，不影响线上业务
  将待更新的用户按固定大小（如每批1000个id）拆分为任务，推送到Laravel队列，用多个后台worker进程消费。可灵活控制worker数量、单任务执行间隔，选择业务低峰期跑任务，就算中途进程中断，重启后队列也能从断点继续执行，不会重复处理也不会丢进度，适合对可用性要求高、不能停服的线上环境。

必做的基础优化 #

• 提前建好索引：orders表添加(user_id, created)联合索引，thirdparty表可根据查询情况添加start_rate、end_rate索引，users表的thirdparty_id字段也要加索引，索引建好后查询速度可提升数倍
• 尽量选择业务低峰期（如凌晨）执行更新脚本，避免在流量高峰运行大查询、大更新
• 不要开长事务跑全量更新，每批处理完及时提交，减少锁占用时间

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