SQLite PRAGMA 与配置调优详解

详解 SQLite PRAGMA 语句的全部常用参数：journal_mode（删除/wal/内存）、synchronous、cache_size、page_size、temp_store、auto_vacuum、foreign_keys、busy_timeout、wal_autocheckpoint、mmap_size、locking_mode、user_version 等，含默认值、设置语法与实测效果。基于 SQLite 3.53.4 实时环境实测。

SQLiteSQLite配置详解参数调优嵌入式数据库关系型数据库OLTP2026/10/02

SQLite PRAGMA 与配置调优详解

目标：掌握 SQLite 通过 PRAGMA 语句查看与调整数据库运行时行为的全部常用参数。PRAGMA 是 SQLite 特有的配置指令，可直接在命令行或程序内执行，用于控制事务模式、缓存、并发、外键、内存映射等。本文所有默认值、设置语法与效果均在 SQLite 3.53.4 环境实测。

环境信息

信息项值
SQLite 版本3.53.4（源码编译）
操作系统Ubuntu 22.04.5 LTS
演示库/tmp/p.db（磁盘库）

一、PRAGMA 基本用法

PRAGMA 有两种形态：

  • 标量查询：PRAGMA 参数; 返回当前值。
  • 设置：PRAGMA 参数=值; 或 PRAGMA 参数(值);。
  • 部分参数还以表值函数形式提供：SELECT * FROM pragma_xxx;（如 pragma_journal_mode、pragma_cache_size）。

示例：

PRAGMA journal_mode;           -- 查看
PRAGMA journal_mode=WAL;       -- 设置
SELECT * FROM pragma_journal_mode;  -- 表值形式

设置仅对当前连接生效（除非是 journal_mode、page_size 等持久化到数据库文件头部的参数）。

二、核心参数详解（含默认值与实测）

下表为 3.53.4 磁盘库（新建）的实测默认值：

PRAGMA默认值说明
journal_modedelete（磁盘）/ memory（内存库）提交前 WAL 日志模式
synchronous2（FULL）落盘同步级别
cache_size-2000（2000 页 ≈ 8 MB）页面缓存大小；负数为 KB，正数为页数
page_size4096逻辑页大小（字节）
temp_store0（DEFAULT）临时表/排序文件存放
auto_vacuum0（NONE）自动回收删除空间
foreign_keys0（OFF）外键约束开关
read_uncommitted0读未提交隔离
busy_timeout0写锁等待毫秒数
journal_size_limit-1（无限）日志文件大小上限
wal_autocheckpoint1000WAL 自动 checkpoint 页数
locking_modenormal锁模式：normal/exclusive
mmap_size0内存映射大小限制
secure_delete0（OFF）删除覆盖原数据
user_version0用户自定义版本号
integrity_checkok检查库完整性
quick_checkok快速完整性检查

三、常用调优参数详解

3.1 journal_mode（日志模式）

  • delete（默认）：事务用回滚日志文件，提交后删除，多写者并发受限。
  • wal：预写式日志，读与写并发更好，写性能高，推荐多读少写或要求并发读的场景。
  • memory：日志放内存，最快但崩溃可能丢数据。
  • off：完全关闭日志，最快但最不安全。

查看与切换（切换是持久化的）：

PRAGMA journal_mode;          -- delete
PRAGMA journal_mode=WAL;      -- 返回 wal
PRAGMA wal_autocheckpoint;    -- 1000

实测切换输出：

delete
wal
1000

启用 WAL 后目录下会多出 -wal 与 -shm 两个文件。

⚠️ 在 WAL 模式下，只有一个写入者；无法同时打开两个客户端向同一 WAL 库写入。可通过 PRAGMA busy_timeout 缓解写等待。

3.2 synchronous（同步级别）

可选 0(OFF) / 1(NORMAL) / 2(FULL) / 3(EXTRA)：

  • OFF(0)：完全不等待落盘，最快但崩溃可能丢失最近提交。
  • NORMAL(1)：大多数情况安全，WAL 模式下故障几乎不丢。
  • FULL(2)（默认）：每次提交等落盘，最安全。
  • EXTRA(3)：更进一步，极端谨慎。
PRAGMA synchronous;            -- 2
PRAGMA synchronous=NORMAL;     -- 1

调优建议：WAL + NORMAL 是"高写并发 + 可接受少量丢失"的常见组合；需严格持久化用 FULL。

3.3 cache_size（页面缓存）

缓存页数；当值为负时表示 KB（-2000 ≈ 2000 KB = 8 MB 于 4096 页），为正是页数（2000 ≈ 2000 页 = 8 MB）。

PRAGMA cache_size;            -- -2000
PRAGMA cache_size=-8000;      -- 8 MB（-8000 KB）
PRAGMA cache_size=8000;       -- 8000 页

