По материалам статьи Craig Freedman
Чтобы прочитать данные из каждой таблицы запроса, оптимизатор должен выбрать соответствующий путь доступа. При этом для индексов он учитывает несколько факторов, с помощью которых он определяет, нужно ли использовать просмотр (сканирование) или поиск, и понадобится ли делать поиск закладок. Вот некоторые из этих факторов:
-
Какой ожидается объём ввода-вывода для поиска по индексу или просмотра по нему?
-
Имеет ли индекс ключи, подходящие для оценки предикатов запроса?
-
Насколько селективен предикат? (То есть, каков процент от общего числа строк в таблице квалифицируется этим предикатом? Чем меньше — тем лучше).
-
Покрывает ли индекс все необходимые столбцы?
В этой статье, я собираюсь дать несколько примеров того, как перечисленные выше факторы взаимосвязаны.
Схема
Я буду использовать представленную ниже схему для всех последующих примеров:
Если Вы собираетесь повторять примеры из этой статьи, я заполнял таблицу, используя следующий сценарий:
set nocount on declare @i int set @i = 0 while @i < 100000 begin insert T values (@i, @i, @i, @i, @i) set @i = @i + 1 endПример по вводу-выводу
Рассмотрим следующий запрос:
select a, b from TВ этом запросе нет предложения WHERE, так что будет использоваться просмотр. Однако, есть два индекса, которые могут использоваться для просмотра. Это кластеризованный индекс «Ta» и некластеризованный индекс «Tb». Оба этих индекса покрывают столбцы «a» и «b», но, следует заметить, что кластеризованный индекс также покрывает столбцы «c» и «x». Поскольку столбец «x» имеет тип char (200), суммарная длинна каждой строки в кластеризованном индекс получается больше 200 байт, и на каждой 8 КБ странице умещается менее 40 строк, т.е. для индекса потребует более 2500 страниц для хранения наших 100000 строк. Напротив, суммарная длинна каждой строки в некластеризованном индексе составляет 8 байт плюс небольшой довесок, при этом на каждой странице помещаются сотни строк, и индексу потребуется менее 250 страниц для хранения всё тех же 100000 строк. В нашем случае, при использовании просмотра по некластеризованному индексу для исполнения запроса потребуется много меньше операций ввода-вывода.
Таким образом, лучшим планом исполнения запроса будет:
|—Index Scan(OBJECT:([T].[Tb]))
Обратите внимание, что для сравнения индексов в SQL Server 2005 можно использовать системное административное представление dm_db_index_physical_stats:
select index_id, page_count from sys.dm_db_index_physical_stats (DB_ID('tempdb'), OBJECT_ID('T'), NULL, NULL, NULL) index_id page_count ----------- -------------------- 1 2858 2 174 3 223 4 223Также можно использовать статистику ввода-вывода и подсказки индексов, что позволяет сравнить объёмы ввода-вывода для двух разных планов:
set statistics io on select a, b from T with (index(Ta))Table ‘T’. Scan count 1, logical reads 2872, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
select a, b from T with (index(Tb))Table ‘T’. Scan count 1, logical reads 176, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Пример по селективности
Рассмотрим ещё один запрос:
select a from T where c > 150 and c < 160 and d > 100 and d < 200В этом запросе есть два предиката, которые могут использоваться для поиска по индексу. Можно использовать предикат по столбцу «c» с некластеризованным индексом «Tcd», или предикат по столбцу «d» с некластеризованным индексом «Tdc» (почитайте мою статью про предикаты поиска, в которой объясняется, почему нельзя использовать индекс по одному столбцу, чтобы удовлетворить условия обоих предикатов неравенств).
Оптимизатор будет анализировать данные о селективности двух указанных предикатов, и на основании этого определит, какой индекс он будет использовать. Предикат по столбцу «c» извлечёт 9 строк, в то время как предикат по столбцу «d» извлечёт 99 строк. Таким образом, дешевле искать по индексу «Tcd» и оценивать остаточный предикат по столбцу «d» для девяти строк, чем искать по индексу «Tdc» и оценивать остаточный предикат по столбцу «c» для 99-ти строк.
Ниже представлен план исполнения запроса:
|—Index Seek(OBJECT:([T].[Tcd]), SEEK:([T].[c] > (150) AND [T].[c] < (160)),
WHERE:([T].[d]>(100) AND [T].[d]<(200)) ORDERED FORWARD)
Пример на тему: «Поиск против просмотра»
Рассмотрим такие два запроса:
select a from T where a between 1001 and 9000 select a from T where a between 101 and 90000Можно ожидать, что для первого запроса оптимизатор выберет кластеризованный индекс, стремясь удовлетворить условие предиката по столбцу «a». Вот каков его план:
|—Clustered Index Seek(OBJECT:([T].[Ta]),
SEEK:([T].[a] >= CONVERT_IMPLICIT(int,@11],0)
AND [T].[a] <= CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)
Обратите внимание на параметры этого плана, они такие из-за автоматической параметризации. Когда этот план будет выполняться, @1 примет значение 1001, а @2 примет значение 9000.
Для второго запроса, вместо поиска по кластеризованному индексу, оптимизатор выбирает просмотр некластеризованного индекса «Tb» и использует остаточный предикат для предложения WHERE. Снова, вот его план:
|—Index Scan(OBJECT:([T].[Tb]), WHERE:([T].[a]>=(101) AND [T].[a]<=(90000)))
Почему так произошло? Предикат первого запроса отбирает 8000 из 100000 строк; это — приблизительно 8 % от всей таблицы или приблизительно 230 страниц кластеризованного индекса. Предикат второго запроса выбирает 89000 строк; это почти 90 % от всей таблицы и если бы использовался кластеризованный индекс, это означало бы обработку более 2500 страниц. Для сравнения с этим, можно взять полный просмотр некластеризованного индекса «Tb», во время которого обработано было бы всего 174 страниц. Таким образом, оптимизатор выбирает тот план, для которого потребуется значительно меньший объём ввода-вывода.
Пример на тему: «Поиск закладок против просмотра»
Рассмотрим ещё два запроса:
select x from T where b between 101 and 200 select x from T where b between 1001 and 2000Мы снова имеем два плана на выбор. Возможен просмотр непосредственно кластеризованного индекса, с применением предиката по столбцу «b» как остаточного, или возможно использование некластеризованного индекса «Tb» с поиском для предиката по столбцу «b» и последующим поиском закладок в кластеризованном индексе для получения значений столбца «x» каждой квалифицированной строки. В статье о bookmark lookup, я уже подчёркивал, что поиск закладок носит характер случайного ввода-вывода, что обходится очень дорого. Таким образом, план с использованием поиска закладок можно считать хорошим планом, только если предикат поиска будет селективным.
Первый запрос обрабатывает 100 строк, и оптимизатор принимает решение, что использование закладок (bookmark lookup) является предпочтительным:
|—Nested Loops(Inner Join, OUTER REFERENCES:([T].[a], [Expr1005]) …)
|—Index Seek(OBJECT:([T].[Tb]), SEEK:([T].[b] >= (101) AND [T].[b] <= (200)) …)
|—Clustered Index Seek(OBJECT:([T].[Ta]), SEEK:([T].[a]=[T].[a]) LOOKUP …)
Второй запрос обрабатывает 1000 строк. Хотя это всего 1 % от всей таблицы, оптимизатор решит, что 1000 операций случайного ввода-вывода обойдутся дороже, чем 2800 операций последовательного ввода-вывода, и выберет просмотр кластеризованного индекса:
|—Clustered Index Scan(OBJECT:([T].[Ta]), WHERE:([T].[b]>=(1001) AND [T].[b]<=(2000)))
ссылка на оригинал статьи https://habr.com/ru/post/655689/
Добавить комментарий