SQLite FTS查询执行计划疑问及关联查询优化咨询

我有一个包含三张表的小型SQLite数据库：

• manga表：存储漫画，包含id字段
• tag表：包含id、name字段
• manga_tag_association表：存储tag_id与manga_id的关联关系

为了实现按标题搜索漫画并返回对应标签的功能，我使用SQLite内置的FTS虚拟表（mangafts）存储漫画标题及id。

最初的查询尝试 #

我最初编写了以下查询语句：

SELECT manga.title, GROUP_CONCAT(tag.name) tags FROM manga
JOIN mangafts fts ON fts.manga_id = manga.id
JOIN manga_tag_association ass ON ass.manga_id = manga.id
JOIN tag ON tag.id = ass.tag_id
WHERE fts.title MATCH 'mushishi' GROUP BY manga.id;

我原本预期会优先扫描FTS表再做关联，但查询计划显示先扫描manga表：

QUERY PLAN
|--SCAN manga
|--SEARCH ass USING AUTOMATIC COVERING INDEX (manga_id=?)
|--SEARCH tag USING INTEGER PRIMARY KEY (rowid=?)
`--SCAN fts VIRTUAL TABLE INDEX 3:

修改ON子句后的查询 #

我尝试将MATCH条件移到ON子句中：

SELECT manga.title, GROUP_CONCAT(tag.name) tags FROM manga
JOIN mangafts fts
  ON (fts.title MATCH 'mushishi' AND fts.manga_id = manga.id)
JOIN manga_tag_association ass ON ass.manga_id = manga.id
JOIN tag ON tag.id = ass.tag_id
GROUP BY manga.id;

但执行计划和之前完全一致。

我的疑问 #

1. 为何会先扫描manga表？
2. 为何不优先扫描FTS表以提升搜索速度？
3. 当前写法是否不符合需求？

后续修正的查询（包含无标签漫画） #

我了解到MATCH的正确写法应为fts_table_name MATCH 'column: text to search'，于是改写了查询语句以包含无标签的漫画：

SELECT manga.id, manga.title, GROUP_CONCAT(tag.name) AS tags FROM manga
LEFT JOIN manga_tag_association ass ON ass.manga_id = manga.id
LEFT JOIN tag ON tag.id = ass.tag_id
JOIN mangafts ON mangafts.manga_id = manga.id
WHERE mangafts MATCH 'title: mushishi' GROUP BY manga.id;

此时查询计划变为优先扫描FTS表：

QUERY PLAN
|--SCAN mangafts VIRTUAL TABLE INDEX 4:
|--SEARCH manga USING INTEGER PRIMARY KEY (rowid=?)
|--SCAN ass LEFT-JOIN
|--SEARCH tag USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
`--USE TEMP B-TREE FOR GROUP BY

问题解答 #

1. 为何最初会先扫描manga表？ #

SQLite查询优化器会根据表的数据量、索引情况、查询条件的过滤能力选择执行顺序。最初的查询中，fts.title MATCH 'mushishi'不是FTS虚拟表的标准匹配语法，优化器没能识别这是一个可利用FTS索引的高效过滤条件。再加上JOIN语句从manga表开始，当优化器判断manga表数据量较小时，就会选择先扫描manga表，再依次关联其他表。

2. 为何不优先扫描FTS表？ #

FTS虚拟表的高效查询依赖标准的表名 MATCH '列名: 查询内容'语法。最初的写法让优化器无法正确识别这是一个能快速缩小结果集的FTS索引查询，因此没有优先选择扫描FTS表。

改用标准MATCH写法后，优化器能明确识别到这个条件可以通过FTS索引快速定位目标数据，自然会优先扫描FTS表——先过滤出少量符合条件的漫画ID，再关联其他表，整体查询成本更低。

3. 当前写法是否不符合需求？ #

最初的两种写法确实不符合需求：

• 非标准MATCH语法导致优化器选择了低效执行路径，没利用FTS表的索引优势；
• 使用INNER JOIN关联标签表，会过滤掉无标签的漫画，无法覆盖所有搜索结果；
• 执行顺序低效，没有优先缩小结果集。

后续修正的写法完全符合需求：

• 采用标准FTS MATCH语法，让优化器优先扫描FTS表，大幅提升搜索速度；
• 使用LEFT JOIN关联标签表，保留了无标签的漫画，结果更完整；
• 能正确返回搜索到的漫画及其对应标签（无标签时tags字段为NULL）。

内容的提问来源于stack exchange，提问作者M I P A