Індекси SQL Server - запорука оптимальної роботи бази даних

У більшості серйозних бізнес-додатків досі використовуються реляційні системи баз даних, такі як SQL Server для зберігання даних. Для того, щоб до даних, що там зберігаються, також можна було отримати ефективний доступ, індекси є дуже вирішальним фактором - від проектування бази даних до запиту. Однак MS SQL Server тепер пропонує цілий ряд різних форм індексів: На додаток до класичних згрупованих та негрупованих індексів існують індекси для спеціальних областей застосування, таких як повнотекстові індекси, XML-індекси та просторові індекси. Окрім того, пізніше були додані індекси стовпців, які спочатку були розроблені для сховищ даних, які пізніше у третьому поколінні також можуть ефективно використовуватися для інших областей.

Загальне, як працюють індекси

Щоб краще зрозуміти, як працюють індекси, корисно зрозуміти, як SQL Server зберігає дані на диску. Дані зберігаються у файлах із розширенням .mdf або .ndf. Вони розділені на сторінки пам’яті розміром 8 КБ кожна, що також є найменшою одиницею, в якій SQL Server читає або записує дані. Вісім послідовних сторінок пам'яті кожна утворюють так званий екстент. Хоча сторінка пам'яті містить лише дані з однієї таблиці, екстенти є однорідними та змішаними, що містять сторінки пам'яті з різних таблиць. Наприклад, якщо запит читає всіх осіб з певним прізвищем із таблиці, яка не має індексу, він повинен прочитати всі сторінки пам’яті, що містять дані в цій таблиці.

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

Кластерні індекси

У найпростішому випадку сама таблиця сортується за певним стовпцем (у випадку таблиці осіб, наприклад, за прізвищем). Це саме те, що робить кластерний індекс. Однак це робиться не у формі простого списку, а у формі двійкового дерева, в якому кожен вузол дерева використовує власну сторінку пам'яті, що містить алфавітно відсортований список імен, доповнений посиланням на сторінку пам'яті, в якій далі шукайте це ім’я (або ім’я, яке слідує за ним за алфавітом). Це може - залежно від кількості записів даних у таблиці - тривати кілька етапів, поки нарешті не буде зроблено посилання на сторінку пам'яті, на якій зберігаються повні особисті дані. Це означає, що перед знаходженням відповідних рядків таблиці потрібно прочитати лише кілька сторінок даних.

Хоча прізвище, безумовно, може з’являтися кілька разів у таблиці осіб, на практиці зазвичай використовується унікальний стовпець для згрупованого індексу, який часто відповідає первинному ключу таблиці, тобто ключу, за допомогою якого можна чітко ідентифікувати всі рядки таблиці. Отже, при створенні первинного ключа для нього неявно генерується згрупований індекс, якщо ключове слово NONCLUSTERED не вказано явно (Лістинг 1).

Найбільш поширеним варіантом кластерного індексу є стовпець Id типу int (або bigint для великих таблиць) із специфікацією ідентифікації, яка гарантує, що стовпцю автоматично присвоюються унікальні номери. В якості альтернативи часто використовуються стовпці типу uniqueidentifier, які також отримують унікальні, але не постійні значення через функцію NewId () як обмеження за замовчуванням. Це особливо корисно в розподілених середовищах, де нові рядки створюються в різних копіях бази даних, які потім об'єднуються пізніше, не перекриваючи первинні ключі.
Основним недоліком кластерних індексів є те, що їх можна сортувати лише в одному порядку. Запит, який здійснює пошук за іншим стовпцем (наприклад, за іменем), не отримає вигоди від індексу у стовпці Прізвище. Для вирішення цього використовується n негрупованих індексів.

Відображення планів виконання

Для того, щоб перевірити використання індексів у запитах, SQL Server Management Studio пропонує можливість відображати як очікувані, так і фактичні плани виконання запитів. Обидва варіанти можна найпростіше активувати за допомогою відповідних параметрів у меню запитів або, як варіант, за допомогою панелі інструментів. Слід зазначити, що відображення передбачуваного плану виконання не виконує сам запит, тоді як фактичний план виконання може бути відображений лише після виконання запиту. У графічно представлених планах виконання, які можна прочитати вгорі праворуч, ви швидко зможете побачити, чи здійснюється доступ (та як) до індексу чи до самої таблиці. У "як" слід зазначити ключові слова "сканувати" та "шукати". Пошук індексу означає, що відповідні рядки знайдені за допомогою двійкового пошуку в дереві індексу, тоді як сканування вказує на те, що таблиця або індекс були прочитані повністю, що в більшості випадків значно більш трудомістке. Більш детальну інформацію про читання планів впровадження можна знайти в посиланнях на цю статтю [1], [2], [3].

Некластеризовані індекси

Негруповані індекси представляють додаткову структуру даних, якою також управляють у деревній структурі, але листки якої містять не фактичні дані рядків, а посилання на адресу даних. Простіше кажучи, негрупований індекс поводиться як індекс в кінці книги: він сортується сам, щоб можна було швидко шукати певний термін. Після того, як термін був знайдений, за ним слід лише номер сторінки, де цей термін можна знайти. Ще одна зміна відповідної сторінки книги потрібна, щоб знайти потрібну інформацію. З індексом бази даних посилання на реальні дані, звичайно, трохи складніше, ніж простий номер сторінки. Цей процес, відомий як пошук рядків, використовує ідентифікатор рядка (скорочено RID), який складається з трьох частин:

Номер файлу бази даних (оскільки таблицю можна розділити на кілька файлів)
Номер сторінки пам'яті
Номер запису даних на цій сторінці пам'яті

Однак це стосується лише того випадку, якщо таблиця не має згрупованого індексу і тому зберігається як невпорядкована "купа". З іншого боку, якщо існує згрупований індекс, ключове значення згрупованого індексу вказується як посилання на рівень листа негрупованого індексу. Інше дерево індексу потрібно пройти, перш ніж можна буде знайти фактичні дані. (Однак додаткові зусилля для цього так званого пошуку ключів, як правило, досить низькі.)

Рис. 1: Дерево індексу для таблиці без згрупованого індексу

Великою перевагою негрупованих індексів є те, що для таблиці їх може бути декілька, які сортуються за різними стовпцями. Це означає, що тоді ви зможете шукати різні критерії з високою продуктивністю (Рис. 1).

Оскільки індекси, які не згруповані, є додатковими структурами даних, потрібно завжди пам’ятати про те, що, з одного боку, вони потребують додаткової пам’яті, а з іншого - при кожному зміні самих даних усі індекси, в яких містяться змінені стовпці, повинні бути оновлені. Якщо це можливо, ви не повинні створювати окремий індекс для кожного стовпця таблиці, але завжди зважуйте, для яких стовпців індекс має сенс і де з ним можна обійтися. Як основне правило, ви можете пам’ятати, що додаткові індекси можуть прискорити доступ до читання, але сповільнити доступ до запису. Рішення щодо того, які стовпці має сенс в індексі, може прийматись на основі таких критеріїв:

Якщо стовпець часто шукають або фільтрують, це говорить на користь індексу.
Те саме стосується стовпців, які використовуються як стовпці зовнішнього ключа, оскільки вони фільтруються під час JOIN.
Якщо стовпець містить лише кілька різних значень (одне також говорить про низьку вибірковість), це скоріше говорить проти індексу в ньому. Особливо це стосується стовпців, які використовують бітовий тип даних.
Якщо таблицю здебільшого читають і лише рідко змінюють, прийнятно більше індексів.
Якщо таблиця написана переважно (наприклад, таблиця журналу) і лише рідко читається, кількість індексів повинна бути якомога меншою.

Комбіновані покажчики

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

ВИБЕРІТЬ * ІЗ Person.Person

WHERE FirstName = 'John' AND LastName = 'Wood'

Натомість SQL Server автоматично використовуватиме індекс, з якого очікується менша кількість сторінок для читання (індекс у стовпці з більшою вибірковістю). Однак, оскільки для імені та прізвища може бути кілька записів, ще ефективніше було б мати індекс, що містить обидва стовпці, тобто комбінований індекс, який можна створити наступним чином:

СТВОРИТИ НЕКЛУСТЕРОВАНИЙ ІНДЕКС IX_Person_LastName_FirstName

ON Person.Person (Прізвище, Ім'я)

Хоча ім'я індексу (IX_Person_LastName_FirstName) є лише загальноприйнятою умовою іменування, порядок стовпців у визначенні індексу має вирішальне значення. Поставивши прізвище першим, індекс також можна ефективно використовувати, коли запит шукає лише прізвище (оскільки це основний критерій сортування для індексу). Якщо замість цього ви шукаєте лише ім’я, індекс також можна використовувати, але не через ефективний доступ через дерево бінарного індексу (Index Seek). Натомість потрібно прочитати весь індекс (сканування індексу), щоб знайти всі можливі комбінації, що містять ім’я, яке ви шукаєте (що в більшості випадків все-таки ефективніше, ніж читання всієї таблиці без індексу).

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

Індекси, що охоплюють та включають стовпці

Пояснюючи негруповані індекси, було зазначено, що існують посилання на фактичні записи даних на рівні аркушів дерева двійкового індексу (або у формі згрупованого ключа індексу, або як багатокомпонентна адреса, що складається з номера файлу, сторінки пам’яті та номера рядка). Однак в ідеалі всі запитувані стовпці знаходяться в самому індексі, тому немає необхідності слідувати за цим посиланням. Якщо наступний запит виконується з наявним комбінованим індексом у стовпцях Прізвище та Ім'я:

тоді індекс можна шукати за допомогою стовпця LastName, але він також уже містить запит LastName, так що звичайно необхідний пошук для всього рядка даних може бути опущений. Потім говорять про індекс покриття (стосовно запиту), оскільки він охоплює всі стовпці запиту.

Для того, щоб якомога частіше використовувати перевагу індексу покриття, не маючи необхідності переупорядковувати сам індекс, стовпці можуть бути інтегровані в індекс як так звані включають стовпці. Це означає, що значення цих стовпців містяться на рівні аркуша дерева індексу, але не враховуються для сортування індексу (тому достатньо, якщо стовпці існують лише на рівні аркушів, а не на вузлах вище). Для останнього показаного запиту було б достатньо такого індексу:

Можна також визначити кілька колонок, що включають, порядок яких тоді не має значення, оскільки вони не мають значення для сортування. Якби вам до індексу додати всі стовпці (або для сортування, або як колонки включення), ви змогли б змоделювати згрупований індекс, але з тим недоліком, що тоді використовується простір для посилання на сам рядок даних. що є непотрібним у цьому варіанті, оскільки воно повністю міститься в індексі. Тож цього точно не рекомендується, тим більше, що індекси можна використовувати ефективніше, чим менше вони.

Відфільтровані індекси

Починаючи з SQL Server 2008, існував ще один варіант із відфільтрованими індексами, щоб зберегти індекс якомога компактнішим і таким чином мінімізувати кількість сторінок пам'яті для читання. Індекс доповнюється реченням WHERE, так що індекс повинен бути створений лише для рядків, які відповідають вказаній умові. Це, як наслідок, призводить до обмеження, що умови фільтрування можна використовувати лише в поєднанні з негрупованими індексами.

Типовим варіантом використання відфільтрованих індексів є таблиці зі стовпцями, які не заповнюються у значній частині рядків:

ДЕ Середнє ІМЕ НЕ НУЛЕВО

Індексовані погляди

Починаючи з версії 2005, SQL Server також пропонує можливість створення індексів на основі подань. У результаті дані, запитувані поданням, надлишково зберігаються як копія, але сортуються відповідно до критеріїв індексу. Тому цю концепцію часто називають матеріалізованою точкою зору (цей термін особливо поширений в середовищі Oracle). Головною перевагою такого індексованого подання є те, що всі дані, що підлягають запиту, вже пов’язані та мають правильний порядок сортування. Таким чином, запит повинен отримати доступ лише до проіндексованого подання без попереднього об’єднання даних з кількох таблиць за допомогою умов JOIN. Додаткові перегляди рядків або ключів також опускаються, оскільки - за умови, що подання створено відповідним чином - усі стовпці, що запитуються, містяться у поданні, а отже, і в індексі на ньому.

Однак для того, щоб мати можливість використовувати індексовані подання, слід дотримуватися кількох обмежень:

Сам вигляд повинен бути створений з опцією WITH SCHEMABINDING, що запобігає зміні структури даних стовпців, що використовуються у поданні.
Індекс у поданні повинен бути створений як УНІКАЛЬНИЙ КЛАСТЕРОВАНИЙ ІНДЕКС.
Індекс не можна фільтрувати, але це не означає жодних реальних обмежень, оскільки критерій фільтра можна легко застосувати до подання, що використовується індексом.

Лістинг 2 показує, як індексований вигляд можна створити у два етапи. Після створення подання з додаванням SCHEMABINDING для цього подання створюється УНІКАЛЬНИЙ КЛАСТЕРОВАНИЙ ІНДЕКС. Якщо тепер ви виконуєте простий запит у поданні, ви можете з плану виконання визначити, що зчитувався лише сам індекс, а не таблиці за ним:

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

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

Висновок

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

Посилання та література

[1] Пантера, Роберт: "Оптимізація запитів SQL", entwickler.press, червень 2014 р., ISBN: 978-3868021233

[2] Пантера, Роберт: "SQL Server Performance Ratgeber", entwickler.press, лютий 2010 р., ISBN: 978-3868020304

[3] Фрітчі, Грант: "Плани виконання SQL Server", Публікація Simple Talk, жовтень 2012 р., ISBN: 978-smartblock>

Розробник Windows

Ця стаття була опублікована у Windows Developer. Розробник Windows надає вичерпну, нейтральну від виробника інформацію про нові тенденції та можливості в розробці програмного забезпечення та систем для технологій Microsoft.