Как найти тяжёлые SQL-запросы в MySQL, MariaDB и PostgreSQL

Чтобы найти запрос, который нагружает базу, смотрят суммарное время выполнения, частоту, число просмотренных строк, блокировки и дисковый ввод-вывод. В MySQL и MariaDB для этого используют slow query log и Performance Schema, в PostgreSQL — pg_stat_statements и журналы.

Нагрузка по таблицам видна в Performance Schema или представлениях sys: они показывают количество чтений и записей, время ожидания и наиболее активные объекты. После этого конкретный SQL проверяют через EXPLAIN и связывают с URL, фоновым заданием или участком PHP-кода.

Сначала связать SQL с пользовательским сценарием

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

Полезный набор показателей:

• суммарное время по шаблону запроса;
• число выполнений;
• среднее и высокие процентили времени;
• число просмотренных и возвращённых строк;
• время ожидания блокировок;
• путь в приложении или стек вызова.

MySQL и MariaDB: slow query log

В MySQL журнал медленных запросов записывает операции, которые выполнялись дольше long_query_time и соответствуют дополнительным условиям. По умолчанию slow query log отключён [1]. В MariaDB назначение механизма то же: находить запросы, превышающие установленный порог [2].

Порог задают на ограниченный период с учётом нагрузки. Значение по умолчанию часто слишком велико для веб-запросов, но постоянное логирование почти всех операций создаёт лишний ввод-вывод и большой объём чувствительных данных. Доступ к журналу ограничивают, а после диагностики возвращают штатную конфигурацию.

Как читать журнал

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

Оценивать записи по одной строке неверно. Нужна таблица запросов, отсортированная по суммарному времени и количеству просмотренных строк. После неё выбирают несколько кандидатов и возвращаются к соответствующему коду.

MySQL Performance Schema

Performance Schema позволяет получать агрегированную статистику без разбора текстового файла. Конкретные представления и доступные поля зависят от версии и конфигурации сервера, поэтому перед запросами сверяются с документацией установленной версии. Метод удобен для постоянно работающего наблюдения, но статистика также имеет срок жизни и может сбрасываться при перезапуске.

Для оценки нагрузки по таблицам полезны представление performance_schema.table_io_waits_summary_by_table и готовое представление sys.schema_table_statistics. Они помогают увидеть, какие таблицы чаще читаются и изменяются, сколько времени занимают операции ввода-вывода и где искать запросы для дальнейшего анализа.

Slow log лучше показывает отдельные медленные выполнения, а агрегированная статистика — часто повторяющиеся шаблоны. На практике эти источники дополняют друг друга.

PostgreSQL: три основных инструмента

log_min_duration_statement

Параметр log_min_duration_statement записывает завершённые запросы, чья длительность не меньше заданного значения. Значение указывается в миллисекундах, если единицы не заданы; -1 отключает механизм [3]. На нагруженной системе осторожно выбирают порог и политику хранения логов.

pg_stat_statements

Расширение pg_stat_statements агрегирует статистику планирования и выполнения по нормализованным запросам. Для его загрузки требуется добавить модуль в shared_preload_libraries, обычно перезапустить сервер, а затем создать расширение в нужной базе [4]. Это изменение планируют заранее, а не выполняют спонтанно в часы пик.

Для приоритизации смотрят количество вызовов, общее и среднее время, строки и операции чтения. После исправления статистику сбрасывают только осознанно: иначе потеряется база для сравнения.

auto_explain

auto_explain может автоматически записывать планы медленных запросов. Это полезно, когда проблема проявляется только с реальными параметрами, но подробный анализ и сбор фактических данных добавляют накладные расходы. Настройки сначала проверяют на тестовой среде и ограничивают нужными запросами [5].

EXPLAIN и EXPLAIN ANALYZE

Обычный EXPLAIN показывает оценочный план без выполнения запроса. В MySQL EXPLAIN ANALYZE выполняет поддерживаемый запрос и добавляет фактические строки, циклы и время по узлам [6]. В PostgreSQL опция ANALYZE тоже реально выполняет запрос [7].

Поэтому на рабочей базе сначала используют обычный план, копию данных или транзакцию с понятным откатом. Для UPDATE, DELETE и тяжёлого SELECT запуск с анализом без оценки риска может изменить данные или создать заметную нагрузку.

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

1С-Битрикс: найти место в PHP-коде

Монитор производительности 1С-Битрикс может вести журнал SQL, отдельно записывать медленные запросы и сохранять стек вызовов [8]. Именно стек связывает SQL с компонентом, обработчиком события или модулем.

Сбор включают на короткий контролируемый период. Полный монитор в документации ограничен одним часом, а режим только для медленных запросов — одной неделей [8]. После воспроизведения данные выгружают и монитор выключают.

Почему добавление индекса не всегда помогает

Индекс полезен, когда соответствует фильтрам, соединениям и сортировке. Но причина может быть другой:

• N+1-запросы внутри цикла;
• выборка ненужных полей и строк;
• функция над индексируемым полем в условии;
• неудачная пагинация с большим смещением;
• долгая блокировка, а не чтение;
• частое выполнение корректного запроса из-за отсутствия кеша;
• устаревшая статистика оптимизатора.

Каждый новый индекс увеличивает объём данных и стоимость записи. После добавления проверяют импорт, обновление каталога и другие операции изменения, а не только ускорившийся SELECT.

Рабочая последовательность

1. Воспроизвести медленный сценарий и зафиксировать исходное время.
2. Собрать SQL и стек на ограниченном интервале.
3. Сгруппировать запросы и отсортировать по суммарному влиянию.
4. Проверить план и место вызова первых кандидатов.
5. Исправить запрос, структуру данных или повторное выполнение.
6. Повторить тот же сценарий и сравнить план, время и нагрузку.
7. Проверить корректность данных и соседние операции.

Результат диагностики — перечень конкретных запросов, причина каждого, изменение в коде или схеме и измерение до/после. Список «рекомендуемых настроек MySQL» без привязки к рабочей нагрузке не заменяет такой отчёт.

Источники

[1] MySQL 8.4 Reference Manual — The Slow Query Log: dev.mysql.com/doc/refman/8.4/en/slow-query-log.html

[2] MariaDB Documentation — Slow Query Log: mariadb.com/docs/server/server-management/server-monitoring-logs/slow-query-log

[3] PostgreSQL Documentation — log_min_duration_statement: postgresql.org/docs/current/runtime-config-logging.html

[4] PostgreSQL Documentation — pg_stat_statements: postgresql.org/docs/current/pgstatstatements.html

[5] PostgreSQL Documentation — auto_explain: postgresql.org/docs/current/auto-explain.html

[6] MySQL 8.4 Reference Manual — EXPLAIN: dev.mysql.com/doc/refman/8.4/en/explain.html

[7] PostgreSQL Documentation — Using EXPLAIN: postgresql.org/docs/current/using-explain.html

[8] 1С-Битрикс — настройки Монитора производительности: dev.1c-bitrix.ru/user_help/settings/perfmon/settings.php