Преобразовать в запрос SARGable
Я хочу написать запрос для поиска содержащей строку в таблице.
Таблица:
Create table tbl_sarg
(
colname varchar(100),
coladdres varchar(500)
);
Примечание: я просто хочу использовать Index Seek для поиска по 300 миллионам записей.
Показатель:
create nonclustered index ncidx_colname on tbl_sarg(colname);
Примеры записей:
insert into tbl_sarg values('John A Mak','HNo 102 Street Road Uk');
insert into tbl_sarg values('Shawn A Meben','Church road USA');
insert into tbl_sarg values('Lee Decose','ShopNo 22 K Mark UK');
insert into tbl_sarg values('James Don','A Mall, 90 feet road UAE');
Запрос 1:
select * from tbl_sarg
where colname like '%ee%'
Фактический план выполнения:
Запрос 2:
select * from tbl_sarg
where charindex('ee',colname)>0
Фактический план выполнения:
Запрос 3:
select * from tbl_sarg
where patindex('%ee%',colname)>0
Фактический план выполнения:
Как заставить обработчик запросов использовать поиск по индексу вместо сканирования таблицы / индекса для большого набора данных?
Любой другой вариант, кроме полнотекстового индекса?
Если вы ищете только '%ee%', создайте постоянный вычисляемый столбец. В противном случае ваш единственный другой вариант - сканировать таблицу каждый раз, когда вы хотите выполнить поиск по шаблону с лидирующими символами.
Ответы 2
Все отправленные вами запросы по определению не подлежат SARgable, например, использование "% ..%" автоматически заставляет механизм запросов выполнять сканирование, другой случай - использование функций (как charindex или patindex ) внутри вашего столбца внутри предиката.
Вот пост: https://bertwagner.com/2017/08/22/how-to-search-and-destroy-non-sargable-queries-on-your-server/
Кимберли Трипп написала об этом очень интересные статьи, если для вас обязательно выполнение такого рода запросов с подстановочными знаками, возможно, стоит проверить возможность использования функции FullTextSearch. Я хочу сказать, или ваш предел, и сделайте точный предикат в ваших запросах, или вам придется изменить стратегию, почти забыть, не пытайтесь принудительно использовать Seek с HINT, я не вижу, что это лекарство будет лучше, чем болезнь.
Аргумент поиска, или сокращенно SARG, - это предикат фильтра, который позволяет оптимизатору полагаться на
порядок индекса. Предикат фильтра использует следующую форму (или вариант с двумя разделителями
диапазон, или с перевернутыми позициями операндов):
ГДЕ <column> <operator> <expression>
Таким фильтром является сомнительный, если:
Вы не применяете никаких манипуляций к отфильтрованному столбцу.
Оператор определяет последовательный диапазон подходящих строк в индексе. Это регистр с такими операторами, как =,>,> =, <, <=, BETWEEN, LIKE с известным префиксом и т. д. Это не относится к операторам типа <>, LIKE с подстановочным знаком в качестве префикса.
В большинстве случаев, когда вы применяете манипуляции к отфильтрованному столбцу, оптимизатор не постарайтесь быть слишком умным и понять смысл расчета, и если индексирование все еще можно положиться. Он просто предполагает, что значения результатов могут быть отсортированы иначе, чем исходные значения, и, следовательно, упорядочению индекса нельзя доверять.
Так почему же SQL Server не использует индекс для запроса %ee%? Представьте на мгновение, что вы держите в руке телефонную книгу, и я попросил вас найти всех, чья фамилия содержит буквы% ee%. Вам придется сканировать каждую страницу в телефонной книге, потому что результаты будут включать такие вещи, как:
Энн Ли
Ли Юнг
Кэтли
Алин
Когда я попросил вас указать все фамилии, содержащие
%ee%в любом месте имени, мой запрос не поддавался сортировке - это означает, что вы не могли использовать индексы для поиска по индексу.
Вот тут-то и появляется Полнотекстовый поиск SQL Server.
Ни один из этих запросов не поддерживает поиск по запросу, поскольку ведущий символ подстановки означает, что «нормальный» индекс бесполезен. Индексы для типов данных
(n)(var)charсортируют буквы в порядке сортировки слева направо. Если вы ищете значение, содержащее строку'ee'в любом месте значения, никакая сортировка вам здесь не поможет. Если вам нужно выполнять подобные запросы, вам нужно подумать о полнотекстовом индексе.