Індекси (іndexes)
Однією з найважливіших задач розробника БД є коректне визначення таблиці, яка служить для підтримки оптимальної продуктивності системи. Таблична структура БД не залежить від способів доступу до даних і фізичного їх збереження на диску. Однак існують певні обмеження на використання типів даних для стовпчиків, які служать первинними ключами. Крім того, в СУБД існує механізм індексів і ключів, який дозволяє оптимізувати процес обробки запитів.
За відсутності індексів СУБД повинна повністю відсканувати таблицю, тобто прочитати кожен її рядок, перш ніж дати відповідь на запит. Якщо таблиця дуже велика, то цей процес займе багато часу. Індекси надають можливість створення покажчиків на необхідні дані. Вони виконують в БД роль, аналогічну тій, яку в довіднику виконує алфавітний (предметний) покажчик. Індекс в базі даних являє собою перелік важливих значень з посиланнями на сторінки таблиць бази даних, на яких міститься необхідна інформація (тобто значення). Таким чином сервер прочитує відносно невелику кількість індексів та за їх допомогою знаходить дані, необхідні для відповіді на запит.
Отже, індекси – це набори унікальних значень деякої таблиці і відповідні їм покажчики на сторінки таблиці, де фізично знаходяться ці значення. Це структура, яка дозволяє прискорити виборку рядків з таблиці. Індекси є ще одним видом об’єкта бази даних, для створення якого потрібна додаткова пам’ять, так само, як і для таблиць. Перевагою індексів є те, що вони у цілому скорочують кількість операцій введення/виведення, які необхідні для доступу до певного фрагмента даних і виборки потрібного значення.
При роботі з даними певна її частина припадає на оновлення індексів. Якщо в СУБД виконується багато операцій вставки, має сенс обмежити кількість активних індексів таблиць бази даних. Адже коли відбувається вставка чи оновлення записів, індекси теж повинні оновлюватися. Фактично, при оновленні записів в базі даних СУБД необхідно виконати такий самий об’єм роботи, як і при вставці. Якщо переважно виконуються запити, тобто виконується відносно невелика кількість операцій вставки і основна увага приділяється аналізу існуючої інформації, то можна створити всі необхідні індекси, щоб підвищити продуктивність виконання запитів. В даному випадку обмежувачем буде лише об’єм дискового простору, оскільки місце на диску необхідно кожному окремому індексу.
Перед тим як створювати індекс, слід добре розібратися, для чого він призначений, які запити будуть направлятися в базу даних. “Кандидатами” в індекси вважають:
Зовнішні ключі. Стовпчики таблиці, які є зовнішніми ключами, – перші “кандидати” в індекси. Існує дуже мало випадків, коли не слід обирати для індекса ключ. Він дозволяє суттєво скоротити час пошку потрібної інформації.
Запити з великими результуючими множинами. Для запитів, що повертають великі набори даних, необхідно індексувати все, що лише можна (хоча це явне порушення правил побудови системи “клієнт-сервер”). Зрозуміло, слід намагатися знайти способи зменшення розміру результуючої множини, хоч це і не завжди вдається. Тому слід бути готовими підтримати виконання подібних запитів за допомогою індексів.
Стовпчики, які вказані в директивах ORDER BY, GROUP BY оператора SELECT мови SQL, інколи дуже корисно використовувати в індексах.
Для створення індекса МS SQL Server, наприклад, пропонує два методи – за допомогою графічної програми SQL Server Enterprise Manager і з використанням оператора CREATE INDEX мови Тransact-SQL.
У залежності від необхідних опцій визначається тип індекса. Так, при використанні опції UNIQUE створюється унікальний індекс для вказаного стовпчика. При цьому в останньому не повинно бути однакових значень. Застосування індексів для забезпечення цілісності даних в таблиці є найпоширенішим. Якщо вказується опція CLUSTERED, то SQL Server створить для таблиці кластерний індекс. Кластерний індекс змушує SQL Server зберігати табличні дані в порядку, визначаємому цим індексом. Інакше кажучи, порядок, що визначається цим індексом, відповідає фізичному розміщенню даних в таблиці. Використання цього індекса для фізичного зберігання даних в таблиці – це гарний спосіб значно підвищити швидкість доступу до інформації. Відповіно, при створенні некластеризованого індекса на сторінках індексних даних будуть міститися покажчики на реальні сторінки табличних даних в базі. Можливе визначення в якості індекса групи файлів; це припускає його розміщення окремо від даних (тобто в іншій групі файлів), дозволяючи підвищити продуктивність некластерних індексів.
Ім’я індекса повинно бути унікальним. Індекс може бути складним (складається з кількох стовпчиків) і простим (у його складі лише один стовпчик).
Вміння працювати з індексами таблиць є дуже важливим для створення оптимізованої БД, яка не “зависне” від численних запитів користувачів, змушуючих проводити сканування таблиць.