MariaDB存储客户日终余额：多行少列与少行多列方案选型咨询

两种方案的优劣对比 #

方案1（按账户类型拆行存储） #

优势

• 扩展性极强：后续新增账户类型无需修改表结构，直接插入对应Account枚举值的行即可，不会出现表列数膨胀的问题
• 无冗余存储：仅用户实际持有的账户类型会生成记录，无需为未开通的账户存储空值或0值
• 适配复杂查询需求：如果后续出现按账户类型聚合、批量统计某类账户总余额等扩展需求，可直接基于Account字段过滤统计，无需修改SQL逻辑，业务适配灵活度高
• 可优化空间大：无需单独为Account字段建索引，改为创建(Name, Date, Account)联合索引，就能覆盖绝大多数查询场景，性能比单独建索引提升明显

劣势

• 数据总量大：5000万行的体量是方案2的5倍，相同Name+Date查询条件下，需要扫描的行数更多，IO开销更高，单查询性能弱于方案2
• 写入开销大：多出来的索引会提升写入时的维护成本，日终批量导入数据的耗时更长，额外索引也会占用更多存储空间
• 查询结果处理成本高：如果需要将同用户同日期的所有账户余额合并为一行返回，需要额外做行转列处理，SQL复杂度更高，也会增加额外的查询耗时

方案2（按账户类型拆列存储） #

优势

• 查询性能极高：仅1000万行数据，相同Name+Date查询条件下仅需扫描1行就能拿到该用户所有账户的余额，完全贴合当前所有查询都携带Name+Date的业务场景，查询速度远快于方案1
• 写入成本低：仅需维护(Name, Date)联合索引，写入时索引维护开销小，日终批量导入速度更快，整体存储空间占用更小
• 开发成本低：查询结果直接按行展示所有账户余额，无需额外做行转列处理，SQL写法简单，适配前端展示、报表导出等场景的效率更高

劣势

• 扩展性差：后续新增账户类型需要修改表结构加列，针对千万级大表执行DDL操作在MariaDB中属于重操作，容易锁表影响业务，长期来看如果账户类型持续增加，会出现列数过度膨胀的问题
• 少量冗余存储：用户未开通的账户类型需要存储0值或空值，不过当前仅15种账户类型的前提下，冗余占用的存储空间可以忽略
• 复杂查询适配性弱：如果后续出现按账户类型统计的需求，需要挨个指定列名编写SQL，新增账户类型时所有相关的统计SQL都要同步修改，维护成本极高

选型建议 #

• 若业务形态稳定，账户类型1~2年内无新增计划，且所有查询场景均为按Name+Date查询用户全量账户余额，优先选择方案2，性能更优、开发和维护成本更低
• 若业务迭代速度快，后续大概率会新增账户类型，或者存在按账户类型做统计分析的潜在需求，优先选择方案1，扩展性更强，长期改造成本更低

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