January 30, 2023
By: Kevin'

使用SQLiteFTS5扩展实现全文检索

  1. 使用 LIKE 运算符的性能问题
  2. 使用索引能解决问题吗?
  3. FTS5 全文搜索扩展
  4. 创建 FTS5 虚拟表
  5. 同步数据到 FTS5 虚拟表
  6. 使用 FTS5 虚拟表进行搜索
  7. 结果排序: 相关性排序
  8. 总结
  9. 参考资料

本文将介绍如何使用 SQLite 的 FTS5 (Full-Text Search 5) 扩展, 为您的电影评论应用添加高效的电影标题搜索功能. 如果您正在使用 SQLite 并且需要实现全文搜索, 那么 FTS5 绝对是您的理想选择.

使用 LIKE 运算符的性能问题

假设您已经有了一个电影数据库, 并且想要根据电影标题进行搜索. 最先想到的方法可能是使用 SQL 的 LIKE 运算符. 例如, 您可以使用以下查询来查找标题包含 "Star Wars" 的电影:

SELECT count(*) FROM titles WHERE primary_title LIKE '%Star Wars%';

在上面的查询中, %符号是通配符, 表示匹配任意字符. %Star Wars%表示标题中包含 "Star Wars" 这个短语, 不论其前后是否有其他字符.

但是, 这种使用 LIKE 运算符进行模糊查询的方式, 在数据量大的情况下, 性能会非常糟糕. 让我们通过实际操作来验证一下.

首先, 我们打开 SQLite 命令行工具, 并连接到电影数据库. 然后, 我们执行上述的 LIKE 查询, 并使用 .timer on 命令来记录查询时间:

sqlite> .timer on
sqlite> SELECT count(*) FROM titles WHERE primary_title LIKE '%Star Wars%';

执行结果显示, 查询耗时约 0.8 秒.

count(*)
----------
      4086
Run Time: real 798ms user 785ms sys 12ms

0.8 秒 的查询时间对于人来说可能不算长, 但是对于需要快速响应的应用来说, 这个速度是无法接受的. 我们通常希望应用的响应时间在100 毫秒 以内, 才能给用户带来流畅的体验.
理想情况下, 查询速度应该比 100 毫秒还要快几个数量级.

那么, 为什么 LIKE 查询会如此缓慢呢? 我们可以使用 EXPLAIN QUERY PLAN 命令来查看查询的执行计划:

EXPLAIN QUERY PLAN SELECT count(*) FROM titles WHERE primary_title LIKE '%Star Wars%';

执行计划显示, SQLite 使用了 SCAN TABLE 操作. 这意味着数据库为了找到匹配的记录, 必须 扫描titles表中的每一行数据, 逐行检查 primary_title 列是否包含 "Star Wars". titles 表包含了大约 940 万行数据, 全表扫描的效率非常低下.

