【SQLite 内核】索引与 covering scan：什么时候真的省掉了第二次查找
内容提要
本文探讨SQLite覆盖索引与自动索引机制。覆盖索引可省去回表二次查找，是否覆盖取决于SELECT列是否全在索引中，与有无索引无关。自动索引是语句级临时索引，默认开启，与持久索引sqlite_autoindex_*无关。WITHOUT ROWID表主键查找天然无需回表。实测验证了这些判定差异。
延伸解读
覆盖索引的判定标准
覆盖索引的判定标准是SELECT列表中的列是否全部包含在索引叶子节点中，而不是是否使用了索引。例如，对于索引idx_orders_user(user_id)，查询SELECT user_id会使用覆盖索引，而SELECT amount则不会，因为amount不在索引中。此外，INTEGER PRIMARY KEY是rowid的别名，索引叶子天然包含rowid，因此查询SELECT id, user_id也能使用覆盖索引。
自动索引与持久索引的区别
自动索引是语句级的临时索引，仅在单个SQL语句执行期间存在，不写盘，且仅对当前连接可见。它由查询规划器在缺少索引时自动创建，默认开启。而sqlite_autoindex_*是PRIMARY KEY或UNIQUE约束自动生成的持久索引，写盘且跨连接可见。两者名称相似但机制完全不同，官方文档明确说明它们没有关联。
WITHOUT ROWID表的覆盖特性
WITHOUT ROWID表本身是一棵索引B-tree，没有独立的表B-tree，因此按主键查找时天然不需要回表，不存在覆盖与非覆盖的区分。EXPLAIN QUERY PLAN输出中显示USING PRIMARY KEY而非COVERING INDEX，但这并不意味着没有优化，而是因为数据结构上只有一棵树，无需额外标注。
Q&A
SQLite中覆盖索引（covering index）具体省掉了哪一步操作？
覆盖索引省掉了索引查找后回原表（table b-tree）进行第二次二分查找以获取非索引列的操作。如果SELECT语句所需的所有列都包含在索引中，SQLite可以直接从索引叶子节点返回数据，无需访问原表。
如何判断一个查询是否使用了覆盖索引？
通过EXPLAIN QUERY PLAN的输出判断：如果显示“USING COVERING INDEX”，则表示使用了覆盖索引；如果只显示“USING INDEX”，则表示未使用覆盖索引，需要回表。覆盖与否取决于SELECT列表中的列是否全部包含在索引中（包括隐式的rowid），与是否使用索引无关。
SQLite中的自动索引（automatic index）和sqlite_autoindex_*有什么区别？
自动索引是查询规划期间为优化JOIN等操作临时创建的索引，仅存在于单个SQL语句执行期间，不写入磁盘，且仅对当前连接可见。而sqlite_autoindex_*是PRIMARY KEY或UNIQUE约束自动生成的持久索引，长期存在并写入磁盘，跨连接可见。两者名称相似但机制完全不同，官方文档明确说明它们没有关联。
SQLite中自动索引默认是开启还是关闭？如何关闭？
自动索引默认是开启的（PRAGMA automatic_index=1）。可以通过执行PRAGMA automatic_index=OFF来关闭，关闭后查询计划会退化为双重全表扫描。
为什么WITHOUT ROWID表的主键查找不需要回表？
因为WITHOUT ROWID表没有独立的table b-tree，整张表本身就是一棵index b-tree，其key是PRIMARY KEY列。因此按主键查找时，数据直接存储在这棵树上，不存在第二棵树需要回表，天然避免了第二次查找。
SELECT * 查询能否使用覆盖索引优化？
通常不能。SELECT *需要获取表的所有列，除非索引恰好覆盖了所有列（或表是WITHOUT ROWID），否则必须回表。覆盖索引通常只对查询少数列且这些列恰好是索引列或rowid的情况有效。
SQLite中自动索引的构建成本是怎样的？
自动索引在每次执行相关语句时都会重新构建，构建成本为O(N log N)，其中N是表的大小。如果同一查询形状反复执行，每次都会付出构建成本，官方建议通过SQLITE_WARNING_AUTOINDEX警告识别并创建持久索引来优化。
