sqlite-ops

SQLite 操作

SQLite 是一个引擎，支持多种宿主环境。无论通过 sqlite3 CLI、Python、node:sqlite、better-sqlite3、Bun、Cloudflare D1 还是 libSQL/Turso 使用它，SQL 语义、查询规划器和 pragmas 都相同——不同的是驱动接口和运行环境（由谁管理文件、“连接”的开销有多大，以及是否能运行 PRAGMA）。先分析引擎，再查看对应的宿主环境章节，了解其中不同的陷阱。

Where does the problem live?
│
├─ A statement is slow, or scans too much
│  └─ EXPLAIN QUERY PLAN first, always → references/query-performance.md
│
├─ "database is locked" / SQLITE_BUSY / writers blocking readers
│  └─ WAL + busy_timeout + BEGIN IMMEDIATE → references/concurrency-durability.md
│
├─ Wrong data got in, or a constraint didn't fire
│  └─ Type affinity, STRICT, foreign_keys=OFF → references/schema-design.md
│
├─ Search / JSON / geo / analytics feature question
│  └─ FTS5, JSON, R-tree, window fns → references/feature-modules.md
│
├─ Running on a managed/edge engine (D1, Turso)
│  └─ references/d1-edge.md + references/hosts.md
│
└─ Corruption, size, backup, VACUUM
   └─ references/operations.md

测量规范（优化前请先阅读）

大多数 SQLite“优化”都没有经过测量。以下四条规则按最常被违反的顺序排列：

1. 测量语句，而不是工具调用。 如果一个开销很大的聚合语句包含在另一个查询本来就会发送的批处理中，它就不会增加额外往返，因此在按调用计时中不可见——但每次请求仍会扫描整张表。请拆分多部分语句，并分别为每部分计时。
2. 同时报告延迟和扫描行数。 两者的变化互不相关。某项优化可能让延迟降低约 25 倍，而读取行数几乎不变（在 D1 这类按量计费的引擎上，读取行数才是成本指标——参见 references/d1-edge.md）。
3. 不要相信 CLI 的墙钟时间。 进程启动耗时占主导。使用引擎自身报告的执行时长（CLI 中的 .timer on，D1 上的 meta.timings.sql_duration_ms）。
4. 至少运行 10 次，取中位数并报告范围。 首次运行时缓存是冷的。在一次测量中，同一语句的冷运行耗时为 2,495 ms，中位数为 171 ms——在读取数千行的场景中，首次运行出现 1.5–1.7 倍的惩罚很常见。

# sqlite3 CLI: engine-reported timing, not shell time
sqlite3 app.db '.timer on' "SELECT count(*) FROM q_product WHERE org LIKE '%acme%';"

# What the planner thinks the data looks like (empty = ANALYZE never ran)
sqlite3 app.db 'SELECT * FROM sqlite_stat1;'

创建索引前，先证明它能带来帮助

这是本技能中收益最高的技巧，也能确保架构变更通过部署门禁：**针对某个现有索引已覆盖的列，运行形态完全相同的语句。**使用相同的表、行数和谓词形态——只改变列。两者的差异就是预期收益，并且是在生产环境的真实数据上测得的，无需进行任何架构写入。

-- Hypothesis: a covering index on (org, product_id) makes this fast.
-- Unindexed control (what you have today):
SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';

-- Proof shot: same shape, over a column an existing index already covers.
-- If this is fast, the index is worth writing. If it isn't, the index is not your problem.
SELECT DISTINCT org FROM q_product WHERE org LIKE '%acme%';

在完整示例中，验证查询耗时 6.75 ms，而对照查询耗时 171.83 ms——足以证明创建该索引合理，无需改动生产环境架构。

EXPLAIN QUERY PLAN — 60 秒读懂

EXPLAIN QUERY PLAN（EQP）是排查任何慢语句时首先应使用的命令。它开销低、安全、只读，并且适用于所有允许运行任意 SQL 的宿主环境。

EXPLAIN QUERY PLAN
SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';

