上位机历史数据存哪查得快？SQLite 本地数据库选型：索引设计、百万行批量插入、分表归档与 SQL Server/LiteDB 对比

一句话说清楚：上位机本地历史数据首选 SQLite——整个库就是一个文件、零部署、事务可靠，百万行级查询配合索引在毫秒级返回。但写法有讲究：批量写入必须包在一个事务里（快慢差上百倍），高频采集场景开 WAL 模式，数据量持续增长就按月分表并归档冷数据。用错地方（高并发多写入、多终端共享）它也会坑你。

SEC 01历史数据到底存哪？先看四种选择

上位机软件存历史数据，常见候选就四个，我按这些年项目里的实际取舍列清楚：

判断标准很实在：数据只在这台工控机上产生和查询，SQLite；多台机器的数据要汇总到一处，那是服务端数据库的活，别让单机库硬扛。

SEC 02表结构与连接初始化

CREATE TABLE hist_202609 (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    ts        INTEGER NOT NULL,   -- 存Unix毫秒，查询排序都方便
    point_id  INTEGER NOT NULL,
    value     REAL    NOT NULL,
    quality   INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX idx_202609_ts_point ON hist_202609(ts, point_id);

var conn = new SqliteConnection(
    "Data Source=data/hist.db;Pooling=True;");
using (var c = conn.CreateCommand())
{
    c.CommandText =
      "PRAGMA journal_mode=WAL;" +   // 读写不互斥
      "PRAGMA synchronous=NORMAL;";    // 兼顾安全与速度
    c.ExecuteNonQuery();
}

两个设计细节：时间用整数毫秒而不是字符串，比较、索引、时区处理全都省事；点位编号单独成列并和时间组成联合索引，因为最常见的查询就是"某点位在某时间段的值"。

SEC 03索引怎么建，查询才能用上？

很多人建完表查询还是慢，多半是两个原因：没建对索引，或者写的 SQL 让索引用不上。

• 联合索引遵循最左前缀：索引 (ts, point_id) 支持"按时间""时间+点位"查询，但只按 point_id 查用不上它；
• 别对索引列做函数运算：WHERE datetime(ts) > ... 会让索引失效，改成对参数做换算再比较；
• 避免 SELECT *：只取需要的列，配合索引覆盖少回表。

拿不准就看执行计划：在 SQL 前加 EXPLAIN QUERY PLAN，出现 USING INDEX 说明走了索引；看到 SCAN TABLE（全表扫描）就得回去查条件和索引。这个命令是 SQLite 调优最趁手的工具，每次写新查询都看一眼。

SEC 04批量插入为什么能差上百倍？

先理解 SQLite 的事务开销：默认情况下，每条 INSERT 都是一个自动事务，提交时要把数据刷盘并等磁盘确认。机械盘上一次刷盘约 10ms，一秒最多插一百来条——时间全耗在等磁盘，不是算力不够。

把一万条 INSERT 包进一个显式事务，只在最后提交时刷一次盘，写入直接提升两个数量级。再叠加两个优化：

• Prepare 复用：命令只编译一次，循环里改参数值，省去重复解析 SQL；
• WAL 模式：写入追加到 WAL 文件，读操作不阻塞，突发写入更顺。

SEC 05C# 批量写入完整代码

public int BatchInsert(IReadOnlyList<Sample> samples)
{
    using var conn = new SqliteConnection(_cs);
    conn.Open();
    using var tran = conn.BeginTransaction();

    using var cmd = conn.CreateCommand();
    cmd.Transaction = tran;
    cmd.CommandText = "INSERT INTO hist_202609
        (ts,point_id,value,quality) VALUES($ts,$p,$v,$q)";
    var pTs = cmd.Parameters.Add("$ts", SqliteType.Integer);
    var pP  = cmd.Parameters.Add("$p",  SqliteType.Integer);
    var pV  = cmd.Parameters.Add("$v",  SqliteType.Real);
    var pQ  = cmd.Parameters.Add("$q",  SqliteType.Integer);
    cmd.Prepare();   // 只编译一次

    foreach (var s in samples)
    {
        pTs.Value = s.Ts; pP.Value = s.PointId;
        pV.Value = s.Value; pQ.Value = s.Quality;
        cmd.ExecuteNonQuery();
    }
    tran.Commit();
    return samples.Count;
}

高频采集时的推荐结构：采集线程把数据攒进内存队列，写入线程每秒（或攒够一批）取出调一次 BatchInsert。既保证批量，又把数据库操作和实时采集隔开。

SEC 06数据一直涨：按月分表与归档

单表几百万行后，索引变大、查询变慢、备份笨重。时序数据的经典做法是按月（或按周）分表，表名带月份（hist_202609、hist_202610），程序按时间路由到对应表；跨月查询就查两张表再合并。

• 归档冷数据：把半年前的表导出成独立的 .db 文件（ATTACH 后 CREATE TABLE ... AS SELECT），主库里 DROP 掉，需要时再挂上；
• 删数据注意 VACUUM：DELETE/DROP 不会自动缩小文件，空闲时执行 VACUUM 回收空间（会锁库，挑停机窗口）；
• 自动建表：每月初、或写入前检测表是否存在，缺了就按模板建，别让运维手动操作。

SEC 07WAL、并发写入与备份的坑

• WAL 会多两个文件（.wal、.shm）：正常关闭会自动合并删除，异常退出后残留无妨，下次打开自动恢复。备份时别只拷 .db——要么用 PRAGMA wal_checkpoint(TRUNCATE) 先合并，要么三个文件一起拷；
• 同一时刻只允许一个写者：多线程写要靠队列串行化，否则频繁 database is locked。可以设 busy_timeout 让后来的写等待一小段，而不是立刻报错；
• 网络盘/共享目录不要放库文件：SMB/NFS 上文件锁不可靠，断电易损坏，SQLite 只支持本机磁盘；
• 加密需求：用 SQLCipher 或 SEE，普通版数据文件可直接被拷走，敏感数据要知悉。

SEC 08实测数据与验证方法

在 i5、普通 SSD 上我做过一组对比，供你建立量级概念（实际随硬件波动）：

1. 灌百万行测试：先验证批量写入耗时和文件大小是否符合预期；
2. EXPLAIN QUERY PLAN：对每类核心查询确认走索引；
3. 断电演练：写入中直接杀进程、断电重启，库应能正常打开、数据回退到上一个完整事务——验证事务的可靠性承诺；
4. 长跑观察：连续运行数周，配合分表归档，监控文件体积和查询耗时是否平稳。