Как определить, каких индексов не хватает в базе данных, и на какие поля их нужно добавить?

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

Как искать проблемные запросы

• Включите логирование медленных запросов: в PostgreSQL параметр log_min_duration_statement, в MySQL — slow_query_log.
• Используйте pg_stat_statements в PostgreSQL или performance_schema в MySQL, чтобы найти запросы с высоким средним временем и большим числом вызовов.
• Смотрите планы выполнения: EXPLAIN ANALYZE в PostgreSQL, EXPLAIN в MySQL.

На что смотреть в плане

Признаки нехватки индекса: Seq Scan или Full Table Scan на большой таблице, высокая стоимость, сортировка во внешней памяти, вложенные циклы с большим числом строк. Индекс обычно нужен на поля, которые участвуют в условиях соединения и фильтрации.

-- PostgreSQL: смотрим план
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC;

-- Если видим Seq Scan по orders,
-- создаём составной индекс
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

Правила выбора полей

1. Сначала поля из WHERE с высокой селективностью.
2. Затем поля из JOIN.
3. Потом поля из ORDER BY и GROUP BY.
4. Порядок колонок в составном индексе важен: сначала более селективные и часто используемые в равенствах.

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

Итог: недостающие индексы выявляют по медленным запросам и планам выполнения, добавляя их на поля фильтрации, соединения и сортировки, но только там, где выигрыш от чтения превышает затраты на запись.