Полнотекстовый поиск с сортировкой по релевантности на основе SQL запроса в СУБД MySQL

Решил написать на эту тему отдельную статью т.к. думаю, что это будет очень интересно для читателей блога. Естественно я не первый кто описывает данный метод, поэтому на правду в последней инстанции не претендую. Вообще когда данный способ поиска с сортировкой по релевантности стал возможен в СУБД MySQL был большой резонанс, многие об этом писали, в частности в US нете. Действительно способ очень интересный, т.к. предоставляет простейший механизм качественного быстрого релевантного полнотекстового поиска по базе данных. Т.е. вы получаете мощнейшую поисковую систему, которая быстро и не ресурсозатратно ищет различные слова и словосочетания (с морфологией) при этом сортирует результаты по релевантности. Сразу скажу, что описанный в статье метод применим к СУБД MySQL, для неё и писался т.к. в работе применяются специфические именно для этой СУБД операторы и индексы, которые не входят в общую спецификацию языка SQL.  Ну да хватит лирических отступлений, давайте переходить к делу.

Собственно весь механизм поиска очень просто, поэтому то он и получил большое распространение. Всё умещается всего лишь в один SQL запрос. Основополагающей фундаментальной составляющей в этом деле служит специфичный сугубо для СУБД MySQL оператор полнотекстового поиска MATCH () AGAINST (), который работает в двух режимах: IN NATURAL LANGUAGE MODE (режим естественного языка) и IN BOOLEAN MODE (логический режим). В нашем мощном полнотекстовом поисковом SQL запросе мы будем использовать сразу все два этих режима оператора, так называемый, смешанный режим. Естественно данный оператор, для достижения максимальной скорости выборки, работает только с индексами (а не с столбцами), поэтому надо будет создать индекс для поля по которому будет вестись поиск. Естественно мы будем искать по большим полям, таким как text (65535), поэтому будем создавать специфичный именно для СУБД MySQL полнотекстовый индекс (FULL TEXT INDEX). Суть именно релевантной сортировки результатов поиска в том, что оператор MATCH AGAINST возвращает числовую степень похожести ячейки на запрос. По этой самой степени сходства строк мы и будем, в результате, сортировать результаты поиска. Собственно вот и вся логика полнотекстового поиска с сортировкой по релевантности.

Переходим от теории к практике. Для столбика по которому будет вестись поиск создадим полнотекстовый индекс. Если поиск будет вестись по одному столбцу, то для него и создаём один полнотекстовый индекс. В примере будем создавать один полнотекстовый индекс сразу для двух полей таблицы title и description. Когда поиск будет вестись сразу по нескольким полям, то обязательно нужно создавать один составной полнотекстовый индекс сразу для этих столбцов. Нельзя создавать по одному индексу на каждое поле если поиск будет вестись одновременно по нескольким столбцам.

Сразу скажу, что полнотекстовый индекс это прерогатива сугубо СУБД MySQL.

CREATE FULLTEXT INDEX `ixFullText` ON `table_name` (`title`, `description`);

Создали именованный полнотекстовый индекс ixFullText для столбцов title и description таблицы table_name.

Обязательно для всех полей по которым ведётся полнотекстовый поиск нужно создавать полнотекстовый индекс иначе СУБД MySQL выдаст ошибку:

Error Code: 1191. Can’t find FULLTEXT index matching the column list

Когда создаётся один индекс для нескольких текстовых полей, то их строки просто склеиваются, после этого слова текста разделяются (разделителем служит символ пробела) и заносятся в отдельную не видимую таблицу индекса в каждой строке которой указывается слово и строки в которых оно встречается. При этом указанные «стоп слова» и слова длинной меньше 4-ёх символов включительно (значение по умолчанию, которое можно изменить) в индекс не попадают, следовательно по ним искать нельзя.

Теперь, когда полнотекстовый индекс создан, приступим к самому поисковому SQL запросу.

SELECT `id`, `title`,
MATCH (title, description) AGAINST ('query_string' IN NATURAL LANGUAGE MODE) AS score
FROM table1
WHERE MATCH (title, description) AGAINST ('query_string' IN BOOLEAN MODE)
ORDER BY score DESC;

Сначала считаем степень похожести, далее вытаскиваем данные и в конце сортируем полученные данные по релевантности. Указываем оператор MATCH() дважды. Это не приведет к дополнительным издержкам, так как оптимизатор СУБД MySQL учтет, что эти два вызова MATCH() идентичны, и запустит код полнотекстового поиска только однажды. Естественно степень идентичности можно так же дополнительно использовать в своей программе т.к. этот результат так же вернётся для каждой строки отдельно.

Поделиться!
Tags: , ,

49.69MB | MySQL:53 | 0,306sec