Как включить мощный полнотекстовый поиск для ваших приложений

Краткий обзор полнотекстового поиска — MySQL против Elasticsearch

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

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

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

У меня будет пошаговое руководство по использованию полнотекстового поиска в MySQL и Elasticsearch.

Видео

Создание полнотекстового индекса в Microsoft SQL Server

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

Создание полнотекстового индекса на T-SQL

Для того чтобы создать полнотекстовый индекс, мы пишем инструкцию CREATE FULLTEXT INDEX.

Где

• CREATE FULLTEXT INDEX – команда создания полнотекстового индекса;
• TestTable(TextData) – таблица и столбец, включенные в индекс;
• KEY INDEX PK_TestTable – имя уникального индекса таблицы TestTable;
• ON (TestCatalog) – указываем, что полнотекстовый индекс будет создан в полнотекстовом каталоге TestCatalog. Если не указать этот параметр, то индекс будет создан в полнотекстовом каталоге по умолчанию;
• WITH (CHANGE_TRACKING AUTO) – это мы говорим, что все изменения, которые будут вноситься в базовую таблицу (TestTable), автоматически отобразятся и в нашем полнотекстовом индексе, т.е. автоматическое заполнение.

Заметка! Подробно про все типы индексов мы разговаривали в материале – Основы индексов в Microsoft SQL Server.

Создание полнотекстового индекса в графическом интерфейсе Management Studio

Полнотекстовый индекс можно создать, используя и графические инструменты, для этого открываем свойства полнотекстового каталога и переходим в пункт «Таблицы или представления», выбираем нужную таблицу, столбец, уникальный индекс и способ отслеживания изменений. В нашем случае у нас всего одна доступная таблица и один столбец. Нажимаем «ОК».

Сравнение предиката LIKE и запросов полнотекстового поиска

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

Архитектура полнотекстового индекса

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

Начиная с SQL Server 2008полнотекстовые индексы встроены в ядро СУБД, а не размещены в файловой системе, как в предыдущих версиях SQL Server. В новой базе данных полнотекстовый каталог является виртуальным объектом, не принадлежащим ни к одной файловой группе. Он является лишь логическим понятием, связанным с группой полнотекстовых индексов. Однако обратите внимание, что при обновлении базы данных SQL Server 2005 (9.x) (любого полнотекстового каталога с файлами данных) создается новая файловая группа. Дополнительные сведения см. в разделе Обновление полнотекстового поиска.

На одну таблицу может приходиться только один полнотекстовый индекс. Чтобы в таблице можно было создать полнотекстовый индекс, она должна содержать один уникальный столбец, значением которого не является NULL. Можно создать полнотекстовый индекс на столбцах типа char, varchar, nchar, nvarchar, text, ntext, image, xml и varbinary, а также для полнотекстового поиска может индексироваться varbinary(max) . Для создания полнотекстового индекса на столбце, тип данных которого varbinary, varbinary(max) , image или xml , необходимо указать столбец типов. Столбец типов — это столбец таблицы, в каждой строке которого хранятся расширения файлов (DOC, PDF, XLS и т. д.) документа.

Структура полнотекстового индекса

Чтобы понять, как работает средство полнотекстового поиска, необходимо разобраться в структуре полнотекстового индекса. В данном разделе в качестве примера таблицы используется следующий фрагмент таблицы Document в Adventure Works . В данном фрагменте показываются только два столбца: DocumentID и Title , а также три строки из таблицы.

В этом примере подразумевается, что полнотекстовый индекс был создан по столбцу Title .

DocumentID	Title
1	Crank Arm and Tire Maintenance
2	Front Reflector Bracket and Reflector Assembly 3
3	Front Reflector Bracket Installation

Например, в следующей таблице, в которой приведен фрагмент 1, показано содержимое полнотекстового индекса, созданного по столбцу Title таблицы Document . Полнотекстовые индексы содержат больше данных, чем представлено в этой таблице. Таблица является логическим представлением полнотекстового индекса, она предоставляется только с целью демонстрации. Строки хранятся в сжатом формате для оптимизации использования диска.

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

Обратите также внимание, что ключевое слово «and» было удалено из полнотекстового индекса. Это было сделано, поскольку «and» является стоп-словом. Удаление стоп-слов из полнотекстового индекса может привести к значительной экономии дискового пространства, тем самым увеличивая производительность запросов. Дополнительные сведения о стоп-словах см. в разделе Настройка и управление стоп-словами и списками стоп-слов для полнотекстового поиска.

Фрагмент 1

Ключевое слово	ColId	DocId	Наличие
Crank	1	1	1
Arm	1	1	2
Tire	1	1	4
Обслуживание	1	1	5
Front	1	2	1
Front	1	3	1
Reflector	1	2	2
Reflector	1	2	5
Reflector	1	3	2
Bracket	1	2	3
Bracket	1	3	3
Сборка	1	2	6
3	1	2	7
Установка	1	3	4

Столбец Keyword содержит представление одного токена, извлеченного при индексировании. Токены определяются средствами разбиения по словам.

