数据库基础体系 · 第 33/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQLite 查询与索引：类型亲和性、查询计划、FTS5 和性能边界

SQLite 的查询性能，通常不是“有没有索引”这么简单。至少有四层机制会共同决定结果：

列声明产生的类型亲和性会影响比较时的类型转换；
索引定义决定哪些条件能够被快速定位；
查询规划器根据统计信息、约束形式和估算成本选择执行路径；
FTS5 使用与普通 B-tree 索引完全不同的倒排索引，适合全文检索，但不能替代所有字符串查询。

如果忽略其中任意一层，就可能出现“查询结果看起来正确，但索引没有生效”“同样的值有时能匹配、有时不能匹配”“全文检索比 LIKE 更快，但结果语义不同”等问题。

本文示例以 SQLite 命令行或使用官方 SQLite 库的应用为边界。执行计划的具体文本格式属于实现输出，SQLite 版本升级后可能变化；应关注其含义，而不是依赖某一行固定字符串。

一、先建立 SQLite 的执行模型

一条普通查询可以抽象为：

SELECT 投影列
FROM 数据源
WHERE 过滤条件
GROUP BY 分组键
ORDER BY 排序键
LIMIT 限制;

SQLite 大致需要完成以下工作：

解析 SQL；
为表达式确定语义，包括名称解析、类型亲和性和函数；
选择访问路径，例如全表扫描、索引查找、多个索引合并；
读取数据页；
执行过滤、排序、聚合和结果投影。

“索引命中”只解决了其中一部分问题。即使使用了索引，SQLite 仍可能需要：

回表读取完整行；
对大量候选行继续执行过滤；
创建临时 B-tree 完成排序或分组；
调用函数计算表达式；
将大量结果复制到应用层。

因此，查询性能不能只看是否出现了 USING INDEX。

SQLite 的普通表通常使用 B-tree 存储。对普通 rowid 表而言：

表本身的 B-tree 以 rowid 为键；
普通索引的 B-tree 以索引列值为键，并保存对应 rowid；
查询先在索引中定位 rowid，再回到表 B-tree 读取其他列。

如果索引已经包含查询需要的列，SQLite 可能直接从索引读取，这称为覆盖索引，执行计划中常见 USING COVERING INDEX。

二、类型亲和性：声明类型不是强制类型

2.1 存储类与类型亲和性是两个概念

SQLite 的运行时值具有存储类（storage class）：

NULL
INTEGER
REAL
TEXT
BLOB

而列还具有类型亲和性（type affinity）。类型亲和性不是“这个列只能存什么类型”，而是 SQLite 在写入值和比较值时，尝试采用某种存储形式的规则。

常见亲和性包括：

INTEGER
REAL
NUMERIC
TEXT
BLOB

SQLite 从列声明中的类型名称推导亲和性。例如：

CREATE TABLE demo (
    a INTEGER,
    b TEXT,
    c NUMERIC,
    d BLOB
);

在非 STRICT 表中，这些声明不会像传统强类型数据库那样绝对禁止不匹配的值：

INSERT INTO demo(a, b, c, d)
VALUES ('123', 123, '3.14', 'raw');

可能得到的实际存储类是：

SELECT
    typeof(a),
    typeof(b),
    typeof(c),
    typeof(d)
FROM demo;

典型结果：

integer|text|real|text

原因是：

a 的 INTEGER 亲和性会尝试把 '123' 转成整数；
b 的 TEXT 亲和性会把 123 转成文本；
c 的 NUMERIC 亲和性会把 '3.14' 转成实数；
d 的 BLOB 亲和性通常不主动转换。

可以使用 typeof() 检查实际存储类，而不是根据列声明猜测：

SELECT value, typeof(value)
FROM some_table;

2.2 插入时的转换与比较时的转换不同

类型亲和性至少会在两个阶段产生影响：

写入表时，列亲和性可能转换输入值；
比较表达式两侧的值时，SQLite 可能再次根据操作数亲和性转换另一侧。

比较的直觉规则可以概括为：

如果一侧具有 INTEGER、REAL 或 NUMERIC 亲和性，另一侧可能被尝试转换为数值；
如果一侧具有 TEXT 亲和性，另一侧可能被尝试转换为文本；
没有适用亲和性时，SQLite 按实际存储类和比较规则处理。

下面这个例子容易违反直觉：

CREATE TABLE prices (
    id INTEGER PRIMARY KEY,
    price TEXT
);

INSERT INTO prices(price) VALUES
    ('8.00'),
    ('8.0'),
    ('8'),
    ('10.00');

SELECT id, price
FROM prices
WHERE price = 8.00;

price 是 TEXT，右侧字面量 8.00 初始是数值。比较时，SQLite 会倾向于把右侧转换为文本。数值转换为文本时，其规范表示通常是：

8.0

因此这条查询通常只匹配：

8.0

而不会匹配文本值：

8.00

这不是 SQLite 把小数点后的零“误删”了，而是比较前发生了从数值到文本的转换，文本格式化结果与原始字符串格式不一致。

如果业务含义是金额数值，应使用数值列，或者把金额统一保存为最小货币单位的整数：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    amount_cents INTEGER NOT NULL
);

