«Покрывающие» индексы и SQL Server 2005
Ни для кого не секрет, что индексы могу существенно ускорить выполнение запросов. И если мы часто используем запрос с условием WHERE my_filed=@my_value, то имеет смысл создать индекс по полю my_field. Но индексы бывают разные и иногда их можно использовать для ускорения запросов, даже если поле не входит в условие фильтрации.
Рассмотрим следующий запрос к базе данных AdventureWorks (это стандартная база данных пример в поставке SQL Server 2005):
SELECT DueDate, ShipDate FROM Sales.SalesOrderHeader WHERE OrderDate='2001-07-01'
По умолчанию индексов связанных с полем OrderDate у нас нет, и, поэтому выполняется полное сканирование таблицы (т.к. в таблице есть кластерный индекс то выполняется его сканирование). План будет выглядеть следующим образом:
, а оценочная стоимость запроса 0,55.
Добавим индекс:
create index IDX_OrderDeatils on Sales.SalesOrderHeader(OrderDate)
и повторим запрос. План показан ниже, а оценочная стоимость 0,13:
В это случае поиск нужной записи занимает 2% времени, а 97% занимает извлечение данных (полей DueDate, ShipDate) из таблицы.
Что можно сделать в качестве альтернативы?
Можно создать так назывемые покрывающий (covered) индекс:
create index IDX_OrderDeatils on Sales.SalesOrderHeader(OrderDate, DueDate, ShipDate)
план запроса показан ниже, а оценочная стоимость 0,0033
В этом случаем нам не нужно после нахождение строки в индексе дополнительно вытаскивать данные из самой таблицы.
Выигрыш можно получить даже если не использовать WHERE часть. Для запроса:
SELECT DueDate, ShipDate FROM Sales.SalesOrderHeader
Мы будем сканировать только маленький индекс, а не всю таблицу (оценка 0,13 против 0,55):
Какие недостатки у этого подхода:
- Увеличается размер индекса. И мы можем уперется в 900б органичение на длину ключа
- При каждом изменении полей DueDate, ShipDate возможна существенная перестройка индекса. И выигрыш в выполнении SELECT, может оказатся незаметным на фоне замедления операций обновления.
- Мы не можем таким образом ускорить работу с полями для которых нельзя строить индекс, например, nvarcahr(,ax)
В SQL Server 2005, есть одно маленькое, но очень приятное расширение – ключевое слово INCLUDE. В этом случае создание индекса будет выглядеть следующим образом:
create index IDX_OrderDeatils on Sales.SalesOrderHeader(OrderDate) include( DueDate, ShipDate)
При этом план запроса будет таким же как и для простого покрывающего :
- Поля DueDate и ShipDate будут включатся только на самом последнем уровне индекса, что уменьшает его размер. Что приводит к повышению скорости чтения и модицикации.
- Можно включать поля любого типа (даже те для которых нельзя использовать индексы, в том числе nvarchar(max) и xml) и любой длины (больше нет ограничения на 900б)