Базы данных и SQLite

Полнотекстовый поиск в SQLite с FTS5

Полнотекстовый поиск в SQLite с FTS5

Когда в приложении накапливаются тысячи записей с текстом — заметки, статьи, описания товаров, логи — обычный LIKE '%слово%' быстро перестаёт устраивать. Он не использует индекс, сканирует таблицу целиком и ничего не знает о словах, словоформах и релевантности. SQLite решает эту задачу штатно: модуль FTS5 (Full-Text Search версии 5) даёт полноценный полнотекстовый индекс прямо внутри одного файла базы, без отдельного поискового сервера вроде Elasticsearch. В этой статье разберём, как создать FTS-таблицу, наполнить её, делать запросы через MATCH, ранжировать результаты функцией bm25(), подсвечивать совпадения через highlight() и snippet(), держать индекс в синхроне с основной таблицей через триггеры и обращаться ко всему этому из C#.

Что такое FTS5 и когда он нужен

FTS5 — это расширение SQLite, реализованное в виде виртуальной таблицы. Внешне она выглядит как обычная таблица со столбцами, но под капотом хранит инвертированный индекс: для каждого слова (токена) запоминается, в каких строках и столбцах оно встречается. Благодаря этому поиск по слову выполняется за время, близкое к логарифмическому, а не линейным сканированием.

FTS5 входит в состав официальных сборок SQLite (включая ту, что поставляется с Microsoft.Data.Sqlite и System.Data.SQLite), поэтому отдельно ничего ставить обычно не нужно. Проверить наличие модуля можно запросом:

SELECT sqlite_compileoption_used('ENABLE_FTS5');
-- вернёт 1, если FTS5 доступен

Использовать FTS5 стоит, когда нужен поиск по естественному тексту: по словам и фразам, с учётом релевантности, с подсветкой найденного. Если же вам нужны только точные совпадения по коротким полям (email, артикул), обычного индекса B-tree достаточно.

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

Базовый синтаксис — CREATE VIRTUAL TABLE ... USING fts5. Перечисляем столбцы, по которым хотим искать. Все столбцы FTS5 текстовые; типы данных не указываются.

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    body,
    tokenize = 'unicode61 remove_diacritics 2'
);

Параметр tokenize задаёт токенизатор — то, как текст режется на слова. По умолчанию используется unicode61, который корректно разбивает Unicode-текст (в том числе кириллицу) по границам слов и приводит буквы к нижнему регистру. Опция remove_diacritics 2 убирает диакритику, чтобы «кафе» и «кафе́» считались одним словом. Для многих языков базового unicode61 достаточно; для стемминга (поиска по основам слов) понадобятся внешние токенизаторы, но это уже за рамками штатной поставки.

Наполняется FTS-таблица обычным INSERT:

INSERT INTO articles_fts (title, body) VALUES
  ('Введение в SQLite', 'SQLite — встраиваемая база данных в одном файле.'),
  ('Полнотекстовый поиск', 'Модуль FTS5 строит инвертированный индекс по тексту.');

Внешняя таблица контента

Часто текст уже лежит в обычной таблице, и дублировать его в FTS не хочется. Для этого есть режим external content: FTS5 хранит только индекс, а сами строки берёт из «настоящей» таблицы по rowid.

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

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    body,
    content = 'articles',     -- источник данных
    content_rowid = 'id'      -- связь по столбцу id
);

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

Запросы через MATCH

Поиск выполняется оператором MATCH (или эквивалентным синтаксисом с =). Слева — имя FTS-таблицы или конкретного столбца, справа — поисковый запрос.

-- поиск по всем индексированным столбцам
SELECT rowid, title FROM articles_fts
WHERE articles_fts MATCH 'индекс';

-- поиск только по столбцу title
SELECT rowid, title FROM articles_fts
WHERE title MATCH 'поиск';

Язык запросов FTS5 богаче, чем просто слово:

  • sqlite база — обе лексемы (неявное И);
  • sqlite OR mysql — любая из них;
  • "полнотекстовый поиск" — точная фраза (слова подряд);
  • поис* — префиксный поиск (поиск, поисковый, поисковик);
  • база NOT данных — есть «база», но нет «данных»;
  • title : sqlite — ограничение по столбцу прямо в запросе;
  • NEAR(sqlite индекс, 5) — слова на расстоянии не более 5 токенов.

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

Ранжирование через bm25()

По умолчанию MATCH возвращает строки в порядке релевантности (внутренний rank). Чтобы управлять сортировкой явно и получать числовую оценку, используется функция bm25() — реализация известного алгоритма ранжирования Okapi BM25. Она учитывает частоту слова в документе и его редкость по всей коллекции.

Особенность: bm25() возвращает отрицательное число, и чем оно меньше (более отрицательное), тем релевантнее документ. Поэтому сортируем по возрастанию:

SELECT
    rowid,
    title,
    bm25(articles_fts) AS rank
FROM articles_fts
WHERE articles_fts MATCH 'sqlite индекс'
ORDER BY rank          -- лучшие совпадения сверху
LIMIT 20;

Функции bm25() можно передать веса столбцов — так заголовок будет важнее тела статьи:

-- title весит в 10 раз больше, чем body
SELECT rowid, title, bm25(articles_fts, 10.0, 1.0) AS rank
FROM articles_fts
WHERE articles_fts MATCH 'поиск'
ORDER BY rank;

