Header RSS-подписка на обновления сайта eMail-подписка на обновления сайта

К вопросу уникальности индексов. Часть 2/4.













Итак, мы можем двигаться далее, или, с учетом «по-этажной» структуры индекса, более корректно будет сказать «вниз». Сначала исследуем «левый» «путь поиска», т.е. ознакомимся с содержимым первой (из двух) страницы промежуточного уровня все того же кластерного индекса CL_uq_Index:

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 94, 3) --cluster idx, non-leaf level(1)

Видим примерно следующее (приведены начальные и конечные строки резалт-сета):

PageId  Level   ChildPageId col1 (key)
94      1       80          NULL
94      1       109         10476
94      1       115         10952
94      1       119         11428
94      1       121         11904
...     ...     ...         ...
94      1       573         114720
94      1       574         115196
94      1       575         115672
94      1       584         116148
94      1       585         116624

Самое интересное, что если вы разобрались со структурой root-страницы, то эта первая из двух страниц промежуточного уровня вообще не требует никаких комментариев. Все совершенно аналогично, только «нарезка» диапазонов пошла более гранулированной, вот и все. Как видно, на этом уровне первый диапазон поиска будет от «минус бесконечности» до 10476, что уже очень недалеко от первой строчки нашей таблицы T1 с ее ключом 10000 ровно. А последний диапазон будет от 116624 и до, формально говоря, «плюс бесконечности». А на самом деле — до 117099. Потому как если значение искомого ключа превысит указанное значение на страницу 94 мы и вовсе не попадем — согласны?

Вот так мы очень быстро разобрались со страницей уровня 1. Это все потому, что страницы индекса всех уровней кроме листового концептуально просто копируют друг-друга. Обсудив один уровень можно не делать тоже самое с уровнями оставшимися. Но теперь у нас совсем иной вопрос! На «сцену» выходит первая страница как-раз таки листового уровня:

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 80, 3) --cluster idx, leaf level(0)

Тут полюбившаяся нам команда DBCC PAGE несколько нас «подводит». Нет, возвращаемая ею информация совершенно корректна и относится к нужной странице, но вот никакого табличного вывода, к которому мы привыкли на не-листовых уровнях, нет и в помине. К нашим услугам лишь представление страницы на уровне слотов (они же записи, они же row). Вот как выглядит первый из таковых:

Slot 0 Offset 0x60 Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 15

Memory Dump @0x0000000012C2A060

0000000000000000:   10000c00 10270000 20a10700 020000††††.....'.. ¡.....  

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

col1 = 10000                        

Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4

col2 = 500000                        

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (8f2db235afd3)

В целом, выудить нужную информацию несложно. Мы видим, что каждый слот (запись) занимает на этом уровне ровно 15 байт (см. поле Record Size), и эта информация поможет нам в дальнейших исследованиях. Ну а самое главное — в записи содержится и ключ, и значение колонки col2, как и обещал ответ второй «контрольной работы». Для данного слота col1 (ключ) равен 10000, col2 (с точки зрения кластерного индекса — обычная колонка) — 500000. То есть это самая первая строка таблицы T1. В каждом последующем слоте значения обоих колонок возрастают на единицу, как то и предписывал цикл эти строки вставляющий.

Если мы пролистаем отчет по 80-й странице до конца, то легко убедимся, что последний слот имеет номер 475 и содержит запись с ключом 10475 (ну и соответствующую ему col2=500475, разумеется). А отчет по странице 94-й («родительской» для 80-й) говорит нам, что первой записью на следующей странице, 109-й («сестринской» для 80-й), будет запись с ключом 10476. Что как нельзя лучше согласуется с окончанием страницы 80-й. Ну и так далее. Если мы возьмем последнюю страницу листового уровня исследуемого нами в настоящий момент «левого» пути поиска (а номер ее, как вы понимаете, будет 585-й), а на ней последний слот, то мы увидим там:

Slot 475 Offset 0x1c35 Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 15

Memory Dump @0x00000000132CBC35

