Управління індексами та ключами для підвищення продуктивності

Сьогодні розберемо одну з ключових тем у роботі з базами даних — індекси та ключі. Чому це важливо? Бо повільні запити в продакшені — це саме те, що може назавжди зіпсувати вам репутацію у світі веб-розробки.

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

Що таке індекс у базі даних?

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

А якщо книги відсортовані за алфавітом і в кожної є номер, вказаний у каталозі? Тепер пошук простіший. Ви просто дивитесь у каталог і миттєво знаходите потрібну книгу. Цей каталог і є індекс!

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

Приклад на SQL:

CREATE INDEX idx_user_name ON users(name);

Тепер база даних шукатиме по полі name не всю таблицю, а тільки відповідні індекси.

Типи індексів

SQL підтримує кілька типів індексів:

• B-Tree індекси: використовуються для пошуку діапазонів (наприклад, знайти записи, де age > 25).
• Hash індекси: швидкі, але підходять лише для точного відповідності (наприклад, email = 'user@example.com').
• Full-text індекси: корисні для пошуку по тексту, наприклад, "знайти записи, де згадується слово 'Python'".
• Геопросторові індекси: використовуються для роботи з геоданими.

---

Створення та керування індексами в SQLAlchemy

Тепер давайте подивимось, як це виглядає в SQLAlchemy.

Створення індексу в моделі SQLAlchemy — це як поставити "турбонаддув" на вашу таблицю.

Приклад:

from sqlalchemy import Column, Integer, String, Index
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String, index=True)  # Додаємо простий індекс
    email = Column(String)

# Альтернативний спосіб через об'єкт Index
Index('idx_email_unique', User.email, unique=True)

Тут ми створили два індекси:

• Індекс на полі name, додавши параметр index=True.
• Індекс з унікальністю для email (unique індекс).

Іноді індекси можуть стати не потрібні. Наприклад, якщо вони не використовуються в запитах, але уповільнюють операції INSERT.

Для видалення індексу використовуйте SQL-команду:

DROP INDEX idx_user_name;

У випадку з SQLAlchemy зміни індексів зазвичай робляться через Alembic-міграції.

---

Оптимізація через використання ключів

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

У SQLAlchemy, щоб задати первинний ключ, ми використовуємо параметр primary_key=True:

id = Column(Integer, primary_key=True)

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

Ми вже говорили про ForeignKey на попередніх лекціях. Але нагадаємо: це такий зовнішній ключ, який створює зв'язок з іншою таблицею.

from sqlalchemy import ForeignKey

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))

Це зв'язує поле user_id в таблиці posts з id в таблиці users. Зовнішні ключі — основа реляційної моделі, а їх індексація використовується для прискорення JOIN операцій.

---

Як правильно використовувати індекси

Індекси — потужний інструмент, але з великою силою приходить велика відповідальність. Якщо індексів занадто багато, це може уповільнити операції вставки, оновлення й видалення. Тому важливо розуміти, коли і де їх застосовувати.

Поради

1. Додавайте індекси тільки на ті поля, які часто використовуються в фільтрах (WHERE), сортуваннях (ORDER BY) або приєднаннях (JOIN).
2. Уникайте надлишкових індексів. Якщо у вас вже є індекс для поля email, немає сенсу додавати другий індекс для UPPER(email).
3. Ураховуйте розмір таблиці. Індекси особливо корисні для великих таблиць (від кількох тисяч рядків і більше).
4. Використовуйте комбіновані індекси. Якщо запити часто фільтруються або сортуються по кількох полях, об'єднайте їх в один індекс.

Приклад комбінованого індексу:

Index('idx_user_name_email', User.name, User.email)

Такий індекс покращить продуктивність запитів типу:

SELECT * FROM users WHERE name='Alice' AND email='alice@example.com';

---

Приклади оптимізації запитів з індексами

Припустимо, у нас є таблиця orders з мільйоном записів. Ми хочемо знайти всі замовлення, зроблені клієнтом з ідентифікатором 42.

Без індексу

SELECT * FROM orders WHERE customer_id = 42;

База сканує ВСЮ таблицю, щоб знайти записи. Це називається повним скануванням таблиці (table scan).

З індексом

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

CREATE INDEX idx_customer_id ON orders(customer_id);

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

---

Приклади помилок і проблем

1. Надлишкові індекси. Якщо ви додасте індекс на кожне поле "про всяк випадок", це уповільнить операції запису (наприклад, INSERT, UPDATE), бо базі доведеться оновлювати кожен індекс.
2. Погане використання комбінованих індексів. Індекс на (name, email) ефективний лише якщо запит фільтрує спочатку по name, а потім по email. Якщо ви будете фільтрувати тільки по email, індекс може бути марним.
3. Величезні індекси. Якщо поле занадто велике (наприклад, рядок довжиною 255 символів), індекс займе багато місця.

---

Практичне завдання

1. Створіть модель Product з полями id, name і price.
2. Додайте унікальний індекс на name і окремий індекс на price.
3. Напишіть SQL-запит, який витягує всі продукти, ціна яких більше 100, і перевірте, що використовується індекс.

Підказка: використовуйте EXPLAIN в PostgreSQL або MySQL, щоб перевірити, який індекс використовується.

---

Ось і все на сьогодні! Тепер ви знаєте, як змусити базу даних працювати швидше, замість того щоб мучити свій сервер. Використовуйте індекси з розумом, і ваші застосунки стануть шустріші, а користувачі — задоволені.