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

Столбцы базового индекса сортируются, но включенные столбцы не сортируются. Это экономит ресурсы при обслуживании индекса, но при этом позволяет предоставлять данные во включенных столбцах для покрытия запроса. Итак, если вы хотите охватить запросы, вы можете поместить критерии поиска, чтобы найти строки в отсортированных столбцах индекса, но затем «включить» дополнительные, несортированные столбцы с данными, не относящимися к поиску. Это определенно помогает уменьшить объем сортировки и фрагментации при обслуживании индексов.

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

Дополнительное соображение, которое я не видел в уже приведенных ответах, заключается в том, что включенные столбцы могут иметь типы данных, которые не разрешены в качестве столбцов ключа индекса, например varchar (max).

Это позволяет включать такие столбцы в индекс покрытия. Недавно мне пришлось сделать это, чтобы предоставить сгенерированный nHibernate запрос, в котором было много столбцов в SELECT, с полезным индексом.

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

Учитывая ваш пример:

CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)

Этот индекс лучше всего, если ваш запрос выглядит так:

SELECT col2, col3
  FROM MyTable
 WHERE col1 = ...

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

SELECT col2, col3
  FROM MyTable
 WHERE col1 = ...
   AND col2 = ...

SELECT TOP 1 col2, col3
  FROM MyTable
 WHERE col1 = ...
 ORDER BY col2

Предположим, что это не так, и у нас есть col2 в предложении INCLUDE, потому что его наличие в древовидной части индекса просто бесполезно.

Перенесемся на несколько лет вперед.

Вам нужно настроить этот запрос:

SELECT TOP 1 col2
  FROM MyTable
 WHERE col1 = ...
 ORDER BY another_col

Для оптимизации этого запроса подойдет следующий индекс:

CREATE INDEX idx1 ON MyTable (Col1, another_col) INCLUDE (Col2)

Если вы проверите, какие индексы у вас уже есть в этой таблице, ваш предыдущий индекс может все еще там:

CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)

Теперь вы знаете, что Col2 и Col3 не являются частью дерева индексов и поэтому не используются ни для сужения диапазона индекса чтения, ни для упорядочивания строк. Довольно безопасно добавлять another_column в конец ключевой части индекса (после col1). Риск сломать что-либо невелик:

DROP INDEX idx1 ON MyTable;
CREATE INDEX idx1 ON MyTable (Col1, another_col) INCLUDE (Col2, Col3);

Этот индекс станет больше, что по-прежнему сопряжено с некоторыми рисками, но, как правило, лучше расширять существующие индексы, чем вводить новые.

Если бы у вас был индекс без INCLUDE, вы не могли бы знать, какие запросы вы бы нарушили, добавив another_col сразу после Col1.

CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3)

Что произойдет, если вы добавите another_col между Col1 и Col2? Пострадают ли другие запросы?

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

Чтобы ответить на ваш вопрос:

какие рекомендации вы бы предложили при определении того, следует ли создавать покрывающий индекс с предложением INCLUDE или без него?

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

Если добавление столбца к ключу индекса дает дополнительные преимущества (например, для order by или потому, что он может сузить диапазон индекса чтения), добавьте его в ключ.

Вы можете прочитать более подробное обсуждение этого здесь:

https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes

Существует ограничение на общий размер всех столбцов, включенных в определение индекса. Тем не менее, мне никогда не приходилось создавать такой широкий индекс. Для меня большим преимуществом является то, что вы можете охватить больше запросов с помощью одного индекса, который включает столбцы, поскольку их не нужно определять в каком-либо конкретном порядке. Подумайте об этом как об индексе внутри индекса. Одним из примеров может быть StoreID (где StoreID - низкая избирательность, что означает, что каждый магазин связан с большим количеством клиентов), а затем демографические данные клиентов (LastName, FirstName, DOB): если вы просто вставите эти столбцы в этом порядке (StoreID, LastName , FirstName, DOB), вы можете эффективно искать только тех клиентов, для которых вы знаете StoreID и LastName.

С другой стороны, определение индекса для StoreID и включение столбцов LastName, FirstName, DOB, по сути, позволит вам выполнить два предиката поиска индекса для StoreID, а затем предикат поиска для любого из включенных столбцов. Это позволит вам охватить все возможные перестановки поиска, если они начинаются с StoreID.