0000000000000000:   10000c00 6bc90100 7b430900 020000††††....kÉ..{C    ....  

Slot 475 Column 1 Offset 0x4 Length 4 Length (physical) 4

col1 = 117099                        

Slot 475 Column 2 Offset 0x8 Length 4 Length (physical) 4

col2 = 607099                        

Slot 475 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (31e5e6cf864d)

То есть последняя запись этого пути содержит ключевое значение 117099. А если нам нужен ключ 117099+1 (или более) — так это надо было еще в корне «направо» идти. Однако исследование этого пути автор оставляет своим читателям в виде «домашней работы». После столь подробно разобранного пути «левого» такая работа не может представлять никаких проблем — все совершенно аналогично. А проделав такую работу мы можем смело записываться к самым именитым SQL-писателям, если не в соавторы, то уж в «со-иллюстраторы» определенно:

ClusterIndex

Как бы ни были хороши чужие изображения, а нарисованное собственноручно — оно понятнее. Тем более когда рисунок делался с самой что ни на есть «натуры». Вот он — наш кластерный индекс CL_uq_Index, во всем великолепии своих трех уровней.

Теперь обратимся к индексу не-кластерному, NONCL_uq_Index. Как вы помните (а если запамятовали — освежите в памяти) этот индекс состоит из всего двух уровней — корневого и сразу же листового. Обратимся к странице root-уровня:

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 110, 3) --non-cluster idx, root level(1)

Сокращенный отчет имеет вид:

PageId  Level   ChildPageId col2 (key)
110     1       90          NULL
110     1       114         500578
110     1       118         501156
110     1       120         501734
110     1       126         502312
...     ...     ...         ...
110     1       1117        747384
110     1       1118        747962
110     1       1119        748540
110     1       1128        749118
110     1       1129        749696

Первое на что стоит обратить внимание, так это на факт наличия множества записей (читай — «путей поиска») прямо на корневом уровне. Как пояснялось в ответах на один из контрольных вопросов некоторые почему-то полагают что на этом уровне движок может «пойти» лишь «налево» или «направо». Как видите — отнюдь, по «диагонали» ходы на этом уровне вполне возможны. Второй интересный факт заключается в том, что если закрыть самую правую колонку последнего резалт-сета (col2 (key)) то вы не сможете определить, содержит ли страница 110-я записи кластерного или не-кластерного индекса. Это все потому, что оба типа индекса опираются на одну и ту же концепцию сбалансированного дерева, причем именно для рассматриваемых в настоящий момент уникальных индексов и реализация этой концепции совпадает на 100%. Так что добавить к последним разъяснениям относящимся к кластерному индексу решительно нечего, за исключением одного очень существенного, но безусловно всем хорошо известного факта: если взять любую страницу не-кластерного индекса листового уровня (допустим — 90-ю, в нашем примере) и посмотреть ее содержимое, то, во-первых, DBCC PAGE вернет нам табличное ее представление. А не «слотовое», как было в случае с кластерным индексом. Например у автора команда

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 90, 3) --non-cluster idx, leaf level(0)

вернула:

PageId  Level   col2 (key)  col1
90      0       500000      10000
90      0       500001      10001
90      0       500002      10002
90      0       500003      10003
...     ...     ...         ...
90      0       500575      10575
90      0       500576      10576
90      0       500577      10577

А, во-вторых, как хорошо видно из последнего результата, листовой уровень не-кластерного индекса включает не только его ключ (col2 (key)), но и ключ кластерного индекса (col1), который ключом для индекса обсуждаемого не является (еще раз вспоминайте точное определение термина ключ индекса). И что подтверждается отсутствием слова «key» в заголовке последней колонки. В общем-то, появление именно на этом уровне значений кластерного индекса — это такая «SQL-азбука», что объяснять ее нужды нет, мы бы просто очень, очень удивились не найдя на уровне 0 не-кластерного индекса упоминаний о колонке col1. Важно другое. Оба подмеченных нами факта говорят вот о чем: листовые страницы кластерного индекса содержат данные. А те же страницы не-кластерного индекса содержат все еще индексные записи. Что бы глубже проникнуть в эту мысль сделайте пару запросов к вспомогательной таблице Tsup:

1
2
SELECT PagePID,IndexID,PageType,IndexLevel FROM Tsup WHERE PageType=1
SELECT PagePID,IndexID,PageType,IndexLevel FROM Tsup WHERE PageType=2

и проанализируйте — какие страницы, каких индексов и каких уровней движок «засчитывает» за страницы данных (PageType=1), и какие — за страницы индекса (PageType=2). Все понятно? Уровень 0 кластерного индекса предназначен для финальной выборки данных, тот же уровень индекса не-кластерного — для продолжения поиска (разумеется, есть и исключения — значения кластерного индекса, included колонки и т.п. можно «брать» прямо с этого уровня; и все же в первую очередь этот уровень не-кластерного индекса ориентирован на продолжение процесса, а не на его финальный шаг). Если этот важный момент вами как следует осознан — отлично и едем дальше!

