Полнотекстовый поиск по базе данных MySQL 5.6 в Ubuntu 16.04

30 ноября, 2017 12:02 пп 4 820 views | Комментариев нет

, | | Комментировать запись

Полнотекстовый поиск – это метод, используемый поисковыми системами для поиска результатов в базе данных. Вы можете использовать его для поиска результатов на новостных сайтах, в онлайн-магазинах и т. п.

FTS извлекает документы – объекты базы данных, содержащие текстовые данные, которые не соответствуют критериям поиска. Если, к примеру, пользователь вводит запрос «овощи и фрукты», приложение с поддержкой FTS может возвращать результаты, которые содержат:

  • отдельные слова из запроса (например, только «овощи» или «фрукты»)
  • слова в другом порядке («фрукты и овощи»)
  • варианты слов («овощ» или «фрукт»).

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

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

Полнотекстовый поиск позволяет создать более мощную поисковую систему, которая не зависит от сложных  инструментов. В этом мануале вы на примере БД MySQL 5.6 научитесь выполнять полнотекстовый поиск, отбирать лучшие результаты и повышать производительность поиска.

Требования

1: Создание тестовой базы данных

Для начала нужно добавить данные, на которых можно потренироваться выполнять полнотекстовый поиск.

Примечание: Если у вас уже есть таблица с такими данными, можете переходить к разделу 2.

Создайте БД testdb, а затем создайте в базе данных тестовую таблицу news.

Откройте консоль MySQL. При этом будет запрошен root-пароль MySQL.

mysql -u root -p

После этого строка изменится:

mysql>

Теперь создайте базу данных testdb.

CREATE DATABASE testdb;

Сделайте базу данных testdb базой по умолчанию, чтобы не пришлось указывать имя БД при создании или обновлении ее компонентов.

USE testdb;

Затем создайте таблицу в базе данных тестовую таблицу news.

CREATE TABLE news (
id INT NOT NULL AUTO_INCREMENT,
title TEXT NOT NULL,
content TEXT NOT NULL,
author TEXT NOT NULL,
PRIMARY KEY (id)
);

  • CREATE TABLE – это команда SQL, которая создает таблицу.
  • news – название таблицы.
  • title, content и author – текстовые столбцы неограниченной длины.
  • NOT NULL – определяет столбцы, которые не могут иметь нулевые значения (хотя они могут содержать пустые строки).
  • id – основной индекс таблицы с особым типом AUTO_INCREMENT, который автоматически заполняет поле ID следующим доступным идентификатором.

Добавьте в таблицу несколько записей:

