Что делают индексы в базе данных

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

Что такое индекс

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

Как работают индексы

Без индекса БД выполняет полное сканирование таблицы (full table scan) — проходит по всем строкам. С индексом используется быстрый поиск по структуре данных (B-Tree, Hash-таблица и т.д.).

// Пример: без индекса на email — O(n) операция
SELECT * FROM users WHERE email = user@example.com;  // Scans 1M rows

// С индексом на email — O(log n) операция
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = user@example.com;  // ~10 lookups

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

1. B-Tree индекс (стандартный)

Самый частый тип, используется по умолчанию в PostgreSQL и MySQL. Данные организованы в сбалансированное дерево.

-- Создание простого B-Tree индекса
CREATE INDEX idx_users_age ON users(age);

-- Использование: WHERE age = 25, WHERE age > 30, ORDER BY age

2. Hash индекс

Использует хеширование, быстро для точного поиска (=), но не для диапазонов.

CREATE INDEX idx_users_id USING HASH ON users(id);

3. Составной индекс (composite)

Индекс по нескольким столбцам, полезен при фильтрации по нескольким полям.

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Эффективно работает для:
SELECT * FROM orders WHERE user_id = 5 AND created_at > 2025-01-01;

4. Индекс покрытия (covering/included)

Содержит всю информацию для ответа на запрос без доступа к основной таблице.

CREATE INDEX idx_orders_cover ON orders(user_id) INCLUDE (total_amount, status);

-- Запрос полностью выполняется по индексу (index-only scan)
SELECT total_amount FROM orders WHERE user_id = 5;

5. Partial индекс

Индекс только по части данных, ускоряет запросы с фильтрами.

CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Индекс меньше, работает быстрее для:
SELECT * FROM users WHERE active = true AND email = user@example.com;

Преимущества индексов

✓ Ускорение SELECT запросов — логарифмическая сложность вместо линейной

✓ Ускорение WHERE, ORDER BY, JOIN — быстрая фильтрация и сортировка

✓ Улучшение производительности — особенно на больших таблицах (миллионы записей)

✓ Соблюдение UNIQUE и PRIMARY KEY — внутри используются индексы

Недостатки индексов

✗ Замедление INSERT, UPDATE, DELETE — нужно обновлять индекс при изменении данных

✗ Потребление дисковой памяти — индекс занимает дополнительное место на диске

✗ Overhead на обслуживание — дефрагментация, пересчёт статистики

✗ Неправильные индексы замедляют БД — оптимизатор может выбрать плохой план

Лучшие практики использования индексов

1. Индексируй столбцы в WHERE условиях:

CREATE INDEX idx_status ON orders(status);
SELECT * FROM orders WHERE status = completed;

2. Используй составные индексы для частых комбинаций:

CREATE INDEX idx_user_order ON orders(user_id, status);
SELECT * FROM orders WHERE user_id = 5 AND status = pending;

3. Не переусложняй — один индекс часто лучше 10:

// Минимум — только необходимые индексы
// Максимум — тестирование на реальных данных

4. Проверяй план запроса (EXPLAIN) перед оптимизацией:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 5;
-- Покажет: использует ли индекс, сколько строк сканирует

5. Регулярно проверяй неиспользуемые индексы:

-- PostgreSQL: найди мёртвые индексы
SELECT schemaname, tablename, indexname 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

Пример с Java/Hibernate

@Entity
@Table(name = "users")
public class User {
    @Id
    private Long id;
    
    @Column(name = "email")
    @Index(name = "idx_users_email")  // Создаёт индекс при миграции
    private String email;
    
    @Column(name = "status")
    @Index(name = "idx_users_status")
    private String status;
}

// Или в миграции Flyway/Liquibase:
// CREATE INDEX idx_users_email ON users(email);

Заключение

Индексы — это фундамент производительности БД. Правильное их использование может ускорить запросы в 100-1000 раз, но неправильное — замедлить всё. Всегда:

• Анализируй медленные запросы через EXPLAIN
• Тестируй индексы на реальных объёмах данных
• Удаляй неиспользуемые индексы
• Помни о компромиссе между скоростью SELECT и UPDATE операций