Очередной вопрос. Вот страницы не-листовых уровней кластерного индекса содержат диапазоны (в виде значения ключа этого индекса) и ссылки на дочерние страницы. И страницы тех же уровней не-кластерного индекса содержат тоже самое, только ключ уже будет не col1, а col2. Но для задаваемого вопроса важно что оба ключа имеют один и тот же размер/тип — int. Ну а размеры указателей на страницу не могут быть различны. Как мы убедимся через пару абзацев это всегда 6-ти байтовая структура и никак иначе. Далее, на листовом уровне кластерного индекса никаких ссылок уже нет, а есть только две упомянутых колонки, каждая типа int. Но и в не-кластерном индексе на том же уровне все тоже самое! Никаких указателей, две колонки типа int... А спрашивается в задаче: если все настолько симметрично, а так же с учетом что, очевидно, число строк в каждом индексе так же совпадает — как так выходит, что индекс не-кластерный «влез» всего в два уровня (и у него еще место на этих уровнях осталось), а кластерному те же рамки оказались малы, и он был вынужден «надстроить» еще один «этаж»? Сразу скажу, что вопрос не банальный, «мозговой штурм» тут вряд ли поможет. Если вам нравятся «SQL-ребусы» и вы хотите проверить себя в решении «не детских» задач — поставьте чтение статьи на «паузу» и попытайтесь найти верный ответ самостоятельно, все инструменты для такого поиска у вас, уверяю, есть. Ну а мы, тем временем, посмотрим как к той же задачке «подступился» автор.

Обдумав указанную дилемму, первое что приходит в голову, так это мысль: несмотря на кажущуюся полную «конгруэнтность» индексов обоих типов все же их отдельные записи (слоты) имеют различный размер. Запись индекса кластерного должна быть по числу байт ее составляющих несколько больше чем запись индекса противоположного. Вот тогда положение вещей наблюдаемое нами впишется в естественную природу мира, более ёмким записям нужно больше места — логично? Правда пока эта мысль не только ничем не подтверждается, но и даже скорее наблюдаемое нами до сих пор говорит против нее. Давайте вновь вернемся к страницам наших индексов, но на этот раз будем работать с ними на уровне байтов, что бы видеть — как и из чего состоит каждый слот (запись) такой страницы. Вновь начнем с «корня» кластерного индекса (обратите внимание, что я изменил последний параметр команды DBCC PAGE с 3 на 1):

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 1091, 1) --cluster idx, root level(2)

Вполне ожидаемо, на этой странице всего 2 слота:

Slot 0, Offset 0x60, Length 11, DumpStyle BYTE

Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 11

Memory Dump @0x000000001DD9C060

0000000000000000:   06000000 005e0000 000100†††††††††††††.....^.....      

Slot 1, Offset 0x6b, Length 14, DumpStyle BYTE

Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 14

Memory Dump @0x000000001DD9C06B

0000000000000000:   166cc901 00440400 00010001 0000††††††.lÉ..D........

Мы видим, что первый слот занимает 11 байт, второй — 14. Уже непонятно — один и тот же индекс, на одном уровне, а записи имеют различную структуру? Но ладно, рассмотрим байты составляющие каждый слот «под микроскопом». Для вашего удобства я сгруппирую записи не в привычные «четверки», как это делает отчет команды DBCC PAGE, а в группы по назначению каждого набора байт. Тогда содержимое слота 0 запишется так:

06 00000000 5e000000 0100

