PLC 监控系统数据持久化方案MySQL SQLite 双保险不丢数据工业监控中数据丢失是最致命的。本文详解 PLCMonitor 的双数据库架构MySQL 主存储 SQLite 本地缓存网络故障时自动降级恢复后自动同步。问题你的产线数据打算存多久很多工业监控项目上线后才发现一个问题数据说没就没了。场景后果上个月温度曲线异常数据已经清了找不到证据排查延长月底要出生产报表历史数据丢了管理层不高兴数据库文件损坏三个月白干了停电了没电时采的数据永远没了工业监控的数据存储不是能存就行而是要做到✅ 不丢数据容灾✅ 存得住容量管理✅ 查得快性能✅ 随时恢复备份为什么用两个数据库PLCMonitor 同时使用SQLite和MySQL分工明确数据库角色特点SQLite本地缓存 故障切换单文件、免安装、离线可用MySQL主存储并发支持、远程访问、多客户端共享核心设计理念优先 MySQL失败走 SQLite采集数据 ↓ 优先写入 MySQL主库 ↓ 成功 ├─ YES → 记录 synced1 └─ NO → 写入 SQLite备库 ↓ MySQL 恢复后 SQLite → MySQL 自动同步效果正常运行时数据存在 MySQL多客户端可查网络故障时数据不会丢缓存在 SQLite网络恢复后积压数据自动补传为什么不选其它数据库数据库为什么不选PostgreSQL功能强大但重量级部署成本高MongoDB文档型时序数据量巨大时查询性能不如关系型InfluxDB专用时序数据库门槛高社区版不支持双写降级Redis内存数据库断电即丢不适合长期持久化SQL Server / Oracle企业级商业数据库授权费数万起步结论SQLite MySQL 组合兼顾离线可用和远程共享成本最低、部署最简单。数据量计算500 标签每秒采集假设场景500 个标签每秒采集一次。时间维度记录数每秒500 条每分钟3 万条每小时180 万条每天4320 万条每月13 亿条每年157 亿条以每条记录约 40 字节计算每年原始数据约 620 GB。存储不是问题问题在查询。为什么不能直接查原始数据查询过去一年温度趋势一年 157 亿条数据筛选温度标签 → 至少千万级扫描全表扫描一次查询 30 秒到数分钟时间越长需要的不是原始数据而是聚合数据。聚合策略时间越长粒度越粗工业监控的本质是看趋势而不是看瞬时值。查询目的时间跨度需要的精度实时报警1 秒精确到每个采样点当前状态1 分钟最近值足够今天趋势1 小时每分钟一个点即可本周分析1 天每小时一个点即可年度总结1 个月每天一个点即可聚合表设计粒度表名数据源触发间隔保留时长聚合规则秒级data_secondPLC 采集实时7 天原始值分钟级data_minute秒级数据每分钟30 天平均值 最后值小时级data_hour分钟数据每小时1 年平均值 最后值天级data_day小时数据每天永久平均值 最后值什么是平均值 最后值以温度为例一分钟内 60 次采样时间14:00:0114:00:02…14:00:59原始值25.125.3…25.4聚合后变成一条记录timestamp: 2024-06-01 14:00:00 avg_value: 25.2 last_value: 25.4好处查一年数据只需查data_hour表438 万条而不是data_second157 亿条。EAV 模型标签随意增减传统方式的问题每加一个标签就要改表结构——维护噩梦。EAV 方案EAV Entity-Attribute-Value实体-属性-值动态标签友好CREATETABLEdata_second(idINTEGERPRIMARYKEYAUTOINCREMENT,data_idBIGINTNOTNULL,-- 全局唯一 IDtag_nameVARCHAR(255)NOTNULL,-- 标签名valueDOUBLENOTNULL,-- 值qualityTINYINTDEFAULT0,-- 质量0好1坏timestampDATETIMENOTNULL,-- 时间syncedTINYINTDEFAULT0-- 同步标记);无论加多少标签表结构不动。data_id使用(当前毫秒时间戳 20) | 随机数生成确保跨机器、跨时间唯一。写入优化异步批量问题500 次单独 INSERT 太慢每秒 500 个标签每条单独插入500 次 SQL 解析500 次磁盘 IO500 次锁竞争太慢了。方案队列 批量写入┌─────────────┐ ┌─────────────┐ ┌──────────────┐ │ PLC 采集线程 │ ──→ │ 异步队列 │ ──→ │ 数据库工作线程 │ │ (每秒一次) │ │ QQueue │ │ 每 200ms 处理 │ └─────────────┘ └─────────────┘ └──────┬───────┘ │ 批量 500 条 INSERT具体做法PLC 采集线程把数据放入异步队列数据库工作线程独立运行每 200ms 取出一批最多 500 条一批用预处理语句批量写入成功写入 MySQL 后标记synced1500 次插入变成 1 次批量插入性能提升数百倍。主备切换MySQL 挂了怎么办步骤 1: 尝试写入 MySQL 步骤 2: 失败 → 标记 MySQL 不可用 步骤 3: 下次写入直接走 SQLite 步骤 4: 每 30 秒检测 MySQL 是否恢复 步骤 5: 恢复后自动同步积压数据历史查询自动选表根据查询跨度系统自动选择最优表时间跨度查询表原因≤ 7 天data_second精度高≤ 30 天data_minute数据量少查询快≤ 1 年data_hour天级精度足够 1 年data_day长期趋势查询性能场景数据量响应时间查最近 1 小时3 万条 100ms查最近 7 天8.4 万条 200ms查最近 1 年438 万条 2s关键是聚合表数据量小且命中索引。数据清理不越存越多每日凌晨自动执行-- 清理 7 天前的秒级数据DELETEFROMdata_secondWHEREtimestampdatetime(now,-7 days);-- 清理 30 天前的分钟级数据DELETEFROMdata_minuteWHEREtimestampdatetime(now,-30 days);-- SQLite 回收空间VACUUM;SQLite 文件超过 300MB 时发出警告支持手动或配置自动清理策略。总结能力实现方式不怕丢数据双数据库 自动降级不怕数据量大异步批量写入长期数据查得快多级聚合标签随意增减EAV 模型工业监控的数据存储不是技术深度的竞赛而是可靠性的较量。参考资料PLCMonitor 开源项目https://github.com/freddiezhang1990/plcmonitor标签#PLC#工业监控#上位机#MySQL#SQLite#数据库设计 数据持久化方案_MySQLSQLite双保险
PLC 监控系统数据持久化方案MySQL SQLite 双保险不丢数据工业监控中数据丢失是最致命的。本文详解 PLCMonitor 的双数据库架构MySQL 主存储 SQLite 本地缓存网络故障时自动降级恢复后自动同步。问题你的产线数据打算存多久很多工业监控项目上线后才发现一个问题数据说没就没了。场景后果上个月温度曲线异常数据已经清了找不到证据排查延长月底要出生产报表历史数据丢了管理层不高兴数据库文件损坏三个月白干了停电了没电时采的数据永远没了工业监控的数据存储不是能存就行而是要做到✅ 不丢数据容灾✅ 存得住容量管理✅ 查得快性能✅ 随时恢复备份为什么用两个数据库PLCMonitor 同时使用SQLite和MySQL分工明确数据库角色特点SQLite本地缓存 故障切换单文件、免安装、离线可用MySQL主存储并发支持、远程访问、多客户端共享核心设计理念优先 MySQL失败走 SQLite采集数据 ↓ 优先写入 MySQL主库 ↓ 成功 ├─ YES → 记录 synced1 └─ NO → 写入 SQLite备库 ↓ MySQL 恢复后 SQLite → MySQL 自动同步效果正常运行时数据存在 MySQL多客户端可查网络故障时数据不会丢缓存在 SQLite网络恢复后积压数据自动补传为什么不选其它数据库数据库为什么不选PostgreSQL功能强大但重量级部署成本高MongoDB文档型时序数据量巨大时查询性能不如关系型InfluxDB专用时序数据库门槛高社区版不支持双写降级Redis内存数据库断电即丢不适合长期持久化SQL Server / Oracle企业级商业数据库授权费数万起步结论SQLite MySQL 组合兼顾离线可用和远程共享成本最低、部署最简单。数据量计算500 标签每秒采集假设场景500 个标签每秒采集一次。时间维度记录数每秒500 条每分钟3 万条每小时180 万条每天4320 万条每月13 亿条每年157 亿条以每条记录约 40 字节计算每年原始数据约 620 GB。存储不是问题问题在查询。为什么不能直接查原始数据查询过去一年温度趋势一年 157 亿条数据筛选温度标签 → 至少千万级扫描全表扫描一次查询 30 秒到数分钟时间越长需要的不是原始数据而是聚合数据。聚合策略时间越长粒度越粗工业监控的本质是看趋势而不是看瞬时值。查询目的时间跨度需要的精度实时报警1 秒精确到每个采样点当前状态1 分钟最近值足够今天趋势1 小时每分钟一个点即可本周分析1 天每小时一个点即可年度总结1 个月每天一个点即可聚合表设计粒度表名数据源触发间隔保留时长聚合规则秒级data_secondPLC 采集实时7 天原始值分钟级data_minute秒级数据每分钟30 天平均值 最后值小时级data_hour分钟数据每小时1 年平均值 最后值天级data_day小时数据每天永久平均值 最后值什么是平均值 最后值以温度为例一分钟内 60 次采样时间14:00:0114:00:02…14:00:59原始值25.125.3…25.4聚合后变成一条记录timestamp: 2024-06-01 14:00:00 avg_value: 25.2 last_value: 25.4好处查一年数据只需查data_hour表438 万条而不是data_second157 亿条。EAV 模型标签随意增减传统方式的问题每加一个标签就要改表结构——维护噩梦。EAV 方案EAV Entity-Attribute-Value实体-属性-值动态标签友好CREATETABLEdata_second(idINTEGERPRIMARYKEYAUTOINCREMENT,data_idBIGINTNOTNULL,-- 全局唯一 IDtag_nameVARCHAR(255)NOTNULL,-- 标签名valueDOUBLENOTNULL,-- 值qualityTINYINTDEFAULT0,-- 质量0好1坏timestampDATETIMENOTNULL,-- 时间syncedTINYINTDEFAULT0-- 同步标记);无论加多少标签表结构不动。data_id使用(当前毫秒时间戳 20) | 随机数生成确保跨机器、跨时间唯一。写入优化异步批量问题500 次单独 INSERT 太慢每秒 500 个标签每条单独插入500 次 SQL 解析500 次磁盘 IO500 次锁竞争太慢了。方案队列 批量写入┌─────────────┐ ┌─────────────┐ ┌──────────────┐ │ PLC 采集线程 │ ──→ │ 异步队列 │ ──→ │ 数据库工作线程 │ │ (每秒一次) │ │ QQueue │ │ 每 200ms 处理 │ └─────────────┘ └─────────────┘ └──────┬───────┘ │ 批量 500 条 INSERT具体做法PLC 采集线程把数据放入异步队列数据库工作线程独立运行每 200ms 取出一批最多 500 条一批用预处理语句批量写入成功写入 MySQL 后标记synced1500 次插入变成 1 次批量插入性能提升数百倍。主备切换MySQL 挂了怎么办步骤 1: 尝试写入 MySQL 步骤 2: 失败 → 标记 MySQL 不可用 步骤 3: 下次写入直接走 SQLite 步骤 4: 每 30 秒检测 MySQL 是否恢复 步骤 5: 恢复后自动同步积压数据历史查询自动选表根据查询跨度系统自动选择最优表时间跨度查询表原因≤ 7 天data_second精度高≤ 30 天data_minute数据量少查询快≤ 1 年data_hour天级精度足够 1 年data_day长期趋势查询性能场景数据量响应时间查最近 1 小时3 万条 100ms查最近 7 天8.4 万条 200ms查最近 1 年438 万条 2s关键是聚合表数据量小且命中索引。数据清理不越存越多每日凌晨自动执行-- 清理 7 天前的秒级数据DELETEFROMdata_secondWHEREtimestampdatetime(now,-7 days);-- 清理 30 天前的分钟级数据DELETEFROMdata_minuteWHEREtimestampdatetime(now,-30 days);-- SQLite 回收空间VACUUM;SQLite 文件超过 300MB 时发出警告支持手动或配置自动清理策略。总结能力实现方式不怕丢数据双数据库 自动降级不怕数据量大异步批量写入长期数据查得快多级聚合标签随意增减EAV 模型工业监控的数据存储不是技术深度的竞赛而是可靠性的较量。参考资料PLCMonitor 开源项目https://github.com/freddiezhang1990/plcmonitor标签#PLC#工业监控#上位机#MySQL#SQLite#数据库设计