先把你心里的疑问拆成三块讲,尽量通俗,不和之前的术语绕
第一块:SQL Server 主键、聚集索引,为什么容易搞混
你原来的理解:我就加一个自增 ID 当主键,仅此而已,别的不管 SQL Server 有一个独有的默认规则(这个就是坑的来源!)
-- 你写这句的时候,没指定 CLUSTERED / NONCLUSTERED
RecId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
✅ 默认行为:这个主键自动变成【聚集索引】 很多别的数据库不是这样,所以你用 Mongo、PG 的时候完全没有这个困扰。
聚集索引到底是干嘛的(大白话)
聚集索引 = 规定这张表的数据行在磁盘上怎么放、按什么顺序摆在一起。
- 一张表,数据只有一种摆放顺序,所以一张 SQL Server 表最多只能有 1 个聚集索引
- 非聚集索引:相当于单独的目录,里面存【索引字段 + 聚集键】,单独放,不改变原始数据摆放
- 堆表:没有聚集索引 → 数据随便堆着放,没有统一排序规则
举个例子:
- 聚集索引在 RecId(自增): 数据物理上按 RecId 从小到大排。新来数据 RecId 更大,直接加到最后一页,几乎不拆页,碎片稳。
- 聚集索引在 testtime(时间),但你会补录旧时间: 新行要插到中间位置 → 页拆分、碎片飙升,就是你老库的问题。
- 堆表(无聚集索引): 数据随便塞,没有顺序;索引只是目录,找不到连续的数据块。
重点:主键是约束(保证唯一、非空);聚集索引是存储组织方式。 这是两套独立东西,只是 SQL Server 默认把主键绑定成聚集索引! 主键 ≠ 聚集索引,只是默认绑定。
-- 写法1:主键 + 同时是聚集索引(默认,推荐你的场景)
CONSTRAINT PK_RecId PRIMARY KEY CLUSTERED(RecId)
-- 写法2:主键只是普通索引,表是堆(数据无序摆放)
CONSTRAINT PK_RecId PRIMARY KEY NONCLUSTERED(RecId)
第二块:PostgreSQL、MongoDB 有没有聚集索引?
✅ PostgreSQL
PG 默认没有聚集索引!! PG 的表默认是堆表(heap),主键默认是非聚集 B 树索引。 PG 里你建一个自增 id 主键:
- 只是建了一个 B 树索引,不会改变表数据物理存储顺序
- PG 有一个功能叫
CLUSTER,可以一次性把表按索引排序整理一次,但不是持续生效,后续插入新数据不会维持顺序,不是 SQL Server 这种原生聚集索引机制。
所以你在 PG 里面,只加 serial/bigserial 主键,完全不会遇到 SQL Server 这套聚集索引碎片、页拆分的烦恼,这就是你感觉 PG 简单的原因。
✅ MongoDB
MongoDB 的集合,默认就是聚集表(聚集索引) Mongo 强制:_id 是聚集索引! 每一条文档物理上就是按 _id 顺序存放。
_id类似你的 RecId,自动生成(ObjectId),递增特性;- 所以 Mongo 天然就是【聚集在自增类主键】的模式,写入追加,很稳;
- Mongo 没有 SQL Server 这种 “你可以关掉聚集索引变成堆” 的选择,集合一定以
_id为聚集键。
对比小结:
- Mongo:一定有聚集索引,固定绑定 _id
- PG:默认没有聚集索引,表是堆
- SQL Server:你可以选;主键默认自动变成聚集索引(最容易踩坑的地方)
第三块:回到你最开始朴素想法:
“我就想要一个自增主键 ID,不想折腾聚集索引,它到底对我有啥好处?”
结合你的 MES 时序采集、按 testtime 查时间段同步的场景:
- 如果你用 SQL Server,
RecId 聚集主键✅ 写入:新记录尾部追加,页拆分极少,碎片不容易爆炸(解决你之前 99% 碎片的根源) ✅ 清理数据:删 30 天前旧数据,清理带来的碎片更容易管控 ✅ 稳定性:整套结构和 Mongo 的_id思路是一样的,时序采集很适配 - 如果你强行:RecId 主键 NONCLUSTERED(堆表,你想要的 “不动聚集”) 表变回堆,和 PG 默认行为一样: ✅ 满足你:只是一个唯一主键,数据无序存放 ❌ 代价:更新产生转发记录(forwarded record),大批量时间范围查询性能波动,就是你报表里 Mylar_A、Mylar_B 那种堆表碎片问题
一句话帮你打通跨数据库感受:
Mongo 天生:聚集在 _id PG 默认:堆,主键只是普通索引 SQL Server:给了你选择权,但默认主键 = 聚集索引,很多人不知道这个默认规则,稀里糊涂踩坑(比如之前老库,聚集索引绑到了无序字段)
给你一个极简选择,不用再烧脑
你现在在 SQL Server,做 MES 时序采集: 👉 最简单稳妥方案:RecId 自增,主键 CLUSTERED(聚集),再加 IX_testtime 非聚集索引。 这套方案逻辑等价于 MongoDB 的设计,稳定性最好,刚好避开你之前遇到的大坑。
如果你想,我可以用一句对比,给你演示:同样一张 AOI 表,分别写成【堆模式(PG 风格)】和【聚集 RecId 模式(Mongo 风格)】两个完整 SQL。
💥💥💥💥💥💥💥
不是聚集索引本身解决碎片,是【选对递增的聚集键】,减少页拆分,从而不容易产生碎片。
拆开讲,很简单:
- 碎片来源 = 页拆分(page split) 一页数据满了,又要往这一页中间位置插一条新记录,SQL 把这页一半数据搬到新页 → 产生碎片。
- 如果聚集键是递增 RecId(bigint identity) 新记录的 RecId 永远最大,永远插到 B 树最后一页,几乎不用往中间插,页拆分极少 → 碎片很难涨起来。
👉 这就是你要的效果。
- 如果聚集键选的是无序的值(你老库的情况) 每次插入经常插到索引中间,频繁页拆分 → 碎片很快冲到 99%。同样是聚集索引,键选错了,反而疯狂造碎片。
- 对比堆表 堆表没有聚集索引,不会有这种 B 树页拆分; 但是堆表更新行会产生 转发行 forwarded record,是另外一种碎片(就是你看到 Mylar_A、Mylar_B 那种 99% 碎片)。
浓缩成一句口诀给你记
聚集索引是存储顺序; 递增聚集键,减少页拆分,抑制碎片; 无序聚集键,疯狂页拆分,碎片爆炸。
回到你的选型
你的场景:MES 采集表 ✅ RecId 自增,作为聚集主键:就是用递增聚集键,从源头压住页拆分、防止碎片快速累积。 再单独建 IX_testtime 非聚集索引给时间段查询。
要不要我给你区分:页拆分碎片 和 堆的转发行碎片,各自怎么清理?