Что такое полнотекстовый индекс
Заполнение полнотекстовых индексов
Создание и обслуживание полнотекстового индекса включает процесс заполнения индекса (которое также называется сканированием).
Types of population
Полнотекстовый индекс поддерживает следующие типы заполнения:
Полное заполнение
Во время полного заполнения индексные записи строятся для всех строк таблицы или индексированного представления. Во время полного заполнения полнотекстового индекса индексные записи строятся для всех строк базовой таблицы или индексированного представления.
По умолчанию SQL Server полностью заполняет новый полнотекстовый индекс сразу после его создания.
Пример создания полнотекстового индекса без выполнения полного заполнения
Пример выполнения полного заполнения в таблице
Заполнение на основе отслеживания изменений
Можно также использовать отслеживание изменений для обслуживания полнотекстового индекса после его первоначального полного заполнения. Это вызывает небольшую нагрузку, связанную с отслеживанием изменений, поскольку SQL Server поддерживает таблицу, в которой выполняется отслеживание изменений в базовой таблице со времени последнего заполнения. При использовании отслеживания изменений SQL Server поддерживает запись строк базовой таблицы или индексированного представления, измененного операциями обновления, удаления или вставки. Изменения данных, внесенные с помощью инструкций WRITETEXT и UPDATETEXT, не отражаются в полнотекстовом индексе и не отмечаются при отслеживании изменений.
Для таблиц, содержащих столбец timestamp, вместо отслеживания изменений можно использовать добавочное заполнение.
При включении отслеживания изменений во время создания индекса SQL Server полностью заполняет новый полнотекстовый индекс сразу же после его создания. Таким образом, изменения отслеживаются и распространяются в полнотекстовый индекс.
Включить отслеживание изменений
Отслеживания изменений выполняется двумя способами:
Способ заполнения полнотекстового индекса определяется типом отслеживания изменений.
Автоматическое заполнение
По умолчанию (либо если задан параметр CHANGE_TRACKING AUTO ) средство полнотекстового поиска использует автоматическое заполнение полнотекстового индекса. После завершения первоначального полного заполнения изменения отслеживаются по мере изменений данных в базовой таблице, а зарегистрированные изменения распространяются автоматически. Полнотекстовый индекс обновляется в режиме вне сети, однако изменения, распространяемые таким образом, могут не отражаться в индексе сразу.
Запуск отслеживания изменений с автоматическим заполнением
Пример переключения полнотекстового индекса на автоматическое отслеживание изменений
В следующем примере полнотекстовый индекс таблицы HumanResources.JobCandidate образца базы данных AdventureWorks переключается на отслеживание изменений с автоматическим заполнением.
Заполнение вручную
Запуск отслеживания изменений с заполнением вручную
Включение отслеживания изменений
Добавочное заполнение с использованием отметок времени
Добавочное заполнение является альтернативным механизмом по отношению к заполнению полнотекстового индекса вручную. Если выполняется большое количество операций вставки в таблицу, то использование добавочного заполнения может быть более эффективным, чем использование заполнения вручную.
Можно запустить добавочное заполнение для полнотекстового индекса, у которого параметру CHANGE_TRACKING присвоено значение MANUAL или OFF.
В некоторых случаях попытка выполнить добавочное заполнение приведет к полному заполнению.
Запуск добавочного заполнения
Создание или изменение расписания для добавочных заполнений
В обозревателе объектов среды Management Studio разверните сервер.
Разверните узел Базы данных, а затем базу данных, которая содержит полнотекстовый индекс.
Раскройте узел Таблицы.
Если базовая таблица или представление не содержат столбец типа данных timestamp, то выполнить добавочное заполнение невозможно.
На панели Выбор страницы выберите пункт Расписания.
Эта страница используется для создания расписания задания агента SQL Server, которое запускает добавочное заполнение базовой таблицы или индексированного представления полнотекстового индекса, а также для управления таким расписанием.
Существуют следующие варианты выбора.
Чтобы создать новое расписание, щелкните Создать.
Чтобы изменить существующее расписание, выберите существующее расписание и щелкните Изменить.
Сведения об изменении задания агента SQL Server см. в этой статье.
Чтобы удалить существующее расписание, выберите существующее расписание и щелкните Удалить.
Нажмите кнопку ОК.
Устранение ошибок в заполнении средства полнотекстового поиска (сканирование)
Имена файлов журналов сканирования имеют следующий формат.
Ниже перечислены переменные части в именах файла журнала сканирования.
Например, SQLFT0000500008.2 является файлом журнала сканирования для базы данных с идентификатором базы данных 5 и идентификатором полнотекстового каталога 8. Двойка в конце имени файла показывает, что этой паре базы данных и каталога соответствуют два файла журналов сканирования.
Полнотекстовый поиск и его возможности
Многие СУБД поддерживают методы полнотекстового поиска (Fulltext search), которые позволяют очень быстро находить нужную информацию в больших объемах текста.
В отличие от оператора LIKE, такой тип поиска предусматривает создание соответствующего полнотекстового индекса, который представляет собой своеобразный словарь упоминаний слов в полях. Под словом обычно понимается совокупность из не менее 3-х не пробельных символов (но это может быть изменено). В зависимости от данных словаря может быть вычислена релевантность – сравнительная мера соответствия запроса найденной информации.
В статье рассказывается как работать с полнотекстовым поиском на примере БД MySQL, а так же приведу примеры «нестандартного» использования данного механизма.
В MySQL возможности полнотекстового поиска (только для MyISAM-таблиц) поддерживаются начиная с версии 3.23.23. В последующих версиях механизм потерпел существенные доработки и расширения, в тоге превратившись в мощное средство для создания поисковых механизмов веб-приложений. Главная особенность – быстрый поиск слов в очень больших объемах текстовой информации.
Индекс FULLTEXT
Итак, чтобы работать с полнотекстовым поиском, сначала нам нужно создать соответствующий индекс. Он называется FULLTEXT, и может быть наложен на поля CHAR, VARCHAR и TEXT. Причем, как и в случае с обычным индексом – если происходит поиск по 2-м полям, то нужен объединенный индекс 2-х полей, используйте поиск по одному полю – нужен индекс только этого поля. Например:
CREATE TABLE `articles` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(200) default NULL,
`body` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `ft1` (`title`,`body`),
FULLTEXT KEY `ft2` (`body`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
В этом примере создается таблица с 2-мя полнотекстовыми индексами: ft1 и ft2, которые можно использовать для поиска в полях title и body, или только в body. Только в поле title искать не получится.
Конструкция MATCH-AGAINST
Собственно для самого полнотекстового поиска в MySQL используется конструкция MATCH(filelds)… AGAINST(words). Она может работать в различных режимах, которые достаточно сильно между собой отличаются. Для всех действует следующее правило: данная конструкция возвращает условную релевантность, но способ вычисления которой может быть разным в зависимости от режима. Еще стоит добавить что во всех режимах поиск всегда регистрозависимый. Далее более подробно о каждом из них.
MATCH-AGAINST IN NATURAL LANGUAGE MODE
— это основной вид поиска, который используется по умолчанию, т.е. если режим не указан:
SELECT * FROM `articles` WHERE MATCH (title,body) AGAINST (‘database’);
В этом примере мы ищем слово database в полях title и body таблицы articles на основе индекса ft1 (см. пример создания таблицы выше). Выборка будет автоматически отсортирована по релевантности – это происходит в случае указания конструкции MATCH-AGAINST внутри блока WHERE и не задано условие сортировки ORDER BY.
Кстати, несмотря на возможности алиасов, при запросах конструкцию приходится повторять в разных местах, что усложняет запросы. Вот например нельзя написать так:
SELECT *, MATCH (title,body) AGAINST (‘database’) as REL
FROM `articles`
WHERE REL > 0;
— этот запрос выдаст ошибку: поле Rel не определено. Что бы работало, придется продублировать данную конструкцию:
SELECT *, MATCH (title,body) AGAINST (‘database’) as REL
FROM `articles`
WHERE MATCH (title,body) AGAINST (‘database’) > 0;
Однако, сколько бы вы не использовали одну и туже конструкцию (разумеется с одинаковыми параметрами) она будет вычислена только один раз.
В примере выше в переменной REL будет вычислена релевантность. Эта величина зависит прежде всего от количества слов в полях tilte и body, того насколько близко данное слово встречается к началу текста, отношения количества встретившихся слов к количеству всех слов в поле и др.
Например, релевантность будет не нулевая, если слово database встретится либо в title, либо body, но если оно встретится и там и там, значение релевантности будет выше, нежели если оно два раза встретится в body.
Сама по себе релевантность ничего не определяет. Это лишь сравнительная характеристика, по которой можно сортировать результат выборки, не более того.
Еще следует заметить что для IN NATURAL LANGUAGE MODE действует так называемое «50% threshold». Это означает, что если слово встречается более чем в 50% всех просматриваемых полей, то оно не будет учитываться, и поиск по этому слову не даст результатов.
MATCH-AGAINST IN BOOLEAN MODE
В бинарном режиме, в отличие от других режимов, релевантность вычисляется несколько иначе — как условная мера совпадения заданного шаблона. Положение искомого шаблона в тексте, количество встретившихся вариантов роли не играют.
Самая важная особенность бинарного режима – возможность указания логических операторов. Сами операторы я приводить не буду, о них хорошо рассказано в оригинальной документации по MySQL.
Еще особенностями бинарного режима является отсутствие автоматической сортировки в случае указания условия WHERE, однако для сортировки можно использовать алиас:
SELECT *,
MATCH (title,body) AGAINST (‘+database MySQL’ IN BOOLEAN MODE) as REL
FROM `articles`
WHERE MATCH (title,body) AGAINST (‘+database MySQL’ IN BOOLEAN MODE)
ORDER BY REL;
Пример выведет все записи содержащие слово database, но если в записи присутствует слово MySQL, то его релевантность будет выше. Записи будут отсортированы по релевантности.
В бинарном режиме отсутствует ограничение «50% threshold». Бинарный режим можно использовать и без создания полнотекстового индекса, однако это будет работать очень медленно.
MATCH-AGAINST IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
Или просто «WITH QUERY EXPANSION». Работает примерно также, как NATURAL LANGUAGE MODE, с той лишь разницей, то в результат поиска попадают не только совпадения с шаблоном, но и возможные логические совпадения. Это работает примерно так:
Сначала MySQL выполняет запрос аналогичный NATURAL LANGUAGE MODE и формирует результат. По этому результату производится попытка вычислить слова, которые так же имеют высокую релевантность для полученной выборки. В случае, если эти слова присутствуют производится поиск и по ним тоже, но значение их на релевантность будет существенно ниже. Отдается смешанная выборка – сначала те результаты, где слово присутствует, а потом те, которые были получены в результате «повторного» поиска.
WITH QUERY EXPANSION не рекомендуется использовать для больших объемов информации, так как в результат может попасть очень много лишнего.
Использование FULLTEXT SEARCH
Пара слов об алгоритмах поиска
Соответственно, с поисковым запросом надо сделать тоже самое. Режим поиска используется любой – как удобнее… А вообще поиск – это отдельная тема, про которую нужна отдельная статья.
Раскрытие связок многое-ко-многим
В некоторых случаях – не во всех – с помощью полнотекстового поиска можно раскрывать соотношения многое-ко-многим без привлечения третьей таблицы.
Допустим, у нас есть две большие таблицы: с пользователями и группами пользователей. Причем, каждый пользователь имеет отношение к большому количеству различных групп, в свою очередь группы включают в себя большое количество пользователей. При нормальном соотношении (т.е. раскрытии через 3-ю таблицу), что бы выбрать все группы, которые принадлежат к некоторому пользователю понадобиться сделать запрос, объединяющий 2 или 3 таблицы, что даже при присутствии индексов очень накладно.
Однако можно выполнить денормализацию по следующей схеме: 
Теперь, что бы выбрать группы, принадлежащие к пользователю 2 можно сделать:
SELECT *
FROM `groups`
WHERE MATCH (groups) AGAINST (‘+user2’ IN BOOLEAN MODE);
Это будет работать намного быстрее, чем исходный вариант (с 3-ей таблицей). Аналогично с группами, но если подобные выборки нам в принципе не нужны, то можно обойтись без соответствующего поля в таблице групп. Тогда получится что-то вроде «односторонней» связи M:N. То есть можно вычислить все M, которые принадлежат к N, не нельзя сделать обратного.
В этом случае, как правило, используется IN BOOLEAN MODE.
— Кстати, на эту схему очень хорошо ложится тегирование информации, но там не все так просто и это опять же отдельная тема.
Использование релевантности как меры отношения одного объекта к другому
Один из алгоритмов для вычисления статей, «похожих» на данную статью. Всё просто: берутся теги данной статьи, и делается полнотекстовый запрос по полю с тегами всех остальных статей с сортировкой по релевантности (если она нужна). Естественно, сначала вылезут те, которые содержат максимальное совпадение по тегам.
Можно и без учета тегов. Если статьи индексированы для полнотекстового поиска, из индекса выбираются с десяток наиболее употребляемых слов, после чего делается поиск по ним.
Или вот еще пример – интересы пользователей. Используя точно такую же схему можно легко найти других пользователей, у которых интересы наиболее соответствуют вашим.
Создание и управление полнотекстовыми индексами
Здесь описывается создание и заполнение полнотекстовых индексов, а также управление ими в SQL Server.
Предварительное требование — создание полнотекстового каталога
Необходимо создать полнотекстовый каталог, прежде чем создавать полнотекстовый индекс. Каталог — это виртуальный контейнер для одного или нескольких полнотекстовых индексов. Дополнительные сведения см. в статье Создание полнотекстовых каталогов и управление ими.
Создание, изменение или удаление полнотекстового индекса
Создание полнотекстового индекса
Изменение полнотекстового индекса
Удаление полнотекстового индекса
Заполнение полнотекстового индекса
Процесс создания и сопровождения полнотекстового индекса называется заполнением (а также сканированием). Существует три типа заполнения полнотекстового индекса:
Просмотр свойств полнотекстового индекса
Просмотр свойств полнотекстового индекса с помощью Transact-SQL
| Каталог или динамическое административное представление | Описание |
|---|---|
| sys.fulltext_index_catalog_usages (Transact-SQL) | Возвращает строку для каждого полнотекстового каталога, ссылающегося на полнотекстовый индекс. |
| sys.fulltext_index_columns (Transact-SQL) | Содержит по одной строке для каждого столбца, являющегося частью полнотекстового индекса. |
| sys.fulltext_index_fragments (Transact-SQL) | Полнотекстовый индекс использует внутренние таблицы, называемые фрагментами полнотекстового индекса, для хранения данных инвертированного индекса. Это представление может использоваться для запросов к метаданным об этих фрагментах. Представление содержит строку для каждого фрагмента полнотекстового индекса в каждой таблице, содержащей полнотекстовый индекс. |
| sys.fulltext_indexes (Transact-SQL) | Содержит по одной строке для каждого полнотекстового индекса табличного объекта. |
| sys.dm_fts_index_keywords (Transact-SQL) | Возвращает сведения о содержимом полнотекстового индекса для указанной таблицы. |
| sys.dm_fts_index_keywords_by_document (Transact-SQL) | Возвращает сведения о содержимом полнотекстового индекса на уровне документа для указанной таблицы. Данное ключевое слово может встречаться в нескольких документах. |
| sys.dm_fts_index_population (Transact-SQL) | Возвращает сведения о выполняющихся в настоящий момент процессах заполнения полнотекстовых индексов. |
Просмотр свойств полнотекстового индекса с помощью Management Studio
В обозревателе объектов среды Management Studio разверните сервер.
Разверните узел Базы данных, а затем базу данных, которая содержит полнотекстовый индекс.
Раскройте узел Таблицы.
На панели Выбор страницы можно выбрать любую из приведенных ниже страниц.
| Страница | Описание |
|---|---|
| Общие сведения | Отображаются основные свойства полнотекстового индекса. Сюда относятся несколько изменяемых свойств и несколько неизменяемых, например имя базы данных, имя таблицы и имя полнотекстового ключевого столбца. Изменяемыми являются следующие свойства. Список стоп-слов полнотекстового индекса. Полнотекстовое индексирование включено. Отслеживание изменений Поиск в списке свойств. |
| Столбцы | Отображаются столбцы таблицы, доступные для полнотекстового индексирования. Выбранные столбцы включаются в полнотекстовый индекс. Можно выбрать столько доступных столбцов, сколько нужно включить в полнотекстовый индекс. Дополнительные сведения см. в статье Заполнение полнотекстовых индексов. |
| Расписания | Эта страница используется для создания расписаний задания агента SQL Server, которое запускает добавочное заполнение таблицы для заполнения полнотекстового индекса или для управления такими расписаниями. Дополнительные сведения см. в статье Заполнение полнотекстовых индексов. Примечание. После выхода из диалогового окна Свойства полнотекстового индекса любое вновь созданное расписание связывается с заданием агента SQL Server (запуск добавочного заполнения таблицы в имя_базы_данных.имя_таблицы). |
Нажмите кнопку ОК., чтобы сохранить изменения и выйти из диалогового окна Свойства полнотекстового индекса.
Просмотр свойств индексированных таблиц и столбцов
Могут использоваться некоторые функции Transact-SQL, такие как OBJECTPROPERTYEX, для получения значений различных свойств полнотекстового индексирования. Эти сведения полезны для администрирования и устранения нарушений в работе средств полнотекстового поиска.
В следующей таблице перечислены свойства полнотекстового поиска, связанные с индексированными таблицами и столбцами, и относящиеся к ним функции Transact-SQL.
| Свойство | Описание | Компонент |
|---|---|---|
| FullTextTypeColumn | TYPE COLUMN в таблице, которая содержит информацию о типе документа столбца. | COLUMNPROPERTY |
| IsFulltextIndexed | Указывает, включено ли в столбце полнотекстовое индексирование. | COLUMNPROPERTY |
| IsFulltextKey | Указывает, является ли индекс полнотекстовым ключом таблицы. | INDEXPROPERTY |
| TableFulltextBackgroundUpdateIndexOn | Указывает, имеется ли в таблице фоновое полнотекстовое индексирование обновлений. | OBJECTPROPERTYEX |
| TableFulltextCatalogId | Идентификатор полнотекстового каталога, где находятся данные полнотекстового индекса таблицы. | OBJECTPROPERTYEX |
| TableFulltextChangeTrackingOn | Указывает, включено ли в таблице полнотекстовое отслеживание изменений. | OBJECTPROPERTYEX |
| TableFulltextDocsProcessed | Количество строк, обработанных с начала полнотекстового индексирования. | OBJECTPROPERTYEX |
| TableFulltextFailCount | Количество строк, для которых полнотекстовый поиск не выявил индекса. | OBJECTPROPERTYEX |
| TableFulltextItemCount | Количество строк, для которых было успешно выполнено полнотекстовое индексирование. | OBJECTPROPERTYEX |
| TableFulltextKeyColumn | Идентификатор столбца полнотекстового уникального ключевого столбца. | OBJECTPROPERTYEX |
| TableFullTextMergeStatus | Определяет, участвует ли в настоящий момент полнотекстовый индекс для таблицы в процессе слияния. | OBJECTPROPERTYEX |
| TableFulltextPendingChanges | Количество ожидающих отслеженных изменений к обработке. | OBJECTPROPERTYEX |
| TableFulltextPopulateStatus | Указывает состояние заполнения полнотекстовой таблицы. | OBJECTPROPERTYEX |
| TableHasActiveFulltextIndex | Указывает, содержит ли таблица активный полнотекстовый индекс. | OBJECTPROPERTYEX |
Получение информации о столбце полнотекстового ключа
Обычно результат функций, возвращающих наборы строк CONTAINSTABLE или FREETEXTTABLE, необходимо соединить с базовой таблицей. В таких случаях необходимо знать уникальное имя ключевого столбца. Можно уточнить, используется ли данный уникальный индекс как полнотекстовый ключ, а также получить идентификатор полнотекстового ключевого столбца.
Определение использования данного уникального индекса в качестве полнотекстового ключевого столбца
Если индекс принудительно обеспечивает уникальность столбца полнотекстового ключа, будет возвращено значение 1, в противном случае запрос возвратит 0.
Пример
Следующий пример уточняет, используется ли индекс PK_Document_DocumentNode для обеспечения уникальности столбца полнотекстового ключа:
Если индекс PK_Document_DocumentNode используется для обеспечения уникальности столбца полнотекстового ключа, будет возвращено значение 1. В противном случае возвращается значение 0 или NULL. NULL означает, что используется недопустимое имя индекса, имя индекса не соответствует таблице, таблица не существует и пр.
Поиск идентификатора столбца полнотекстового ключа
Примеры
В следующем примере возвращается идентификатор столбца полнотекстового ключа или значение NULL. NULL означает, что используется недопустимое имя индекса, имя индекса не соответствует таблице, таблица не существует и пр.
Следующий пример показывает, как использовать идентификатор уникального ключевого столбца для получения имени столбца.
Индексирование столбцов varbinary(max) и xml
Индексирование данных типа varbinary(max) и varbinary
Учтите, что средство полнотекстового поиска может использовать имеющиеся фильтры, которые установлены в операционной системе. Перед использованием фильтров операционной системы, средств разбиения по словам и парадигматических модулей их необходимо загрузить на экземпляр сервера следующим образом.
Индексирование XML-данных
Столбец с данными типа xml содержит только документы и фрагменты XML, и для таких документов используется только фильтр XML. Поэтому столбец типов не требуется. Для столбцов типа xml можно создать полнотекстовый индекс, который индексирует содержимое XML-элементов, но пропускает XML-разметку. К значениям атрибута, если они не являются числовыми значениями, применяется полнотекстовый индекс. Теги элементов используются в качестве границ токенов. Поддерживаются XML- или HTML-документы и фрагменты правильного формата, содержащие несколько языков.
Дополнительные сведения о создании запросов к столбцу типа xml и его индексировании см. в статье Полнотекстовый поиск в XML-столбцах.
Отключение и повторное включение полнотекстовой индексации для таблицы
В SQL Serverво всех созданных пользователями базах данных полнотекстовый поиск включен по умолчанию. Кроме того, отдельная таблица автоматически включается для полнотекстового индексирования, как только для нее создается полнотекстовый индекс, и в этот индекс добавляется столбец. Таблица автоматически выключается для полнотекстового индексирования, как только из ее полнотекстового индекса удаляется последний столбец.
В таблице с полнотекстовым индексом можно вручную отключить и повторно включить полнотекстовое индексирование, используя среду SQL Server Management Studio.
Разверните группу серверов, узел Базы данных и базу данных, содержащую таблицу, для которой нужно включить полнотекстовое индексирование.
Разверните узел Таблицы и щелкните правой кнопкой мыши таблицу, которую нужно отключить или повторно включить для полнотекстового индексирования.
Выберите Полнотекстовый индекс и щелкните Отключить полнотекстовое индексирование или Включить полнотекстовое индексирование.
Удаление полнотекстового индекса из таблицы
В обозревателе объектов щелкните правой кнопкой мыши таблицу, содержащую полнотекстовый индекс, который необходимо удалить.
Выберите Удалить полнотекстовый индекс.