Microsoft SQL Server
Full-Text Search

Настройка полнотекстового поиска

Опубликовано: 07 мар 05
Рейтинг:

Автор: Программизд 02
Прислал: Программизд 02

MS SQL Server должен быть установлен с опцией "full-text search". В версии MSDE опция "full-text search" недоступна. Необходимые операции в примере будут осуществляться с помощью команд T-SQL. Так же возможно выполнить их с помощью wizzard'a или через соответсвующие пункты меню.

Использование фильтров

SQL Server позволяет также индексировать данные в формате .doc, .xls, .ppt, .txt, .html с помощью фильтров. Для этих целей необходимо задать дополнительный столбец, содержащий обозначение формата индексируемого столбца. Не пытайтесь задать формат данных, хранящихся в поле TEXT или NTEXT. Формат данных можно задавать только для типа IMAGE. В BOL сказано

BOL
Formatted text strings, such as Microsoft® Word™ document files or HTML files, cannot be stored in character string or Unicode columns because many of the bytes in these files contain data structures that do not form valid characters.

Many sites store this type of data in image columns, because image columns do not require that each byte form a valid character. SQL Server 2000 introduces the ability to perform full-text searches against these types of data stored in image columns. SQL Server 2000 supplies filters that allow it to extract the textual data from Microsoft Office™ files (.doc, .xls, and .ppt files), text files (.txt files), and HTML files (.htm files). When you design the table, in addition to the image column that holds the data, you include a binding column to hold the file extension for the format of data stored in the image column.


В общем и целом это утверждение верно за исключением случая с форматом HTML. HTML текст может быть помещен в поле TEXT/NTEXT. Видимо разработчики MS SQL Server не стали реализовывать фичу фильтра для полей TEXT/NTEXT ради единственного случая с форматом HTML, посему имеет место требование - фильтруемые данные могут хранится только в поле IMAGE. Это влечет за собой некоторые издержки на преобразование текстовых данных при их записи и извлечении из поля IMAGE.

/*
Используем базу tempdb для отработки примера
*/
use tempdb
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DOCS]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DOCS]
GO

