Исполняемые доки SQLite: Полнотекстовый поиск

Q: Чем MATCH отличается от LIKE в SQLite?

LIKE — это линейный поиск подстроки, который не знает ничего про границы слов. MATCH работает по инвертированному индексу FTS5: он быстрый даже на больших таблицах и понимает токены, префиксные запросы (term*), логические операторы (AND, OR, NOT) и фразовый поиск ("точная фраза"). Важный момент: MATCH доступен только на FTS-таблицах.

Q: Как ранжировать результаты полнотекстового поиска?

У FTS5 есть скрытая колонка rank со значением BM25 (чем меньше — тем релевантнее). Достаточно написать ORDER BY rank. Можно вызвать bm25(table) явно или передать веса колонок — например, bm25(posts, 10.0, 1.0), чтобы заголовок весил больше тела статьи.

`LIKE` не масштабируется

Если вы уже искали текст в SQLite, скорее всего, вы хватались за LIKE '%слово%'. На небольших таблицах это работает, на больших — рассыпается. Никакой индекс тут не поможет: SQLite приходится пройтись по каждой строке, привести её к нижнему регистру и проверить вхождение подстроки. А границы слов, ранжирование, поиск по нескольким словам и префиксный поиск — всё это придётся писать руками.

Полнотекстовый поиск в SQLite уже встроен — за него отвечает FTS5. Это специальный тип виртуальной таблицы, который ведёт инвертированный индекс по вашим текстовым колонкам, понимает свой небольшой язык запросов и ранжирует результаты по BM25. Идёт в комплекте со SQLite — никаких расширений ставить не нужно.

Создание виртуальной таблицы FTS5