计划行	含义	判断
SEARCH t USING INDEX ix (col=?)	使用 B 树查找，仅触及匹配的行	最理想
SEARCH t USING COVERING INDEX ix	执行查找，且所需列都在索引中——不会读取表	最理想
SCAN t USING COVERING INDEX ix	全表扫描，但扫描的是较窄的索引项，而非较宽的行	通常没问题——见下文
SCAN t USING INDEX ix	全面扫描索引，并且每命中一项都要读取一行	值得警惕：索引带来的收益很小
SCAN t	全表扫描	应予修复，除非表很小
USE TEMP B-TREE FOR ORDER BY	因为没有索引可提供所需顺序而进行排序	成本信号
USE TEMP B-TREE FOR GROUP BY	同上，用于分组	成本信号
CORRELATED SCALAR SUBQUERY	子查询对外层每一行都重新执行	通常是整个问题的根源

最重要的区别： SCAN … USING COVERING INDEX 并不代表失败。覆盖索引扫描读取的是较窄的索引项，不必将较宽的行载入内存；这正是让无法通过索引查找的谓词变快的方法。

深入阅读：./references/query-performance.md — 索引设计、列顺序、部分索引和表达式索引、ANALYZE/sqlite_stat1，以及完整的查询规划器失效情形目录。

无法通过索引定位的谓词陷阱（实例详解）

支撑这条建议的 D1 实测案例（为什么在 LIKE '%x%' 上建普通索引毫无作用、解决方法以及前后对比数据），以及使用和不使用 sqlite_stat1 的查询规划器检查：references/covering-index-worked-example.md。

索引设计一览

谓词形式	可建立索引？	应构建的索引
col = ?、col IN (…)、col > ?、BETWEEN	是	在 col 上建立 B 树索引
a = ? AND b = ?	是	复合索引 (a, b) — 等值列放在前面
a = ? ORDER BY b	是	复合索引 (a, b) — 可避免临时 B 树
col LIKE 'x%'（前缀匹配）	是，但要求 col 为使用 BINARY 排序规则的 TEXT 类型	在 col 上建立 B 树索引
col LIKE '%x%'（前置通配符）	无法通过索引定位	让扫描使用覆盖索引，或使用 FTS5 trigram
lower(col) = ?	普通索引不支持	表达式索引 ON t(lower(col))
status = 'open'，其中符合条件的行占 2%	是	部分索引 WHERE status = 'open'
json_extract(doc,'$.k') = ?	普通索引不支持	表达式索引，或生成列加索引

能长期见效的规则： 在覆盖索引中，把筛选列放在前面，把投影列放在后面；索引应建在列上，而不是列的函数上（除非使用表达式索引）；每增加一个索引，写入操作都要付出代价 — 添加前先审查。

并发与持久性 — 80/20 原则

症状	原因	解决方法
SQLITE_BUSY	另一个连接持有锁；当前连接等待后放弃	使用 PRAGMA busy_timeout = 5000;，并缩短写事务
SQLITE_LOCKED	同一连接内（或共享缓存中）发生冲突	修复代码 — 重试循环会无限空转
事务中途出现 "database is locked"	BEGIN（DEFERRED）先读取、之后再写入 → 升级死锁，无法重试	对任何会写入的事务使用 BEGIN IMMEDIATE
写入方阻塞读取方	回滚日志模式	使用 PRAGMA journal_mode = WAL;（此设置会持久保存，只需设置一次）
-wal 文件不断增长	长时间运行的读取方阻止检查点推进	关闭或刷新读取方；使用 PRAGMA wal_checkpoint(TRUNCATE);

PRAGMA journal_mode = WAL;      -- persistent; survives reconnect
PRAGMA busy_timeout = 5000;     -- per-connection; set on EVERY connection
PRAGMA foreign_keys = ON;       -- per-connection, OFF by default — see below
PRAGMA synchronous = NORMAL;    -- safe with WAL; FULL only if you fear power loss

深入了解：./references/concurrency-durability.md — WAL 内部机制、DEFERRED 升级死锁、synchronous 级别、检查点饥饿、多进程访问。

架构 — 三个隐蔽的错误

