如何能够更快地获取由另一张表中的字段排序的FTS4查询结果？

Question

如何能够更快地获取由另一张表中的字段排序的FTS4查询结果？

sqlitefts3fts4

16

背景

我正在实现对存储在SQLite中的电子邮件消息体进行全文搜索，利用其内置的FTS4引擎。尽管不是完全预料到的，但我得到了一些非常糟糕的查询性能。让我们来看看。

代表性架构

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

我们有一个名为MessageTable的表格，它存储有关电子邮件消息的数据（完整版本分散在几个文件中，此处、此处和此处）：

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

可以在名为 MessageSearchTable 的 FTS4 表中添加可搜索的文本（完整版本在此处）：

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

搜索表中的id作为消息表的外键。

我会留给读者将数据插入这些表格的习题（我当然不能透露我的私人电子邮件）。每个表格中都有将近26k条记录。

问题查询

当我们检索搜索结果时，我们需要按internaldate_time_t降序排列，以便只获取最近的几个结果。以下是一个示例查询（完整版本在此处）：

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毫秒并不是一个复杂的查询，但对于简单的全文搜索和索引排序来说，它有些缓慢。例如，如果我省略 ORDER BY，则仅需10毫秒即可完成。请注意，实际查询还有一个子查询，因此通常会进行更多的工作：完整版本的查询运行大约需要600毫秒，这已经进入到较为缓慢的范畴了。在这种情况下省略 ORDER BY 可以节省500毫秒的时间。

如果我在sqlite3中打开统计信息并运行查询，则会注意到以下行：

Sort Operations:                     1

如果我对关于这些统计数据的文档的解释正确的话，那么看起来查询完全没有使用“MessageTableInternalDateTimeTIndex”。完整版本的查询还包括以下行：

有关这些统计数据的文档我的理解是，查询完全跳过了使用“MessageTableInternalDateTimeTIndex”的步骤。查询的完整版本还包括以下行：

Fullscan Steps:                      25824

听起来好像在某个地方遍历表格，但现在先不考虑这个。

我发现了什么

那么让我们尝试优化一下。我可以把查询改成子查询，并使用INDEXED BY扩展来强制SQLite使用我们的索引：

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，带或不带INDEXED BY
在索引中显式添加id列，带或不带internaldate_time_t以DESC排序，带或不带INDEXED BY
可能还有其他我此刻无法记起来的事情

问题

对于看起来应该是简单的全文搜索和索引排序，这里的100毫秒仍然似乎非常慢。

这里发生了什么？为什么要忽略明显的索引，除非强制它？
我是否达到了从虚拟表和常规表中组合数据的限制？
为什么它仍然相对缓慢，是否有其他方法可以使FTS匹配按另一张表中的字段排序？

谢谢！

- chazomaticus

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- CL. · Accepted Answer

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

这意味着，在查询中访问的每个表只能使用一个索引。

另请参阅文档：查询计划，查询优化器。

您的第一个查询具有以下解释查询计划输出：

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 中的所有条目，在第1步中找到的值中返回一行，当 id 值为其中一个值时。SQLite 在找到第10个这样的行后停止。

在此查询中，可以使用索引进行（隐含的）排序，但这只是因为没有其他索引用于查找这个表中的行。以这种方式使用索引意味着 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 值排序行。

就索引而言，步骤 2 和 3 相互冲突。数据库必须选择使用步骤 2 的索引（在这种情况下必须显式地排序）还是使用步骤 3 的索引（在这种情况下必须遍历所有 MessageTable 条目）。

您可以尝试通过将消息时间作为 FTS 表的一部分并仅搜索最近几天来返回较少的记录（如果结果不足，则增加或减少时间）。