Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите понять физический смысл с Include.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Привет.

Открыл для себя ключевое слово Include при создании индекса. Очень интересно. Однако не удалось найти информации по его физической реализации. Помогите понять.

Что гласит документация и различные источники: Если создать индекс и через Include добавить к нему некоторые поля, то "данные поля будут хранится в листовых частях индекса позволяя получить их сразу при сканировании индекса".

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

Не пойму как это устроено физически.Где именно хранятся эти "дополнительные поля" ?

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

А как обстоит дело с индексами, для которых добавлены поля через Include ?
Если они не хранятся в самом индексе, то где? И все равно выходит, что лист индекса хранит некий указатель на "некую кучу" где эти данные находятся. Так в чем выигрышь ?
23 сен 15, 18:21    [18186606]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8823
ProBiotek
А как обстоит дело

Для них в таблицу вообще не надо лезть, просто читать данные поля прямо из индекса.
23 сен 15, 18:24    [18186633]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Не пойму как это устроено физически.Где именно хранятся эти "дополнительные поля" ?

"в листовых частях индекса"

> а затем собирает строчки из кучи.

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

> Если они не хранятся в самом индексе, то где?

хранятся. но только на листовом уровне, в отличие от значений ключевых столбцов - те еще и на не листовых.
23 сен 15, 18:34    [18186690]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
o-o
Guest
ProBiotek
ограничение "индекс не должен превышать 900 байт, и можно использовать поля которые недопустимы в индексе"

Не пойму как это устроено физически.Где именно хранятся эти "дополнительные поля" ?

Читать надо внимательно , тогда не будут выпадать нужные слова из цитат, и ответ найдете в своем же посте (в части, где все слова на месте)
Что за бред про непревышение 900 байт ИНДЕКСОМ?
Вы много индексов видели размером меньше килобайта?
РАЗМЕР КЛЮЧЕВЫХ ПОЛЕЙ не должен превысить 900 байт.
Раньше что было некластерным индексом?
Дерево из ключей + на листовом уровне кроме ключа row locator.
А теперь в некластерный индекс поля можно включить не только как ЧАСТЬ КЛЮЧА.
И храниться они будут только на листовом уровне
23 сен 15, 18:46    [18186767]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Погодите.

Т.е. эти дополнительные поля загружаются в оперативную память сразу вместе с индексом ? Не пойму фразу "Хранятся в листовом узле". Что это значит конкретно на физическом уровне то ?

Ну и в чем различие, чем если бы они были использованы непосредственно в индексе ?
23 сен 15, 19:00    [18186853]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
daw
хранятся. но только на листовом уровне, в отличие от значений ключевых столбцов - те еще и на не листовых.


" те еще и на не листовых"

Вот этого и не понимаю. Какая фиг разница ? Все равно же все загрузится в оперативную память. А там уже какая разница ?
23 сен 15, 19:01    [18186864]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Т.е. эти дополнительные поля загружаются в оперативную память сразу вместе с индексом ? Не пойму фразу "Хранятся в листовом узле". > Что это значит конкретно на физическом уровне то ?

на физическом уровне это конкретно означает, что в каждом слоте на страницах листового уровня хранятся еще и данные include-столбцов.

> Вот этого и не понимаю. Какая фиг разница ? Все равно же все загрузится в оперативную память. А там уже какая разница ?

разница с ключевыми столбцами индекса? ну, длина ключа меньше получается, значит меньше затраты на вставку/обновление (и от ограничения на длину ключа уходим). ну, и несколько меньше читать надо.
23 сен 15, 19:09    [18186908]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
daw,

Ну хорошо. Тогда вопрос в лоб.
Что мешает ВСЮ таблицу занести в этот самый Include ? Ведь все так хорошо получается с этим индексом - даже ограничение на 900 байт отпадает.
Где подвох тогда ?
23 сен 15, 19:10    [18186920]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Ну хорошо. Тогда вопрос в лоб.
> Что мешает ВСЮ таблицу занести в этот самый Include ?
> Ведь все так хорошо получается с этим индексом - даже ограничение на 900 байт отпадает.
> Где подвох тогда ?