1. PRAGMA foreign_keys 默认处于 OFF 状态。 每个连接都要单独设置。你的 REFERENCES 子句虽然能解析并保存下来，却不会执行任何操作。这是 SQLite 应用中典型的隐蔽数据完整性错误。
2. 类型亲和性不是类型。 TEXT 列可以轻松存储整数；声明的类型只是对转换方式的 一种建议。需要强制执行声明类型时，请使用 STRICT 表（SQLite 3.37+）。
3. ALTER TABLE 功能有限。 支持添加列和重命名；删除列、重新指定类型以及更改约束， 则需要执行 12 步重建流程。

CREATE TABLE product (
    id       INTEGER PRIMARY KEY,
    org      TEXT NOT NULL,
    price    REAL NOT NULL,
    doc      TEXT,
    -- indexable projection of a JSON field
    sku      TEXT GENERATED ALWAYS AS (json_extract(doc, '$.sku')) VIRTUAL
) STRICT;

深入了解：./references/schema-design.md（亲和性、STRICT、生成列、 WITHOUT ROWID、约束）和 ./references/migration-patterns.md（12 步 ALTER 流程、带版本管理的迁移运行器）。

功能模块一览

需求	使用	说明
子串／模糊文本搜索	使用 trigram 分词器的 FTS5	这是大规模处理 LIKE '%x%' 的真正解决方案
带排名的词语／短语搜索	FTS5 + bm25()	外部内容表可避免重复存储语料
半结构化文档	json_extract / -> / ->>、JSONB（3.45+）	通过生成列或表达式索引建立索引
边界框／区间重叠	R-tree 虚拟表	编译时模块；请检查是否可用
累计总和、排名、间隔	窗口函数（3.25+）	语法与 PostgreSQL 相同
插入或更新	ON CONFLICT … DO UPDATE（3.24+）	excluded.col 指的是待写入的行
读回已写入的数据	RETURNING（3.35+）	可通过单条语句实现原子化的任务认领模式

深入了解：./references/feature-modules.md。

主机

引擎相同，封装方式却不同。

各主机的连接模型与注意事项：主机一览。在 D1 上，成本按读取的行数计算，绑定参数上限为 100，且不提供 PRAGMA 接口。

深入了解：./references/hosts.md，了解各主机的连接方案与注意事项。

特别是在 D1 上，有三项平台功能在原生 SQLite 中没有对应功能，也是最常被忽略的：

wrangler d1 insights <db> --sort-type=sum --sort-by=reads --limit=10   # rank REAL queries by cost
wrangler d1 time-travel info <db>                                      # 30-day point-in-time restore point
# Sessions API (env.DB.withSession(bookmark)) - read replicas, sequential consistency

./references/d1-edge.md 涵盖这些功能、读取行数的成本机制、经验证的限制表、错误目录以及导入／导出。有关生产环境中的事故模式——超时后仍已生效的 migrations apply --remote、将影响 0 行的范围限定 UPDATE 判定为成功的 batch()，以及选择加入副本的读取复制发布方案——请参阅 ./references/d1-production-patterns.md。

运维

sqlite3 app.db 'PRAGMA quick_check;'        # fast structural check
sqlite3 app.db 'PRAGMA integrity_check;'    # full check — slow on big DBs
sqlite3 app.db "VACUUM INTO 'backup.db';"   # consistent backup, no downtime, defragmented
sqlite3 app.db '.dump' > backup.sql         # portable text backup
sqlite3 app.db 'PRAGMA optimize;'           # run before closing a long-lived connection

写入器仍在运行时，切勿使用 cp 复制数据库文件——请使用 VACUUM INTO、备份 API 或 .dump。

深入了解：./references/operations.md——损坏原因与恢复、VACUUM 与 VACUUM INTO 的区别、页面／缓存大小设置、大小分析。

分类诊断脚本

scripts/eqp-triage.py 可读取 EXPLAIN QUERY PLAN 的结果——既可以通过对数据库运行语句获取，也可以从管道输入计划文本——并按严重程度对每一行分类，同时给出修复提示。发现问题时退出码为 10（表示领域问题），计划没有问题时退出码为 0。