INSERT INTO orders(amount_cents) VALUES (800);

SELECT *
FROM orders
WHERE amount_cents = 800;

如果业务含义是保留原始格式的文本，则应该按文本比较：

SELECT *
FROM prices
WHERE price = '8.00';

不要指望 CAST() 自动让“数值相等”和“字符串格式相等”成为同一个概念：

SELECT *
FROM prices
WHERE CAST(price AS NUMERIC) = 8.00;

这条查询会把不同文本格式解释为相同数值，可能匹配 '8.00'、'8.0' 和 '8'。它改变的是比较语义，而不只是性能。

2.3 列亲和性、表达式亲和性和参数绑定

列引用通常带有该列的亲和性，但很多表达式没有列亲和性，或者其亲和性与原始列不同。例如：

SELECT *
FROM prices
WHERE price + 0 = ?;

price + 0 已经不是简单的列引用。它是一个表达式，可能导致：

比较转换规则变化；
不能直接使用 price 上的普通索引；
每行都需要计算表达式。

显式 CAST 也会改变表达式：

SELECT *
FROM prices
WHERE CAST(price AS NUMERIC) = ?;

如果确实需要按转换后的值查询，可以创建表达式索引：

CREATE INDEX prices_numeric_idx
ON prices(CAST(price AS NUMERIC));

但这并不表示任何等价写法都能使用该索引。查询表达式需要与索引表达式在 SQLite 能识别的语义范围内匹配；不能只凭“数学上等价”推断规划器一定会复用索引。

参数绑定本身不会让参数自动变成某种“数据库类型”。应用绑定的是 SQLite 的运行时值，例如整数、浮点数或文本。参数的绑定类型仍会影响比较结果：

WHERE id = ?       -- 绑定整数 8
WHERE id = ?       -- 绑定文本 "8"

对具有数值亲和性的 id，两者通常都能得到合理的数值比较；但在文本列、混合存储数据或复杂表达式中，绑定类型可能改变结果。生产代码应让参数类型与业务字段语义一致，而不是依赖隐式转换。

2.4 `STRICT` 表解决什么问题

SQLite 支持 STRICT 表，用于收紧写入类型检查：

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance REAL NOT NULL,
    name TEXT NOT NULL
) STRICT;

STRICT 可以减少“同一列混入整数、数字文本和任意文本”的情况，尤其适合需要稳定数据约束的业务表。

但 STRICT 不会改变所有 SQL 的语义，也不会把 SQLite 变成拥有完全相同类型系统的其他数据库：

比较仍然要遵循 SQLite 的表达式和亲和性规则；
TEXT 中保存数字字符串仍然是文本；
函数、连接、排序和索引仍受 SQLite 语义影响。

它主要解决的是数据入口的一致性，而不是替代查询设计。

三、索引到底加速了什么

3.1 从全表扫描到索引查找

假设有订单表：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    created_at INTEGER NOT NULL,
    status TEXT NOT NULL,
    total_cents INTEGER NOT NULL
);

没有合适索引时：

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 7
  AND created_at >= 1700000000
ORDER BY created_at
LIMIT 20;

SQLite 可能扫描所有订单：

读取每一行；
判断 tenant_id = 7；
判断时间条件；
为满足条件的行排序；
取前 20 行。

建立复合索引：

CREATE INDEX orders_tenant_created_idx
ON orders(tenant_id, created_at);

索引中的键按以下顺序排列：

(tenant_id, created_at)

查询可以先定位：

tenant_id = 7

再在该租户的索引范围内扫描：

created_at >= 1700000000

因为索引的第二列本身就是 created_at，扫描顺序也满足 ORDER BY created_at。于是可能不再需要全表扫描或额外排序。

可以用：

EXPLAIN QUERY PLAN
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 7
  AND created_at >= 1700000000
ORDER BY created_at
LIMIT 20;

观察计划。典型结果可能类似：

SEARCH orders USING INDEX orders_tenant_created_idx
    (tenant_id=? AND created_at>?)

具体格式和是否显示某些细节取决于 SQLite 版本。

3.2 复合索引的左侧连续性

对于索引：

CREATE INDEX idx
ON t(a, b, c);

通常最有利的使用方式是：

WHERE a = ?
  AND b = ?
  AND c > ?

这里形成了一个连续的索引前缀：

a 等值 → b 等值 → c 范围

而下面的条件通常不能同样有效地使用后续列：

WHERE a > ?
  AND b = ?

一旦在 a 上进入范围扫描，b 通常不能再像独立等值条件那样缩小同一索引的搜索区间。规划器可能仍使用 a，但不能把这个索引理解为“先按 b 过滤，再按 a”。

以下查询缺少左侧列：

WHERE b = ? AND c = ?;

普通情况下不能直接高效定位 idx(a,b,c) 的内部范围，因为 B-tree 的排序首先由 a 决定。SQLite 可能选择全表扫描，也可能在特定条件下使用跳跃扫描等能力，但不能把这种可能性当作索引设计的基础保证。

索引列顺序应从查询的约束和排序需求推导，而不是机械地把所有字段都放进去。

