Полнотекстовый поиск. Создание полнотекстового индекса.
Подготовительные действия на этом закончены, можно переходить к созданию полнотекстовых индексов. При этом можно идти двумя путями: визардо-гуевым и Т-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
Был создан полнотекстовый каталог по имени 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)
on (Cat1, filegroup FTS)
with change_tracking = auto, stoplist = system
Language 1049 означает, что к распарсенному результату будут применяться брейкер и стеммер (см. "Введение") для русского языка. Русский полнотекст поддерживается в SQL Server 2008, что называется, из коробки; никаких дополнительных действий для включения поддержки предпринимать не требуется – см.
select * from sys.fulltext_languages order by name
Полнотекстовый индекс создается в каталоге 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
Для полнотекстового индекса необходим обычный уникальный индекс, идентифицирующий строки таблицы. В его качестве подсовывается созданный ранее 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
Блин, когда же они наконец догадаются сделать поддержку выражений в параметрах процедур?
Key - это бинарное представление ключей нашего уникального индекса над таблицей по полю HierarchyID. На самом деле соответствие хранится во внутренней системной таблице, а эта процедура просто делает из нее выборку. Существует изрядно внутренних таблиц на тему полнотекста; их можно перечислить так:
select * from sys.internal_tables where name like '%fulltext%' or name like '%fts%' order by name
Легко видеть, что все они сопровождаются ObjectID той пользовательской таблицы, к которой относятся. Например, вот здесь хранится перекодировка key index <-> DocID:
declare @s varchar(10) = str(object_id('dbo.TestFTS'))
exec ('select * from sys.fulltext_index_docidmap_' + @s)
Понятно, что поскольку таблица internal, выполнять его надо с DACa. Также по этой таблице можно убедиться, что длина DocID в 2008-м составляет 8 байт:
select * from sys.all_columns where name = 'docid' and object_id = object_id('sys.fulltext_index_docidmap_' + @s)
Посмотреть, как в общих чертах выглядит полнотекстовый индекс, можно по таблицам 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
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 @b1 = substring(@x, @i, 1)
if @i > @l begin set @y += @b1; break end
set @b2 = substring(@x, @i, 1)
так как в колонке Keyword они идут по принципу little endian: 0x00770069006E0064006F00770073, тогда как T-SQLная конвертация varbinary в nvarchar построена по принципу big endian:
select cast(N'windows' as varbinary(max))
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')
Идейно то же самое можно получить при помощи 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
Список каталогов можно посмотреть в 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
(1 row(s) affected)
(1 row(s) affected)
Change Tracking при создании полнотекстового индекса, по-видимому, использует сходные механизмы отслеживания изменений, что и Change Tracking, хотя привычных для Change Tracking структур при этом на таблицу не создается. Отслеживание изменений происходит синхронно с изменениями, но полнотекстовый индекс обновляется асинхронно. Идентификаторы отслеженных изменившихся записей DocID подсовываются процессу индексирования во время его очередной актуализации. Принимаются во внимание модификации только в поля, покрытые полнотекстовым индексом, плюс type column, плюс в колонки, входящие в key index. Проверить, включен ли Change Tracking для полнотекстового индекса над таблицей, можно так:
select objectpropertyex (object_id('TestFTS'), 'TableFullTextChangeTrackingOn')
alter fulltext index on TestFTS set change_tracking manual
не отменяет отслеживание изменений, но означает, что обновление полнотекстового индекса будет происходить не в фоновом режиме, а по команде
alter fulltext index on TestFTS start update population
С точки зрения оценки трудоемкости работы во время следующего обновления полнотекстового индексирования было бы полезно знать, а сколько таких измененных записей уже накопилось. Например, индекс у нас сейчас перестраивается вручную (Скрипт 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')
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Если теперь сделать Скрипт 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
Но это только при наличии в таблице поля timestamp. Если нет, из деликатности ругаться не станет, а будет все равно делать по-своему полную переиндексацию, т.е. фактически