Виртуальная таблица FTS5 создаётся через CREATE VIRTUAL TABLE ... USING fts5(...), где перечисляются текстовые колонки, которые надо проиндексировать:

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('Полнотекстовый поиск в SQLite', 'FTS5 быстрый и встроенный.'),
  ('Индексы в SQLite', 'B-деревья обеспечивают большинство поисков в SQLite.'),
  ('Зачем использовать SQLite', 'Работает внутри процесса и хранит данные в одном файле.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Здесь стоит обратить внимание на три момента. У столбцов нет типов — FTS5 любое значение считает текстом. Оператор MATCH применяется к самой таблице (posts MATCH ...), а не к конкретному столбцу. И сам запрос работает без учёта регистра и проходит через токенизатор, поэтому 'sqlite' найдёт SQLite в любой из строк.

Язык запросов оператора MATCH

MATCH принимает не только одиночные слова. У строки запроса есть собственный небольшой синтаксис:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Полнотекстовый поиск', 'FTS5 поддерживает фразовые и префиксные запросы.'),
  ('Индексы', 'B-tree индекс ускоряет поиск по равенству.'),
  ('Триггеры', 'Используйте триггеры для синхронизации FTS с исходной таблицей.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

Что делает каждый запрос:

'fts5 AND prefix' — оба слова должны встречаться (в любом порядке, в любом месте строки).
'"keep fts"' — точная фраза, именно в таком порядке.
'trig*' — префиксный поиск, подходит для trigger, triggers, trigonometry...
'index NOT trigger' — есть index, но нет trigger.

Можно искать и по конкретной колонке через column:term, например 'title:sqlite'. Полный синтаксис поддерживает скобки для группировки и OR для альтернатив — всё как в обычной поисковой системе.

Ранжирование bm25 в sqlite

По умолчанию FTS5 добавляет к каждой строке скрытую колонку rank. Это релевантность по алгоритму BM25 — чем меньше число, тем лучше совпадение. Сортируем по ней и получаем самые релевантные результаты сверху:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Руководство по SQLite', 'Изучите SQLite с нуля.'),
  ('SQLite против Postgres', 'Сравнение SQLite и Postgres для небольших приложений.'),
  ('SQLite SQLite SQLite', 'Пост, в котором SQLite упоминается много раз.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

Если хочется, чтобы одни колонки влияли на ранжирование сильнее других, передайте в bm25() весовые коэффициенты — по одному на каждую колонку в порядке их объявления:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Индексы в SQLite', 'Краткая заметка о B-деревьях.'),
  ('Заметки про Postgres', 'Содержит абзац про sqlite для сравнения.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

Первый пост побеждает, потому что sqlite встретился в title (вес 10×), а не только в body (вес 1×). Подбирайте веса под то, как ваше приложение реально хочет ранжировать результаты.

Синхронизация индекса с основной таблицей

Самый простой вариант FTS5-таблицы хранит копию текста у себя. Для логов, куда вы только вставляете записи, это нормально, но в большинстве приложений уже есть основная таблица, и хочется, чтобы FTS просто следил за ней. Чистое решение — таблица FTS с external content и три триггера.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body,
    content='articles',
    content_rowid='id'
);

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles (title, body) VALUES
  ('Триггеры SQLite', 'Как использовать триггеры AFTER INSERT.'),
  ('Шаблоны FTS5', 'Внешний контент хранит данные в одном месте.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' говорит FTS5 не хранить сам текст — он будет подтягиваться из таблицы articles по мере необходимости. Триггеры зеркалят все изменения в полнотекстовый индекс. Так таблица articles становится источником истины, а articles_fts — лишь поисковой надстройкой рядом с ней.

Странная на вид конструкция INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) — это специальный командный синтаксис FTS5, через который индексу сообщают: удали такую-то строку.

Сниппеты и подсветка совпадений

В выдаче поиска обычно хочется показать превью с выделенными словами запроса. Для этого в FTS5 есть две функции — highlight и snippet:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Начало работы', 'SQLite — это небольшой, быстрый, встраиваемый движок базы данных, который хранит данные в одном файле на диске.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

highlight(table, column_index, open, close) возвращает полный текст колонки, в котором найденные токены обёрнуты заданными тегами.
snippet(table, column_index, open, close, ellipsis, token_count) возвращает короткий фрагмент текста вокруг совпадения.

Индексы колонок начинаются с нуля и идут в порядке объявления. Это базовые кирпичики для подсветки совпадений жёлтым — той самой, которая нужна в любом поисковом интерфейсе.

Подводные камни, о которых стоит знать

Вот несколько моментов, на которых обычно спотыкаются:

MATCH работает только с FTS-таблицами. К обычной колонке MATCH применить нельзя. Если нужен полнотекстовый поиск sqlite по уже существующей таблице — используйте паттерн external content из примера выше.
Не забывайте про ORDER BY rank. Без него FTS5 вернёт строки в порядке хранения, а это вообще не имеет отношения к релевантности.
Токенизатор имеет значение. Токенизатор по умолчанию (unicode61) разбивает текст по границам слов Unicode и приводит к нижнему регистру. Если нужен стемминг (чтобы run совпадал с running) — берите токенизатор porter: USING fts5(body, tokenize='porter').
FTS5 не прощает опечаток. Он умеет префиксный поиск, но не нечёткий. Если нужна логика «возможно, вы имели в виду...» — это уже слой поверх FTS5.
Contentless-таблицы (content='') компактнее, но без оригинала. Искать по ним можно, а вот получить исходный текст — нет, только rowid. Удобно, когда сам текст вы храните где-то ещё.

Дальше: оконные функции

С полнотекстовым поиском разобрались. На следующей странице — другой класс продвинутых запросов: оконные функции. С их помощью считают накопительные суммы, ранжирование и аналитику по группам без того, чтобы схлопывать строки в агрегаты.

Часто задаваемые вопросы

Что такое FTS5 в SQLite?

FTS5 — это встроенное расширение SQLite для полнотекстового поиска. Вы создаёте специальную виртуальную таблицу через CREATE VIRTUAL TABLE ... USING fts5(...) и обращаетесь к ней через оператор MATCH. При вставке текст разбивается на токены, складывается в инвертированный индекс, а результаты по умолчанию ранжируются по BM25.

Чем MATCH отличается от LIKE в SQLite?

LIKE — это линейный поиск подстроки, который не знает ничего про границы слов. MATCH работает по инвертированному индексу FTS5: он быстрый даже на больших таблицах и понимает токены, префиксные запросы (term*), логические операторы (AND, OR, NOT) и фразовый поиск ("точная фраза"). Важный момент: MATCH доступен только на FTS-таблицах.

Как держать FTS5-индекс в синхроне с основной таблицей?

Есть два подхода. Первый — использовать contentless или external-content FTS5-таблицу, которая ссылается на исходную таблицу. Второй — навесить триггеры AFTER INSERT, AFTER UPDATE и AFTER DELETE, которые зеркалят изменения в FTS-таблицу. Вариант с external-content (content='posts') удобен тем, что текст не дублируется на диске.

Как ранжировать результаты полнотекстового поиска?

У FTS5 есть скрытая колонка rank со значением BM25 (чем меньше — тем релевантнее). Достаточно написать ORDER BY rank. Можно вызвать bm25(table) явно или передать веса колонок — например, bm25(posts, 10.0, 1.0), чтобы заголовок весил больше тела статьи.

Связанные концепции

Полнотекстовый поиск в SQLite: FTS5 и оператор MATCH