3.3 等值条件、范围条件和排序

一个常见模式是：

WHERE tenant_id = ?
  AND category = ?
  AND created_at >= ?
ORDER BY created_at

适合的索引候选可能是：

CREATE INDEX idx
ON items(tenant_id, category, created_at);

推导过程为：

tenant_id 是等值条件；
category 是等值条件；
created_at 是范围条件，同时承担排序；
等值列放在范围列前面，使范围扫描限定在更小的分区中。

如果查询改为：

WHERE tenant_id = ?
  AND created_at >= ?
ORDER BY category, created_at;

原索引不一定能同时满足过滤和排序。索引能高效过滤，不代表它也能提供所需排序顺序。过滤顺序和排序顺序必须一起检查。

3.4 覆盖索引的收益与代价

如果查询是：

SELECT tenant_id, created_at
FROM orders
WHERE tenant_id = 7
  AND created_at >= 1700000000
ORDER BY created_at;

orders(tenant_id, created_at) 可能已经包含投影所需的列，SQLite 可以只读取索引页，而不回表读取订单行。

但增加列到索引会带来明确代价：

索引占用更多磁盘空间；
插入、更新、删除需要维护更多索引内容；
写事务产生更多页面修改；
缓存中可容纳的索引项变少。

索引不是免费的只读加速器。对嵌入式 SQLite，索引页和表页都受进程缓存、操作系统缓存以及存储设备延迟影响。

四、哪些写法会让索引难以使用

4.1 对列包函数

假设：

CREATE INDEX users_email_idx ON users(email);

下面的查询：

WHERE lower(email) = 'a@example.com'

通常不能直接使用 email 的普通索引，因为索引保存的是 email，而查询需要的是 lower(email)。

可以改为表达式索引：

CREATE INDEX users_email_lower_idx
ON users(lower(email));

但还要确认：

查询表达式与索引表达式匹配；
函数语义稳定；
排序规则和大小写规则符合业务预期。

如果业务需要大小写不敏感的唯一约束，最好在数据模型层明确设计，而不是在每次查询时临时转换。

4.2 隐式转换不是可靠的索引设计

下面两列虽然都看起来“存数字”，但语义不同：

CREATE TABLE t1 (code TEXT);
CREATE TABLE t2 (code INTEGER);

对 t1.code 使用数字参数、对 t2.code 使用文本参数，比较结果可能仍然合理，但不能由此推断：

所有混合类型比较都等价；
所有比较都能使用索引；
排序结果一定符合数字顺序；
连接两侧的键一定具有相同语义。

例如：

TEXT 排序：'10' < '2'
数值排序：10 > 2

如果编码是字符串，就按字符串设计；如果是数值，就使用数值表示。不要用隐式转换承担数据建模责任。

4.3 `LIKE` 的范围

普通索引对前缀匹配可能有帮助：

WHERE name LIKE 'sqlite%'

因为已知前缀可以对应到一个索引范围。但下面这种包含前导通配符的查询：

WHERE name LIKE '%sqlite%'

无法从 B-tree 的开头定位，通常需要扫描大量值。

是否能够优化 LIKE 'prefix%'，还受以下因素影响：

列的排序规则；
LIKE 的大小写行为；
索引使用的 collation；
PRAGMA case_sensitive_like 等配置；
模式是否为编译期可识别的常量或可处理的参数。

因此不能只看到 % 的位置就作绝对判断。应使用 EXPLAIN QUERY PLAN 验证。

对于真正的全文检索，LIKE '%词%' 不是合适的通用方案，后文的 FTS5 使用不同的索引模型。

4.4 `OR`、`IN`、`NOT` 和 `NULL`

SQLite 可能对某些 OR 条件分别使用多个索引，再合并结果：

WHERE status = 'open' OR status = 'pending'

也可能因为估算成本、选择性或条件结构而选择扫描。不能假设 OR 必然等价于两个独立索引查找。

IN 通常更容易被规划器转换为多个等值查找：

WHERE status IN ('open', 'pending')

但它的比较仍受类型和 NULL 语义影响。

NULL 不是普通值：

WHERE x = NULL

结果不是 true，而是 unknown，因此不能匹配 NULL。正确写法是：

WHERE x IS NULL

同理：

WHERE x NOT IN (1, 2, NULL)

可能因为三值逻辑得到令人意外的结果。需要排除 NULL 或改用 NOT EXISTS，具体取决于业务语义。

五、查询计划：如何从“猜”变成“验证”

5.1 `EXPLAIN QUERY PLAN` 看什么

使用：

EXPLAIN QUERY PLAN
SELECT ...

可以看到高层次的访问计划。常见词语包括：

SCAN：扫描表或索引；
SEARCH：按某些约束定位索引范围；
USING INDEX：使用普通索引；
USING COVERING INDEX：查询可能只读取索引；
USE TEMP B-TREE FOR ORDER BY：需要临时结构排序；
USE TEMP B-TREE FOR GROUP BY：需要临时结构分组。

例如：

EXPLAIN QUERY PLAN
SELECT id, total_cents
FROM orders
WHERE tenant_id = 7
ORDER BY created_at DESC
LIMIT 20;