да ничего, в общем-то, и не мешает. только размер у получившегося индекса будет такой же, как у таблицы. а по индексам, кроме точечного поиска, бывают еще и сканы - в этом случае узкий индекс выигрывает у широкого.
23 сен 15, 19:25    [18186997]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
ProBiotek
Ну хорошо. Тогда вопрос в лоб.
Что мешает ВСЮ таблицу занести в этот самый Include ? Ведь все так хорошо получается с этим индексом - даже ограничение на 900 байт отпадает.
Где подвох тогда ?
Такой индекс уже есть - называется кластерный. Там как раз ключевые поля на всех уровнях, а все оставшиеся поля таблицы - на листовом.

Ну и можно делать такие индексы ещё, ничего этому не мешает: если для вашей системы, ваших запросов это нужно, и если накладные расходы на обновление множества таких индексов и увеличение места для их хранения не перекроют выгоду при чтении.
23 сен 15, 21:03    [18187379]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
ProBiotek
Какая фиг разница ? Все равно же все загрузится в оперативную память. А там уже какая разница ?
Не всё же мрожно загрузить в ОП, бывают базы и больше, чем ОП :-)

И даже если база помещается в ОП, то в принципе, всё равно считать 10000 записей из 100 страниц индекса быстрее (всего лишь скан блока в 80 КБ памяти!), чем считать 10000 ключей ПК из 100 страниц индекса, а потом 10000 раз выполнить лукап к кластерному индексу - даже если у вас все данные в памяти - всё равно это сотни тысяч операций для каждого из 10000 лукапов.
Это если у вас, например, выборка некоего диапазона по индексу, с ограниченным надором полей.
23 сен 15, 21:11    [18187402]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
alexeyvg,

ээх. Вот бы почитать умные книжки на пальцах расписывающие все эти тонкости...
24 сен 15, 18:32    [18191667]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
ProBiotek
alexeyvg,

ээх. Вот бы почитать умные книжки на пальцах расписывающие все эти тонкости...
Да полно всяких книжек... Начиная от BOL, там всё написано. Вот, например, статейка оттуда, кратко и ёмко: https://msdn.microsoft.com/ru-ru/library/jj835095(v=sql.120).aspx

Но это же простая вещь, вроде всё очевидно.

Индекс - это некая структура, которая хранит ключи (то есть набор полей, по которым будет производиться поиск), в таком виде, что бы можно было быстро всё найти (Б-Дерево).

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

Т.е. вот у вас индекс по полю "Дата Заказа". Вы делаете поиск в запросе - найти записи для даты от... до...
Сервер по индексу находит нужный диапазон, и возвращает вам ИД всех записей, в которых есть "Дата Заказа", соответствующая условию.

Но вам то нужны не ИД записей, а какие то поля, которые вы указали в запросе! Допустим, Клиент, Статус заказа и Сумма.
И тогда сервер проделывает ту же операцию поиска по индексу, но уже по другому - по ПК. Один поиск на каждое найденное значение из списка ИД записей. 10000 записей - 10000 поисков. Вот такой вторичный поиск называется в плане выполнения "лукап".

И вот, что бы не делать кучу таких трудоёмких лукапов, можно сделать INCLUDE полей из наиболее частых запросов к базе. Тогда на конечном уровне индекса, кроме ИД, будут лежать ещё и значения нужных в запросе полей. И тогда лукапов делать не нужно, для большинства запросов.
24 сен 15, 22:35    [18192583]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
o-o
Guest
alexeyvg
И тогда сервер проделывает ту же операцию поиска по индексу, но уже по другому - по ПК. Один поиск на каждое найденное значение из списка ИД записей. 10000 записей - 10000 поисков. Вот такой вторичный поиск называется в плане выполнения "лукап".