Столбец ColId содержит значение, соответствующее столбцу, по которому выполняется полнотекстовое индексирование.

Столбец DocId содержит значения восьмибайтового целого числа, соответствующего определенному значению полнотекстового ключа в определенной таблице, в которой выполняется полнотекстовое индексирование. Данное поведение необходимо, если полнотекстовый ключ имеет тип данных, отличный от целого. В таких случаях сопоставления между значениями полнотекстового ключа и значениями DocId обеспечиваются в отдельной таблице, называемой таблицей сопоставления DocId. Для создания запросов к этим сопоставлениям следует использовать системную хранимую процедуру sp_fulltext_keymappings . Для удовлетворения условия поиска необходимо соединить значения DocId из приведенной выше таблицы с таблицей сопоставления DocId. Это позволит возвращать строки из запрашиваемой базовой таблицы. Если значение полнотекстового ключа из базовой таблицы имеет целочисленный тип, то само значение служит в качестве значения DocId и дополнительного сопоставления не требуется. Следовательно, использование целочисленных значений полнотекстового ключа может оптимизировать выполнение полнотекстовых запросов.

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

Фрагменты полнотекстового индекса

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

DocumentID	Title
3	Rear Reflector

В следующем примере, в котором показан фрагмент 2, содержатся более новые данные о DocId 3, чем во фрагменте 1. Следовательно, если пользователь запрашивает «задний отражатель», то для DocId 3 будут использованы данные из фрагмента 2. Каждый из фрагментов имеет отметку времени создания, которую можно использовать в запросах с помощью представления каталога sys.fulltext_index_fragments .

Фрагмент 2

Ключевое слово	ColId	DocId	Occ
Rear	1	3	1
Reflector	1	3	2

Как можно увидеть во фрагменте 2, полнотекстовым запросам необходимо осуществить внутреннее обращение к каждому фрагменту. Более старые записи не учитываются. Следовательно, наличие слишком большого количества полнотекстовых фрагментов индекса в полнотекстовом индексе может привести к существенному уменьшению производительности запросов. Для уменьшения количества фрагментов выполните переорганизацию полнотекстового каталога, используя параметр REORGANIZE инструкции ALTER FULLTEXT CATALOGTransact-SQL . Данная инструкция выполняет слияние в единый файл, объединяя все фрагменты в единый большой фрагмент, и удаляет все устаревшие записи из полнотекстового индекса.

После выполнения реорганизации в образце индекса будут содержаться следующие строки.

Ключевое слово	ColId	DocId	Occ
Crank	1	1	1
Arm	1	1	2
Tire	1	1	4
Обслуживание	1	1	5
Front	1	2	1
Rear	1	3	1
Reflector	1	2	2
Reflector	1	2	5
Reflector	1	3	2
Bracket	1	2	3
Сборка	1	2	6
3	1	2	7

Различия между полнотекстовыми индексами и обычными индексами SQL Server:

Полнотекстовые индексы	Обычные индексы SQL Server
Для одной таблицы разрешен только один полнотекстовой индекс.	Для одной таблицы разрешено несколько обычных индексов.
Добавление данных к полнотекстовым индексам (заполнение) может быть запрошено явно, выполняться по расписанию либо автоматически при добавлении новых данных в базу.	Обновляются автоматически при создании, вставке, обновлении или удалении данных, на которых они созданы.
Группируются в той же базе данных в один или несколько полнотекстовых каталогов.	Не группируются.

Фильтр синонимов токенов Elasticsearch с настраиваемыми сопоставлениями слов

Elasticsearch поддерживает настраиваемое сопоставление синонимов, если WordNet не соответствует вашим потребностям. Мы настраиваем фильтр токенов синонимов так, чтобы он сопоставлял ключевое слово «fruit» со списком ключевых слов — «banana», «pineapple», «date», «fig», «olive» и «citrus». Следовательно, поиск по ключевому слову «banana» будет соответствовать любым записям с «fruit».

Отправьте этот запрос PUT, чтобы создать новый индекс и настраиваемый анализатор с фильтром токенов «synonym_filter».

После копирования данных во вновь созданный индекс отправьте этот запрос POST для поиска по ключевому слову «banana». Затем вы увидите, что результат содержит все записи, соответствующие ключевому слову «fruit».

Результат:

Примеры полнотекстовых запросов

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

Если помните, наша таблица TestTable содержит определения технологий, языков программирования, в общем, определений, связанных со сферой IT. Допустим, что мы хотим получить все записи, где есть упоминание о компании Microsoft, для этого мы пишем полнотекстовый запрос с ключевым словом CONTAINS, например

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

Как видим, ранг проставлен и по нему отсортированы строки. Сам алгоритм ранжирования, как и более подробную информацию о полнотекстовом поиске, можно найти в электронной документации по SQL Server.

Заметка! Еще больше статей по Microsoft SQL Server и языку T—SQL Вы можете найти в специальном сборнике статей – Сборник статей для изучения Microsoft SQL Server.

На этом предлагаю завершить, надеюсь, все было понятно, удачи!

Теги