如果只有：

CREATE INDEX orders_tenant_idx ON orders(tenant_id);

SQLite 可能能快速找到租户为 7 的行，但仍需要按 created_at 排序。计划可能出现临时 B-tree。

如果改为：

CREATE INDEX orders_tenant_created_desc_idx
ON orders(tenant_id, created_at DESC);

则有机会同时满足租户过滤和倒序扫描。

“出现索引”不等于“计划已经理想”；要继续看是否有回表、临时排序，以及估计扫描范围是否足够小。

5.2 计划是估算，不是实际耗时

查询规划器需要在执行前估计成本。它通常根据：

表和索引规模；
索引列的分布统计；
条件选择性；
访问页数估计；
排序和临时结构代价；

选择一个计划。

如果统计信息过时，规划器可能错误判断。例如，某个状态列在数据中 99% 都是 'active'，只有 1% 是 'deleted'。对：

WHERE status = 'deleted'

使用索引可能很好；对：

WHERE status = 'active'

全表扫描可能更便宜。

可以在数据规模和分布发生明显变化后运行：

ANALYZE;

这会收集统计信息并写入统计表。执行前应确认：

当前连接有写权限；
统计操作不会与应用关键写事务产生不必要竞争；
应用发布或迁移流程能验证统计信息是否存在；
不要手工修改统计表来“强行调参”，除非明确理解其风险。

在较复杂的数据库上，可以针对指定表或索引执行 ANALYZE，但统计粒度和可用能力与 SQLite 版本及编译选项有关。

5.3 绑定参数与查询计划

参数化查询：

SELECT id
FROM orders
WHERE tenant_id = ?
  AND created_at >= ?;

是正确的安全和复用方式。SQLite 可能在准备语句时不知道参数的具体值，因此只能根据一般统计信息规划。

某些情况下，SQLite 会在运行时重新编译或调整计划，例如参数影响 LIKE 前缀优化时；但应用不应依赖每个参数都会生成完全不同的最优计划。若数据分布极端倾斜，应该用实际参数组合进行基准测试。

测试计划时，不能只执行一条代表性 SQL：

EXPLAIN QUERY PLAN ...

还应覆盖：

选择性很高的参数；
选择性很低的参数；
空结果；
大结果集；
排序方向和分页边界；
数据增长后的统计状态。

5.4 用实际数据验证，而不是只看小样本

在只有几十行数据的测试库中，全表扫描往往比索引查找更便宜。于是“索引没有使用”不一定是问题。

应分别验证：

数据规模扩大后计划是否仍合理；
典型数据分布下的实际耗时；
冷缓存和热缓存差异；
单次查询耗时与批量操作总耗时；
是否把过大的结果集传回应用层。

SQLite 命令行可以使用：

.timer on

再执行目标 SQL，观察实际时间。它适合快速验证，但不是完整的生产性能分析工具。更可靠的测试需要控制数据、事务边界、缓存状态、设备和并发条件。

六、部分索引、唯一索引与约束

6.1 部分索引

如果只有少数行需要参与查询，可以使用部分索引：

CREATE INDEX orders_open_idx
ON orders(tenant_id, created_at)
WHERE status = 'open';

它只包含满足 status = 'open' 的行。

对应查询：

SELECT id, created_at
FROM orders
WHERE status = 'open'
  AND tenant_id = ?
ORDER BY created_at;

有机会使用该索引。

部分索引成立的关键是：查询条件必须能够证明目标行满足索引的 WHERE 条件。下面这种动态或复杂表达式不一定能被规划器识别为同一条件：

WHERE status IN ('open', 'pending')

它不能自动等价于只访问 status = 'open' 的部分索引。

部分索引的代价通常小于全量索引，但它只服务于明确的一类查询。不要把它误认为普通索引的“更快版本”。

6.2 唯一索引既是约束也是访问路径

CREATE UNIQUE INDEX users_email_unique
ON users(email);

它同时保证非 NULL 值不重复，并为按 email 查询提供访问路径。

但 SQLite 的 UNIQUE 与 NULL 语义仍需注意：普通唯一约束通常允许多个 NULL，因为 NULL 不等于 NULL。如果业务要求邮箱不能为空，应同时声明：

email TEXT NOT NULL UNIQUE

约束失败属于数据操作错误，应用必须处理，而不是把它当作普通查询未命中。

七、FTS5：面向全文检索的倒排索引

7.1 FTS5 与普通索引解决不同问题

普通 B-tree 索引适合：

WHERE id = ?
WHERE created_at >= ?
WHERE name = ?
WHERE name LIKE 'abc%'
ORDER BY created_at

FTS5 适合在文本中检索词或词组：

WHERE documents MATCH 'sqlite'
WHERE documents MATCH 'sqlite AND index'
WHERE documents MATCH '"query planner"'

FTS5 使用倒排索引。它大致维护这样的关系：

词项       → 出现该词项的文档和位置
sqlite     → 文档 1、文档 7、文档 12
index      → 文档 1、文档 4、文档 12

