优化MES生产项的DynamoDB表：AppSync模型设计咨询

一、核心索引设计（避免全表扫描） #

1. 针对「生产日期区间查询」的全局二级索引（GSI） #

将productionDate设为GSI的排序键，用固定值（比如ALL_ITEMS）作为分区键，利用DynamoDB对排序键的范围查询能力，直接命中索引获取目标区间的物料。

修改后的模型定义：

type Item @model @key(name: "ByProductionDate", fields: ["prodDatePartition", "productionDate"]) {
  id: ID!
  ref: String
  projectID: ID
  location: String
  productionDate: AWSDate
  prodDatePartition: String! # 固定值，例如"ALL_ITEMS"
}

对应的查询示例：

query GetItemsByProductionDate {
  listItemsByProductionDate(
    prodDatePartition: "ALL_ITEMS"
    productionDate: { between: ["2024-01-01", "2024-01-31"] }
  ) {
    items {
      id
      ref
      location
      productionDate
    }
  }
}

2. 针对「位置+生产日期区间查询」的全局二级索引（GSI） #

把location设为GSI的分区键，productionDate设为排序键，先定位指定位置的物料集合，再通过日期范围筛选结果，完全避免全表扫描。

补充索引后的模型：

type Item @model 
  @key(name: "ByProductionDate", fields: ["prodDatePartition", "productionDate"])
  @key(name: "ByLocationAndDate", fields: ["location", "productionDate"]) {
  id: ID!
  ref: String
  projectID: ID
  location: String! # 设为非空，确保索引能覆盖所有物料
  productionDate: AWSDate
  prodDatePartition: String!
}

对应的查询示例：

query GetItemsByLocationAndDate {
  listItemsByLocationAndDate(
    location: "LOCATION_X"
    productionDate: { between: ["2024-01-01", "2024-01-31"] }
  ) {
    items {
      id
      ref
      productionDate
    }
  }
}

二、处理频繁变更的字段（productionDate/location） #

不需要删除重写物料条目：

• DynamoDB的GSI会异步同步字段变更，对于MES原型场景，几秒内的延迟完全可接受
• 如果需要强一致性，查询时可添加consistentRead: true参数（Amplify自动生成的查询支持该配置）
• 若location可能为空，设置默认值（比如UNASSIGNED），避免索引忽略空值物料

三、历史数据归档优化（productionDate<今日） #

无需通过删写降低历史数据可访问性，改用以下方式：

1. 新增字段isActive（布尔值，默认true），并添加对应GSI
2. 用Amplify的Lambda触发器（@function），每日自动将productionDate<今日的物料isActive设为false
3. 常规查询默认只获取isActive: true的物料，历史数据单独通过isActive: false筛选

补充归档逻辑后的模型：

type Item @model 
  @key(name: "ByProductionDate", fields: ["prodDatePartition", "productionDate"])
  @key(name: "ByLocationAndDate", fields: ["location", "productionDate"])
  @key(name: "ByActiveStatus", fields: ["isActive", "productionDate"]) {
  id: ID!
  ref: String
  projectID: ID
  location: String!
  productionDate: AWSDate
  prodDatePartition: String!
  isActive: Boolean! # 默认值设为true
}

四、主键策略优化 #

保持id作为唯一主键（使用Amplify自动生成的UUID），修改productionDate或location时仅需更新条目，避免删写操作带来的额外开销和数据风险。

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