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

Логотип MySQL

Быстрые ссылки

Настройка полнотекстового поиска
Использование полнотекстовых запросов
Сортировка по релевантности
Режим расширения запроса
Булевый режим
Конфигурация и тонкая настройка
Практические рекомендации и чек-листы
Резюме

Что такое полнотекстовый поиск — простыми словами

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

Ключевая идея: вместо перебора каждой строки с помощью LIKE сервер заранее строит индекс слов и использует его для быстрого и релевантного поиска.

Важно: это поисковая функциональность внутри СУБД. Для более продвинутых требований часто используются внешние движки поиска (Elasticsearch, Sphinx и т.д.).

Настройка полнотекстового поиска

Полнотекстовый поиск в MySQL работает только для колонок, к которым добавлен FULLTEXT-индекс. Индекс можно создать при создании таблицы или добавить позже:

CREATE TABLE articles(content TEXT, FULLTEXT (content));

Добавление индекса к существующей таблице:

ALTER TABLE articles ADD FULLTEXT (content);

После добавления индекса таблица готова к полнотекстовым запросам.

Примечание: FULLTEXT поддерживается для типа TEXT/CHAR/VARCHAR и различных движков хранения; у InnoDB и MyISAM есть свои параметры конфигурации.

Как выполнять полнотекстовый поиск

Вместо оператора LIKE используйте MATCH … AGAINST в WHERE или SELECT. Пример поискового запроса в естественном режиме:

SELECT * FROM articles WHERE MATCH (content) AGAINST ('database engine' IN NATURAL LANGUAGE MODE);

Если режим явно не указан, по умолчанию применяется естественный (natural language) режим.

Пример с выборкой релевантности

Вы можете вернуть виртуальную колонку с оценкой релевантности и сортировать по ней:

SELECT content, MATCH (content) AGAINST ('database engine') AS relevance FROM articles ORDER BY relevance DESC;

Оценка релевантности помогает ранжировать результаты так, чтобы наиболее подходящие попадали в начало.

Если требуется отфильтровать только совпавшие записи, можно использовать ту же формулу в WHERE через подзапрос или вычислить её в SELECT и затем отфильтровать по значению relevance > 0:

SELECT content, MATCH (content) AGAINST ('database engine') AS relevance FROM articles WHERE MATCH (content) AGAINST ('database engine') > 0 ORDER BY relevance DESC;

(Некоторые версии MySQL не позволяют ссылаться на псевдоколонку relevance в WHERE в том же запросе — в таких ситуациях используйте подзапрос или HAVING.)

Режим расширения запроса

Режим WITH QUERY EXPANSION расширяет исходный запрос, автоматически подбирая наиболее релевантные термины из начального набора подходящих документов и выполняя повторный поиск по расширённому набору слов. Это увеличивает охват результатов при сохранении допустимого уровня релевантности.

Пример:

SELECT * FROM articles WHERE MATCH (content) AGAINST ('database engine' WITH QUERY EXPANSION);

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

Булевый режим

Булевый режим позволяет вставлять управляющие символы в запрос, задавая точные правила соответствия:

- слово обязательно
- слово должно отсутствовать
- служит в качестве суффиксного подстановочного знака (например data*)

Пример:

SELECT * FROM articles WHERE MATCH (content) AGAINST ('+data* engine -sqlite' IN BOOLEAN MODE);

Этот запрос вернёт статьи, где присутствует слово, начинающееся на “data”; статьи, содержащие “sqlite”, будут исключены; слово “engine” учитывается, но не обязательно.

Ограничение булевого режима: он не возвращает оценку релевантности, сопоставимую с естественным режимом. Поэтому при использовании булева вы жертвуете ранжированием в пользу предсказуемой логики совпадения.

Конфигурация и параметры, которые важно знать

MySQL имеет ряд переменных, влияющих на индексирование и поведение полнотекстового поиска. Ниже — самые значимые (названия даны как в MySQL):