查询 sqlite AND index 时，FTS5 可以对两个词项的文档集合求交集，而不是逐行扫描整段文本。

这也是 FTS5 与：

WHERE body LIKE '%sqlite%'

的根本区别。LIKE 是对字符串执行模式匹配；FTS5 先对文本分词，再对词项建立倒排索引。

7.2 建立和查询 FTS5 表

前置条件是当前 SQLite 构建启用了 FTS5。可以检查：

SELECT sqlite_version();

但版本号不能单独证明扩展已启用。创建虚拟表最直接：

CREATE VIRTUAL TABLE docs USING fts5(
    title,
    body
);

插入数据：

INSERT INTO docs(rowid, title, body) VALUES
    (1, 'SQLite 索引', 'SQLite 使用 B-tree 保存普通表和索引。'),
    (2, '全文检索', 'FTS5 使用倒排索引检索文本。'),
    (3, '查询计划', '查询规划器会选择扫描或索引查找。');

执行全文查询：

SELECT rowid, title
FROM docs
WHERE docs MATCH '索引';

MATCH 右侧是 FTS5 查询语法，不是普通 SQL 字符串比较。一个简单的布尔查询：

SELECT rowid, title
FROM docs
WHERE docs MATCH 'SQLite AND 索引';

短语查询：

SELECT rowid, title
FROM docs
WHERE docs MATCH '"查询 规划器"';

列限定查询：

SELECT rowid, title
FROM docs
WHERE docs MATCH 'title:索引';

结果集可以与普通表继续连接、过滤和排序：

SELECT d.rowid, d.title, d.body
FROM docs AS d
WHERE d MATCH ?
ORDER BY d.rowid;

应用应使用 SQL 参数绑定。需要注意，参数绑定只避免了 SQL 拼接；用户输入仍可能被解释为 FTS5 查询语法，从而产生语法错误或改变检索逻辑。若产品只允许用户输入普通关键词，应在应用层定义并实现对应的 FTS 查询转义或语法限制，不能把任意文本直接当作完整 FTS5 查询语言。

7.3 分词器决定“搜索什么”

FTS5 默认通常使用 unicode61 分词器，但具体行为受配置和文本语言影响。分词器决定：

哪些字符是词边界；
大小写如何处理；
重音符号如何处理；
中文、日文等不以空格分词的文本如何被切分；
查询词如何与索引词对应。

例如，用户搜索“数据库”，并不保证所有中文文本都按产品期望的方式拆分。FTS5 的默认分词器不是中文语义分析器，也不会自动完成同义词、拼音、词性或语义检索。

可以显式指定部分分词器选项：

CREATE VIRTUAL TABLE docs2 USING fts5(
    title,
    body,
    tokenize = 'unicode61 remove_diacritics 1'
);

如果需要中文分词，通常需要：

使用自定义 tokenizer；
在写入 FTS5 前自行分词并以空格或约定格式写入；
或选择经过验证的扩展。

自定义 tokenizer 涉及编译、部署和升级边界，不能把普通 SQLite SQL 当成完整的中文全文检索方案。

7.4 前缀搜索不是任意子串搜索

FTS5 可以配置前缀索引：

CREATE VIRTUAL TABLE docs_prefix USING fts5(
    title,
    body,
    prefix = '2 3'
);

这表示为长度为 2 和 3 的前缀建立额外索引。查询中可以使用前缀语法：

SELECT rowid, title
FROM docs_prefix
WHERE docs_prefix MATCH 'sq*';

前缀搜索适合“以某个词前缀开头”的场景。

它不等于任意子串搜索：

搜索 "qLi" 能匹配 "SQLite"

是否支持这种需求取决于 tokenizer 和索引设计，不能由普通 FTS5 词项索引直接保证。若产品明确需要任意子串、拼音首字母、模糊纠错等能力，通常需要额外的数据结构或专门搜索引擎。

7.5 相关性排序与 `bm25()`

FTS5 可以按相关性排序：

SELECT
    rowid,
    title,
    bm25(docs) AS score
FROM docs
WHERE docs MATCH ?
ORDER BY score
LIMIT 20;

FTS5 的 bm25() 分数通常是“越小越相关”，这与某些搜索系统“分数越大越相关”的约定不同。不能直接把它按降序排序而不验证结果。

bm25() 是基于词项频率、文档频率和字段权重的相关性函数，不等于业务排序。生产应用常常需要组合：

全文相关性
+ 更新时间
+ 权限过滤
+ 业务状态
+ 人工置顶

例如：

SELECT
    rowid,
    title,
    bm25(docs) AS score
FROM docs
WHERE docs MATCH ?
ORDER BY score ASC, rowid DESC
LIMIT 20;

如果加入普通字段过滤或复杂排序，FTS5 负责的是候选文档检索，后续仍可能需要额外过滤和排序。

7.6 高亮和摘要

FTS5 提供 highlight() 和 snippet() 等辅助函数，用于生成展示文本。例如：

SELECT
    rowid,
    highlight(docs, 1, '<b>', '</b>') AS highlighted_body
FROM docs
WHERE docs MATCH '索引';

列编号从 0 开始；这里的 1 表示第二列 body。

