SQLite 写入提速：WAL、批量事务与索引取舍的实测数据

行情、日志、指标这类「高频追加、偶尔范围查询」的数据，用 SQLite 落盘是最省事的选择。但很多人第一次把采集脚本接上去就会发现：每秒只能写两千多行，一个交易日的分钟线要写十几分钟。

直觉会告诉你「批量插入」。这个直觉是对的，但不完整——我在本机把五种组合拆开跑了一遍，发现最大的杠杆不是批量，而是 journal_mode 与 synchronous 的组合：同样是「每行提交」，换掉这两个 pragma，吞吐差了 40 倍。

下面所有数字都是本机真实跑出来的，不是估算。

一、先建立成本模型：一次 commit 到底贵在哪

SQLite 的持久化保证来自「提交时必须把日志刷到稳定存储」。用吞吐的语言写出来：

单次提交延迟  t_commit = t_parse + t_btree + k · t_fsync
稳态吞吐上界  T_max   = 1 / (t_parse + t_btree + k · t_fsync)

其中 k 是这次提交需要触发的 fsync 次数：

t_fsync 在消费级 SSD 上通常是 50～500 µs 量级，比解析和 B-tree 插入（微秒级）高两个数量级。所以只要 k ≥ 1 且「一行一提交」，吞吐就被 fsync 死死钉住——这是模型层面的天花板，任何代码优化都绕不过去，只能通过减少 commit 次数来摊薄。

这解释了后面所有数据。

二、实测环境与方法

每个场景都从删除旧库文件后重建开始，避免上一个场景的页缓存污染。

三、第一轮：五种组合的对照

同样的 10 万行，只改三个开关：

* WAL 模式下数据先落在 -wal 文件里，主库文件在 checkpoint 前几乎不增长（所以第一轮采样时主库大小显示为 0）。

三个可以直接读出来的结论：

1. A → B：批量提交把 39.6 秒压到 0.052 秒，761 倍。 单行成本从 395.8 µs 降到 0.52 µs，正好是「每次提交 2 次 fsync」与「整个事务 2 次 fsync」的差别，和模型吻合。
2. A → D：只改 pragma 不批量，也能从 39.6 秒降到 0.967 秒（第二轮 batch_1 数据），40.9 倍。 单行成本 395.8 µs → 9.67 µs。这说明 journal_mode+synchronous 是独立的第二根杠杆，很多人只记住了「批量」而漏了它。
3. E：加 2 个复合索引，写入慢 6.5 倍（0.059 → 0.387 s），但范围读快 30 倍（2.37 → 0.08 ms）。 索引不是免费的，它是拿写换读。

四、第二轮：提交粒度到底多细才够

固定 WAL + NORMAL，只改「每多少行 commit 一次」：

曲线在 1,000～10,000 行/事务 之间就基本饱和了。这很关键：不需要为了性能把一整天的数据塞进一个巨型事务——那会带来两个副作用：

• 事务期间的 -wal 文件膨胀，内存和磁盘同时吃紧；
• 一旦中途失败，整个事务回滚，前面几万行白写。

工程上的甜点区是 1,000～10,000 行/事务：拿到 86%～100% 的峰值吞吐，同时把失败重做的代价控制在可接受范围。

五、索引建在插入前还是插入后

固定 1 个复合索引 (sym, ts)：

两者几乎持平（差 3%）。所以「先删索引、批量导入、再重建」这个流传很广的做法，在单索引、10 万行这个量级上收益可以忽略。它真正有价值的场景是：

• 索引数量多（比如 5 个以上），每个索引都要在插入时维护 B-tree；
• 数据量大（千万行级），索引重建的排序成本可以被批量导入的吞吐优势覆盖；
• 导入过程中不查这些索引。

换句话说：索引取舍要看「索引数 × 行数」，不是看「有没有索引」。我这里的 2 索引场景已经让写入慢了 6.5 倍，说明索引维护成本是近似线性叠加的。

六、可运行代码

下面是可以直接复现上面数字的最小脚本（Python 3.11+，无第三方依赖）：

import sqlite3, os, time, json

N = 100_000
BASE = '/tmp/bench1109'
os.makedirs(BASE, exist_ok=True)

def fresh(name):
    p = os.path.join(BASE, name + '.db')
    for s in ('', '-wal', '-shm'):
        try: os.remove(p + s)
        except FileNotFoundError: pass
    return p

def total_mb(p):
    return round(sum(os.path.getsize(p + s) for s in ('', '-wal') if os.path.exists(p + s)) / 1048576, 2)

def rows_gen(a, b):
    return ((f'S{i % 500:03d}', 1_700_000_000 + i, 10.0 + i % 1000 / 100, i % 10000)
            for i in range(a, b))

