SQL-Ex blog

UPDATE dbo.Users
  SET Age = Age + 1
  WHERE Id = 643;

то при наличии индекса:

CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age
  ON dbo.Users(LastAccessDate, Id, DisplayName, Age);

то должны были проделать следующее:

1. Ищется пользователь #643 в кластеризованном индексе, выполняя seek - благодаря тому, что наш добрый разработчик включил кластерный первичный ключ в предложении where.
2. (Если соответствующих страниц кластеризованного индекса для нашего пользователя нет в памяти, они запрашиваются с диска)
3. Получить LastAccessDate, id, DisplayName и Age для пользователя #643, поскольку мы можем найти их быстро в сером индексе.
4. Найти эти LastAccessDate, id, DisplayName и Age в IX_LastAccessDate_Id_DisplayName_Age.
5. (Если соответствующих страниц некластеризованного индекса нет в памяти, запросить их с диска.)
6. Заблокировать строки, которые должны быть обновлены.
7. Записать в файл журнала транзакций то, что мы собираемся изменить.
8. Изменить страницу (страницы) данных кластеризованного индекса.
9. Изменить страницу (страницы) данных некластеризованного индекса, включая перемещение строки между страницами, если это потребуется (например, когда значение ключа изменились таким образом, чтобы находиться на совершенно новой странице).
10. Позже - асинхронно - записать измененные страницы данных на диск.

Итак, какие пункты мы можем пропустить, если просто включим столбцы DisplayName и Age?

Являются ли включенные столбцы в некотором отношении легче? Пусть у нас есть такой индекс:

CREATE INDEX IX_LastAccessDate_Id_Includes
  ON dbo.Users(LastAccessDate, Id) INCLUDE (DisplayName, Age);

Давайте сравним с работой, которая требовалась для старого индекса:

1. Ищется пользователь #643 в кластеризованном индексе, выполняя seek - благодаря тому, что наш добрый разработчик включил кластерный первичный ключ в предложении where - мы по-прежнему должны делать это.
2. (Если соответствующих страниц кластеризованного индекса для нашего пользователя нет в памяти, они запрашиваются с диска) - мы по-прежнему должны делать это.
3. Получить LastAccessDate, id, DisplayName и Age для пользователя #643, поскольку мы можем найти их быстро в сером индексе - мы по-прежнему должны делать это, но нам нужны только LastAccessDate и Age - здесь фактически нет сокращения работы.
4. Найти эти LastAccessDate, id, DisplayName и Age в IX_LastAccessDate_Id_DisplayName_Age - мы по-прежнему должны делать это, только мы ищем их в IX_LastAccessDate_Id_Includes.
5. (Если соответствующих страниц некластеризованного индекса нет в памяти, запросить их с диска) - мы по-прежнему должны делать это.
6. Заблокировать строки, которые должны быть обновлены - мы по-прежнему должны делать это.
7. Записать в файл журнала транзакций то, что мы собираемся изменить - мы по-прежнему должны делать это.
8. Изменить страницу (страницы) данных кластеризованного индекса - мы по-прежнему должны делать это.
9. Изменить страницу (страницы) данных некластеризованного индекса, включая перемещение строки между страницами, если это потребуется (например, когда значение ключа изменились таким образом, чтобы находиться на совершенно новой странице) - мы по-прежнему должны делать это - только мы не должны будем перемещать строку на странице...или должны?
10. Позже - асинхронно - записать измененные страницы данных на диск - мы по-прежнему должны делать это.

Еще до того, как я усложню ответ, вы можете видеть, что мы по-прежнему должны выполнить каждый из этих шагов! (Более точно, даже больше шагов, когда мы имеем дело с обновлением, но эти, безусловно, самые крупные, и я сосредоточусь здесь на основной части рабочей нагрузки, не на мелких различиях, а на общей картине). На этих 10 шагах небольшое возможное сокращение дает пункт 9 - возможность пропуска перемещения строк между страницами.

Ну, мы все же сэкономим немного на шаге 9, правильно?

Продолжаем думать, исследуя соответствующие страницы индекса. Пользователь #643 (Sarcastic) находится в правом верхнем углу страницы: Если вы измените возраст пользователя Sarcastic с 30 на 31 год, переместится он на странице? Если вы измените его имя на Optimistic, должны вы будете переместить его? Даже если вы как-то измените его id, тогда вы переместите его? Пока первый столбец или оба первых столбца довольно уникальны, сортировка по третьему, четвертому, пятому и т.д. ключам индекса не так важны, как первый столбец или два столбца в ключах. Это одна из (многих) причин, почему народ часто предполагает, что первый и второй ключи в индексе должны быть высоко селективны: они должны помочь вам быстро сузить пространство поиска. Если этот первый ключ или два изменяются, то, конечно, нам придется перемещать строку. Но для последующих ключей не так уж и часто, при условии, что вы хорошо подобрали первый и второй ключи. Т.е. совершенно безразлично, являются ли 3-й и 4-й столбцы ключами или же включенными столбцами. Однако это, вероятно, не те случаи, с которыми вы сталкиваетесь. В вашей среде из 10 шагов, связанных с обновлением поля, главным вопрос: "Должен ли этот столбец быть ключевым или включенным?", - не является главным.

Главным вопросом является "Должен ли этот столбец вообще быть в индексе?"

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

На заметку: спроектируйте сначала правильные листовые страницы

Когда вы проектируете индекс, задайте себе вопросы:

1. Позволяет ли этот индекс искать только те строки, которые мне нужны, и читать только их и никаких лишних?
   • Если нет, то какие это лишние чтения, и нормально ли это?
2. Дает ли индекс все необходимые мне столбцы?
   • Если нет, какие дополнительные поиски ключей потребуются, и нормально ли это?

Не принимайте решения в пользу ключей или включенных столбцов, пока не ответите на эти первые 4 вопроса. Когда вы ответите на них, то можете начать зацикливаться на ключах против включений. Но я так часто вижу людей, зацикливающихся на том, что поместить во включения, думая, что это как-то должно существенно повысить производительность. В большинстве случаев это не так, но что еще хуже, они не проектировали индекс, чтобы ответить для начала на эти четыре вопроса. И я понимаю, что это сложно, особенно когда кажется, что рекомендации по отсутствующим индексам работают против вас. Мы поговорим об этом позже.