sqlite - 如何更快地获得按另一个表中的字段排序的 FTS4 查询结果？

Question

背景

我正在对存储在 SQLite 中的电子邮件正文实现全文搜索，利用其出色的内置 FTS4引擎。我得到了一些相当差的查询性能，虽然不是我所期望的。让我们来看看。

代表架构

我将给出相关代码的一些简化示例，并在适用的情况下提供完整代码的链接。

我们有一个 MessageTable存储有关电子邮件消息的数据(完整版本分布在多个文件 here 、 here 和 here ):

CREATE TABLE MessageTable (
    id INTEGER PRIMARY KEY,
    internaldate_time_t INTEGER
);
CREATE INDEX MessageTableInternalDateTimeTIndex
    ON MessageTable(internaldate_time_t);

可搜索文本被添加到名为 MessageSearchTable 的 FTS4 表中。 (完整版 here):

CREATE VIRTUAL TABLE MessageSearchTable USING fts4(
    id INTEGER PRIMARY KEY,
    body
);

id在搜索表中充当消息表的外键。

我将把它作为练习让读者将数据插入这些表中(我当然不能透露我的私有(private)电子邮件)。我在每个表中只有不到 26k 条记录。

问题查询

当我们检索搜索结果时，我们需要将它们按 internaldate_time_t 降序排列。所以我们只能提取最近的几个结果。这是一个示例搜索查询(完整版 here):

SELECT id
FROM MessageSearchTable
JOIN MessageTable USING (id)
WHERE MessageSearchTable MATCH 'a'
ORDER BY internaldate_time_t DESC
LIMIT 10 OFFSET 0

在我的机器上，使用我的电子邮件，运行时间约为 150 毫秒，通过以下方式测量:

time sqlite3 test.db <<<"..." > /dev/null

150 毫秒不是查询的野兽，但对于简单的 FTS 查找和索引顺序，它是缓慢的。如果我省略 ORDER BY ，例如，它在 10 毫秒内完成。还要记住，实际的查询还有一个子选择，所以一般来说还有一点工作要做:完整版的查询运行大约 600 毫秒，这是野兽的领域，并省略了 ORDER BY在这种情况下，时间会减少 500 毫秒。

如果我打开里面的统计信息 sqlite3并运行查询，我注意到这一行:

Sort Operations:                     1

如果我的解释 docs about those stats是正确的，看起来查询完全跳过使用 MessageTableInternalDateTimeTIndex .完整版的查询也有这样一行:

Fullscan Steps:                      25824

听起来它正在某处走动 table ，但现在让我们忽略它。

我发现了什么

所以让我们努力优化一下。我可以将查询重新排列为子选择并强制 SQLite 使用我们的索引和 INDEXED BY 延期:

SELECT id
FROM MessageTable
INDEXED BY MessageTableInternalDateTimeTIndex
WHERE id IN (
    SELECT id
    FROM MessageSearchTable
    WHERE MessageSearchTable MATCH 'a'
)
ORDER BY internaldate_time_t DESC
LIMIT 10 OFFSET 0

瞧，运行时间已经下降到 100 毫秒左右(完整版查询为 300 毫秒，运行时间减少了 50%)，并且没有报告排序操作。请注意，只需像这样重新组织查询，而不是使用 INDEXED BY 强制索引，仍然有一个排序操作(尽管我们仍然奇怪地减少了几毫秒)，所以看起来 SQLite 确实忽略了我们的索引，除非我们强制它。

我还尝试了其他一些事情，看看它们是否会有所作为，但它们没有:

显式制作索引 DESC如所述 here , 有无 INDEXED BY

显式添加 id索引中的列，有无 internaldate_time_t已订购 DESC , 有无 INDEXED BY

可能还有其他几件事我现在不记得了

问题

这里的 100 毫秒似乎仍然非常慢，因为它似乎应该是一个简单的 FTS 查找和索引顺序。

这里发生了什么？为什么它会忽略明显的索引，除非你强制它的手？

合并来自虚拟表和常规表的数据是否遇到了一些限制？

为什么它仍然如此相对缓慢，我还能做些什么来让另一个表中的字段对 FTS 匹配进行排序？

谢谢!

Answer 1

索引对于根据索引列的值查找表行很有用。
一旦找到表行，索引就不再有用，因为按任何其他标准在索引中查找表行效率不高。

这意味着不能为查询中访问的每个表使用多个索引 .

另请参阅文档:Query Planning , Query Optimizer .

您的第一个查询包含以下 EXPLAIN QUERY PLAN输出:

0 0 0 SCAN TABLE MessageSearchTable VIRTUAL TABLE INDEX 4: (~0 rows)
0 1 1 SEARCH TABLE MessageTable USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 USE TEMP B-TREE FOR ORDER BY

发生的事情是

FTS 索引用于查找所有匹配的 MessageSearchTable行；

对于在 1. 中找到的每一行，MessageTable主键索引用于查找匹配的行；

2. 中找到的所有行都使用临时表进行排序；

返回前 10 行。

您的第二个查询具有以下 EXPLAIN QUERY PLAN 输出:

0 0 0 SCAN TABLE MessageTable USING COVERING INDEX MessageTableInternalDateTimeTIndex (~100000 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 0 SCAN TABLE MessageSearchTable VIRTUAL TABLE INDEX 4: (~0 rows)

发生的事情是

FTS 索引用于查找所有匹配的 MessageSearchTable行；

SQLite 遍历 MessageTableInternalDateTimeTIndex 中的所有条目在索引顺序中，当 id 时返回一行value 是在步骤 1 中找到的值之一。
SQLite 在第十个这样的行之后停止。

在此查询中，可以使用索引进行(隐式)排序，但这只是因为没有使用其他索引来查找此表中的行。
以这种方式使用索引意味着 SQLite 必须遍历所有条目，而不是查找与某些其他条件匹配的几行。

当您省略 INDEXED BY 时从你的第二个查询子句，你会得到以下 EXPLAIN QUERY PLAN 输出:

0 0 0 SEARCH TABLE MessageTable USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 0 SCAN TABLE MessageSearchTable VIRTUAL TABLE INDEX 4: (~0 rows)
0 0 0 USE TEMP B-TREE FOR ORDER BY

这与您的第一个查询基本相同，只是连接和子查询的处理方式略有不同。

使用您的表结构，真的不可能变得更快。
您正在执行三个操作:

在 MessageSearchTable 中查找行;

在 MessageTable 中查找相应的行;

按 MessageTable 对行进行排序值(value)。

就索引而言，步骤 2 和步骤 3 相互冲突。
数据库必须选择是在第 2 步(在这种情况下必须明确进行排序)还是在第 3 步(在这种情况下它必须遍历所有 MessageTable 条目)使用索引。

您可以尝试通过将消息时间作为 FTS 表的一部分并仅搜索最近几天(如果没有获得足够的结果，则增加或删除时间)来尝试从 FTS 搜索中返回较少的记录。