А вот и «дешифровка» записи:

  • 06 — байт статуса. Довольно «многоцелевая» вещица, где каждый бит чего-нибудь, да значит. Для нас важно лишь такие сообщения от этих бит: что данная запись, т.е. запись слота 0 — индексная (биты 3-1 образуют цифру 3), и что запись не предусматривает так называемую NULL bitmap (бит 4 очищен). Ситуация несколько парадоксальная: отсутствие указанного битмапа, говорит что в данной записи значения NULL не может быть в принципе, чисто физически. Но на самом деле, именно в этой записи, как мы знаем из наших предыдущих изысканий, NULL не только может, но и гарантированно будет, т.к. запись относится к одному из двух специальных случаев: левая граница самого первого диапазона. И мы с вами этот NULL наблюдали, когда изучали ту же 1091-ю страницу, но в табличном виде. Вот тогда у нас в колонке col1 (key) красовался вполне себе полноценный NULL, и вдруг — его быть не может. Ну да, в SQL Server далеко не все интуитивно ожидаемое соответствует реальности, полным-полно и нюансов, куда ж без них. Вот это — один из них. Отсутствие NULL bitmap в первой (и только первой!) записи индекса говорит не о том, что ключи этой записи будут заполнены реальными значениями, а говорит ровно о противоположном — что в этой записи все (!) ключи будут NULL и только NULL без всяких вариантов. Потому что это — что? Правильно, «особый случай» и «левая граница»;
  • 00000000 — это, скажем так, место где может (и будет) появляться значение ключа, она же граница диапазона. Однако поскольку у нас случай особый для этой записи индекса (снова — только первой!) конкретное содержимое этого поля неважно, движок знает, что ключ в этом месте может быть NULL и только NULL. Так что показанные нули в объеме 4-х байт не более чем «заполнители» пространства. А вот начиная с записи второй они обретут вполне себе осмысленный вид;
  • 5e000000 — или, говоря «по-десятичному», 94. Номер дочерней страницы, разумеется. Если вдруг вас удивляет как из шестнадцатеричного числа 0x5e000000 вдруг получилось всего 94 то прочтите в части 6-й статьи «Понимаете ли вы коллейшены?» на этом блоге о порядке байт называемом little-endian;
  • 0100 — или, снова переводя к десятичному формату, просто 1. Дело в том, что указатель на дочернюю страницу состоит не из одной части, а из двух: «номер файла»:«номер страницы». По причинам изложенным в начале статье номер файла мы полностью игнорируем на протяжении всего данного материала — он, в нашем случае, будет гарантировано 1. Тем не менее на структуру записи эта наша договоренность никак не влияет и каждый pointer занимает всегда ровно 6 байт: 2 на файл и 4 на страницу. Именно такой размер указателя обещал вам автор парой абзацев выше.

Вот и все 11 байт первой индексной записи. Перейдем к «препарированию» слота номер 1:

16 6cc90100 44040000 0100 0100 00

Снова — «дешифровка»:

  • 16 — снова байт статуса, где 6-ка снова нам говорит что это запись именно индекса, а не данных. Но есть и отличие: бит 4 установлен а это значит что в конце записи обязана фигурировать NULL bitmap. Это представляется логичным, ведь col1 не отрицает возможность приема такого значения, мы же не объявляли эту колонку с квалификатором NOT NULL. А значит хотя бы одна строка (у нас уникальный кластерный индекс по col1 — не забыли?) с таким ключом может иметь место;
  • 6cc90100 — или 117100 десятичное. Значение ключа и оно же — граница диапазона. Тут уже никаких бессмысленных «заполнителей», вполне нужное и важное значение;
  • 44040000 — как и выше, дочерняя страница. На этот раз 1092-я;
  • 0100 — совершенно аналогично предыдущему, 1-й файл данных;
  • 0100 — эти два байта указывают число колонок составляющие ключ. В нашем случае, понятно, единственная колонка, col1;
  • 00 — и завершается запись обещанным ранее NULL bitmap. Значение 0 говорит что ни в одной из колонок (а один байт этого битмапа «обслуживает» сразу 8 колонок таблицы, или, более технически точно — до 8-ми включительно) нет значения NULL. Или, говоря языком «человеческим», наша единственная колонка содержит «реальное» значение, и мы даже знаем какое — 117100.

Конечно, две разобранные записи имеют гораздо больше сходства, но и различий у них «днем с огнем» искать не придется:

  • первая запись индекса на каждом не-листовом уровне гарантировано не нуждается в NULL bitmap по причинам изложенным ранее. Вторые и последующие записи в нем нуждаются, если только колонки составляющие индекс не объявлены с квалификатором NOT NULL каждая. Еще раз отметьте себе, что «первая запись» о которой ведет речь первое предложение этого пункта (и пункта следующего так же) это каждая первая запись каждого уровня индекса, кроме листового. А не только первая запись в «корне». Одним словом помните об «особом случае» и «левой границе» первого диапазона каждого уровня;
  • первая запись не нуждается ни в каких колонках (в смысле, нам все-равно сколько колонок составляют ключ, одна или десять) и ни в каких их значениях. Все колонки здесь имеют гарантированное значение NULL. Ну, вы знаете — «особый случай» и все такое.