QUERY PLAN
`-- SCAN TABLE titles

虽然 SQLite 还提供了 GLOB 和 REGEXP 等其他模糊查询运算符, 但它们的性能特性与 LIKE 类似, 都无法避免全表扫描的问题.

使用索引能解决问题吗?

为了提升查询性能, 我们通常会想到使用索引. 那么, 为 titles 表的 primary_title 列创建索引, 能够解决 LIKE 查询的性能问题吗?

CREATE INDEX idx_primary_title ON titles(primary_title);

虽然创建索引在某些情况下可能会略微改善 LIKE 查询的性能, 但 对于以通配符 % 开头的 LIKE 查询, 索引通常无法发挥作用. 因为数据库索引通常是按照列值的 前缀 进行排序和查找的, 通配符 % 开头会使索引失效, 数据库仍然需要进行全表扫描.

因此, 仅仅添加索引 不足以解决 LIKE 运算符带来的性能问题, 我们需要寻找更专业的全文搜索方案.

FTS5 全文搜索扩展

SQLite 核心功能并没有提供专业的全文搜索功能, 但是SQLite强大的 扩展机制 (Extensions) 为我们提供了解决方案. FTS5 (Full-Text Search 5) 就是 SQLite官方提供的全文搜索扩展.

什么是 SQLite 扩展?

SQLite 的设计理念是保持核心功能的精简, 同时通过扩展机制来增强功能. 扩展是 SQLite 提供的一种机制, 允许开发者向 SQLite 添加新的功能, 例如新的函数, 新的虚拟表等等.

FTS5 扩展的优势

FTS5 扩展通过 虚拟表 (Virtual Table) 的方式, 为 SQLite 提供了高效的全文搜索能力.

什么是虚拟表?

虚拟表是SQLite的一种特殊表类型. 它可以像普通表一样使用SQL语句进行查询, 插入, 更新和删除操作. 但虚拟表的数据存储和检索方式是由扩展本身决定的, 而不是由SQLite核心引擎管理.

FTS5 扩展正是利用虚拟表, 实现了针对全文搜索优化的数据存储和索引结构, 从而可以避免全表扫描, 实现毫秒级 甚至微秒级的搜索响应速度.

如何启用 FTS5 扩展?

幸运的是, FTS5 扩展非常流行和常用, SQLite 团队已经决定 默认将FTS5扩展编译到SQLite核心中. 这意味着, 如果使用的是操作系统自带的SQLite版本, 或者通过包管理器 (如 Brew, npm) 安装的 SQLite, 那么 FTS5 扩展很可能已经包含在您的 SQLite 安装中了.

您可以使用以下命令来检查SQLite版本是否包含FTS5扩展:

PRAGMA compile_options;

如果输出结果中包含 ENABLE_FTS5, 则说明 FTS5 扩展已启用.

compile_options
---------------------------------
... ENABLE_FTS5 ...

创建 FTS5 虚拟表

要使用FTS5扩展进行全文搜索, 我们需要创建一个FTS5 虚拟表. FTS5 虚拟表与源表是独立的, 我们需要将需要进行全文搜索的数据 同步到FTS5 虚拟表中.

以下 SQL 语句创建了一个名为 titles_fts 的 FTS5 虚拟表, 并将 title_id, primary_title, original_title 三列添加到虚拟表中:

CREATE VIRTUAL TABLE titles_fts USING fts5(
    title_id,
    primary_title,
    original_title
);

注意:

  • FTS5 虚拟表 不需要定义主键.
  • FTS5 虚拟表 不需要指定数据类型 (即使指定也会被忽略).

同步数据到 FTS5 虚拟表

创建虚拟表后, 我们需要将 titles 表中的数据同步到 titles_fts 虚拟表中. 可以使用 INSERT INTO ... SELECT FROM ... 语句来完成数据同步:

INSERT INTO titles_fts(title_id, primary_title, original_title)
SELECT title_id, primary_title, original_title FROM titles;

由于 titles 表数据量较大 (约 940 万行), 数据同步过程可能需要一些时间.

保持数据同步: 使用触发器

titles表和titles_fts虚拟表在逻辑上是关联的. 当titles表的数据发生变化(插入, 更新, 删除)时, 我们需要同步更新titles_fts虚拟表, 以保证搜索结果的准确性.

为了实现数据自动同步, 可以使用数据库触发器 (Triggers). 触发器可以在特定的数据库事件(例如 INSERT, UPDATE, DELETE) 发生时自动执行预定义的SQL语句.

以下是一些创建触发器的示例 (伪代码, 实际 SQL 语法会根据具体数据库版本略有差异):

INSERT 触发器: 在向 titles 表插入新数据后, 自动向 titles_fts 虚拟表插入对应数据.

CREATE TRIGGER titles_insert_trigger
AFTER INSERT ON titles
BEGIN
  INSERT INTO titles_fts(title_id, primary_title, original_title)
  VALUES (NEW.title_id, NEW.primary_title, NEW.original_title);
END;

UPDATE 触发器: 在更新 titles 表数据后, 自动更新 titles_fts 虚拟表中的对应数据.

CREATE TRIGGER titles_update_trigger
AFTER UPDATE ON titles
BEGIN
  UPDATE titles_fts
  SET primary_title = NEW.primary_title,
      original_title = NEW.original_title
  WHERE title_id = NEW.title_id;
END;

DELETE 触发器: 在删除 titles 表数据后, 自动删除 titles_fts 虚拟表中的对应数据.

CREATE TRIGGER titles_delete_trigger
AFTER DELETE ON titles
BEGIN
  DELETE FROM titles_fts WHERE title_id = OLD.title_id;
END;

通过使用触发器, 我们可以确保titles表和titles_fts虚拟表的数据始终保持同步, 无需手动维护, 避免了数据不一致的风险.

使用 FTS5 虚拟表进行搜索

数据同步完成后, 我们就可以使用titles_fts虚拟表进行高效的全文搜索了. FTS5 扩展提供了 MATCH 运算符, 用于在虚拟表中进行全文匹配查询.

以下查询语句使用MATCH运算符在 titles_fts虚拟表中搜索包含"Star Wars"的电影, 并统计结果数量:

SELECT count(*)
FROM titles t
JOIN titles_fts s ON t.title_id = s.title_id
WHERE titles_fts MATCH 'Star Wars';

与之前的LIKE查询相比, 使用FTS5扩展的MATCH查询速度提升了两个数量级, 仅耗时 不到10毫秒 (0.01 秒).

count(*)
----------
      4086
Run Time: real 9ms user 8ms sys 0ms

性能对比:

  • LIKE 查询: 0.8 秒
  • FTS5 MATCH 查询: 0.01 秒

MATCH 运算符语法说明:

WHERE titles_fts MATCH '搜索关键词'

  • MATCH 运算符用于 FTS5 虚拟表的查询.
  • '搜索关键词' 是要搜索的文本内容.
  • 默认情况下, MATCH 会在 虚拟表的所有列 中进行全文搜索.

限制搜索列

如果只想在特定的列中进行搜索, 可以在 MATCH 表达式中指定列名. 例如, 只在 primary_title 列中搜索 "Star Wars":

SELECT count(*)
FROM titles t
JOIN titles_fts s ON t.title_id = s.title_id
WHERE titles_fts MATCH 'primary_title:Star Wars';

如果要同时在多个列中搜索, 可以列出多个列名, 例如在 primary_title 和 original_title 列中搜索:

WHERE titles_fts MATCH 'primary_title:Star Wars OR original_title:Star Wars';

或者使用更简洁的语法:

WHERE titles_fts MATCH 'primary_title:Star Wars original_title:Star Wars';

结果排序: 相关性排序

目前为止, 我们的查询只是返回了匹配结果的数量, 或者随机返回了 10 条匹配记录. 在实际应用中, 我们通常希望搜索结果按照 相关性 (Relevance) 进行排序, 将最相关的结果排在前面.

FTS5 扩展提供了两种方法来对搜索结果进行相关性排序:

  1. bm25() 函数: bm25() 是一个内置的 排序函数 (Ranking Function), 用于计算文档与查询关键词之间的相关性得分.
  2. rank 隐藏列: FTS5 虚拟表有一个名为 rank 的 隐藏列 (Hidden Column), 它也存储了相关性得分.

以下查询语句同时返回 bm25() 函数的返回值和 rank 隐藏列的值, 并返回 primary_title 和 original_title 列:

SELECT
    t.primary_title,
    t.original_title,
    bm25(titles_fts),
    rank
FROM titles t
JOIN titles_fts s ON t.title_id = s.title_id
WHERE titles_fts MATCH 'Star Wars'
LIMIT 10;

bm25() 函数和 rank 隐藏列都返回一个数值, 数值越低, 表示相关性越高. 我们可以使用 ORDER BY 子句, 按照 bm25() 或 rank 的值进行升序排序, 从而实现相关性排序.

bm25() 函数的权重参数

默认情况下, bm25() 函数和 rank 隐藏列的排序结果是相同的. 但是, bm25() 函数允许我们传入 权重参数 (Weight Parameters), 为不同的列设置不同的权重, 从而更精细地控制排序结果.

例如, 如果我们希望 primary_title 列的权重高于 original_title 列, 可以使用以下语法:

bm25(titles_fts, 2.0, 1.0)

在上面的例子中, 2.0 是 primary_title 列的权重, 1.0 是 original_title 列的权重. 权重值越大, 该列在相关性排序中起到的作用就越大.

使用 ORDER BY 进行排序

要按照相关性排序结果, 只需要在查询语句中使用 ORDER BY 子句, 并指定排序字段为 bm25(titles_fts) 或 rank 即可. 例如, 使用 bm25() 函数进行排序:

SELECT
    t.primary_title,
    t.original_title
FROM titles t
JOIN titles_fts s ON t.title_id = s.title_id
WHERE titles_fts MATCH 'Star Wars'
ORDER BY bm25(titles_fts)
LIMIT 10;

总结

通过本文, 我们了解了如何使用 SQLite 的 FTS5 扩展, 为电影评论应用添加高效的电影标题搜索功能. FTS5 扩展通过虚拟表和优化的索引结构, 极大地提升了全文搜索的性能, 将查询速度从 秒级 提升到 毫秒级, 甚至 微秒级.

FTS5 扩展的优势总结:

  • 高性能: 提供毫秒级甚至微秒级的全文搜索速度.
  • 易于使用: 通过 CREATE VIRTUAL TABLE 和 MATCH 运算符, 即可轻松实现全文搜索功能.
  • 灵活的排序: 提供 bm25() 函数和 rank 隐藏列, 支持基于相关性的结果排序, 并允许自定义权重.
  • 内置扩展: FTS5 扩展通常已默认包含在 SQLite 安装中, 无需额外安装.

参考资料

想深入了解 FTS5 扩展的更多高级特性, 可以参考 SQLite FTS5 官方文档.

Tags: sqlite sql