Питання про індекси, які вам не треба буде задавати



Після відповідей на 14 питань про індекси, які ви соромилися задати, у мене виникло набагато більше коментарів, уточнень і виправлень. Скомпілювати з усього цього статтю виглядало витівкою з мінімумом користі. І це змусило мене задумався, а чому взагалі ми повинні «соромитися ставити подібні питання? Соромно не знати? А чи є спосіб розібратися, не вганяючи себе в фарбу? Є. Причому він позбавить від численних неточностей, якими рясніють багато «відповіді». Ви будете почувати буквально кожен байт вашої бази кінчиками своїх пальців.

Для цього, я пропоную підняти капот» у SQL Server і зануритися в солодкий мир шістнадцяткових дампів. Може статися, що всередині все набагато простіше, ніж вам здавалося.


Готуємо робоче місце
У своїх експериментах я буду використовувати безкоштовний SQL Server 2014 Express Edition разом з звичної багатьом Management Studio. Але майже всі отримані знання корінням йдуть в SQL Server 7.0, родом з дев'яностих, і не складе особливих труднощів використовувати отримані навички на старих інсталяціях.

Для більшості прикладів нам знадобиться включений прапор трасування 3604. Я не буду його тиражувати в кожному з них, але припускаю, що він завжди включений. Просто пам'ятайте — якщо не видно ні дампа, ні помилок, то додайте в початок вашого коду:

DBCC TRACEON(3604)
GO

Деякі команди вимагають явного вказівки імені бази даних. У моєму випадку — це «Lab». Якщо ви вибрали інший ідентифікатор, то не забувайте вносити відповідні зміни. Теж саме стосується і деяких інших значень. Наприклад, фізичні адреси сторінок цілком можуть виявитися іншими. Я буду намагатися нагадувати про це, але намагайтеся тримати руку на пульсі свого коду.

Відповіді сервера часто повертають дуже багато полів або інших даних, з яких жвавий інтерес буде представляти лише мала їх дещиця. Оскільки мова йде не про команду SELECT — регулювати висновок непросто. Тому я періодично буду відрізати зайве, то там, то сям. Не дивуйтеся, якщо побачите набагато більше даних, ніж у мене.

Сторінкова організація
У SQL Server базовою структурою для організації даних є сторінка розміром 8192 байти (8KB або 8KiB, кому як подобається). Її можна адресувати через зазначення коду файлу (FID — File ID), частиною якого вона є (нижче подсмотрим його в sys.database_files), та коду самої сторінки (PID — Page ID) в цьому файлі. Записувати таку адресу ми будемо у вигляді «FID:PID». Наприклад, 1:142 буде означати сторінку з кодом 142 у файлі з кодом 1.

SELECT FID = file_id, name, physical_name FROM sys.database_files

FID name physical_name
----------- --------------- -------------------
1 Lab D:\Lab.mdf
2 Lab_log D:\Lab_log.ldf

Сторінки як звичайна, так і звичайних індексів, організовані у вигляді бінарних дерев (B-Trees). Як у кожного дерева, у нього є корінь (root node), листя (leaf nodes) і проміжні вузли (intermediate nodes, але ми їх можемо назвати гілками, продовжуючи рослинну аналогію). Щоб ці елементи було легко відрізнити один від одного, не візуалізуючи, є поняття рівня індексу (index level). Нульове значення означає рівень листя, максимальне — корінь дерева. Все, що між ними — гілки.



Давайте створимо просту таблицю на 1000 записів з кластерним індексом. Перша колонка буде простим автоінкрементний полем, друга — таке ж значення, але зі знаком мінус, третя — бінарним значенням 0x112233445566778, яким зручно користуватися як маркером в дампі.

CREATE TABLE [dbo].[ClusteredTable] (
[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[A] AS (-[ID]) PERSISTED NOT NULL,
[B] AS (CONVERT([binary](8), 0x1122334455667788)) PERSISTED NOT NULL
)
GO

INSERT INTO [dbo].[ClusteredTable] DEFAULT VALUES;
GO 1000

Тепер скористаємося недокументованій, але безсмертної командою DBCC IND для проведення сторінкової аутопсії. Першим аргументом необхідно вказати назву чи код бази, де знаходиться досліджувана таблиця. Другим — ім'я таблиці (або код відповідного об'єкта), третім — код індексу. Є ще четвертий опціональний параметр — код партіціі. Але він нас в даному контексті не цікавить, на відміну від коду індексу, на якому я зупинюся детальніше. Значення 0 говорить про те, що запитується рівень Heap. По суті, це рівень даних без будь-яких індексів. За номером 1зарезервирован кластерний індекс (виводиться разом з даними, як у Heap). У всіх інших випадках ви просто вказуєте код відповідного індексу. Давайте подивимося на кластерний індекс нашої таблиці.