Записи же начиная со второй нуждаются и в первом, и во втором пункте последнего списка. Одним словом, все это ведет вот к какому простому выводу: первая запись индекса каждого уровня несет всего одну «единицу информации»: куда (на какую страницу) следует перемещаться если мы ищем ключи очень малых значений (самые «левые»). А последующие записи указывают и страницу, и конкретный диапазон ключа. И все это выливается в разницу в числе байт там и тут. В нашем случае разрыв совсем невелик, +/-3 байта, но, как вы верно догадываетесь — возможны варианты. Например, если ключ у нас будет из 16 колонок, то и NULL bitmap для их обслуживания будет длинной 2 байта, а не один. Впрочем, подчеркиваемая автором разница в размерах первого и последующих слотов каждого из не-листовых уровней индекса не играет сколь либо заметной роли на практике. Ну да, первый слот меньше на какое-то мизерное число байт — и что? Мы как-то можем на это влиять? Тем не менее автор исходит из посыла что лишних «SQL-знаний» не бывает, даже если «профит» с них не очевиден и не лежит на поверхности.

Очередная «проверочная работа». Если командой

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 94, 1) --cluster idx, non-leaf level(1)

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

Slot 0, Offset 0x60, Length 11, DumpStyle BYTE

Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 11

Memory Dump @0x000000001212A060

0000000000000000:   06000000 00500000 000100†††††††††††††.....P.....      

Slot 1, Offset 0x6b, Length 14, DumpStyle BYTE

Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 14

Memory Dump @0x000000001212A06B

0000000000000000:   16ec2800 006d0000 00010001 0000††††††.ì(..m........

Контрольный вопрос звучит так:

1. «Дешифровать» и своими словами описать — что нам сообщают эти две записи?

Смотреть ответ
Значение ec280000 (10476) из слота 1 говорит, что если ограничиться только этими двумя записями индекса (на самом деле на странице 94-й записей много, много больше, но допустим), то у нас получится всего лишь два диапазона: до 10476 и после. Если искомый ключ попадает в диапазон «до» — следует перейти на страницу 0x50=80, как предписывает запись 0. Если в диапазон «после» — на страницу 0x6d=109, как предписывает запись 1.

Однако пока ни наши изыскания, на даже успешное решение последней «самопроверочной работы» не дают ответ на основной вопрос — почему там 3 «этажа», а тут все в 2 «влезло»? Ведь если командой

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 110, 1) --non-cluster idx, root level(1)

исследовать корневую страницу не-кластерного индекса выяснится картина полностью аналогичная только что изученной: первая запись на 11 байт (со всеми своими «заморочками»), все последующие (у нашего не-кластерного индекса, напомню, только один уровень и, таким образом, только одна «первая» запись) — 14. По прежнему вопрос «а разница-то в чем?» остается актуальным.

Что же — перейдем на листовой уровень? Начнем с первой страницы этого уровня в кластерном индексе:

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 80, 1) --cluster idx, leaf level(0)

Снова первая пара слотов этой страницы:

Slot 0, Offset 0x60, Length 15, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 15

Memory Dump @0x00000000153DA060

0000000000000000:   10000c00 10270000 20a10700 020000††††.....'.. ¡.....  

Slot 1, Offset 0x6f, Length 15, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 15

Memory Dump @0x00000000153DA06F

0000000000000000:   10000c00 11270000 21a10700 020000††††.....'..!¡.....

Сразу же обращает на себя внимание равенство первой и последующих записей. Тут нет никаких расхождений, абсолютно все записи этого уровня устроены одинаково. «Дешифруем» нулевой слот:

10000c00 10270000 20a10700 0200 00
  • 10000c00 — заголовок строки (row header). Причем первый байт заголовка (0x10) является все тем же байтом статуса, как и в индексе. И этот-то самый байт сообщает нам, что:
    • на этот раз мы будем иметь дело именно с data, а не index (биты 3-1 сброшены);
    • NULL bitmap будет фигурировать в конце строки (бит 4 установлен);
  • 10270000 — или 10000 в десятичной системе. Самая первая строка нашей таблицы имеет именно этот ключ. Что же удивляться что она фигурирует как первая (точнее — нулевая) запись первой страницы листового уровня?
  • 20a10700 — или 500000. Разумеется, это col2 соответствующая предыдущему col1;
  • 0200 — число колонок в записи. Если мы умеем считать до двух, то данное значение нас не удивит;
  • 00 — и снова NULL bitmap и его указание на то, что ни одна из колонок NULL-а не содержит.

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

