Диагностика медленных запросов MySQL: пошаговое руководство

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

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

Обычно я начинаю с трех вопросов:

Какой запрос вредит пользователям, а не просто выглядит подозрительно?
Тратится ли время на чтение строк, сортировку, ожидание блокировок или ожидание приложения?
Могу ли я доказать исправление с помощью EXPLAIN, времени выполнения и свежих данных из журнала медленных запросов?

Начните с журнала медленных запросов

Журнал медленных запросов MySQL записывает операторы, которые превышают настроенный порог. Согласно руководству MySQL, журнал по умолчанию отключен, long_query_time по умолчанию равен 10 секундам, и оператор обычно должен выполняться не менее этого времени и проверять не менее min_examined_row_limit строк, прежде чем будет записан. Если включен log_queries_not_using_indexes, MySQL также может записывать операторы, которые не используют индексы для поиска строк. Эта опция полезна во время диагностики, но может создавать много шума на загруженных системах.

Практическая начальная конфигурация выглядит так:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_output = FILE

На многих производственных системах 1 секунда является разумным первым приближением. Для API, чувствительного к задержкам, вы можете временно снизить его до 0,5 или 0,2 секунды. Делайте это с планом и контролем дискового пространства. База данных с высоким трафиком может записать удивительно много данных в журнал медленных запросов, как только порог будет снижен.

Вы можете проверить активные настройки из сеанса MySQL:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

Для временного расследования вы можете включить журнал без редактирования файла конфигурации:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Помните, что изменения SET GLOBAL могут не сохраниться после перезапуска, если вы также не зафиксируете их через обычный процесс настройки конфигурации. В MySQL 8 некоторые команды используют SET PERSIST, но я все еще предпочитаю фиксировать предполагаемую настройку в управлении конфигурацией, чтобы следующий оператор мог ее увидеть.

Если вы включаете log_queries_not_using_indexes, подумайте также о настройке log_throttle_queries_not_using_indexes, чтобы одна шумная конечная точка не заливала журнал. MySQL поддерживает это ограничение именно потому, что журналирование запросов без индекса может быстро расти.

Группируйте журнал перед чтением отдельных запросов

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

Начните с mysqldumpslow, который поставляется с установками MySQL во многих средах:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Это запрашивает десять лучших шаблонов, отсортированных по времени запроса. Точные флаги различаются в зависимости от версии и платформы, поэтому проверьте mysqldumpslow --help, если ваша команда ведет себя иначе. Полезные сортировки включают общее время, среднее время, время блокировки и количество проверенных строк.

Для производственных расследований pt-query-digest из Percona Toolkit часто лучше, поскольку он предоставляет более богатую группировку и детали в стиле процентилей. Инструмент не является магией; он просто избавляет вас от необходимости выполнять арифметические действия вручную. Важно ранжировать по влиянию. Запрос, который выполняется восемь секунд раз за ночь, может быть менее срочным, чем запрос, который выполняется 120 миллисекунд, но выполняется 600 раз в секунду.

При чтении сгруппированного вывода ищите шаблоны:

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

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

Воспроизведите один запрос безопасно

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

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

Полезная заметка для каждого запроса выглядит так:

Endpoint: GET /customers/123/orders
Query pattern: orders by customer and status, newest first
Observed: 1.8s average, 420k rows examined, 20 rows sent
Table size: 12M rows
Expected result size: one page of orders
Suspicion: missing composite index for customer_id, status, created_at

Эта заметка связывает работу с реальным пользовательским путем, а не со случайным фрагментом SQL.

Используйте `EXPLAIN`, затем читайте его как оператор

Запустите EXPLAIN для медленного запроса:

EXPLAIN
SELECT id, customer_id, status, created_at, total
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Для MySQL 8 EXPLAIN ANALYZE может выполнить запрос и показать фактическую информацию о времени. Используйте его осторожно для дорогих запросов, потому что он действительно выполняет оператор. Для простых запросов SELECT в контролируемой среде это может быть очень полезно.

Столбцы, которые я проверяю в первую очередь: type, possible_keys, key, rows, filtered и Extra.

type сообщает вам шаблон доступа. const, eq_ref, ref и range обычно являются хорошими признаками. index означает, что MySQL сканирует индекс, что все еще может быть слишком большой работой. ALL означает полное сканирование таблицы. Полное сканирование не является автоматически неправильным для крошечной таблицы, но оно подозрительно для горячей таблицы с миллионами строк.

key показывает индекс, выбранный MySQL. Если possible_keys перечисляет многообещающий индекс, но key отличается, оптимизатор может считать, что другой индекс дешевле. Это может произойти из-за плохой селективности, устаревшей статистики или индекса, который не соответствует фильтру и сортировке вместе.

rows — это оценка, а не обещание. Если оценка сильно неверна, запустите ANALYZE TABLE в соответствующее окно обслуживания или проверьте, не искажено ли распределение данных.

