Ответ.

История вопроса:
Первоначально SQL использовался для работы преимущественно со структурированными данными, где поиск в текстовых полях был ограничен простыми опреациями вроде LIKE. С ростом объёмов текстовой информации возникла необходимость быстро и гибко осуществлять поиски по большим текстам: по статьям, сообщениям, блогам и т.д.

Проблема:
Стандартные инструменты SQL (LIKE/ILIKE) плохо работают с большими объёмами текста и не умеют эффективно находить слова по релевантности, с учётом морфологии или расстояния между словами. Это может привести к потере производительности и слишком долгим откликам при поиске.

Решение:
Для таких задач используются механизмы полнотекстового поиска (Full-Text Search, FTS), встроенные в СУБД, например Full-Text Index и специальные операторы (CONTAINS, MATCH AGAINST, tsvector, tsquery). Такие индексы строят «карточку слов» («инвертированный индекс»), ускоряя поиск по текстам в десятки раз.

Пример кода (SQL Server):

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Documents(Content) KEY INDEX PK_Documents;

SELECT * FROM Documents WHERE CONTAINS(Content, '"SQL programming"');

Ключевые особенности:

• Функционирует на базе специальных полнотекстовых индексов, отделённых от обычных.
• Поддерживает запросы с релевантностью, лемматизацией, признанием стоп-слов и сложных условий (НЕ, ИЛИ, близость).
• Требует поддержания индекса при массовых изменениях данных — периодическая переиндексация.

Вопросы с подвохом.

Чем отличается поиск с помощью LIKE от полнотекстового поиска?

LIKE — простая операция сравнения с шаблоном, не используящая индексы по тексту, медленна для больших объёмов. Full-text использует специальный индекс и может учитывать морфологию, релевантность.

Пример:

SELECT * FROM articles WHERE body LIKE '%database%';
-- медленно, нет ранжирования

SELECT * FROM articles WHERE MATCH(body) AGAINST ('database');
-- быстро, с ранжированием

Что происходит с полнотекстовым индексом при массовых вставках или удалениях?

После массовых изменений полей текста индекс становится устаревшим (иногда — автообновление, иногда — вручную), и необходимо провести пересборку индекса для восстановления производительности.

-- Для MSSQL
ALTER FULLTEXT INDEX ON Documents START FULL POPULATION;

Можно ли использовать полнотекстовые индексы для поиска в столбцах типа JSON или XML?

Нет, прямой поддержки большинства полнотекстовых движков для JSON/XML-структур нет; такие данные нужно или вытаскивать в строковое поле, или применять специальные парсеры/внешние инструменты (например, Elasticsearch).

Типовые ошибки и анти-паттерны

• Использование оператора LIKE '%word%' на больших таблицах — катастрофическая производительность
• Переиндексация не выполняется, поиск становится нерелевантным
• Не учитываются особенности языков и стоп-слов
• Индексируем сразу несколько гигабайт данных без дополнительных ресурсов

Пример из жизни

Негативный кейс

Компания хранила десятки миллионов записей статей. Для поиска использовался LIKE '%слово%'. ИТ-отдел жаловался на регулярные таймауты, пользователи ждали выдачи результатов по 10+ минут.

Плюсы:

• Нет необходимости в доп. лицензиях или настройках
• Простая реализация

Минусы:

• Слабая производительность, особенно на больших объёмах
• Нереалистичные сроки ответа системы
• Неправильные результаты поиска (нет учета форм слова)

Позитивный кейс

Внедрили Full-Text Search (FULLTEXT INDEX в MySQL). Поиск стал возвращать до 100 раз быстрее, появилась возможность искать «похожие» слова и фразы, добавили ранжирование.

Плюсы:

• Мгновенный поиск
• Релевантная выдача, поддержка морфологии
• Масштабируемость

Минусы:

• Требуются ресурсы на поддержание индекса
• Индекс создаётся на строковых полях, не работает для вложенных структур