【SQLite 内核】索引与 covering scan：什么时候真的省掉了第二次查找

第 4 篇钉住了 index b-tree 的字节布局：索引叶子 cell 只存”被索引列 + 表行 key（rowid 或 WITHOUT ROWID 主键）“，不存表的其他列。这意味着一次普通的索引查找天然要做两次二分查找——先在索引 b-tree 里定位到目标 key 拿到 rowid，再拿这个 rowid 回原表的 table b-tree 查一次，才能取到 SELECT 真正要的列。第 11 篇讲的是”选哪个索引”，本篇钉住”选定索引之后，这次查找到底要不要付这第二次查找的代价”。

常见误区是把”有索引”和”覆盖”划等号，或者把语句级的自动索引（automatic index）和 CREATE INDEX 建的持久索引混为一谈——这两者名字里都带”index”，工程含义却完全不同。本文钉三件事：

1. Covering index 具体省掉的是哪一步开销，以及 EXPLAIN QUERY PLAN 里 USING INDEX 和 USING COVERING INDEX 的判定差异，本机 3.53.2 实测三条 SELECT 的真实输出对比。
2. 自动索引（AUTOMATIC (COVERING) INDEX）的临时性、语句级生命周期，以及它和 sqlite_autoindex_*（PRIMARY KEY/UNIQUE 约束产物）之间没有关系这一点。
3. WITHOUT ROWID 表为什么在主键查找上天然满足覆盖，与第 4 篇 index b-tree 结论的闭合。

本文是「SQLite 内核」系列第 12 篇（共 17 篇）。→ 系列目录

篇目	核心内容
第 11 篇 · 查询计划器与统计	sqlite_stat*、无统计启发式、N3 join 排序
第 12 篇 · 索引与 covering scan	覆盖索引判定、自动索引边界、WITHOUT ROWID 闭合
第 13 篇 · ATTACH / 多库边界	跨库事务与锁

版本锚定：官方 Query Planning §1.7 Covering Indexes（sqlite.org/queryplanner.html）、EXPLAIN QUERY PLAN（sqlite.org/eqp.html）、The SQLite Query Optimizer Overview §14 Automatic Query-Time Indexes（sqlite.org/optoverview.html）。本文实测锚定本机 SQLite 3.53.2；BLOOM FILTER 这类 EQP 节点是较新版本才会出现的优化标注，具体版本边界未逐一核对，本文只如实转述本机输出，不做版本追溯声明。

一、二次查找：第 4 篇 index b-tree 结论的直接后果

回顾第 4 篇的关键事实：index b-tree 的叶子 cell 是”被索引列 + 表行 key”，没有单独的数据区。这意味着：

CREATE INDEX idx_orders_user ON orders(user_id);
SELECT amount FROM orders WHERE user_id=10;

即使 user_id 上有索引，amount 列的值不在索引里——计划器必须先在 idx_orders_user 上二分查找命中 user_id=10 的所有 rowid，再拿每个 rowid 回 orders 的 table b-tree 做第二次二分查找才能取出 amount。官方 Query Planning 文档把这个过程叫作两次”binary search”：第一次在索引上，第二次在原表上。这不是 SQLite 特有的低效，是”key 与其他列分离存储”这种索引数据结构的通用代价——索引本身就是一张只含部分列的辅助表。

Covering index 消除的正是第二次查找：如果 SELECT 里引用的所有列（WHERE/ORDER BY/输出列）都已经在索引里，计划器根本不需要碰原表，直接在索引 b-tree 上取数据即可。官方文档原话：“Because all of the information needed is in the covering index, SQLite never needs to consult the original table”——这是一个常数因子的改善（官方形容”roughly a doubling of the speed”），不是索引本身带来的”从 (O(N)) 到 (O(N))“那种量级跃升。

flowchart TB
  q["SELECT ... WHERE indexed_col = ?"] --> idxlookup["binary search on index b-tree<br/>find matching key + rowid"]
  idxlookup --> allcols{"all SELECT columns<br/>already in the index?"}
  allcols -->|"no"| rowidlookup["second binary search:<br/>rowid lookup on table b-tree<br/>(fetch remaining columns)"]
  allcols -->|"yes, covering"| done["return directly from<br/>index b-tree leaf,<br/>skip the table b-tree entirely"]
  rowidlookup --> done2["return row"]

二、实测：三条 SELECT 与 EQP 判定差异

复现脚本：reproduce/12-covering-index.sh。表结构与第 11 篇相同：

CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL, note TEXT);
CREATE INDEX idx_orders_user ON orders(user_id);

以下输出经本机 sqlite3 3.53.2 实际执行：

$ sqlite3 sk-cover.db "EXPLAIN QUERY PLAN SELECT user_id FROM orders WHERE user_id=10;"
QUERY PLAN
`--SEARCH orders USING COVERING INDEX idx_orders_user (user_id=?)

$ sqlite3 sk-cover.db "EXPLAIN QUERY PLAN SELECT amount FROM orders WHERE user_id=10;"
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_user (user_id=?)

$ sqlite3 sk-cover.db "EXPLAIN QUERY PLAN SELECT id, user_id FROM orders WHERE user_id=10;"
QUERY PLAN
`--SEARCH orders USING COVERING INDEX idx_orders_user (user_id=?)

三条查询用的是同一个索引、同一个 WHERE 条件，EQP 输出的差异只取决于 SELECT 列表：

第三条查询容易被误判为”应该回表”，因为 id 字面上不是索引声明的列——但 rowid 本身就是每条索引 cell 的组成部分（第 4 篇：index b-tree 的 key 由”被索引列 + 表行 key”拼成），取 rowid 不需要额外访问原表，所以依然是 covering。这条实测直接验证了”覆盖”的判定标准是”索引叶子里已经有的字节”，不是”CREATE INDEX 语句字面写了哪些列”。

三、自动索引：语句级、临时、默认开启

再造一个没有索引的 JOIN 场景，触发计划器的自动索引优化：

CREATE TABLE t1(x INTEGER, y INTEGER);
CREATE TABLE t2(x INTEGER, z INTEGER);
-- t1、t2 各 500 行，均未建索引
EXPLAIN QUERY PLAN SELECT t1.y, t2.z FROM t1 JOIN t2 ON t1.x = t2.x;

实测输出：

QUERY PLAN
|--SCAN t1
|--BLOOM FILTER ON t2 (x=?)
`--SEARCH t2 USING AUTOMATIC COVERING INDEX (x=?)

AUTOMATIC COVERING INDEX 说明计划器判断”给 t2.x 建一个临时索引，比对 t1 的每一行都对 t2 做全表扫描更便宜”（这正是第 11 篇提到的”没有 ANALYZE 时猜表有一百万行”那条默认代价假设在起作用）。BLOOM FILTER ON t2 是另一层独立优化——在真正建自动索引之前，先用一个布隆过滤器快速排除明显不匹配的 t1.x 值，减少落到自动索引上的探测次数；这是计划器输出里可能出现的又一种节点类型，本文只如实转述，不展开其内部实现。

关闭自动索引后，同一条查询退化为双重全表扫描：

$ sqlite3 sk-cover.db "PRAGMA automatic_index=OFF; EXPLAIN QUERY PLAN SELECT t1.y, t2.z FROM t1 JOIN t2 ON t1.x = t2.x;"
QUERY PLAN
|--SCAN t1
`--SCAN t2

官方 Query Optimizer Overview §14 把自动索引的性质写得很明确：它”lasts only for the duration of a single SQL statement”，“are never persisted to disk”，“are only visible to a single database connection”——语句执行完就丢弃，不写盘，也不会被其他连接看到。PRAGMA automatic_index 默认值为 1（开启），可以在编译期用 SQLITE_DEFAULT_AUTOMATIC_INDEX 改默认值，也可以用 SQLITE_OMIT_AUTOMATIC_INDEX 编译期彻底关掉这个能力。

sequenceDiagram
  participant P as sqlite3_prepare_v2
  participant Planner as Query Planner
  participant Tmp as Transient B-Tree (in-memory)
  P->>Planner: compile JOIN with no usable index on t2.x
  Planner->>Planner: estimate cost: O(N*N) full scan<br/>vs O(N log N) build + O(N log N) probe
  Planner->>Tmp: build automatic index on t2.x
  Note over Tmp: exists only for this statement,<br/>never written to disk
  P->>Tmp: probe for each t1 row
  Note over P,Tmp: statement finishes, Tmp discarded

3.1 自动索引与 sqlite_autoindex_*：两个不相关的”autoindex”

官方文档专门用一段话提醒读者不要混淆两个概念：

