Індекс таблиці бази даних

Індекс (англ. index) — об'єкт бази даних, що створений з метою підвищення ефективності виконання запитів. Таблиці в базі даних можуть мати велику кількість рядків, які зберігаються у довільному порядку, і їх пошук за заданим значенням шляхом послідовного перегляду таблиці рядок за рядком може займати багато часу. Індекс формується зі значень одного чи кількох стовпчиків таблиці і вказівників на відповідні рядки таблиці і, таким чином, дозволяє знаходити потрібний рядок за заданим значенням. Прискорення роботи з використанням індексів досягається в першу чергу за рахунок того, що індекс має структуру, що оптимізована для пошуку — наприклад, збалансованого дерева. Деякі СКБД розширюють можливості індексів введенням можливості створення індексів за виразами. Наприклад, індекс може бути створений за виразом upper(last_name) і відповідно буде зберігати посилання, ключем яких будуть значення поля last_name в верхньому регістрі. Крім цього, індекси можуть бути оголошенні як унікальні так і не унікальні. Унікальний індекс реалізує обмеження цілісності на таблиці, виключаючи можливість вставки значень, що повторюються.

Архітектура

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

Індекси фізично можуть бути реалізовані різними структурами. Найчастіше вживані B+ дерева і хеш-таблиці.

Послідовність стовпців в складеному індексі

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

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

Ефективність

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

Обмеження

Індекси корисні для багатьох програм, однак на їхнє використання накладаються обмеження. Візьмемо такий запит SQL: SELECT first_name FROM people WHERE last_name = 'Франкенштейн';. Для виконання такого запиту без індексу СКБД повинна перевірити поле last_name у кожному рядку таблиці (цей механізм відомий як «повний перебір» або «повний скан таблиці», у плані може відображатися словом «NATURAL»). При використанні індексу СКБД просто проходить по бінарному дереву, поки не знайде запис «Франкенштейн». Такий прохід вимагає набагато менше ресурсів, ніж повний перебір таблиці.

Тепер візьмемо такий запит: SELECT email_address FROM customers WHERE email_address LIKE '[e-mail]';. Цей запит повинен нам знайти всіх клієнтів, у яких електронна адреса закінчується на «@yahoo.com», однак навіть якщо по стовпцю email_address є індекс, СКБД все одно буде використати повний перебір таблиці. Це пов'язане з тим, що індекси будуються в припущенні, що слова/символи йдуть зліва на право. Використання символу підстановки на початку умови пошуку виключає для СКБД можливість використання пошуку по бінарному дереву. Ця проблема може бути вирішена створенням додаткового індексу за виразом reverse(email_address) і формуванням запиту виду: select email_address from customers where reverse(email_address) like reverse('[e-mail]');. У цьому випадку символ підстановки виявиться в найправішій позиції («moc.oohay@%»), що не виключає використання індексу за reverse(email_address).