Зачем использовать INCLUDE в индексе SQL

Я недавно столкнулся с индексом в базе данных, которую я поддерживаю, которая имела форму:

CREATE INDEX [IX_Foo] ON [Foo]
( Id ASC )
INCLUDE 
( SubId )

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

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

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

Единственное, о чем я могу подумать, это то, что при наличии обновлений в SubId строку не нужно будет перемещать, если столбец включен (хотя значение в индексе нужно будет изменить). Есть что-то еще, что я пропускаю?

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

Меня в первую очередь интересует MS SQL Server, но приветствуется и информация о других движках БД.

Источник

3 ответа

Решение

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

В вашем случае у вас есть стол Foo и некоторые поля, в том числе Id (который я предполагаю, является первичным ключом), и SubId какой-то дополнительный идентификатор какой-то.

У вас также есть индекс IX_Foo который я предполагаю, имел только Id в этом сейчас.

Так что теперь вам нужно найти SubId за Id=4,

SELECT Id, SubId
FROM Foo
WHERE Id=4
  • SQL Server посмотрит на оператор SELECT и определит, что он может использовать IX_Foo
  • Затем он будет искать значение Id=4 в вашем индексе IX_Foo
  • когда он находит это, теперь ему нужно значение SubId, тоже
  • некластерный индекс IX_Foo будет содержать значение ключа кластеризации
  • используя это значение ключа кластеризации, SQL Server выполнит "поиск закладок", чтобы найти фактическую страницу данных, где находится вся строка данных
  • он извлечет эту страницу и извлечет значение для SubId от него
  • он вернет эти значения, чтобы удовлетворить ваш запрос

Главное здесь: как только SQL Server найдет ваш Id=4 в IX_Foo Индекс, затем потребуется выполнить еще одну операцию ввода-вывода, поиск закладок, чтобы получить всю строку данных, чтобы найти SubId значение.

Если у вас есть индекс покрытия, например, IX_Foo также включает в себя SubId, что дополнительный ввод / вывод для поиска закладок исключен. После того, как значение Id=4 находится в IX_Foo index, эта страница индекса в вашем некластеризованном индексе также будет включать значение SubId - SQL Server теперь может возвращать те два значения, которые вы запрашивали в своем запросе SELECT, без необходимости выполнять дополнительный (потенциально дорогой и, следовательно, медленный) поиск закладок, чтобы просто получить другой столбец Id.

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

ОБНОВЛЕНИЕ: компромисс заключается в следующем: если у вас есть индекс на (Id, SubId), все страницы в индексе имеют оба столбца - все дерево индекса до конца.

Если вы включаете (SubId), поля SubId присутствуют только на уровне листа.

Это означает

  • SQL Server не может искать и сравнивать по SubId (значения не находятся в дереве индекса)
  • используется меньше места, так как значения находятся только на уровне листа
Источник

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

Причина, по которой вы, возможно, не захотите делать этот дополнительный столбец частью "правильного индекса", заключается в том, что при вставке или обновлении этого столбца вам, скорее всего, потребуется пересортировать части индекса.

Источник

Использование include в индексе позволяет использовать индекс в качестве покрывающего индекса (т. Е. Некоторые запросы могут быть выполнены с использованием только этого индекса, без необходимости выполнять поиск закладок в кластеризованном индексе), без добавления этих столбцов к фактической части дерева индекс, таким образом, сохраняя размер индекса вниз. (Включенные столбцы добавляются только в конечные узлы индекса).

Источник
Другие вопросы по тегам