这些函数需要读取匹配位置并生成文本，不能免费获得。对大量结果调用高亮和摘要会增加 CPU 和内存开销，因此通常只对最终分页结果执行。

八、FTS5 的存储模式与一致性

8.1 普通内容表

最简单的 FTS5 表自己保存文本：

CREATE VIRTUAL TABLE docs USING fts5(
    title,
    body
);

优点是简单，插入和查询直接。缺点是：

文本在普通业务表之外又保存一份；
更新和删除需要同时考虑普通表与 FTS5 表；
业务字段无法直接替代 FTS5 的文档存储。

8.2 外部内容表

如果普通表已经保存正文，可以使用 external content：

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    body,
    content = 'articles',
    content_rowid = 'id'
);

此时 FTS5 主要保存索引，正文从 articles 读取。查询：

SELECT a.id, a.title, a.body
FROM articles_fts AS f
JOIN articles AS a
  ON a.id = f.rowid
WHERE f MATCH 'SQLite';

这种模式减少了正文重复存储，但引入了严格的一致性责任：修改 articles 不会自动修改 articles_fts，除非建立触发器或在应用事务中同步维护。

一种常见触发器方案是：

CREATE TRIGGER articles_ai
AFTER INSERT ON articles
BEGIN
    INSERT INTO articles_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad
AFTER DELETE ON articles
BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au
AFTER UPDATE ON articles
BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);

    INSERT INTO articles_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

这里的删除命令是 FTS5 特殊的维护操作，不是普通表的 DELETE FROM articles_fts 替代写法。

触发器与普通表更新在同一事务中执行。若事务回滚，两者应一起回滚；若应用绕过普通表直接修改 FTS5，或者使用错误的 rowid，索引就会失去一致性。

8.3 重建和校验

如果外部内容表已经存在，而 FTS5 索引没有正确初始化，可以使用：

INSERT INTO articles_fts(articles_fts)
VALUES ('rebuild');

这会根据外部内容表重建索引。重建期间会读取大量数据并产生写入，应用应在可接受的维护窗口执行，并准备足够的磁盘空间和事务时间。

可以使用：

INSERT INTO articles_fts(articles_fts)
VALUES ('integrity-check');

执行 FTS5 索引检查。具体可用命令和效果取决于 FTS5 配置及 SQLite 版本；执行结果应结合返回错误和应用数据抽样验证。

FTS5 的一致性不是“建表后永久保证”的属性，而是数据流的一部分：

业务表写入
   ↓
同一事务中的触发器或应用同步逻辑
   ↓
FTS5 索引更新
   ↓
提交

任何绕过这条数据流的批量导入、恢复、迁移或手工修复，都需要重新验证索引。

九、FTS5 的性能边界

FTS5 适合全文检索，但它不是没有成本。

9.1 写入成本与索引膨胀

插入或更新文本时，FTS5 需要：

对文本分词；
更新词项到文档的映射；
保存位置或列信息；
维护内部段（segment）；
在适当时机合并段。

因此，全文索引通常会增加：

写入 CPU；
数据库大小；
事务持续时间；
批量导入时的临时空间需求；
更新和删除的维护成本。

对大量文档导入，通常应明确事务边界，而不是每行一个事务：

BEGIN;

-- 批量插入普通表和/或 FTS5 表

COMMIT;

单个事务过大又可能造成较长锁持有时间、较大的回滚日志或 WAL 增长。实际边界应结合数据库连接并发、存储设备和恢复要求测试。

9.2 查询候选集仍可能很大

FTS5 能快速找到包含词项的文档，但如果查询词非常常见：

MATCH 'the'

候选集仍可能很大。之后的：

权限过滤；
连接普通表；
bm25() 计算；
高亮；
排序；
分页；

都可能成为主要成本。

全文索引解决的是“从文本中找到候选文档”的问题，不保证整个业务查询恒定为低延迟。

9.3 `OFFSET` 深分页

以下分页方式越往后通常越昂贵：

ORDER BY score
LIMIT 20 OFFSET 100000;

SQLite 仍可能需要找到并跳过前面的结果。对于需要深分页的产品，可以考虑基于稳定键的游标分页；但 FTS5 的相关性分数、并列结果和动态数据使游标设计更复杂，需要同时保存足够的排序边界。

9.4 `detail` 配置是空间与能力的取舍

FTS5 支持不同的 detail 配置，例如：

CREATE VIRTUAL TABLE compact_fts USING fts5(
    body,
    detail = 'column'
);

减少 detail 信息可能节省空间，但会限制某些位置相关能力，例如短语、邻近匹配或高亮所需的信息。不能为了减小数据库就盲目关闭细节，应先列出产品需要的查询能力，再选择配置。

十、普通索引与 FTS5 如何组合

一个实际的文章搜索系统通常不是只使用 FTS5：

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    published_at INTEGER NOT NULL,
    title TEXT NOT NULL,
    body TEXT NOT NULL
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    body,
    content = 'articles',
    content_rowid = 'id'
);

CREATE INDEX articles_filter_idx
ON articles(tenant_id, status, published_at DESC);

查询可以是：

