基于FIFO规则的库存与发票数据匹配高效处理方案求助

你现有方案的性能瓶颈主要在单条循环查询+单条更新数据库，10万条数据会产生数十万次数据库IO交互，耗时自然很高，可按以下优先级优化：

1 优先用SQL批量计算（性能提升10~100倍） #

直接在数据库层面完成核销计算，完全避免PHP和数据库的频繁交互，10万条数据全量计算通常3秒内可完成：

1.1 全量核销计算逻辑（适配MySQL 8.0+支持窗口函数的版本） #

• 第一步：计算每个商品入库记录的累计入库量（按入库时间升序）

SELECT
    a.id,
    a.good_id,
    a.num,
    a.created_at,
    SUM(a.num) OVER (PARTITION BY a.good_id ORDER BY a.created_at ASC) AS cumulative_total,
    LAG(SUM(a.num), 1, 0) OVER (PARTITION BY a.good_id ORDER BY a.created_at ASC) AS prev_cumulative_total,
    b.total_invoice
FROM table_a a
LEFT JOIN (
    SELECT good_id, SUM(num_invoice) AS total_invoice FROM table_b GROUP BY good_id
) b ON a.good_id = b.good_id

• 第二步：根据累计量直接计算每条记录的核销结果，规则如下：
  • 若cumulative_total <= total_invoice：当前入库记录全量核销，invoice_number = num，current_number = 0
  • 若prev_cumulative_total < total_invoice < cumulative_total：当前入库记录部分核销，invoice_number = total_invoice - prev_cumulative_total，current_number = num - invoice_number
  • 若prev_cumulative_total >= total_invoice：当前入库记录未核销，invoice_number = 0，current_number = num
• 第三步：用UPDATE JOIN语法将计算结果一次性批量更新到C表，无需逐行操作。

1.2 增量核销优化（性能提升百倍以上） #

如果不需要每次全量重新计算，仅在新发票到账时做增量核销：

• 给C表加current_number > 0的过滤条件，每次仅处理未核销完成的入库记录
• 仅处理B表中未参与过核销的新发票数据，数据量直接降到现有量级的1%甚至更低

2 PHP层面优化（适配不支持窗口函数的低版本数据库） #

如果必须用PHP处理逻辑，可通过批量操作减少IO次数：

• 一次性按商品分组查出所有待核销的C表记录（按created_at排序），不要循环单条查询
• 内存中完成所有核销计算后，用CASE WHEN批量更新语法提交结果，每1000条记录提交一次：

UPDATE table_c
SET
    current_number = CASE id
        WHEN 1 THEN 0
        WHEN 2 THEN 3
        -- 其他记录的计算值
    END,
    invoice_number = CASE id
        WHEN 1 THEN 10
        WHEN 2 THEN 2
        -- 其他记录的计算值
    END
WHERE id IN (1,2,/* 本次更新的ID列表 */)

该方案可将数据库交互次数从10万次降到100次以内，速度提升数十倍。

3 索引优化 #

给C表添加(good_id, created_at)联合索引，查询单个商品的入库记录时无需全表扫描，查询速度可提升3~10倍。如果做增量核销，可再添加current_number索引，进一步过滤待处理数据。

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