SQLite性能调优：PRAGMA优化、批量操作与查询计划分析

说实话，SQLite这小家伙看着轻量，但用不好照样卡得你怀疑人生。我早年接手过一个嵌入式项目，设备上跑着SQLite，数据量也就几十万条，结果写入一次要等好几秒——用户都开始敲设备了。后来一查，全是默认配置惹的祸。

今天咱们就聊聊SQLite性能调优的三个核心方向：PRAGMA参数调优、批量操作优化、查询计划分析。这三板斧砍下去，大部分性能问题都能解决。

SQLite性能调优三大核心 SQLite性能调优 PRAGMA参数调优 cache_size page_size synchronous 批量操作优化 事务包裹 预编译语句 批量插入 查询计划分析 EXPLAIN EXPLAIN QUERY PLAN 索引使用分析

一、PRAGMA参数调优：小参数，大影响

PRAGMA是SQLite提供的一套运行时配置指令。说白了，就是让你在不用重新编译SQLite的情况下，动态调整它的行为。我个人的习惯是，每个新项目启动时，第一件事就是根据场景把PRAGMA参数调一遍。

1.1 cache_size：给SQLite多分点内存

cache_size控制的是SQLite内部页缓存的大小。默认值是2000页（大约2MB，按1KB页大小算）。对于嵌入式设备来说，这个值偏保守了。

核心原则：缓存越大，磁盘I/O越少。但别贪心，内存是有限的。

我在项目中遇到过这样的情况：一个查询要扫描10万行数据，默认缓存下SQLite反复读写磁盘，慢得离谱。把cache_size调到10000页后，查询时间从3秒降到了0.3秒。

-- 设置缓存为10000页（约10MB，按1KB页大小）
PRAGMA cache_size = -10000;

-- 注意：负值表示以KB为单位，正值表示以页数为单位
-- 我个人习惯用负值，更直观
PRAGMA cache_size = -10240;  -- 10MB

我的建议：对于读多写少的场景，cache_size可以设大一些（比如设备可用内存的10%-20%）。对于写密集场景，适当减小，避免缓存刷写时的抖动。

1.2 page_size：一页定乾坤

page_size决定了SQLite读写磁盘的基本单位。默认是4096字节（4KB）。这个值在数据库创建时设定，之后就不能改了（除非重建数据库）。

page_size 适用场景 优缺点
1024 (1KB) 小数据量、频繁随机读写 节省内存，但大查询慢
4096 (4KB) 通用场景（默认） 平衡之选
8192 (8KB) 大字段、批量顺序读写 大查询快，但浪费空间
65536 (64KB) 超大数据库、数据仓库 顺序扫描极快，随机写慢 
-- 只能在创建数据库时设置
PRAGMA page_size = 8192;

-- 如果数据库已创建，需要重建
-- 我常用的做法：导出再导入
sqlite3 old.db ".dump" | sqlite3 new.db

注意：page_size一旦设定，后期修改非常麻烦。我建议你在项目初期就根据数据特征选好。如果拿不准，4096是最稳妥的选择。

1.3 synchronous：安全与性能的博弈

synchronous控制SQLite写入磁盘时的同步策略。它有三个级别：FULL、NORMAL、OFF。

  • FULL（默认）：每次写入都等待数据落盘。最安全，但最慢。
  • NORMAL：关键操作同步，非关键操作异步。折中方案。
  • OFF：完全不等待。最快，但系统崩溃可能丢数据。

我曾经在一个日志采集设备上，把synchronous从FULL改成NORMAL，写入性能提升了将近5倍。代价是什么？如果设备突然断电，可能丢失最后几毫秒的数据。对于日志场景，这个风险完全可以接受。

-- 性能优先（适合日志、缓存等可容忍少量数据丢失的场景）
PRAGMA synchronous = OFF;

-- 平衡模式（我推荐大多数场景使用）
PRAGMA synchronous = NORMAL;

-- 安全优先（金融、交易等场景）
PRAGMA synchronous = FULL;

避坑指南：我曾经在一个项目中把synchronous设为OFF，结果测试时频繁断电，数据库损坏了。后来我改用NORMAL + 定期备份的策略，既保证了性能，又降低了风险。

二、批量操作优化：别一条一条来

很多新手写SQLite代码时，习惯一条INSERT一条INSERT地执行。这在SQLite里是大忌。为什么？因为每条INSERT都隐含一个事务提交，而事务提交涉及磁盘同步，开销巨大。

2.1 事务包裹：把多条操作装进一个事务

把1000条INSERT放在一个事务里，比单独执行快几十倍。我实测过，插入10万条数据：

  • 逐条插入：约45秒
  • 事务包裹（1000条一批）：约0.8秒
-- 错误做法：每条INSERT自动提交
INSERT INTO t VALUES(1, 'a');
INSERT INTO t VALUES(2, 'b');
-- ... 10000次

-- 正确做法：手动控制事务
BEGIN TRANSACTION;
INSERT INTO t VALUES(1, 'a');
INSERT INTO t VALUES(2, 'b');
-- ... 10000次
COMMIT;

2.2 预编译语句：减少SQL解析开销

