Какие плюсы и минусы создания большого количества индексов?

Плюсы и минусы большого количества индексов в базе данных

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

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

Индекс — это структура данных (обычно B-tree), которая хранит отсортированные значения одного или нескольких столбцов и указатели на полные строки таблицы. Индекс позволяет базе данных найти данные без сканирования всей таблицы.

-- Создание индекса
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_user_date ON orders(user_id, created_at);

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

1. Ускорение SELECT запросов

-- Без индекса: полное сканирование таблицы (O(n))
SELECT * FROM users WHERE email = 'john@example.com';
-- Время: 1000 мс (если 1 млн строк)

-- С индексом на email: быстрый поиск (O(log n))
SELECT * FROM users WHERE email = 'john@example.com';
-- Время: 1 мс

2. Ускорение JOIN операций

-- С индексом на user_id в таблице orders, join выполняется быстро
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

3. Ускорение ORDER BY и GROUP BY

-- Индекс позволяет избежать сортировки в памяти
SELECT * FROM orders ORDER BY created_at;

4. Обеспечение уникальности

-- UNIQUE индекс предотвращает дублирование
CREATE UNIQUE INDEX idx_user_email ON users(email);

Минусы большого количества индексов

1. Замедление INSERT операций

При каждой вставке новой строки база данных должна обновить ВСЕ индексы.

// Если у таблицы users 10 индексов:
// 1. INSERT в основную таблицу
// 2. UPDATE idx_1
// 3. UPDATE idx_2
// ...
// 11. UPDATE idx_10

public class UserRepository {
    public void createUser(User user) {
        // С 10 индексами — это в 10 раз медленнее
        userRepository.save(user);  // долго
    }
}

// Тест производительности
public void testInsertPerformance() {
    // 1000 вставок
    // Без индексов: 100 мс
    // С 10 индексами: 1000 мс (10x медленнее)
}

2. Замедление UPDATE операций

-- UPDATE должен обновить строку И все индексы
UPDATE users SET email = 'newemail@example.com' WHERE id = 123;
-- Базе нужно обновить основную таблицу и все индексы с этим столбцом

3. Замедление DELETE операций

-- DELETE должен удалить строку из таблицы И всех индексов
DELETE FROM users WHERE id = 123;
-- Все индексы должны быть обновлены

4. Потребление дискового пространства

Таблица users: 1 ГБ
10 индексов: 5-7 ГБ (каждый индекс занимает 50% размера таблицы)
Итого: 6-8 ГБ вместо 1 ГБ

5. Замедление резервного копирования

Основная таблица 1 ГБ + 10 индексов = 6-8 ГБ для резервной копии.

6. Потребление памяти при загрузке в RAM

База данных старается держать часто используемые индексы в памяти (буфер-пул). Много индексов = много памяти.

7. Замедление статистики и анализа планировщика

Планировщик запросов (Query Optimizer) должен анализировать все индексы для выбора лучшего плана.

// EXPLAIN показывает план выполнения
// С 10 индексами планировщик дольше выбирает лучший
SELECT * FROM users WHERE email = 'john@example.com';
// EXPLAIN ANALYZE показывает, какой индекс использовалась

8. Сложность управления

Требуется больше мониторинга, поддержки и оптимизации неиспользуемых индексов.

Пример: Сравнение производительности

@Service
public class UserInsertBenchmark {
    @Autowired
    private UserRepository userRepository;
    
    // Со сценарием 0 индексов
    // INSERT 10000 пользователей: 500 мс
    
    // Со сценарием 5 индексов
    // idx_email, idx_name, idx_created_at, idx_status, idx_country
    // INSERT 10000 пользователей: 2000 мс (4x медленнее)
    
    // Со сценарием 15 индексов
    // INSERT 10000 пользователей: 6000 мс (12x медленнее)
}

Рекомендации по количеству индексов

Оптимальное количество индексов:

• На таблицу: 3-5 индексов
• На крупную таблицу (миллионы строк): 5-8 индексов
• На таблицу с интенсивными INSERT/UPDATE: 1-3 индекса

Какие индексы создавать:

-- PRIMARY KEY (идентификатор) — всегда
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    ...
);

-- FOREIGN KEY (для JOIN) — важен
CREATE INDEX idx_user_id ON orders(user_id);

-- Столбцы в WHERE — часто используемые
CREATE INDEX idx_email ON users(email);

-- Столбцы в ORDER BY, GROUP BY
CREATE INDEX idx_created_at ON orders(created_at);

-- Составные индексы для часто выполняемых запросов
CREATE INDEX idx_user_status_date ON users(status, created_at);

-- НЕ создавать индексы на:
-- - Столбцы с малым количеством уникальных значений (gender, status)
-- - Редко используемые столбцы
-- - Столбцы, часто обновляемые

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

PostgreSQL:

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- Индексы с idx_scan = 0 не используются

MySQL:

SELECT 
    object_schema,
    object_name,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema != 'mysql'
ORDER BY count_read ASC;

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

@Entity
@Table(name = "orders")
public class Order {
    @Id
    private Long id;
    
    // Индекс 1: Часто ищется по user_id (для JOIN)
    @Index(name = "idx_user_id")
    private Long userId;
    
    // Индекс 2: Часто сортируется по дате
    @Index(name = "idx_created_at")
    private LocalDateTime createdAt;
    
    // Индекс 3: Часто фильтруется по статусу
    @Index(name = "idx_status")
    private String status;
    
    // НЕ нужен индекс — часто обновляемый
    private BigDecimal amount;
    
    // НЕ нужен индекс — всего 5 уникальных значений
    private Boolean isPaid;
}

// Хорошо: 3-4 индекса для часто используемых операций
// Плохо: 10+ индексов на всё подряд

Практический алгоритм

1. Начни с минимума индексов (PRIMARY KEY + FOREIGN KEY)
2. Профилируй приложение (смотри медленные запросы)
3. Добавляй индексы только для реально медленных запросов
4. Измеряй влияние (время SELECT vs. время INSERT/UPDATE)
5. Регулярно удаляй неиспользуемые индексы
6. Мониторь статистику использования индексов

Вывод

Индексы — это мощный инструмент для ускорения SELECT запросов, но большое их количество приводит к замедлению операций модификации данных (INSERT, UPDATE, DELETE) и потреблению лишних ресурсов. Оптимальный подход:

• Создавай индексы на основе анализа, а не "просто так"
• Мониторь использование индексов
• Удаляй неиспользуемые индексы
• Баланс между SELECT и DML операциями
• Составные индексы часто лучше, чем множество простых

Помни: лучше иметь 3-5 хороших индексов, чем 15 плохих.