如何强制SQLite连接临时表时使用索引而非全表扫描？

我有一个包含十亿行数据的SQLite数据库表obj2，以及用于行筛选的临时表__tmp_selection，表结构如下：

CREATE TABLE [obj2](
  [id] integer PRIMARY KEY ASC,
  [name] text,
  [height] integer,
  [identification] text,
  [width] integer
);

CREATE TEMPORARY TABLE [__tmp_selection] (id INTEGER PRIMARY KEY);

当我使用IN子句指定具体行ID时，SQLite会高效利用obj2.id上的索引：

EXPLAIN QUERY PLAN
SELECT obj2.* FROM obj2 WHERE rowid IN (10,20,30,40,50);

查询计划输出:

这表明SQLite正确使用了obj2.id上的索引。

但当我将相同ID插入临时表并与obj2连接时，SQLite却对obj2执行全表扫描，效率极低：

INSERT INTO __tmp_selection(id) VALUES (10);
INSERT INTO __tmp_selection(id) VALUES (20);
INSERT INTO __tmp_selection(id) VALUES (30);
INSERT INTO __tmp_selection(id) VALUES (40);
INSERT INTO __tmp_selection(id) VALUES (50);

EXPLAIN QUERY PLAN
SELECT obj2.* FROM obj2
JOIN __tmp_selection ON obj2.id = __tmp_selection.id;

查询计划输出:

这说明SQLite正在扫描整个obj2表，尽管连接本可以使用obj2.id上的索引。

解决方法 #

要让SQLite优先扫描数据量少的临时表__tmp_selection，并利用obj2的主键索引，可通过以下几种方式实现：

1. 用IN子句子查询替代JOIN

直接将临时表作为子查询放入IN条件，复用高效的索引查找逻辑：

EXPLAIN QUERY PLAN
SELECT obj2.* FROM obj2 
WHERE obj2.id IN (SELECT id FROM __tmp_selection);

这种写法会让SQLite先获取临时表的ID列表，再逐个通过主键索引查找obj2的对应行，避免全表扫描。

2. 强制指定连接顺序（SQLite 3.32.0+）

使用ORDERED提示，告诉优化器按照FROM子句的顺序处理表，先扫描临时表：

EXPLAIN QUERY PLAN
SELECT ORDERED obj2.* FROM obj2
JOIN __tmp_selection ON obj2.id = __tmp_selection.id;

3. 更新临时表统计信息

SQLite优化器依赖统计信息判断扫描成本，临时表的统计信息可能不足导致误判，手动更新后优化器能识别临时表数据量小：

ANALYZE __tmp_selection;

4. 调整连接类型为LEFT JOIN

将临时表作为驱动表，先扫描它再匹配obj2：

EXPLAIN QUERY PLAN
SELECT obj2.* FROM __tmp_selection
LEFT JOIN obj2 ON __tmp_selection.id = obj2.id
WHERE obj2.id IS NOT NULL;

内容的提问来源于stack exchange，提问作者zdenek