innodb_ft_min_token_size — минимальная длина слова для индексирования в InnoDB (по умолчанию 3). Короткие слова не индексируются и не будут найдены.
innodb_ft_max_token_size — максимальная длина слова для индексирования в InnoDB.
ft_min_word_len — эквивалент для MyISAM.
ft_max_word_len — эквивалент для MyISAM.
innodb_ft_enable_stopword — включает фильтрацию стоп-слов по умолчанию.
innodb_ft_user_stopword_table — задаёт таблицу, из которой берётся пользовательский список стоп-слов; таблица должна иметь одну колонку VARCHAR value.

Эти параметры обычно настраиваются в конфигурационном файле сервера (часто /etc/mysql/my.cnf) и требуют перезапуска сервера.

После изменения параметров обязательно перестроить полнотекстовые индексы, иначе старые индексы останутся в употреблении.

Рекомендованные шаги для перестроения индексов:

Для InnoDB:

OPTIMIZE TABLE my_table;

Для MyISAM:

REPAIR TABLE my_table QUICK;

Эти операции перестроят индексы в соответствии с текущими параметрами.

Важно: на больших таблицах перестроение может быть долгим и требовать места на диске и временных заморозок операций записи. Планируйте обслуживание.

Корректировка стоп-слов и пользовательский список

Стоп-слова — очень общие слова, которые по умолчанию исключаются из индекса (например, a, an). В InnoDB список стоп-слов включён по умолчанию. Чтобы использовать свой список:

Создайте таблицу stopwords в базе данных с одной колонкой value VARCHAR.
Заполните её своими стоп-словами.
Укажите innodb_ft_user_stopword_table=your_db.stopwords в конфиге.
Перезапустите сервер и перестройте индексы.

Это полезно для локализации: например, в русском языке набор стоп-слов отличается. Подумайте о стоп-словах и морфологии для целевого языка.

Практические приёмы и оптимизация

Индексируйте только те поля, по которым действительно выполняется поиск. Избыточные индексы замедляют запись.
Для длинных документов разделяйте данные на смысловые части (title, summary, body) и индексируйте отдельно — это даёт гибкость для взвешивания полей.
Если нужен контроль ранжирования, используйте взвешивание полей: MATCH (title, body) AGAINST (…), а затем применяйте коэффициенты в SELECT (например 2.0title_score + 1.0body_score).

Пример взвешивания:

SELECT id, (2 * MATCH(title) AGAINST ('term') + MATCH(body) AGAINST ('term')) AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('term')
ORDER BY score DESC;

Избегайте слишком коротких или слишком общих запросов без дополнительной логики: короткие запросы сильно расширяют результаты и могут понизить качество выдачи.
Локализация и морфология: MySQL не выполняет нормализацию слов (stemming) по умолчанию. Для языка с богатой морфологией (например, русский) рассмотрите использование внешнего поискового движка либо предварительную нормализацию (стемминг/лемматизация) текста перед индексацией.

Когда полнотекстовый поиск не подходит (контрпример)

Если нужна морфологическая обработка и работа с синонимами на уровне языка — встроенный функционал MySQL ограничен.
Если требуется распределённый поиск с миллиардами документов и сложной аналитикой — внешние движки типа Elasticsearch обычно дают больше возможностей по масштабированию и агрегации.
Если нужен мгновенный поиск по миллисекундам при интенсивном потоке записей и сложными требованиями к ранжированию — отдельный поисковый кластер может быть лучшим выбором.

Альтернативные подходы

LIKE / ILIKE — простой, но медленный, требует полного сканирования таблицы.
REGEXP — гибче, но также не индексируется и дорогостоящая операция.
Внешние поисковые движки — Elasticsearch, OpenSearch, Sphinx — предлагают расширенные возможности: морфология, синонимы, масштабируемость, кастомное ранжирование.
Использовать гибридный подход: полнотекст в MySQL для небольших наборов и быстрых задач, а для крупномасштабного поиска — отдельный движок.

Чек-листы

Чек-лист для разработчика перед релизом фичи поиска:

Добавлен FULLTEXT-индекс к колонкам, по которым будет искаться.
Проверены границы минимальной длины токена и список стоп-слов.
Написаны тесты: unit-тесты для ранжирования, интеграционные тесты для функциональности.
Реализовано логирование медленных запросов поиска.
Добавлены ограничения на длину/сложность пользовательских запросов.

Чек-лист для администратора БД при изменении конфигурации:

