Примеры полезности индексов

По материалам статьи Craig Freedman

Чтобы прочитать данные из каждой таблицы запроса, оптимизатор должен выбрать соответствующий путь доступа. При этом для индексов он учитывает несколько факторов, с помощью которых он определяет, нужно ли использовать просмотр (сканирование) или поиск, и понадобится ли делать поиск закладок. Вот некоторые из этих факторов:

1. Какой ожидается объём ввода-вывода для поиска по индексу или просмотра по нему?

2. Имеет ли индекс ключи, подходящие для оценки предикатов запроса?

3. Насколько селективен предикат? (То есть, каков процент от общего числа строк в таблице квалифицируется этим предикатом? Чем меньше - тем лучше).

4. Покрывает ли индекс все необходимые столбцы?

В этой статье, я собираюсь дать несколько примеров того, как перечисленные выше факторы взаимосвязаны.

Схема

Я буду использовать представленную ниже схему для всех последующих примеров:

Если Вы собираетесь повторять примеры из этой статьи, я заполнял таблицу, используя следующий сценарий:

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)))}