/*
Создаем таблицу DOCS
- Поле COMMENT будет содержать "чистый" текст для демонстрации примера
полнотекстового поиска по полю ntext
- Поле CONTENTS содержит данные в формате HTML 
- Поле FMT задает тип формата данных, по умолчанию 'html'
- Поле timestamp необходимо для инкрементального обновления  
индекса полнотекстового поиска
*/
CREATE TABLE [dbo].[DOCS] (
	[ID] [int] NOT NULL CONSTRAINT PK_DOCS PRIMARY KEY identity,
	[COMMENT] [ntext] NOT NULL ,
	[CONTENTS] [image] NOT NULL ,
	[FMT] [varchar](10) default 'html',
	[timestamp] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/*
Включаем опцию полнотекстового поиска
*/
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO

/*
Создаем каталог полнотекстового поиска для таблицы DOCS
*/
if not exists (select * from dbo.sysfulltextcatalogs where name = N'testcatalog')
exec sp_fulltext_catalog N'testcatalog', N'create'
GO

exec sp_fulltext_table N'[dbo].[DOCS]', N'create', N'testcatalog', N'PK_DOCS'
GO

/*
Добавляем колонку COMMENT с типом NTEXT в каталог
*/
exec sp_fulltext_column N'[dbo].[DOCS]', N'COMMENT', N'add', 0 /* neutral */
GO

/*
Добавляем колонку CONTENTS с типом IMAGE в каталог
FMT - имя колонки, задающей формат данных в колонке CONTENTS  
*/
exec sp_fulltext_column N'[dbo].[DOCS]', N'CONTENTS', N'add', 0 /* neutral */, N'FMT'
GO

/*
Активируем полнотекстовый поиск для таблицы DOCS
*/
exec sp_fulltext_table N'[dbo].[DOCS]', N'activate'
GO

/*
Заполняем таблицу данными

Словами для демонстрации поиска будут служить Cat и Dog. Для проверки того,
что фильтр по HTML действительно работает, "засорим" эти слова HTML тэгами 
таким образом

C<b>at</b> and D<b>o</b>g
*/
insert into docs (comment, contents) values
(
N'This documents contains both the words Cat and Dog', 
'
<html>
<body>
Each method differs in the amount of data that is propagated 
to the Subscriber. For example, MCALL will pass 
in values only for the columns that are actually 
affected by the update, and a bitmask representing 
the changed columns and XCALL will pass in all columns 
(whether affected by an update or not) and all the old 
data values for each column. 
C<b>at</b> and D<b>o</b>g
This allows flexibility 
to application developers with diverse requirements. 
When using XCALL, the before image values for text 
and image columns are expected to be NULL.
</body>
</html>
'
)
go

insert into docs (comment, contents) values
(
N'This documents contains only the word Cat', 
'
<html>
<body>
Each method differs in the amount of data that is propagated 
to the Subscriber. For example, MCALL will pass 
in values only for the columns that are actually 
affected by the update, and a bitmask representing 
the changed columns and XCALL will pass in all columns 
(whether affected by an update or not) and all the old 
data values for each column. 
C<b>at</b> 
This allows flexibility 
to application developers with diverse requirements. 
When using XCALL, the before image values for text 
and image columns are expected to be NULL.
</body>
</html>
'
)
go

insert into docs (comment, contents) values
(
N'This documents contains only the word Dog', 
'
<html>
<body>
Each method differs in the amount of data that is propagated 
to the Subscriber. For example, MCALL will pass 
in values only for the columns that are actually 
affected by the update, and a bitmask representing 
the changed columns and XCALL will pass in all columns 
(whether affected by an update or not) and all the old 
data values for each column. 
D<b>o</b>g 
This allows flexibility 
to application developers with diverse requirements. 
When using XCALL, the before image values for text 
and image columns are expected to be NULL.
</body>
</html>
'
)
go

/*
Запускаем полное обновление индекса полнотекстового поиска
*/
exec sp_fulltext_catalog 'testcatalog', 'start_full'
GO

/*
Ждём некоторое время пока обновление закончится.
*/
WAITFOR DELAY '00:00:30'
GO

/*
Тестируем поиск по полю comment типа NTEXT 
*/
select id from docs
where contains (comment, ' "Cat and Dog" ')
go

/*
id          
----------- 
1

(1 row(s) affected)
*/

select id from docs
where contains (comment, ' "Cat" ')
go

/*
id          
----------- 
2
1

(2 row(s) affected)
*/

select id from docs
where contains (comment, ' "Dog" ')
go

/*
id          
----------- 
3
1

(2 row(s) affected)
*/

/*
Тестируем поиск по полю contents IMAGE в формате HTML  
*/
select id from docs
where contains (contents, ' "Cat and Dog" ')
go

/*
id          
----------- 
1

(1 row(s) affected)
*/

select id from docs
where contains (contents, ' "Cat" ')
go

/*
id          
----------- 
2
1

(2 row(s) affected)
*/

select id from docs
where contains (contents, ' "Dog" ')
go

/*
id          
----------- 
3
1

(2 row(s) affected)
*/

/*
Добавляем новые данные в таблицу, чтобы протестировать 
икрементальное обновление индекса.
*/
insert into docs (comment, contents) values
(
N'This documents contains only the word Mouse', 
'
<html>
<body>
Each method differs in the amount of data that is propagated 
to the Subscriber. For example, MCALL will pass 
in values only for the columns that are actually 
affected by the update, and a bitmask representing 
the changed columns and XCALL will pass in all columns 
(whether affected by an update or not) and all the old 
data values for each column. 
M<b>o</b>use 
This allows flexibility 
to application developers with diverse requirements. 
When using XCALL, the before image values for text 
and image columns are expected to be NULL.
</body>
</html>
'
)
go

/*
Запускаем инкрементальное обновление индекса полнотекстового поиска
*/
exec sp_fulltext_catalog 'testcatalog', 'start_incremental'
GO

/*
Ждём некоторое время пока обновление закончится.
*/
WAITFOR DELAY '00:00:30'
GO

/*
Тестируем, что новые данные были проидексированы и поиск их находит
*/

select id from docs
where contains (contents, ' "Mouse" ')
go

/*
id          
----------- 
4

(1 row(s) affected)
*/

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

Можно выделить 4 стратегии.

1) Каждый раз полное обновление (full population)

Приемлема только для небольшого количества данных.