调大缓存可显著提升重复查询性能，尤其配合 mmap 使用。

3.4 page_size（页面大小）

只能在空库上在建表前设置，例如 PRAGMA page_size=8192;。较大的页面减少大 BLOB/长行随机 IO。

3.5 temp_store（临时文件）

  • 0 DEFAULT：默认（内存映射/页文件）
  • 1 FILE：用磁盘临时文件
  • 2 MEMORY：全部用内存
PRAGMA temp_store=2;           -- 排序放内存，更快但更占内存

3.6 auto_vacuum（自动回收）

  • 0 NONE（默认）：删除后文件不自动缩小。
  • 1 FULL：自动回收空间回尾部。
  • 2 INCREMENTAL：配合 PRAGMA incremental_vacuum; 手动回收。

必须在建表前设置，否则需 VACUUM 重建。

3.7 foreign_keys（外键约束）

默认关闭。需显式在每个连接开启，否则外键失效：

PRAGMA foreign_keys=ON;

⚠️ 它不是持久化的，每条连接都要开。应用层可每次建立连接执行。

3.8 busy_timeout（写锁等待）

等待被占用锁的毫秒数，避免并发写立刻报 database is locked：

PRAGMA busy_timeout=3000;      -- 等待 3 秒

3.9 mmap_size（内存映射）

把数据库映射进地址空间，加速只读访问：

PRAGMA mmap_size=268435456;    -- 256 MB

映射大小受 PRAGMA max_mmap_size 限制，-1 表示按需。

3.10 wal_autocheckpoint（WAL 自动检查点）

WAL 文件累积到 N 页时自动执行 checkpoint，把日志合并回主库。默认 1000；设 0 关闭（需要手动 PRAGMA wal_checkpoint;）。

PRAGMA wal_autocheckpoint=1000;
PRAGMA wal_checkpoint(PASSIVE);  -- 手动 checkpoint

3.11 user_version（用户版本标记）

用作 schema 版本号做迁移管理：

PRAGMA user_version=3;
PRAGMA user_version;           -- 3

3.12 完整性检查

PRAGMA integrity_check;   -- ok（库损坏时输出错误列表）
PRAGMA quick_check;       -- ok

损坏时 integrity_check 会列出损坏页，常用于备份恢复后校验。

四、实测：默认值一览

统一查看当前库各参数默认值：

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;
PRAGMA auto_vacuum;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA journal_size_limit;
PRAGMA wal_autocheckpoint;
PRAGMA locking_mode;
PRAGMA mmap_size;
PRAGMA secure_delete;
PRAGMA user_version;

实测输出（3.53.4 新建磁盘库）：

delete
2
-2000
4096
0
0
0
0
-1
1000
normal
0
0
0

五、生产环境推荐组合

针对「高并发读 + 可接受少量写丢失」的典型服务：

PRAGMA journal_mode=WAL;        -- 读多写少，允许并发读
PRAGMA synchronous=NORMAL;      -- WAL 下安全且更快
PRAGMA busy_timeout=5000;       -- 写竞争等待 5 秒
PRAGMA cache_size=-16384;       -- 16 MB 缓存
PRAGMA mmap_size=536870912;     -- 512 MB 内存映射
PRAGMA foreign_keys=ON;         -- 每次连接开启

针对「严格持久化」的可靠性要求：

PRAGMA journal_mode=DELETE;     -- 或 WAL
PRAGMA synchronous=FULL;        -- 每次提交落盘
PRAGMA busy_timeout=3000;

六、常见问题（FAQ）

1. 设置了 journal_mode 为何重启后又变回？

换存储介质或新连接不继承未持久化参数。journal_mode 是持久化到库文件的；synchronous、busy_timeout、foreign_keys 等只对当前连接，需在每次连接重设（应用里写初始化 SQL）。

2. 报 database is locked？

另一个连接正持有写锁。设置 PRAGMA busy_timeout=3000; 让本连接等待锁释放。

3. 想回收文件大小但 DELETE 没变小？

默认 auto_vacuum=NONE，删除只标记空闲页不缩小文件。执行 VACUUM; 或改为 INCREMENTAL 后 PRAGMA incremental_vacuum;。参考《SQLite 备份与恢复指南》中 VACUUM 章节。

4. PRAGMA 的英文词大小写敏感吗？

不敏感，PRAGMA/pragma 均可，参数名也大小写不敏感。

七、小结

PRAGMA 是 SQLite 配置与调优的唯一入口：journal_mode/synchronous/cache_size/mmap_size 决定读写性能与并发，busy_timeout/locking_mode 处理锁竞争，foreign_keys 保证数据完整性，integrity_check/quick_check 校验健康状态。结合《SQLite 命令行与工具详解》与《SQLite 备份与恢复指南》可组成完整运维闭环。