SQL Server 2019 大记录行获取性能影响及表设计最佳方案咨询

你担心的「就算只选部分列，大行长也会影响性能」的情况确实存在，因为SQL Server的存储以8KB的数据页为最小IO单位，哪怕你只读取一行的某2个字段，也需要把这一行所在的整个数据页加载到内存中。如果单行平均大小2KB，一个数据页只能放3行左右，同等数据量下的IO开销会是单行大小100B（仅存基础字段）的十几到几十倍，这个影响和你select多少列没有关系。

方案1：单宽表 #

仅适用于超过20%的查询需要用到至少1个可选属性的场景，必须做对应优化避免性能损耗：

• 所有可选字段设置为稀疏列：SQL Server的稀疏列对NULL值的存储开销几乎为0，如果大部分行的可选字段都是空值，实际平均行大小会降到和仅存基础字段差不多的水平，完全抵消宽表的IO劣势。
• 针对高频基础字段查询，新建非聚集覆盖索引：CREATE NONCLUSTERED INDEX IX_tblPerson_Base ON tblPerson(ID) INCLUDE(Name, Surname)，这类查询会直接走索引，不需要访问主表数据页，完全不受可选字段大小影响。
• 如果可选字段存在大量大长度可变类型，超过8KB的部分会自动存入行溢出页，主数据页仅存指针，也会降低对基础查询的影响。

方案2：垂直分表 #

适用于**可选属性的查询占比低于5%**的场景，直接拆分为两张表：

• 主表tblPerson仅存ID、Name、Surname三个必填字段，单行极小，单数据页可存储上百行，基础查询IO效率拉满。
• 扩展表tblPerson_Ext存储ID + 所有可选属性，和主表是主键1:1关联，只有需要查询可选属性时才做join，主键join的性能损耗极低，几乎可以忽略。
  这个方案不需要额外适配SQL Server特性，兼容性最高，后期维护成本也低。

第三种更优方案：稀疏列 + 列集（SQL Server 2019专属，最适配你的需求） #

你用的SQL Server 2019完全支持这个特性，可以同时兼顾宽表免join和分表高性能的优势：

• 所有可选字段设为稀疏列，额外新增一个COLUMN_SET AllOptionalAttrs XML类型的列，所有稀疏列会自动以XML格式聚合到这个列中。
• 优势：
  • NULL值几乎不占存储空间，主表数据页的行数和垂直分表的主表几乎一致，基础字段查询性能没有损失。
  • 查询个别可选字段时直接写对应列名即可，不需要join操作。
  • 需要批量读取所有可选属性时直接查询AllOptionalAttrs列，不需要罗列大量字段名。
  • 后续新增可选属性直接加稀疏列即可，不需要调整业务逻辑的关联关系。
• 限制：稀疏列总数不能超过1024个，绝大多数业务场景都可以满足。

最终选择建议 #

• 可选属性查询频率高，选稀疏列+列集方案
• 可选属性查询频率极低，选垂直分表方案
• 不建议使用无任何优化的普通宽表，会导致基础查询的IO成本大幅上升。

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