# Run against a database file (uses Python's bundled sqlite3 — no external binary needed)
python3 scripts/eqp-triage.py --db app.db \
  --sql "SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%'"

# More: triage a plan piped from D1 or a log, and --json output -> references/eqp-triage.md

易踩问题

错误做法	影响	修复方法
为 LIKE '%x%' 添加索引	前置通配符无法通过索引查找	使用覆盖索引或 FTS5 trigram
用 shell 秒表计时	CLI／驱动启动耗时占主导	使用引擎报告的耗时；取 10 次以上的中位数
给工具调用计时，而非给语句计时	捎带执行的语句无法单独计时	拆分各部分并分别计时
假定 REFERENCES 会强制执行	每个连接上的 foreign_keys 默认均为 OFF	在每个连接上执行 PRAGMA foreign_keys = ON
假定声明的类型会受到强制约束	只有类型亲和性，并非严格类型检查	使用 STRICT 表
重试 SQLITE_LOCKED	同一连接上的冲突不会自行消失	修复代码路径
先 BEGIN 再写入	DEFERRED→写入升级会死锁，且无法重试	使用 BEGIN IMMEDIATE
对正在使用的数据库执行 cp	可能复制出不完整的文件	使用 VACUUM INTO／备份 API
SELECT *	无法利用覆盖索引；每次读取的行都会变宽	只查询所需的列
为了“加速”而运行 VACUUM	会重写整个文件，需要 2 倍空间，并会持有锁	使用 PRAGMA optimize／针对性地处理索引
轻信一次冷启动运行结果	首次运行慢 1.5–1.7 倍很常见	取 10 次以上的中位数，并报告范围
为了规避参数上限而内联字面值	这正是注入发生的方式	分批处理；继续使用绑定参数
重新运行超时的远程迁移	迁移可能已经生效；错误针对的是响应	先以只读方式验证架构状态——./references/d1-production-patterns.md
将已提交的 batch() 视为每条语句都成功	条件 UPDATE 匹配 0 行并不算错误	检查 meta.changes；范围限定写入返回 0 表示 403／冲突

参考文件

参考资料	何时加载
./references/query-performance.md	任何慢语句：EQP、索引设计、ANALYZE、规划器未达预期、测量方法
./references/d1-edge.md	Cloudflare D1：读取行数相关成本、d1 insights、Sessions API／复制、Time Travel、限制、错误
./references/d1-production-patterns.md	在生产环境运行 D1：验证超时迁移、验证 batch() 的 0 行写入、选择加入副本复制的推出流程
./references/concurrency-durability.md	锁定、WAL、busy_timeout、事务模式、检查点、持久性
./references/schema-design.md	类型亲和性、STRICT、外键、生成列、WITHOUT ROWID、约束
./references/schema-patterns.md	可直接使用的表设计：状态、缓存、事件日志、队列、会话、去重
./references/migration-patterns.md	版本化迁移、12 步 ALTER 流程、特定主机的迁移运行器
./references/feature-modules.md	FTS5、JSON/JSONB、R-tree、窗口函数、upsert、RETURNING
./references/hosts.md	各主机的连接配置与驱动陷阱（Python、Node、Bun、D1、libSQL）
./references/async-patterns.md	Python aiosqlite 深入指南：异步 CRUD、批处理、连接池
./references/operations.md	完整性检查、损坏恢复、VACUUM、备份、大小与页面调优
./references/testing.md	内存数据库与文件数据库、测试夹具、确定性播种、迁移测试
./references/covering-index-worked-example.md	上文建议所依据的实测 D1 覆盖索引案例；有／无统计信息时的规划器检查
./references/eqp-triage.md	更多 eqp-triage.py 调用方式：从 D1 或日志传入查询计划、--json 输出

另请参阅

技能	何时组合使用
sql-ops	与供应商无关的 SQL：CTE、窗口函数、JOIN 策略
perf-ops	更全面的性能工作流——性能分析、负载测试、前后对比规范
cloudflare-ops	D1 数据库周边的 Workers、绑定和部署
postgres-ops	工作负载已超出 SQLite 单写入者模型时
python-database-ops	基于 SQLite 的 SQLAlchemy／ORM 层