Починаючи з версії 2012 з'явилася DMV-функція sys.dm_db_database_page_allocations. Вона в чомусь зручніше у використанні, дає більш детальну інформацію і має масу інших переваг. Всі наші приклади можна без праці відтворити з її використанням.
DBCC IND('Lab', 'ClusteredTable', 1)
GO

PageFID PagePID IndexID PageType IndexLevel
------- ----------- ----------- -------- ----------
1 78 1 10 NULL
1 77 1 1 0
1 79 1 2 1
1 80 1 1 0
1 89 1 1 0
1 90 1 1 0

В якості відповіді сервера ми отримуємо список знайдених сторінок індексу. Першими шпальтами йдуть вже знайомі нам FID і PID, які разом дають адресу сторінки. IndexID своєї одиницею в черговий раз підтверджує, що ми працюємо з кластерним індексом. Тип сторінки (PageType) зі значенням 1 — це дані, 2 — індекси, 10 — IAM (Index Allocation Map), який до індексів має опосередковане відношення і ми його проігноруємо. Ще один наш знайомий — рівень індексу (IndexLevel). За його максимальному значенню ми бачимо корінь кластерного індексу — сторінка 1:79. За нульовим значенням — листя 1:77, 1:80, 1:89, 1:90.

Структура сторінки
Подумки ми вже можемо собі намалювати дерево сторінок. Але це легко зробити, коли у нас два рівня. А їх могли б бути десятки і сотні. Тому без дослідження самої сторінки не обійтися. Почнемо ми з кореневої і скористаємося ще однією недокументованій, але такий же безсмертної командою — DBCC PAGE.

Першим аргументом, також як і DBCC IND, вона приймає ім'я або код бази. Далі йде пара з FID і PID шуканої сторінки. Останнім значенням ми указаний формат виведення з наступних доступних значень:

  • 0 — тільки заголовок;
  • 1 — заголовок, дампи і індекс слотів;
  • 2 — заголовок і повний дамп;
  • 3 — заголовок і максимальна деталізація для кожного слота.
Щоб не пояснювати зміст деяких термінів абстрактно, відразу поглянемо на дамп кореневої сторінки (не забувайте підставляти свої адреси сторінок):

DBCC PAGE('Lab', 1, 79, 2)
GO

