Итак, мы можем двигаться далее, или, с учетом «по-этажной» структуры индекса, более корректно будет сказать «вниз». Сначала исследуем «левый» «путь поиска», т.е. ознакомимся с содержимым первой (из двух) страницы промежуточного уровня все того же кластерного индекса CL_uq_Index:
1 2 | DBCC TRACEON(3604) DBCC PAGE([~DB~], 1, 94, 3) --cluster idx, non-leaf level(1) |
Видим примерно следующее (приведены начальные и конечные строки резалт-сета):
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). Вот как выглядит первый из таковых:
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-й), а на ней последний слот, то мы увидим там:
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-писателям, если не в соавторы, то уж в «со-иллюстраторы» определенно:
Как бы ни были хороши чужие изображения, а нарисованное собственноручно — оно понятнее. Тем более когда рисунок делался с самой что ни на есть «натуры». Вот он — наш кластерный индекс CL_uq_Index, во всем великолепии своих трех уровней.
Теперь обратимся к индексу не-кластерному, NONCL_uq_Index. Как вы помните (а если запамятовали — освежите в памяти) этот индекс состоит из всего двух уровней — корневого и сразу же листового. Обратимся к странице root-уровня:
1 2 | DBCC TRACEON(3604) DBCC PAGE([~DB~], 1, 110, 3) --non-cluster idx, root level(1) |
Сокращенный отчет имеет вид:
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) |
вернула:
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 слота:
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 — байт статуса. Довольно «многоцелевая» вещица, где каждый бит чего-нибудь, да значит. Для нас важно лишь такие сообщения от этих бит: что данная запись, т.е. запись слота 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 — снова байт статуса, где 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) |
извлечь содержимое первой страницы следующего не-листового уровня все того же кластерного индекса, то первая пара слотов будет:
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. «Дешифровать» и своими словами описать — что нам сообщают эти две записи?
Однако пока ни наши изыскания, на даже успешное решение последней «самопроверочной работы» не дают ответ на основной вопрос — почему там 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) |
Снова первая пара слотов этой страницы:
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 — заголовок строки (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) |
и «выписать» из нее первую пару слотов:
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 — и снова байт статуса. Поскольку мы имеем дело вновь с 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') |
Резалт-сет последнего запроса (он выводит информацию сразу по обоим индексам):
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 значений хоть в одной её строке. Однако для всех прочих страниц описанное правило экономии действует и пренебрегать им не следует.