Как найти и проанализировать медленные запросы в базе данных?

Анализ медленных запросов — это итеративный процесс, состоящий из поиска "кандидатов" и их детального изучения.

Общий подход:

1. Включить логирование/мониторинг: Настроить БД на отслеживание запросов, превышающих определенный порог времени.
2. Найти медленные запросы: Изучить логи или статистику, чтобы определить самые ресурсоемкие запросы.
3. Проанализировать план выполнения: Для найденного запроса использовать команду EXPLAIN ANALYZE, чтобы понять, как именно БД его выполняет и где узкое место.

Инструменты для конкретных БД:

PostgreSQL

1. Логирование медленных запросов: В postgresql.conf установите log_min_duration_statement = '200ms'. Все запросы дольше 200 мс будут попадать в лог.

2. Расширение pg_stat_statements: Это мощный инструмент для сбора агрегированной статистики по всем запросам. Идеально для поиска самых "дорогих" запросов в целом.

   -- Необходимо один раз включить расширение
   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   
   -- Посмотреть топ-5 запросов по среднему времени выполнения
   SELECT
       (total_exec_time / 1000) as total_time_ms, -- Общее время в мс
       calls, -- Количество вызовов
       (mean_exec_time / 1000) as mean_time_ms, -- Среднее время в мс
       query -- Текст запроса
   FROM pg_stat_statements
   ORDER BY mean_time_ms DESC
   LIMIT 5;

3. Анализ плана (EXPLAIN ANALYZE): Это главный инструмент для анализа конкретного запроса. Он выполняет запрос и показывает реальный план с фактическим временем и количеством строк.

   EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

   На что смотреть: Seq Scan (полный перебор таблицы) вместо Index Scan, неверные оценки количества строк (rows).

MySQL

1. Slow Query Log: Аналог логгирования в PostgreSQL. Включается в конфигурации или командой:

   -- Включить лог медленных запросов
   SET GLOBAL slow_query_log = 'ON';
   -- Установить порог в 1 секунду
   SET GLOBAL long_query_time = 1;

2. Команда EXPLAIN: Показывает план выполнения запроса. В отличие от EXPLAIN ANALYZE в PostgreSQL, она не выполняет запрос, а только строит предполагаемый план.

Внешние утилиты:

• pgBadger: Генератор отчетов для логов PostgreSQL.
• pt-query-digest: Инструмент из Percona Toolkit для анализа логов MySQL.
• Системы мониторинга: Datadog, New Relic, Prometheus+Grafana часто имеют встроенные инструменты для анализа производительности БД.