Диагностика запросов к базе данных

Журнал долгих запросов

pt-query-digest — это специальный инструмент, который анализирует долгие запросы вашей базы данных и превращает его в понятный отчет. Он сам находит самые медленные запросы и выстраивает их в список, чтобы вы сразу видели, какие именно команды тормозят работу сайта и что нужно исправлять в первую очередь.

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

Как использовать

Вызвать утилиту можно такой командой подключившись по SSH:

[a123456@server]$ pt-query-digest --output slowlog --processlist h=localhost,D=%db_name%,u=%username%,p=%password%

Обратите внимание на поля, в которые нужно ввести данные базы данных:

D= название базы данных
u= пользователь
p= пароль

Где найти данные от нужной вам базы мы разобрали в этой статье.

Учтите, что программа работает в реальном времени. Чтобы она увидела проблему, её нужно спровоцировать: запустите утилиту и начните активно пользоваться «медленными» страницами сайта. Так нужные запросы сразу попадут в отчет.

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

[a123456@server]$ tmux
[a123456@server]$ pt-query-digest queries.log --report-format=query_report

Итак, разобравшись с вызовом утилиты, перейдем к результатам её работы!

Структура отчета

Записи формируются примерно в таком виде:

# Time: 2025-07-25T16:41:12
# User@Host: a123456_db_name[a123456_db_name] @ localhost []
# Query_time: 0.100872  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
use a123456_db_name;
SELECT * FROM users WHERE email = 'test@example.com';

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

Логически каждую секцию можно разделить на две части: метаданные запроса и сам SQL-запрос.

Метаданные выделяются символом #:

# Time: 2025-07-25T16:41:12
# User@Host: a123456_db_name[a123456_db_name] @ localhost []
# Query_time: 0.100872  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
use a123456_db_name;

Здесь стоит обратить внимание поля Query_time и Rows_examined в третьей и четвертой строке.

Параметр Query_time указывает общее время выполнение запроса в секундах, а Rows_examined — количество строк, обработанных сервером для формирования результата. Чем больше значения, тем больше ресурсов сервер тратит на их выполнение.

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

Во второй секции программа выводит сам SQL-запрос. Он обычно начинается c операторов языка SQL, таких как SELECT, ALTER TABLE и так далее.

SELECT * FROM users WHERE email = 'test@example.com';

Разобрать базовый синтаксис и логику SQL-запросов можете, например, в этом курсе или любом другом.

Диагностика запросов. EXPLAIN

Когда SQL-запрос выполняется, оптимизатор движка составляет примерный план его выполнения. Проанализировать этот план позволяет оператор EXPLAIN.

Для его использования необходимо подключится к базе данных MySQL. Как это сделать мы разобрали в этой статье.

Далее нужно скопировать интересующий вас запрос из отчета в командную строку и добавить в самое начало оператор EXPLAIN

mysql> EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Оптимизатор выведет подробную таблицу с планом выполнения запроса.

Почитать о том, как работать с таблицей можете в статье.

В этой же статье мы разберем один из самых частых кейсов — полное сканирование базы данных (full table scan). Полное сканирование его можете заметить по:

столбцу type со значением ALL — MySQL прочел всю таблицу в поиске одного значения;
столбцу key со значением NULL — движок не нашел ни одного индекса;
столбцу rows с количеством прочитанных строк — чем больше, тем хуже.

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

Индекс создается оператором CREATE INDEX:

mysql> CREATE INDEX idx_email ON users(email);

Синтаксис команды расшифровывается так:

mysql> CREATE INDEX название_индекса ON таблица(колонка);

Теперь база не читает всю таблицу. Она открывает индекс (который отсортирован по алфавиту), мгновенно находит там test@example.com и по «ссылке» сразу прыгает к нужной строке в таблице. Вместо миллиона проверок она делает всего 5–10 быстрых прыжков.

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

Перед внесением любых изменений в структуру базы данных (особенно в продуктивной среде) настоятельно рекомендуется создавать резервную копию.

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

Подробнее про составление индексов можете почитать в статьях:

Теги