上位机 500ms 一条测点写库，半年后查询要 40 秒：写入三档方案与分区两刀（C#+SQL Server）

一个车间数据网关项目踩过的坑上位机 500ms 采一次测点往 SQL Server 里插一条记录。上线时好好的运行半年后两个问题一起爆——写入开始超时内存里的采集队列越堆越多老师傅想查三个月前的趋势一条查询跑 40 秒表已经两千多万行。这篇文章把修复过程整理出来写入侧三档方案附完整代码与吞吐对照、查询侧两刀都是能直接抄的东西。先说结论500ms 一条不是问题每条一个自动提交事务 无批量 无分区才是问题。逐条 INSERT 的写法天花板在几百行每秒而且每条都逼着 SQL Server 刷一次事务日志——量一起来最先死的不是数据库是你采集程序里的队列。一、病根拆解原来的写入代码是最常见的写法// 病灶一条一事务500ms 一发日志刷盘 86400×2 次/天usingvarcmdnewSqlCommand(INSERT INTO TagLog(TagId, Ts, Val, Quality) VALUES(t,s,v,q),conn);cmd.Parameters.AddWithValue(t,tagId);cmd.Parameters.AddWithValue(s,ts);cmd.Parameters.AddWithValue(v,val);cmd.Parameters.AddWithValue(q,q);cmd.ExecuteNonQuery();三个叠加因素逐条自动提交每条一个事务日志与磁盘交互是吞吐瓶颈索引当场建了仨每 insert 维护三棵 B 树查询不带分区两千行万数据的表上范围扫描40 秒不冤。二、写入三档方案按量级选档 1批量参数化 Insert小改造量小够用把 500ms 一条改成 5 秒一批 10 条拼一条 SQL几百点规模的小系统就能撑。缺点是 SQL 拼接长度有限制不算正经方案跳过。档 2内存缓冲 SqlBulkCopy本文主推采集线程只管进内存队列落库交给独立的批量写线程。这是工控网关的标准结构publicsealedclassTelemetryWriter:IDisposable{privatereadonlyConcurrentQueueTagPoint_queuenew();privatereadonlyTimer_timer;privatereadonlystring_connStr;privateconstintMaxQueue200_000;// 水位≈按采集速率撑 10 分钟publicTelemetryWriter(stringconnStr){_connStrconnStr;_timernewTimer(_Flush(),null,5000,5000);// 5s 一批}publicvoidEnqueue(TagPointp){_queue.Enqueue(p);if(_queue.CountMaxQueue)OnOverflow();// 超水位告警丢弃策略别让内存爆}privatevoidFlush(){if(_queue.IsEmpty)return;vartablenewDataTable();table.Columns.Add(TagId,typeof(int));table.Columns.Add(Ts,typeof(DateTime));table.Columns.Add(Val,typeof(float));table.Columns.Add(Quality,typeof(byte));while(_queue.TryDequeue(outvarp)table.Rows.Count5000)table.Rows.Add(p.TagId,p.Ts,p.Val,p.Quality);if(table.Rows.Count0)return;usingvarbulknewSqlBulkCopy(_connStr,SqlBulkCopyOptions.TableLock){DestinationTableNameTagLog,BatchSizetable.Rows.Count};// 列映射必须显式写靠位置默认对齐表结构一变就静默错位bulk.ColumnMappings.Add(TagId,TagId);bulk.ColumnMappings.Add(Ts,Ts);bulk.ColumnMappings.Add(Val,Val);bulk.ColumnMappings.Add(Quality,Quality);bulk.WriteToServer(table);// 失败重试 1 次后按溢出策略处理}publicvoidDispose()_timer.Dispose();}要点TableLock在大批量时反而快锁升级一次而不是逐行锁ColumnMappings 显式映射防 schema 变更静默错位队列水位是保命阀——数据库维护重启十几分钟队列就是缓冲超水位宁可丢最旧数据告警不能把采集线程卡死。档 3表值参数 TVP要事务逻辑时用SqlBulkCopy 没法在事务里夹业务逻辑比如重复时间戳丢弃这时用表值参数一批数据当参数传给存储过程过程里想怎么处理怎么处理CREATETYPEdbo.TagPointTableASTABLE(TagIdINTNOTNULL,Ts DATETIME2(3)NOTNULL,ValREAL,QualityTINYINT);CREATEPROCEDUREdbo.BulkInsertTagLogpointsdbo.TagPointTable READONLYASBEGININSERTINTOTagLog(TagId,Ts,Val,Quality)SELECTp.TagId,p.Ts,p.Val,p.QualityFROMpointspWHERENOTEXISTS(SELECT1FROMTagLog t-- 示例窗口内去重WHEREt.TagIdp.TagIdANDt.Tsp.Ts);ENDC# 侧把 DataTable 作为 SqlParameterSqlDbType.Structured传进去即可。吞吐量级对照同机 SSD、千行批量量级供参考方案千行耗时量级适用逐条自动提交数秒演示代码批量 SqlBulkCopy几十毫秒纯写入网关/历史站首选表值参数 TVP百毫秒级批量写入过程内校验逻辑改造后我们网关 5 秒一批、每批几百行写入耗时稳定在 20ms 量级采集队列水位常年个位数百分比。三、查询侧两刀写入通了查询还得救回来索引对齐查询姿势(TagId, Ts)复合聚集或按需非聚集查询条件永远TagId id AND Ts BETWEEN s AND e——别在 Ts 上包函数CONVERT(varchar, Ts, 120) BETWEEN ...会让索引直接失效格式化放显示层按月分区两千多万行的表按 Ts 做月分区查三个月趋势只扫三个分区老数据归档切换分区出去秒级完成不再 DELETE 到天亮。三点五、日常维护作业别让系统半年后再爆一次上线不是终点配好三件事系统才能长期稳统计信息与索引碎片写入型表的统计信息过时执行计划会慢慢劣化查询又悄悄变慢。建一个每周作业ALTERINDEXALLONTagLog REORGANIZE;-- 在线索索重组不锁表UPDATESTATISTICSTagLogWITHFULLSCAN;-- 统计信息刷新老数据归档 分区切换按月分区建好后归档上月数据用SWITCH把整个分区挪到归档表秒级完成、不产生巨量日志——和DELETE FROM TagLog WHERE Ts ...删一整夜是两个世界ALTERTABLETagLog SWITCHPARTITION10TOTagLog_Archive_202610;-- 然后归档表随便处理备份、压缩、导出后 truncate水位监控接入告警TelemetryWriter 的队列水位、每批写入耗时、丢弃计数三个指标接进现有告警通道。我们给的经验阈值水位持续 30%、单批耗时持续 2 秒、24h 内丢弃 0任一触发就去看数据库。这三件套做完这套库在我们产线跑了两年多没再爆过——不是没有量是量上来的时候有人作业和告警先看见了。四、六个真实的坑AddWithValue 无脑用类型靠猜float 被当 double、时间丢精度执行计划还烂。显式SqlDbTypeBulkCopy 不写 ColumnMappings默认按列位置对齐表加了一列之后数据静默错位这种错没有报错只有数据不对缓冲队列无上限数据库一维护队列吃光内存网关进程被系统杀——水位丢弃告警三件套必须有每条一个事务还想快事务的开销在提交刷盘批量的本质是把 N 次提交并成 1 次这是数量级差异的来源别的优化都排在这条后面时间列 datetime 且本地时区混存夏令时/跨机时间回拨出现倒流数据。统一datetime2 UTC 存储展示层再转本地索引贪多写入型表索引每多一个BulkCopy 都要维护。写入表只留查询必需的复合索引报表分析甩给只读副本或聚合表。五、结论测点入库的架构定型就一句话采集与落库之间放一个有水位的内存队列落库走定时批量 SqlBulkCopy表按时间分区。写入吞吐从几百条/秒的天花板进到按万条/秒起的量级查询侧索引对齐分区后40 秒的趋势查询回到亚秒。网关类的程序把这段 TelemetryWriter 直接抄走改个表名就能用。最后提醒一句排障顺序先看写入方式再看索引与分区最后才轮到讨论换数据库——九成的数据库不行其实是写入姿势不对。