如果你用C语言操作SQLite，sqlite3_prepare_v2 + sqlite3_bind_* 的组合比 sqlite3_exec 快得多。因为SQL语句只需要解析一次，后面只传参数。

sqlite3_stmt *stmt;
// 预编译一次
sqlite3_prepare_v2(db, "INSERT INTO t VALUES(?, ?)", -1, &stmt, NULL);

// 批量绑定执行
for (int i = 0; i < 100000; i++) {
    sqlite3_bind_int(stmt, 1, i);
    sqlite3_bind_text(stmt, 2, "hello", -1, SQLITE_TRANSIENT);
    sqlite3_step(stmt);
    sqlite3_reset(stmt);  // 重置，准备下一次绑定
}

sqlite3_finalize(stmt);

性能对比（插入10万条）：

  • sqlite3_exec + 逐条：约45秒
  • 事务包裹 + sqlite3_exec：约1.2秒
  • 事务包裹 + 预编译语句：约0.6秒

2.3 批量插入的终极形态

如果你用的是SQLite 3.32.0以上版本，可以试试 INSERT INTO ... VALUES (...), (...), (...) 这种批量语法。一条SQL插入多行，效率更高。

-- 一次插入100行
INSERT INTO t VALUES
(1, 'a'), (2, 'b'), (3, 'c'), /* ... */ (100, 'z');

我个人习惯的做法是：每500-1000条数据组装成一个批量INSERT，外面再包一层事务。这样既不会让SQL语句太长，又能充分利用批量插入的优势。

三、查询计划分析：让SQLite告诉你它怎么干活

调优了半天，如果不知道SQLite到底怎么执行你的查询，那基本是瞎调。SQLite提供了两个工具：EXPLAIN 和 EXPLAIN QUERY PLAN。

3.1 EXPLAIN QUERY PLAN：快速看懂执行计划

这个命令会输出查询的执行策略，包括用了哪些索引、扫描了多少数据。我每次写复杂查询，都会先跑一遍这个命令看看。

-- 创建测试表
CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);
CREATE INDEX idx_age ON user(age);

-- 查看查询计划
EXPLAIN QUERY PLAN SELECT * FROM user WHERE age > 30;

输出结果类似：

SCAN TABLE user USING INDEX idx_age

这说明SQLite用了idx_age索引来扫描。如果输出是 SCAN TABLE user 而没有索引，那就是全表扫描——性能杀手。

我的经验：看到全表扫描先别慌。如果表很小（几百行），全表扫描可能比用索引还快。但如果是百万级数据，全表扫描基本意味着你要等很久。

3.2 EXPLAIN：深入虚拟机指令

EXPLAIN 输出的是SQLite虚拟机的操作码，比 EXPLAIN QUERY PLAN 详细得多。一般调试时才用。

EXPLAIN SELECT * FROM user WHERE age > 30;

输出会显示一系列指令，比如 OpenRead、Rewind、Next 等。你可以从中看出SQLite到底是怎么遍历数据的。

3.3 实战：用查询计划发现索引问题

有一次我优化一个慢查询，表里有50万条数据，查询条件是 WHERE status=1 AND create_time>'2024-01-01'。我建了联合索引 (status, create_time)，但查询计划显示还是全表扫描。

后来发现，status字段只有0和1两个值，选择性太差。SQLite觉得用索引还不如直接扫表。解决方案是把索引改成 (create_time, status)，让时间字段在前面。改完之后，查询计划显示用了索引，速度从2秒降到了0.05秒。

教训：索引不是建了就一定有用。SQLite的查询优化器会评估索引的性价比。如果索引的选择性太低（比如只有两个值的字段），它可能直接放弃索引。

四、综合调优示例

下面是一个完整的调优示例，把今天讲的内容串起来：

-- 1. 创建数据库时设置page_size
PRAGMA page_size = 8192;

-- 2. 连接后设置其他PRAGMA
PRAGMA cache_size = -10240;   -- 10MB缓存
PRAGMA synchronous = NORMAL;  -- 平衡模式
PRAGMA journal_mode = WAL;    -- WAL模式，读写不互斥

-- 3. 批量插入数据
BEGIN TRANSACTION;
INSERT INTO log VALUES (1, 'msg1', '2024-01-01');
INSERT INTO log VALUES (2, 'msg2', '2024-01-01');
-- ... 更多行
COMMIT;

-- 4. 检查查询计划
EXPLAIN QUERY PLAN SELECT * FROM log WHERE id > 1000;

这套组合拳打下来，大部分SQLite的性能问题都能解决。当然，具体参数要根据你的硬件环境和数据特征来微调。没有银弹，但有方法论。

总结一下今天的核心要点：

  1. PRAGMA参数：cache_size给够内存，page_size选对大小，synchronous按场景取舍
  2. 批量操作：事务包裹 + 预编译语句，别一条一条提交
  3. 查询计划：用EXPLAIN QUERY PLAN验证索引是否生效，别靠猜

好了，今天就聊到这儿。这些技巧我在实际项目中反复验证过，希望能帮你少走弯路。下次遇到SQLite性能问题，记得先检查PRAGMA，再看查询计划，最后优化批量操作——按这个顺序来，基本不会错。

公众号:蓝海资料掘金营，微信deep3321

← 上一章 📖 返回目录 下一章 →