Четыре урока позади. Вы умеете читать и изменять данные, строить JOIN, агрегировать результаты, защищать базу индексами и транзакциями. Это уже рабочий инструментарий.
Но в реальных проектах появляются два сценария, где этого не хватает.
Сценарий первый: поиск по тексту. LIKE '%машинное обучение%' на таблице из 500 000 статей — это полный перебор всех строк, без индекса. На большом объёме такой запрос будет выполняться секунды вместо миллисекунд.
Сценарий второй: сложный запрос, который нужен в пяти местах кода. Через месяц никто — включая автора — не понимает, что там написано. При изменении логики правишь в пяти местах и молишься, что не пропустил ни одного.
Три инструмента сегодня: FTS5 закрывает первую проблему, Views и CTE — вторую.
Обычный LIKE '%слово%' с ведущим знаком % не использует индекс — БД проверяет каждую строку последовательно. Сложность — O(n). На 500 строках это незаметно, на 500 000 — критично.
FTS5 (Full-Text Search, версия 5) работает иначе. При добавлении данных SQLite строит инвертированный индекс: словарь, где каждое слово указывает на список строк, в которых встречается.
"машинное" → [строка 1, строка 4, строка 12] "обучение" → [строка 1, строка 7, строка 12]
Поиск идёт по словарю, а не по данным. Сложность — O(log n) вместо O(n).
Предположим, есть основная таблица статей:
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;
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;
В продакшне нужны все три триггера — иначе индекс расходится с данными и поиск возвращает устаревшие результаты.
Где это используется:
Допустим, в трёх разных местах кода нужен один и тот же запрос — с JOIN, CASE WHEN и фильтрацией. Продублировать его несложно. Сложнее — потом поддерживать. Когда бизнес-логика меняется, правите в трёх местах и рискуете пропустить одно.
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 SQLite выполняет тот же SQL заново. Это сохранённый SELECT, не кеш.
Если View включает тяжёлый JOIN, запрос будет выполняться тяжело — с View или без него. Для ускорения нужны индексы на базовых таблицах, а не пересоздание View.
PostgreSQL поддерживает материализованные представления — они кешируют результат и обновляются по команде. В SQLite такой возможности нет.
Когда использовать View: переиспользование сложной логики, повышение читаемости кода, единая точка изменений. Не для оптимизации производительности.
Сложный запрос с вложенными подзапросами читается изнутри наружу — мозгу приходится работать против естественного порядка чтения. Вот типичный пример:
SELECT title, category FROM ( SELECT id, title, category FROM ( SELECT * FROM articles WHERE category != 'черновик' ) filtered WHERE LENGTH(body) > 500 ) long_articles ORDER BY title;
Чтобы понять что происходит, нужно начать с самого глубокого подзапроса и разматывать наружу. Три уровня вложенности — ещё терпимо. Пять — уже сложно.
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 дважды.
1. Читаемость. Каждый шаг назван. Читая FROM long_articles, сразу понятно что это за данные — без погружения в подзапрос.
2. Отладка. Когда запрос не возвращает то, что ожидалось, можно заменить финальный SELECT на SELECT * FROM filtered — и посмотреть промежуточный результат. С вложенными подзапросами так не сделать.
3. Переиспользование. Один CTE можно упомянуть в финальном SELECT несколько раз — не нужно дублировать одинаковый подзапрос.
| Задача | Инструмент | Почему |
|---|---|---|
| Поиск по началу строки | Индекс + LIKE 'запрос%' | Индекс работает без ведущего % |
| Поиск по вхождению, мало данных | LIKE '%запрос%' | Просто, на малых данных достаточно |
| Полнотекстовый поиск, большой объём | FTS5 + MATCH | Инвертированный индекс, ранжирование bm25 |
| Переиспользование сложного запроса | View | Одно место для изменений |
| Разбить сложный запрос на шаги | CTE (WITH) | Читаемость, удобство отладки |
Проверка понимания материала
Доступно после входаПройдите квиз и сразу закрепите тему, когда откроете интерактивный режим урока.
Пять уроков — пять слоёв:
SELECT, WHERE, ORDER BY — чтение данныхCREATE TABLE, INSERT/UPDATE/DELETE, UPSERT, ORM — изменение данныхJOIN, агрегаты, GROUP BY, подзапросы — связи между таблицамиТеперь вы умеете не только правильно хранить и читать данные, но и строить поиск, который работает на больших объёмах, и писать запросы, которые можно сопровождать через год.
Что изучать дальше: оконные функции — агрегация без GROUP BY, позволяет делать скользящие суммы и ранжирование в одном запросе. EXPLAIN QUERY PLAN — читать план запроса и понимать, где теряется производительность. PostgreSQL-специфика: tsvector для полнотекстового поиска и материализованные представления.