Do not confuse automatic indexes with the internal indexes (having names like “sqlite_autoindex_table_N”) that are sometimes created to implement a PRIMARY KEY constraint or UNIQUE constraint. The automatic indexes described here exist only for the duration of a single query… Internal indexes are part of the implementation of PRIMARY KEY and UNIQUE constraints, are long-lasting and persisted to disk… The term “autoindex” appears in the names of internal indexes for legacy reasons and does not indicate that internal indexes and automatic indexes are related.

sqlite_autoindex_table_N 是 CREATE TABLE/ALTER TABLE 声明 PRIMARY KEY 或 UNIQUE 约束时自动生成的持久索引——它和用 CREATE INDEX 手写的索引一样，写盘、跨连接可见、长期存在，只是命名规则不同。本节讨论的 automatic index（EQP 里的 AUTOMATIC (COVERING) INDEX）是查询规划期间临时搭的脚手架，两者名字撞车纯属历史遗留，官方原文直接承认”does not indicate… are related”。

SQLite 3.8.0 起，任何一次触发自动索引的语句编译都会往错误日志发一条 SQLITE_WARNING_AUTOINDEX——这是官方给应用开发者的明确信号：“这里缺一个持久索引，你应该考虑用 CREATE INDEX 补上”。反复执行同一个查询形状、每次都要重新花 (O(NN)) 建一次临时索引，是自动索引这个”够用的默认行为”留给应用自己去发现和修复的工程间隙。

四、WITHOUT ROWID 与主键覆盖

第 4 篇的结论：WITHOUT ROWID 表没有独立的 table b-tree，整张表本身就是一棵 index b-tree，key 是 PRIMARY KEY 列。这意味着按主键查找 WITHOUT ROWID 表时，天然不存在”索引 b-tree 之外还有一份数据 b-tree 需要回表”的问题——因为它压根没有第二棵树。

实测验证：

