覆盖索引对JOIN+BETWEEN操作是否有效？为何联合索引未用第三列？

我想了解覆盖索引在先按某列执行JOIN，再对第二、第三列施加BETWEEN约束场景下的作用，同时疑惑包含这三列的联合索引是否能带来积极效果？

示例SQL #

CREATE TABLE tbl1(firstcol TEXT);
CREATE TABLE tbl2(firstcol TEXT, secondcol INTEGER, thirdcol INTEGER);

INSERT INTO tbl1 VALUES ('hello');
INSERT INTO tbl1 VALUES ('good morning');
INSERT INTO tbl2 VALUES ('hello', '1', '1');
INSERT INTO tbl2 VALUES ('hello', '5', '210');
INSERT INTO tbl2 VALUES ('bonjour', '999', '1');
INSERT INTO tbl2 VALUES ('hello', '4', '20');

CREATE INDEX myfirstindex ON tbl1 (firstcol);
CREATE INDEX mymultiindex ON tbl2 (firstcol, secondcol, thirdcol);

SELECT *
FROM tbl1
JOIN tbl2 USING(firstcol)
WHERE
    tbl2.secondcol BETWEEN 0 AND 100
    AND tbl2.thirdcol BETWEEN 0 AND 50

查询执行计划 #

id  parent  notused detail
3   0   0   SCAN tbl1
5   0   0   SEARCH tbl2 USING COVERING INDEX mymultiindex (firstcol=? AND secondcol>? AND secondcol<?)

执行EXPLAIN QUERY PLAN后发现仅用到了联合索引的前两列，为何优化器未利用第三列？

覆盖索引的作用与联合索引的价值 #

这个场景中的mymultiindex作为覆盖索引，确实能带来显著性能收益：

• 它是覆盖索引，查询所需的所有列（firstcol、secondcol、thirdcol）都包含在索引结构中，SQLite无需回表访问tbl2的原始数据页，直接从索引即可获取全部结果，大幅减少磁盘IO开销。
• 联合索引的前两列被高效用于数据过滤：firstcol用于JOIN关联，快速定位与tbl1匹配的行；secondcol的BETWEEN约束进一步缩小结果集范围，这两步都是基于索引的有序扫描，效率远高于全表扫描。

为何优化器未利用第三列的索引部分 #

SQLite查询优化器没有使用thirdcol的索引部分，核心原因是联合索引的有序性依赖于列的定义顺序：
联合索引是按列的定义顺序逐层构建的有序结构，在firstcol等值匹配+secondcol范围扫描之后，thirdcol的全局有序性已经被破坏——同一secondcol范围内的thirdcol值并非有序排列，优化器无法通过索引直接对thirdcol执行范围过滤。

举个实际例子：在firstcol='hello'且secondcol处于0-100的行中，thirdcol的值为1、210、20，这些值是无序的，优化器无法利用索引的thirdcol部分快速筛选0-50的范围，只能在通过前两列过滤后的结果集中，逐条检查thirdcol的条件（不过因为是覆盖索引，这一步是在索引数据内完成，无需回表到原表）。

如果想让thirdcol的约束也能利用索引，可根据业务场景调整：比如调换secondcol和thirdcol的索引顺序（需结合常用查询模式权衡），不过在当前这种"等值+范围+范围"的查询模式下，SQLite的优化器逻辑不会主动利用第三列的索引部分。

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