Когда индексы в БД неэффективны?

Когда индексы в БД неэффективны: типичные ошибки и оптимизация

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

1. Индексы на столбцах с низкой селективностью

Если столбец содержит много одинаковых значений, индекс становится малополезным.

# ❌ Неэффективно: индекс на is_active (в 99% cases = True)
CREATE INDEX idx_is_active ON users(is_active);

# Запрос всё равно отсканирует большую часть таблицы
SELECT * FROM users WHERE is_active = True;  -- 1M из 1.01M строк

# ✅ Лучше: индекс на более селективный столбец
CREATE INDEX idx_email ON users(email);  -- Уникальный, селективность = 100%
SELECT * FROM users WHERE email = user@example.com;

2. Индексы на часто обновляемых столбцах

Ежедневное обновление индексов требует ресурсов. INSERT/UPDATE/DELETE становятся медленнее.

# ❌ Неэффективно: индекс на column, который меняется часто
CREATE INDEX idx_last_seen ON users(last_seen);

# Каждое обновление требует перестройки индекса
UPDATE users SET last_seen = NOW() WHERE user_id = ?;

# ✅ Лучше: кэшировать last_seen в Redis или обновлять батчем
# Обновляем раз в день вместо каждого запроса
UPDATE users SET last_seen = NOW() WHERE user_id IN (SELECT id FROM temp_active_users);

3. Индексы на маленьких таблицах

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

# ❌ Неэффективно: индекс на маленькой справочной таблице
CREATE INDEX idx_country_code ON countries(code);

# PostgreSQL просто отсканирует все 200 строк (быстро)
SELECT * FROM countries WHERE code = US;

# ✅ Решение: хранить в памяти приложения или кэше
countries_cache = {"US": {...}, "GB": {...}, ...}

4. Индексы на TEXT/BLOB полях без полнотекстовых индексов

Обычные индексы неэффективны для поиска внутри текста.

# ❌ Неэффективно: индекс B-tree на TEXT
CREATE INDEX idx_description ON products(description);

# LIKE с % впереди игнорирует индекс
SELECT * FROM products WHERE description LIKE %купить%;  -- Full scan

# ✅ Правильный подход: полнотекстовый поиск
CREATE INDEX idx_description_fulltext ON products 
USING GIN(to_tsvector(russian, description));

SELECT * FROM products 
WHERE to_tsvector(russian, description) @@ plainto_tsquery(russian, купить);

5. Индексы, которые не используются в WHERE/JOIN/ORDER

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

# ❌ Неэффективно: отдельные индексы
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_created_at ON orders(created_at);

# Запрос использует оба условия, но может выбрать неоптимальный индекс
SELECT * FROM orders WHERE user_id = 123 AND created_at > 2024-01-01;

# ✅ Правильно: составной индекс (composite index)
CREATE INDEX idx_user_created ON orders(user_id, created_at);

# PostgreSQL может использовать индекс целиком
SELECT * FROM orders WHERE user_id = 123 AND created_at > 2024-01-01;

6. Индексы, которые приводят к излишним сортировкам

Иногда планировщик выбирает индекс, но затем всё равно сортирует результаты.

# ❌ Неоптимально
CREATE INDEX idx_user ON orders(user_id);

SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;
-- PostgreSQL: использует индекс user_id, затем сортирует по created_at

# ✅ Лучше: индекс в порядке сортировки
CREATE INDEX idx_user_created_desc ON orders(user_id, created_at DESC);

SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;
-- Индекс уже отсортирован, сортировка пропускается

7. Индексы при использовании функций в WHERE

Функции обычно не использовют индексы.

# ❌ Неэффективно: индекс не используется
CREATE INDEX idx_email ON users(email);

SELECT * FROM users WHERE LOWER(email) = test@example.com;
-- Индекс игнорируется, потому что есть функция

# ✅ Решение 1: функциональный индекс
CREATE INDEX idx_email_lower ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = test@example.com;

# ✅ Решение 2: хранить в нормализованном виде
CREATE INDEX idx_email_normalized ON users(email_normalized);

8. Индексы при IN с большим списком

Большой IN список может быть неэффективен даже с индексом.

# ⚠️ Неэффективно при id_list > 1000 элементов
SELECT * FROM users WHERE user_id IN (1, 2, 3, ..., 5000);

# ✅ Лучше: JOIN с временной таблицей
CREATE TEMP TABLE temp_ids AS
SELECT UNNEST(ARRAY[1, 2, 3, ..., 5000]) AS user_id;

SELECT u.* FROM users u
JOIN temp_ids t ON u.user_id = t.user_id;

# Или batch queries
for batch in chunks(id_list, 100):
    users.extend(db.query(User).filter(User.id.in_(batch)).all())

Как определить неэффективный индекс

# PostgreSQL: найти неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

# 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_WRITE DESC;

Резюме: красные флаги неэффективности

• Низкая селективность — индекс на is_active, status (много повторений)
• Частые обновления — last_seen, last_activity изменяются постоянно
• Маленькие таблицы — справочники < 1000 строк лучше в памяти
• Текстовый поиск — нужны полнотекстовые индексы
• Неиспользуемые индексы — висят в памяти, замедляют INSERT
• Функции в условиях — обычные индексы игнорируются
• Неправильный порядок — составные индексы должны соответствовать ORDER BY

Правило: измеряй, не угадывай. Используй EXPLAIN, смотри idx_scan, проверяй план запроса.