数据库建表 & 日常性能踩坑清单(适配你:MES 时序采集、SQL Server 为主,顺带 PG/Mongo 对照)

傻瓜友好、少操心、不用折腾聚集索引 / 碎片、日常几乎不用维护:PostgreSQL > MongoDB > SQL Server

偏向建表阶段就能规避的问题,不是事后调优,适合新建表的时候对照检查;重点覆盖你踩过的【聚集索引、碎片、页拆分、同步查询假丢数据】这类痛点 分大类:索引设计、字段设计、表存储结构、查询 & 业务写入模式、数据清理、其他隐性坑

一、索引类(你本次最大的坑区,SQL Server 重点)

  1. ✘ 聚集索引键选无序字段(testtime、条码、随机值)
    • 后果:频繁页拆分、逻辑碎片快速拉到 99%,时间范围查询巨慢,同步超时,疑似漏数据
    • ✔ 时序采集推荐:自增 ID(RecId)做聚集主键;Mongo 天然_id就是这个思路
  2. ✘ SQL Server:不知道「主键默认是聚集索引」,稀里糊涂绑定错字段
    • 后果:以为只是加个唯一主键,实则直接改了数据物理排序
    • ✔ 想做堆表,必须显式写 PRIMARY KEY NONCLUSTERED
  3. ✘ 完全不建索引(堆表 + 无索引)
    • 后果:范围查询全表扫描,数据量上来直接卡死
  4. ✘ 索引建太多
    • 后果:写入、插入、更新变慢;每写一行,所有索引都要维护
  5. ✘ 索引字段太长:nvarchar (4000) 做索引 key
    • 后果:索引页容纳行数变少、索引膨胀、重建索引开销大、容易溢出
    • ✔ 长文本放到 INCLUDE 里,不要放到索引 key
  6. ✘ 没有覆盖索引,高频查询大量回表(key lookup)
    • 你的场景:按 testtime 同步,只建 testtime 索引,select * 大量回表读聚集页
  7. ✘ 索引 FILLFACTOR 乱设置
    • 后果:正常追加写入设太低→索引提前膨胀;频繁中间插入不调低 fillfactor→疯狂页拆分
    • ✔ 只经常补历史数据才调低(80/90);只实时追加默认即可
  8. ✘ 忘记索引维护(重建 / 重组)
    • 后果:碎片慢慢累积,性能逐步衰减

二、字段类型设计坑(MES 采集表高频踩)

  1. ✘ 字段类型过大:明明最多 50 字符,统一 nvarchar (4000)
    • 后果:行变宽、一页能存的数据变少、更容易页拆分、备份 / IO 压力变大
    • ✔ 短文本 nvarchar (50/80/100),真正超长内容单独一张表或者 INCLUDE
  2. ✘ 时间用 datetime,不用 datetime2 (3)
    • datetime 精度只有 3.33ms,存在时间重合、排序、同步边界漏行风险
  3. ✘ 数值随便用 int,预估行数超 21 亿还在用 int 自增
    • ✔ 采集表直接 bigint(RecId),避免 ID 溢出
  4. ✘ 该 NOT NULL 的字段设为 NULL(比如 testtime)
    • 后果:索引效率略差、统计信息容易不准、业务判断容易出边界 bug
  5. ✘ 冗余字段、重复存储大文本,不做拆分
  6. ✘ 字符集 / 排序规则混用,导致隐式转换
    • 后果:索引失效!where 条件不走索引,全表扫描(非常隐蔽)

    例:Barcode 一边 nvarchar 一边 varchar 对比

三、表存储结构坑(区分 SQL Server / PG / Mongo)

重点:不同数据库机制不一样,不要把 Mongo 习惯直接套 SQL Server

  1. SQL Server
    • ✘ 时序采集主表盲目用堆表(无聚集索引) 后果:更新产生 forwarded record 转发行碎片,批量扫描抖动,就是你 Mylar_A/B 的现象
    • ✘ 一张超大表不做分区,按月删数据用 DELETE 后果:删除产生大量日志 + 碎片,删完索引全碎 ✔ 保留 30 天的时序表:优先分区表,删除直接切分区(零碎片)
  2. PostgreSQL
    • PG 默认是堆表,主键默认非聚集 B 树,没有 SQL Server 那种聚集索引碎片问题
    • ✘ 频繁 CLUSTER,CLUSTER 是一次性排序,不会持续维护
  3. MongoDB
    • ✘ 不要换掉_id聚集索引逻辑;不要自定义无序的聚集键(新版 Mongo 支持)
    • ✘ 大集合不建合适复合索引,大量 collscan 全集合扫描

四、业务写入模式带来的性能坑(和你的同步场景强相关)

  1. ✘ 时序表,一边持续实时新增,一边频繁补录 / 重测旧时间数据
    • 后果:哪怕 RecId 递增聚集,补历史数据依然插到索引中间,产生页拆分
    • ✔ 方案:补录单独走小分片、或者补录表和主采集表分开
  2. ✘ 批量一次性导入海量无序数据
    • 后果:一次性导入直接把聚集索引碎片冲到 99%(哪怕聚集键是自增 ID,批量导入方式不对也会碎)
  3. ✘ 上位机循环单条 insert,不批量提交
    • 后果:事务日志频繁刷盘、写入吞吐低
  4. ✘ 长事务、事务开很久再提交
    • 后果:日志暴涨、锁阻塞、VLF 碎片化、同步读取被阻塞
  5. ✘ 同步查询一次性拉超大时间范围(一次查 2 小时数据)
    • 后果:单次查询时间过长,超过 C# CommandTimeout,拿到部分数据,假漏数据(你最初遇到的现象)
    • ✔ 拆小时间分片循环拉取,并且行数对账重试

五、数据清理、过期删除坑(你 30 天保留周期重点)

  1. ✘ 直接大批量 DELETE 删除过期数据
    • 后果:大量日志、大量碎片、索引破碎、VLF 变多
  2. ✘ 删除后不做碎片监控
  3. ✘ 日志文件自动增长,每次涨一点点(默认 1M/10M)
    • 后果:VLF 极多,日志碎片,备份、恢复、索引重建变慢
    • ✔ 提前预分配合适大小的 ldf,不要让它频繁自动扩容

六、查询写法隐性坑(建表之后配套使用)

  1. ✘ where 条件对索引字段做函数运算 DATEADD(testtime...) → 索引失效
  2. ✘ select * ,不需要的字段全部读出来,放大 IO、回表压力
  3. ✘ 时间边界条件写错:>= / > 边界搞错,跨毫秒丢行(你同步查询边界风险)
  4. ✘ 没有分页、没有分片,单次返回几十万上百万行给程序

七、其他容易忽略的坑

  1. ✘ 数据文件、日志文件放同一个磁盘,无分开存放
  2. ✘ 自动增长比例设置百分比(比如 10%),后期文件一次暴涨
  3. ✘ 数据库恢复模型搞错:采集库不需要完整恢复模式,用 SIMPLE(简单模式),日志不会疯狂涨
  4. ✘ 统计信息长期不更新,优化器选错执行计划(突然某一天查询变慢)

✅ 给你【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。

正文完
 0