Полнотекстовый поиск в 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», но он явно не хочет получить все объявления, где встречается слово «куплю», так как это слово встречается почти в каждом объявлении базы. В большинстве случаев стандартного списка стоп-слов бывает достаточно для обеспечения качественного поиска, но в случае необходимости разработчик может создать свой собственный список шумовых слов.

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