Полнотекстовый поиск. Создание полнотекстового индекса.

Продолжение. См. пред.серии "Введение" и "Наполнение таблицы".

Подготовительные действия на этом закончены, можно переходить к созданию полнотекстовых индексов. При этом можно идти двумя путями: визардо-гуевым и Т-SQLным. Я буду придерживаться второго, потому что скрипты публиковать быстрее, чем скриншоты. Ознакомиться с первым можно в документации - https://msdn.microsoft.com/ru-ru/library/aa337082.aspx.

Для начала стоит создать полнотекстовый каталог. В 7.0 - 2005 полнотекстовый каталог был действительно каталогом файловой системы, куда клались полнотекстовые индексы. В 2008 полнотекстовые индексы хранятся внутри SQL Server, поэтому понятие полнотекстового каталога носит в нем абстрактный характер. Это просто логическое объединение полнотекстовых индексов.

if exists (select 1 from sys.fulltext_catalogs where name = 'Cat1') drop fulltext catalog Cat1

create fulltext catalog Cat1 with accent_sensitivity = off as default authorization dbo

Скрипт 1

Был создан полнотекстовый каталог по имени Cat1, владельцем которого является dbo. Аccent_sensitivity = off означает, что и и й, е и ё не будут различаться. По умолчанию эта установка берется из коллации базы, но, поскольку она у меня Cyrillic_General_CI_AS, нечувствительность к надстрочным знакам пришлось оговорить особо. Не то, чтобы это имело какое-то значение в последующей демонстрации, просто хотелось показать. В 2005-м в операторе create fulltext catalog можно было указать, в каком месте файловой системы его создать (опция IN PATH). Также внутри себя SQL Server создавал системные объекты, ссылавшиеся на файлы каталога, и можно было ему сказать, на какой файл-группе их хранить (опция ON FILEGROUP), чтобы все полнотекстовое хозяйство бэкапилось вместе с этой файл-группой. В sys.database_files файлы этой файл-группы виделись с type_desc = "fulltext". Прикольно выразился Robert Vieira в своей книжке "Professional SQL Server 2005 Programming": For the vast, vast majority of installations, you're going to want to stick with the primary filegroup (which is default). So, in short, now that I've told you about this option, forget about it. Забыть стоит еще и потому, что в 2008-м обе этих опции лишены смысла. Поскольку полнотекстовые индексы теперь хранятся не в файловой системе, а в базе данных, каждому полнотекстовому индексу, как и обычному, можно сказать, на какой файл-группе он должен располагаться. Опции IN PATH и ON FILEGROUP в синтаксисе остались, но не имеют эффекта. В связи с этим, если скриптовать из SSMS 2008 полнотекстовый каталог на 2005-м, они в скрипте не отразятся, что может быть не вполне ожидаемым результатом для 2005-го.

Ниже создается полнотекстовый индекс по полю Content таблицы TestFTS. Полнотекстовый индекс на таблицу может быть только один, поэтому отдельное имя ему не требуется, а если нужно проиндексировать несколько полей, их всех нужно перечислить – см. https://msdn.microsoft.com/ru-ru/library/ms187317.aspx. Поле Content содержит не текст, а бинарное содержание файла, которое требуется распарсить при помощи фильтра. Применение нужного фильтра производится на основе расширения файла, вынесенного в колонку Type.

if exists(select 1 from sys.fulltext_indexes where object_id = object_id('dbo.TestFTS')) drop fulltext index on TestFTS

create fulltext index on TestFTS (Content type column Type language 1049)

key index ixId

on (Cat1, filegroup FTS)

with change_tracking = auto, stoplist = system

Скрипт 2

Language 1049 означает, что к распарсенному результату будут применяться брейкер и стеммер (см. "Введение") для русского языка. Русский полнотекст поддерживается в SQL Server 2008, что называется, из коробки; никаких дополнительных действий для включения поддержки предпринимать не требуется – см.

select * from sys.fulltext_languages order by name

Скрипт 3

Полнотекстовый индекс создается в каталоге Cat1 (см. Скрипт 1), в файл-группе FTS (см. "Наполнение таблицы"\Скрипт 2).

Если при создании полнотекстового индекса не указывать язык, будет взят язык по умолчанию. Посмотреть (и при желании поменять его) можно через sp_configure:

exec sp_configure 'default full-text language'

name minimum maximum config_value run_value

--------------- --------- ----------- ----------- ------------ -----------

default full-text language 0 2147483647 1033 1033

Скрипт 4

