Можно ли, добавив индекс, сделать запрос SARGable? | OTUS

Курсы

Курсы в разработке Подготовительные курсы
Работа в компаниях Компаниям Блог +7 499 110-61-65

Можно ли, добавив индекс, сделать запрос SARGable?

SUBD_Deep_7.10-5020-5c41c0.png

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 чтений. Вот как выглядит план выполнения:

1-20219-089e01.jpg

Если мы всегда ищем по последним трём символам, то можем добавить вычисляемый столбец, который содержит только последние три символа, а затем добавить для него некластеризованный индекс.

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 и это хорошее улучшение. Вот как выглядит план выполнения.

2-20219-38e416.jpg

Однако есть небольшая проблема...Мы сказали, что не будем изменять запрос.

Но что произойдет, если мы выполним первоначальный запрос? Сможет ли оптимизатор 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.

Чертовски верно. Оптимизатор смог. Он использует новый индекс и столбец, хотя мы указываем исходный столбец (должно быть там встроен искусственный интеллект...шутка!).

Если вы посмотрите на план выполнения, то увидите, что это действительно поиск по индексу.

3-20219-6da316.jpg

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

Материал подготовлен специально для OTUS на основании статьи Can adding an index make a non SARGable query SARGable?

Не пропустите новые полезные статьи!

Спасибо за подписку!

Мы отправили вам письмо для подтверждения вашего email.
С уважением, OTUS!

Автор
0 комментариев
Для комментирования необходимо авторизоваться