PAGE: (1:79)
...
000000001431A000: 01020001 00800001 00000000 00000b00 00000000 ....................
000000001431A014: 00000400 78000000 6c1f8c00 4f000000 01000000....x...l.?.O.......
000000001431A028: 23000000 cc000000 0e000000 00000000 00000000 #...I...............
000000001431A03C: 9dcd3779 01000000 00000000 00000000 00000000 .I7y................
000000001431A050: 00000000 00000000 00000000 00000000
06000000 ....................
000000001431A064: 074d0000 00010006 44010000 50000000 01000687 .M......D P.........?
000000001431A078: 02000059 00000001 0006ca03 00005a00 00000100 Y .........E Z........
000000001431A08C: 00002121 21212121 21212121 21212121 21212121 ..!!!!!!!!!!!!!!!!!!
...
000000001431BFE0: 21212121 21212121 21212121 21212121 21212121 !!!!!!!!!!!!!!!!!!!!
000000001431BFF4: 21212121 81007600 6b006000!!!!..v.k.`.

OFFSET TABLE:

Row - Offset 
3 (0x3) - 129 (0x81) 
2 (0x2) - 118 (0x76) 
1 (0x1) - 107 (0x6b) 
0 (0x0) - 96 (0x60) 

На даному етапі я хочу показати структуру сторінки в цілому, тому більшу частину дампа, яка на це не впливає, я вирізав.

Починається все з заголовка довжиною в 96 байт, кінець якого у висновку вище я зазначив розривом, щоб легше читалося. В побуті його там немає. За заголовком йдуть записи з даними, які називаються «slots» (перекладати цей термін я не став). Точніше, вони намагаються йти після заголовка. Але дані в базі — величина непостійна. Кортежі додаються видаляються, оновлюються. Тому розташовуватися вони можуть не відразу після заголовка, а повністю в хаотичному порядку, з проміжком після заголовка, так і між слотами теж.

Для того, щоб контролювати їх поточне місце розташування в самому кінці сторінки розташовується індекс цих слотів. Кожен елемент займає два байти, зберігає зміщення слоти на сторінці у вигляді little-endian (тобто, саме останнє значення 0x6000 в нашому дампі ми читаємо байт за байтом задом наперед — 0x0060) і нумеруються з кінця. Самим останнім йде слот 0, перед ним слот 1 і так далі. Індекс слотів і дані як би рухаються назустріч один одному з різних боків сторінки. Розшифровка їх індексу дана в самому дампі після заголовка OFFSET TABLE. Порівняйте її з дампом індексу в кінці сторінки — 81007600 6b006000.



Структура запису
Тепер поглянемо на самі слоти в деталях і для цього застосуємо режим 1 для виведення інструкції DBCC PAGE.

DBCC PAGE('Lab', 1, 79, 1)
GO

...
Slot 0, Offset 0x60, Length 11, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = Record Size = 11
Memory Dump @0x0000000014B1A060
0000000000000000: 06000000 074d0000 000100.....M.....

Slot 1, Offset 0x6b, Length 11, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = Record Size = 11
Memory Dump @0x0000000014B1A06B
0000000000000000: 06440100 00500000 000100 .D P........

Slot 2, Offset 0x76, Length 11, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = Record Size = 11
Memory Dump @0x0000000014B1A076
0000000000000000: 06870200 00590000 000100 .?...Y.....

Slot 3, Offset 0x81, Length 11, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = Record Size = 11
Memory Dump @0x0000000014B1A081
0000000000000000: 06ca0300 005a0000 000100 .E Z........
...

Це ті ж самі чотири слоти, які ми бачили раніше, тільки їх двійкове подання вирізано з дампа сторінки і подано на блюдечку, що очевидно полегшить нам їх подальше дослідження.

Перший байт кожного з них (0x06) містить інформацію про тип запису. Точніше, за нього відповідають тільки біти 1-3 (у нашому випадку 011b = 3), які можуть прийняти одне з наступних значень:

  1. 0 — дані;
  2. 1/2 — forwarded/forwarding-запису;
  3. 3 — індекс;
  4. 4 — двійкові дані (блоб);
  5. 5/6/7 — ghost-запису для індексу/даних/версій.
Оскільки в наших тестах ми не побачимо встановленим нульовий біт, то можемо сміливо говорити, що запис описує індекс, якщо молодша частина байта дорівнює 0x06.

За першим байтом у нашому випадку йдеться значення ключа, за яким побудовано кластерний індекс, а за ним — адреса сторінки в дещо переверненому форматі PID:FID. Тобто, для слота 3 значення ключа ID буде 0xca030000 (970 у десятковому вигляді після перевороту little-endian), PID — 0x5a000000 (90), FID — 0x0100 (1). Що можна перевести як: «для пошуку записів з ID від 970, вирушайте на сторінку 1:90». Для слота 2 ми отримаємо ID = 0x87020000 (647), PID = 59000000 (89), FID = 0x0100 (1). А читаємо: «для записів, ID від 647 до 970 (які вже обслуговує слот 3), дотримуйтесь на сторінку 1:89». І так, всі чотири слоти відправляють нас на чотири сторінки, кожен для свого діапазону значень. Ці чотири сторінки ми вже бачили в самому початку, коли умоглядно будували дерево за списком сторінок. Подивіться на самий перший висновок команди DBCC IND. Це ті ж адреси, що і сторінок з даними листового рівня (PageType = 1, IndexLevel = 0).

Якби наша таблиця набагато більше, ці записи посилалися б на сторінки індексу проміжного рівня (Index Level кореня мінус один рівень). І шукай ми, скажімо, значення ID = 743794, то в кореневій сторінці отримали б слот, який відповідає за досить широкий діапазон 700000-750000. Він би нас відправив на схожу сторінку, за тим винятком, що там би ми звузили діапазон пошуку до слота 743000-744000. Який, у свою чергу, відправив би вже на сторінку з даними, де зберігаються записи 743750-743800.



Коль скоро заговорили про дані, то давайте перейдемо на сторінку 1:89, обслуговуючу значення від 657 до 970, яку ми тільки що виявили.

DBCC PAGE('Lab', 1, 89, 1)
GO

...
Slot 0, Offset 0x60, Length 23, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 23
Memory Dump @0x0000000011F1A060
0000000000000000: 10001400 87020000 79fdffff 11223344 55667788....?...yyyy."3DUfw.
0000000000000014: 030000 ... 

Slot 1, Offset 0x77, Length 23, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 23
Memory Dump @0x0000000011F1A077
0000000000000000: 10001400 88020000 78fdffff 11223344 55667788 ........xyyy."3DUfw.
0000000000000014: 030000 ... 
...

Сторінка з даними принципово не відрізняється від сторінок з індексом. Це все той же самий заголовок і ті ж самі слоти з їх індексом у кінці сторінки. Для прикладу я обмежився тільки парою перших записів, по дампу яких ми послідовно і пройдемося.

  1. 0x10. Тип запису, з яким ви вже знайомі. Біти 1-3 дають нам значення 0, що говорить про сторінку з даними. Але ще спливає встановлений біт 4. Він сигналізує, що у запису є NULL BITMAP — бітова карта, де за кожним полем досліджуваної таблиці закріплений один біт. Якщо цей біт встановлений, то вважається, що значення у відповідному полі — NULL.
  2. 0x00. Другий байт відповідає за хитрі ghosted-forwarded запису, починаючи з SQL Server 2012, і нас зараз не цікавить.
  3. 0x1400. Всі типи можна класифікувати як типи з фіксованою довжиною і змінної. Найбільш очевидний приклад — binary(n) і varbinary(n). Відразу після цього значення підуть безпосередньо дані з типами фіксованої довжини. А саме воно вказує, з якого зміщення у слоті почнеться наступна порція інформації. Наведене значення (а саме, 20 в десятковому вигляді після розвороту з little-endian) = 2 (перших байта з типами) + 2 (цих байта) + 4 (байти на тип int в поле ID) + 4 (байти на такий же тип у полі A) + 8 (байт на бінарне поле B).
  4. 0x87020000 / 88020000 — це значення 647 і 648, які є ні що інше, як значення поля ID. Пам'ятаєте, раніше ми писали, що слот 2 обслуговує запису, починаючи з ID = 647. Це вони і є.
  5. 0xFFFFFD79 / 0xFFFFFD78 — а це вже значення поля «A» (-647, -648), яке ми розраховували за формулою-ID.
  6. 0x11223344 55667788 — порцію даних логічно завершує наш бінарний маркер, який ми визначали для поля B.
  7. 0x0300 — кількість колонок. Як і очікувалося — їх три (ID, A, B).
  8. 0x00 — а це та сама NULL BITMAP, яку я згадував раніше. Її довжина залежить від кількості колонок (по битку на кожну) і округляється до найближчого байта. У нашому випадку для зберігання трьох біт (для трьох полів ID, A, B) буде достатньо одного байта. Оскільки значень NULL у нас немає, всі біти скинуті.


Загальні індекси
Тепер, давайте створимо у таблиці звичайний, не кластерний індекс поля A і подивимося, чим він буде відрізнятися від кластерного.

CREATE INDEX
[IX_ClusteredTable]
ON [dbo].[ClusteredTable] (
[Value] ASC
)
GO

Подивимося, які сторінки виявилися задіяні і для цього вказуємо код нового індексу — 2.

DBCC IND('Lab', 'ClusteredTable', 2)
GO

PageFID PagePID IndexID PageType IndexLevel
------- ----------- ----------- -------- ----------
1 94 2 10 NULL
1 93 2 2 0
1 118 2 2 0
1 119 2 2 1

Дуже знайома картина. Є сторінки рівня листя, які ми дізнаємося з IndexLevel = 0. Тільки тепер у них тип PageType = 2, тобто це індексні сторінки, а не сторінки з даними. Є і кореневий вузол з незмінними IndexLevel = 1, PageType = 2. За його адресою 1:119 ми і продовжимо подальше дослідження.

DBCC PAGE('Lab', 1, 119, 1)
GO

Slot 0, Offset 0x60, Length 15, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = Record Size = 15
Memory Dump @0x000000001095A060
0000000000000000: 0618fcff ffe80300 005d0000 000100 ..uyye...].....

Slot 1, Offset 0x6f, Length 15, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = Record Size = 15
Memory Dump @0x000000001095A06F
0000000000000000: 065afeff ffa60100 00760000 000100 .Z?yy¦v........

І знову нічого принципово нового. Все той же заголовок, ті ж слоти. Чи це ті два слоти, які посилаються на дві сторінки (1:93, 1:118) знайдені вище? Розкладемо по поличках. Перший байт з типом 0x06 каже, що це індекс. Останні 6 байт — PID:FID, які ми вже знаємо як розбирати. Після трансформації в звичний формат ми отримуємо точно 1:93 (0x5d000000 0100) і 1:118 (0x7600000000 0100). А ось між ними нас чекає цікавий сюрприз. Наприклад, для слота 1 значення 0x5AFEFFFF і 0xA6010000 — це ніщо інше, як -422 і 422. Тобто, крім значення поля A, яке ми включили в індекс, він містить ще й значення кластерного ключа ID.

В іншому схема аналогічна спостерігається в кластерному індексі. Діапазон від -1000 до -422 обслуговує сторінка 1:93, від -422 — сторінка 1:118. За тим лише винятком, що кластерний індекс у нас посилався на сторінку з даними, а куди веде цей? Поїхали подивимося.

DBCC PAGE('Lab', 1, 118, 1)
GO

...
m_pageId = (1:118) m_headerVersion = 1 m_type = 2
...
Slot 0, Offset 0x60, Length 12, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 12
Memory Dump @0x000000001331A060
0000000000000000: 165afeff ffa60100 00020000 .Z?yy¦......

Slot 1, Offset 0x6c, Length 12, DumpStyle BYTE
Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 12
Memory Dump @0x000000001331A06C
0000000000000000: 165bfeff ffa50100 00020000 .[?yy?......
...

Я навмисно залишив одну сходинку з заголовка, щоб ми переконалися, що доля привела нас на індексну сторінку (тип сторінки m_type = 2). Усе інше ви напевно читаєте вже жартома. Не має збентежити навіть новий тип запису — 0x16, оскільки це 0x10 + 0x06. Перший доданок — біт 4 наявності NULL_BITMAP, який ми бачили на сторінках з даними. Друге — тип індексу.

За ними впевнено йдуть значення -422 (0x5AFEFFFF для слота 0) і -421 (0x5BFEFFFF для слота 1) стовпця A. Віримо, попередній рівень індексу так і сказав, що сторінка 1:118 працює від -422. Після цих значень слідують кластерні ключі (422, 421), якими нас теж не здивуєш. А далі — тільки кількість колонок (0x0200) і NULL_BITMAP (0x00), як на сторінках з даними. Зауважте, що посилань на самі сторінки з даними немає.

Чи Знали ви до цього, що звичайний індекс працює через кластерний (при його наявності, зрозуміло)? І якщо ви кластеризовали по великому (особливо, натуральному) ключу, то у вас є ризик отримати роздуті вторинні індекси? Сподіваюся, що ні. Також як і не знали ще багато чого з цієї статті, оскільки тоді в ній є толк :)

Мені хочеться думати, що тепер, ви поставитеся з настороженістю до заяв у стилі: «Важливою характеристикою звичайна індексу є те, що всі значення розташовані в певному порядку або зростання, або зменшення» зі згаданої на початку статті. Знаючи, що місце розташування сторінок невідомо, що упорядкованість на сторінці визначається індексом слотів, можна з натяжкою говорити тільки про квазі-впорядкованості. А краще про те, що кластерний індекс… кластеризует. Так, це він робить відмінно. Ви точно знаєте, на яких сторінках знаходиться кластер даних від X до Y.

Хочеться сподіватися, що тепер ви зможете самі, з задоволенням від виконаної роботи, дослідити роботу індексів з Heap. Знайти відповіді на запитання: «чи гарний GUID для кластерного індексу», «проходить у таких випадках page split», «як виглядає UNIQUIFIER», «правда Heap швидше для вставки»" і багато інші самостійно. Для потрібної версії і в необхідних умовах. Повертаючись до заголовку статті, «вам не треба буде їх ставити».

До швидких зустрічей під капотом, сподіваюся цей процес вам припав до душі.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.