[ ] Изменён конфигурационный файл (указаны переменные innodbft или ft_).
Выполнен перезапуск MySQL.
Перестроены полнотекстовые индексы (OPTIMIZE или REPAIR).
Проверено влияние на диск и время выполнения операций.
Проведён smoke-test поиска на типичных запросах.

Критерии приёмки

Поиск возвращает релевантные результаты для набора тестовых запросов.
Запросы выполняются в пределах допустимого времени для SLA (обозначьте конкретный порог в своем проекте).
Отсутствие регрессий в операциях записи после добавления индексов.
Корректная обработка стоп-слов и минимальных длин токенов согласно требованиям.

Частые ошибки и как их отлавливать

Неправильный минимальный размер токена — короткие слова не индексируются. Проверьте innodb_ft_min_token_size и ft_min_word_len.
Не перестроены индексы после изменения конфигурации — всё ещё используются старые значения стоп-слов и размеров токенов.
Ожидание ранжирования в булевом режиме — булевый режим не даёт привычной оценки релевантности.
Поиск по русским словам без нормализации — приводите текст к нормализованному виду или используйте внешний движок.

Примерный план миграции на внешний движок

Выявить требования: язык, синонимы, морфология, SLA.
Настроить пайплайн синхронизации данных (CDC, периодическая выгрузка или real-time через очередь).
Создать индекс и тестовые запросы в целевом движке.
Провести A/B тестирование и сравнить релевантность и производительность.
Переключить трафик постепенно.

Безопасность и конфиденциальность

Поисковые индексы содержат извлечённый текст — учитывайте права доступа и шифрование резервных копий.
При работе с персональными данными убедитесь, что поиск не раскрывает закрытую информацию пользователям, у которых нет соответствующих прав.
Для соответствия GDPR/локальному законодательству предусмотреть механизмы удаления данных из индекса при удалении записи пользователя.

Тестовые случаи и приёмочные критерии

Поисковый запрос из 1–2 слов возвращает ожидаемые верхние N результатов.
Булевый запрос с + и - корректно фильтрует документы.
После изменения списка стоп-слов короткие запросы начинают/перестают возвращать ожидаемые результаты.
Перестройка индекса завершилась без ошибок и время отклика поиска не ухудшилось значительно.

Шаблон команды для отказоустойчивой перестройки индекса (режим общего обслуживания)

Отключите запись в целевые таблицы (или переведите приложение в режим ограниченного доступа).
Выполните OPTIMIZE TABLE для InnoDB или REPAIR TABLE QUICK для MyISAM.
Прогоните тестовый набор запросов.
Верните приложение в боевой режим.

Примеры запросов для повседневных сценариев

Поиск по нескольким полям с весами:

SELECT id, (2 * MATCH(title) AGAINST ('sql') + MATCH(body) AGAINST ('sql')) AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('sql')
ORDER BY score DESC;

Булево-отфильтрованный поиск:

SELECT * FROM articles WHERE MATCH(content) AGAINST ('+backup -snapshot' IN BOOLEAN MODE);

Режим расширения запроса:

SELECT * FROM articles WHERE MATCH (content) AGAINST ('replication' WITH QUERY EXPANSION);

Резюме

Полнотекстовый поиск в MySQL — мощный инструмент для текстового поиска внутри СУБД. Он быстрее и функционально богаче, чем простой LIKE, но имеет свои ограничения: отсутствие встроенной морфологии для многих языков и ограничения булевого режима по ранжированию. Планируйте конфигурацию (минимальный размер токена, стоп-слова), перестраивайте индексы после изменений и тестируйте релевантность. Для сложных задач поиска рассмотрите гибридный подход с внешним поисковым движком.

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

Ключевые идеи:

Используйте FULLTEXT-индекс и MATCH … AGAINST вместо LIKE для масштабируемого текстового поиска.
Выбирайте режим поиска в зависимости от задачи: естественный — для релевантности; расширение запроса — для более широкого охвата; булевый — для точной логики поиска.
Настраивайте параметры и перестраивайте индексы после изменений.

Примечание: стоп-слова и параметры min/max token size зависят от выбранного движка хранения (InnoDB vs MyISAM).