SELECT
    a.id,
    a.title,
    bm25(f) AS score
FROM articles_fts AS f
JOIN articles AS a
  ON a.id = f.rowid
WHERE f MATCH ?
  AND a.tenant_id = ?
  AND a.status = 'published'
ORDER BY score ASC, a.published_at DESC
LIMIT 20;

这里有两类索引分别承担职责：

articles_fts：根据词项找候选文章；
articles_filter_idx：帮助普通表上的租户、状态和时间条件。

但最终效果取决于候选集大小和连接顺序。若搜索词命中几乎所有文章，普通过滤条件可能才是主要选择性；若租户过滤非常严格，先缩小普通表范围可能更合理。SQLite 会根据可见统计信息和计划成本选择路径，但对于 FTS5 与复杂连接，必须在真实数据上验证。

权限条件尤其不能只依赖 FTS5：

WHERE f MATCH ?
  AND a.tenant_id = ?

全文命中并不等于有权读取。应用仍应在数据库查询中执行租户、用户或可见性过滤，而不是先全文搜索再在内存中删除无权结果。

十一、事务、并发与索引维护的边界

普通索引和 FTS5 索引都属于数据库状态的一部分。对业务表和相关索引的维护，应尽量在同一事务中完成。

例如外部内容 FTS5 的更新流程应是：

BEGIN IMMEDIATE;

UPDATE articles
SET title = ?, body = ?
WHERE id = ?;

-- 如果没有触发器，在这里显式更新 articles_fts

COMMIT;

如果事务失败：

ROLLBACK;

普通表和全文索引必须一起回滚，否则会出现：

FTS5 返回已经不存在的 rowid；
普通表有文章但全文检索找不到；
搜索结果标题与正文不一致；
删除后的文档仍然出现在检索结果中。

在 WAL 模式下，读写并发通常比 rollback journal 模式更适合嵌入式应用，但 WAL 不会消除：

写事务之间的竞争；
长事务对 checkpoint 的影响；
FTS5 批量更新带来的写放大；
磁盘空间增长；
忙等待和超时处理。

因此，索引设计不能脱离事务边界。一个“查询只读”的 FTS5 搜索，如果与长时间写事务、checkpoint 或缓存压力叠加，仍可能出现延迟波动。相关问题需要结合 SQLite 的锁状态、WAL、checkpoint 和 busy timeout 一起诊断。

十二、诊断一条慢查询的完整路径

可以按以下顺序处理，而不是先盲目增加索引。

第一步：确认结果语义

检查列声明、实际存储类和参数绑定类型：

SELECT
    typeof(column_name),
    column_name
FROM table_name
LIMIT 20;

确认：

数字是否被保存为文本；
NULL 是否参与比较；
排序是文本顺序还是数值顺序；
COLLATE 是否一致；
FTS5 查询词是否被正确分词。

如果结果语义本身错误，优化索引没有意义。

第二步：查看查询计划

EXPLAIN QUERY PLAN
SELECT ...;

关注：

是 SCAN 还是 SEARCH；
使用了哪个索引；
是否出现覆盖索引；
是否出现临时 B-tree；
连接中的每个表采用什么访问顺序。

不要只看“有没有 USING INDEX”。

第三步：检查索引与条件是否匹配

逐项核对：

过滤列 → 索引左侧列
范围列 → 是否过早截断了后续列
排序列 → 是否与索引顺序一致
函数表达式 → 是否有对应表达式索引
部分索引条件 → 查询是否能证明满足

第四步：更新统计信息

在大量导入、删除或数据分布变化后：

ANALYZE;

然后重新检查计划和实际耗时。

第五步：测量真实成本

分别测量：

首次执行；
重复执行；
小结果集；
大结果集；
不同参数选择性；
读事务和写事务并发；
FTS5 是否调用 bm25()、高亮和连接。

最后再决定是调整索引、改写 SQL、改变数据模型、拆分查询，还是接受当前成本。

十三、常见错误与失败表现

错误一：把 SQLite 的声明类型当成强类型约束

失败表现：

同一列中出现 integer、real、text 混合存储；
比较结果依赖参数绑定类型；
排序结果与业务预期不一致。

修复方向：

使用合适的列声明；
对关键表使用 STRICT；
统一写入类型；
对金额、时间和编码采用明确表示；
用 typeof() 抽样检查历史数据。

错误二：看到索引就认为查询足够快

失败表现：

计划使用索引，但仍然很慢；
大量回表；
出现 USE TEMP B-TREE；
返回几十万行后由应用层过滤。

修复方向是分析完整数据流，而不是继续添加随机索引。

错误三：用普通 B-tree 处理包含匹配

失败表现：

WHERE body LIKE '%database%'

随着文本行数增加，查询接近全表扫描。需要词项检索时，应评估 FTS5；需要任意子串时，还要评估 tokenizer、前缀索引或其他专门结构。

错误四：外部内容 FTS5 没有同步维护

失败表现：

普通表能查到，MATCH 查不到；
MATCH 返回的 rowid 在普通表中不存在；
更新正文后搜索结果仍是旧内容。

