Полнотекстовый поиск SQL Server с понятными правилами

Поиск через LIKE '%term%' по большим статьям может стать дорогим при росте коллекции. Полнотекстовый поиск SQL Server предлагает другой доступ, но одновременно меняет смысл совпадения. Он индексирует лингвистические токены, а не все возможные подстроки. Необдуманная замена способна сделать поиск быстрее и менее полезным.

Разделите требования продукта. Точный код, слово в тексте, фраза и произвольный фрагмент являются разными вопросами. Для кода может лучше подходить обычный индекс и равенство. Полнотекстовый механизм полезен для слов и языковых правил. Не обязательно проводить все поисковые поля через один инструмент.

Язык задаётся явно

Пример создаёт постоянные учебные объекты. Используйте одноразовую базу с установленным Full-Text Search и достаточными правами настройки.

-- Run only in a disposable database with Full-Text Search installed.
CREATE TABLE dbo.SearchArticleDemo (
    ArticleId int NOT NULL CONSTRAINT PK_SearchArticleDemo PRIMARY KEY,
    Title nvarchar(200) NOT NULL,
    Body nvarchar(max) NOT NULL
);
INSERT dbo.SearchArticleDemo VALUES
(1, N'Index design', N'An index can reduce reads for selective queries.'),
(2, N'Backup planning', N'Restore tests verify that backups are usable.'),
(3, N'Query performance', N'Query performance depends on access paths and data.');
CREATE FULLTEXT CATALOG SearchArticleDemoCatalog;
CREATE FULLTEXT INDEX ON dbo.SearchArticleDemo
    (Title LANGUAGE 1033, Body LANGUAGE 1033)
KEY INDEX PK_SearchArticleDemo
ON SearchArticleDemoCatalog
WITH CHANGE_TRACKING AUTO;

Ключом полнотекстового индекса служит уникальный идентификатор без NULL. Оба текстовых столбца используют английские правила, LCID 1033. Язык влияет на выделение слов и обработку словоформ, а не просто подписывает интерфейс.

Многоязычное приложение должно согласовать язык индексируемого столбца с содержимым документов. Смесь разных языков под произвольной настройкой способна ухудшить релевантность. Когда требования сложнее, могут лучше подойти отдельное локализованное хранение либо специализированный многоязычный поиск.

CHANGE_TRACKING AUTO обновляет индекс асинхронно. Создание индекса не означает завершения начального заполнения. Подтверждённое редактирование статьи тоже может появиться с задержкой. Поэтому отсутствие результатов сразу после настройки ещё не доказывает ошибку поискового выражения.

Ранжирование должно быть предсказуемым

FREETEXTTABLE принимает естественный поисковый текст и возвращает ключи вместе с рангами.

DECLARE @Search nvarchar(4000) = N'index performance';
SELECT TOP (10) a.ArticleId, a.Title, ft.[RANK]
FROM FREETEXTTABLE(
    dbo.SearchArticleDemo, (Title, Body), @Search, LANGUAGE 1033
) AS ft
JOIN dbo.SearchArticleDemo AS a ON a.ArticleId = ft.[KEY]
ORDER BY ft.[RANK] DESC, a.ArticleId;

Конкретные ранги зависят от содержимого и запроса. Это не калиброванная вероятность полезности. ArticleId задаёт однозначный порядок при одинаковом ранге. Английский поисковый текст оставлен намеренно: учебные документы тоже написаны по-английски.

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

Передавайте ввод SQL-параметром. Для структурированного поиска параметризация предотвращает склейку SQL, но не делает любой текст допустимой поисковой грамматикой. Отдельно проверяйте или стройте поддерживаемые формы и возвращайте понятное объяснение неверного ввода.

Стоп-листы могут исключать распространённые слова. Проверьте запрос только из стоп-слов, фразы в кавычках, коды с дефисами и характерные словоформы. Заранее решите поведение пустого либо непригодного запроса, не превращая его автоматически в неограниченное сканирование.

Проверяем свежесть и полноту выдачи

Запрос помогает различить отсутствие компонента и активность заполнения.

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS Installed,
       OBJECTPROPERTYEX(OBJECT_ID(N'dbo.SearchArticleDemo'),
           'TableFulltextPopulateStatus') AS PopulationStatus;

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

Разрешения и ограничения арендатора должны применяться до возврата данных. Распространённая ошибка: получить небольшую глобальную верхушку ранжирования, а затем отфильтровать арендатора. Он может увидеть слишком мало строк при множестве допустимых совпадений. Проверяйте полноту и план реальной стратегии.

Используйте представительный корпус и набор запросов с известными полезными ответами. Измеряйте задержку и чтения, но также рассматривайте пропущенные документы и ложнополезные совпадения. Отдельно проверяйте обновление и удаление статьи: выдача не должна бесконечно показывать старое содержимое из-за незамеченного сбоя обслуживания.

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

Техническая документация: Microsoft Learn: Full-text search · Microsoft Learn: FREETEXTTABLE · Microsoft Learn: CONTAINS.