Для полнотекстового индекса необходим обычный уникальный индекс, идентифицирующий строки таблицы. В его качестве подсовывается созданный ранее ixId – см. "Наполнение таблицы"\Скрипт 6. В предыдущем посте, говоря о структуре полнотекстового индекса как об инверсивном индексе, я для простоты представил более идеалистическую картину. Во-первых, я не стал упоминать г) порядковый номер колонки, т.к. полнотекстовый индекс может строиться по нескольким полям таблицы. Во-вторых, в качестве а) используется не РК, и даже не key index, а свой идентификатор записи в таблице под названием DocID. Он является тупо целочисленным. Отсюда следуют два неприятных вывода. Во-первых, количество строк в полнотекстно-индексированной таблице не может превышать максимального целого в пределах отведенной на DocID длины. В 2005-м это было 4 байта, т.е. при кол-ве строк таблицы > 2**32 полнотекстовый поиск загибался. В 2008-м, слава богу, сделали bigint. Во-вторых, требуется перетолмачить ключи уникального индекса, указанного в качестве key index при создании полнотекстового, в DocID. Если key index  был по целочисленному полю, перетолмачивать ничего не требовалось, она брала его в качестве DocID; в противном случае создавалась таблица соответствия key index <-> DocID, что, понятно, не прибавляло производительности. В 2005-м ее затащили из FTS внутрь SQL Server, что преподносилось как важное завоевание трудящихся. В 2008-м FTS стал iFTSом, и я, честно говоря, ждал, что под это дело DocID отождествят с key index, а дурацкую перекодировку уберут. Не тут-то было. Видимо, полнотекстовый поиск у нас теперь хоть и интегрированный, но все же не до такой степени. Чтобы в следующей версии было, куда интегрировать его еще глыбже. Посмотреть перекодировку можно при помощи хранимой процедуры sp_fulltext_keymappings.

declare @table_id int = object_id('dbo.TestFTS')

exec sp_fulltext_keymappings @table_id = @table_id

Скрипт 5

Блин, когда же они наконец догадаются сделать поддержку выражений в параметрах процедур?

рис.1

Key - это бинарное представление ключей нашего уникального индекса над таблицей по полю HierarchyID. На самом деле соответствие хранится во внутренней системной таблице, а эта процедура просто делает из нее выборку. Существует изрядно внутренних таблиц на тему полнотекста; их можно перечислить так:

select * from sys.internal_tables where name like '%fulltext%' or name like '%fts%' order by name

Скрипт 6

рис.2

Легко видеть, что все они сопровождаются ObjectID той пользовательской таблицы, к которой относятся. Например, вот здесь хранится перекодировка key index <-> DocID:

declare @s varchar(10) = str(object_id('dbo.TestFTS'))

exec ('select * from sys.fulltext_index_docidmap_' + @s)

Скрипт 7

Понятно, что поскольку таблица internal, выполнять его надо с DACa. Также по этой таблице можно убедиться, что длина DocID в 2008-м составляет 8 байт:

select * from sys.all_columns where name = 'docid' and object_id = object_id('sys.fulltext_index_docidmap_' + @s)

Скрипт 8

Посмотреть, как в общих чертах выглядит полнотекстовый индекс, можно по таблицам sys.ifts_comp_fragment_<object_id полнотекстно индексированной таблицы>_ля-ля-ля. Это фрагменты полнотекстового индекса.. Все существующие фрагменты перечислены в DMV sys.fulltext_index_fragments, где ля-ля-ля – колонка fragment_id. Для начала нужно написать вспомогательную функцию

if object_id('dbo.SwapBytes', 'FN') is not null drop function dbo.SwapBytes

go

create function dbo.SwapBytes(@x varbinary(max)) returns varbinary(max) as begin

declare @y varbinary(max) = cast('' as varbinary(max)), @i bigint = 0, @l bigint = len(@x), @b1 binary(1), @b2 binary(1)

while 1 = 1 begin

 set @i += 1

 if @i > @l break

 set @b1 = substring(@x, @i, 1)

 set @i += 1

 if @i > @l begin set @y += @b1; break end

 set @b2 = substring(@x, @i, 1)

 set @y += @b2 + @b1

end

return @y

end

Скрипт 9

так как в колонке Keyword они идут по принципу little endian: 0x00770069006E0064006F00770073, тогда как T-SQLная конвертация varbinary в nvarchar построена по принципу big endian:

select cast(N'windows' as varbinary(max))

---

0x770069006E0064006F0077007300

