Как найти медленные SQL-запросы сайта через MySQL Slow Query Log

Чтобы найти медленные SQL-запросы сайта, нужно связать задержку страницы с измерениями базы данных. MySQL Slow Query Log помогает получить конкретные запросы и параметры их выполнения. Но включённый журнал ещё не означает, что в нём окажется вся причина торможения: часть запросов может не пройти фильтры, а десятки коротких обращений иногда задерживают страницу сильнее одного длинного.

Ниже — порядок работы для MySQL 8.4: ограничить сбор, проверить действующие настройки, сгруппировать запросы, выбрать приоритет и подтвердить улучшение. Для другой версии MySQL, MariaDB или управляемой базы сначала сверяют доступные параметры с документацией платформы. Числа, пути и названия таблиц в примерах учебные.

1. Определите, что именно должен показать журнал

В документации Slow Query Log описаны условия попадания SQL в журнал: длительность, число просмотренных строк и дополнительные настройки отбора. Это источник кандидатов для исследования, а не автоматический список ошибок. Долгий отчёт, запускаемый ночью, и такой же запрос внутри оформления заказа имеют разное значение для бизнеса.

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

Время SQL не равно времени страницы. До обращения к базе приложение может ждать свободное соединение, после него — обрабатывать данные или обращаться к внешнему API. Поэтому сохраняйте измерение полного HTTP-запроса отдельно. Если найденный SQL занимает малую часть задержки, его оптимизация полезна, но не объясняет весь симптом.

2. Задайте границы сбора и ответственного

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

Выберите интервал, в котором проблема действительно проявляется. Для ручного воспроизведения это может быть короткое согласованное окно; для вечернего пика нужны измерения во время этого пика. Сбор случайной ночной нагрузки не следует выдавать за характеристику рабочего дня. Одновременно отметьте импорты, резервное копирование и другие заметные фоновые задачи.

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

Установите закрытое место хранения и список исполнителей. Текст SQL может включать адреса, сообщения, идентификаторы и другие данные приложения. Для обсуждения используйте обезличенный фрагмент, сохраняя структуру запроса и значимые для плана свойства параметров. Не помещайте исходный журнал в общедоступную папку сайта или случайный онлайн-анализатор.

3. Снимите текущие настройки до изменения

Следующая команда читает значения и ничего не переключает. Её выполняют на подтверждённом экземпляре MySQL с подходящими правами:

SHOW GLOBAL VARIABLES
WHERE Variable_name IN (
  'slow_query_log', 'slow_query_log_file', 'log_output',
  'long_query_time', 'min_examined_row_limit',
  'log_queries_not_using_indexes',
  'log_throttle_queries_not_using_indexes'
);

Сохраните результат вместе со временем. Он нужен и для объяснения текущего поведения, и для возврата прежнего режима. Состояние slow_query_log = ON ещё не доказывает запись SQL в нужный файл: необходимо проверить назначение вывода, фильтры и фактическое появление записей.

По правилам выбора назначения журналов возможны FILE, TABLE или их сочетание; NONE отключает вывод и имеет приоритет. Для файлового разбора должен быть выбран FILE. При табличном выводе данные находятся в mysql.slow_log, поэтому поиск только файла даст неверный вывод.

Параметр log_output относится также к general query log. Не заменяйте его значение шаблонной командой, не проверив влияние на существующий сбор. На управляемом хостинге вместо прямого доступа к файлу может использоваться штатная выгрузка провайдера. Получите именно журнал нужного экземпляра за нужное время, а не произвольный файл с похожим названием.

4. Включите сбор и проверьте настройки соединений

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

SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log = ON;

Это пример изменения состояния сервера, а не обязательные значения для любого сайта. Порог 0,5 секунды выбирают только при соответствии задаче и допустимом объёме данных. Значения до изменения уже должны быть сохранены. Временный эксперимент не следует автоматически закреплять через SET PERSIST или в постоянной конфигурации.