Есть и удобный псевдостолбец rank: ORDER BY rank внутри FTS-таблицы делает то же, что сортировка по bm25() с весами по умолчанию.

Подсветка результатов: highlight() и snippet()

Чтобы показать пользователю, где именно нашлось совпадение, FTS5 даёт две функции. highlight() возвращает весь текст столбца, оборачивая найденные слова заданными маркерами:

-- highlight(таблица, индекс_столбца, открывающий, закрывающий)
SELECT highlight(articles_fts, 1, '<b>', '</b>') AS marked
FROM articles_fts
WHERE articles_fts MATCH 'индекс';
-- ... строит <b>инвертированный</b> <b>индекс</b> по тексту.

Здесь индекс столбца — порядковый номер, начиная с 0 (0 = title, 1 = body).

snippet() делает то же, но возвращает короткий фрагмент вокруг совпадения, а не весь текст — то, что обычно показывают в выдаче поиска:

-- snippet(таблица, столбец, откр, закр, многоточие, макс_токенов)
SELECT snippet(articles_fts, 1, '[', ']', '…', 10) AS preview
FROM articles_fts
WHERE articles_fts MATCH 'инвертированный'
ORDER BY rank;

Последний аргумент ограничивает длину фрагмента в токенах (до 64). Если найденный текст длиннее, по краям подставляется заданное многоточие.

Синхронизация с основной таблицей через триггеры

В режиме external content FTS5 не обновляется сам при изменении основной таблицы — это нужно делать вручную. Стандартный приём — повесить триггеры AFTER INSERT, AFTER DELETE и AFTER UPDATE. Удаление из FTS5 в этом режиме выполняется специальной командой: вставкой служебной строки со столбцом articles_fts равным 'delete' и тем же rowid и значениями, что были у удаляемой записи.

-- при вставке: добавить запись в индекс
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;

Если индекс создаётся для уже заполненной таблицы, его нужно один раз построить целиком. Для external content это делается так:

INSERT INTO articles_fts (articles_fts) VALUES ('rebuild');

Команда 'rebuild' отбрасывает текущий индекс и перестраивает его из таблицы-источника. Также полезна команда оптимизации, объединяющая внутренние сегменты индекса для более быстрого поиска:

INSERT INTO articles_fts (articles_fts) VALUES ('optimize');

Доступ из C#

Из .NET с FTS5 удобно работать через пакет Microsoft.Data.Sqlite — он использует нативный движок SQLite со встроенным FTS5, дополнительных нативных зависимостей не нужно. Запрос MATCH ничем не отличается от обычного: параметр передаётся как значение, а не вклеивается в строку, что закрывает SQL-инъекции.

using Microsoft.Data.Sqlite;

string userQuery = txtSearch.Text;          // ввод пользователя
// экранируем: оборачиваем в кавычки, удваивая внутренние, и даём префиксный поиск
string ftsQuery = "\"" + userQuery.Replace("\"", "\"\"") + "\" *";

using var conn = new SqliteConnection("Data Source=app.db");
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = @"
    SELECT a.id,
           a.title,
           snippet(articles_fts, 1, '[', ']', '…', 12) AS preview,
           bm25(articles_fts, 10.0, 1.0) AS rank
    FROM articles_fts
    JOIN articles a ON a.id = articles_fts.rowid
    WHERE articles_fts MATCH $q
    ORDER BY rank
    LIMIT 50;";
cmd.Parameters.AddWithValue("$q", ftsQuery);

using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    int    id      = reader.GetInt32(0);
    string title   = reader.GetString(1);
    string preview = reader.GetString(2);
    double rank    = reader.GetDouble(3);
    Console.WriteLine($"{rank:F2}  {title}  ->  {preview}");
}

Несколько практических замечаний для .NET-приложений:

  • Соединение к одному файлу базы лучше переиспользовать или применять пул — открытие нового подключения на каждый запрос недёшево.
  • Для интерактивного поиска «по мере набора» добавляйте задержку (debounce) и отменяйте предыдущий запрос через CancellationToken, иначе на каждый символ полетит обращение к БД.
  • Параметризуйте именно значение для MATCH; имя таблицы и столбцов параметром передать нельзя.
  • Если данные правят несколько потоков, оберните запись и пересборку индекса в транзакцию, чтобы триггеры отработали атомарно.

Итого

  • FTS5 — встроенный в SQLite полнотекстовый поиск; создаётся через CREATE VIRTUAL TABLE ... USING fts5 и не требует внешнего сервера.
  • Токенизатор unicode61 корректно работает с кириллицей; remove_diacritics и префиксный поиск слово* расширяют возможности.
  • Поиск идёт через MATCH с поддержкой И/ИЛИ/НЕ, фраз, ограничения по столбцу и оператора NEAR.
  • Ранжирование даёт bm25(): значения отрицательные, сортируем ORDER BY rank, веса столбцов задаются аргументами.
  • highlight() и snippet() подсвечивают совпадения и формируют короткие превью для выдачи.
  • Для режима external content синхронизацию обеспечивают триггеры AFTER INSERT/UPDATE/DELETE, а первичное построение — команда 'rebuild'.
  • Из C# (Microsoft.Data.Sqlite) запрос MATCH выполняется как обычный параметризованный SQL — не забудьте экранировать пользовательский ввод.

Читайте также — Базы данных и SQLite