性能指标采集通常具有写入频繁、字段固定、查询按时间范围展开的特点。一个轻量的监控代理可能每隔几秒就采集一次CPU、内存、磁盘IO等数据，并在本地保留若干天。对于这类需求，如果直接引入InfluxDB、TimescaleDB等时序数据库，确实能获得更丰富的聚合函数和横向扩展能力，但也会带来额外的部署依赖、资源占用与运维成本。SQLite作为嵌入式关系数据库，能够在单个文件中实现完整的事务保证和SQL查询能力，只要设计得当，完全可以承担中等规模指标存储任务。
本文会从表结构、写入优化、查询与清理几个方面展开，通过一个实际项目演示如何让SQLite在指标存储场景中稳定运行。主要目标不是追求极限写入速度，而是在可控的硬件资源下，用最少的外部服务完成可靠的本地持久化。
一、SQLite存储指标数据的优势与边界
SQLite最明显的优势是单文件部署、零配置和事务完整性。对于需要把采集程序打包发布的工具，运维人员不必单独安装数据库服务，只需要一个可执行文件和一个数据文件。相比于直接写CSV或日志文件，SQLite还能提供结构化查询、索引和事务回滚能力，这些都让后续分析更加方便。
性能指标数据很像时序数据：时间戳单调递增、字段结构固定、写入远大于更新、查询通常围绕时间范围。SQLite虽然不是专门的时序数据库，但它支持复合索引、WAL预写日志和批量事务，只要利用好这些能力，每秒写入几千条记录并不是难事。这在单机监控、边缘设备、开发环境数据采集等场景中已经足够。
当然，SQLite也有明显边界。它不适合大量客户端同时写入，多进程并发写同一个数据库文件时需要谨慎处理锁竞争。如果采集节点超过几十个，或者需要实时聚合海量数据，那么还是应该考虑专门的时序数据库。本文讨论的是单实例或轻量分布式采集中的本地存储方案。
二、核心表结构与索引设计
设计指标存储表时，最重要的是避免使用无业务意义的主键，比如自增ID或随机UUID。指标查询几乎不会按ID查找，而是按时间、主机和指标名称过滤。因此表结构可以非常精简，只保留必要字段。SQLite从3.37版本开始支持STRICT表，可以强制字段类型，避免误插入文本到数值列。
下面是一个基础的指标表结构，使用毫秒级Unix时间戳作为时间字段，主机名和指标名使用TEXT类型，指标值使用REAL类型。
CREATE TABLE metrics (
ts INTEGER NOT NULL,
host TEXT NOT NULL,
metric TEXT NOT NULL,
value REAL NOT NULL
) STRICT;
CREATE INDEX idx_metrics_host_metric_ts
ON metrics(host, metric, ts);
复合索引(host, metric, ts)能够覆盖“某台主机某个指标在某段时间范围内”的典型查询。当查询条件先过滤host和metric，再按ts排序时，SQLite可以直接使用该索引避免全表扫描。由于该索引包含查询需要的所有字段，部分聚合查询甚至可以实现覆盖索引扫描，不必回表读取原始数据。
还需注意索引不宜过多。每多一个索引，写入时都需要同步维护，会直接拉低插入性能。对于指标存储，通常保留一个按主机和指标过滤的复合索引即可。如果还经常按全局时间范围扫描，可以额外建一个ts单列索引，但要评估写入量是否能够承受。
三、WAL模式与批量事务：写入性能的关键
默认情况下，SQLite使用DELETE日志模式，写入时需要频繁操作文件锁和回滚日志，写入吞吐较低。切换到WAL模式后，写操作先追加到单独的WAL文件，读操作可以继续从主数据库读取，读写不会互相阻塞。这在指标采集场景中非常重要，因为写入循环和查询请求往往同时发生。
除了WAL模式，还需要调整同步策略和缓存大小。PRAGMA synchronous=NORMAL可以在保证基本安全的前提下减少fsync次数，PRAGMA cache_size=-64000表示分配64MB页面缓存，能显著降低磁盘IO频率。批量事务则是最直接的性能优化手段：把数百条INSERT放进一个事务，比每条都自动提交快几十倍。
import sqlite3
import time
import random
conn = sqlite3.connect("metrics.db")
conn.executescript("""
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA temp_store=MEMORY;
PRAGMA cache_size=-64000;
PRAGMA busy_timeout=5000;
""")
def insert_batch(rows):
sql = "INSERT INTO metrics(ts, host, metric, value) VALUES(?,?,?,?)"
with conn:
conn.executemany(sql, rows)
batch = []
for i in range(1000):
ts = int(time.time() * 1000)
batch.append((ts, "node-1", "cpu_usage", random.uniform(0, 100)))
insert_batch(batch)
上面的代码使用executemany配合事务上下文，可以有效减少每条INSERT的SQL解析和事务开销。更极致的做法是使用预编译语句加手动事务控制，但在Python中，executemany已经是简洁且性能不错的方式。需要注意的是，不要在循环里频繁调用commit()，否则又会退化成逐条提交。
如果采集程序是多线程的，最好为每个线程创建独立连接。如果多个进程同时写入同一个SQLite文件，即便开了WAL，写入者之间仍然会竞争写锁。此时可以设置busy_timeout，让某个进程在锁忙时等待而不是立刻报错。单进程多线程采集是最稳妥的部署方式。
四、查询与聚合：快速获取趋势
指标数据落盘后，最常见的需求是查看某台主机在某个时间段内的趋势，或者计算CPU使用率的平均值、最大值。查询时要注意不要对索引列使用函数，否则索引会失效。比如WHERE date(ts/1000, 'unixepoch') = ...就无法走ts索引，而应转换为ts的范围条件。
下面这个SQL展示如何按分钟聚合CPU指标，并返回每分钟的平均值和最大值。由于ts字段存储的是毫秒时间戳，需要除以1000后再传给strftime。
SELECT
strftime('%Y-%m-%d %H:%M', ts / 1000, 'unixepoch') AS minute,
avg(value) AS avg_value,
max(value) AS max_value
FROM metrics
WHERE host = 'node-1'
AND metric = 'cpu_usage'
AND ts >= ?
AND ts < ?
GROUP BY minute
ORDER BY minute;
查询性能在百万级数据量下仍然可以接受，但前提是查询条件必须命中复合索引。如果只按时间范围扫描所有主机和指标，SQLite需要遍历更多行，耗时会明显增加。对于需要频繁全局聚合的场景，可以考虑按主机和指标拆分存储表，或者使用SQLite的物化视图思路定期预聚合。
如果只关心最近数据，查询时应始终携带时间下界，并用ORDER BY ts DESC LIMIT 100的方式快速拿到最新记录。不要使用ORDER BY ts DESC全量排序后再截取，虽然SQLite会尝试优化，但显式LIMIT能减少排序压力。
五、数据保留与空间回收
指标数据会持续增长，如果不清理，数据库文件最终会占满磁盘。最简单的策略是定期删除超过保留期的数据，例如只保留最近30天。一次删除几十万行可能会造成长时间锁库，因此应分批删除。实际项目中可以用一个循环，每次删除10000条，直到影响行数为0。
更高效的方式是按天分表。采集程序可以每天创建一个新表，例如metrics_20260616。过期数据不需要逐行DELETE，直接DROP TABLE即可，速度极快且不会产生碎片。查询最新数据时，只需从最近几天的表中读取，历史分析则可以用UNION ALL把多个表合并。
CREATE TABLE metrics_20260616 (
ts INTEGER NOT NULL,
host TEXT NOT NULL,
metric TEXT NOT NULL,
value REAL NOT NULL
) STRICT;
-- 过期后直接删除整个表
DROP TABLE metrics_20260516;
无论使用哪种清理策略，都应该定期执行PRAGMA wal_checkpoint(TRUNCATE)来回收WAL文件占用的空间。WAL文件在写入频繁时会增长，如果不检查点，磁盘占用可能比主数据库还大。也可以使用PRAGMA incremental_vacuum逐步回收未使用页面，但不要频繁执行完整的VACUUM，它会重建整个数据库文件，耗时与文件大小成正比。
六、完整Python实战：从采集到查询
下面给出一个完整的Python示例，包含数据库初始化、模拟采集、批量写入和查询聚合。这个示例可以直接运行，也可以作为实际项目的骨架。采集部分用随机数模拟CPU和内存使用率，实际使用时替换为psutil等库读取系统指标即可。
import sqlite3
import time
import random
DB = "metrics.db"
def init_db():
conn = sqlite3.connect(DB)
conn.executescript("""
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA temp_store=MEMORY;
PRAGMA cache_size=-64000;
PRAGMA busy_timeout=5000;
CREATE TABLE IF NOT EXISTS metrics (
ts INTEGER NOT NULL,
host TEXT NOT NULL,
metric TEXT NOT NULL,
value REAL NOT NULL
) STRICT;
CREATE INDEX IF NOT EXISTS idx_metrics_host_metric_ts
ON metrics(host, metric, ts);
""")
conn.close()
def collect(host):
ts = int(time.time() * 1000)
cpu = random.uniform(0, 100)
mem = random.uniform(20, 80)
return [
(ts, host, "cpu_usage", cpu),
(ts, host, "mem_usage", mem),
]
def writer_loop(host):
conn = sqlite3.connect(DB)
rows = []
while True:
rows.extend(collect(host))
if len(rows) >= 200:
sql = "INSERT INTO metrics(ts, host, metric, value) VALUES(?,?,?,?)"
with conn:
conn.executemany(sql, rows)
rows.clear()
time.sleep(1)
init_db()
try:
writer_loop("node-1")
except KeyboardInterrupt:
print("采集已停止")
这个骨架保持了较低的复杂度，但已经能够体现WAL、批量事务和参数化语句等关键优化。运行时可以观察数据库文件大小和写入延迟，再根据实际采集频率调整批量大小。一般建议每次批量写入200到1000条，过小会导致事务开销上升，过大则可能在采集进程崩溃时丢失更多数据。
在查询侧，可以单独实现一个函数，按主机、指标和时间范围返回聚合结果。如果发现查询速度不理想，优先检查是否命中了复合索引。使用EXPLAIN QUERY PLAN可以看到SQLite选择的查询计划，从而判断索引是否被正确使用。性能优化应该以实际测量为依据，而不是盲目增加索引或调整参数。