У long_query_time есть глобальное и сессионное значения. По правилам системных переменных MySQL сессионные настройки обычно инициализируются при подключении. Изменение глобального порога не заменяет его в уже существующих соединениях пула. Проверка в новой административной консоли поэтому не подтверждает настройку старого подключения сайта.

SELECT @@GLOBAL.long_query_time AS global_threshold,
       @@SESSION.long_query_time AS session_threshold,
       @@SESSION.min_examined_row_limit AS session_row_limit;

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

5. Выберите порог и учтите остальные фильтры

В MySQL 8.4 значение long_query_time по умолчанию составляет 10 секунд. Описание серверных переменных допускает дробные секунды и предупреждает о быстром росте журнала при низком пороге. Десятисекундный порог может пропустить запрос, который уже заметно задерживает обычную страницу.

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

Проверьте min_examined_row_limit: слишком высокий минимум способен исключить интересующую запись. Отдельно учитывайте log_queries_not_using_indexes, который добавляет запросы без индексного поиска, в том числе короткие. Отсутствие индексного доступа не является самостоятельным доказательством проблемы: для маленькой таблицы полный просмотр может быть разумным.

При включённой регистрации запросов без индексов учитывайте и ограничитель log_throttle_queries_not_using_indexes. Если записи подавляются, количество строк журнала не равно числу выполнений. Укажите все действующие фильтры рядом с результатами анализа, чтобы другая команда могла правильно интерпретировать выборку и повторить измерение.

6. Прочитайте запись целиком и привяжите её ко времени

В файловом журнале полезны Query_time, Lock_time, Rows_sent и Rows_examined. Они показывают время выполнения, время получения блокировок, число отправленных клиенту строк и число строк, просмотренных серверным слоем. Последний показатель не описывает всю внутреннюю работу движка хранения.

# Query_time: 1.250000  Lock_time: 0.000010
# Rows_sent: 20  Rows_examined: 180000

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

MySQL записывает завершённый оператор после выполнения и освобождения блокировок; порядок записей может отличаться от порядка запуска. Время получения начальных блокировок не входит в указанное время выполнения. Поэтому не восстанавливайте последовательность пользовательских действий только по расположению строк в файле.

Сопоставьте часовые пояса, сервер и соединение с данными приложения. Если один пользовательский запрос выполняет несколько SQL параллельно, простое сложение их длительностей не даст время HTTP-ответа. Для причинной связи нужна трассировка сценария или другое наблюдение, показывающее, какое обращение действительно задержало результат.

7. Сгруппируйте повторяющиеся запросы

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

Официальная утилита mysqldumpslow обобщает файловый slow log и по умолчанию заменяет числовые и строковые значения условными обозначениями. Эти команды читают заранее подготовленный закрытый файл выбранного интервала:

mysqldumpslow -l -s t -t 20 /srv/diagnostics/mysql-slow-window.log
mysqldumpslow -l -s c -t 20 /srv/diagnostics/mysql-slow-window.log

В первом случае сортировка идёт по времени группы, во втором — по числу повторов. Параметр -t 20 ограничивает вывод двадцатью группами, но не означает чтение только двадцати исходных записей. Параметр -l отключает вычитание времени блокировок утилитой. По умолчанию без выбранного режима она сортирует по среднему времени, что отвечает на другой вопрос.

Группировка не отменяет проверку параметров. Один и тот же шаблон может быстро работать для маленькой категории и медленно для категории с сотнями тысяч товаров. Сохраняйте несколько представительных обезличенных примеров. Не делайте вывод о всех выполнениях только по одному удачно выбранному идентификатору.

8. Сравните суммарное время и влияние на сценарий

Рассмотрим вымышленный пятиминутный интервал, где порог и остальные условия позволили зарегистрировать все перечисленные выполнения. Для простоты внутри каждой группы длительности одинаковы. Таблица показывает, почему максимальная длительность одной записи не должна быть единственным критерием:

Группа запросов	Выполнений	Время одного	Суммарное время
Получение свойств товаров	400	0,18 с	72 с
Поиск с фильтрами	8	3 с	24 с
Внутренний отчёт	2	7 с	14 с

