SQL-Ex blog

CREATE INDEX IX_LastAccessDate_Id_Includes

  ON dbo.Users(LastAccessDate, Id)

  INCLUDE (DisplayName, Age);

GO

CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age

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

GO

Я также говорил, что листовые страницы каждого индекса будут выглядеть одинаково:


С точки зрения пространства, которое они занимают на листовых страницах, не имеет значения, находятся столбцы в ключе индекса или же являются включенными столбцами. Они будут занимать одно и то же место на листовых страницах. Чтобы в этом убедиться, запустите sp_BlitzIndex и посмотрите на эту таблицу:


Обратите внимание, что два выделенных индекса внизу - это те два индекса, между которыми я предлагал вам сделать выбор - занимают 12,4Мб и 12,5Мб. Разница в размере обусловлена нелистовыми страницами дерева, которые помогают SQL Server достичь конкретной листовой страницы.

Почему-то люди думают, что включенные колонки дешевы.

Я не знаю, почему так случилось, но вокруг столбцов INCLUDE сложилась легенда о том, что они почему-то работают быстро и стоят недорого. Чтобы понять причину, рассмотрим следующий запрос:

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. Позже - асинхронно - записать измененные страницы данных на диск.

Что вы говорите? Вы не видите всего этого в плане? Ну, много всего скрыто в первичной информации, и вы должны навести мышку на различные всплывающие подсказки. Вот, например, обновление некластеризованного индекса, спрятанное в обновлении кластеризованного индекса:

Требуют ли включенные столбцы меньше работы?

Вы должны сказать: взгляните на этот контрольный список из 10 пунктов и ответьте, что изменится, если бы мы использовали этот индекс:

CREATE INDEX IX_LastAccessDate_Id_Includes

  ON dbo.Users(LastAccessDate, Id)

  INCLUDE (DisplayName, Age);

И запустили такой конкретный запрос:

UPDATE dbo.Users

  SET Age = Age + 1

  WHERE Id = 643;

Я дам вам подсказку: наш пользователь находится наверху этой серой страницы:


Теперь вы можете самостоятельно поработать над запросом с ручкой и бумагой, чтобы выяснить, насколько было бы лучше, если бы DisplayName и Age были только включенными столбцами, а не частью ключа. Вы можете высказать свои соображения в комментариях, а я рассмотрю их в следующей статье этой серии.