Индексы с включенными столбцами, в чем разница?

Question

Индексы с включенными столбцами, в чем разница?

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

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

Включенный индекс

CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode) 
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

"Нормальный" индекс

CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode, AddressLine1, AddressLine2, City, StateProvinceID);

24

sql sql-server t-sql database-indexes sql-server-performance

Источник

user2808645 22 янв '17 в 13:22

3 ответа

Решение

В первом указателе, в Index page только PostalCode ключевой столбец и AddressLine1, AddressLine2, City, StateProvinceID являются частью листового узла, чтобы избежать key/RID уважать

Я предпочитаю первый индекс, когда моя таблица будет фильтроваться всегда PostalCode и любой из этих столбцов AddressLine1, AddressLine2, City, StateProvinceID будет частью select а не фильтрация

select AddressLine1, AddressLine2, City, StateProvinceID
from Person.Address 
Where PostalCode=

Во втором индексе, в Index page будет пять ключевых столбцов PostalCode, AddressLine1, AddressLine2, City, StateProvinceID

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

Where PostalCode = And AddressLine1 =

или же

Where PostalCode = And AddressLine2 =

или же

Where PostalCode = And AddressLine1  = and AddressLine2 =

и так далее..

В любом случае первый столбец в индексе должен быть частью фильтрации, чтобы использовать индекс

7

Источник

user3349551 22 янв '17 в 13:29

В первом примере в дереве индекса хранится только столбец индекса: PostalCode, а все остальные столбцы хранятся на уровне листа индекса. Это делает индекс меньшим по размеру и полезно, если вы не будете использовать where, Join, group by против других столбцов, но только против PostalCode.

Во втором индексе все данные для всех столбцов хранятся в дереве индексов, это делает индекс намного больше, но полезно, если бы вы использовали любой из столбцов в операторах WHERE/JOIN/GROUP BY/ORDER By.

Включить столбцы ускоряет получение данных, когда они указаны в списке выбора.

Например, если вы работаете:

SELECT PostalCode, AddressLine1, AddressLine2, City, StateProvinceID 
FROM Person.Address 
Where PostalCode= 'A1234'

Это выиграет от создания индекса на PostalCode и включения всех остальных столбцов

С другой стороны, если вы работаете:

SELECT PostalCode, AddressLine1, AddressLine2, City, StateProvinceID 
FROM Person.Address 
Where PostalCode= 'A1234' or City = 'London' or StateProvinceID = 1 or AddressLine1 = 'street A' or AddressLine2 = 'StreetB'

Это выиграет больше от наличия всех столбцов в индексе

Посмотрите на ссылки ниже, они могут помочь больше с вашим запросом

Индекс с включенным столбцом: https://msdn.microsoft.com/en-us/library/ms190806(v=sql.105).aspx
Организация таблиц и индексов: https://msdn.microsoft.com/en-us/library/ms189051(v=sql.105).aspx

2

Источник

user7415753 22 янв '17 в 13:45

Другие вопросы по тегам sql sql-server t-sql database-indexes sql-server-performance

user224704 22 янв '17 в 14:12 2017-01-22 14:12 · Accepted Answer · 2017-01-22 14:12

Внутреннее хранилище индексов использует структуру B-Tree и состоит из "страниц индекса" (корень и все промежуточные страницы) и "страниц данных индекса" (только конечные страницы).

Примечание: не путайте "страницы данных индекса" с "страницами данных" (конечными страницами кластерных индексов), в которых хранится большинство столбцов фактических данных.

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

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

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

Некоторые вещи, на которые стоит обратить внимание, возможно, помогут решить некоторые ваши проблемы:

Если ключи вашего индекса и фильтры в вашем запросе недостаточно избирательны, то индекс будет игнорироваться (независимо от того, что находится в вашем запросе). INCLUDE колонны).
Каждый созданный вами индекс имеет накладные расходы для операторов INSERT и UPDATE; тем более для "больших" показателей. (Больше относится к INCLUDE колонны тоже.)
Поэтому, хотя теоретически вы можете создать множество больших индексов с включаемыми столбцами для соответствия всем перестановкам путей доступа: это будет очень контрпродуктивно.

Стоит отметить, что раньше INCLUDE столбцы были добавлены как функция:

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

NB. Нечто очень важное, на что следует обратить внимание. Вы обычно получаете нулевую выгоду от INCLUDE столбцы в ваших индексах, если вы ленивые привычки всегда писать свои запросы как SELECT * ..., Возвращая все столбцы, вы гарантируете, что поиск закладок необходим в любом случае.