Получение первых n последних записей из полнотекстового индекса SQL Server

Короткий ответ: да, есть способы ускорить этот конкретный запрос, но с корпусом из 20 миллионов строк 5 секунд — это неплохо. Вам нужно будет серьезно подумать, являются ли приведенные ниже предложения оптимальными для вашей поисковой нагрузки FT, и взвесить затраты и преимущества. Если вы будете слепо применять их, у вас будут плохие времена.

Общие предложения по улучшению производительности полнотекстового поиска Sql Server

Уменьшите размер искомого полнотекстового индекса Чем меньше индекс FT, тем быстрее выполняется запрос. Есть несколько способов уменьшить размер индекса FT. Первые два могут применяться, а могут и не применяться, а для выполнения третьего потребуется значительная работа.

1. Добавить шумовые слова, специфичные для домена Шумные слова – это слова, которые не добавляют ценности запросам полнотекстового поиска, например "the", "and", "in" и т. д. Если есть термины, связанные для бизнеса, не добавляющего ценности для индексации, вы можете исключить их из индекса FT. Рассмотрим гипотетический полнотекстовый индекс библиотеки MSDN. Такие термины, как «Майкрософт», «библиотека», «включать», «dll» и «ссылка», могут не повышать ценность результатов поиска. (Есть ли реальная ценность в том, чтобы перейти на http://msdn.microsoft.com и выполнить поиск по слову "microsoft"? ) Индекс юридических заключений FT может исключать такие слова, как «ответчик», «обвинение» и «юридический» и т. д.

2. Удаление посторонних данных с помощью iFilters Полнотекстовый поиск с использованием Windows iFilters для извлечения текста из двоичных документов. Это та же технология, которую функция поиска в окне использует для поиска документов в формате pdf и PowerPoint. Единственный случай, когда это особенно полезно, — это когда у вас есть столбец описания, который может содержать HTML-разметку. По умолчанию полнотекстовый поиск Sql Server будет индексировать все, поэтому вы получите такие термины, как «семейство шрифтов», «Arial» и «href» в качестве поисковых терминов. Использование HTML iFilter может удалить разметку.

   Два требования для использования iFilter в вашем индексе FT заключаются в том, что индексированный столбец является VARBINARY и существует столбец «тип», который содержит расширение файла. И то, и другое можно выполнить с помощью вычисляемых столбцов.

   CREATE TABLE t (
   ....
   description varbinary(max),
   FTS_description as (CAST(description as VARBINARY(MAX)),
   FTS_filetype as ( N'.html' )
   )
   -- Then create the fulltext index on FTS_description specifying the filetype.
   

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

   DECLARE @rows int
   DECLARE @ids table (id int not null primary key)
   
   INSERT INTO @ids (id)   
       SELECT TOP (100) id 
       FROM vw_2013_FTDocuments WHERE CONTAINS (....) 
       ORDER BY Id DESC 
   SET @rows = @@rowcount
   IF @rows < 100
   BEGIN
     DECLARE @rowsLeft int
     SET @rowsLeft = 100 - @rows
     INSERT INTO @ids (id) SELECT TOP (@rowsLeft) ......
     --Logic to incorporate the historic data
   END
   SELECT ... FROM t INNER JOIN @ids .....
   

   Это может привести к существенному сокращению времени запроса за счет усложнения логики поиска. Этот подход также применим, когда поиск обычно ограничивается подмножеством данных. Например, Craigslist может иметь индекс FT для жилья, один для «Продажи» и один для «Работы». Любые поиски, выполненные с домашней страницы, будут объединены из отдельных индексов, в то время как общий случай поиска внутри категории более эффективен.

Неподдерживаемый метод, который, вероятно, сломается в будущей версии Sql Server.

Вам потребуется тщательно протестировать данные того же количества и качества, что и производственные. Если поведение изменится в будущих версиях сервера Sql, у вас не будет права жаловаться. Это основано на наблюдениях, а не на доказательствах. Используйте на свой страх и риск!!

Немного полнотекстовой истории В Sql Server 2005 функция полнотекстового поиска находилась во внешнем процессе из sqlservr.exe. Способ, которым FTS был включен в планы запросов, представлял собой черный ящик. Сервер Sql передаст FTS запрос, FTS вернет поток идентификаторов. Это ограничивало планы, доступные для Sql Server, планами, в которых оператор FTS можно было рассматривать как сканирование таблицы.

В Sql Server 2008 FTS был интегрирован в движок, что повысило производительность. Это также дало оптимизатору новые возможности для планов запросов FTS. В частности, теперь у него есть возможность исследовать индекс FTS внутри оператора LOOP JOIN, чтобы проверить, соответствуют ли отдельные строки предикату FTS. (см. http://sqlblog.com/blogs/joe_chang/archive/2012)./02/19/query-optimizer-gone-wild-full-text.aspx за прекрасное обсуждение этого вопроса и того, как что-то может пойти не так.)

Требования к оптимальному плану запроса FTS Чтобы получить оптимальный план запроса, нужно стремиться к двум характеристикам.

1. Без операций сортировки. Сортировка идет медленно, и мы не хотим сортировать ни 20 миллионов строк, ни 350 000 строк.
2. Не возвращайте все 350 тыс. строк, соответствующих предикату FTS. Мы должны избегать этого, если это вообще возможно.

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

Для планов с петлевым соединением есть два варианта. Просканируйте кластеризованный индекс в обратном направлении и проверьте каждую строку в системе полнотекстового поиска, чтобы увидеть, соответствует ли эта конкретная строка. Теоретически это кажется хорошим решением, поскольку, как только мы сопоставляем 100 строк, все готово. Возможно, нам придется перебрать 10 000 идентификаторов, чтобы найти 100 совпадающих, но это может быть лучше, чем чтение всех 350 тысяч. Также может быть и хуже (см. выше ссылку на блог Джо Чанга), если каждый зонд стоит дорого, тогда наши 10k зондов могут занять значительно больше времени, чем простое чтение всех 350k строк.

Другой вариант соединения цикла состоит в том, чтобы часть FTS располагалась на внешней стороне цикла и осуществляла поиск в кластеризованном индексе. К сожалению, механизм FTS не любит возвращать результаты в обратном порядке, поэтому нам пришлось бы читать все 350 КБ, а затем сортировать их, чтобы вернуть первые 100.

Проблема заключается в том, что механизм FTS возвращает строки в обратном порядке. Если мы сможем решить эту проблему, то сможем сократить число операций ввода-вывода до чтения только последних 100 совпадающих строк. К счастью, механизм FTS имеет тенденцию возвращать строки в порядке ключа уникального индекса, указанного при создании индекса. (Это естественный побочный эффект внутренней памяти, которую использует механизм FTS)

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

CREATE TABLE t (id int not null primary key, txt varchar(max), neg_id as (-id) persisted )
CREATE UNIQUE INDEX IX_t_neg_id on t (neg_id)
CREATE FULLTEXT INDEX on t ( txt ) KEY INDEX IX_t_neg_id

Теперь для нашего запроса мы будем использовать CONTAINSTABLE и некоторые приемы LEFT-соединения, чтобы гарантировать, что предикат FTS не окажется внутри LOOP JOIN.

SELECT TOP (100) t.id, t.txt 
FROM CONTAINSTABLE(t, txt, 'house') ft 
LEFT JOIN t on tf.[Key] = t.neg_id ORDER BY tf.[key]

Результирующий план должен представлять собой циклическое соединение, которое считывает только последние 100 строк из индекса FT.

Небольшие порывы ветра, которые могут сдуть этот карточный домик:

• Сложные запросы FTS (например, несколько условий или использование операторов NOT или OR могут привести к тому, что Sql 2008+ станет «умным» и преобразует логику в несколько запросов FTS, которые объединены в плане запроса.
• Любое накопительное обновление, пакет обновления или обновление основной версии может сделать этот подход бесполезным.
• Он может сработать в 95% случаев, а таймаут в оставшихся 5%.
• Это может вообще не работать для вас.

Удачи!