C помощью функции Скрипт 9 не составляет труда конвертнуть Keyword из varbinary в nvarchar:

declare @s varchar(10) = str(object_id('dbo.TestFTS'))

exec ('select cast(dbo.SwapBytes(keyword) as nvarchar(max)), colid, pid, docidmin, docidmax, dupseq, lv1cnt, complv1, complv2 from sys.ifts_comp_fragment_' + @s + '_336')

Скрипт 10

рис.3

Идейно то же самое можно получить при помощи DMF

select * from sys.dm_fts_index_keywords(db_id('TestFS'), object_id('TestFTS'))

для всей таблицы скопом и при помощи DMF

select * from sys.dm_fts_index_keywords_by_document(db_id('TestFS'), object_id('TestFTS'))

в разбивке по строкам. В вывод добавляется поле DocID, по которому можно связать слова со строками таблицы, пользуясь перекодировкой DocID <-> unique index в sp_fulltext_keymappings (см.Скрипт 5).

Потоки ползают по таблице, каждый по своим записям и строят такие куски полнотекстового индекса. Процесс консолидации кусков в единый индекс называется master merge. Он происходит автоматом через каждые 500 тыс. проиндексированных записей, либо принудительно по команде:

alter fulltext catalog Cat1 reorganize

Скрипт 11

