傻瓜友好、少操心、不用折腾聚集索引 / 碎片、日常几乎不用维护:PostgreSQL > MongoDB > SQL Server
偏向建表阶段就能规避的问题,不是事后调优,适合新建表的时候对照检查;重点覆盖你踩过的【聚集索引、碎片、页拆分、同步查询假丢数据】这类痛点 分大类:索引设计、字段设计、表存储结构、查询 & 业务写入模式、数据清理、其他隐性坑
一、索引类(你本次最大的坑区,SQL Server 重点)
- ✘ 聚集索引键选无序字段(testtime、条码、随机值)
- 后果:频繁页拆分、逻辑碎片快速拉到 99%,时间范围查询巨慢,同步超时,疑似漏数据
- ✔ 时序采集推荐:自增 ID(RecId)做聚集主键;Mongo 天然
_id就是这个思路
- ✘ SQL Server:不知道「主键默认是聚集索引」,稀里糊涂绑定错字段
- 后果:以为只是加个唯一主键,实则直接改了数据物理排序
- ✔ 想做堆表,必须显式写
PRIMARY KEY NONCLUSTERED
- ✘ 完全不建索引(堆表 + 无索引)
- 后果:范围查询全表扫描,数据量上来直接卡死
- ✘ 索引建太多
- 后果:写入、插入、更新变慢;每写一行,所有索引都要维护
- ✘ 索引字段太长:nvarchar (4000) 做索引 key
- 后果:索引页容纳行数变少、索引膨胀、重建索引开销大、容易溢出
- ✔ 长文本放到 INCLUDE 里,不要放到索引 key
- ✘ 没有覆盖索引,高频查询大量回表(key lookup)
- 你的场景:按 testtime 同步,只建 testtime 索引,select * 大量回表读聚集页
- ✘ 索引 FILLFACTOR 乱设置
- 后果:正常追加写入设太低→索引提前膨胀;频繁中间插入不调低 fillfactor→疯狂页拆分
- ✔ 只经常补历史数据才调低(80/90);只实时追加默认即可
- ✘ 忘记索引维护(重建 / 重组)
- 后果:碎片慢慢累积,性能逐步衰减
二、字段类型设计坑(MES 采集表高频踩)
- ✘ 字段类型过大:明明最多 50 字符,统一 nvarchar (4000)
- 后果:行变宽、一页能存的数据变少、更容易页拆分、备份 / IO 压力变大
- ✔ 短文本 nvarchar (50/80/100),真正超长内容单独一张表或者 INCLUDE
- ✘ 时间用 datetime,不用 datetime2 (3)
- datetime 精度只有 3.33ms,存在时间重合、排序、同步边界漏行风险
- ✘ 数值随便用 int,预估行数超 21 亿还在用 int 自增
- ✔ 采集表直接 bigint(RecId),避免 ID 溢出
- ✘ 该 NOT NULL 的字段设为 NULL(比如 testtime)
- 后果:索引效率略差、统计信息容易不准、业务判断容易出边界 bug
- ✘ 冗余字段、重复存储大文本,不做拆分
- ✘ 字符集 / 排序规则混用,导致隐式转换
- 后果:索引失效!where 条件不走索引,全表扫描(非常隐蔽)
例:Barcode 一边 nvarchar 一边 varchar 对比
三、表存储结构坑(区分 SQL Server / PG / Mongo)
重点:不同数据库机制不一样,不要把 Mongo 习惯直接套 SQL Server
- SQL Server
- ✘ 时序采集主表盲目用堆表(无聚集索引) 后果:更新产生 forwarded record 转发行碎片,批量扫描抖动,就是你 Mylar_A/B 的现象
- ✘ 一张超大表不做分区,按月删数据用 DELETE 后果:删除产生大量日志 + 碎片,删完索引全碎 ✔ 保留 30 天的时序表:优先分区表,删除直接切分区(零碎片)
- PostgreSQL
- PG 默认是堆表,主键默认非聚集 B 树,没有 SQL Server 那种聚集索引碎片问题
- ✘ 频繁 CLUSTER,CLUSTER 是一次性排序,不会持续维护
- MongoDB
- ✘ 不要换掉
_id聚集索引逻辑;不要自定义无序的聚集键(新版 Mongo 支持) - ✘ 大集合不建合适复合索引,大量 collscan 全集合扫描
- ✘ 不要换掉
四、业务写入模式带来的性能坑(和你的同步场景强相关)
- ✘ 时序表,一边持续实时新增,一边频繁补录 / 重测旧时间数据
- 后果:哪怕 RecId 递增聚集,补历史数据依然插到索引中间,产生页拆分
- ✔ 方案:补录单独走小分片、或者补录表和主采集表分开
- ✘ 批量一次性导入海量无序数据
- 后果:一次性导入直接把聚集索引碎片冲到 99%(哪怕聚集键是自增 ID,批量导入方式不对也会碎)
- ✘ 上位机循环单条 insert,不批量提交
- 后果:事务日志频繁刷盘、写入吞吐低
- ✘ 长事务、事务开很久再提交
- 后果:日志暴涨、锁阻塞、VLF 碎片化、同步读取被阻塞
- ✘ 同步查询一次性拉超大时间范围(一次查 2 小时数据)
- 后果:单次查询时间过长,超过 C# CommandTimeout,拿到部分数据,假漏数据(你最初遇到的现象)
- ✔ 拆小时间分片循环拉取,并且行数对账重试
五、数据清理、过期删除坑(你 30 天保留周期重点)
- ✘ 直接大批量 DELETE 删除过期数据
- 后果:大量日志、大量碎片、索引破碎、VLF 变多
- ✘ 删除后不做碎片监控
- ✘ 日志文件自动增长,每次涨一点点(默认 1M/10M)
- 后果:VLF 极多,日志碎片,备份、恢复、索引重建变慢
- ✔ 提前预分配合适大小的 ldf,不要让它频繁自动扩容
六、查询写法隐性坑(建表之后配套使用)
- ✘ where 条件对索引字段做函数运算
DATEADD(testtime...)→ 索引失效 - ✘ select * ,不需要的字段全部读出来,放大 IO、回表压力
- ✘ 时间边界条件写错:>= / > 边界搞错,跨毫秒丢行(你同步查询边界风险)
- ✘ 没有分页、没有分片,单次返回几十万上百万行给程序
七、其他容易忽略的坑
- ✘ 数据文件、日志文件放同一个磁盘,无分开存放
- ✘ 自动增长比例设置百分比(比如 10%),后期文件一次暴涨
- ✘ 数据库恢复模型搞错:采集库不需要完整恢复模式,用 SIMPLE(简单模式),日志不会疯狂涨
- ✘ 统计信息长期不更新,优化器选错执行计划(突然某一天查询变慢)
✅ 给你【MES 时序采集表 新建快速自检表】(每次建表勾一遍)
- 自增 RecId bigint,主键;SQL Server 聚集在 RecId
- testtime datetime2(3) NOT NULL
- 字段长度按需设置,不统一无脑 nvarchar (4000)
- 建立 IX_testtime 非聚集索引,用于时间段同步查询
- 恢复模式 SIMPLE,日志预先分配大小
- 业务:同步查询拆小时间片 + 行数对账
- 确认:是否经常补历史数据,是的话调低 fillfactor 或者单独存放补录数据
如果你想要,我可以把这份清单压缩成一页极简版(打印 / 复制到记事本,每次建表直接对照)。
💥💥💥💥💥💥AOI数据存放
MES 时序采集表|建表自检清单(极简版,直接复制保存)
适用:SQL Server,产线采集、按时间同步、保留短期数据(30 天) 核对顺序:存储→字段→主键索引→库配置→业务规则
一、表结构 & 主键索引(最重要)
- RecId BIGINT IDENTITY(1,1) NOT NULL
- RecId 设为 PRIMARY KEY CLUSTERED(聚集主键,递增防页拆分、防碎片暴涨)
- 时间字段:testtime datetime2 (3) NOT NULL
- 建立索引:IX_表名_testtime (非聚集,给时间段同步查询用)
- 不长文本无脑 nvarchar (4000),按需 nvarchar (50/80/100)
- 尽量 NOT NULL:时间、条码这类核心字段
二、数据库基础配置
- 恢复模式:SIMPLE(简单模式,防止日志暴增)
- mdf、ldf 预分配固定大小,不要百分比自动增长
- 日志提前扩容,避免频繁自动扩容产生大量 VLF 碎片
三、业务写入规则(上位机 C# 侧)
- 优先实时追加新数据,尽量少补录旧时间历史数据
- 补录历史数据单独处理,补录容易产生页拆分
- Insert 优先批量提交,不要循环单条插入
四、同步查询规则(解决你 “假漏数据” 核心)
- 禁止一次拉超大时间范围,拆小分片循环查询(比如 5 分钟一片)
- 时间边界严格:> 和 < 分清,防止跨毫秒丢行、重复行
- 每片同步后做行数对账,行数不一致自动重试
- 尽量不要 select *,按需取字段,减少回表
五、过期清理(30 天自动删数据)
- 大批量 DELETE 会产生碎片、大量日志,尽量避免高频大批量删除
- 有条件:按月 / 按天分区分区表,删数据直接删分区(最优,几乎无碎片)
- 删除后留意碎片,碎片高时安排低峰重建索引
六、避坑黑名单(不要这么干)
- ❌ 不要把 testtime / Barcode 做聚集索引(无序→疯狂页拆分、碎片 99%)
- ❌ 时序主表不要盲目做成堆表(堆表容易产生转发行碎片,批量查询抖动)
- ❌ 索引建太多,每个索引都会拖慢写入
- ❌ where 条件给索引字段套函数(索引失效,全表扫描)
- ❌ 长事务长时间不提交(日志暴涨、锁、阻塞)
七、定期巡检(每天 / 每周看一眼)
- leaf_allocation_count:页拆分次数,涨太快代表写入模式有问题
- 索引碎片:高碎片(>30%)低峰重建
- VLF 数量,不要几百上千个
跨库快速记忆(PG / Mongo)
- PostgreSQL:默认堆表,主键非聚集 B 树,没有 SQL Server 聚集碎片问题
- MongoDB:_id 固定聚集索引,天然递增追加模式,和 RecId 聚集思路一致
如果你需要,我再给你配套【巡检 SQL 小脚本包】,直接复制定期跑,看碎片、页拆分、VLF。
正文完