$ sqlite3 sk-cover.db <<'SQL'
CREATE TABLE wr(k INTEGER PRIMARY KEY, v TEXT) WITHOUT ROWID;
EXPLAIN QUERY PLAN SELECT v FROM wr WHERE k=5;
SQL
QUERY PLAN
`--SEARCH wr USING PRIMARY KEY (k=?)

这里 EQP 没有出现 COVERING 字样，是因为这个场景根本不需要”覆盖”这个概念来区分——WITHOUT ROWID 表的主键查找从数据结构上就只有一棵树，没有”覆盖 vs 非覆盖”的分岔路。普通 rowid 表的覆盖索引是”用一棵辅助树避免碰主树”，WITHOUT ROWID 表的主键查找是”压根只有一棵树”，两者都消除了第二次查找，但消除的方式不是同一件事，不能把”这里没写 COVERING 就说明没有优化”当成反例。

五、与第 4 篇 B-Tree 的闭合

本篇到这里，第 4 篇留下的”index b-tree 只存 key，没有单独数据区”这一事实，在计划器层面对应的具体后果已经闭合完整：

六、常见误解

1. 「表上有索引，走这个索引的查询就一定是 covering。」 见第二节实测：同一个 idx_orders_user (user_id)，SELECT user_id ... 是 covering，SELECT amount ... 不是。覆盖与否取决于 SELECT 列表是否全部落在索引叶子已有的字节里，与”是否用到了索引”是两个独立的判断维度。

2. 「自动索引和 CREATE INDEX 是同一种机制，只是名字里多了’自动’两个字。」 自动索引语句级、临时、从不写盘、单连接可见；CREATE INDEX（含 PRIMARY KEY/UNIQUE 隐式生成的 sqlite_autoindex_*）持久、写盘、跨连接可见。两者字面都含”index”/“autoindex”，官方文档专门声明二者”does not indicate… are related”——命名是历史遗留，不是设计上的关联提示。

3. 「PRAGMA automatic_index 默认是关闭的，需要手动开启才会用到自动索引。」 默认值是 1（开启）。本文第三节的实测里，不加任何 PRAGMA 就已经触发了 AUTOMATIC COVERING INDEX；反而是要显式 PRAGMA automatic_index=OFF 才能看到退化为双重 SCAN 的对照结果。

4. 「SELECT * 只要 WHERE 列有索引，也能享受覆盖索引优化。」 SELECT * 要取表的全部列，除非索引恰好覆盖了表的每一列（或者表本身是 WITHOUT ROWID，如第四节），否则一定要回表。覆盖索引通常只对”只取少数几列，且这些列恰好是索引列或 rowid”的查询有意义，SELECT * 是最不容易享受到这个优化的写法。

七、学术谱系、工程间隙与开放问题

谱系：Covering index（有时称 index-only scan）作为一种通用的关系数据库优化技术，其收益来源与第 4 篇引用的 Bayer & McCreight（1972）B-Tree 范式直接相关——只要索引结构本身是”key 单独存储、与主数据分离”，就天然存在”如果查询只碰 key 相关信息，可以完全跳过主数据”的优化空间；这不是 SQLite 独有的发明，而是页式索引结构的通用性质在查询层面的自然推论。自动索引这一具体机制则是 SQLite 面对”缺失索引场景下要不要退化为 O(N²) 嵌套扫描”这个工程问题给出的答案：用一次性构建的临时 B-Tree 换取近似哈希连接的效果，官方文档直言”An automatic index is almost the same thing as a hash join”，只是选用 B-Tree 而非专门实现一套哈希表——嵌入式库体积敏感是这个选择的直接动机。

工程间隙：自动索引解决的是单次语句的最坏情况，但文档承认它治标不治本——SQLITE_WARNING_AUTOINDEX 存在的意义就是提醒”这不是长期方案”；生产应用如果对同一形状的查询反复付出自动索引构建成本却不去补一个持久索引，等于把一次性优化当成了永久优化在用，这是文档明确划出但很容易被忽视的边界。

开放问题：第 11 篇钉住的”没有 ANALYZE 时表大小默认猜一百万行”这条启发式，同样驱动着”是否值得建自动索引”这个决策——但官方文档没有给出：当真实表远小于（如本节的 500 行）或远大于这个默认猜测时，自动索引决策的性价比曲线具体是什么形状，也没有量化”从多大的表开始，猜测偏差会导致明显错误的构建/不构建决策”。这是第 11、12 两篇共享的同一套启发式在不同优化点上留下的、尚无公开量化答案的边界，本文按现状写，不代入没跑过的数字。

八、小结

1. Covering index 消除的是”索引命中后回表取剩余列”这第二次二分查找；本机 3.53.2 实测证明覆盖与否只取决于 SELECT 列表是否落在索引叶子已有字节内（含隐式携带的 rowid），与”有没有索引”是两个不同的判断维度。
2. 自动索引（AUTOMATIC COVERING INDEX）语句级、临时、默认开启、从不写盘，与 PRIMARY KEY/UNIQUE 约束生成的持久索引 sqlite_autoindex_* 只是命名撞车，官方文档明确否认二者有设计关联；本机实测同时展示了 PRAGMA automatic_index=OFF 后计划退化为双重全表扫描。
3. WITHOUT ROWID 表的主键查找因为整表就是一棵 index b-tree（第 4 篇结论）而天然不存在”回表”这一步，EQP 用 USING PRIMARY KEY 而非 COVERING INDEX 标注这条路径——两者都消除了第二次查找，但消除方式不同，不能用字面标注的有无判断优化是否生效。

参考资料

规范与官方文档（A 级）

1. SQLite Documentation, Query Planning，§1.7 Covering Indexes（sqlite.org/queryplanner.html）——覆盖索引定义、“roughly a doubling of the speed”。
2. SQLite Documentation, EXPLAIN QUERY PLAN（sqlite.org/eqp.html）——SEARCH/USING (COVERING) INDEX 输出格式。
3. SQLite Documentation, The SQLite Query Optimizer Overview，§14 Automatic Query-Time Indexes（sqlite.org/optoverview.html）——自动索引生命周期、sqlite_autoindex_* 区分、SQLITE_WARNING_AUTOINDEX、hash join 类比。
4. SQLite Documentation, File Format For SQLite Databases，§1.6 B-tree Pages（sqlite.org/fileformat2.html，第 4 篇已引用）——index b-tree 结构，本篇引用其覆盖判定的结构依据。

实验（A 级，本机实测）

5. 本机 sqlite3 3.53.2：covering/非 covering 三条 SELECT 对比、自动索引开关对照、WITHOUT ROWID 主键查找 EQP；脚本 reproduce/12-covering-index.sh，输出见第二、三、四节。

站内

6. B-Tree 遍历与分裂——table b-tree/index b-tree 结构差异、overflow 阈值，本篇的直接前提。
7. 查询计划器与统计——无统计时表大小/重复度的默认猜测，驱动本篇自动索引决策的同一套启发式。
8. 本系列 index、PLAN.md。

上一篇：查询计划器与统计 下一篇：ATTACH / 多库边界