Список каталогов можно посмотреть в DMV sys.fulltext_catalogs, а перечень фрагментов - в sys.fulltext_index_fragments. Если после Скрипта 10 взглянуть на fulltext_index_fragments, мы увидим там всего один фрагмент с совокупными значениями data_size и row_count. Поле Status может иметь следующие значения (https://msdn.microsoft.com/ru-ru/library/cc280700.aspx): 0 — фрагмент только что создан и еще не использован; 1 — используется для вставки во время заполнения или слияния полнотекстового индекса; 4 — закрыт и готов к запросу; 6 — используется для входа слияния и готов к запросу; 8 — помечен для удаления и не будет использоваться для запросов и как вход слияния. Состояние 4 или 6 означает, что фрагмент является частью логического полнотекстового индекса и к нему можно выполнять запросы; это запрашиваемый фрагмент. Состояние процесса слияния можно узнать как select fulltextcatalogproperty('Cat1', 'MergeStatus'). Здесь все бесхитростно (https://msdn.microsoft.com/ru-ru/library/ms190370.aspx): 0 = слияние в единый файл не выполняется; 1 = слияние в единый файл выполняется.

Как написано в BOL, не рекомендуется во время мастер-слияния перестраивать или реорганизовывать некластерные индексы на эту таблицу.

Количество проиндексированных строк можно посмотреть при помощи ф-ции objectpropertyex:

select objectpropertyex (object_id('TestFTS'), 'TableFulltextItemCount')

select count(1) from TestFTS

----------

872

(1 row(s) affected)

-----------

872

(1 row(s) affected)

Скрипт 12

Change Tracking при создании полнотекстового индекса, по-видимому, использует сходные механизмы отслеживания изменений, что и Change Tracking, хотя привычных для Change Tracking структур при этом на таблицу не создается. Отслеживание изменений происходит синхронно с изменениями, но полнотекстовый индекс обновляется асинхронно. Идентификаторы отслеженных изменившихся записей DocID подсовываются процессу индексирования во время его очередной актуализации. Принимаются во внимание модификации только в поля, покрытые полнотекстовым индексом, плюс type column, плюс в колонки, входящие в key index. Проверить, включен ли Change Tracking для полнотекстового индекса над таблицей, можно так:

select objectpropertyex (object_id('TestFTS'), 'TableFullTextChangeTrackingOn')

---

1

Скрипт 13

Команда

 

alter fulltext index on TestFTS set change_tracking manual

Скрипт 14

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

alter fulltext index on TestFTS start update population

Скрипт 15

С точки зрения оценки трудоемкости работы во время следующего обновления полнотекстового индексирования было бы полезно знать, а сколько таких измененных записей уже накопилось. Например, индекс у нас сейчас перестраивается вручную (Скрипт 14). С момента его последнего обновления (Скрипт 15) никаких модификаций в записях не происходило, поэтому первый вызов функции дает 0. Поменяем какую-нибудь запись:

select objectpropertyex (object_id('TestFTS'), 'TableFulltextPendingChanges')

update dbo.TestFTS set Content = cast('aaa' as varbinary(max)) where FullName = '\\192.168.0.1\c$\Trainings\Sqlclub\rsdn.txt'

select objectpropertyex (object_id('TestFTS'), 'TableFulltextPendingChanges')

-------------------

0

(1 row(s) affected)

(1 row(s) affected)

-------------------

1

(1 row(s) affected)

Скрипт 16

Если теперь сделать Скрипт 15, то снова станет 0. В отличие от Change Tracking и CDC реальное изменение значения поля не проверяется. Можно обновить на предыдущее значение, она отметится. Повторные модификации этой записи на что бы то ни было не учитываются, потому что ее уже и так записали в обновление индекса.

Логичным продолжением будет посмотреть, не только сколько всего обновлений накопилось, но и в каких именно записях. Увы, это опять же не Change Tracking и не CDC Один мужик здесь - https://www.eggheadcafe.com/conversation.aspx?messageid=34200947&threadid=34200947 - озадачился этим вопросом, но конкретики так и не добился.

Можно отключить Change Tracking. Тогда об изменившихся записях ей не узнать никак, и остается полная перепопуляция индекса, когда все строки таблицы по-новой перебираются и все поля в составе полнотекстового индекса по-новой лопатятся. Хотя eсли в таблице есть поле типа timestamp (rowversion), она сможет использовать его, чтобы определить записи, изменившиеся с момента последнего обновления полнотекстового индекса. Тогда нужно написать так:

alter fulltext index on TestFTS set change_tracking off

select objectpropertyex (object_id('TestFTS'), 'TableFullTextChangeTrackingOn')

alter fulltext index on TestFTS start incremental population

Скрипт 17

Но это только при наличии в таблице поля timestamp. Если нет, из деликатности ругаться не станет, а будет все равно делать по-своему полную переиндексацию, т.е. фактически

alter fulltext index on TestFTS start full population

Скрипт 18

Запустим Скрипт 18 и дополнительно к нему сразу выполним

select * from sys.dm_fts_index_population p join sys.dm_fts_population_ranges r on p.memory_address = r.parent_memory_address

         where p.database_id = db_id() and p.table_id = object_id('TestFTS')

select * from sys.dm_fts_active_catalogs where database_id = db_id()

select r.session_id, r.request_id, r.start_time, r.status, r.command, db_name(r.database_id), r.wait_type, r.wait_time,

r.cpu_time, r.scheduler_id, r.reads, r.writes, r.lock_timeout, w.context_switch_count, w.pending_io_count, w.state

from sys.dm_exec_requests r join sys.dm_os_workers w on r.task_address = w.task_address order by r.start_time, r.command

select * from sys.dm_fts_fdhosts

Скрипт 19

Как говорилось выше (https://blogs.msdn.com/alexejs/archive/2009/06/13/9745778.aspx), полнотекстовый поиск в SQL Server 2008 стал наполовину интегрированный, потому что ту его часть, что отвечала за компиляцию, оптимизацию и выполнение полнотекстовых запросов, короче, full-text engine, засунули в SQL Server. Фильтры, ворд брейкеры и стеммеры остались в fdhost, потому что их при желании может написать любой, и тащить это все in-process с SQL Server стремно. iFTS любит память практически так же сильно, как OLAP, (а кто, скажите, ее не любит) и отжирает при всяком удобном случае, как поросенок. Max Server Memory нужно выставлять с учетом того, что fdhost живет вне sqlservr.exe, а он тоже кушать хочет. Оценки необходимой полнотексту памяти - см. BOL, "Настройка и оптимизация производительности полнотекстовых индексов". При построении полнотекстового индекса SQL Server'у нужно считать у себя подлежащие индексации страницы и отдать их в fdhost.exe, чтобы тот их разбил по словам, словоформам и пр. Этим занимается товарищ FT GATHERER, который мы наблюдаем на Рис.4 в третьем резалтсете. Нижний резалтсет говорит, что fdhost.exe выполняет в настоящий момент один батч (распарсинг по словам для популяции индекса) о двух потоках. Из-за того, что потоков 2, в верхнем резалтсете мы наблюдаем процесс популяции в параллели по двум диапазонам. Fdhost здесь никакой магии от себя не привносит: если iFilter умеет выполняться многопоточно, он будет выполняться многопоточно. И тут вдруг бац, в следующей записи лежит файл такого типа, для которого имеется только однопоточный iFilter. Ничего фатального, запустится еще один экземпляр fdhosts.exe, который будет выполняться однопоточно. Батч можно посмотреть в sys.dm_fts_outstanding_batches. Как написано в BOL, memregion_memory_address - это адрес общей памяти для fdhost.exe, следовательно, тот, у кого он не нулевой, и есть товарищ, на которого ссылается sys.dm_fts_fdhosts (batch_count = 1).

database_id	catalog_id	table_id	batch_id	memory_address	crawl_memory_address	memregion_memory_address	retry_hints_description	doc_failed	batch_timestamp
11	5	1237579447	76	0x0519BEF8	0x0B262750	0x00000000	NO RETRY	0	0x0000000000001BED
11	5	1237579447	77	0x0B263D48	0x0B2630B8	0x0BF226F0	NO RETRY	0	0x0000000000001BED

Рис.4

Fdhost можно видеть в обычном диспетчере задач. Вот он с ProcessID = 3936, как указывает sys.dm_fts_fdhosts (см. Рис.5).

Рис.5

При полной популяции индекса мастер-слияние фрагментов (см. Скрипт 11) происходит по умолчанию, поэтому на Рис.2 в этом случае мы увидим только один фрагмент. SQL Server сообщает о прогрессе построения полнотекстового индекса в логах полнотекста. Логи полнотекста лежат там же, где Error Log'и (см., напр., пост "Как определить дефолтную локацию для файлов БД") и называются SQLFT<ля-ля-ля>.LOG.(N). Их полезно читать, чтобы своевременно отлавливать траблы. Например, сообщение

2009-08-21 18:00:56.95 spid26s Warning: No appropriate filter was found during full-text index population for table or indexed view '[TestFS].[dbo].[TestFTS]' (table or indexed view ID '1237579447', database ID '11'), full-text key value '/6/6/2/1/4/'. Some columns of the row were not indexed.

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

В практической задаче имелась разновидность таблицы TestFTS (не файлстримовская, т.е. varbinary(max) лежало в базе). Кроме файлового контента, в полнотекстовый индекс были включены еще два поля nvarchar (длиной 1000 и 255). Полнотекстовый контент менялся непрерывно. В таблице находилось примерно 25 млн.записей. Размер таблицы ~40 гиг. Процессор Intel x64, двухведерный, 2.20 GHz. Full population занимает ~5 часов. Полнотекстовый индекс был вынесен на отдельную файл-группу, но отдельного диска, чтобы положить ее туда, не было. Полнотекстовый индекс был сконфигурирован подобно Скрипту 2. Чтобы полная популяция не начиналась сразу после команды создания полнотекстового индекса, следует написать CREATE FULLTEXT INDEX ON ... WITH CHANGE_TRACKING OFF, NO POPULATION и выполнить Скрипт 18, когда удобно. Если запустить популяцию, когда этот индекс уже популируется, будет выдано предупреждение, и команда аннулируется. Старая популяция продолжается своим чередом.

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

alter fulltext catalog Cat1 rebuild with accent_sensitivity = off

Скрипт 19

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

Полную популяцию можно приостанавливать и возобновлять:

alter fulltext index on TestFTS pause population

alter fulltext index on TestFTS resume population

Скрипт 20

Если популяции в этот момент не происходило, команды игнорируются. Состояние популяции можно узнать при помощи

select objectpropertyex(object_id('TestFTS'), 'TableFulltextPopulateStatus')

Скрипт 21

Где 0 = Бездействует, 1 = Производится полное заполнение, 2 = Производится добавочное заполнение, 3 = Выполняется распространение отслеженных изменений, 4 = Выполняется индексирование фонового обновления (например автоматическое отслеживание изменений), 5 = Полнотекстовое индексирование приостановлено или не хватает ресурсов на его выполнение. Это я скопипастил из BOL - https://msdn.microsoft.com/ru-ru/library/ms188390.aspx. Там же можно найти еще полезные проявления функции objectpropertyex на тему полнотекстовой индексации. Все они начинаются со слова TableFulltext во втором аргументе.

При включенном Change Tracking, неважно, ручном или автоматическом, сказать NO POPULATION нельзя. Можно включить Change Tracking после начальной популяции полнотекстового индекса (Скрипт 18). При интенсивной работе с таблицей стали возникать тормоза при выполнении пользовательских запросов, т.е. они с Change Tracking стали мешаться друг другу. В sys.dm_os_wait_stats накручивался FULLTEXT GATHERER, т.е. полнотекстовая индексация то и дело лочила данные, не давая до них доступиться отсальным желающим. Change Tracking был переведен в ручной режим (Скрипт 14). Обновление индекса (Скрипт 15) было поставлено в джобу с периодичностью раз в 20 мин.