SQL Server 2016大表场景下主键（PK）与列存储索引（CSI）的最优组合方案咨询

嘿，针对你在SQL Server 2016里遇到的这个4600万行大表的主键（PK）+列存储索引（CSI）组合问题，结合你的每日大规模删插需求，我来给你拆解下可行的方案和各自的优劣：

先明确你的核心场景 #

你的表有6列唯一标识第7列的持有百分比，每日要做大规模删插，同时大概率需要对持有百分比做分析类查询（比如聚合、分组统计）——这是我们选择方案的核心依据。

方案1：聚集主键（前6列）+ 非聚集列存储索引（NCCI） #

这是最适配你场景的组合，先讲为什么：

为什么这么选？ #

• 聚集主键用前6列：这6列是唯一标识，聚集B树索引的叶子节点就是实际数据，能让你在删插时快速定位目标行，同时主键的唯一性约束能保证不会插入重复的标识行，维护数据完整性。
• 非聚集列存储索引：专门用来优化分析类查询（比如对持有百分比求和、按前6列分组统计），列存储的高压缩率（SQL Server 2016里NCCI通常能做到10:1甚至更高的压缩比）能大幅降低IO开销，提升读性能。

优缺点 #

• 优点：
  • 聚集主键的B树结构对OLTP型的大规模删插友好，定位快、维护成本可控；
  • NCCI和聚集主键各司其职，分析查询和更新操作互不干扰，不会互相拖慢性能；
  • 批量插入时，NCCI的维护开销远低于聚集列存储，只要插入批次够大，效率很高。
• 缺点：
  • 会有额外的存储开销，因为要维护两个索引，但列存储的高压缩能抵消大部分；
  • 小批量插入时，NCCI可能会生成较多小行组，长期下来可能影响查询性能，但定期重组索引就能解决。

具体操作示例 #

-- 创建带聚集主键的表
CREATE TABLE HoldingTable (
    Col1 INT,
    Col2 DECIMAL(18,2),
    Col3 BIGINT,
    Col4 FLOAT,
    Col5 INT,
    Col6 VARCHAR(50), -- 假设其中一列是字符串类型
    HoldingPercentage DECIMAL(5,2),
    CONSTRAINT PK_HoldingTable PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5, Col6)
);

-- 创建非聚集列存储索引（按需选择包含的列，这里包含所有列）
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_HoldingTable ON HoldingTable 
(Col1, Col2, Col3, Col4, Col5, Col6, HoldingPercentage);

方案2：聚集列存储索引（CCI）+ 非聚集主键 #

这个方案适合读远多于写的场景，你的每日大规模删插需求下要谨慎选择：

为什么？ #

• 聚集列存储能做到极致的压缩率，分析查询性能拉满，但它的更新机制天生不适合大规模删插：删除是软删除（标记为删除），需要后台的tuple mover清理；插入如果是小批量会生成大量小行组，拖慢查询，即使是大批量插入，维护CCI的开销也远高于聚集B树。
• 非聚集主键用来保证6列的唯一性，因为CCI本身不支持唯一性约束，只能靠非聚集B树索引来实现。

优缺点 #

• 优点：存储成本最低，分析查询速度最快；
• 缺点：大规模删插性能极差，tuple mover可能跟不上清理节奏，导致磁盘被大量标记删除的行占用，长期下来会严重影响整体性能。

方案3：仅用聚集主键（不建列存储） #

这个方案只适合完全没有分析类查询的场景：

优缺点 #

• 优点：维护成本最低，只有一个聚集B树索引，删插操作效率最高，没有额外存储开销；
• 缺点：如果需要对持有百分比做聚合、分组等查询，4600万行的全表扫描或B树索引扫描会慢到无法接受。

方案4：仅用列存储索引（不建主键） #

绝对不推荐！

• 没有主键就无法保证6列的唯一性，会出现重复标识行，导致持有百分比数据混乱；
• 列存储索引本身不支持唯一性约束，没法替代主键的完整性校验作用；
• 大规模删插时，没有主键就无法快速定位行，只能全表扫描删除，性能灾难。

最终推荐方案 #

优先选择聚集主键（前6列）+ 非聚集列存储索引，如果你的分析查询只涉及前6列和持有百分比，那么NCCI只包含这些列即可，能进一步减少存储和维护开销。

额外小建议 #

• 每日更新尽量用批量操作（比如BULK INSERT或大批次的INSERT ... SELECT），能减少日志生成和索引碎片；
• 定期检查聚集主键的碎片率，超过30%就重建索引；NCCI如果有大量小行组，用ALTER INDEX ... REORGANIZE合并；
• SQL Server 2016里，NCCI支持批量模式查询，确保你的分析查询能用上这个优化。

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