时间戳

首页 > 解决方案 > Sqlite 使用 fts5 表代替主表来执行所有查询

sqlite - Sqlite 使用 fts5 表代替主表来执行所有查询

问题描述

所以假设我有两个表：users搜索表users_fts在哪里。users_fts所以users有两个字段name和surname。

通常我会为这两个 em' 创建索引。但是既然我有users_fts我应该需要为name和创建surname索引users吗？是否有任何使用users_fts来执行所有查询而不是使用主表的警告users？

标签： sqlitedatatableandroid-sqliteios-sqlite

解决方案

SQLite 提供全文搜索，我假设这就是您从表名中使用的内容。我将使用 FTS5 显示示例代码，但如果需要，您可以向后调整它。如果你有一张users类似的桌子：

CREATE TABLE users(
    id INTEGER PRIMARY KEY, 
    name TEXT NOT NULL, 
    surname TEXT NOT NULL
);

然后您使用以下内容制作了全文搜索表：

CREATE VIRTUAL TABLE users_fts USING fts5(
    name, 
    surname, 
    content='user', 
    content_rowid='id'
);

在这一点上，我们必须确保users表中的记录被索引以进行全文搜索，这可以使用users表上的触发器自动完成。触发器看起来像：

CREATE TRIGGER users_ai AFTER INSERT ON users
    BEGIN
        INSERT INTO users_fts (rowid, name, surname)
        VALUES (new.id, new.name, new.surname);
    END;

CREATE TRIGGER users_ad AFTER DELETE ON users
    BEGIN
        INSERT INTO users_fts (users_fts, rowid, name, surname)
        VALUES ('delete', old.id, old.name, old.surname);
    END;

CREATE TRIGGER users_au AFTER UPDATE ON users
    BEGIN
        INSERT INTO users_fts (users_fts, rowid, name, surname)
        VALUES ('delete', old.id, old.name, old.surname);
        INSERT INTO users_fts (rowid, name, surname)
        VALUES (new.id, new.name, new.surname);
    END;

完成所有这些后，您现在可以使用该users_fts表执行全文搜索。

那么表上的索引是如何users影响users_fts表的呢？如果您只使用users_fts表进行搜索，则表上的索引users无关紧要。我不知道您打算如何填充users_fts表，但如果您在表上使用触发器，那么users表上的建议索引users仍然无关紧要。如果您手动保持users_fts表是最新的，那么答案是users表上的索引可能影响性能。我认识的大多数人都使用触发器方法，这就是我所做的，它让您忘记手动维护全文搜索，并且您可以获得额外的好处，您可以在填充全文搜索时忽略源表上的索引。请记住，尽管这是您根本不查询表的场景users- 如果您对表有任何查询users，那么您可能需要支持索引。

您还询问了使用该表进行查询的方法是否有任何警告users_fts- 只要您保持users_fts表是最新的，那么这种方法就没有缺点。如果您需要全文搜索功能和排名，这是一种融入 SQLite 的非常方便的方法。这将需要更多的存储空间，但您可以通过使用外部内容表（我在创建users_fts表时展示过这一点）来最大限度地减少这种影响。您可以在https://www.sqlite.org/fts5.html的 FTS5 扩展文档的第 4.4.2 节中阅读有关它的一些详细信息

这种方法适用于全文搜索功能，只要您维护索引，它应该可以很好地工作并为您提供更多搜索和排名功能。根据我的经验，大多数全文搜索都比使用标准 SQL 函数和运算符（例如LIKE等）更快，而且功能更强大。

推荐阅读