Хорошо, что если взять первую страницу листового уровня не-кластерного индекса

1
2
DBCC TRACEON(3604)
DBCC PAGE([~DB~], 1, 90, 1) --non-cluster idx, leaf level(0)

и «выписать» из нее первую пару слотов:

Slot 0, Offset 0x60, Length 12, DumpStyle BYTE

Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 12

Memory Dump @0x000000001831A060

0000000000000000:   1620a107 00102700 00020000 ††††††††††. ¡...'.....    

Slot 1, Offset 0x6c, Length 12, DumpStyle BYTE

Record Type = INDEX_RECORD           Record Attributes =  NULL_BITMAP     Record Size = 12

Memory Dump @0x000000001831A06C

0000000000000000:   1621a107 00112700 00020000 ††††††††††.!¡...'.....

То «дешифровка» байт из слота 0 дает нам такую информацию:

16 20a10700 10270000 0200 00
  • 16 — и снова байт статуса. Поскольку мы имеем дело вновь с index (биты 3-1 образуют цифру 3) то никакого row header нет и быть не может, это неотъемлемая часть строки данных, а у нас — индекс. Ну а про 4-й бит вы уже знаете, NULL bitmap будет иметь место в конце слота;
  • 20a10700 — или 500000. col2, разумеется;
  • 10270000 — или 10000. Снова все понятно, однако интересно отметить, что в предыдущем, кластерном индексе, в той же строке сначала (физически сначала!) шли 4-е байта col1, затем — столько же байт col2. В индексе текущем — ровно наоборот. Можно сказать, что каждый индекс считает свой ключ «главным» и «продвигает» его на первые позиции записи. Факт забавный, и, в целом, выглядящий логично, но не несущий каких-то практических выгод. Однако автор уже высказывал свое мнение по поводу «бесполезных» знаний.;
  • 0200 — число колонок в записи;
  • 00 — NULL bitmap.

И все последующие записи этого уровня умеют абсолютно идентичную структуру и требую для своего хранения 12 (а не 15) байт. Три байта выгадываются за счет пропуска не-кластерным индексом такой вещи как заголовок строки. А существенна ли эта ничтожна разница? Давайте проведем что-то типа мини-«расследования»:

1
2
3
SELECT index_id AS 'Index', index_level AS 'IndexLevel', record_count AS 'SlotsCount',
page_count AS 'PgCnt',avg_page_space_used_in_percent AS 'PgPercentFull'
FROM sys.dm_db_index_physical_stats (DB_ID('~DB~'),OBJECT_ID('T1'),null,null,'Detailed')

Резалт-сет последнего запроса (он выводит информацию сразу по обоим индексам):

Index   IndexLevel  SlotsCount  PgCnt   PgPercentFull
1       0           250000      526     99.8004447739066
1       1           526         2       51.945885841364
1       2           2           1       0.33358042994811
2       0           250000      433     99.8411292315295
2       1           433         1       85.5324932048431

Что мы видим? Что на листовых уровнях обоих индексов число записей (слотов) ровно как предписал наш цикл — по 250 тыс. Но в случае не-кластерного индекса (Index=2) они заняли 433 data pages, в другом случае — целых 526. Прикинем, создают ли именно 3 байта такую заметную разницу? На 250 тыс. записей не-кластерный индекс «выиграет» 3*250000=750000 байт или примерно 732 килобайта. Для их размещения потребуется дополнительно 732/8=91.5 data pages, чей размер, как всем хорошо известно, всегда и строго 8Кб. Проверяем: 526-433=93. С учетом пары округлений произошедших по ходу расчета — идеальное попадание, все «уперлось» именно в эти три «микроскопических» байта. А если бы строк было 20 млн.? В общем, суть проста — из двух идентичных индексов меньшим по размеру (и значит, как ни крути, более эффективным в работе) будет не-кластерный. Однако, не все теоретические выводы могут выливаться в полезные практические руководства если у нас нет полной картины происходящего (и поэтому, в частности, автор настаивает на принципиальной ошибочности термина «лишние SQL-знания»; никто не знает, когда это «лишнее» подскажет нам верное решение). Например, какое допущение можно было бы сделать из предыдущего посыла? А такое, что, как вариант, на таблице не стоит создавать кластерный индекс вообще, а пользоваться исключительно индексами не-кластерными. Ну, понятно, все проблемы и задачи которые нам подсовывает реальная «IT-жизнь» и ее «SQL-составляющая» объять невозможно, наверно есть случаи когда такой подход — верный. Но в 99% случаев такая, с позволения сказать, «идея» будет иметь одно замечательное (в кавычках, конечно) следствие: резкое увеличение размеров всех (!) не-кластерных индексов, и, конечно же, ухудшение их эффективности. Почему так? Читайте материал до конца, вам все расскажут... :)

