Full-Text Search - полнотекстовый поиск в MS SQL Server

И начнем мы, конечно же, с рассмотрения основ полнотекстового поиска, т.е. что это такое и для чего он вообще нужен.

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

Полнотекстовый поиск – это поиск слов или фраз в текстовых данных. Обычно такой вид поиска используется для поиска текста в большом объёме данных, например, таблица с миллионом и более строк, так как он значительно быстрей обычного поиска, который можно осуществить, используя конструкцию LIKE.

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

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

Возможности полнотекстового поиска в MS SQL Server

• В полнотекстовом поиске SQL сервера можно осуществлять поиск не только по отдельным словам или фразам, но и по префиксным выражениям, например, задать текст начала слова или фразы;
• Также можно искать слова по словоформам, например, различные формы глаголов или существительные в единственном и во множественном числе, т.е. по производным выражениям;
• Можно построить запрос так чтобы найти слова или фразы, находящиеся рядом с другими словами или фразами, т.е. выражения с учетом расположения;
• Есть возможность искать синонимические формы конкретного слова (тезаурус) т.е. например, если в тезаурусе определено что «Автомобиль» и «Машина» это синонимы, то при поиске слова «Автомобиль» в результирующий набор войдут и строки содержащие слово «Машина»;
• В запросе можно указывать слова или фразы с взвешенными значениями, например, если в запросе указано несколько слов или фраз, то им можно присвоить важность от 0,0 до 1,0 (1,0 означает что это самое важное слово или фраза);
• Для того чтобы не учитывать в поиске некоторые слова можно использовать «список стоп-слов» т.е. по словам, включенным в этот список, поиск выполняться не будет.

Подготовка к реализации полнотекстового поиска в MS SQL Server

Перед тем как приступать к созданию полнотекстового поиска, необходимо знать несколько важных моментов:

• Для реализации полнотекстового поиска компонент Full-Text Search (Полнотекстовый поиск) должен быть установлен;
• У таблицы может быть только один полнотекстовый индекс;
• Чтобы создать полнотекстовый индекс, таблица должна содержать один уникальный индекс, который включает один столбец и не допускает значений NULL. Рекомендовано использовать уникальный кластеризованный индекс (или просто первичный ключ) первый столбец которого должен иметь целочисленный тип данных;
• Полнотекстовый индекс можно создавать на столбцах с типом данных: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary или varbinary(max);
• Для того чтобы создать полнотекстовый индекс сначала необходимо создать полнотекстовый каталог. Начиная с SQL Server 2008 полнотекстовый каталог это логическое понятие, обозначающее группу полнотекстовых индексов, т.е. является виртуальным объектом и не входит в файловую группу (есть способ создания полнотекстового индекса, используя «Мастер», при котором каталог можно создать одновременно с индексом, этот способ мы будем рассматривать чуть ниже).

Примечание! Реализовывать полнотекстовый поиск я буду на примере версии SQL Server 2008 R2. Также подразумевается, что компонент Full-Text Search у Вас уже установлен, если нет, то установите его путем добавления соответствующего компонента через «Центр установки SQL Server» т.е. поставьте соответствующую галочку.

В примерах ниже в качестве инструмента создания и управления полнотекстовыми каталогами и индексами я буду использовать SQL Server Management Studio.

Исходные данные для создания полнотекстового поиска

Допустим, что у нас есть база данных TestBase, а в ней есть таблица TestTable в которой всего два поля первое (id) это первичный ключ, а второе (textdata) это текстовые данные по которым мы и будем осуществлять полнотекстовый поиск.

CREATE TABLE TestTable(
	id int IDENTITY(1,1) NOT NULL,
	textdata varchar(500) NULL,
 CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (id ASC)
)

Для примера она будет содержать следующие данные

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

Для создания полнотекстового каталога как впрочем, и индекса можно использовать или графический интерфейс SSMS или инструкций T-SQL, мы с Вами разберем оба способа.

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

CREATE FULLTEXT CATALOG TestCatalog
	WITH ACCENT_SENSITIVITY = ON
	AS DEFAULT
	AUTHORIZATION dbo