def bench(name, journal='WAL', sync='NORMAL', batch=1000, indexes=0):
    p = fresh(name)
    con = sqlite3.connect(p)
    con.execute(f'PRAGMA journal_mode={journal}')
    con.execute(f'PRAGMA synchronous={sync}')
    con.execute('CREATE TABLE t(id INTEGER PRIMARY KEY, sym TEXT, ts INTEGER, px REAL, qty INTEGER)')
    for i in range(indexes):
        con.execute(f'CREATE INDEX idx_{i} ON t(sym, ts)')
    con.commit()

    t0 = time.perf_counter()
    if batch == 1:
        sql = 'INSERT INTO t(sym,ts,px,qty) VALUES(?,?,?,?)'
        for r in rows_gen(0, N):
            con.execute(sql, r)
            con.commit()
    else:
        for a in range(0, N, batch):
            con.execute('BEGIN')
            con.executemany('INSERT INTO t(sym,ts,px,qty) VALUES(?,?,?,?)',
                            rows_gen(a, min(a + batch, N)))
            con.commit()
    el = time.perf_counter() - t0

    # 范围读：某个代码一整天
    t1 = time.perf_counter()
    n = con.execute('SELECT count(*) FROM t WHERE sym=? AND ts BETWEEN ? AND ?',
                    ('S007', 1_700_000_000, 1_700_086_400)).fetchone()[0]
    rd = (time.perf_counter() - t1) * 1000
    con.close()
    return {'case': name, 'insert_s': round(el, 3), 'rows_per_s': int(N / el),
            'size_mb': total_mb(p), 'read_ms': round(rd, 2), 'read_rows': n}

print(json.dumps([
    bench('A_perrow_delete_full', 'DELETE', 'FULL', 1, 0),
    bench('D_wal_normal_1k', 'WAL', 'NORMAL', 1000, 0),
    bench('E_wal_normal_1k_idx2', 'WAL', 'NORMAL', 1000, 2),
], ensure_ascii=False, indent=1))

七、边界条件（这些情况下上面的数字不成立）

1. synchronous=NORMAL 会丢最后一个事务。 它保证数据库文件不损坏，但不保证最近一次提交在断电后还在。行情数据可重采就无所谓；如果是账本，必须 FULL。
2. WAL 要求所有进程在同一台机器、同一文件系统上。 放在 NFS / SMB 共享盘上，WAL 的共享内存机制失效，写法会退回 rollback journal，甚至直接报错。
3. -wal 文件不会自己变小。 默认 wal_autocheckpoint=1000 页只在写入时顺带 checkpoint，文件最大尺寸不回落。需要显式 PRAGMA wal_checkpoint(TRUNCATE);。
4. executemany 传列表会吃内存。 十万行还好，千万行必须传生成器（上面代码用的是生成器表达式）。
5. 索引收益只在能命中前缀时成立。 (sym, ts) 复合索引对 WHERE ts BETWEEN ...（不带 sym）几乎无效，因为最左前缀不匹配。
6. 内存库 :memory: 不适用这套结论。 它没有 fsync 成本，批量几乎无差别。
7. journal_mode 是持久化设置，synchronous 不是。 前者写进库文件头，一次设置长期有效；后者是连接级，每次建连接都要设。

八、排查清单：写慢了先看这五条

1. 先确认 journal 与 sync 实际值，不要看代码里写了什么：

   PRAGMA journal_mode;   -- 期望 wal
   PRAGMA synchronous;    -- 期望 1(NORMAL) 或 2(FULL)
   

2. 看单行成本落在哪个量级。> 100 µs/行 → 大概率是 fsync 瓶颈（提交太频繁）；10～100 µs/行 → 索引/触发器/外键维护；< 5 µs/行 → 已经接近 B-tree 插入的极限，别再优化写入。
3. 数一下索引。SELECT count(*) FROM sqlite_master WHERE type='index'; 每个索引都会在每次插入时更新。
4. 确认没有隐式事务。Python 的 sqlite3 默认 isolation_level=''，会在 DML 前自动开事务、在 commit() 时提交。混用手写 BEGIN 会报 cannot start a transaction within a transaction。
5. 读慢 ≠ 写慢。用 EXPLAIN QUERY PLAN 确认走了索引；复合索引注意最左前缀。

九、结论

• 两根杠杆，缺一不可：提交粒度（每行 → 每 1,000～10,000 行）与 WAL + synchronous=NORMAL。前者在本机带来 761 倍，后者单独带来 40.9 倍。
• 不要追求单事务写完。1,000～10,000 行/事务已拿到 86%～100% 的峰值吞吐，且失败代价可控。
• 索引是写换读的交易：2 个索引换来写入 6.5 倍变慢、范围读 30 倍变快。按查询模式决定，不要无脑加。
• synchronous=NORMAL 是可用性取舍，不是性能开关的免费午餐：它放宽的是「最后一次提交必须落盘」，不是「数据可能损坏」。

把这几条落到采集脚本里，本机 10 万行从 39.6 秒降到 0.052 秒，一分钟级别的行情落库基本不再有感知。