按二值标记区分数据：单表加字段与分两表的优劣及行业实践咨询

两种方案优缺点 #

方案一：单集合/单表新增标记字段 #

• 优点：
  • 维护成本低：仅需维护一套表结构，后续如果标记位新增枚举值，不需要新增表，仅调整字段取值范围即可
  • 跨类型查询简单：需要同时统计两类数据、或者关联查询两类记录时，直接查单表即可，无需做union操作
  • 写入逻辑简单：新增/更新记录时不需要判断标记位切换表，标记位变更时也不需要处理跨表数据迁移和事务问题
• 缺点：
  • 单表膨胀快：如果两类记录量级都很高，单表数据量过大后，分库分表/分片的运维成本更高
  • 定向查询需加过滤条件：仅查询某一类标记的记录时，每次都要带上标记位过滤条件，需要额外给标记位建索引才能保证效率，索引本身会占用存储
  • 关系型数据库场景下如果两类记录字段差异大，会产生大量空置字段，浪费存储空间

方案二：拆分两个集合/表分别存储两类记录 #

• 优点：
  • 单表数据量小：每张表仅存一类数据，查询时底层扫描的天然范围更小
  • 可做差异化优化：两类数据如果访问频率、存储周期差异大，可以分别设置不同的存储介质、索引规则、缓存策略，比如冷数据存在低成本归档存储，热数据开高频缓存
  • 定向查询逻辑简单：不需要额外加标记位过滤条件，直接查对应表即可
• 缺点：
  • 维护成本高：两套表结构需要同步迭代，后续标记位新增枚举值就要新增对应表，结构调整成本高
  • 跨类型查询性能差：需要同时查两类数据时必须做union操作，查询复杂度高，性能也会受影响
  • 标记位变更处理复杂：某条记录的标记位变化时，需要做跨表的删增操作，还要保证事务一致性，实现成本很高

常见疑问解答 #

拆分表是否存储空间成本更低？ #

不一定，绝大多数场景下反而不会更省。标记位本身的存储成本极低，布尔型或者tinyint类型仅占1字节，1000万条记录的总存储消耗不到10MB，和整个表的存储量相比几乎可以忽略。反过来拆分表需要存储两套完全相同的表元信息、索引结构，反而会增加额外的存储开销。
只有在以下两种极端场景下才会省存储：一是MySQL这类关系型数据库中，两类记录的字段重合度极低，单表存储会产生大量空置字段；二是其中一类数据完全不需要建和另一类相同的索引，可以减少索引存储成本。

拆分表是否查询性能更快？ #

定向查询单类数据的场景下大多有优势，但不是绝对的。如果单表场景下已经给标记位建了合适的索引，且两类数据的量级差距不大，加标记位过滤的查询性能和分表的差距非常小。只有当单表数据量已经达到千万甚至亿级、查询扫描的范围极广时，分表天然的小数据量优势才会明显体现。
如果你的业务经常需要同时访问两类数据，分表需要做union操作，反而会让性能比单表差很多。

行业通用实践 #

绝大多数业务场景下优先选择单表加标记字段的方案，只有同时满足以下几个条件时才会考虑分表：

• 两类记录的业务属性差异极大，字段重合度极低，单表存储的空置字段浪费严重
• 两类数据的访问频率、存储周期、性能要求差异极大，需要做完全差异化的运维优化
• 单类数据的量级已经达到千万级以上，单表存储已经出现明确的性能瓶颈，分表可以大幅降低单表运维成本

内容的提问来源于stack exchange，提问作者Bear Bile Farming is Torture