Расчёты простые: 400 × 0,18 = 72; 8 × 3 = 24; 2 × 7 = 14. Первая группа даёт наибольшую сумму, хотя каждый её запрос заметно короче остальных. При пороге 0,5 секунды эта группа не попала бы по времени в slow log. Для такого сравнения нужен подходящий короткий сбор с меньшим порогом либо независимая статистика выполнений.

Сумма 110 секунд не означает, что сайт не работал 110 секунд, и не является измерением чистого процессорного времени. Запросы могут выполняться одновременно и ждать разные ресурсы. Используйте сумму как показатель накопленного времени группы, а затем смотрите её связь с пользовательской задержкой.

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

9. Исследуйте план выбранного запроса

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

EXPLAIN FORMAT=TREE
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

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

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

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

10. Разберите ситуацию, когда журнал пуст или неполон

Пустой slow log не подтверждает, что база работает быстро. Сначала проверьте экземпляр сервера, выбранный вывод, период сбора, успешность записи, сессионный порог и фильтры. Затем убедитесь, что проблемный запрос завершился и исследуемый маршрут действительно обращался к этой базе. Кешированная страница или другой сервер чтения меняют картину.

Для большого числа коротких запросов полезны сводные таблицы операторов Performance Schema. Они агрегируют события, в том числе по схеме и нормализованному шаблону. Это отдельный источник измерений: его полнота зависит от включённого сбора и доступной ёмкости статистики.

При наличии нужных прав и настроенного sys можно начать с представления statement_analysis. Оно показывает нормализованные запросы, число выполнений и суммарную, среднюю и максимальную задержку. В стандартном представлении результаты по умолчанию упорядочены по суммарной задержке.

Сопоставляйте один и тот же интервал. Накопленная статистика за несколько дней не сравнима с пятиминутным slow log без дополнительного расчёта. Для счётчиков используют снимки до и после, учитывают перезапуски и сбросы; средние значения и процентили нельзя механически вычитать. Не очищайте общую статистику только ради собственного отчёта: она может требоваться другим специалистам.

Если доступа к журналу и Performance Schema нет, подготовьте для хостинга конкретный запрос: экземпляр, база, время с часовым поясом, маршрут и длительность. Попросите ограниченную выборку или агрегированный отчёт. Установленный в CMS модуль сам по себе не даёт административных прав к серверному журналу.

11. Подтвердите улучшение на сопоставимых условиях

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

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

Проверьте корректность результата: состав товаров, сортировку, доступы, суммы и сохранение данных. Более быстрый запрос, который теряет строки или нарушает ограничения пользователя, не решает задачу. Если вводится кеширование, отдельно задайте допустимый срок актуальности и проверьте обновление после изменений.

В учебном примере уменьшение времени первой группы с 0,18 до 0,05 секунды при тех же четырёхстах выполнениях снизило бы её сумму с 72 до 20 секунд. Разница составила бы 52 секунды накопленного времени SQL. Это ещё не обещание ускорить каждую страницу на конкретный процент: итоговый эффект измеряют отдельно по полному сценарию сайта.

12. Завершите диагностику и сохраните полезный результат

По окончании окна верните прежние настройки или согласованный постоянный режим. Если журнал уже использовался до расследования, не отключайте его автоматически. Учитывайте и соединения пула, которые получили временный порог: возврат только глобального значения не исправляет их сессионные настройки.

Для дальнейшего хранения применяйте предусмотренную процедуру обслуживания журналов MySQL. Ротация должна учитывать переоткрытие файла сервером и права на новый файл. Не удаляйте произвольные файлы из каталога базы и не переносите правила очистки slow log на бинарные журналы: у них другое назначение.

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

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

Дополните внутреннюю диагностику внешним контролем HTTP-доступности через SiteGuard. Анализ SQL и проверку данных выполняет ваша команда; внешняя проверка помогает наблюдать, доступен ли сайт после изменений.