交易卡数据库拆分表与单表查询的性能及架构合理性问询

一、拆分Historic/Current表 vs 保留AllCards的isSold字段 #

你的核心诉求是解决select someColumns from AllCards where isSold = False的全表扫描性能问题，我们从性能和架构两方面对比两种方案：

性能维度 #

• 拆分表方案：
  若未售出卡片占比极低（比如不足总数据量的5%），Current表行数远小于AllCards，关联查询select ac.someColumns from Current c join AllCards ac on c.CCardID = ac.cardID会明显快于全表扫描。但如果未售卡片占比不低（比如超过30%），Current表行数和原AllCards中isSold=False的数据量相当，此时多一次join操作反而增加开销，性能未必优于加索引的原表。
  另外，拆分表会提升写操作成本：售出卡片时需执行「删除Current行 + 插入Historic行」两个操作，比原方案仅更新isSold字段的单写操作更耗时，还可能引发事务一致性风险（比如删了Current但未成功插入Historic）。

• 保留isSold字段方案：
  给isSold字段加上联合索引（比如isSold + someColumns，覆盖查询所需字段），原查询就能直接走索引，完全避免全表扫描，性能可达到甚至超过拆分表的效果，还省去了join的开销。

架构维度 #

• 拆分表将已售/未售数据物理隔离，逻辑上更清晰，但增加了表的数量，后续的 schema 变更、关联查询维护都会更复杂。
• 保留isSold字段的方案表结构简单，数据集中，维护成本低，状态切换逻辑也更直观（仅需更新字段）。

结论 #

绝大多数场景下，优先选择给isSold加联合索引的方案，性能足够且架构更简洁。只有当未售卡片占比极低、查询频率极高，且索引优化无法满足性能要求时，才考虑拆分表。

二、Week表存储本周购买记录 vs 直接查询AllCards #

同样从性能和架构两方面分析：

性能维度 #

• Week表方案：
  由于仅存储本周数据，表的行数极少，关联AllCards查询时过滤速度极快，远优于在数百万行的AllCards中按时间范围（比如where purchaseTime between '本周一' and '本周日'）查询——尤其是当AllCards的purchaseTime索引不够高效（比如索引字段不覆盖查询列，需要回表）时，性能提升更明显。

• 直接查询AllCards方案：
  若给purchaseTime加了合适的联合索引（包含查询所需字段），且每周购买数据占总数据量的比例不高，查询性能也能接受，但每次查询都需要计算时间范围，执行计划的效率略低于Week表的关联查询。

架构维度 #

• Week表把高频查询的本周数据单独隔离，业务逻辑更清晰，代码中无需重复计算时间范围，可读性更好。但需要额外维护数据生命周期：比如每周定时清空或归档Week表的旧数据，否则表会逐渐膨胀，失去性能优势。同时，购买卡片时需同时写入AllCards和Week表，要保证事务一致性，否则会出现数据不一致的情况。
• 直接查询AllCards的方案无需额外维护，表结构简单，但查询语句会包含时间范围逻辑，略繁琐。

结论 #

如果用户本周购买记录的查询频率很高，且AllCards按时间查询的性能无法满足需求，Week表方案值得采用，但要做好数据同步和定期清理。如果查询频率不高，或者AllCards的时间索引已经足够高效，直接查询原表更省心，避免额外的维护成本。

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