Extra часто рассказывает историю. Using filesort означает, что MySQL нужен отдельный шаг сортировки; это не обязательно означает сортировку на диске, но стоит проверить, когда результирующий набор велик. Using temporary часто появляется с группировкой, запросами DISTINCT или сложными сортировками. Using index может быть хорошим, потому что запрос удовлетворяется из индекса без чтения строк таблицы.

Исправляйте индексы с учетом полной формы запроса

Самое распространенное исправление медленного запроса — это не «добавить индекс к столбцу в предложении WHERE». Лучшее правило: создайте индекс, который соответствует тому, как запрос фильтрует, объединяет, сортирует и ограничивает строки.

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

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

Если запрос сортирует по убыванию (сначала новые), MySQL часто может сканировать индекс в обратном порядке. В MySQL 8 вы также можете определить нисходящие индексы, когда это соответствует более крупному шаблону:

CREATE INDEX idx_orders_customer_status_created_desc
ON orders (customer_id, status, created_at DESC);

Порядок столбцов имеет значение. Поместите фильтры равенства первыми, затем столбцы диапазона или сортировки, когда это соответствует запросу. Например, для WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20 порядок customer_id, status, created_at обычно более полезен, чем created_at, customer_id, status.

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

SHOW INDEX FROM orders;

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

Переписывайте запросы, которые блокируют использование индекса

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

Эта версия распространена и болезненна:

SELECT *
FROM orders
WHERE YEAR(created_at) = 2026;

Если created_at индексирован, обертывание его в YEAR() может предотвратить обычный поиск по диапазону. Запишите предикат как диапазон:

SELECT id, customer_id, status, created_at, total
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

Та же идея применима к поиску с ведущим символом подстановки:

WHERE email LIKE '%@example.com'

Обычный B-дерево индекс не может перейти в середину строки. Если поиск по суффиксу важен, вам может понадобиться вычисляемый столбец, отдельное нормализованное поле или поисковая система, предназначенная для этого случая.

Также следите за SELECT *. Это выглядит безвредно во время разработки, но может заставить MySQL читать строки таблицы, когда меньшая проекция могла бы использовать покрывающий индекс. Это также отправляет ненужные данные по сети.

Проверяйте блокировки, когда план запроса выглядит нормально

Запрос может иметь приличный план и все равно быть медленным из-за ожидания. Lock_time в журнале медленных запросов может указать вам в этом направлении, но он не объясняет каждый вид ожидания. Если пользователи сообщают о случайных паузах, проверьте активные сеансы:

SHOW PROCESSLIST;

В MySQL 8 представления Performance Schema и sys schema могут дать более детальную информацию, в зависимости от того, как настроен сервер. Для быстрого просмотра я часто проверяю длительные транзакции и заблокированные операторы перед изменением индексов.

Реальный пример: запрос UPDATE orders SET status = ? WHERE id = ? должен быть быстрым. Если он появляется в журнале медленных запросов с поиском по первичному ключу, проблема может быть в транзакции, которая оставила строку заблокированной, выполняя несвязанную работу. Исправление — не другой индекс. Исправление — сокращение транзакции и вызов медленных внешних вызовов за ее пределами.

Блокировки метаданных могут создать аналогичную ловушку. Миграция, выполняющая ALTER TABLE, может ждать старую транзакцию, в то время как новые запросы накапливаются за ожидающим DDL. Журнал медленных запросов покажет симптомы, но коренная причина — поведение развертывания.

Настраивайте параметры сервера только после работы с запросами

Конфигурация имеет значение, но ее легко переоценить как первое решение. Если запрос сканирует пять миллионов строк, чтобы вернуть десять, увеличение памяти может только уменьшить боль от плохого плана.

Для систем, интенсивно использующих InnoDB, innodb_buffer_pool_size — это первая настройка для проверки. На выделенном сервере MySQL он часто устанавливается на большую долю памяти, но правильное значение зависит от того, что еще работает на хосте, размера набора данных и рабочей нагрузки. Не копируйте процент вслепую из сообщения в блоге.

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

Пулы соединений могут сделать медленные запросы еще хуже. Если одна конечная точка запускает слишком много медленных операторов, пул заполняется, несвязанные запросы ждут соединений, и все приложение кажется сломанным. В этом случае исправление запроса все еще является основной задачей, но ограничения пула и тайм-ауты определяют, насколько gracefully система выходит из строя.

Докажите исправление

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

Не останавливайтесь на одном быстром запуске. Теплый кэш может скрыть проблемы. Попробуйте общие, большие и неудобные случаи:

Клиент с большим количеством заказов.
Клиент без соответствующих заказов.
Диапазон дат, охватывающий загруженный период.
Значение статуса, которое соответствует большинству строк.

Затем следите за журналом медленных запросов после развертывания. Лучший результат — не «запрос выглядел лучше в staging». Лучший результат — это то, что шаблон запроса исчезает из списка главных нарушителей, нагрузка на ЦП или ввод-вывод снижается, а пользовательский путь становится быстрее.

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