Полнотекстовый поиск в MS SQL 2008: Часть 2. Написание полнотекстовых запросов

 

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

   Осуществлять полнотекстовые запросы можно двумя базовыми способами. С помощью предикатов CONTAINS и FREETEXT или функций CONTAINSTABLE и FREETEXTTABLE.

 

CONTAINS и CONTAINSTABLE

   Поиск при помощи предиката CONTAINS осуществляется следующим образом:

SELECT * FROM dbo.ProductSearchView WHERE CONTAINS(Description, 'bike');

   Первым параметром в предикате указываются колонки представления, по которым необходимо производить поиск. Сюда необходимо вводить либо имена колонок, либо *. Звездочка будет означать, что поиск будет осуществляться по всем колонкам, которые поддерживают полнотекстовый поиск.

   Вторым параметром идет непосредственно поисковый запрос. В примере выше – это поиск точного вхождения слова «bike». Однако, поисковый механизм очень гибкий и кроме поиска точного вхождения нам доступен ряд других типов поиска:

   1) Поиск по префиксному выражению. Такой запрос имеет следующий вид:

SELECT * FROM dbo.ProductSearchView WHERE CONTAINS(Description, '"bik*"');

   После слова запроса ставится звездочка, и все выражение обязательно берется в двойные кавычки. В результате данного запроса будут выведены все результаты, в которых есть слово, которое начинается с выражения «bik».

   2) Поиск синтаксических форм слов. Это один из самых мощных режимов поиска. Для работы в этом режиме используется ключевое слово FORMSOF и два параметра поиска: INFLECTIONAL и THESAURUS.

SELECT * FROM dbo.ProductSearchView WHERE CONTAINS(Description, 'FORMSOF(INFLECTIONAL, bike)');

   В данном запросе будут выбраны все строки, которые содержат слово «bike» или какую-либо форму этого слова, например «biking». Параметр INFLECTIONAL задает поиск по синтаксическим формам слова.

   Если вместо INFLECTIONAL подставить параметр THESAURUS, то будут выбраны строки, которые содержат данное слово, либо его синоним из словаря для конкретного языка.

   3) Поиск выражения с учетом расположения. При поиске разработчик может ограничить расстояние, на котором могут находиться в тексте искомые слова запроса. Это делается с помощью ключевого слова NEAR либо оператора ~.

SELECT * FROM dbo.ProductSearchView WHERE CONTAINS(Description, 'bike NEAR quick')

   В данном запросе будут выбраны строки, в которых слово «bike» находится в тексте недалеко, по отношению к слову «quick». Запрос может содержать любую цепочку слов, соединенных оператором NEAR.

   4) Поиск с учетом веса слов. В данном режиме каждому слову в поисковом запросе можно присвоить коэффициент веса (от 0 до 1). Механизм полнотекстового поиска будет строить результирующую выборку данных на основе вхождения слов с наибольшими коэффициентами значимости.

   Запрос будет выглядеть следующим образом:

SELECT * FROM dbo.ProductSearchView WHERE CONTAINS(Description, 'ISABOUT(bike weight(0.8), quick weight(0.5))');

   После ключевого слова ISABOUT в скобках через запятую указывается список слов запроса. После каждого слова идет ключевое слово weight и коэффициент веса.

   5) Комбинированный режим поиска. В этом режиме разработчик может комбинировать при помощи логических операторов AND, AND NOT, или OR различные механизмы поиска, перечисленные выше. Такой режим позволяет максимально гибко создавать высокоэффективные поисковые механизмы, ориентируясь на задачи конкретной системы или приложения.

   Этими пять режимов составляют ядро полнотекстового поиска с использованием предиката CONTAINS.

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

SELECT * FROM CONTAINSTABLE(dbo.ProductSearchView,

Description, 'ISABOUT(bike weight(0.8), quick weight(0.5))', 10) as result

   Первым параметром функции идет имя таблицы или представления, по которому производится поиск.

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

   Третий параметр – поисковый запрос (формируется таким же образом, как и в предикате CONTAINS).

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

   Данный запрос вернет следующий результат:

KEY	RANK
1201	87
690	75
554	60
209	55
249	55
746	37
64	29
88	29
168	29
320	29

   Столбец KEY – это первичный ключ записи в представлении ProductSearchView, а RANK – это коэффициент релевантности данных. Для каждого результата механизм SQL Server устанавливает коэффициент, который определяет, насколько данный результат соответствует поисковому запросу. Отсортировав результаты по полю RANK, разработчик сможет вернуть в приложение наиболее точные результаты поиска. При задании в функции CONTAINSTABLE четвертого параметра, результаты сортируются по полю RANK автоматически.

 

FREETEXT и FREETEXTABLE

   Данный тип поиска является наиболее интеллектуальным во всем механизме полнотекстового поиска SQL Server. Поиск при помощи CONTAINS основан на дословном поиске результатов. Предикат FREETEXT не просто ищет конкретные слова и формы в тексте, а проводит интеллектуальный анализ поискового запроса и ищет подходящие строки по смыслу запроса.

   Приведем пример такого запроса:

SELECT * FROM dbo.ProductSearchView WHERE FREETEXT(Description, 'provides a light stiff ride')

   Данный запрос вернет 34 результата. В случае, если переписать данный запрос с предикатом CONTAINS, то получим лишь один результат.

   Функция FREETEXTTABLE (аналогично CONTAINSTABLE), отличается от предиката FREETEXT формой вызова и наличие ранга релевантности результатов:

SELECT * FROM FREETEXTTABLE(dbo.ProductSearchView, Description, 'provides a light stiff ride', 40) as result

 

   Выводы

   В данной статье мы рассмотрели примеры написания базовых запросов для механизма полнотекстового поиска в MS SQL 2008. Были приведены примеры использования основных предикатов. Использование предикатов в поисковых запросах – очень гибкий механизм, и вы сможете легко организовывать эффективный поиск в своих программах, научившись правильно подбирать и группировать предикаты в своих поисковых запросах.

Полнотекстовый поиск в MS SQL 2008: Часть 1. Подготовка базы

 

    Одна из наиболее мощных функций, которые предоставляются Microsoft SQL Server 2008 – это механизм полнотекстового поиска. В большинстве случаев, приложениям, которые используют базы данных, требуется механизм поиска текста внутри базы (например, поиск на веб-сайтах). Основные требования, которые предъявляются к такому механизму – это скорость, гибкость и простота использования. И, конечно, банальный поиск в запросе со словом LIKE следующего вида:

SELECT * FROM Products WHERE Description LIKE ‘…..’;

никак не может удовлетворить такие требования.

   К счастью Microsoft SQL Server предоставляет высокоуровневый, интеллектуальный механизм полнотекстового поиска, который позволяет разработчикам организовать высокоэффективный поиск по базе данных, затрачивая при этом минимальное количество ресурсов. Данная опция является достаточно распространенной единицей SQL Server, поэтому доступна во всех версиях Microsoft SQL Server кроме Express. Я буду использовать SQL Server 2008 Developer.

   Стоит отметить, что механизм полнотекстового поиска в SQL Server 2008 значительно эволюционировал, по сравнению с предыдущей версией SQL Server 2005. Были значительно улучшены поисковые алгоритмы, а также была добавлена поддержка 28 новых языков. И теперь в SQL Server 2008 разработчики могут писать полнотекстовые запросы поиска на 33 различных языках.

   В этой статье мы познакомимся с функциями, которые предоставляет полнотекстовый поиск в SQL Server 2008, научимся создавать полнотекстовые индексы в своих базах данных.

   Для примера мы будем использовать базу данных AdventureWorks. Поиск будет осуществляться по таблице ProductDescription. Для настройки механизмов полнотекстового поиска необходимо выполнить следующую последовательность операций:

   1) Открыть программу SQL Server Management Studio и подключится к удаленному серверу;

   2) Для оптимизации производительности рекомендуется создавать механизм полнотекстового поиска не на основной таблице, а на представлении этой таблицы. Поэтому мы создадим представление dbo. ProductSearchView при помощи следующего скрипта:

USE [AdventureWorks] GO

CREATE VIEW [dbo].[ProductSearchView] WITH SCHEMABINDING

