Sqlite с использованием таблицы fts5 для выполнения всех запросов вместо основной таблицы

Question

Итак, скажем, у меня есть две таблицы: и где это таблица поиска. Так есть два поля и.

Обычно я бы создал индекс для них обоих. Но поскольку у меня есть, мне нужно создать индекс для name и surnameв ? И есть ли предостережения относительно использования users_fts выполнять все запросы вместо использования основной таблицы users?

Answer 1

SQLite обеспечивает полнотекстовый поиск, и я предполагаю, что это то, что вы используете из имени своей таблицы. Я покажу пример кода с использованием FTS5, но вы можете адаптировать его назад, если вам нужно. Если у вас есть таблица, например:

      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'
);

На этом этапе мы должны убедиться, что записи в таблице проиндексированы для полнотекстового поиска, и это можно сделать с помощью триггеров в таблице, чтобы сделать это автоматически. Триггеры будут выглядеть так:

      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;

Теперь, когда все это готово, вы можете использовать таблицу для полнотекстового поиска.

Итак, как индексы в таблице влияют на таблицу? Если вы выполняете поиск только по таблице, то индексы в таблице не имеют значения. Я не знаю, как вы планируете заполнять таблицу, но если вы используете триггеры в таблице, предлагаемые индексы в таблице все равно не имеют значения. Если вы вручную обновляете таблицу, то ответ таков: индексы в таблице могут повлиять на производительность. Большинство людей, которых я знаю, используют триггерный подход, и это то, что я делаю, что позволяет вам забыть о ручном ведении полнотекстового поиска и получить дополнительное преимущество, заключающееся в том, что вы можете игнорировать индексы в исходной таблице в отношении заполнения полнотекстового поиска. Помните, что это сценарий, когда вы вообще не запрашиваете таблицу - если у вас есть какие-либо запросы к userstable, то вам могут понадобиться вспомогательные индексы.

Вы также спросили, есть ли какие-либо предостережения в отношении подхода к использованию таблицы для ваших запросов - пока вы поддерживаете таблицу в актуальном состоянии, у этого подхода нет недостатков. Если вам нужны функции полнотекстового поиска и ранжирование, это очень удобный подход, встроенный в SQLite. Для этого потребуется больше места для хранения, но вы можете минимизировать это влияние, используя внешнюю таблицу содержимого (я показал это, когда создавал users_ftsТаблица). Вы можете прочитать некоторые подробности об этом в разделе 4.4.2 документации по расширению FTS5 по адресу https://www.sqlite.org/fts5.html.

Этот подход хорошо работает для возможностей полнотекстового поиска, и пока вы поддерживаете индексы, он должен хорошо работать и предоставлять вам больше возможностей для поиска, а также для ранжирования. По моему опыту, большинство полнотекстовых поисков выполняется быстрее, чем то, что вы могли бы сделать с помощью стандартных функций и операторов SQL (таких как LIKEи т. д.) и намного мощнее.