Что дальше? А дальше страницам первого (считая «снизу», со стороны «листьев») не-листового уровня кластерного индекса нужно сослаться на 526 дочерних страниц, а не-кластерного — лишь на 433. Иными словами нужно разместить на странице/страницах этого уровня (причем на как можно меньшем их числе — никто ж не «подписывал» SQL Server на роль генерального спонсора производителей HDD, хотя подчас сомнения в этом нет-нет, а промелькнут :) ) указанное количество слотов-записей. Попробуем «втиснуть» все на единственную страницу. Так же не забудем, что начиная с обсуждаемого уровня и «выше» размер одного слота от типа индекса не зависит. Как видим, у не-кластерного индекса это получилось без проблем, и более того — около 15% места на этой единственной странице осталось свободным (см. колонку PgPercentFull). Принимая размеры всех слотов эквивалентными друг другу (но только в рамках не-листовых уровней, разумеется) прикинем — сколько слотов «забьют» одну страницу до упора. Решаем несложную пропорцию: 100*433/85=509. Так что извините, граждане дорогие, но 526 слотов содержащих одно целое значение и одну ссылку не уместить на одной странице никакими ухищрениями, хоть что вы делайте. Тут безусловно требуется завести «сестринскую» страницу к этой первой, а над ними, как положено, поставить «начальника» — root-страницу. Похвалим в очередной раз SQL Server: заводя такую «сестринскую» страницу он, по крайней мере, не «забивает» первую страницу «до упора» и не размещает на второй только 3% от общего числа записей. Нет, число записей выравнивается приблизительно поровну, и каждая из двух страниц оказывается заполненной на, примерно, 50%. Что много, много предусмотрительней нежели первый вариант «перекошенного» распределения данных. Да и то сказать, букву B в названии главной концепции построения индексов (B+ tree) надо же «отрабатывать»? ;)

Наконец, нельзя не отметить что бы изменилось в только что разобранном нами процессе наполнения слотов индексных страниц байтами, будь колонки col1/col2 определены с квалификатором NOT NULL. Во-первых, сразу скажу, что «первые записи» не-листовых уровней все-равно содержали бы в этих колонках NULL. Даже при условии кажущейся невозможности такого положения дел. Однако, можете поверить автору, а можете проверить — NULL там будет. А все потому, что опять тот же самый «особый случай». Во-вторых, из слотов начиная со второго и далее будут убраны такие два компонента:

  • двухбайтовый идентификатор числа колонок составляющих ключ индекса;
  • NULL bitmap. Размер этого компонента варьируется, но точно не меньше байта;

Так вот оба этих компонента (а это минимум 3 байта на слот, если не больше) становятся не нужны и вот это уже самые что ни на есть практические знания, сводящиеся к правилу: если у вас нет особых оснований (бизнес-правила, логика создаваемого приложения и т.п.) не помечать колонку NOT NULL — именно так ее и помечайте. Проиграть вы не можете, а вполне конкретный «профит» более чем возможен. Даже если вы не планируете немедленное создание индекса по такой колонке, кто знает, как изменится ваше мнение после годичной эксплуатации готового решения, такую колонку содержащую. Если же колонка обязана допускать NULL значения, то обсуждать нечего, но придется «оплатить» такую вольность известным числом добавочных байт в каждой странице каждого индекса где указанная колонка будет фигурировать в качестве ключа. И только одно исключение из данного правила — листовой уровень кластерного индекса. Вот на этом уровне вы никакого выигрыша от NOT NULL колонок не получаете. По причинам которые обсудить интересно, но которые лежат слишком уж далеко за границами данного материала, на указанных страницах NULL bitmap присутствует всегда, даже если дизайн таблицы тотально отрицает возможность появления NULL значений хоть в одной её строке. Однако для всех прочих страниц описанное правило экономии действует и пренебрегать им не следует.