Бази даних - це спеціалізовані сховища інформації, і працюючи з ними, ми оперуємо поняттями наборів даних і операціями над цими даними, забуваючи про те, що за ними ховається реальне обладнання. Створюючи SQL-запит, ми маємо на увазі, що всі дії над таблицями здійснюються одночасно, адже поняття часу в командах SQL-мови відсутня. Коли наша база даних має невеликий розмір, ми, можливо, і не помітимо, що сервера баз даних потрібен час для обробки зазначеної нами таблиці, для пошуку необхідної рядки, вилучення потрібних записів. Але з ростом інформації, що зберігається в базі даних, ця проблема стає все більш і більш помітною, і в певний момент стає ясно, що потрібно вживати спеціальних заходів. Ми починаємо нарощувати продуктивність нашого сервера, ставлячи все більш швидке обладнання або намагаючись налаштувати вже існуюче, з метою вичавити з нього максимальну швидкодію, з однією тільки метою - прискорити виконання запитів до нашої бази.
Але перш ніж розглядати питання на "фізичному" рівні, необхідно з'ясувати, чи не можна використовувати якісь можливості, які надає сам сервер баз даних. Дуже часто причина повільного виконання запитів полягає в тому, що таблиці не індексовані. Як правило, якщо у таблиць немає індексів, в більшості випадків навряд чи вдасться істотно поліпшити продуктивність бази даних іншими способами.
При використанні індексів сервер баз даних набагато швидше витягне дані
Розглянемо, яким чином індекс таблиці може прискорити обробку запитів. У таблиці, що не має індексу, записи зберігаються безладним чином, і при спробі вилучення інформації сервер баз даних просканує всі записи з метою встановлення збігу з умовами. Як приклад візьмемо базу даних сервера MySQL. в якій зберігається інформація про товари. Припустимо, що нам потрібно отримати всі рядки про товари, вироблених в конкретній країні, і якщо ця операція здійснюється часто, то замість перебору всіх рядків ми можемо проіндексувати таблицю з товаром items по полю, який містить номери країн country_id. Створений індекс буде містити запис про кожному рядку в таблиці, причому його записи будуть відсортовані по полю country_id. Тепер при виконанні запитів сервер баз даних може дізнатися, які записи знадобляться з індексу, причому якщо нам потрібна країна з номером 5, то, дійшовши в індексі до номера 6, сервер баз даних може припинити пошук - адже рядків з номером 5 в індексі явно більше не буде.
Точно так же в кожному сервері баз даних існують алгоритми для швидкого знаходження рядків в середині списку індексу, що дозволяє оптимізувати пошук необхідних рядків. Наведений приклад не пояснює переваги створення індексів, наприклад, перед сортуванням таблиці по цьому полю, і збереження її в такому вигляді. Так, подібне рішення можна було б застосувати, якби всі запити здійснювалися з пошуком по цьому полю, але нам можуть знадобитися сортування і по інших полях, тому ми можемо створити для таблиці кілька індексів для найпопулярніших запитів - наприклад, для кількості товару, його ціною та іншими параметрами.
При вибірці інформації з декількох таблиць індекси ще більш ефективні
При використанні індексів необхідно пам'ятати про їх недоліки
Крім оптимізації запитів з умовами WHERE, індекси можу принести користь і в інших випадках. Очевидно, що сортування обраних даних теж буде здійснюватися швидше при індексуванні по цьому полю. Також швидше буде відбуватися вибірка даних при використанні агрегатних функцій MIN () і MAX () для отримання максимального і мінімального значень при угрупованнях рядків. У деяких випадках, якщо потрібно витягти інформацію, яку може надати сам індекс, звернення до таблиці бази даних взагалі не здійснюються. Може скластися невірне уявлення, що необхідно індексувати кожне поле в таблиці - гірше не буде. Це не зовсім так - індекси, незважаючи на всю їх очевидну користь, мають і деякі недоліки. Сама кидається в очі проблема полягає в тому, що файл будь-якого індексу займає певне місце на диску, і так як необхідність використання індексів найбільш актуальна для таблиць великих розмірів, додаткові індексні файли будуть рости разом з нею.
Друга проблема полягає в тому, що індекси як прискорюють вибірку інформації, так і уповільнюють операції додавання, редагування та видалення записів - адже при цьому необхідно вносити зміни в усі індекси змінною таблиці. Існує також ще кілька рекомендацій, які дозволяють збільшити ефективність застосування індексів, зводячи до мінімуму їх недоліки. Одна рекомендація вже була згадана: індексувати слід поля, які шукаються, а не вибираються, тобто хорошим кандидатом на індексування буде поле, згадане в умові WHERE. Також слід пам'ятати, що набагато ефективніше індекси використовуються для полів з унікальними значеннями. Якщо ж поле містить багато однакових значень, то індекс може себе і не виправдати. При створенні індексів особливо для довгих символьних полів, необхідно проаналізувати - чи не можна забезпечити унікальність ключа не по всьому полю, а першим 10-20 символам цього поля, що істотно заощадить розмір файлу індексу і забезпечить прискорення виконання запитів. Подібне обмеження для символьних полів рекомендується, а для полів типу TEXT і BLOB є обов'язковою.
Дуже часто, якщо ви створювали індекс не для одного поля, а відразу для декількох полів, існує можливість його використання для вибірок і в інших випадках. У цьому випадку використовується правило "лівого крайнього". Припустимо, для таблиці з товаром ми створили індекс по полях country, category, quantity - наприклад, для сортування таблиці саме в такому порядку. Цей же самий індекс ми можемо використовувати і в тих випадках, коли нам знадобитися індекс для пошуку по полю country, а також в разі необхідності індексу для одночасної сортування по полях country і category. Тому, якщо ви створювали індекс для кількох полів, при додаванні нового індексу непогано було б перевірити, чи не дублює він вже існуючі.
Створюйте індекси в міру необхідності
Створити індекс для таблиці можна як під час її створення, так і додати потім, слідуючи простим правилом - індекси треба створювати в міру необхідності. При створенні таблиці поле, що забезпечує унікальність, треба оголосити з ключовими полями PRIMARY KEY або UNIQUE, що автоматично створить індекс для цього поля. Такі поля не можуть містити повторюваних значень або дорівнювати NULL, і тому обробляються дуже швидко. Точно так же при створенні таблиці можна вказати ключове слово
INDEX імя_індекса спісок_полей.
де всі параметри після ключового слова INDEX необов'язкові. Але така можливість використовується вкрай рідко - набагато частіше виникає необхідність додати індекс до вже існуючої таблиці. Для цього можна скористатися оператором ALTER TABLE, який використовується для зміни вже існуючих таблиць:
ALTER TABLE ім'я_таблиці INDEX імя_індекса спісок_полей;
Індекс створюється за стовпцями, зазначеним в спіске_полей. Якщо імя_індекса не задані, то воно створюється автоматично на ім'я першого индексируемого поля. Для символьних полів можна зменшити довжину індексованих значень, вказавши кількість символів n для індексування в імені_поля (n) Таким чином, ми для нашої таблиці можемо створити індекс:
ALTER TABLE items INDEX country_id;
Якщо необхідність в індексі відпала, то його можна легко видалити, указу ім'я індексу:
ALTER TABLE items DROP INDEX country_id;
Існують також синоніми цих команд з більш зрозумілим синтаксисом:
CREATE INDEX імя_індекса ON ім'я_таблиці спісок_полей;
DROP INDEX імя_індекса ON ім'я_таблиці;