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

Навіщо потрібні індекси?

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

Порядок колонок у індексі: чому це важливо?

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

Принципи індексування зліва направо

1. Логіка порядку колонок

Селективність першої колонки:

Перша колонка в індексі повинна мати дані з високою унікальністю. Наприклад, для фільтрації за датою, де значення часто повторюються, краще зробити першу колонку селективнішою, наприклад, user_id, якщо дані запитуються для конкретного користувача.

Враховуйте порядок WHERE, GROUP BY, ORDER BY:

Якщо запит фільтрує дані по кількох колонках, перша колонка індексу повинна збігатися з першою колонкою за умови WHERE. Наприклад:

SELECT * FROM orders

WHERE user_id = 123 AND status = 'completed';

Для цього запиту індекс (user_id, status) буде ефективним.

2. Індекси композитних колонок

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

Приклад:

SELECT * FROM sales
WHERE region = 'North'; - Індекс працює

SELECT * FROM sales

WHERE region = 'North' AND country=‘Finland’ AND date = '2025-01-01';

Індекс (region, country, date) буде ефективним. Але якщо запит буде:

SELECT * FROM sales

WHERE country=‘Finland’ AND region=‘North’

Індекс (region, country, date) не дасть очікуваного прискорення.
 

3. Облік напрямків сортування

Якщо у запиті використовується сортування ORDER BY, то важливо враховувати напрямок сортування (ASC або DESC) під час створення індексу. Наприклад:

SELECT * FROM sales

WHERE region=‘North’

ORDER BY country DESC;

Індекс (region, country DESC) пришвидшить такий запит.
 

Помилки під час створення індексів

1. Неправильний порядок колонок

Неправильний порядок колонок в індексі може зробити його марним. Наприклад, для індексу (B, A) запит із фільтром WHERE A = ? AND B =? не використовуватиме індекс ефективно.

2. Надлишкові індекси

Створення індексу на кожну комбінацію колонок призводить до збільшення обсягу пам'яті та зниження продуктивності запису даних. Індекси потрібно оптимізувати та переглядати.

3. Ігнорування зміни запитів

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

Практичне застосування

Приклад завдання:

Є таблиця orders, що містить дані:

order_id (унікальний ідентифікатор замовлення),

customer_id (ідентифікатор клієнта),

order_date (дата замовлення),

status (статус замовлення).

Часто виконуються такі запити:

Отримати всі замовлення клієнта за конкретний день:

SELECT * FROM orders

WHERE customer_id =? AND order_date =?;

Отримати замовлення за певний день за статусом:

SELECT * FROM orders

WHERE order_date =? AND status =?;

Рішення:

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

(customer_id, order_date) для першого запиту.

(order_date, status) для другого запиту.

Це дозволить оптимізувати обидва сценарії.

Висновок

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