INSERT INTO news (id, title, content, author) VALUES
(1, 'Pacific Northwest high-speed rail line', 'Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.', 'Greg'),
(2, 'Hitting the beach was voted the best part of life in the region', 'Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.', 'Ethan'),
(3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');

Рассмотрим подробнее эту команду:

  • INSERT вставляет данные.
  • INTO указывает, куда нужно поместить данные (в данном случае в таблицу news).
  • (id, title, content, author) VALUES определяет столбцы, в которых следует хранить данные.
  • Последние три строки – это строки данных, которые будут добавлены в таблицу. Каждая строка содержит условную статью для новостного сайта с заголовком, некоторый контент и имя автора.

Каждая запись также имеет уникальный id, который автоматически вводится в индекс базы данных. Индекс базы данных – это структура, которая улучшает производительность операций поиска данных. Этот индекс хранится отдельно от основных данных. Он обновляет содержимое таблицы за счет дополнительных записей и сравнительно небольшого объема памяти. Небольшой размер и индивидуальная структура позволяют индексам работать намного эффективнее пространства основной таблицы.

2: Создание индекса полнотекстового поиска

Создайте индекс для текстовых столбцов, чтобы затем выполнить полнотекстовый поиск.

Для этого нужно использовать специальную команду MySQL, FULLTEXT. Эта команда позволяет выполнять полнотекстовый поиск по заданным полям.

ALTER TABLE news ADD FULLTEXT (title, content, author);

Команда объединяет все текстовые столбцы и фильтрует их (например, удаляет пунктуацию и преобразовывает прописные буквы в нижний регистр). Полученный индекс будет обновляться любым SQL-запросом, который изменяет содержимое исходной таблицы.

Теперь попробуйте выполнить полнотекстовый поиск по запросу «Seattle beach» с помощью функции MATCH() AGAINST().

SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('Seattle beach' IN NATURAL LANGUAGE MODE)\G

Часть команды MATCH() указывает, какой набор столбцов индексируется с помощью полнотекстового поиска; здесь нужно указать список столбцов, который использовался для создания индекса. Часть AGAINST() указывает, какое слово применяется для полнотекстового поиска (в данном примере это Seattle beach).

IN NATURAL LANGUAGE MODE включает режим, при котором слова для поиска поступают непосредственно из пользовательского ввода без предварительной обработки. MySQL по умолчанию использует этот режим, поэтому явно указывать его не нужно.

Примечание: word stemming – еще один полезный метод полнотекстового поиска, который сохраняет только корневую часть слова. Например, слова «подходит» и «подойти» были бы идентичными в этом режиме. К сожалению, MySQL не поддерживает режим word stemming. Stemming есть в рабочем логе MySQL, но пока что нет возможности реализовать его. Тем не менее, полнотекстовый поиск по-прежнему полезен, потому что он намного быстрее, чем LIKE. Если вы хотите использовать word stemming, вы можете интегрировать библиотеку Snowball.

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

*************************** 1. row ***************************
id: 1
title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
author: Greg
*************************** 2. row ***************************
id: 2
title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
author: Ethan
2 rows in set (0.00 sec)

Как видите, в таблице не нашлось записей, содержащих запрос «Seattle beach». Но полнотекстовый поиск обнаруживает два результата: в первой строке содержится только слово «Seattle», а во второй содержится только слово «beach». Вы можете попробовать создать дополнительные запросы, изменив ключевые слова.

Теперь вы можете использовать функции полнотекстового поиска в SQL-запросах и находить строки.

3: Оптимизация полнотекстового поиска

Есть два метода, которые могут помочь сделать результаты полнотекстового поиска более актуальными. Первый метод – фильтрация по релевантности результатов. Второй – исключение слов из результатов с помощью IN BOOLEAN.

Фильтрация результатов по релевантности

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

Попробуйте определить оценку релевантности каждого ряда на основе запроса «traveling to parks».

SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news;

Благодаря опции as score эта команда отмечает второй столбец результата как score. В противном случае она бы отметила его командой, с помощью которой он был заполнен (в этом случае это MATCH (title,content,author) AGAINST (‘traveling to parks’)).

Команда вернет:
+----+----------------------+
| id | score                |
+----+----------------------+
|  1 | 0.031008131802082062 |
|  2 |  0.25865283608436584 |
|  2 |  0                   |
+----+----------------------+
3 rows in set (0.00 sec)

Третья строка имеет оценку релевантности 0, так как в ней нет ни одного слова из поиска. Первая строка содержит слово «traveling», но в ней нет «to» или «parks», потому она получает очень низкую оценку релевантности 0,03. Вторая строка, содержащая все слова, имеет самый высокий показатель релевантности 0,25.

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

Следующая команда запускает тот же запрос, но добавляет две вещи:

  • Она отображает только строки с ненулевым показателем релевантности с помощью WHERE MATCH (title,content,author) AGAINST (‘traveling to parks’) > 0.
  • Она сортирует результаты по релевантности с помощью ORDER BY score DESC.
  • SELECT id, MATCH (title,content,author) AGAINST (‘traveling to parks’) as score FROM news WHERE MATCH (title,content,author) AGAINST (‘traveling to parks’) > 0 ORDER BY score DESC;

+----+----------------------+
| id | score                |
+----+----------------------+
|  2 |  0.25865283608436584 |
|  1 | 0.031008131802082062 |
+----+----------------------+
2 rows in set (0.01 sec)

Сначала отображается наиболее релевантный результат, строка 2, за которым следует менее релевантная строка 1. Строка 3 вообще не показана, потому что ее показатель релевантности равен 0.

Вы можете продолжить тонкую настройку этой функции самостоятельно.

Метод IN BOOLEAN

Ранее при указании условия запроса вы использовали режим по умолчанию IN NATURAL LANGUAGE. Существует еще один режим – IN BOOLEAN, который позволяет исключать определенные слова из поиска, определять диапазон поисковых слов и многое другое.

Чтобы исключить слово из запроса, используйте минус в IN BOOLEAN. Следующая команда вернет результаты, содержащие слово «traveling», исключая слово «Seattle».

SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('traveling -Seattle' IN BOOLEAN MODE)\G
*************************** 1. row ***************************
id: 2
title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
author: Ethan
1 row in set (0.01 sec)

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

Вы также можете использовать IN BOOLEAN MODE, чтобы указать максимальное расстояние между словами поиска. Это расстояние измеряется в словах и, что важно, включает условия поиска. Например, во фразе «cats and dogs» расстояние 3.

Следующая команда возвращает результаты, в которых слова «traveling» и «miles» встречаются на расстоянии не более двух слов.

SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('"traveling miles" @4' IN BOOLEAN MODE)\G
*************************** 1. row ***************************
id: 1
title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
author: Greg
1 row in set (0.00 sec)

Если изменить @4 на @3 в исходной команде, вы не увидите никаких результатов.

Ограничение результатов поиска по расстоянию между поисковыми словами может быть полезно при поиске по очень крупным документам. Чем меньше разрыв между словами, тем точнее будут результаты. Тонкая настройка этой функции будет зависеть от набора документов, с которыми вы работаете. Например, для научных работ может подойти небольшое расстояние в 3 слова, а для поиска сообщений на форуме – в 8 слов или больше.

Заключение

Данный мануал учит работать с полнотекстовым поиском в MySQL. Теперь вы знаете, как создать индекс для БД и использовать специальные операторы для поиска наиболее релевантных результатов.

Читайте также: Официальная документация полнотекстового поиска в MySQL

Tags: ,

Добавить комментарий