1. 為什么上位機(jī)項(xiàng)目繞不開數(shù)據(jù)持久化——先看清你的數(shù)據(jù)模型做上位機(jī)開發(fā)的人大多經(jīng)歷過這種場面PLC、板卡或者儀器儀表傳回來的數(shù)據(jù)在界面上跑得飛快曲線、數(shù)字、開關(guān)量全部正常但只要一斷電或者軟件重啟歷史數(shù)據(jù)就全部蒸發(fā)。客戶驗(yàn)收時(shí)隨口一句“我想看看上周那臺設(shè)備某幾個(gè)小時(shí)的曲線”你就得當(dāng)場想辦法撈數(shù)據(jù)撈不出來就是事故。反過來我也見過不少新入行的同事覺得“數(shù)據(jù)持久化嘛不就是把每條數(shù)據(jù)往數(shù)據(jù)庫里懟”。于是采集線程里每來一條就執(zhí)行一次 INSERT結(jié)果界面卡死、數(shù)據(jù)庫文件膨脹、程序越跑越慢。問題不在于 SQLite 本身行不行而在于沒有想清楚“什么數(shù)據(jù)、什么頻率、存多久、誰來讀”這四件事。1.1 上位機(jī)里的三類數(shù)據(jù)性格完全不同我在實(shí)際項(xiàng)目里習(xí)慣把上位機(jī)的數(shù)據(jù)分成三類高速采集類數(shù)據(jù)典型是振動(dòng)波形、電流瞬態(tài)值、位置誤差、編碼器反饋。這類數(shù)據(jù)采樣率從 100Hz 到幾十 kHz 都很常見每條數(shù)據(jù)可能只有幾個(gè)到幾十個(gè)字節(jié)但一秒鐘就能產(chǎn)生幾千到幾萬條記錄。這類數(shù)據(jù)如果直接落盤磁盤 IO 很快就成為瓶頸更麻煩的是它們通常用于實(shí)時(shí)曲線顯示和報(bào)警判斷寫磁盤的速度根本跟不上采集節(jié)奏。狀態(tài)與報(bào)警類數(shù)據(jù)比如設(shè)備啟停信號、故障碼、溫控開關(guān)動(dòng)作、操作記錄。這類數(shù)據(jù)頻率低、每秒幾條甚至每分鐘幾條但價(jià)值高、需要長期保留、經(jīng)常會(huì)被檢索?？蛻魡枴白蛱炝璩咳c(diǎn)那臺設(shè)備為什么停機(jī)”時(shí)查的就是這類數(shù)據(jù)。配置與工藝參數(shù)類數(shù)據(jù)比如配方、PID 參數(shù)、坐標(biāo)補(bǔ)償值、設(shè)備型號。這類數(shù)據(jù)量小、改動(dòng)不頻繁但是絕對不能丟。如果程序重啟后參數(shù)丟失整個(gè)設(shè)備都可能變成“工廠門禁都打不開”的狀態(tài)。這三類數(shù)據(jù)的訪問模式完全不同選型時(shí)如果用一套方案硬套要么過度設(shè)計(jì)要么性能翻車。我見過有人為了讓 10kHz 的波形數(shù)據(jù)實(shí)時(shí)展示硬是往 SQLite 里高頻寫結(jié)果數(shù)據(jù)庫還沒崩UI 先卡死了也有人因?yàn)槭∈掳阉袇?shù)都扔在一個(gè) CSV 文件里結(jié)果設(shè)備運(yùn)行久了文件幾個(gè) G打開都費(fèi)勁。1.2 持久化發(fā)生在采集閉環(huán)的哪個(gè)位置上位機(jī)從硬件取數(shù)到界面展示中間至少經(jīng)過采集、解析、處理、顯示、存儲幾個(gè)環(huán)節(jié)。持久化不應(yīng)該僅僅放在采集線程里面搞“實(shí)時(shí)同步寫”而應(yīng)該把它視為整條數(shù)據(jù)處理鏈路上的一層。我通常建議畫一張簡單的數(shù)據(jù)流圖硬件設(shè)備 - 采集線程 - 原始數(shù)據(jù)緩存 - 實(shí)時(shí)計(jì)算/曲線顯示 - 存儲層。采集線程只負(fù)責(zé)把數(shù)據(jù)丟進(jìn)緩存區(qū)顯示模塊從緩存區(qū)讀數(shù)據(jù)做實(shí)時(shí)渲染存儲層則按照設(shè)定策略把緩存區(qū)里的數(shù)據(jù)批量寫入歸檔。這樣無論是 SQLite 還是內(nèi)存數(shù)據(jù)庫都只是一個(gè)“后端角色”不會(huì)反向拖累前端采集。其實(shí)很多所謂“選型困難”根源在于沒有給每種數(shù)據(jù)分配不同的車道。高頻數(shù)據(jù)走內(nèi)存緩沖低頻記錄直接寫庫配置參數(shù)用單獨(dú)的表管理并定期備份這就是最樸素的分層思路。下面我把 SQLite 和實(shí)時(shí)內(nèi)存數(shù)據(jù)庫分別拆開講再給出組合用法。2. SQLite 在上位機(jī)里的定位與四個(gè)關(guān)鍵配置2.1 單機(jī)嵌入式數(shù)據(jù)庫的“部署友好”壓倒一切接觸過工控現(xiàn)場的人都知道客戶那臺工控機(jī)上的軟件環(huán)境有多“原生態(tài)”。有的機(jī)器連 VC 運(yùn)行庫都沒裝全更別提 MySQL、PostgreSQL 那套獨(dú)立服務(wù)。上位機(jī)軟件要是在部署環(huán)節(jié)還得裝一個(gè)數(shù)據(jù)庫服務(wù)端項(xiàng)目經(jīng)理的臉當(dāng)場就能拉下來。SQLite 最大的優(yōu)勢是它沒有服務(wù)端數(shù)據(jù)庫就是軟件目錄下的一個(gè)文件。C# 里用 Microsoft.Data.Sqlite 這個(gè)官方庫發(fā)布時(shí)帶上運(yùn)行時(shí)需要的原生二進(jìn)制客戶機(jī)器上只要能跑 .NET數(shù)據(jù)庫就能用。升級、備份、遷移都極其簡單關(guān)掉程序、復(fù)制文件、完事。這一點(diǎn)在工控場景里是實(shí)打?qū)嵉摹懊饩S護(hù)”。有些同事會(huì)說“我用 CSV 不也一樣嗎不就是寫文件嗎”。數(shù)據(jù)量小的時(shí)候是差不多但一旦涉及按時(shí)間段查詢、條件過濾、跨天統(tǒng)計(jì)CSV 的處理就非常痛苦。SQLite 至少給了你標(biāo)準(zhǔn) SQL、索引、事務(wù)和并發(fā)控制哪怕你只用到其中兩成功能收益也已經(jīng)明顯超出文件方案。2.2 讓 SQLite 別卡脖子WAL、busy_timeout、synchronous、連接池不少人第一次用 SQLite 時(shí)被“database is locked”坑過然后就直接給 SQLite 判了死刑。其實(shí)這個(gè)錯(cuò)誤大多數(shù)情況下不是 SQLite 不行而是你把滾珠軸承當(dāng)錘子使。SQLite 默認(rèn)的 rollback journal 模式下讀和寫會(huì)互相阻塞寫入時(shí)甚至可能阻塞整個(gè)數(shù)據(jù)庫文件的讀取。解決這個(gè)問題最常用的手段是開啟 WAL 模式也就是 Write-Ahead Logging。開啟之后寫入先落到一個(gè)獨(dú)立的-wal文件讀操作依然可以從主庫文件讀取讀寫并行能力大幅提升。我在代碼里一般這樣設(shè)置using var cmd conn.CreateCommand(); cmd.CommandText PRAGMA journal_modeWAL;; var mode cmd.ExecuteScalar()?.ToString(); // 正常返回值應(yīng)當(dāng)是 wal注意要檢查返回值如果返回的不是wal說明當(dāng)前環(huán)境下寫入可能被限制或者數(shù)據(jù)庫處于其他狀態(tài)。WAL 模式啟用后你會(huì)在數(shù)據(jù)庫文件旁邊看到.wal和.shm兩個(gè)輔助文件這都是正常的程序正常關(guān)閉并 checkpoint 后會(huì)合并回主庫文件。第二個(gè)關(guān)鍵配置是busy_timeout。這個(gè)參數(shù)的作用是當(dāng)數(shù)據(jù)庫文件被其他連接鎖住時(shí)當(dāng)前操作最長等多久再報(bào)錯(cuò)。我一般設(shè)置為 3000ms寫入線程爭取任務(wù)時(shí)如果遇到短時(shí)占用會(huì)自動(dòng)等待而不是立刻拋出SQLITE_BUSY異常using var cmd conn.CreateCommand(); cmd.CommandText PRAGMA busy_timeout3000;; cmd.ExecuteNonQuery();第三個(gè)參數(shù)是synchronous。默認(rèn)是 FULL每一次寫操作都要等待數(shù)據(jù)落盤安全但速度偏慢。數(shù)據(jù)量大的批量寫入場景我會(huì)在事務(wù)里臨時(shí)設(shè)置為 NORMAL。NORMAL 模式下 WAL 機(jī)制本身仍能保證數(shù)據(jù)庫崩潰時(shí)數(shù)據(jù)不會(huì)損壞只是極端掉電場景下可能丟失最近一小段已提交數(shù)據(jù)。對于絕大多數(shù)工業(yè)數(shù)據(jù)歸檔來說這個(gè)代價(jià)可以接受。第四個(gè)要點(diǎn)是連接串。很多人寫 SQLite 代碼喜歡每次操作都新建一個(gè)連接用完就關(guān)這在數(shù)據(jù)量大的時(shí)候非常浪費(fèi)。Microsoft.Data.Sqlite 支持連接池要在連接串里顯式開啟var connStr new SqliteConnectionStringBuilder { DataSource dbPath, Mode SqliteOpenMode.ReadWriteCreate, Cache SqliteCacheMode.Shared, Pooling true }.ToString();連接池加上共享緩存能讓并發(fā)讀寫場景下的鎖沖突明顯減少。但也要注意連接池里的物理連接如果長時(shí)間空閑WAL 文件可能不會(huì)被及時(shí) checkpoint所以長時(shí)間運(yùn)行的程序最好定期執(zhí)行一下PRAGMA wal_checkpoint(TRUNCATE);或者干脆讓程序重啟時(shí)自動(dòng) checkpoint。2.3 數(shù)據(jù)庫文件損壞與恢復(fù)別指望每次都靠備份工業(yè)現(xiàn)場斷電從來不講道理Windows 異常關(guān)機(jī)、工控機(jī)藍(lán)屏、USB 被拔都有可能發(fā)生。SQLite 雖然有事務(wù)保護(hù)但也不能 100% 免疫文件損壞。我處理過的案例里最常見的是.wal文件異常殘留導(dǎo)致主庫打不開或者是數(shù)據(jù)庫文件被第三方工具半路打開改壞了。遇到這種問題我一般先從備份恢復(fù)所以我的程序里會(huì)保留最近三份數(shù)據(jù)庫備份文件這是最后一道防線。如果連備份都沒有也先別急著刪除文件可以試試 SQLite 自帶的恢復(fù)機(jī)制。使用 Db Browser for SQLite 時(shí)File - Export - Export to SQL file有時(shí)能把還能讀出來的數(shù)據(jù)轉(zhuǎn)成 SQL 腳本再用腳本重建數(shù)據(jù)庫。命令行里可以用.recover命令但這兩招的成功率取決于文件損壞程度別抱太大希望。2.4 一個(gè)順手的小工具Db Browser for SQLite排查 SQLite 數(shù)據(jù)問題純靠寫代碼看結(jié)果太慢了。我調(diào)試時(shí)基本都會(huì)開著 Db Browser for SQLite直接打開數(shù)據(jù)庫文件查看表結(jié)構(gòu)、索引和數(shù)據(jù)內(nèi)容。它還支持執(zhí)行任意 SQL方便我驗(yàn)證查詢語句的寫法。比如我要確認(rèn)某張表的數(shù)據(jù)量、檢查時(shí)間字段是否被正確存儲、查看高頻寫入后 WAL 文件有沒有異常膨脹直接用這個(gè)工具跑一條SELECT count(*) FROM samples WHERE ts ...就清楚了。還有一個(gè)很實(shí)用的功能是“Define Query”可以用它快速寫一條帶參數(shù)的查詢語句調(diào)試復(fù)雜統(tǒng)計(jì)邏輯時(shí)省不少事。3. 實(shí)時(shí)內(nèi)存數(shù)據(jù)庫高頻采集場景的“緩沖層”3.1 不是每個(gè)項(xiàng)目都需要一個(gè)真正的“內(nèi)存數(shù)據(jù)庫”名詞聊到實(shí)時(shí)內(nèi)存數(shù)據(jù)庫很多人第一反應(yīng)是 Redis、Memcached 這類獨(dú)立服務(wù)。但在上位機(jī)項(xiàng)目里情況往往沒有這么“重型”。工控現(xiàn)場就一臺工控機(jī)客戶不會(huì)同意你為了存數(shù)據(jù)再部署一個(gè) Redis 服務(wù)更不會(huì)維護(hù)它。所以我在大多數(shù)項(xiàng)目里說的“實(shí)時(shí)內(nèi)存數(shù)據(jù)庫”其實(shí)指的是進(jìn)程內(nèi)維護(hù)的一套內(nèi)存數(shù)據(jù)機(jī)構(gòu)加上必要的并發(fā)控制和查詢接口。它要解決的核心問題是高頻數(shù)據(jù)進(jìn)入系統(tǒng)后既要讓顯示模塊毫秒級拿到數(shù)據(jù)又不能因?yàn)榈却疟P IO 拖慢采集。簡單的ListSample不夠用因?yàn)樽x寫競爭會(huì)帶來臟數(shù)據(jù)不加容量上限也不行進(jìn)程跑一晚上會(huì)把內(nèi)存吃光。所以一個(gè)合格的上位機(jī)內(nèi)存數(shù)據(jù)層應(yīng)該具備這幾點(diǎn)無鎖或輕量鎖的并發(fā)讀寫、容量上限或過期淘汰機(jī)制、按時(shí)間窗口快速查詢的能力。舉個(gè)例子我的一個(gè)項(xiàng)目里32 個(gè)通道每 10ms 采集一條原始數(shù)據(jù)也就是每秒 3200 條記錄。實(shí)時(shí)顯示只需要最近 500 條點(diǎn)但整段曲線的原始數(shù)據(jù)可能要持續(xù)采集幾個(gè)小時(shí)。如果把這些數(shù)據(jù)全部塞進(jìn)內(nèi)存假設(shè)每條記錄包含時(shí)間戳和 32 個(gè) float 和一個(gè)狀態(tài)字節(jié)算下來約 140 字節(jié)一小時(shí)就是 50 萬條占內(nèi)存約 70MB還可以接受。但如果連續(xù)采集一天就是 1.7GB 內(nèi)存這在工控機(jī)上已經(jīng)不便宜了。3.2 輕量級內(nèi)存數(shù)據(jù)庫設(shè)計(jì)環(huán)形緩沖加時(shí)間窗口我的習(xí)慣是給高頻采集數(shù)據(jù)做一個(gè)“容量有限、后續(xù)有機(jī)會(huì)落盤”的緩沖層。最常用的是環(huán)形緩沖容量固定新數(shù)據(jù)到來時(shí)如果緩沖滿了最老的記錄會(huì)被覆蓋。這樣內(nèi)存占用永遠(yuǎn)有上限實(shí)時(shí)曲線永遠(yuǎn)能拿到最近的數(shù)據(jù)而落盤由另一個(gè)線程從緩沖里批量取走并寫入 SQLite。還有一個(gè)更輕量的選擇是直接用ConcurrentQueueT不需要自己實(shí)現(xiàn)鎖。它的優(yōu)勢是 FIFO 語義非常自然缺點(diǎn)是沒有容量上限要你自己在入隊(duì)時(shí)檢查Count并且手動(dòng)丟棄隊(duì)尾元素。實(shí)測下來采集線程入隊(duì)、后臺線程出隊(duì)寫庫、主線程偶爾查最近數(shù)據(jù)這個(gè)組合在上位機(jī)場景里很穩(wěn)。真正需要“數(shù)據(jù)庫”語義的時(shí)候也就是要按時(shí)間范圍查某個(gè)通道的值、要做聚合計(jì)算、要根據(jù)報(bào)警條件做復(fù)雜篩選時(shí)一個(gè)沒有索引的容器就不夠了。這時(shí)候我建議直接用時(shí)間序列的專用思路數(shù)據(jù)在內(nèi)存中按通道分片每個(gè)分片內(nèi)部是一個(gè)按時(shí)間排序的數(shù)組再保存一份分片的時(shí)間范圍索引。查詢時(shí)先定位到分片再做二分查找。這個(gè)實(shí)現(xiàn)并不復(fù)雜但性能比掃描全量列表高一個(gè)數(shù)量級。3.3 什么時(shí)候可以直接引入外部時(shí)序數(shù)據(jù)庫如果項(xiàng)目本身就是一個(gè)數(shù)據(jù)密集型平臺采集點(diǎn)很多、讀取方也不只一個(gè)上位機(jī)那內(nèi)部的“內(nèi)存數(shù)據(jù)容器”確實(shí)不夠用了。這種情況下可以選擇正式的內(nèi)存數(shù)據(jù)庫或時(shí)序數(shù)據(jù)庫。常見的有 Redis 搭配 Stream 數(shù)據(jù)類型處理時(shí)間序列也有 InfluxDB、QuestDB 這類專門為時(shí)序場景設(shè)計(jì)的存儲。但我要提醒一句引入外部服務(wù)意味著你的項(xiàng)目從“單機(jī)軟件”變成了“分布式系統(tǒng)”。權(quán)限管理、網(wǎng)絡(luò)連接、客戶端依賴、服務(wù)自動(dòng)拉起、現(xiàn)場機(jī)器資源占用這些都要評估。上位機(jī)項(xiàng)目多數(shù)時(shí)候跑在 Windows 工控機(jī)上現(xiàn)場沒有專職運(yùn)維我不建議一上來就用重方案。先用進(jìn)程內(nèi)緩沖加 SQLite 扛住等數(shù)據(jù)量確實(shí)大到單機(jī)撐不住再考慮橫向拆分這條路最穩(wěn)。4. 選型對比持續(xù)寫入的持久層到底怎么決策4.1 一張表看清 SQLite 和實(shí)時(shí)內(nèi)存數(shù)據(jù)庫的差異很多朋友糾結(jié)“SQLite 和內(nèi)存數(shù)據(jù)庫哪個(gè)好”其實(shí)答案取決于你拿它做什么場景。我整理了一張對比表基本覆蓋了大多數(shù)上位機(jī)項(xiàng)目的考量點(diǎn)對比維度SQLite進(jìn)程內(nèi)實(shí)時(shí)內(nèi)存數(shù)據(jù)庫數(shù)據(jù)生命周期永久保存重啟不丟臨時(shí)保存重啟即丟寫入頻率上限受磁盤 IO 和 WAL 影響一般每秒幾千次批量寫入沒問題但不宜單條高頻寫微秒級可達(dá)每秒幾十萬次以上查詢能力標(biāo)準(zhǔn) SQL、索引、聚合、條件篩選都非常方便需要自己實(shí)現(xiàn)索引或按時(shí)間窗口掃描并發(fā)模型多連接需要 WAL 和 busy_timeout 配合單寫多讀最穩(wěn)進(jìn)程內(nèi)共享內(nèi)存用鎖或無鎖結(jié)構(gòu)維護(hù)部署復(fù)雜度單文件無服務(wù)端發(fā)布簡單無外部部署隨程序啟動(dòng)可靠性事務(wù)機(jī)制掉電后較易恢復(fù)掉電丟數(shù)需要定期落盤兜底典型用途歷史歸檔、報(bào)警記錄、配置參數(shù)、報(bào)表查詢實(shí)時(shí)曲線、高速采樣緩沖、報(bào)警判斷的臨時(shí)數(shù)據(jù)集這張表其實(shí)已經(jīng)暗示了一個(gè)結(jié)論這兩者根本不是競爭關(guān)系而是接力關(guān)系。4.2 按寫入頻次和數(shù)據(jù)語義畫一條決策線我在做選型時(shí)習(xí)慣先回答三個(gè)問題數(shù)據(jù)到達(dá)頻次是多少如果每條數(shù)據(jù)間隔大于 100ms、每秒寫入不超過幾十條SQLite 完全可以直接寫入不需要額外的內(nèi)存緩沖。如果每秒幾百條到幾千條建議用內(nèi)存緩沖積累一段時(shí)間再批量向 SQLite 落盤。如果每秒上萬條甚至更高你要考慮是不是真的需要全部原始數(shù)據(jù)落盤還是只保留統(tǒng)計(jì)特征值就夠了。數(shù)據(jù)需要保存多久、誰來讀歸檔數(shù)據(jù)肯定要長期保存那就必須落到 SQLite 或者其他真持久化存儲中。內(nèi)存數(shù)據(jù)庫里的數(shù)據(jù)只能作為臨時(shí)態(tài)不能指望它撐起客戶“查前三個(gè)月數(shù)據(jù)”的需求。數(shù)據(jù)是否需要跨進(jìn)程共享上位機(jī)如果只有一個(gè)進(jìn)程進(jìn)程內(nèi)緩沖完全夠用。如果有多個(gè)上位機(jī)同時(shí)讀同一批數(shù)據(jù)比如中控室和現(xiàn)場同時(shí)看一臺設(shè)備就需要考慮真正的服務(wù)端存儲。4.3 兩層并用才是大多數(shù)項(xiàng)目的最終答案選 SQLite 和內(nèi)存數(shù)據(jù)庫從來不是“二選一”。我經(jīng)手的項(xiàng)目里最終落地的方案基本都是兩層內(nèi)存層負(fù)責(zé)承接高頻采集數(shù)據(jù)按時(shí)間窗口保存最近幾分鐘到幾小時(shí)的數(shù)據(jù)用于實(shí)時(shí)曲線和報(bào)警判斷SQLite 層負(fù)責(zé)把內(nèi)存層的數(shù)據(jù)批量落盤用于歷史查詢、報(bào)表導(dǎo)出和故障追溯。典型流程是采集線程把數(shù)據(jù)寫入內(nèi)存緩沖后臺落盤線程每隔一秒或幾百毫秒從內(nèi)存緩沖里取一批數(shù)據(jù)放進(jìn)一個(gè)臨時(shí)列表然后開啟事務(wù)批量插入 SQLite。這樣 SQLite 的寫入頻率大大降低每條 SQL 都能處理成百上千條記錄效率和數(shù)據(jù)安全性都得到保障。同時(shí)內(nèi)存層因?yàn)槿萘渴芟迌?nèi)存占用始終可控即使出現(xiàn)極端情況也只是丟了最近一小段來不及落盤的數(shù)據(jù)不會(huì)導(dǎo)致整個(gè)程序崩潰。這個(gè)架構(gòu)看起來簡單但很多項(xiàng)目一開始沒做好問題往往出在沒有人明確劃分“哪些數(shù)據(jù)必須落盤、哪些數(shù)據(jù)只要內(nèi)存”。我自己的原則是設(shè)備配置、報(bào)警事件、統(tǒng)計(jì)結(jié)果必須落盤原始波形按采樣率評估能落盤就落盤不能落盤就保存特征值中間計(jì)算過程只放內(nèi)存不用考慮持久化。5. 實(shí)戰(zhàn)一套采集、緩存、落盤的參考實(shí)現(xiàn)5.1 一個(gè)真實(shí)項(xiàng)目參數(shù)我在一個(gè)自動(dòng)化檢測項(xiàng)目里處理過這樣一臺設(shè)備上位機(jī)通過串口和采集卡讀取 32 通道的數(shù)據(jù)采樣周期 10ms也就是每秒產(chǎn)生 3200 條記錄。每條記錄包含時(shí)間戳、32 個(gè)浮點(diǎn)通道值和一個(gè)狀態(tài)碼序列化后大概 140 字節(jié)。需求是實(shí)時(shí)曲線展示最近 30 秒的數(shù)據(jù)歷史數(shù)據(jù)至少保留 30 天期間任何時(shí)間段都要能回放曲線程序異常退出或斷電后已經(jīng)采集的重要數(shù)據(jù)盡量不丟。這個(gè)需求下內(nèi)存數(shù)據(jù)庫和 SQLite 必須配合單靠任何一邊都搞不定。5.2 數(shù)據(jù)庫表設(shè)計(jì)與批量寫入SQLite 部分我建的表結(jié)構(gòu)大概長這樣CREATE TABLE IF NOT EXISTS samples ( id INTEGER PRIMARY KEY AUTOINCREMENT, ts INTEGER NOT NULL, ch0 REAL, ch1 REAL, ch2 REAL, -- 按實(shí)際通道數(shù)擴(kuò)展 status INTEGER ); CREATE INDEX IF NOT EXISTS idx_samples_ts ON samples(ts);時(shí)間戳用 Unix 毫秒整數(shù)存儲。不要用字符串時(shí)間或者默認(rèn)的 ISO8601 文本格式字符串查詢和排序性能都差一大截尤其在數(shù)據(jù)量上來之后。寫入時(shí)不要把單條 INSERT 暴露給采集線程整理成一個(gè)批量寫入方法。下面的代碼用 Microsoft.Data.Sqlite 實(shí)現(xiàn)核心是“一個(gè)事務(wù)、一條命令、循環(huán)復(fù)用參數(shù)”public void WriteSamples(ListSampleData samples) { if (samples.Count 0) return; using var conn new SqliteConnection(_connectionString); conn.Open(); using var tx conn.BeginTransaction(); using var cmd conn.CreateCommand(); cmd.Transaction tx; cmd.CommandText INSERT INTO samples (ts, ch0, ch1, ch2, status) VALUES ($ts, $ch0, $ch1, $ch2, $status); ; var pTs cmd.Parameters.Add($ts, SqliteType.Integer); var pCh0 cmd.Parameters.Add($ch0, SqliteType.Real); var pCh1 cmd.Parameters.Add($ch1, SqliteType.Real); var pCh2 cmd.Parameters.Add($ch2, SqliteType.Real); var pStatus cmd.Parameters.Add($status, SqliteType.Integer); foreach (var sample in samples) { pTs.Value sample.TimestampMs; pCh0.Value sample.Channel0; pCh1.Value sample.Channel1; pCh2.Value sample.Channel2; pStatus.Value sample.Status; cmd.ExecuteNonQuery(); } tx.Commit(); }注意循環(huán)里不要重復(fù)調(diào)用cmd.Parameters.Clear()或者每次都新建 Command那會(huì)讓事務(wù)的性能優(yōu)勢大打折扣。我實(shí)測過每次開啟新命令插入一萬條數(shù)據(jù)可能要好幾秒用上面這種方式一萬條基本在幾十毫秒到一兩百毫秒差距非常明顯。5.3 實(shí)時(shí)內(nèi)存緩沖 定時(shí)落盤內(nèi)存層部分我用一個(gè)容量固定的環(huán)形緩沖來保存原始數(shù)據(jù)。實(shí)現(xiàn)不復(fù)雜關(guān)鍵是控制臨界區(qū)避免采集線程和落盤線程互相干擾public class RingBufferT { private readonly T[] _buffer; private readonly object _lock new object(); private int _head; private int _count; public RingBuffer(int capacity) { _buffer new T[capacity]; } public bool TryAdd(T item) { lock (_lock) { int index (_head _count) % _buffer.Length; _buffer[index] item; if (_count _buffer.Length) { // 隊(duì)列已滿時(shí)覆蓋最舊數(shù)據(jù) _head (_head 1) % _buffer.Length; } else { _count; } return true; } } public ListT TakeAll() { lock (_lock) { var result new ListT(_count); while (_count 0) { int index _head; result.Add(_buffer[index]); _head (_head 1) % _buffer.Length; _count--; } return result; } } }后臺落盤線程用Task.Run定期把內(nèi)存緩沖的數(shù)據(jù)取走批量寫入 SQLite。核心思路是“攢一批、寫一次”既控制磁盤寫頻次又把采集延遲降到最低。public class PersistenceWorker { private readonly RingBufferSampleData _buffer; private readonly SqliteStore _store; private readonly CancellationToken _ct; public PersistenceWorker(RingBufferSampleData buffer, SqliteStore store, CancellationToken ct) { _buffer buffer; _store store; _ct ct; } public async Task RunAsync() { while (!_ct.IsCancellationRequested) { await Task.Delay(500, _ct).ConfigureAwait(false); var batch _buffer.TakeAll(); if (batch.Count 0) { try { _store.WriteSamples(batch); } catch (Exception ex) { // 記錄異常把數(shù)據(jù)重新放回緩沖或者單獨(dú)寫入異常文件 Debug.WriteLine($Write failed: {ex.Message}); } } } // 退出前把剩余數(shù)據(jù)刷一遍 var rest _buffer.TakeAll(); if (rest.Count 0) { _store.WriteSamples(rest); } } }一個(gè)小細(xì)節(jié)落盤線程從緩沖里把數(shù)據(jù)取走后內(nèi)存里就沒有這些數(shù)據(jù)了。如果軟件在寫入 SQLite 之前崩潰這批數(shù)據(jù)就丟了。所以我一般在程序退出時(shí)做一次強(qiáng)制 flush同時(shí)把落盤周期控制在 1 秒以內(nèi)即使偶爾丟數(shù)據(jù)也只會(huì)丟失最近一秒的數(shù)據(jù)對大多數(shù)設(shè)備監(jiān)控場景來說是可以接受的。5.4 讀取歷史曲線防 UI 卡頓的一次性查詢歷史曲線回放時(shí)SQLite 的查詢壓力集中在“按時(shí)間段取大量數(shù)據(jù)”。如果一次性把整個(gè)時(shí)間窗口的數(shù)據(jù)全部加載到 UI 線程界面必然卡頓。我通常用異步查詢加分段加載的方式第一次只查詢總點(diǎn)數(shù)和首尾時(shí)間然后按縮放級別分批取數(shù)據(jù)。public async TaskListSampleData QueryRangeAsync(long startMs, long endMs, int limit) { await using var conn new SqliteConnection(_connectionString); await conn.OpenAsync(); using var cmd conn.CreateCommand(); cmd.CommandText SELECT ts, ch0, ch1, ch2, status FROM samples WHERE ts $start AND ts $end ORDER BY ts LIMIT $limit; ; cmd.Parameters.AddWithValue($start, startMs); cmd.Parameters.AddWithValue($end, endMs); cmd.Parameters.AddWithValue($limit, limit); var result new ListSampleData(); await using var reader await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { result.Add(new SampleData { TimestampMs reader.GetInt64(0), Channel0 reader.GetFloat(1), Channel1 reader.GetFloat(2), Channel2 reader.GetFloat(3), Status reader.GetInt32(4) }); } return result; }這里L(fēng)IMIT不是單純限制“最多取多少條”而是在查詢策略上配合降采樣如果時(shí)間窗口很大就直接查詢一個(gè)降采樣后的統(tǒng)計(jì)值比如每 10 秒一個(gè)平均/最大值。這種“先粗后細(xì)”的查詢方式能讓歷史曲線在絕大多數(shù)機(jī)器上都能流暢回放。6. 常見問題與排查技巧實(shí)錄6.1 database is locked 到底是誰的鍋我相信不少人都被 SQLite 的“database is locked”折磨過。排查思路強(qiáng)烈建議按順序來先確認(rèn)是不是沒開 WAL再確認(rèn)是不是連接串沒有啟用連接池接著看代碼里有沒有長時(shí)間占用事務(wù)、有沒有忘記了Dispose的 DataReader最后再檢查是不是有第三方工具在用獨(dú)占模式打開數(shù)據(jù)庫文件。我之前遇到過一個(gè)很隱蔽的問題程序里有一個(gè)定時(shí)任務(wù)在后臺統(tǒng)計(jì)一天的報(bào)警數(shù)查詢語句寫得特別慢掃了全表幾百萬行記錄。每次這個(gè)統(tǒng)計(jì)任務(wù)執(zhí)行時(shí)大批量寫入就會(huì)被阻塞表現(xiàn)為采集線程那邊的 INSERT 報(bào)鎖錯(cuò)誤。后來把統(tǒng)計(jì)查詢加上索引并改成只掃最近一天的數(shù)據(jù)問題直接消失。所以“鎖庫”很多時(shí)候不是 SQLite 的并發(fā)能力不行而是你后臺有一個(gè)慢查詢把鎖持有時(shí)間拉長了。6.2 C# 調(diào)用原生庫時(shí)的 Access Violation別只怪三方庫做上位機(jī)的人經(jīng)常會(huì)調(diào)用各種 C DLL 或者采集卡的 SDK。搜索詞里有個(gè)非常經(jīng)典的問題C# 調(diào)用 C 時(shí)出現(xiàn)Access Violation c0000005。這個(gè)報(bào)錯(cuò)幾乎都是內(nèi)存訪問越界或者函數(shù)調(diào)用約定不一致導(dǎo)致的和 SQLite 本身不大相關(guān)但如果你用微軟官方封裝之外的舊版 SQLite 庫也可能因?yàn)?C 版本不匹配、指針生命周期管理不當(dāng)在 GC 回收后觸發(fā)類似崩潰。我自己的處理原則是能用官方托管庫就用官方托管庫Microsoft.Data.Sqlite內(nèi)部對 SQLite 原生庫做好了封裝避免手寫 DllImport 時(shí)踩內(nèi)存坑。如果必須通過 P/Invoke 調(diào)用第三方 C 庫一定要在調(diào)用處固定好緩沖區(qū)用Marshal.AllocHGlobal分配原生內(nèi)存并在 finally 中釋放千萬不要把托管數(shù)組直接交給在后臺線程運(yùn)行的原生函數(shù)而不做固定。6.3 寫入變慢時(shí)先看是不是事務(wù)粒度太小很多“SQLite 越用越慢”的報(bào)告最后查下來都是一個(gè)原因每一條數(shù)據(jù)都單獨(dú)開一個(gè)事務(wù)提交。事務(wù)本身有開銷每條都提交等于把每一條寫入都放大成一次磁盤同步自然慢得離譜。解決方式非常直接用批量提交把 500ms 到 1 秒內(nèi)攢下的數(shù)據(jù)放同一個(gè)事務(wù)。事務(wù)提交間隔也不能調(diào)到太久否則內(nèi)存緩沖會(huì)堆積很多數(shù)據(jù)程序異常退出時(shí)的丟失窗口變大。我一般以 1 秒為上限如果一秒攢的數(shù)據(jù)超過五千條就縮短間隔到 500ms保證單次事務(wù)條數(shù)在一個(gè)合理范圍。6.4 時(shí)間戳亂序?qū)е虑€錯(cuò)亂還有一個(gè)經(jīng)常被忽視的問題采集線程和落盤線程時(shí)間戳生成方式不統(tǒng)一。有的數(shù)據(jù)用采集時(shí)的時(shí)間有的數(shù)據(jù)用落盤時(shí)的時(shí)間結(jié)果就是歷史曲線回放時(shí)出現(xiàn)時(shí)間倒流或者亂序。統(tǒng)一策略很重要。我建議所有數(shù)據(jù)在采集線程進(jìn)入內(nèi)存緩沖的那一刻就打好時(shí)間戳之后無論內(nèi)存緩沖、落盤還是查詢都不要再重新賦值。時(shí)間戳統(tǒng)一用DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()生成整數(shù)毫秒值避免本地時(shí)區(qū)、夏令時(shí)這類問題影響排序。6.5 數(shù)據(jù)庫文件加密與備份的小技巧有人問 SQLite 數(shù)據(jù)庫文件能否加密。答案是官方標(biāo)準(zhǔn)版不帶加密需要走 SEESQLite Encryption Extension或者社區(qū)方案。但加密這種事在上位機(jī)項(xiàng)目里要慎重因?yàn)槟阋B同打開文件的程序一起考慮。如果只是怕客戶把數(shù)據(jù)庫文件拷走查看不如用輕量級的整體目錄加密或者把數(shù)據(jù)庫放在程序數(shù)據(jù)目錄并通過訪問控制限制權(quán)限實(shí)測成本更低。備份方面我推薦程序定期執(zhí)行一次“全量復(fù)制”在程序空閑時(shí)段把主數(shù)據(jù)庫文件復(fù)制到備份目錄保留最近三份。WAL 模式下直接復(fù)制主庫文件并不完整穩(wěn)妥的做法是執(zhí)行一次PRAGMA wal_checkpoint(TRUNCATE);讓 WAL 文件合并回主庫再復(fù)制主庫文件。7. 最后分享一點(diǎn)項(xiàng)目經(jīng)驗(yàn)在我做過的上位機(jī)項(xiàng)目里數(shù)據(jù)持久化這塊踩的坑不算少但總結(jié)下來其實(shí)就是“分層、批量化、留備份”。不管是 SQLite 還是實(shí)時(shí)內(nèi)存數(shù)據(jù)庫都不需要追求極致的性能參數(shù)而要把重心放在“數(shù)據(jù)流是否順暢”“故障時(shí)能不能恢復(fù)”“查詢時(shí)用戶是否覺得卡”這三件實(shí)際的事情上。如果新項(xiàng)目讓我從零開始設(shè)計(jì)我會(huì)先把數(shù)據(jù)分類和時(shí)間戳規(guī)范定死再畫數(shù)據(jù)流圖確認(rèn)各層職責(zé)然后才動(dòng)手寫代碼。SQLite 作為歷史存儲層非?？煽窟M(jìn)程內(nèi)內(nèi)存緩沖作為實(shí)時(shí)處理層非常高效兩者組合起來能撐住絕大多數(shù)工業(yè)現(xiàn)場的數(shù)據(jù)需求。真到數(shù)據(jù)量爆炸的時(shí)候再把內(nèi)存層換成獨(dú)立時(shí)序數(shù)據(jù)庫SQLite 層改成歸檔服務(wù)路是通的。最后一個(gè)小建議任何數(shù)據(jù)持久化方案都要在項(xiàng)目早期就做一次連續(xù)寫入的壓測讓設(shè)備跑兩三個(gè)小時(shí)觀察數(shù)據(jù)庫文件大小、寫入延遲、內(nèi)存占用和 CPU 占用。早期發(fā)現(xiàn)問題改代碼永遠(yuǎn)比產(chǎn)品上線之后在現(xiàn)場救火要輕松得多。