Индексы: создание, типы, влияние на производительность

Что такое индексы и зачем они нужны? 🧐

Индексы в SQL — это специальные структуры данных, которые ускоряют поиск и извлечение данных из таблиц. Представьте учебник с оглавлением 📖 — без него поиск нужной главы займёт много времени, а с индексом (оглавлением) вы сразу перейдёте к нужной странице.

Без индекса СУБД вынуждена сканировать всю таблицу (FULL TABLE SCAN), что крайне неэффективно на больших объёмах данных. Индекс позволяет находить данные за O(log n) вместо O(n).

Как создать индекс? 🏗️

Создать индекс можно при помощи команды CREATE INDEX:

-- Создаём индекс по столбцу email в таблице users
CREATE INDEX idx_users_email ON users(email);

После этого запросы с условием WHERE email = '...' будут выполняться быстрее.

💡 Совет: Именуйте индексы в формате idx_таблица_столбец для ясности.

Удаление индекса:

DROP INDEX idx_users_email;

Основные типы индексов в SQL 🗂

1️⃣ B-Tree индекс (дерево) 🌳

Стандартный тип индекса в PostgreSQL, MySQL, Oracle. Подходит для:
- Точечных запросов (WHERE id = 5).
- Диапазонов (WHERE age BETWEEN 20 AND 30).
- Сортировки (ORDER BY created_at).

CREATE INDEX idx_orders_created_at ON orders(created_at);

2️⃣ Hash индекс ⚡

Работает только для точных совпадений (=), не поддерживает диапазоны или сортировку. Быстрее B-Tree для WHERE key = value.

-- В PostgreSQL:
CREATE INDEX idx_users_username_hash ON users USING HASH(username);

3️⃣ Составной индекс (Composite Index) 🧩

Индекс по нескольким столбцам. Порядок столбцов важен!

-- Индекс сначала по department_id, потом по salary
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary);

📌 Правило: Первым должен идти столбец с высокой селективностью (много уникальных значений).

Как индексы влияют на производительность? 🚀

✅ Плюсы:

Ускорение SELECT (особенно с WHERE, JOIN, ORDER BY).
Ускорение агрегаций (GROUP BY, DISTINCT).

❌ Минусы:

Замедление INSERT, UPDATE, DELETE (индекс нужно перестраивать).
Дополнительное место на диске.

Примеры:

Без индекса (медленно):

SELECT * FROM users WHERE last_name = 'Smith'; -- FULL SCAN

С индексом (быстро):

CREATE INDEX idx_users_last_name ON users(last_name);
SELECT * FROM users WHERE last_name = 'Smith'; -- Использует индекс

Когда индексировать? 📊

Индексируйте:

Ключевые столбцы в WHERE (часто используемые условия).
Столбцы в JOIN (для ускорения связей).
Столбцы в ORDER BY (если часто сортируете).

Не индексируйте:

Столбцы с низкой селективностью (например, gender с двумя значениями).
Таблицы, которые часто обновляются (индексы замедляют запись).

Как проверить, используется ли индекс? 🔍

В PostgreSQL и MySQL можно использовать EXPLAIN:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

В выводе ищите:
- Index Scan — индекс используется.
- Seq Scan — полное сканирование (возможно, индекс не задействован).

Итоги 🎯

Индексы ускоряют чтение, но замедляют запись.
B-Tree — универсальный выбор, Hash — только для точных совпадений.
Составные индексы требуют правильного порядка столбцов.
Проверяйте EXPLAIN перед оптимизацией.

Попробуйте добавить индекс к медленному запросу и замерьте разницу! ⚡

📹 Подробнее о сложных случаях индексации — в видео Данилы Бежина: https://www.youtube.com/@DanilaBezhin.