Можно ли, добавив индекс, сделать запрос SARGable?
SARGable (Search ARGument Able) — термин, который означает, что для условия WHERE в запросе возможен поиск по индексу.
На днях коллега спросил у меня: «Можно ли, добавив индекс, сделать запрос SARGable?».
Выражение WHERE в запросе выглядело примерно так:
WHERE RIGHT(SomeColumn,3) = '333'
Я спросил его, может ли он изменить таблицу. Он ответил, что изменять существующие столбцы нельзя, но можно их добавить. Хорошо. Это заставило меня задуматься о решении. Давайте посмотрим, что я придумал.
Сначала создадим таблицу:
USE tempdb GO CREATE TABLE StagingData (SomeColumn varchar(255) NOT NULL ) ALTER TABLE dbo.StagingData ADD CONSTRAINT PK_StagingData PRIMARY KEY CLUSTERED ( SomeColumn ) ON [PRIMARY] GO
Потом генерируем тестовые данные, добавив точку и число от 100 до 999 к GUID. Вставим одну строку, чтобы посмотреть, как будут выглядеть данные.
DECLARE @guid uniqueidentifier SELECT @guid = 'DEADBEEF-DEAD-BEEF-DEAD-BEEF00000075' INSERT StagingData SELECT CONVERT(varchar(200),@guid) + '.100' SELECT * FROM StagingData
Результат:
SomeColumn -------------------------------- DEADBEEF-DEAD-BEEF-DEAD-BEEF00000075.100
Теперь вставим 999 999 строк.
INSERT StagingData SELECT TOP 999999 CONVERT(varchar(200),NEWID()) + '.' + CONVERT(VARCHAR(10),s2.number) FROM master..SPT_VALUES s1 CROSS JOIN master..SPT_VALUES s2 WHERE s1.type = 'P' AND s2.type = 'P' AND s1.number BETWEEN 100 AND 999 AND s2.number BETWEEN 100 AND 999
После этого у нас должен быть миллион строк. Если мы выполним наш запрос для поиска строк, где последние три символа равны 333, то мы увидим сканирование.
SET STATISTICS IO ON GO SELECT SomeColumn FROM StagingData WHERE RIGHT(SomeColumn,3) = '333' SET STATISTICS IO OFF GO
(900 rows affected) Table 'StagingData'. Scan count 1, logical reads 5404, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Мы получили 900 строк и 5404 чтений. Вот как выглядит план выполнения:
Если мы всегда ищем по последним трём символам, то можем добавить вычисляемый столбец, который содержит только последние три символа, а затем добавить для него некластеризованный индекс.
ALTER TABLE StagingData ADD RightChar as RIGHT(SomeColumn,3) GO CREATE INDEX ix_RightChar on StagingData(RightChar) GO
Теперь давайте посмотрим, что мы получим, если будем использовать этот новый столбец.
SET STATISTICS IO ON GO SELECT SomeColumn FROM StagingData WHERE RightChar = '333' SET STATISTICS IO OFF GO
(900 rows affected) Table 'StagingData'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Число чтений уменьшилось с 5404 до 10 и это хорошее улучшение. Вот как выглядит план выполнения.
Однако есть небольшая проблема...Мы сказали, что не будем изменять запрос.
Но что произойдет, если мы выполним первоначальный запрос? Сможет ли оптимизатор SQL Server распознать, что наш новый столбец и индекс практически то же самое, что и условие в WHERE?
SET STATISTICS IO ON GO SELECT SomeColumn FROM StagingData WHERE RIGHT(SomeColumn,3) = '333' SET STATISTICS IO OFF GO
(900 rows affected) Table 'StagingData'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Чертовски верно. Оптимизатор смог. Он использует новый индекс и столбец, хотя мы указываем исходный столбец (должно быть там встроен искусственный интеллект...шутка!).
Если вы посмотрите на план выполнения, то увидите, что это действительно поиск по индексу.
Итак, что мы имеем... Иногда вы не можете изменить запрос и существующие столбцы, но вы можете добавить столбец в таблицу, и в этом случае может быть полезен рассмотренный метод.
Материал подготовлен специально для OTUS на основании статьи Can adding an index make a non SARGable query SARGable?
