SQLite 大数据量优化：WAL 模式、批量事务、游标分页和索引策略

Soulock
2026年05月27日
- 技术

SQLite 单数据库文件理论上限约 281TB，实际几十 GB 均可正常运行，性能瓶颈通常来自配置和使用方式，而非 SQLite 本身。

必开：WAL 模式

PRAGMA journal_mode=WAL;

默认的 DELETE journal 模式写入时会锁全库，WAL 模式允许读写并发，大幅提升写入性能。需要在每次连接时设置（或写入配置持久化）。

减少同步次数

-- 兼顾安全和性能（推荐）
PRAGMA synchronous=NORMAL;

-- 最高性能（断电可能丢最近几秒数据，非关键数据可用）
PRAGMA synchronous=OFF;

批量事务（最重要的优化）

单条 INSERT 性能极差，每次写入都会触发一次磁盘同步：

// 错误写法：每条 INSERT 是独立事务
for (const item of items) {
    db.run("INSERT INTO logs VALUES (?)", item);
}

改为批量事务，性能提升 100-1000 倍：

BEGIN;
INSERT INTO logs VALUES (1, 'event_a', 1716800000);
INSERT INTO logs VALUES (2, 'event_b', 1716800001);
-- ... 批量写入
COMMIT;

Node.js（better-sqlite3）示例：

const insertMany = db.transaction((items) => {
    const stmt = db.prepare("INSERT INTO logs (id, type, ts) VALUES (?, ?, ?)");
    for (const item of items) {
        stmt.run(item.id, item.type, item.ts);
    }
});

insertMany(items);  // 一次事务写入全部

Python（sqlite3）示例：

conn.execute("BEGIN")
conn.executemany("INSERT INTO logs VALUES (?, ?, ?)", items)
conn.execute("COMMIT")

建议每批 500-5000 条，批次过大会增加内存压力。

游标分页（替代 OFFSET）

OFFSET 越大越慢，因为需要扫描并跳过前面所有行：

-- 慢：OFFSET 100万，需要扫描 100万行
SELECT * FROM logs LIMIT 20 OFFSET 1000000;

改用游标分页（Keyset Pagination）：

-- 快：直接从上次最后一条的 id 开始
SELECT * FROM logs WHERE id > ? LIMIT 20;

记录上次最后一条的 id 作为下次查询的起始点，无论翻到多后面性能都一致。

索引策略

查看查询执行计划，确认是否走索引：

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE user_id = 123 AND created_at > 1716800000;

原则：

高频 WHERE 条件字段建索引
复合查询建复合索引（字段顺序与查询条件一致）
低选择度字段（如布尔值）单独建索引意义不大
写入密集型表控制索引数量，每个索引都会拖慢写入

其他配置

-- 增大缓存（默认 -2000 即 2MB，建议改大）
PRAGMA cache_size=-32000;  -- 32MB

-- 内存临时表（减少磁盘 I/O）
PRAGMA temp_store=MEMORY;

-- 预写 WAL 检查点
PRAGMA wal_autocheckpoint=1000;

大字段（图片、视频、大 JSON、二进制 Blob）建议存文件系统，数据库只存文件路径，避免单数据库文件膨胀过快。