Оптимизация баз данных, индексы

Я заметил, что далеко не многие понимают, как работают индексы в SQL Server и особенно Included Columns, а ведь это отличный способ оптимизации запросов. Я сам сначала не понял, что это за фигня – включаемые колонки, но мои опыты показали, что это очень удобная фишка.

Допустим, что у вас есть следующая таблица и запрос:

CREATE TABLE Person (
 PersonID int,
 FirstName varchar(100),
 LastName varchar(100),
 Age int,
 …
 …
)

SELECT FirstName, LastName, Age
FROM Person
WHERE FirstName = 'Иванов' and LastName = 'Сидоров'

Ясно, что PersonID – это первичный ключ. Допустим, что у вас есть индекс по имени и фамилии и назовем его IX_Person_FirstNameLastName. План выполнения для такого запроса будет следующим:

1. Используя дерево индекса IX_Person_FirstNameLastName все строки с указанными именами и фамилиями.

2. На листиках индекса находим реальное расположение строки на диске, идем на реальное расположение и читаем возраст.

Теперь допустим, что этот запрос выполняется очень часто. Каждый раз приходится выполнять два шага. А можно ли это оптимизировать? В случае с MS SQL Server не проблема. Не знаю на счет других серверов, я их так хорошо не знаю. У SQL Server есть возможность включить значения прямо в индекс. Для этого используется опция INCLUDE.

CREATE INDEX IX_PERSON ON Person
( 
 FirstName,
 LastName
) 
INCLUDE(Age)

Теперь это поле не используется при индексировании, но включено в индекс. Чем это нам грозит? Когда мы индексируем таблицу по какому-то полю, то сервер базы данных должен построить дерево индекса по этому полю. Это значит, что при изменении значения, нужно изменить и дерево индекса. При массовом изменении значений это оказывается достаточно проблематично и тяжело для сервера. При слишком массовом обновлении иногда проще убить индекс. Индекс отлично оптимизирует поиск, но отрицательно сказывается на операциях вставки, удаления и конечно же обновления.

Если поле просто включено в индекс, то оно не используется в построении дерева индекса и не влияет на него, но само значение просто находится на листике этого дерева. Когда происходит поиск по фамилии и имени в нашем случае, то сервер ищет по дереву все имена и фамилии и когда доходит до листика (находит нужное значение индекса), то там помимо указателя на физическое положение значений строки находится и значения полей включенных в индекс. Это значит, что не нужно выполнять второе действие для перехода к физическому положению строки и читать там.

Так как при изменении данных возраста не нужно изменять дерево, то на операциях изменения данных вся эта фигня не сильно сказывается, не нужно изменять индекс, нужно только изменить значения на листике дерева. Поэтому изменение даже массовое поля Age не сильно скажется на производительности. Оно конечно скажется, но не так сильно.

На сколько я помню, значения кластерного индекса автоматом включаются на листовой уровень, но это нужно проверить со спецификацией.

Итак, когда выгодно использование включаемых полей? Когда они часто используются в результатах запроса, но при этом изменяются не так часто. Еще один пример – таблица банковских транзакций. Такая таблица может состоять из полей: номер счета, тип транзакции, дата, сумма. Индексировать по сумме смысла нет, это глупость полнейшая, но ее можно включить в индекс и это реально может ускорить запрос.

Чтобы получить реальный эффект индексации, запросы не должны выбирать все полня, т.е. следует забыть про SELECT * FROM table. Всегда перечисляйте только те поля, которые вам реально нужны. И если все значения этих полей окажутся в индексе, то скорость выполнения может быть достаточно высокой.

Статья получилось немного короткой, но я надеюсь, что вы нашли что-то интересное в ней.

---

Внимание!!! Если ты копируешь эту статью себе на сайт, то оставляй ссылку непосредственно на эту страницу. Спасибо за понимание