优化WHERE IN查询：提升SQLite多表关联查询性能

现有两张关联表，结构如下：

CREATE TABLE 'data' (
    HashCode INTEGER PRIMARY KEY NOT NULL,
    OtherColumns INTEGER NOT NULL
);
CREATE TABLE 'datahash' (
    PrimaryKey INTEGER PRIMARY KEY NOT NULL,
    HashCode INTEGER NOT NULL,
    FOREIGN KEY (HashCode) REFERENCES 'data' (HashCode) ON DELETE CASCADE
);

数据先存入data表，再存入datahash表，通过HashCode字段关联；当data表中的HashCode被删除时，会级联删除datahash表的对应行。

需要从指定PrimaryKey列表查询匹配的data表数据，当前使用的查询语句：

SELECT 'datahash'.PrimaryKey, 'data'.* FROM 'data'
    JOIN 'datahash' ON 'datahash'.PrimaryKey IN (SELECT PrimaryKey FROM 'templist') AND 'datahash'.HashCode = 'data'.HashCode
    LIMIT 1000000;

其中templist是内存临时表，创建语句：

PRAGMA temp_store = MEMORY;
CREATE TEMP TABLE 'templist' (
    PrimaryKey INTEGER PRIMARY KEY NOT NULL
);

使用临时表是因为代码难以处理IN子句内的动态数量参数。

当前查询速度较慢，执行计划如下：

性能数据：

• 向templist插入10万行数据耗时约110ms
• 在data和datahash各100万行数据的情况下，关联查询耗时约420ms

已在单事务中执行插入与查询操作，求进一步提升查询性能的方法。

1. 调整查询关联顺序 #

当前查询从data表出发关联datahash，但实际需求是先筛选templist中的PrimaryKey对应的datahash行，再关联data。修改查询语句，让数据库优先处理小数据集：

SELECT dh.PrimaryKey, d.*
FROM templist tl
JOIN datahash dh ON tl.PrimaryKey = dh.PrimaryKey
JOIN data d ON dh.HashCode = d.HashCode
LIMIT 1000000;

这样数据库会先通过templist的主键快速匹配datahash的主键，再通过datahash的HashCode关联data的主键，减少无效关联操作。

2. 给datahash的HashCode添加索引 #

data的HashCode是主键，但datahash的HashCode仅有外键约束，无单独索引。添加索引可加快datahash与data的关联速度：

CREATE INDEX idx_datahash_hashcode ON datahash(HashCode);

注：若datahash的HashCode重复率极高，索引收益有限，但多数场景下能有效提升关联效率。

3. 优化临时表插入方式 #

批量插入templist时，使用多行插入语句代替循环单行插入，减少IO和事务开销：

INSERT INTO templist (PrimaryKey) VALUES (1), (2), (3), ..., (100000);

这种方式能大幅降低插入耗时，优化整体流程效率。

4. 调整SQLite核心参数 #

根据服务器内存和业务需求，调整以下PRAGMA参数：

• PRAGMA cache_size = -20000;：设置缓存大小（单位为页，默认每页4KB，-20000代表80MB缓存，可按需调整）
• PRAGMA journal_mode = WAL;：开启WAL模式，提升并发读写性能，适合多线程或频繁读写场景
• PRAGMA synchronous = OFF;：若对数据一致性要求不极端，关闭同步写入，可显著提升读写速度，但断电存在数据丢失风险

5. 避免全字段查询 #

如果不需要data表的所有字段，明确列出所需字段，代替data.*，减少数据传输与解析开销：

SELECT dh.PrimaryKey, d.HashCode, d.OtherColumns
FROM templist tl
JOIN datahash dh ON tl.PrimaryKey = dh.PrimaryKey
JOIN data d ON dh.HashCode = d.HashCode
LIMIT 1000000;

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