SQL-5: Полнотекстовый поиск, Views и CTE

Четыре урока позади. Вы умеете читать и изменять данные, строить JOIN, агрегировать результаты, защищать базу индексами и транзакциями. Это уже рабочий инструментарий.

Но в реальных проектах появляются два сценария, где этого не хватает.

Сценарий первый: поиск по тексту. LIKE '%машинное обучение%' на таблице из 500 000 статей — это полный перебор всех строк, без индекса. На большом объёме такой запрос будет выполняться секунды вместо миллисекунд.

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

Три инструмента сегодня: FTS5 закрывает первую проблему, Views и CTE — вторую.

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

Почему LIKE не подходит для текста

Обычный LIKE '%слово%' с ведущим знаком % не использует индекс — БД проверяет каждую строку последовательно. Сложность — O(n). На 500 строках это незаметно, на 500 000 — критично.

FTS5 (Full-Text Search, версия 5) работает иначе. При добавлении данных SQLite строит инвертированный индекс: словарь, где каждое слово указывает на список строк, в которых встречается.

"машинное" → [строка 1, строка 4, строка 12]
"обучение" → [строка 1, строка 7, строка 12]

Поиск идёт по словарю, а не по данным. Сложность — O(log n) вместо O(n).

Создание FTS-таблицы

Предположим, есть основная таблица статей:

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

FTS-таблица создаётся как виртуальная:

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

Что здесь что:

• VIRTUAL TABLE — не хранит строки, это индексная структура поверх данных.
• content='articles' — сами данные остаются в основной таблице, FTS хранит только индекс. Экономия места.
• content_rowid='id' — связь с основной таблицей по первичному ключу.

Наполнение индекса

После создания таблицы FTS-индекс пустой. Нужно его заполнить из основной таблицы:

INSERT INTO articles_fts(rowid, title, body)
SELECT id, title, body FROM articles;

Поиск через MATCH

SELECT a.id, a.title
FROM articles_fts fts
JOIN articles a ON a.id = fts.rowid
WHERE articles_fts MATCH 'машинное обучение';

MATCH ищет строки, где оба слова встречаются в проиндексированных колонках. JOIN с основной таблицей нужен, чтобы получить полные данные статьи — FTS хранит только индекс.

Ранжирование по релевантности

Результатов может быть много. Чтобы самые релевантные шли первыми, используют функцию bm25():

SELECT a.id, a.title, bm25(articles_fts) AS relevance
FROM articles_fts fts
JOIN articles a ON a.id = fts.rowid
WHERE articles_fts MATCH 'машинное обучение'
ORDER BY relevance ASC;

Важная деталь: bm25() возвращает отрицательные числа — это соглашение SQLite. Значение -5.2 означает более высокую релевантность, чем -0.3. Поэтому сортировка ASC (по возрастанию) ставит самые релевантные результаты первыми.

Синхронизация при изменении данных

FTS-индекс не обновляется автоматически при INSERT/UPDATE/DELETE в основной таблице. Для синхронизации нужны триггеры:

-- При добавлении новой статьи
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_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;

-- При удалении статьи
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;

В продакшне нужны все три триггера — иначе индекс расходится с данными и поиск возвращает устаревшие результаты.

Где это используется:

• Android: поиск по заметкам, рецептам, контактам в приложении
• Backend: поиск по статьям, документации, каталогу товаров
• AI/RAG: поиск релевантных документов перед отправкой контекста в языковую модель

Views — представления

Проблема повторяющихся запросов

Допустим, в трёх разных местах кода нужен один и тот же запрос — с JOIN, CASE WHEN и фильтрацией. Продублировать его несложно. Сложнее — потом поддерживать. Когда бизнес-логика меняется, правите в трёх местах и рискуете пропустить одно.

View решает это: один запрос в одном месте. Везде остальном — просто имя.

Создание View

CREATE VIEW article_summary AS
SELECT
    a.id,
    a.title,
    a.category,
    CASE
        WHEN LENGTH(a.body) > 1000 THEN 'длинная'
        ELSE 'короткая'
    END AS length_type
FROM articles a;

Использовать View можно как обычную таблицу:

SELECT * FROM article_summary WHERE category = 'AI';
SELECT title, length_type FROM article_summary WHERE length_type = 'длинная';

Что View не делает

View не ускоряет запрос. Каждый раз при обращении к View SQLite выполняет тот же SQL заново. Это сохранённый SELECT, не кеш.

Если View включает тяжёлый JOIN, запрос будет выполняться тяжело — с View или без него. Для ускорения нужны индексы на базовых таблицах, а не пересоздание View.

PostgreSQL поддерживает материализованные представления — они кешируют результат и обновляются по команде. В SQLite такой возможности нет.

Когда использовать View: переиспользование сложной логики, повышение читаемости кода, единая точка изменений. Не для оптимизации производительности.

CTE — общие табличные выражения

Проблема вложенных подзапросов

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

SELECT title, category
FROM (
    SELECT id, title, category
    FROM (
        SELECT * FROM articles WHERE category != 'черновик'
    ) filtered
    WHERE LENGTH(body) > 500
) long_articles
ORDER BY title;

Чтобы понять что происходит, нужно начать с самого глубокого подзапроса и разматывать наружу. Три уровня вложенности — ещё терпимо. Пять — уже сложно.

CTE разворачивает логику сверху вниз

WITH filtered AS (
    SELECT * FROM articles
    WHERE category != 'черновик'
),
long_articles AS (
    SELECT id, title, category
    FROM filtered
    WHERE LENGTH(body) > 500
)
SELECT title, category
FROM long_articles
ORDER BY title;

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

Синтаксис и возможности

WITH cte1 AS (
    SELECT … FROM … WHERE …
),
cte2 AS (
    SELECT … FROM cte1 WHERE …
),
cte3 AS (
    SELECT … GROUP BY …
)
SELECT … FROM cte2 JOIN cte3 ON cte2.id = cte3.id;

Несколько CTE перечисляются через запятую после WITH. Каждый следующий может ссылаться на предыдущие. В финальном SELECT можно JOIN-ить несколько CTE между собой или использовать один CTE дважды.

Три преимущества CTE перед подзапросом

1. Читаемость. Каждый шаг назван. Читая FROM long_articles, сразу понятно что это за данные — без погружения в подзапрос.

2. Отладка. Когда запрос не возвращает то, что ожидалось, можно заменить финальный SELECT на SELECT * FROM filtered — и посмотреть промежуточный результат. С вложенными подзапросами так не сделать.

3. Переиспользование. Один CTE можно упомянуть в финальном SELECT несколько раз — не нужно дублировать одинаковый подзапрос.

Когда какой инструмент

Итого

Пять уроков — пять слоёв:

• SQL-1: SELECT, WHERE, ORDER BY — чтение данных
• SQL-2: CREATE TABLE, INSERT/UPDATE/DELETE, UPSERT, ORM — изменение данных
• SQL-3: JOIN, агрегаты, GROUP BY, подзапросы — связи между таблицами
• SQL-4: индексы, транзакции, защита от инъекций, миграции — надёжность
• SQL-5: FTS5, Views, CTE — продвинутые инструменты

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

Что изучать дальше: оконные функции — агрегация без GROUP BY, позволяет делать скользящие суммы и ранжирование в одном запросе. EXPLAIN QUERY PLAN — читать план запроса и понимать, где теряется производительность. PostgreSQL-специфика: tsvector для полнотекстового поиска и материализованные представления.