为何组合两个快速WHERE子句后SQLite查询大幅变慢？

问题背景 #

MyTable表的所有列均单独建立了索引，以下两个单条件查询执行时间都在15毫秒以内：

• select id from MyTable where end > 1704067100000（仅返回1条记录）
• select id from MyTable where start < 1704067900000（返回几乎全表记录）

但将两个条件组合后，查询耗时骤升至500毫秒：

select id From MyTable where end > 1704067100000 and start < 1704067900000

改用子查询写法，耗时依然约500毫秒：

select id
from (select id, start
    from MyTable
    where end > 1704067100000)
where start < 1704067900000

执行查询计划分析：

explain query plan select id From MyTable where end > 1704067100000 and start < 1704067900000

得到结果：

SEARCH TABLE MyTable USING INDEX IX_MyTable_Start (Start<?)

原因分析 #

SQLite查询优化器错误选择了IX_MyTable_Start索引，这个索引对应的条件会返回几乎全表数据，之后需要逐行过滤end > 1704067100000的条件，本质是全表级别的扫描筛选，所以耗时剧增。

明明end > ...条件仅返回1条记录，优化器却没选对应的IX_MyTable_End索引，大概率是因为表的统计信息过时，或者优化器对数据分布的判断出现偏差，导致索引选择失误。

解决办法 #

1. 强制指定索引：直接让SQLite使用IX_MyTable_End索引，先筛选出仅1条符合end条件的记录，再判断start条件，成本极低：

select id from MyTable indexed by IX_MyTable_End where end > 1704067100000 and start < 1704067900000

2. 创建复合索引：针对(end, start)创建复合索引，让优化器可以通过索引直接匹配两个条件，无需二次过滤：

create index IX_MyTable_End_Start on MyTable(end, start);

3. 更新统计信息：执行analyze命令让SQLite重新收集表的统计数据，帮助优化器做出正确的索引选择：

analyze MyTable;

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