SQLite 被低估的 7 个能力

一个内部工具，几十万行数据，同时在线不超过五个人。有人提议上 PostgreSQL，理由是「正式环境不能用 SQLite」。结果装完数据库、配好连接池、写了备份脚本，运维成本比业务代码还高。

SQLite 被低估的地方不在于它能存数据，而在于它早就补齐了很多人以为只有「正经数据库」才有的能力。下面七个，是我在真实项目里用得最多的。

先确认你手里的 SQLite 是哪个版本#

这件事比想象中重要。语言绑定常常自带一份编译好的 SQLite，和系统里的 sqlite3 命令不是同一个东西。

bash

sqlite3 --version                    # 命令行工具的版本
python -c "import sqlite3; print(sqlite3.sqlite_version)"   # Python 实际用的版本

如果两行输出差距很大，说明你的 Python 环境链的是一个偏老的库，STRICT、jsonb 这类新特性可能用不了。升级方式取决于发行版和编译方式，以官方文档的版本说明为准。任何一个特性写进生产前，先用 select sqlite_version(); 确认运行时版本，而不是看你开发机上的版本。

七个被低估的能力#

1. STRICT 表：把类型约束要回来#

SQLite 默认是动态类型，integer 列里塞字符串也照收。这当然是历史设计，但对业务表来说是隐患。较新的版本支持建表时声明 STRICT：

sql

create table doc (id integer primary key, title text not null) strict;

之后 insert into doc (id, title) values ('abc', 1) 会直接报错，而不是悄悄写进去。新项目的业务表建议全开 STRICT，代价只是建表语句多四个字母。

要注意 STRICT 表里列类型只能从 INT、INTEGER、REAL、TEXT、BLOB、ANY 里选，varchar(50) 这类写法会被拒绝。这其实是好事：长度约束本来就不该指望数据库，放到应用层校验更清楚。

2. WAL 模式：让读不再被写阻塞#

默认的回滚日志模式下，写操作会阻塞读。开 WAL 之后读写可以并行，只有写与写之间互斥：

sql

pragma journal_mode = wal;      -- 持久化设置，执行一次即可
pragma busy_timeout = 5000;     -- 遇到写锁时最多等 5 秒再报错

busy_timeout 经常被忘掉，结果并发一上来就报 database is locked。它不是银弹，但能把偶发冲突变成短暂等待。

WAL 还有一个配套取舍：pragma synchronous = normal 能显著减少 fsync 次数，代价是断电时可能丢掉最后几个已提交的事务，但数据库不会损坏。桌面端、可重建的数据可以接受；涉及账目的表就保持默认的 full，别为了那点写入速度冒险。

3. UPSERT 与 RETURNING：少一次往返#

insert ... on conflict ... do update 在较新版本里已经可用，配合同样较新的 returning，一次语句就能拿到写入后的行，不用再 select 一遍。对「有则更新、无则插入」这类逻辑，能省掉一层事务和一次网络往返。

4. 生成列：把派生字段交给数据库#

generated always as (...) virtual 定义的列不占存储，查询时计算；加 stored 则在写入时落盘，可以建索引。典型用法是把标题归一化成 slug、把 JSON 里的字段抽出来建索引，避免在应用层到处重复同样的表达式。

5. JSON 函数：不必为了一个字段上文档库#

json_extract、json_each 这些函数让 SQLite 能直接查 JSON 文本。适合「主体是结构化字段，附加信息是半结构化配置」的表。要注意：JSON 字段没有 schema 约束，热路径上的查询还是抽成真正的列更稳。

6. FTS5：站内搜索的最低成本方案#

create virtual table using fts5(...) 建全文索引，支持分词、前缀查询、bm25() 排序。数据量在几十万文档以内，配一个触发器同步主表，搜索体验足够好，还不用额外部署 Elasticsearch。

中文场景要留意分词器。默认的 unicode61 会把整段中文当成一个词，搜「数据库」命中不了「SQLite 数据库很轻」。较新版本提供 trigram 分词器，按三字符切分，子串匹配可用，但短于三个字符的关键词用不上索引，需要应用层兜底或换外部分词方案。这一条踩过的人不少，建议先用真实语料试。

7. VACUUM INTO：一致性热备份#

直接 cp app.db 在写入过程中可能拿到半个事务。vacuum into 'backup.db' 会生成一个整理过的完整副本，可以在服务运行时执行。备份脚本一行就够，恢复就是换个文件。

把上面七项拼起来#

下面这段把其中几项串在一起，二十几行能看到大部分效果：

python

import json
import sqlite3

con = sqlite3.connect("app.db")
con.execute("pragma journal_mode = wal")
con.execute("pragma busy_timeout = 5000")
con.execute("pragma foreign_keys = on")   # 外键默认关闭，必须显式打开

con.executescript("""
create table if not exists doc (
  id    integer primary key,
  title text not null,
  meta  text,
  slug  text generated always as (lower(title)) virtual
  ) strict;
""")

row = con.execute("""
insert into doc (id, title, meta) values (?, ?, ?)
on conflict(id) do update
  set title = excluded.title, meta = excluded.meta
returning id, slug
""", (1, "Hello SQLite", json.dumps({"tag": "db"}))).fetchone()
print(row)                                     # (1, 'hello sqlite')

print(con.execute("select json_extract(meta, '$.tag') from doc").fetchone())

如果这段在 strict 那行报语法错误，就是版本问题，回到「先确认版本」那节核对。

性能上真正要盯的两件事#

SQLite 慢起来，九成是索引问题，不是引擎问题。

一是看执行计划。explain query plan select ... 会告诉你走的是索引扫描、索引查找还是全表扫描。输出里出现 SCAN table 且表又大，就该补索引。复合索引注意列顺序：(user_id, created_at) 能服务「按用户取最新」，反过来则不行。

二是统计信息。pragma optimize; 会按当前数据分布更新查询规划器用的统计，数据量变化大的库值得定期执行一次（比如在维护脚本里）。它不会自己跑，忘了这件事的表现是「明明有索引却选了全表扫描」，然后开始怀疑人生。

什么时候不该用 SQLite#

场景	用 SQLite	换 PostgreSQL
单机、读多写少	合适	过度设计
并发写频繁（多进程抢写）	会排队，慎用	合适
数据放在 NFS / 网络盘	不要用，锁语义不可靠	合适
需要多用户权限体系	没有，得在应用层做	内置
需要跨机读写同一份数据	不支持	合适
桌面、边缘、嵌入式	首选	部署成本高

还有两个容易踩的点：WAL 模式下不要在共享网络文件系统上跑；备份不要依赖文件复制，用 vacuum into 或 .backup。另外 SQLite 没有连接池概念，连接数不多时直接复用单连接反而更省事，但跨线程共享连接需要自己控制。

结论#

新项目建表就开 STRICT，把类型错误挡在写入前。
打开 WAL 和 busy_timeout，这是并发可用的最低配置。
UPSERT + RETURNING、生成列、JSON 函数能消掉不少应用层的胶水代码。
几十万文档的搜索用 FTS5，别急着上独立搜索服务。
备份用 vacuum into，并发写高或多机共享时老老实实换 PostgreSQL。

完

#SQLite#数据库#嵌入式#SQL

回到顶部 ↑