Повышение производительности полнотекстовых запросов в SQL Server

В этой статье приведен метод повышения производительности запросов, использующих полнотекстовые предикаты в SQL Server.

Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 2549443

Итоги

В этой статье описывается метод повышения производительности запросов Microsoft SQL Server, использующих предикаты полнотекстового поиска (например CONTAINS , и CONTAINSTABLE) и фильтрующие данные. Например, этот метод повышает производительность следующего запроса:

SELECT * FROM dbo.ftTest WHERE CONTAINS(TextData, '"keyword"') AND CDate > @date

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

Дополнительная информация

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

Метод, описанный в этой статье, использует функцию Table-Valued (TVF) для фильтрации строк на ранних этапах и уменьшения количества строк, которые должны быть обработаны позже.

Например, следующий план запроса возвращает 131051 строки, соответствующие строке CONTAINS поиска. Кроме того, оператор соединения в плане выполняет дополнительную фильтрацию с помощью поиска индекса.

Rows StmtText
-------------------- ----------------------------------------------------------------------------------------
1167 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate> '08/05/2019'

1167 |--Merge Join(Left Semi Join, MERGE:([FTSdb].[dbo].[fttest].[ID])=(FulltextMatch.[docid]), RESIDUA
5858 |--Sort(ORDER BY:([FTSdb].[dbo].[fttest].[ID] ASC))
5858 | |--Clustered Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[clidx1]), SEEK:([FTSdb].[
131051 |--Table-valued function

Однако если запрос включает в себя полнотекстовый ключевой столбец ключа индекса в качестве предиката, полнотекстовая поисковая система может использовать предикат для фильтрации результатов на полнотекстовом уровне. В этой ситуации TVF возвращает гораздо меньший объем данных перед применением дополнительной фильтрации. Например, следующий запрос указывает пять значений, которые должны соответствовать условию c2, а TVF возвращает только те результаты, которые соответствуют пяти значениям:

Rows StmtText
-------------------------------------------------------------------------------------------------------------------------------------------
5 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate > '08/05/2019' AND ID IN ( 654051, 644051, 649106, 465, 105)

5 |--Nested Loops(Left Semi Join, OUTER REFERENCES:([FTSdb].[dbo].[fttest].[ID]))
5 |--Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[idx1]), SEEK:([FTSdb].[dbo].[fttest].[ID]=(105) OR ...
5 |--Table-valued function

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

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

Чтобы реализовать метод, измените тип данных полнотекстового уникального ключа IDBIGINTна . Первые 4 байта идентификатора ключа фиксируют значения года, месяца и даты из столбца даты, а последние 4 байта остаются неизменными. Например, первый байт идентификатора ключа может ссылаться на год, следующий байт может ссылаться на месяц, а последние 2 байта могут ссылаться на дату. Приложение должно соответствовать изменению этого типа данных.

Затем преобразуйте предикат диапазона в предикат в идентификаторе ключа. Например, x < CDate < y предикат диапазона можно преобразовать в (x*2^32 < ID < y*2^32) предикат. Так как переведенный предикат является предикатом полнотекстового ключа, предикат будет отправлен в полнотекстовые функции потоковой передачи табличного значения (STVF). Это поведение эффективно выполняет поиск в диапазоне дат.