ASP.NET Core MVC+EF框架下仓库库存统计优化及交易表归档方案咨询

• 你的核心优化方向是对的，新增结存表确实能大幅降低库存统计的耗时，但是有两个小误区需要修正：
  1. Transaction是出入库原始凭证，绝对不能删除，哪怕有了结存表也要留着所有日志，后续对账、排查问题都要用到，不存在“避免Transaction表无限增长”的说法，30万条数据对于关系型数据库来说完全是小体量，不需要担心存储不够。
  2. 你当前的库存查询写法有冗余，两次扫Transaction表的操作可以合并为一次，再加上索引优化，哪怕不新增结存表，单个工具的库存查询也能做到毫秒级响应。

第一步：先做零改造成本的基础优化 #

1. 先给Transaction模型补全显式外键字段，方便后续配置索引：

public class Transaction
{        
    public long Id { get; set; }        
    public string FormId { get; set; }               
    public DateTime Date { get; set; }     
    public bool IsInput { get; set; }                 
    public float Quantity { get; set; }        
    // 新增显式外键，EF Core之前已经自动生成了隐式字段，补全不会影响现有数据
    public long ItemId { get; set; }
    public Tool Item { get; set; }           
    public string Description { get; set; }
}

2. 给Transaction表配置联合索引，字段顺序为ItemId、Date、IsInput、Quantity，索引配置后所有库存相关的查询都会直接走索引，不需要扫全表，EF Core的配置代码示例：

// 在DbContext的OnModelCreating方法里添加
modelBuilder.Entity<Transaction>()
    .HasIndex(t => new { t.ItemId, t.Date, t.IsInput, t.Quantity });

3. 优化现有库存查询写法，把两次查询合并为一次：

// 单个工具库存查询
var stock = dbContext.Transactions
    .Where(t => t.ItemId == 目标工具ID)
    .Sum(t => t.IsInput ? t.Quantity : -t.Quantity);

第二步：新增结存表实现长期性能保障 #

1. 新建结存表对应的模型类：

public class ToolStockBalance
{
    [Key]
    public long Id { get; set; }
    // 结存周期的截止时间，比如月度结存就存当月最后一天的23:59:59
    public DateTime BalanceEndDate { get; set; }
    public long ToolId { get; set; }
    public Tool Tool { get; set; }
    // 截止到BalanceEndDate时的工具库存
    public float BalanceQuantity { get; set; }
    public string? Remark { get; set; }
}

2. 新增结存计算逻辑：
   • 每个盘点周期（比如每月末/每季度末）跑一次结算任务，批量计算所有工具截止到当期结束时的总库存，写入ToolStockBalance表
   • 后续查库存时，逻辑改为：最新一次结存的数量 + 结存结束日期之后所有出入库的差值，示例代码：

// 先查最新的结存记录
var latestBalance = dbContext.ToolStockBalances
    .OrderByDescending(b => b.BalanceEndDate)
    .FirstOrDefault();

// 计算结存之后的出入库差值
var afterBalanceChange = dbContext.Transactions
    .Where(t => t.ItemId == 目标工具ID && t.Date > latestBalance.BalanceEndDate)
    .Sum(t => t.IsInput ? t.Quantity : -t.Quantity);

// 最终库存
var finalStock = latestBalance.BalanceQuantity + afterBalanceChange;

3. 如果后续Transaction表数据量确实太大，也可以把结存过的历史日志迁移到单独的归档表，只留最近一个周期的日志在主表，完全不影响其他业务表。

• EF Core 索引配置
• EF Core 迁移（用来新增表和索引）
• .NET 后台定时任务（IHostedService / Hangfire，用来自动跑周期结存任务）
• 关系型数据库表归档

整个方案只需要新增索引和新增结存表，不会修改、删除任何现有业务表的数据，完全符合你的要求。

内容的提问来源于stack exchange，提问作者Vahid.M