SQLite查询规划器未按预期工作的问题求助

问题核心 #

使用编译了ENABLE_STAT4的SQLite 3.39.2时，针对包含authorityName LIKE 'miller%'条件的JOIN查询，规划器错误选择了用于GROUP BY的ix_tr_checkInYear索引，而非匹配WHERE过滤条件的ix_tr_authorityName索引，导致查询耗时长达十余秒；手动指定索引后查询耗时骤降至0.128秒。此外，.expert工具建议的索引创建后未被规划器采用。

可行解决方案 #

1. 优化统计信息采样精度 #

SQLite的STAT4依赖数据采样估算分布，若采样量不足会导致成本计算偏差。执行以下步骤更新统计信息：

PRAGMA analysis_limit = 10000; -- 增大采样量，默认值通常为1000
ANALYZE;

重新执行原查询，观察规划器是否选择正确索引。

2. 创建针对性覆盖索引 #

为WHERE条件+查询所需列创建覆盖索引，消除回表开销，同时让规划器更容易识别其价值：

CREATE INDEX ix_tr_authorityName_covering ON treatments (
    authorityName COLLATE NOCASE, 
    treatmentId, 
    checkInYear
);
ANALYZE;

该索引直接包含查询所需的treatmentId（用于JOIN）和checkInYear（用于SELECT和GROUP BY），无需回表读取原表数据，性能最优且更易被规划器选中。

3. 临时强制指定索引（兜底方案） #

若上述方法无效，可在查询中强制指定索引，确保使用最优执行计划：

SELECT 
    checkInYear, 
    Count(DISTINCT materialsCitations.materialsCitationId) AS num 
FROM 
    treatments INDEXED BY ix_tr_authorityName
    JOIN materialsCitations 
        ON materialsCitations.treatmentId = treatments.treatmentId 
WHERE 
    treatments.authorityName LIKE 'miller%' 
GROUP BY 1;

注意：该方法属于硬编码，若未来数据分布变化，强制索引可能不再最优，仅作为临时解决方案。

4. 升级SQLite版本 #

SQLite 3.39.2发布于2022年，后续版本（如3.40+）修复了多个查询规划器的bug，包括统计信息估算和索引选择逻辑。升级至最新稳定版后，规划器可能自动选择最优索引。

原理说明 #

SQLite查询规划器基于成本估算选择执行计划，会对比不同索引的扫描行数、IO开销、回表成本等。若sqlite_stat4中的采样数据未能准确反映authorityName LIKE 'miller%'的实际匹配行数，规划器可能错误认为扫描ix_tr_checkInYear后过滤的成本更低。覆盖索引通过消除回表开销，大幅降低执行计划的估算成本，从而引导规划器做出正确选择。

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