Так вот, давайте изучим эту самую первую запись (строку) нашей тестовой таблицы. Напомню, что согласно предположению последнего абзаца предыдущей части статьи, движок должен был считать ключ кластерного индекса этой строки (col1=10000) уникальным, хотя буквально следующая же строка «развеяла» бы такое допущение. Итак — вот она, строка с «возможно уникальным» ключом:
Что мы видим?
- 10000c00 — снова заголовок строки, разумеется. Снова NULL bitmap — присутствует, а вот плавающих колонок больше нет! Да, пятый бит первого байта очищен, а это значит у нас имеются колонки только фиксированных размеров;
- 10270000 и 20a10700 — col1 и col2 соответственно, со своими значениями 10000 и 500000.
- 0300 — как ни удивительно, но снова — общее число колонок. Которых снова 3. Ну да, как там в классике — «...видишь? И я не вижу. А он — есть!».
Так вот «он» (в данном случае она, колонка UNIQUIFIER) — таки есть; - 00 —снова NULL bitmap, снова рапортующий о тотальном отсутствии значений NULL в записи.
Итого, что бы прояснить картину: для пока уникальной записи не-уникального ключа UNIQUIFIER отсутствует. Физически он отсутствует абсолютно, его эфемерное присутствие выражается разве что в счетчике колонок. Логически, с точки зрения автора, удобнее считать что UNIQUIFIER для такой записи содержит ноль. Всегда помня, однако, что физически это не ноль, и даже не NULL, а еще меньше. Справедливости ради отметим, что это не какое-то «особое» изобретение для «особой» колонки. Движок вообще опускает (на физическом уровне) все колонки «плавающих» (но не фиксированных!) размеров содержащие значения NULL. Как мы видим, для нужд «уникализации» такое поведение пришлось очень, очень «в кассу», иначе необходимость включения в кластерный индекс не-уникальной колонки вызывало бы у DBA беспросветную тоску. А так, с учетом что раз мы создаем по колонке индекс, тем более кластерный, то, наверно, можно говорить о хотя бы относительной уникальности значений в ней содержащихся — все не так уж плохо. Да, дубликаты «ощипают» наш HDD, и порядочно. Однако все уникальные (пока) значения присутствия UNIQUIFIER-а не почувствуют! Последний разобранный нами слот занимает 15 байт, ровно столько же сколько и его «коллега» из уникального кластерного индекса.
Разобравшись с индексом кластерным и его «эрзац-уникализатором», а так же вооруженные знаниями по структуре уникального не-кластерного индекса из самого первого опыта, мы можем предсказать, как будет выглядеть индекс NONCL_uq_Index в опыте текущем. На не-листовых уровнях этому индексу не требуется ни «уникализатор» (он сам по себе уже уникальный), ни ссылки на кластерный ключ. Стало быть, записи этих уровней будут выглядеть точно как и в первом опыте. Вот, к примеру, записи корневой страницы разбираемого индекса (эта страница имеет номер 1259):
1259 2 110 NULL
1259 2 1260 585751
То есть на всех не-листовых уровнях фигурирует только ключ не-кластерного индекса состоящий ровно из одной колонки — col2. Что совершенно совпадает с тем, как был этот индекс устроен, во времена когда оба индекса были уникальными.
Все несколько меняется, когда дело доходит до листового уровня. Нет, общая идея сохранилась: каждый ключ не-кластерного индекса должен сослаться на соответствующий ему ключ индекса кластерного, дабы через последний иметь доступ к остальной части таблицы. Вот только кластерный ключ теперь, в отличии от первого опыта, стал составным. И записи листового уровня выглядят теперь вот как:
90 0 500000 10000 0
90 0 500001 10000 1
90 0 500002 10000 2
90 0 500003 10000 3
... ... ... ... ...
90 0 500010 10010 0
90 0 500011 10010 1
90 0 500012 10010 2
... ... ... ... ...
90 0 500020 10020 0
90 0 500021 10020 1
... ... ... ... ...
90 0 500030 10030 0
90 0 500031 10030 1
... ... ... ... ...
90 0 500040 10040 0
90 0 500041 10040 1
... ... ... ... ...
Итого: ключ кластерного индекса включается полностью, как он есть, на листовой уровень не-кластерного индекса. Однако роль этого ключа именно что «перекинуть мостик» от не-кластерного индекса к остальной части таблицы. Он не становится частью не-кластерного ключа, как это было в предыдущем случае, ведь нам не требуется «уникализация» записей текущего индекса. С точки зрения занимаемого места на HDD дела на этом листовом уровне снова «не блеск», как можно догадаться. Но много, много лучше чем могло бы быть. А именно, те слоты у которых согласно последнему резалт-сету в колонке UNIQUIFIER содержится 0 (на самом деле NULL) занимают те же 12 байт, что и в самом первом нашем эксперименте. Потому что для них указанной колонки как бы нет (или она для них «как бы есть», тут уж решайте сами насчет формулировки). Но вот если значение UNIQUIFIER-а отличается от 0/NULL, то объем занимаемого места «подпрыгивает» к отметке 20 байт. Ну а чего вы ждали? Плюс четыре байта на само значение, и плюс четыре байта на обеспечение «плавающего» размера — все верно.
Вывод? Все тот же: делая колонку гарантировано уникальной проиграть вы не можете, а выиграть — запросто. Если же колонка планируется под именно кластерный индекс то можно говорить не просто о выигрыше, а почти о джек-поте. Жаль только, что в общем случае влиять на характеристику уникальности колонки мы почти не можем, она практически однозначно диктуется правилами бизнеса. Мы, разумеется, можем ввести «лишнюю» колонку, именно и только для создания кластерного индекса. Как покажет дальнейшее изложение эта идея почти всегда будет здравой, если уж у нас не «вытанцовывается» «естественно-уникальная» колонка типа «номер паспорта сотрудника». Кроме того, и теперь вы это знаете, «лишняя» колонка будет введена и без вашего желания, так чего «тянуть»? Правда, если колонка по которой мы планируем создать кластерный индекс будет содержать «почти уникальные» значения, хотя и не будет официально объявляться как UNIQUE, «профит» именно от UNIQUIFIER-колонки (а не своей собственной) можно получить вполне ощутимый, ведь при таком сценарии ее «почти не будет»! Одним словом — выбор за нами, как и всегда. Главное знать альтернативы и критерии по которым мы эти альтернативы выбираем.
Оба индекса не-уникальны.
Наконец — четвертая вариация сочетания уникальности двух индексов, сводящаяся к полному отсутствию уникальности. Поскольку, очевидно, этот опыт есть комбинация последнего и предпоследнего экспериментов раздел будет совсем коротким. Код тестового скрипта для экономии приводится не будет, он без проблем пишется самостоятельно на основе предыдущих скриптов. Перейдем сразу к выводам.
Несложно догадаться, что этот случай берет худшие черты двух предыдущих, что и выливается в «трехэтажность» каждого из индексов и в общее число страниц не-листового уровня равного десяти. По пять «на брата». А ведь начинали с четырех — три страницы для кластерного и одна для противоположного индекса!
Не менее простым представляется и такое умозаключение: кластерный индекс по сравнению с только что разобранным не изменился ни на йоту. Все тот же UNIQUIFIER сверху донизу, со всеми своими полюсами и минусами. Так что переходим сразу к индексу не-кластерному.
Что касается не-листовых уровней, то тут изменения самые заметные: наш составной кластерный ключ теперь является составной частью не-кластерного ключа. Обратите внимание: мы заказывали ключ из одной колонки (col2), а получили — из трех! col2+col1+UNIQUIFIER. И ведь все верно, не придерешься! Итого, вот как выглядят записи, скажем, root-страницы разбираемого индекса:
794 2 110 NULL NULL NULL
794 2 795 551450 61450 1
794 2 1083 602510 112510 9
794 2 1370 653580 163580 8
Обратите внимание, что все колонки — ключ, а не «просто так». Нужно ли заострять внимание на том, что для хранения страниц не-листовых уровней такой «финт» имеет самые отрицательные последствия в плане объема места требуемого на HDD?
Листовой же уровень все того же не-кластерного индекса, напротив, очень похож на только что виденный. По сути изменения только два: в заголовках столбцов col1 и UNIQUIFIER появился суффикс (key) в каждом. То есть ранее это был именно «мостик», теперь — просто часть ключа (но и снова «мостик», по совместительству). По счастью, на хранении данных этого уровня такой суффикс никак не сказался. Слот по прежнему занимает 12 байт если наш «эрзац-уникализатор» равен 0/NULL, либо 20 байт в обратном случае.
Не-кластерный индекс на хип (heap) таблице.
В заключительном разделе статьи мы разберем структуру индекса на хип таблице. Поскольку такая таблица по своему определению кластерного индекса не имеет, то в рамках этого заключительного раздела мы будем говорить просто «индекс», без уточнения что имеется в виду его не-кластерная разновидность. Наши исследования помогут прояснить один важный момент: почему настоятельно рекомендуются (в том числе и официально Microsoft) таблицы с кластерным индексом. А хип-таблицы, как им противоположные, соответственно — не рекомендуются совсем. Иными словами: «создаешь таблицу? сразу реши вопрос с колонкой (или несколькими, в данном контексте не важно) кластерного индекса!». Почему так и откуда такая настойчивая рекомендация проистекает мы сейчас и выясним. Только учтем, что хотя индекс у нас остался строго один, не-кластерный, ничто не может ему помешать быть в одиночестве как уникальным, так и нет. Поэтому наше заключительное исследование вновь подразделяется на два варианта: не-кластерный не-уникальный и не-кластерный уникальный. Начнем с последнего.
Уникальный индекс на хип таблице.
На этот раз тестовый скрипт будет таким:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | USE master go CREATE DATABASE [~DB~] ON PRIMARY ( NAME = '~DB~_Data', FILENAME = 'c:\sqlCMD.ru\~DB~.mdf', SIZE = 20 MB ) LOG ON ( NAME = '~DB~_Log', FILENAME = 'c:\sqlCMD.ru\~DB~.ldf', SIZE = 20 MB ) GO USE [~DB~] GO IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1 IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tsup') DROP TABLE Tsup CREATE TABLE Tsup (PageFID tinyint, PagePID int, IAMFID tinyint, IAMPID int, ObjectID int, IndexID tinyint, PartitionNumber tinyint, PartitionID bigint, iam_chain_type varchar(30), PageType tinyint, IndexLevel tinyint, NextPageFID tinyint, NextPagePID int, PrevPageFID tinyint, PrevPagePID int) CREATE TABLE T1 (col1 int, col2 int) GO CREATE UNIQUE NONCLUSTERED INDEX NONCL_uq_Index ON T1(col2) GO declare @i int SET @i = 0 BEGIN TRANSACTION WHILE @i < 250000 --loop counter BEGIN insert into T1 VALUES (999, 500000+@i) set @i += 1 END COMMIT TRANSACTION INSERT INTO Tsup EXEC ('DBCC IND ([~DB~], T1, -1)'); SELECT PagePID,IndexID,PageType,IndexLevel FROM Tsup WHERE IndexLevel>0 |
Как и договорились — только не-кластерный, но уникальный индекс по колонке col2. Команда DBCC IND сообщает нам:
94 2 2 1
1179 2 2 2
1180 2 2 1
Пока все представляется не столь и ужасным: всего три «этажа» (два мы видим и плюс листовой), всего 3 страницы не-листовых уровней — достойно! Ладно, посмотрим на устройство этих страниц изнутри. Для начала — «корень»:
1 2 | DBCC TRACEON(3604) DBCC PAGE([~DB~], 1, 1179, 3) --non-cluster idx, root level(2) |
Видим, что:
1179 2 94 NULL
1179 2 1180 601025
И что? По моему — идеально. Единственная колонка в виде ключа, ссылка на дочернюю страницу... Меньше-то не бывает, если только мы в своих опытах ничего важного не упустили. Действительно, пока мы на не-листовых уровнях рассказы о «плохости» хип-таблиц представляются ничем не подтвержденными инсинуациями, не более того. Но посмотрим что у нас на уровне листовом. В случае автора первой страницей на нем была страница с номером 90, ее и смотрим:
1 2 | DBCC TRACEON(3604) DBCC PAGE([~DB~], 1, 90, 3) --non-cluster idx, leaf level(0) |
И вот тут-то — «шоу начинается»:
90 0 500000 0x5000000001000000
90 0 500001 0x5000000001000100
90 0 500002 0x5000000001000200
90 0 500003 0x5000000001000300
90 0 500004 0x5000000001000400
90 0 500005 0x5000000001000500
... ... ... ...
90 0 500010 0x5000000001000A00
90 0 500011 0x5000000001000B00
... ... ... ...
90 0 500020 0x5000000001001400
90 0 500021 0x5000000001001500
... ... ... ...
90 0 500050 0x5000000001003200
90 0 500051 0x5000000001003300
90 0 500052 0x5000000001003400
... ... ... ...
90 0 500080 0x5000000001005000
90 0 500081 0x5000000001005100
90 0 500082 0x5000000001005200
... ... ... ...
Давайте сразу о «больном», о размерах: в наших прошлых четырех экспериментах в тех двух из них когда не-кластерный индекс был уникальным каждый его слот на листовом уровне занимал или 12 байт (когда второй, кластерный индекс был так же уникальным), или либо 12, либо 20 байт (когда кластерный индекс уникальным не являлся; выбор между этими двумя значениями был обусловлен, как вы помните, фактической уникальностью значения в колонке кластерного ключа). Поскольку «в среднем» индексы стараются создавать на колонках с более-менее уникальными значениями, то «в среднем», если бы T1 была реальной таблицей в промышленной системе, слот листового уровня занимал бы 12 байт иногда требуя еще 8. В случае отсутствия кластерного индекса, то есть в случае разбираемой в настоящий момент хип-таблицы, тот же слот занимает необсуждаемые 16 байт каждый, без вариантов. Так что, снова «в среднем», на каждый слот мы просто теряем 4 байта не получая в ответ никаких выгод. А откуда такая потеря проистекает? А оттуда, что раньше «мостиком» между записями не-кластерного индекса и остальной частью таблицы выступали ключи индекса кластерного, а теперь, сами видите, эту роль принял на себя загадочный HEAP RID. Который, пожалуй, заслуживает краткого описания в размере пары-тройки абзацев.
Стало быть — где у нас (а точнее у движка сервера) очередные «грабли»? А вот они: раньше кластерный ключ гарантированно и однозначно определял целевую строчку таблицы с которой следует произвести те или иные действия. Помните наш разговор про «хэндл»? А теперь такого хэндла — нету. Почему бы в такой роли не выступить колонке col2, спрашиваете вы? Тоже, на самом деле, уже обсудили — она не является гарантированно уникальной. Да, мы пометили индекс по ней как UNIQUE, но это — сегодня. А кто даст гарантии что завтра мы не передумаем? А вот с кластерным индексом вы можете думать, раздумывать, передумывать, снова обдумывать и делать вообще что угодно — по факту он гарантировано уникален. Стало быть — нужна замена. Встречайте — HEAP RID, та самая замена. RID расшифровывается как row ID, а иногда row locator, то есть, грубо, «определитель строки» (что, кстати говоря, и есть мета-задача обсужденного нами ранее «хэндла»). Ну а слово HEAP подчеркивает, что находится та строка в хипе и только там. В ином случае, согласитесь, нам никакие «заменители хэндлов» не требуются. Разумеется, значения HEAP RID имеют четко выраженную структуру занимающую всегда ровно 8 байт. Вот она: FileID(2):PageID(4):SlotNumber(2). Цифры в скобках означают число байт из восьми отводимую под эту часть структуры. Пример: строка с col2=500082 (последняя в резалт-сете выше) имеет HEAP RID=0x5000000001005200, «дешифруем» его:
- первые четыре байта — номер страницы, в нашем случае 0x00000050=80;
- следующие два — номер файла в котором «лежит» страница. У нас: 0x0001=1;
- завершающая пара байтов — слот, 0x0052=82.
Итого, если бы мы дали запрос вида SELECT * FROM T1 WHERE col2=500082, то возвращаемые значение следовало взять в первом файле данных, на 80-й странице, в слоте номер 82. Многие удивляются что страница указывается не 90-я, которую мы исследуем в настоящий момент, а 80-я. «Разве col2=500082 находится не на 90-й?», спрашивают они. «Вот же она, я ее вижу!». Видят-то они видят, только они видят индексную запись с тем же значением. А нам нужна вся запись, то есть строка таблицы, со всеми нашими двумя колонками, которая хранится — где? Правильно — в хип таблице. Которая является структурой совершенно самостоятельной, хоть и связанной с разбираемым индексом. Что бы убедится, что вас не обманывают, вызовите для просмотра «внутренности» 80-й страницы:
1 2 | DBCC TRACEON(3604) DBCC PAGE([~DB~], 1, 80, 3) --the heap-table |
Найдя на этой странице 82-й слот обнаруживаем там:
col1 = 999
Slot 82 Column 2 Offset 0x8 Length 4 Length (physical) 4
col2 = 500082
Не обманули? 
А самое главное — решение вновь совершенно рабочее. Потому что сочетание трех указанных компонентов (FileID:PageID:SlotNumber) снова гарантировано уникально что бы вы себе не думали и не передумывали.
Итак, по сравнению с кластерной таблицей в текущей ситуации существенное изменение только одно: подавляющее большинство записей листового (только) уровня требуют лишние 4 байта не предоставляя никаких выгод взамен. То есть просто выброшенное место на HDD. Однако — все работает (в смысле индекс корректно и однозначно извлекает целевые данные), это не отнять, и тут претензий никаких. Посмотрим на возможные изменения в случае индекса не-уникального на все той же хип-таблице.
Не-уникальный индекс на хип таблице.
Вооруженные знанием о «нелюбви» движка к любым не-уникальным индексам, почерпнутом в предыдущих разделах текущей статьи, большинство читателей уже, наверно, настроились на самое худшее. И хотя автор пессимистов не любит, в данном случае таких читателей правильно и корректно называть реалистами. Потому что как выяснится через минуту они совершенно правы в своих ожиданиях. Давайте к тестам:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | USE master go CREATE DATABASE [~DB~] ON PRIMARY ( NAME = '~DB~_Data', FILENAME = 'c:\sqlCMD.ru\~DB~.mdf', SIZE = 20 MB ) LOG ON ( NAME = '~DB~_Log', FILENAME = 'c:\sqlCMD.ru\~DB~.ldf', SIZE = 20 MB ) GO USE [~DB~] GO IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1 IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tsup') DROP TABLE Tsup CREATE TABLE Tsup (PageFID tinyint, PagePID int, IAMFID tinyint, IAMPID int, ObjectID int, IndexID tinyint, PartitionNumber tinyint, PartitionID bigint, iam_chain_type varchar(30), PageType tinyint, IndexLevel tinyint, NextPageFID tinyint, NextPagePID int, PrevPageFID tinyint, PrevPagePID int) CREATE TABLE T1 (col1 int, col2 int) GO CREATE NONCLUSTERED INDEX NONCL_NONuq_Index ON T1(col2) GO declare @i int, @j int SET @i = 0 SET @j = 0 BEGIN TRANSACTION WHILE @i < 250000 --loop1 counter BEGIN WHILE @j < 10 --loop2 counter BEGIN insert into T1 VALUES (999, 500000+@i) set @j += 1 END set @i += 10 set @j = 0 END COMMIT TRANSACTION INSERT INTO Tsup EXEC ('DBCC IND ([~DB~], T1, -1)'); SELECT PagePID,IndexID,PageType,IndexLevel FROM Tsup WHERE IndexLevel>0 |
Получаем:
94 2 2 1
834 2 2 2
835 2 2 1
1135 2 2 1
Ну — все понятно, уже, на «ровном месте», добавилась лишняя страница не-листового уровня — сразу минус по сравнению с предыдущим вариантом. Ладно, смотрим «корень»:
1 2 | DBCC TRACEON(3604) DBCC PAGE([~DB~], 1, 834, 3) --non-cluster idx, root level(2) |
Видим:
834 2 94 NULL NULL
834 2 835 568030 0xD601000001005500
834 2 1135 636060 0x0C0300000100A500
В общем-то — это ожидалось, нам вновь нужен «эрзац-уникализатор», ведь наше «правило №1», очевидно, будет нарушено если оставить в ключе только значения col2. Но у нас нет удобного (или хотя бы относительно удобного) кластерного ключа годящегося на эту роль. А что у нас есть? Как всегда — его «сменщик», HEAP RID. «Прицепляем», «насильно вводим в ключ» и т.д. — логика совершенно та же самая что была в основной части рассказа, когда там не-кластерный индекс перестал быть уникальным. О сухом остатке таких изменений структуры читатели уже все поняли: «плюс восемь» к каждому слоту не-листового уровня, что и выливается в дополнительную страницу этого уровня. И, что характерно, взамен — никакого «профита», Снова одни расходы.
На уровне листовом, как можно себе представить, изменения минимальные:
90 0 500000 0x5000000001000000
90 0 500000 0x5000000001000100
90 0 500000 0x5000000001000200
... ... ... ...
Если раньше HEAP RID был просто «мостиком» к хип-таблице и, как следствие, к основному массиву данных, то теперь, сохранив эту роль, он еще получил «повышение статуса» — стал частью ключа. По счастью, статус хотя бы не требует лишних байт, как занимал каждый слот этого уровня 16 байт, так и продолжает.
Итого, отмена уникальности индекса хип-таблицы приводит к дальнейшему «сжиганию» места на HDD, как следствие — снижению эффективности работы индекса, и, самое главное, отсутствию каких либо компенсаций за все эти неудобства. Ну разве «но все же работает!», если это вас утешит. Работает, по крайней мере за это можете не волноваться.
Вместо заключения: почему хип-таблица почти всегда плохо.
Итак, мы уже имели возможность убедиться, что идея «а давайте-ка мы вообще кластерный индекс заводить не будем» является «не айс», мягко выражаясь. Индексы от этого — растут, толку от этого — ноль (а более корректно сказать, что наносится неоспоримый ущерб системе, обсуждению подлежит лишь размер его, но не сам факт), какие еще доказательства нужны? Для самых въедливых читателей приведу ссылку на отличную статью, где сам Microsoft ставит опыты с кластерными и хип-таблицами (к сожалению — только english). А для читателей не столь въедливых ограничусь выводами из этих опытов. Итак, с точки зрения Microsoft (причем эта точка зрения подтверждена конкретными замерами конкретных таблиц), если на таблице есть кластерный индекс:
- таблица и не-кластерные индексы работают быстрее;
- места на HDD требуется меньше (тут читатели освоившие приведенный выше материал могут скромно заметить: «да мы уж в курсе» );
- удаление строк таблицы высвобождает больше места на HDD.
Для таблицы кластерного индекса не имеющего, то есть для хип-таблицы, просто поменяйте в данном списке каждый пункт с «плюс» на «минус». Надо полагать, будет разумным допущение о том, что могут подобраться такие специфические обстоятельства и плюс такие структуры таблиц, и плюс такие наборы конкретных данных, что хип-таблицы все же будут оправданы в своем применении. Для того они и разрешены синтаксически и поддерживаются движком. Но в целом дискуссия закрыта: «создал таблицу — создал для нее кластерный индекс», все!
Автор традиционно благодарит своих читателей за внимание проявленное ими к материалам блога, и желает им быстрых запросов, оптимальных индексов и отсутствия в их базах хип-таблиц. Увидимся, пока!