修复方式包括触发器、同一事务中的显式维护，以及在修复后执行 rebuild 和抽样校验。

错误五：把 FTS5 查询语法当作普通文本

失败表现：

用户输入 AND、引号或括号后查询语义改变；
非法输入导致 FTS5 查询错误；
直接拼接字符串造成查询语法注入式问题。

参数绑定仍然必要，但还需要对用户搜索框的语言设计清楚：是允许完整 FTS5 语法，还是只接受普通关键词并由应用负责转换。

十四、性能边界：SQLite 什么时候不再是简单问题

SQLite 很适合：

单机嵌入式应用；
本地缓存和离线数据；
中小规模业务数据库；
读多写少或写入可控的应用；
与应用同进程、低运维复杂度的场景。

但下面的成本不能被索引掩盖：

写入串行化边界
索引越多，单次写入需要修改的页面越多。FTS5 又增加了分词和倒排索引维护成本。
内存与缓存边界
数据库和索引大于可用缓存后，随机页访问会更多依赖存储设备。
事务边界
长事务会延长锁持有时间，WAL 文件可能增长，并影响 checkpoint。
结果集边界
查询返回大量数据时，传输、解码和应用层处理常常比索引查找更贵。
全文检索边界
FTS5 提供词项检索，不自动提供中文语义理解、拼写纠错、同义词、分布式索引或跨节点搜索。
统计信息边界
规划器只能依据可见统计和成本模型估算。极端数据倾斜、复杂表达式和多表连接需要真实测试。

最终应把 SQLite 查询看成一个组合系统：

数据表示
  → 比较与转换语义
  → 索引结构
  → 查询规划
  → 表页/索引页访问
  → 排序、聚合、全文相关性
  → 事务、锁和存储设备

只有先确认类型和结果语义，再用查询计划验证访问路径，最后在真实事务与数据规模下测量，才能判断一个索引是否真正解决了问题。普通 B-tree、表达式索引、部分索引和 FTS5 并不是同一种优化工具；它们分别对应精确查找、范围与排序、条件子集以及文本词项检索。正确的设计来自查询语义与数据分布，而不是索引数量。

系列导航与关联阅读

系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
上一篇：SQLite 事务与 WAL：锁状态、并发读写、Checkpoint 和忙等待
下一篇：SQLite 工程实践：嵌入应用、备份、迁移、损坏恢复与安全

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。

SQLite 查询与索引：类型亲和性、查询计划、FTS5 和性能边界

一、先建立 SQLite 的执行模型

二、类型亲和性：声明类型不是强制类型

2.1 存储类与类型亲和性是两个概念

2.2 插入时的转换与比较时的转换不同

2.3 列亲和性、表达式亲和性和参数绑定

2.4 STRICT 表解决什么问题

三、索引到底加速了什么

3.1 从全表扫描到索引查找

3.2 复合索引的左侧连续性

3.3 等值条件、范围条件和排序

3.4 覆盖索引的收益与代价

四、哪些写法会让索引难以使用

4.1 对列包函数

4.2 隐式转换不是可靠的索引设计

4.3 LIKE 的范围

4.4 OR、IN、NOT 和 NULL

五、查询计划：如何从“猜”变成“验证”

5.1 EXPLAIN QUERY PLAN 看什么

5.2 计划是估算，不是实际耗时

5.3 绑定参数与查询计划

5.4 用实际数据验证，而不是只看小样本

六、部分索引、唯一索引与约束

6.1 部分索引

6.2 唯一索引既是约束也是访问路径

七、FTS5：面向全文检索的倒排索引

7.1 FTS5 与普通索引解决不同问题

7.2 建立和查询 FTS5 表

7.3 分词器决定“搜索什么”

7.4 前缀搜索不是任意子串搜索

7.5 相关性排序与 bm25()

7.6 高亮和摘要

八、FTS5 的存储模式与一致性

8.1 普通内容表

8.2 外部内容表

8.3 重建和校验

九、FTS5 的性能边界

9.1 写入成本与索引膨胀

9.2 查询候选集仍可能很大

9.3 OFFSET 深分页

9.4 detail 配置是空间与能力的取舍

十、普通索引与 FTS5 如何组合

十一、事务、并发与索引维护的边界

十二、诊断一条慢查询的完整路径

第一步：确认结果语义

第二步：查看查询计划

第三步：检查索引与条件是否匹配

第四步：更新统计信息

第五步：测量真实成本

十三、常见错误与失败表现

错误一：把 SQLite 的声明类型当成强类型约束

错误二：看到索引就认为查询足够快

错误三：用普通 B-tree 处理包含匹配

错误四：外部内容 FTS5 没有同步维护

错误五：把 FTS5 查询语法当作普通文本

十四、性能边界：SQLite 什么时候不再是简单问题

系列导航与关联阅读

官方资料

2.4 `STRICT` 表解决什么问题

4.3 `LIKE` 的范围

4.4 `OR`、`IN`、`NOT` 和 `NULL`

5.1 `EXPLAIN QUERY PLAN` 看什么

7.5 相关性排序与 `bm25()`

9.3 `OFFSET` 深分页

9.4 `detail` 配置是空间与能力的取舍