多列数据库表拆分后的查询性能对比及优化方案咨询

我拥有结构和数据完全一致的两个版本数据库：一个用于网站（基于SQL Server与VB.Net），另一个用于Android应用（基于Xamarin与SQLite）。现有一张表存储对象数据（每行对应一个对象），包含超100个关联字段（如Length、Weight、Color等），但并非所有对象的所有字段都有值，实际存在大量Null值。计划将该表拆分为三张新表：

• 表1：存储始终存在的主属性（ObjectID、Name、Barcode等）
• 表2：存储特征信息（FeatureID、FeatureName）
• 表3：存储对象与特征值的关联关系（ObjectID、FeatureID、FeatureValue）

咨询以下问题：

1. 拆分后的查询性能是否与原表一致？
2. SQL Server与SQLite在该场景下存在差异吗？
3. 是否有其他优化方案？

1. 拆分后的查询性能对比 #

拆分后的性能不会和原表完全一致，具体差异取决于查询场景：

• 优势场景：
  • 查询仅需部分特征时：原表要扫描整行（即使大部分字段为Null），拆分后只需关联主表和对应特征的关联表数据，数据量更小，扫描更快。
  • 新增/修改特征时：无需修改表结构，仅需在特征表新增条目，避免了宽表ALTER TABLE的开销（尤其是SQL Server这类大型数据库，表结构变更的锁表成本很高）。
• 劣势场景：
  • 需要查询对象全量特征时：原表是单表查询，拆分后需要三次表关联（主表+特征字典表+关联表），如果索引设计不到位，会比原表单表查询慢。
  • 小数据量场景：如果总对象数和特征数都很少，单表的简单查询可能反而比多表关联更快，因为多表关联带来的额外IO和连接开销会超过Null存储的冗余成本。

2. SQL Server与SQLite的场景差异 #

两者在这个拆分方案下的表现有明显差异：

• SQL Server：
  • 擅长处理多表关联，查询优化器对复杂Join的处理能力强，只要给关联字段（ObjectID、FeatureID）建立合适的聚集/非聚集索引，多表查询的性能损耗可以控制在很低的水平。
  • 宽表的Null存储支持稀疏列优化，原宽表如果用稀疏列定义那些大量Null的字段，存储效率接近拆分后的结构，此时拆分的性能收益会降低。
• SQLite：
  • 作为嵌入式数据库，多表关联的开销比SQL Server高，尤其是在Android设备上，IO性能有限，复杂Join的延迟会更明显。
  • SQLite没有稀疏列特性，宽表的Null会占用实际存储空间，拆分后能显著减少磁盘占用，对移动端的存储压力和查询IO更友好。
  • SQLite的查询优化器对小数据量的处理更高效，但数据量增大后，多表关联的性能下降比SQL Server快。

3. 其他优化方案 #

除了拆分表之外，还可以根据场景选择以下优化方式：

• 针对SQL Server的优化：
  • 给原宽表的大量Null字段设置为稀疏列，既保留单表查询的便利，又减少Null值的存储开销。
  • 采用列存储索引：如果以分析类查询为主（比如统计不同特征的分布），列存储对稀疏数据的压缩和查询效率远高于行存储。
• 针对SQLite的优化：
  • 对于移动端常用的查询场景，提前预计算视图，将常用的主属性+高频特征关联结果生成视图，减少查询时的Join操作。
  • 使用WITH RECURSIVE构建临时数据集，针对特定对象的全量特征查询，减少多表扫描的次数。
• 跨平台通用优化：
  • 对拆分后的关联表（表3）建立复合索引(ObjectID, FeatureID)，同时给FeatureID建立单独索引，兼顾按对象查特征和按特征查对象的场景。
  • 对于频繁查询的特征，可以考虑在主表（表1）中保留几个高频字段，避免每次都要关联查询，平衡单表便利和存储冗余。

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