Что такое индексы в БД?

Индексы в базах данных

Индекс в БД — это структура данных, которая ускоряет поиск и получение данных из таблицы. Думай об индексе как об оглавлении в конце книги: вместо того чтобы прочитать всю книгу линейно, ты смотришь в оглавление, находишь нужную тему и идёшь прямо на нужную страницу.

Как это работает

Без индекса (Full Table Scan)

SELECT * FROM users WHERE email = 'john@example.com';

Как работает:

Табль users
┌──────┬─────────────────────┐
│ id   │ email               │
├──────┼─────────────────────┤
│ 1    │ alice@example.com    │ Проверяем? Нет
│ 2    │ bob@example.com      │ Проверяем? Нет
│ 3    │ john@example.com     │ Проверяем? ДА! Найдено!
│ 4    │ charlie@example.com  │ Уже не проверяем
│ ..   │ ..                   │
└──────┴─────────────────────┘

Время: O(n) — пришлось проверить все 1М строк

С индексом (Indexed Search)

CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';

Как работает:

Индекс (B-Tree структура)
             "john"
            /      \
      "alice"      "john"
       /  \        /   \
    ... ... ... ...
    ↓
  Row ID: 3
  ↓
  Берём строку с id=3 из таблицы

Время: O(log n) — вместо проверки 1М строк, проверяем ~20 узлов дерева

Типы индексов

1. Primary Key Index

Определение: Уникальный индекс на основной ключ таблицы.

CREATE TABLE users (
    id INT PRIMARY KEY,  -- Автоматически создаётся индекс
    name VARCHAR(100),
    email VARCHAR(100)
);

Характеристики:

• ✅ Гарантирует уникальность
• ✅ Ускоряет поиск по ID (самый частый случай)
• ✅ Не может быть NULL

2. Unique Index

Определение: Индекс, гарантирующий уникальность значений.

CREATE UNIQUE INDEX idx_email ON users(email);

Использование:

• Email должен быть уникален (каждый email только 1 раз)
• Username должен быть уникален
• Phone number должен быть уникален

3. Non-Unique Index (Regular Index)

Определение: Обычный индекс для ускорения поиска, не требует уникальности.

CREATE INDEX idx_created_at ON posts(created_at);
CREATE INDEX idx_user_id ON posts(user_id);

Использование:

• Поля, по которым часто фильтруют (WHERE status = 'active')
• Поля, по которым часто сортируют (ORDER BY created_at DESC)
• Поля, по которым делают JOIN

4. Composite Index (Многополевой индекс)

Определение: Индекс на несколько полей одновременно.

CREATE INDEX idx_user_date ON posts(user_id, created_at);

Когда использовать:

• Часто используются вместе: WHERE user_id = 5 AND created_at > '2024-01-01'
• Работает для (user_id, created_at), но НЕ для (created_at, user_id)

Правило слева направо (Left-to-Right):

Индекс: (user_id, created_at)
Работает: WHERE user_id = 5
Работает: WHERE user_id = 5 AND created_at > '2024-01-01'
НЕ работает: WHERE created_at > '2024-01-01'  ← Пропущен первый столбец

5. Full-Text Index

Определение: Специальный индекс для полнотекстового поиска.

CREATE FULLTEXT INDEX idx_content ON articles(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('database' IN BOOLEAN MODE);

Использование:

• Поиск в больших текстовых полях
• Search engine функционал

6. Partial Index (PostgreSQL)

Определение: Индекс на подмножество строк.

-- Индексируем только активные пользователи
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

Преимущества:

• ✅ Меньше памяти на индекс
• ✅ Быстрее обновление (обновляем меньше индексов)

Структуры данных индексов

B-Tree (Наиболее частый)

Балансированное дерево для быстрого поиска

             40
           /    \
         20      60
       / |  \   / | \
      10 15 30 50 70 80

Используется в:

• Primary key, unique, regular indexes
• Все современные БД (PostgreSQL, MySQL, etc.)

Сложность: O(log n)

Hash Index

Быстро для точных совпадений

Email хешируется в число → быстрый поиск
john@example.com → Hash(123) → Row ID: 3

Использование:

• Очень быстрый поиск по точному совпадению
• НЕ подходит для range queries (WHERE age > 18)

Bitmap Index

Использование:

• Для столбцов с небольшим количеством уникальных значений
• Статус: active/inactive/pending
• Пол: male/female

Особенность: Очень компактный по памяти

Пример: Оптимизация медленного запроса

Проблема

-- Запрос работает 5 секунд! Медленно!
SELECT u.name, COUNT(*) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC;

Анализ (EXPLAIN):

Seq Scan on orders o  ← Сканируем ВСЕ строки!
Filter: (created_at > '2024-01-01')
Join with users u

Проблема: Нет индекса на orders.created_at

Решение

-- Добавляем индекс
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Может быть ещё лучше — composite индекс
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

После оптимизации:

Index Scan on idx_orders_user_date  ← Используем индекс!
Index Cond: (created_at > '2024-01-01')
Join with users u

Время: 5 сек → 50 мс (100x ускорение!)

Плюсы и минусы индексов

Преимущества

✅ Ускорение SELECT запросов: O(n) → O(log n) ✅ Ускорение JOIN операций ✅ Ускорение WHERE условий ✅ Ускорение ORDER BY и GROUP BY ✅ Ускорение DISTINCT

Недостатки

❌ Требуют память: Индекс может занять 10-50% размера таблицы ❌ Замедляют INSERT/UPDATE/DELETE: Нужно обновлять индекс при каждом изменении ❌ Требуют обслуживание: VACUUM (PostgreSQL), ANALYZE, DEFRAGMENT ❌ Могут не использоваться: БД выбирает неправильно

Правила создания индексов

1. Индексируй поля из WHERE

-- Часто пишешь:
SELECT * FROM users WHERE status = 'active' AND country = 'USA';

-- Создай:
CREATE INDEX idx_status_country ON users(status, country);

2. Индексируй поля из JOIN

-- JOIN по user_id:
SELECT * FROM orders JOIN users ON orders.user_id = users.id;

-- Нужен индекс на orders.user_id
CREATE INDEX idx_user_id ON orders(user_id);

3. Избегай индексов на редко используемые поля

-- Плохо: индекс на поле, которое никто не ищет
CREATE INDEX idx_internal_code ON users(internal_code);

-- Хорошо: индекс на email, который ищут постоянно
CREATE INDEX idx_email ON users(email);

4. Избегай слишком много индексов на одну таблицу

• Оптимально: 3-5 индексов на таблицу
• Максимум: 10 индексов
• Слишком много индексов замедлят INSERT/UPDATE

5. Используй EXPLAIN для проверки

-- Проверь, использует ли БД твой индекс
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Если видишь "Seq Scan" вместо "Index Scan" — индекс не работает

Мониторинг индексов

PostgreSQL: Найти неиспользуемые индексы

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- Не используется

PostgreSQL: Размер индекса

SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_indexes
JOIN pg_stat_user_indexes USING (indexname);

Выводы

Индексы — это критическая часть оптимизации БД. Они ускоряют чтение данных в 100+ раз, но требуют внимательного планирования.

Правило большого пальца:

• Индексируй поля, часто используемые в WHERE/JOIN/ORDER BY
• Используй EXPLAIN для проверки
• Регулярно анализируй производительность
• Удаляй неиспользуемые индексы — они только замедляют обновления

Мастерское использование индексов может превратить медленную систему в молниеносную.