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

Вычисление медленных запросов средствами базы данных

Для выявления медленных запросов в базе данных существует несколько встроенных инструментов и методов, которые позволяют анализировать производительность SQL-запросов без привлечения сторонних решений. Вот основные подходы:

1. Логирование медленных запросов (Slow Query Log)

Большинство СУБД поддерживают функцию логгирования запросов, выполнение которых превышает заданный порог времени. Например:

В PostgreSQL включается через параметры конфигурации:

-- В postgresql.conf
log_min_duration_statement = 1000 -- логировать запросы дольше 1 секунды

В MySQL/MariaDB:

-- В my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 -- запросы дольше 2 секунд

2. Использование системных представлений (System Views)

Современные СУБД предоставляют системные представления для мониторинга производительности:

PostgreSQL - pg_stat_statements (требует предварительной настройки):

-- Активация расширения
CREATE EXTENSION pg_stat_statements;

-- Топ-10 самых долгих запросов
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

MySQL - Performance Schema:

-- Запросы с наибольшим временем выполнения
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3. Анализ планов выполнения (EXPLAIN и EXPLAIN ANALYZE)

Ключевой инструмент для диагностики - EXPLAIN команда, которая показывает план выполнения запроса:

-- Базовый анализ плана
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- С фактическим временем выполнения (выполняет запрос)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Ключевые метрики в выводе EXPLAIN:

• Seq Scan vs Index Scan - показывает использование индексов
• Cost - оценка стоимости операции
• Actual Time - реальное время выполнения
• Rows - количество обрабатываемых строк

4. Мониторинг активности в реальном времени

PostgreSQL - pg_stat_activity:

-- Текущие выполняемые запросы
SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;

MySQL - SHOW PROCESSLIST:

-- Все текущие подключения и их запросы
SHOW FULL PROCESSLIST;
-- Или через системную таблицу
SELECT * FROM information_schema.processlist
WHERE TIME > 5 AND COMMAND = 'Query';

5. Использование расширенных инструментов

PostgreSQL - auto_explain:

-- Автоматическое логирование планов для медленных запросов
-- В postgresql.conf:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = true

6. Практические шаги для анализа

1. Сбор статистики - убедитесь, что сбор статистики включен
2. Базовая настройка логов - настройте логирование запросов > 1 секунды
3. Регулярный мониторинг - создайте скрипты для регулярного сбора статистики
4. Анализ паттернов - группируйте похожие запросы для выявления системных проблем
5. Тестирование в изоляции - выполняйте проблемные запросы отдельно для точного измерения

7. Пример комплексного анализа

-- Для PostgreSQL: комплексный отчет о медленных запросах
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows / calls AS avg_rows,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
WHERE mean_exec_time > 100 -- более 100ms
ORDER BY total_exec_time DESC
LIMIT 20;

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