如何让SQLite使用基于LIKE子句创建的部分索引？

问题背景 #

尝试为courses表创建基于LIKE条件的部分索引，索引创建成功，但执行匹配特定学期的查询时，SQLite仍执行全表扫描，改用IN子句硬编码目标值也无效。

表结构 #

CREATE TABLE IF NOT EXISTS "courses" (
    "id" INTEGER,
    "department" TEXT NOT NULL,
    "number" INTEGER NOT NULL,
    "semester" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    PRIMARY KEY("id")
);

创建的部分索引语句 #

CREATE INDEX "course_semester" ON
"courses" ("semester")
WHERE 1=1
AND (
"semester" LIKE '%2023'
or "semester" LIKE '%2024'
)

未触发索引的查询语句 #

SELECT "department", "number", "title"
FROM "courses"
WHERE 1=1
AND "semester" = 'Fall 2023';

问题原因 #

SQLite的部分索引仅在查询条件完全包含索引过滤条件，或查询条件能被索引过滤条件蕴含时，才会被选中使用。当前索引的过滤条件是semester LIKE '%2023' OR semester LIKE '%2024'，而查询条件semester = 'Fall 2023'虽然满足索引的过滤逻辑，但SQLite无法自动推导该等值条件必然符合LIKE匹配规则，因此不会触发使用该部分索引。

解决方法 #

方法一：查询语句显式包含索引过滤条件 #

在查询的WHERE子句中加入索引的过滤逻辑，让SQLite明确查询范围落在索引覆盖范围内：

SELECT "department", "number", "title"
FROM "courses"
WHERE "semester" = 'Fall 2023'
AND (semester LIKE '%2023' OR semester LIKE '%2024');

方法二：重新设计部分索引的过滤条件（推荐） #

如果业务场景仅关注2023、2024年的学期，可通过字符串函数提取年份，让索引条件更清晰，便于SQLite推导匹配：

CREATE INDEX "course_semester_2023_2024" ON "courses" ("semester")
WHERE SUBSTR(semester, -4) IN ('2023', '2024');

此时查询semester = 'Fall 2023'时，SQLite可推导出SUBSTR('Fall 2023', -4) = '2023'满足索引条件，自动启用索引。

方法三：改用普通字段索引 #

若查询范围不局限于2023、2024年，可直接创建普通的semester字段索引，覆盖所有相关查询：

CREATE INDEX "course_semester" ON "courses" ("semester");

验证索引是否生效 #

执行查询前，用EXPLAIN QUERY PLAN查看执行计划，若输出包含SEARCH TABLE courses USING INDEX course_semester (...)，则说明索引已被使用：

EXPLAIN QUERY PLAN
SELECT "department", "number", "title"
FROM "courses"
WHERE "semester" = 'Fall 2023';

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