Fulltext индексы, режимы поиска, релевантность, оптимизация текстового поиска
Fulltext индекс — специализированная структура для поиска слов и фраз в текстовых данных. Он принципиально отличается от B-Tree и решает другие задачи.
Fulltext индекс можно создать для текстовых столбцов (CHAR, VARCHAR, TEXT):
-- Создание Fulltext индекса
CREATE FULLTEXT INDEX idx_ft_body ON articles(body);
CREATE FULLTEXT INDEX idx_ft_title_body ON articles(title, body);
-- При создании таблицы
CREATE TABLE articles (
id BIGINT PRIMARY KEY,
title VARCHAR(255),
body TEXT,
FULLTEXT idx_ft (title, body)
);Fulltext индекс разбивает текст на токены (слова) и строит инвертированный индекс: слово → список документов, содержащих это слово.
-- NATURAL LANGUAGE MODE (по умолчанию) — автоматическая релевантность
SELECT id, title,
MATCH(title, body) AGAINST('MySQL optimization') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL optimization')
ORDER BY score DESC;MATCH() возвращает релевантность (float) — можно использовать для сортировки. Релевантность рассчитывается на основе TF-IDF: частота слова в документе × обратная частота в коллекции.
BOOLEAN MODE поддерживает булевы операторы:
-- +обязательно, -исключить
SELECT id, title FROM articles
WHERE MATCH(body) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);
-- Точная фраза
SELECT id, title FROM articles
WHERE MATCH(body) AGAINST('"MySQL optimization"' IN BOOLEAN MODE);
-- Префикс (wildcard)
SELECT id, title FROM articles
WHERE MATCH(body) AGAINST('MySQL*' IN BOOLEAN MODE);
-- Необязательное слово (повышает релевантность)
SELECT id, title FROM articles
WHERE MATCH(body) AGAINST('>MySQL <MariaDB' IN BOOLEAN MODE);Операторы BOOLEAN MODE:
| Оператор | Значение | Пример |
|---|---|---|
+ | Обязательно | +MySQL |
- | Исключить | -PostgreSQL |
> | Повысить релевантность | >MySQL |
< | Понизить релевантность | <PostgreSQL |
* | Префикс (в конце слова) | MySQ* |
"" | Точная фраза | "MySQL optimization" |
| (нет) | Необязательное | MySQL |
BOOLEAN MODE не рассчитывает релевантность — все строки, удовлетворяющие условиям, возвращаются без ранжирования.
Минимальная длина слова:
-- InnoDB: по умолчанию 4 символа
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
-- Слова короче 4 символов не индексируются: 'SQL', 'DB'Стоп-слова — часто встречающиеся слова, исключённые из индекса:
-- Встроенные стоп-слова: 'the', 'is', 'and', 'но', 'и', 'в'
-- Кастомный стоп-словарь
SET GLOBAL innodb_ft_server_stopword_table = 'mydb/my_stopwords';50% порог (только NATURAL MODE): слово, встречающееся в >50% строк, получает вес 0. Это защита от слишком общих слов. В BOOLEAN MODE порога нет.
Изменение минимальной длины слова:
# my.cnf
[mysqld]
innodb_ft_min_token_size = 3
# После изменения — перестроить Fulltext индекс-- Перестроить Fulltext индекс после изменения настроек
ALTER TABLE articles DROP INDEX idx_ft_body;
ALTER TABLE articles ADD FULLTEXT INDEX idx_ft_body(body);Кеш Fulltext индекса:
-- Размер кеша для Fulltext индексации (в байтах)
SHOW VARIABLES LIKE 'innodb_ft_cache_size';
-- По умолчанию 32 МБ; увеличение ускоряет массовую индексацию
SET GLOBAL innodb_ft_cache_size = 64000000; -- 64 МБДля таблиц с миллионами строк:
-- Массовая загрузка: отключить cache, загрузить, включить
SET GLOBAL innodb_optimize_fulltext_only = ON;
ALTER TABLE articles ENGINE=InnoDB; -- перестроить Fulltext индексСтратегии:
LIKE 'prefix%' вместо Fulltext| Задача | Решение |
|---|---|
| Поиск по началу строки | B-Tree: WHERE email LIKE 'test%' |
| Поиск слова внутри текста | Fulltext: MATCH(body) AGAINST('word') |
| Точное совпадение | B-Tree: WHERE status = 'active' |
| Поисковая строка на сайте | Fulltext + BOOLEAN MODE |
| Фразовый поиск | Fulltext: AGAINST('"exact phrase"' IN BOOLEAN MODE) |
Fulltext — для текста, B-Tree — для структурированных данных. Не используйте Fulltext для email, статусов, кодов.
BOOLEAN MODE для пользовательского поиска — даёт контроль через операторы. NATURAL MODE — для автоматического ранжирования.
Проверяйте 50% порог. Если поиск не находит очевидные результаты — слово может встречаться в >50% строк. Переключитесь на BOOLEAN MODE.
Настраивайте ft_min_token_size под ваши данные. Если нужны 2-3-символьные аббревиатуры — уменьшайте порог.
Далее: Индексы для больших данных