AS SELECT ProductDescriptionID, Description FROM Production.ProductDescription

GO

   Мы создали представление ProductSearchView на основе таблицы ProductDescription, которое включает в себя два столбца:

• ProductDescriptionID – уникальный идентификатор продукта;
• Description – текстовое поле с описанием продукта, по содержимому которого будет производится полнотекстовый поиск.

   Заметьте, что обязательным является параметр WITH SCHEMABINDING, если его не указать при создании представления, то в дальнейшем SQL Server не разрешит нам создавать индексы внутри этого представления.

   3) Следующий этап – создание уникального кластерного индекса в ранее созданном представлении. Данный индекс будет служить основой для полнотекстового поиска. Для этого необходимо выполнить следующий скрипт:

USE [AdventureWorks] GO

CREATE UNIQUE CLUSTERED INDEX [IDX_SEARCH] ON [dbo].[ProductSearchView]

( [ProductDescriptionID] ASC ) ON [PRIMARY] GO

   Мы создали уникальный кластерный индекс с именем IDX_SEARCH внутри представления ProductSearchView. Данный индекс строится на основе колонки ProductDescriptionID, которая может уникально идентифицировать все строки представления.

   4) Следующая составляющая полнотекстового поиска – полнотекстовый каталог. Данный каталог создается на уровне базы данных и представляет группу полнотекстовых индексов. Создадим такой каталог следующим скриптом:

USE AdventureWorks GO

CREATE FULLTEXT CATALOG ftCatalog AS Default

   Наш каталог будет называться – ftCatalog.

   5) И, наконец, последний этап – непосредственное создание полнотекстового индекса. Для начала приведем общий формат команды:

CREATE FULLTEXT INDEX ON имя_таблицы_или_представления

   [ ( { имя_колонки [ TYPE COLUMN название_типа ]  [ LANGUAGE код_языка ] } [ ,...n]  ) ]

    KEY INDEX имя_индекса  [ ON имя_полнотекстового_каталога ]

    [ WITH [ ( ] перечень_опций [ ,...n] [ ) ] ] [;]

   В нашем случае данная команда будет выглядеть следующим образом:

USE AdventureWorks GO

CREATE FULLTEXT INDEX ON dbo.ProductSearchView (Description LANGUAGE 1033)

KEY INDEX IDX_SEARCH ON ftCatalog

WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM) GO

      Разберем параметры команды более детально:

• ON dbo.ProductSearchView – здесь указывается для какого объекта создается полнотекстовый индекс(в данном случае это наше представление);
• (Description LANGUAGE 1033) – в скобках идет перечисление всех полей, по которым будет производится поиск. Для каждого поля указывается код языка, в зависимости от его содержимого. Для каждого поля может быть указан только один язык поиска. Код 1033 – это английский язык. Чтобы узнать код какого-либо языка необходимо запросить системное представление sys.syslanguages;
• KEY INDEX IDX_SEARCH – здесь указывается имя уникального кластерного индекса, который мы создали в пункте 3;
• ON ftCatalog – здесь необходимо указать имя полнотекстового каталога, который мы создали в пункте 4;
• WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM) – последним параметром идут опции полнотекстового индекса.
• CHANGE_TRACKING = AUTO – это значит, что все изменения в основной таблице будут автоматически переноситься в полнотекстовый индекс.
• STOPLIST = SYSTEM – обозначает, что при поиске будет использоваться системный список стоп-слов. Стоп-слова – это «шумовые» слова, которые не являются ключевыми в поисковом запросе и отрицательно влияют на качество результатов поиска. Например, при поиске по базе объявлений, в поисковом запросе «куплю iPhone» слово «куплю» будет являться шумовым словом. При таком запросе пользователь хочет, чтобы ему вернулись объявления со словами «куплю iPhone» или «iPhone», но он явно не хочет получить все объявления, где встречается слово «куплю», так как это слово встречается почти в каждом объявлении базы. В большинстве случаев стандартного списка стоп-слов бывает достаточно для обеспечения качественного поиска, но в случае необходимости разработчик может создать свой собственный список шумовых слов.

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