exec sp_fulltext_catalog 'testcatalog', 'start_full'

2) Полное обновление плюс периодические инкрементальные обновления (incremental population)

-- first time
exec sp_fulltext_catalog 'testcatalog', 'start_full'
-- periodically
exec sp_fulltext_catalog 'testcatalog', 'start_incremental'

По моим наблюдениям, при больших объемах данных incremetal population занимает практически такое же время как full population. Даже если после выполненного full population не вносились новые данные, запустить incremetal population, то его выполнение занимает достаточно долгое время.

Full -text search
Проблема с Incremental Population заключается в том, что он, строго говоря не совсем incremental - SQL Server снова сканирует ВСЮ таблицу проверяя timestamp у каждой записи - если запись изменилась, происходит обновление данных в full-text индексе. Как он удаленные записи обрабатывает - не знаю. Индексирование поля timestamp процесс не ускоряет - наверное из-за необходимости обработки удаленных записей.

Т.е. в реальной жизни - на больших таблицах, да еще более или менее часто обновляемых, использовать incremetal population практически бессмысленно. Для редко обновляемых данных им пользоваться еще можно.


Incremental population of the full-text catalog
Создал full-text каталог, выполнил Full population.
Таблица имеет timestamp поле, даже индекс создал по этому полю.
Ничего не меняю - запускаю Incremental population - по идее все должно завершиться мгновенно, а на самом деле процессор занят на 100% и выполнение занимает столько же времени как и в случае full population - вопрос - а чем же он занят??? Изменений же вообще не было ...


3) Отслеживание изменений (change tracking) плюс постоянное обновление индекса в фоновом режиме (update index in background)

EXEC sp_fulltext_table N'[dbo].[DOCS]', N'start_change_tracking'
EXEC sp_fulltext_table N'[dbo].[DOCS]', N'start_background_updateindex'

Преимущество этой стратегии в том, что всё делается автоматически. Нет необходимости самому следить за обновлением индекса. Однако по моим наблюдениям включенная опция (update index in background) достаточно сильно потребляет ресурсы. Заметил, что когда она включена идёт постоянное интенсивное чтение (а может и запись) с жеского диска, хотя никакие новые данные не вносятся. При выключение этой опции оно перестает.

4) Отслеживание изменений (change tracking) плюс периодический запуск обновления идекса на основе зафиксированных измениний (update index)

-- first time
EXEC sp_fulltext_table N'[dbo].[DOCS]', N'start_change_tracking'
-- periodically
EXEC sp_fulltext_table N'[dbo].[DOCS]', N'update_index'

Эту стратегию я бы назвал как наименее требовательную к ресурсам. Дополнительная нагрузка идет только в момент внесения/изменения данных. Затем можно запустить обновления идекса на основе зафиксированных измениний. Такой запуск можно за'schedule'ить.

Проверить текущее состояние каталога можно с помощью следующего скрипта:

  SELECT CASE FulltextCatalogProperty('testcatalog',  'PopulateStatus')
  WHEN 0 THEN 'Idle'
  WHEN 1 THEN 'Full population in progress'
  WHEN 2 THEN 'Paused'
  WHEN 3 THEN 'Throttled'
  WHEN 4 THEN 'Recovering'
  WHEN 5 THEN 'Shutdown'
  WHEN 6 THEN 'Incremental population in progress'
  WHEN 7 THEN 'Building index'
  WHEN 8 THEN 'Disk is full. Paused'
  WHEN 9 THEN 'Change tracking'
  ELSE 'Unknown'
  END

Пример функции для преобразования двоичных данных из поля IMAGE в текст на ASP JScript:

function BinaryToString(BinaryField, AuxStream)
{
  if(BinaryField.Type != 128 /*adBinary*/ &&
     BinaryField.Type != 204 /*adVarBinary*/ &&
     BinaryField.Type != 205 /*adLongVarBinary*/ 
    ) return BinaryField;
  
  var result = "";

  AuxStream.Open();
  AuxStream.Type = 1;
  AuxStream.Write(BinaryField);
  AuxStream.Position = 0;
  AuxStream.Type = 2;
  AuxStream.CharSet = "windows-1251";
  result = AuxStream.ReadText;
  AuxStream.Close();

  return result;
}

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Full-Text Search / Настройка полнотекстового поиска