背景
我正在实现对存储在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匹配按另一张表中的字段排序?
谢谢!
UNION
操作。 - chazomaticusEXPLAIN QUERY PLAN
;我一直在使用更难理解的EXPLAIN SELECT...
。无论如何,感谢你的回答!我想再等几天再接受它,看看是否有其他人能在这里提供任何见解。 - chazomaticus