如何扩展MySQL表存储历史数据且不增大主表占用空间

你设计的最新主表+全量历史表的双表架构是当前场景下的成熟可行方案，本身没有逻辑问题，也是行业内的主流选择之一，核心优势是查询最新数据的性能极高，主表规模稳定和用户量级对齐（1000万行），不会随提交次数增长膨胀。可以做两点优化提升可靠性和性能：

• 新增提交的操作用事务包裹，避免历史表插入成功、主表更新失败导致的数据不一致，事务隔离级别选择REPEATABLE READ即可满足需求
• 主表将user_id设为唯一主键/唯一索引，更新时直接用INSERT ... ON DUPLICATE KEY UPDATE语法，省去先查后判断插入/更新的步骤，减少一次数据库查询开销

如果你的业务场景符合特定特征，可以选择以下适配性更高的方案：

单表联合索引方案 #

仅用一张表存储全量提交记录，新增submit_time字段存储提交时间，给(user_id, submit_time DESC)建联合索引。查询单用户最新提交时直接取索引第一条即可。

• 优势：无需维护双表数据一致性，查询用户全量历史提交无需跨表
• 劣势：单表体积会随提交次数持续膨胀，查询最新数据的性能略低于双表方案，适合单用户平均提交次数≤5次的场景
• 可选优化：新增is_latest标记字段，每次新提交时同步把该用户旧记录的is_latest设为0、新记录设为1，查询最新数据时可以直接按user_id + is_latest=1走索引，性能接近双表方案

冷热数据分离方案 #

如果超过3个月的历史数据仅用于离线分析、不会被在线业务访问，可以在双表方案的基础上增加归档逻辑：定时任务每天批量导出超过3个月的历史数据到低成本存储介质（对象存储、离线数仓等），导出后删除在线库的对应记录，大幅降低在线数据库的存储成本。

大部分C端问卷、用户资料更新、收货地址管理这类「需留存全量历史、高频访问最新版本」的业务场景，绝大多数都会采用你最初设计的双表方案，核心原因有三个：

• 逻辑简单易维护，后续迭代、排查问题的成本极低
• 性能完全可控，主表规模稳定在和用户量级持平，查询最新数据为主键查询，延迟稳定在毫秒级
• 历史表可以单独做分区、归档优化，完全不影响核心业务的主表访问

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