Введение

Большинство СУБД поддерживают методы полнотекстового поиска.

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

В статье я расскажу как работать с полнотекстовым поиском на примере PostgreSQL, а так же приведу примеры использования данного механизма в связке с Laravel.


Содержание

  1. Что такое PostgreSQL
  2. Что такое полнотекстовый поиск
  3. Про индексы
  4. Примеры
  5. Примеры в Ларавел
  6. Laravel 9 Full Text
  7. Когда стоит выбирать полнотекстовый поиск в Postresql
  8. Используемые источники


Немного теории

1. Что такое PostgreSQL

PostgreSQL, или просто postgres - это объектно-реляционная система управления базами данных с открытым исходным кодом.

  • кросс-платформенная
  • за лицензирование не нужно платить
  • поддерживает свойства ACID*
  • open-course

ACID (Атомарность, Согласованность, Изолированность, Надёжность). Этот набор свойств транзакций в базе данных должен гарантировать корректность операций при параллельном выполнении, а также в случае ошибок, при отключении питания и т. п.

2. Что такое полнотекстовый поиск

Полнотекстовый поиск - это:

  • поиск документов, удовлетворяющих некоторому запросу поиска
  • сортировка результатов поиска по заданному критерию (дополнительно) 

Типичный поиск — это:

  • поиск документов, содержащих все слова запроса
  • сортировка результатов поиска по релевантности

3. Про индексы

Цель полнотекстового поиска найти в тексте слова или группы слов. Полнотекстовый поиск ближе к операции “содержит” так как мы почти никогда не ищем точную строку.

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

Для полнотекстового поиска PostgreSQL предлагает такие индексы на выбор:

  • GIN — быстро ищет, но не слишком быстро обновляется. Отлично работает, если вы сравнительно редко меняете данные, по которым ищите;
  • GiST — ищет медленнее GIN, зато очень быстро обновляется. Может лучше подходить для поиска по очень часто обновляемым данным;

Также есть и другие индексы, которые можно использовать. Например, есть индекс RUM. Это extension для Postgres, построенный на GIN. Он позволяет возвращать при проходе по индексу отсортированные по релевантности результаты.

4. Примеры

Для полнотекстового поиска в PostgreSQL предусмотрено несколько специальных типов.

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

  • tsvector – хранилище для документов, оптимизированное для поиска (полнотекстовый индекс)
  • отсортированный массив лексем
  • позиционная информация

  • структурная информация (важность)

  • tsquery – текстовый тип для запроса

  • Оператор полнотекстового поиска tsquery @@ tsvector
Например: Преобразование обычной строки в tsvector осуществляется при помощи процедуры to_tsvector

=# SELECT to_tsvector('russian', 'Привет, мир! Приветы всем');

-------------------------------------------------------
'всем':4 'мир':2 'привет':1,3
Что тут происходит?

В этом примере представлено простое предложение. Функция tsvector принимает строку, применяет к ней правила русского языка, отбрасывает стоп-слова и производит стемминг. Например, слова привет и приветы преобразуются в привет. Результат to_tsvector зависит от языка.

Например: Тип tsquery используется для представления запросов
=# SELECT to_tsvector('russian', 'Привет, мир! Приветы всем') @@ to_tsquery('russian', 'мир');

-------------------------------------------------------
?column?
true
Что тут происходит?

В этом примере показано как искать слово “мир”

Немного практики

5. Примеры в Ларавел

Для эксперимента попробуем сделать полнотекстовый поиск на примере блога. Cделаем миграцию в Laravel для таблицы сообщений в блоге:

Ula

Что здесь происходит?

Мы добавляем столбец с именем searchtext с типом TSVECTOR (к сожалению, в Laravel нет удобного метода для создания этого типа столбца, поэтому нам нужно сделать это с помощью необработанного оператора). В этой колонке будет содержаться наш документ с возможностью поиска.

Мы используем метод to_tsvector() для создания документа в каждой строке, объединяющего поля title и text, и сохраняем его в столбце searchtext. Обратите также внимание, что мы указываем язык в качестве первого аргумента. Это связано с тем, что полнотекстовый поиск Postgres понимает так называемые “стоп-слова”, которые настолько распространены, что с ними вообще не стоит возиться, такие как “the” - они, очевидно, будут отличаться в разных языках, поэтому разумно явно указать это, чтобы Postgres знал, какие стоп-слова использовать. ожидайте.

Мы создаем индекс GIN в таблице posts, используя наш новый столбец searchtext.

Наконец, мы создаем триггер, который при внесении изменений в таблицу восстанавливает текст поиска.

Ula

Сделав это, теперь мы можем перейти к фактическому выполнению полнотекстового поиска. Чтобы упростить повторное использование, мы создадим локальную область в нашей модели Post. Если вы раньше не использовали области видимости в Laravel, они, по сути, позволяют вам разбивать запросы на повторно используемые фрагменты. В этом случае мы ожидаем, что наша область получит два аргумента: экземпляр запроса (который передается автоматически) и текст поиска

Если “$search” пуст, мы просто возвращаем объект и мы поможем как В противном случае мы сначала создаем предложение WHERE, не одному такой вашему текст, поиска мне нужно принимать по столбцу searchtext. Обратите внимание на используемый здесь синтаксис:

searchtext @@ to_tsquery('russian', 'мир')

Мы используем метод to_tsquery(), чтобы сопоставить наш текст с нашим поисковым документом. Как и прежде, обратите внимание, что мы указываем язык.

Наконец, мы указываем порядок - мы хотим, чтобы совпадения с наивысшим рейтингом появлялись первыми, и этот раздел запроса выполняет это:

ts_rank(searchtext, to_tsquery('russian', 'мир')) DESC

Здесь мы используем ts_rank(), чтобы убедиться, что мы получаем наши результаты в соответствующем порядке. Обратите внимание, что для обоих запросов мы передавали аргументы в виде параметризованных запросов, а не создавали необработанную строку - мы должны следить за SQL-инъекцией при написании необработанных запросов, но мы можем использовать параметризованные запросы PDO из Eloquent в необработанном операторе, что немного упрощает задачу.

Теперь мы можем назвать нашу новую область поиска следующим образом:

$posts = Post::search($search)->get();


7. Laravel 9 Full Text

В Laravel 9 были завезены следующие методы whereFullText и orWhereFullText. В настоящее время эти функции поддерживается MySQL и PostgreSQL.

Ula

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

Ula


8. Когда стоит выбирать полнотекстовый поиск в Postresql

Когда стоит выбирать полнотекстовый поиск в Postresql вместо внешнего поискового движка?

Внешние поисковые движки быстрые, но:

  • если небольшой проект, то нет смысла подключать еще одну инфраструктуру

  • не могут индексировать все документы, например, виртуальные

  • не обеспечивают согласованность данных

  • не имеют доступа ко всем атрибутам, поэтому нельзя выполнить сложные запросы

  • требуют поддержки администратором

  • платные


9. Используемые источники

  • https://postgrespro.ru/

  • книга ‘PostgreSQL 11. Мастерство разработки’ Ганс-Юрген Шениг