Когда стоит добавлять индексы в БД?

Когда добавлять индексы в БД

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

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

Индекс — это структура данных (обычно B-дерево), которая ускоряет поиск данных в таблице за счёт дополнительной памяти. Без индекса БД выполняет полное сканирование таблицы (Full Table Scan) — O(n), с индексом — O(log n).

// Пример: запрос без индекса
SELECT * FROM users WHERE email = user@example.com;  -- Full Scan: 1000000 строк

// С индексом на email
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = user@example.com;  -- Binary Search: ~20 операций

Первичные критерии для добавления индекса

1. Столбцы в WHERE клаузулах

Главное правило: индексируй столбцы, по которым часто фильтруешь:

-- Часто выполняемый запрос
SELECT * FROM posts WHERE user_id = 123;

-- Создай индекс
CREATE INDEX idx_posts_user_id ON posts(user_id);

В Java это выглядит так:

// JPA/Hibernate
@Entity
@Table(name = "posts", indexes = {
    @Index(name = "idx_posts_user_id", columnList = "user_id")
})
public class Post {
    @Id
    private Long id;
    
    @Column(name = "user_id")
    @Index  // или
    private Long userId;
}

// SQL migration
CREATE INDEX idx_posts_user_id ON posts(user_id);

2. Столбцы в JOIN условиях

Для связей между таблицами нужны индексы на внешних ключах (FK):

-- Частый запрос
SELECT p.*, u.name 
FROM posts p 
JOIN users u ON p.user_id = u.id
WHERE u.email = user@example.com;

-- Индексы на FK
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_users_email ON users(email);

В Hibernate:

@Entity
public class Post {
    @ManyToOne
    @JoinColumn(name = "user_id", foreignKey = @ForeignKey(name = "fk_posts_user_id"))
    private User user;
}

3. Столбцы в ORDER BY и GROUP BY

Для сортировки и группировки больших наборов данных:

-- Медленно без индекса
SELECT user_id, COUNT(*) as count
FROM posts
GROUP BY user_id
ORDER BY count DESC;

-- Добавь индекс
CREATE INDEX idx_posts_user_id ON posts(user_id);

4. Уникальные столбцы

Все уникальные столбцы должны быть индексированы (это обеспечивает уникальность):

@Entity
public class User {
    @Id
    private Long id;
    
    @Column(unique = true)  // Автоматически создаёт индекс
    private String email;
    
    @Column(unique = true)
    private String username;
}

Композитные индексы

Для запросов с несколькими условиями:

-- Запрос с двумя условиями
SELECT * FROM posts 
WHERE user_id = 5 AND status = published;

-- Композитный индекс (порядок важен!)
CREATE INDEX idx_posts_user_status ON posts(user_id, status);

Правило для композитных индексов:

Порядок столбцов в индексе важен:

• Сначала более селективные столбцы (с большим количеством уникальных значений)
• Затем менее селективные

-- ХОРОШО (status имеет ~100 значений, user_id — миллионы)
CREATE INDEX idx_posts_user_status ON posts(user_id, status);

-- ПЛОХО (обратный порядок менее эффективен)
CREATE INDEX idx_posts_status_user ON posts(status, user_id);

Когда НЕ добавлять индекс

1. Столбцы с низкой кардинальностью

Если столбец имеет мало уникальных значений, индекс может быть менее эффективен, чем Full Scan:

-- ПЛОХО: только 2 значения (true/false)
CREATE INDEX idx_users_is_active ON users(is_active);

-- ХОРОШО: используй только если нужно искать is_active=true в огромной таблице
-- и результат — маленькая часть
CREATE INDEX idx_users_is_active ON users(is_active)
WHERE is_active = true;  -- Partial index

2. Столбцы, которые часто обновляются

Индексы требуют пересчёта при каждом UPDATE, что замедляет запись:

-- ПЛОХО: updated_at меняется каждый раз
CREATE INDEX idx_posts_updated_at ON posts(updated_at);

-- ХОРОШО: используй только если эта колона часто в WHERE

3. Маленькие таблицы

Для таблиц с < 1000 строк индексы часто неэффективны:

-- Для маленькой справочной таблицы индекс может замедлить
CREATE TABLE countries (id INT, name VARCHAR(100));
-- Индекс не нужен — Full Scan быстрее

4. Функции в WHERE

В большинстве случаев индексы не работают с функциями:

-- ПЛОХО: индекс на created_at НЕ используется
SELECT * FROM posts 
WHERE YEAR(created_at) = 2024;

-- ХОРОШО: переделай запрос
SELECT * FROM posts 
WHERE created_at >= 2024-01-01 AND created_at < 2025-01-01;
-- Теперь индекс на created_at используется

Стратегия добавления индексов

Шаг 1: Профилирование и анализ

-- PostgreSQL: смотри slow queries
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 5;

-- MySQL: enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;

-- Oracle: используй explain plan
EXPLAIN PLAN FOR SELECT * FROM posts WHERE user_id = 5;

Шаг 2: Добавь индексы на основе анализа

-- Проверь, есть ли индекс
SELECT * FROM information_schema.STATISTICS 
WHERE TABLE_NAME = posts AND COLUMN_NAME = user_id;

-- Добавь индекс
CREATE INDEX idx_posts_user_id ON posts(user_id);

Шаг 3: Измери улучшение

-- ДО: Full Scan
EXPLAIN SELECT * FROM posts WHERE user_id = 5;  -- rows scanned: 1000000

-- ПОСЛЕ: Index Scan
EXPLAIN SELECT * FROM posts WHERE user_id = 5;  -- rows scanned: 100

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

B-Tree индекс (по умолчанию)

• Используется для сравнений (=, >, <, BETWEEN)
• Подходит для большинства случаев

Hash индекс

• Только для точного совпадения (=)
• Быстрее B-Tree для точного поиска

CREATE INDEX idx_users_email ON users(email) USING HASH;  -- MySQL

Full-text индекс

• Для поиска по тексту (LIKE, MATCH)

CREATE FULLTEXT INDEX idx_posts_content ON posts(content);
SELECT * FROM posts WHERE MATCH(content) AGAINST(java);

GiST, GIN индексы (PostgreSQL)

• Для сложных типов данных (JSON, geometry)

CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

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

-- PostgreSQL: неиспользуемые индексы
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- MySQL: размер индексов
SELECT OBJECT_NAME, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_NAME = posts;

Чеклист добавления индекса

• Есть ли на столбце WHERE клаузула? → Добавь индекс
• Это внешний ключ (FK)? → Добавь индекс
• Это столбец в JOIN? → Добавь индекс
• Это UNIQUE столбец? → Добавь индекс
• Столбец часто в ORDER BY/GROUP BY? → Рассмотри индекс
• Это справочник (< 1000 строк)? → Индекс не нужен
• Столбец часто обновляется? → Опасайся замедления
• Проведён EXPLAIN ANALYZE? → Проверь использование индекса

Итоговая рекомендация

Добавляй индекс, если:

1. Столбец часто в WHERE, JOIN, ORDER BY условиях
2. EXPLAIN ANALYZE показывает Full Table Scan
3. Таблица > 1000 строк
4. Кардинальность столбца достаточно высокая (> 10 уникальных значений)

Избегай индексов на:

1. Столбцах с низкой кардинальностью
2. Столбцах, которые часто обновляются
3. Маленьких таблицах
4. Столбцах, которые используются с функциями