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

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

Поиск по слову «механизм базы данных» с использованием полнотекстового поиска вернет результаты, содержащие фразы «база данных», «механизм» или «механизм базы данных». Это устраняет возможную двусмысленность в ваших критериях поиска, поэтому строки типа «У меня есть база данных и движок» по-прежнему отображаются.

Простые поиски в MySQL можно выполнять с помощью оператора LIKE. Это неэффективно и ограничено по функциональности. Обычно MySQL необходимо выполнить полное сканирование таблицы, чтобы идентифицировать записи, соответствующие вашему запросу.

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

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

Вы можете использовать полнотекстовый поиск только со столбцами, имеющими полнотекстовый индекс. Используйте предложение FULLTEXT в инструкциях CREATE TABLE, чтобы настроить это при заполнении схемы базы данных. Вы также можете использовать его с ALTER TABLE, чтобы добавить поисковый индекс к существующим столбцам.

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

Имея индекс на месте, вы готовы начать запросы к базе данных.

Использование полнотекстового поиска

Полнотекстовый поиск начинается с предложения WHERE. Вы используете MATCH AGAINST вместо LIKE. Вам нужно указать проиндексированные столбцы для сопоставления, а также запрос для поиска.

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

Это выполнит полнотекстовый поиск статей с использованием запроса database engine. Указав В РЕЖИМЕ ЕСТЕСТВЕННОГО ЯЗЫКА, MySQL получает указание интерпретировать запрос буквально, не обрабатывая его каким-либо образом. Это режим поиска по умолчанию, если он не указан.

Упорядочивание по релевантности результата

Одним из преимуществ полнотекстового поиска является то, что он позволяет упорядочивать возвращаемые записи по релевантности. Это невозможно с помощью обычного запроса LIKE. Вы можете использовать предложение MATCH ... AGAINST как часть SQL-запроса SELECT. Возвращенный виртуальный столбец будет содержать показатель релевантности от 0 до 1, указывающий, насколько точно запись соответствует поисковому запросу.

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

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

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

При использовании MATCH ... AGAINST в операторе SELECT вам не нужно повторять его в предложении WHERE. Вы можете вручную отфильтровать результаты, чтобы включить только записи с ненулевым показателем релевантности.

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

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

Естественный язык — не единственный поддерживаемый режим полнотекстового поиска. Режим расширения запроса является альтернативой, которая помогает расширить диапазон результатов поиска. Он автоматически взвешивает поисковый запрос по наиболее релевантным терминам.

Расширенный поиск начинается с поиска записей, содержащих совпадение с запросом. Эти записи затем проверяются, чтобы определить наиболее релевантные слова. Затем база данных выполняет еще один поиск, на этот раз на основе релевантных слов, а не исходного запроса. Обычно это приводит к тому, что возвращается больше записей при сохранении приемлемой степени релевантности.

Вот как вы включаете режим расширения запроса:

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

Логический режим

Последний режим полнотекстового поиска — это логический режим. Это позволяет включать логические модификаторы в ваш запрос. Вы можете использовать этот режим, когда вам нужен расширенный контроль над логикой сопоставления.

Вы можете потребовать, чтобы слово присутствовало в каждом результате, поставив перед ним префикс +. Используйте -, чтобы исключить результаты, содержащие это слово. Другие операторы можно использовать для сопоставления частей слов, создания подвыражений и того, чтобы слова уменьшали показатель релевантности. Последнее может быть полезно при маскировании «шумовых» терминов. Если вы не укажете оператор, подразумевается, что слово будет упоминаться в запросе с помощью ИЛИ.

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

Этот запрос выведет статьи, содержащие хотя бы одно слово, начинающееся с data, где sqlite не упоминается. Каждый результат может включать или не включать слово engine.

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

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

MySQL поставляется с несколькими параметрами полнотекстовой конфигурации, которые позволяют точно настроить способ проведения поиска. Вот некоторые из наиболее важных.

innodb_ft_min_token_size — устанавливает минимальную длину слова для индексируемых терминов. Слова с меньшим количеством символов, чем это значение, не будут добавлены в индекс, поэтому вы не сможете их искать. Значение по умолчанию — 3, что исключает чрезвычайно распространенные слова, такие как a, an и I. Этот параметр применяется к таблицам InnoDB; используйте ft_min_word_len для MyISAM.
innodb_ft_max_token_size — аналогично innodb_ft_min_token_size устанавливает максимальную длину индексируемых слов. Более длинные слова не будут доступны для поиска. Используйте ft_max_word_len для таблиц MyISAM.
innodb_ft_enable_stopword — этот параметр, включенный по умолчанию, позволяет вам контролировать, должен ли MySQL отфильтровывать «стоп-слова». Стоп-слова — это очень часто используемые слова, которые могут ненадлежащим образом повлиять на результаты поиска. Список стоп-слов по умолчанию содержит 36 частых фраз.
innodb_ft_user_stopword_table. Вы можете установить это значение на имя таблицы базы данных, из которой MySQL должен получить список стоп-слов. В этой таблице должен быть один столбец VARCHAR с именем value. Добавьте свои стоп-слова для исключения в качестве записей в этой таблице.

Эти настройки обычно задаются в файле конфигурации вашего сервера MySQL. Расположение зависит от платформы; его часто можно найти в /etc/mysql/my.cnf. Вам нужно будет перезапустить службу MySQL после изменения настроек.

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

Чтобы переиндексировать таблицу InnoDB, запустите OPTIMIZE TABLE my_table. Для таблиц MyISAM используйте REPAIR TABLE my_table QUICK. После этого поисковые индексы будут перестроены, чтобы изменения конфигурации вступили в силу.

Краткое содержание

Полнотекстовый поиск MySQL включается путем добавления индекса FULLTEXT в ваши поля для поиска. Затем вы используете MATCH ... AGAINST с одним из трех режимов поиска, чтобы получить результаты. Запросы на естественном языке возвращают оценку релевантности поиска, которую можно использовать для ранжирования результатов.

Полнотекстовый поиск обеспечивает более эффективное поисковое поведение, чем оператор LIKE. Это также намного более производительно, особенно для больших наборов данных, так как весь текст индексируется заранее.