совсем необязательно это будет "поиск по другому индексу -- ПК"
на листовом уровне некластерного лежат row locators.
и это либо ключи кластерного индекса, либо RID-ы кучи.
итого, дальнейший поиск будет либо в кластерном индексе, либо в куче
а о ПК о речи не идет вообще.
24 сен 15, 22:59    [18192676]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
o-o
Guest
ProBiotek
Вот бы почитать умные книжки на пальцах расписывающие все эти тонкости...

вот коротко и с картинкой:
Nonclustered Index Structures

а вот наиподробнейше и с картинками на все случаи:
T-SQL Querying By Itzik Ben-Gan, Adam Machanic, Dejan Sarka, Kevin
25 сен 15, 00:28    [18192812]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
o-o
совсем необязательно это будет "поиск по другому индексу -- ПК"
на листовом уровне некластерного лежат row locators.
и это либо ключи кластерного индекса, либо RID-ы кучи.
итого, дальнейший поиск будет либо в кластерном индексе, либо в куче
а о ПК о речи не идет вообще.
Ну, это понятно, я просто упростил. ПК - я подразумевал уникальный кластерный индекс, ну а если куча, то да, берём сразу по RID. Но всё равно будет множество чтений множества разбросанных страниц.

Думаю, для понимания смысла использования INCLUDE в индексах, и для общего осознания, которое нужно иметь перед тем, как начать читать и изучать - такое упрощение вполне нормально.
25 сен 15, 00:36    [18192819]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
o-o
Guest
alexeyvg
Ну, это понятно, я просто упростил. ПК - я подразумевал уникальный кластерный индекс
...
Думаю, для понимания смысла использования INCLUDE в индексах, и для общего осознания, которое нужно иметь перед тем, как начать читать и изучать - такое упрощение вполне нормально.

Я думаю, лучше так не упрощать.
Уникальность кластерного индекса не требуется,
внутренне все индексы уникализируются без нашего участия,
а вот мне бы такое сказали, пришлось бы тут же предъявить пример таблицы со строками (2,1), (2,2),
кластерный по col1, некластерный по col2, можно же такое организовать?
Я знаю примеры товарищей,
считавших, что нельзя соединить 2 таблицы если нет ФК
Поэтому не стОит упоминать ПК, если он не нужен.
По обеим моим ссылкам думают так же:
ПК там не фигурирует
25 сен 15, 07:24    [18192942]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
хе-хе )
Guest
Поля, используемые для поиска (в предикате where, например) делают индексируемый, а поля которые только в списке select можно сделать включенными в индекс (include). Тогда найдя запись по индексу, для селекта ничего делать не надо, данные там лежат уже
Т.е. include используется не для ускорения поиска, а для ускорения получения данных
25 сен 15, 09:34    [18193205]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять физический смысл с Include.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
хе-хе )
Поля, используемые для поиска (в предикате where, например) делают индексируемый, а поля которые только в списке select можно сделать включенными в индекс (include). Тогда найдя запись по индексу, для селекта ничего делать не надо, данные там лежат уже
Т.е. include используется не для ускорения поиска, а для ускорения получения данных


В целом это я понял.

Все мое не понимание - это не понимание конкретных цифр. Мне почему то хочется понять досконально что и как.
Хотелось понять на таком уровне, чтобы я сам, при взгляде на запрос - зная какие там индексы, понимал какой план запроса будет. Причем с учетом селективности индексов. Фактически суметь понять какой индекс будет выбран в каком случае. Ну и т.д. Т.е. просто поднять свой уровень знаний существенно выше.

Вот тут и нужно доскональное понимать Физического представления Кластерных индексов. Или не кластерных но с включенными полями.

Ну ладно. Спасибо за ссылки со схемами. Думаю помогут разобраться.
25 сен 15, 11:19    [18193813]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить