Використання індексів в таблицях бази даних

Що являє собою індекс? Індекс – це впорядкований вказівник на записи в таблиці.

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

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

Індекс не є частиною таблиці – це окремий об’єкт, пов’язаний з таблицею і іншими об’єктами бази даних. Це дуже важливий момент реалізації СУБД, що дозволяє відділити збереження інформації від її подання.

Індекси використовуються в трьох основних випадках:

  1. Прискорення виконання запитів. Індекси створюються для полів, які використовуються в умовах пошуку SQL-запитів.
  2. Забезпечення унікальності значень в полях. Обмеження первинного ключа вимагає, щоб в усій таблиці не знайшлося двох однакових значень полів, що входять до первинного ключа. Щоб виконати цю умову, необхідно при кожній вставці нового запису здійснювати пошук такого ж значення, яке буде вставлене.
  3. Забезпечення цілісності посилань. Обмеження зовнішніх ключів Foreign key використовується для перевірки того, щоб значення, які вставляються до таблиці, обов’язково існували в іншій таблиці. При створенні зовнішнього ключа автоматично створюється індекс, який використовується як для прискорення запитів, що використовують з’єднання таблиць, так і для перевірки умов зовнішнього ключа.

Формат команди, що створює індекси має вид:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
INDEX <Ім’я індекса> ON <Ім’я таблиці> (<Стовпець1> [, <Стовпець2> …]);

Мінімальним виразом, що створює індекс, є:

CREATE INDEX model_index ON Cars (model);

В цьому прикладі створюється індекс з іменем model_index для таблиці Cars, причому індексованим полем є поле model. Індекс є зростаючим, тобто значення в ньому впорядковані за зростанням, а також не унікальним, тобто значить, що поле model може мати декілька однакових значень. Це самий простий і самий поширений приклад індексу.

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

У визначенні індексу є ще одна опція – UNIQUE. Якщо її вказати, то індекс дозволить заносити до таблиці лише унікальні значення. Фактично це служить основою для реалізації унікальних ключів (UNIQUЕ KEY).

Також, дуже важливим аспектом у використанні індексів є оптимізація їх продуктивності. Для чого це потрібно? Щоб відповісти на дане питання, нам доведеться ще раз сказати про те, що індекси реалізовані у вигляді “дерева”. І коли до таблиці додається, змінюється або видаляється запис, в дерево додається нова гілка. Причому гілка додається не в середину дерева, а на кінць іншої гілки. Поступово дерево стає все більш “розлогим”, а пошук по ньому – все менш ефективним.

Розглянемо три способи, які дозволяють відновити продуктивність малоефективного індексу:

1. Перебудові індексу за допомогою команди ALTER INDEX, яка має наступний формат:

ALTER INDEX <Ім’я індекса> {ACTIVE | INACTIVE};

В даній команді параметри ACTIVE і INACTIVE відповідають за стан індексу, в який його можна перевести за допомогою команди ALTER INDEX. Параметр ACTIVE означає, що індекс активний і може використовуватися у всіх запитах і процедурах. Зміна стану індексу в INACTIVE (неактивний) призводить до заборони його використання. Для перебудови дерева треба послідовно виконати дві команди:

ALTER INDEX model_index INACTIVE;
ALTER INDEX model_index ACTIVE;

2. Перестворення індексу за допомогою послідовного використання команд DROP INDEX і CREATE INDEX. Тобто з допомогою команди DROP INDEX здійснюємо видалення індексу з бази даних. Після чого використавши команду CREATE INDEX заново створюємо його з тим же іменем і з тими ж параметрами. Синтаксис команди CREATE INDEX нам вже відомий, розглянемо синтаксис команди DROP INDEX:

DROP INDEX <Ім’я індекса>;

3. Третій спосіб поліпшити продуктивність індексу – це зібрати статистику за допомогою команди SET STATISTICS. Статистика таблиці – це величина в межах від 0 до 1 , значення якої залежить від числа різних (неоднакових) записів у таблиці. Оптимізатор InterBase використовує статистику для визначення ефективності застосування того чи іншого індексу в запиті. Коли число записів у таблиці може сильно змінюватися (наприклад, при великій кількості вставок або видалень), то перерахунок статистики може значно поліпшити продуктивність.

Команда перерахунку статистики наступна:

SET STATISTICS INDEX <Ім’я індекса>;

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

2 коментаря

  1. Іван, ми дуже раді, що даний матеріал став для Вас корисним та зрозумілим.

Залишити коментар

Your email address will not be published. Required fields are marked *

*