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

В моей работе с MySQL и PostgreSQL я использую комбинацию инструментов для проактивного поиска и анализа медленных запросов.

Для MySQL:

1. Включение лога медленных запросов (Slow Query Log): Это базовый метод. Лог записывает все запросы, время выполнения которых превышает порог long_query_time.

   -- Проверяем текущие настройки
   SHOW VARIABLES LIKE 'slow_query_log%';
   SHOW VARIABLES LIKE 'long_query_time';
   
   -- Включаем лог (в конфиге my.cnf это надежнее)
   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
   SET GLOBAL long_query_time = 2.0; -- Запросы медленнее 2 секунд
   SET GLOBAL log_queries_not_using_indexes = 'ON'; -- Также ловить запросы без индексов

   Для анализа лога удобно использовать утилиту mysqldumpslow:

   mysqldumpslow -s t /var/log/mysql/slow.log | head -20

2. Использование Performance Schema (MySQL 5.6+): Это более продвинутый и менее затратный способ, чем файловый лог.

   -- Находим самые тяжелые запросы
   SELECT query_sample_text,
          ROUND(avg_timer_wait/1e12, 3) as avg_ms,
          COUNT_STAR as exec_count
   FROM performance_schema.events_statements_summary_by_digest
   WHERE schema_name = 'your_database_name'
   ORDER BY avg_timer_wait DESC
   LIMIT 10;

3. EXPLAIN и EXPLAIN ANALYZE (MySQL 8.0.18+): EXPLAIN показывает план выполнения, EXPLAIN ANALYZE — выполняет запрос и показывает фактические затраты.

   EXPLAIN ANALYZE
   SELECT u.name, COUNT(o.id) 
   FROM users u
   JOIN orders o ON u.id = o.user_id 
   WHERE u.created_at > '2023-01-01'
   GROUP BY u.id;

   Ключевые метрики в выводе: actual time, loops, доступ к индексам (Index Scan vs Seq Scan).

Для PostgreSQL:

1. Расширение pg_stat_statements:

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   
   -- Топ-10 запросов по общему времени
   SELECT query, calls, total_exec_time, mean_exec_time
   FROM pg_stat_statements
   ORDER BY total_exec_time DESC
   LIMIT 10;

2. Автовакуум и анализ статистики: Медленные запросы могут быть следствием неактуальной статистики или «раздутых» таблиц.

   -- Проверка последнего анализа и автовакуума
   SELECT schemaname, relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
   FROM pg_stat_user_tables;

Общий подход: После выявления кандидата через логи или pg_stat_statements, я детально разбираю его план с помощью EXPLAIN, смотрю на отсутствующие индексы, выборку больших объемов данных (ROWS) и дорогие операции (SORT, HASH JOIN).