如何实现类Tally的库存估值报表优化？求解性能瓶颈方案

一、替代方案：用MySQL窗口函数直接生成报表 #

完全不需要通过PHP逐行遍历历史数据，直接利用MySQL的窗口函数在数据库层面完成累计计算，生成目标报表。核心思路是按SKU分区、按交易时间排序，通过SUM() OVER()窗口函数实时计算到每个时段的期末库存和估值，避免从头遍历全量历史数据。

示例SQL（适配常见表结构） #

假设stock_transactions表包含sku_id（SKU编号）、transaction_date（交易日期）、quantity（变动数量）、unit_cost（单位成本）字段，SQL语句如下：

SELECT
    sku_id,
    DATE_FORMAT(transaction_date, '%Y-%m') AS period,
    -- 累计计算期末库存数量
    SUM(quantity) OVER (PARTITION BY sku_id ORDER BY transaction_date) AS ending_qty,
    -- 累计计算期末库存估值
    SUM(quantity * unit_cost) OVER (PARTITION BY sku_id ORDER BY transaction_date) AS ending_value
FROM stock_transactions
-- 按需指定报表时间范围
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY sku_id, period
ORDER BY sku_id, period;

如果需要任意时段的期末值，只需调整WHERE子句的日期范围即可，窗口函数会自动从该SKU的第一条记录开始累计到时段末尾，无需额外的PHP循环计算。

二、表结构优化建议（可选，进一步提效） #

• 添加复合索引：创建(sku_id, transaction_date)的复合索引，能大幅提升窗口函数的分区、排序效率，数据量越大效果越明显：

  CREATE INDEX idx_sku_transaction_date ON stock_transactions(sku_id, transaction_date);
  

• 预生成库存快照表（高频查询场景）：如果报表查询频率极高，可定时生成库存快照表，存储每个SKU在固定时段（如每月末）的期末库存和估值。查询时直接读取快照表，彻底避免实时计算开销。
  快照表示例结构：

  CREATE TABLE stock_snapshots (
      sku_id INT NOT NULL,
      period CHAR(7) NOT NULL, -- 存储格式如"2023-12"
      ending_qty INT NOT NULL,
      ending_value DECIMAL(12,2) NOT NULL,
      PRIMARY KEY (sku_id, period)
  );
  

  可通过MySQL事件或PHP定时任务，每月初自动计算上月的期末值并插入快照表。

三、方案优势对比 #

• 性能飞跃：将计算逻辑从PHP转移到MySQL，利用数据库的批量处理优化能力，避免PHP循环带来的IO和内存开销，数据量上万条时性能差距尤为显著。
• 灵活性提升：通过调整SQL的日期条件，可快速生成任意时段的报表，无需从头遍历所有历史记录。

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