Какие плюсы и минусы индексов в БД?

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

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

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

Без индекса:

SELECT * FROM users WHERE email = 'user@example.com';
// Full table scan — проверяет все строки (O(n))

С индексом:

CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
// B-tree поиск — O(log n) — намного быстрее

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

1. Быстрый поиск (Speeding Up Queries)

• Находит данные за O(log n) вместо O(n)
• Чтение становится на порядки быстрее

// БЕЗ индекса: 1000ms для 1M записей
// С индексом: 5ms для 1M записей

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

• Foreign key индексы скорят соединения таблиц
• Планировщик выбирает оптимальный путь выполнения

3. Поддержка UNIQUE constraint

CREATE UNIQUE INDEX idx_users_email ON users(email);
// Быстро проверяет уникальность при вставке

4. Поддержка ORDER BY и GROUP BY

CREATE INDEX idx_orders_date ON orders(created_at);
SELECT * FROM orders ORDER BY created_at;
// Может использовать индекс вместо сортировки

5. Поддержка диапазонных запросов

SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
// Индекс помогает быстро найти диапазон

Минусы индексов

1. Медленные операции записи (Slower Writes)

• Индекс нужно обновлять при INSERT, UPDATE, DELETE
• Может замедлить запись на 20-50%

// БЕЗ индексов: INSERT 1M записей за 10 сек
// С 5 индексами: INSERT 1M записей за 50+ сек

2. Потребление памяти (Memory Overhead)

• Индекс копирует данные столбца
• На большом столбце (JSON, TEXT) это значительно

CREATE INDEX idx_users_name ON users(name);
// Копирует все значения name в B-tree структуру
// Если name в среднем 50 байт — 50 байт/строка дополнительно

3. Стоимость обслуживания

• PostgreSQL нужно периодически REINDEX старые индексы
• MySQL может фрагментировать индекс

REINDEX INDEX idx_users_email; -- PostgreSQL
OPTIMIZE TABLE users; -- MySQL

4. Выбор индекса оптимизатором (Query Planner Issues)

• Иногда БД выбирает неправильный индекс
• Нужно использовать EXPLAIN для отладки

EXPLAIN SELECT * FROM users WHERE email = '...';
// Покажет, какой индекс использовался

5. Усложнение запросов с OR (Index Usage)

-- Может не использовать индекс
SELECT * FROM users WHERE email = '...' OR name = '...';

-- Лучше с UNION
SELECT * FROM users WHERE email = '...'
UNION
SELECT * FROM users WHERE name = '...';

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

B-tree (по умолчанию):

• Хороши для точного поиска и диапазонов
• Работают на =, >, <, BETWEEN

CREATE INDEX idx_users_email ON users(email);

Hash:

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

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

Composite (составной):

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

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- Хорош для: WHERE user_id = ? AND created_at > ?
-- Плох для: WHERE created_at > ? (user_id не в условии)

Partial (частичный):

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

Когда использовать индексы

DO:

• На столбцах, которые часто идут в WHERE
• На foreign keys (для JOIN операций)
• На столбцах в ORDER BY
• На уникальных полях (email, username)
• На столбцах в GROUP BY

DON'T:

• На столбцах, которые редко читаются
• На столбцах с малым количеством уникальных значений (пол: M/F)
• На BOOLEAN столбцах (пересечение слишком велико)
• На очень больших TEXT столбцах (используй FULL TEXT INDEX)
• На столбцах, которые часто обновляются

Инструменты анализа

// EXPLAIN ANALYZE показывает реальный план
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

// Результат может показать:
// - Seq Scan (полный скан) — может нужен индекс
// - Index Scan — индекс использован
// - Rows — сколько строк обработано
// - Actual rows — сколько вернулось

Практические рекомендации

Стратегия:

1. Напиши приложение БЕЗ индексов
2. Профилируй медленные запросы (SLOW QUERY LOG)
3. Добавь индексы только на проблемные запросы
4. Проверь EXPLAIN ANALYZE
5. Измеряй impact на запись

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

• На таблице < 1000 строк индексы редко помогают
• На таблице > 100K строк почти всегда нужны индексы
• На таблице 1-100K зависит от шаблонов запросов

Вывод: индексы — это мощный инструмент оптимизации, но требуют осторожности. Всегда профилируй, измеряй и принимай решения на основе данных, не на предположениях.