GO

Где,

• CREATE FULLTEXT CATALOG – команда создания полнотекстового каталога;
• TestCatalog – имя нашего полнотекстового каталога;
• WITH ACCENT_SENSITIVITY {ON|OFF} – опция указывает, будет ли полнотекстовый каталог учитывать диакритические знаки для полнотекстового индексирования. По умолчанию ON;
• AS DEFAULT – опция, для того чтобы указать, что каталог является каталогом по умолчанию. В случае создания полнотекстового индекса без явного указания каталога используется каталог по умолчанию;
• AUTHORIZATION dbo - устанавливает владельца полнотекстового каталога, им может быть пользователь или роль базы данных. В данном случае мы указали роль dbo.

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

Точно такой же полнотекстовый каталог можно создать и в графическом интерфейсе Management Studio. Для этого открываем базу данных, переходим в папку Хранилище ->Полнотекстовые каталоги, щелкаем правой кнопкой мыши по данному пункту и выбираем «Создать полнотекстовый каталог»

Откроется окно создания каталога, где мы указываем название каталога и его опции

Изменение и удаление полнотекстового каталога в SQL Server

Для изменения опций каталога можно использовать инструкцию ALTER FULLTEXT CATALOG, например, давайте сделаем так, чтобы наш каталог перестал учитывать диакритические знаки, для этого пишем SQL инструкцию, которая перестроит наш каталог с новой опцией

  ALTER FULLTEXT CATALOG TestCatalog
  REBUILD WITH ACCENT_SENSITIVITY=OFF 
  GO

Для того чтобы удалить каталог можно использовать инструкцию T-SQL, например

  DROP FULLTEXT CATALOG TestCatalog

Все это можно было сделать и в графическом интерфейсе Management Studio (для изменения параметров каталога «Свойства», для удаления «Удалить»)

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

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

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

Для того чтобы создать полнотекстовый индекс можно написать следующую SQL инструкцию

CREATE FULLTEXT INDEX ON TestTable(textdata)
	KEY INDEX PK_TestTable ON (TestCatalog) 
	WITH (CHANGE_TRACKING AUTO)
GO

Где,

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

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

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

Изменение и удаление полнотекстового индекса

В случае необходимости можно изменить параметры полнотекстового индекса. Давайте в качестве примера, изменим способ отслеживания изменений с автоматического на ручной. Для изменения в графическом интерфейсе можно использовать окно «Свойства полнотекстового каталога -> Таблицы или представления», которое мы использовали при создании полнотекстового индекса.

Или можно написать следующий код

 ALTER FULLTEXT INDEX ON TestTable 
	SET CHANGE_TRACKING = MANUAL

Для того чтобы удалить полнотекстовый индекс достаточно просто удалить таблицу из списка объектов связанных с полнотекстовым каталогом в том же окне «Свойства полнотекстового каталога -> Таблицы или представления»

Или написать код T-SQL

  DROP FULLTEXT INDEX ON TestTable

Создание полнотекстового каталога и индекса с помощью мастера

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

Примечание! Перед этим я удалил и каталог, и индекс которые мы создавали в предыдущих примерах.

В итоге запустится мастер полнотекстового индексирования SQL Server

Далее выбираем уникальный индекс

Затем столбец, который будет включен в полнотекстовый индекс

Потом необходимо выбрать способ отслеживания изменений

Указываем название каталога и его опции, для того чтобы его создать, так как предполагается, что у нас каталога нет, если бы он был, то мы могли бы его выбрать

Здесь мы можем настроить расписание заполнения полнотекстового каталога

Для создания каталога и индекса осталось нажать «Готово»

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

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

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

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

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

 select * from TestTable 
			where CONTAINS (textdata, '"Microsoft"')

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

 SELECT  Table1.id AS ID, 
		RowRank.Rank as [RANK], Table1.textdata as [TEXTDATA] 
 FROM TestTable Table1
 INNER JOIN CONTAINSTABLE(TestTable, textdata, '"Microsoft"') as RowRank 
						on Table1.id=RowRank.[KEY]
 ORDER BY RowRank.RANK DESC

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

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