Анализ, лог запросов  Mysql, оптимизация, статистика sql запросов базы данных Mysql Slow Query Log Optimize,   Mysql 8,  5.7, 5.6, 5.5

Для анализа производительности базы данных необходимо включить в файле my.cnf ведение лога медленных запросов mysql.

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

После тестирования сайта можно проанализировать лог файл медленных запросов и выявить наиболее часто повторяющиеся запросы.

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

Лог медленных запросов - надежный способ фиксации медленных запросов, который позволяет администратору определить какие запросы в первую очередь нуждаются в оптимизации. Начиная с MySQL 5.6 появилась возможность использовать для этой цели Performance Schema, которая имеет более низкие накладные расходы. Однако, лог медленных запросов имеет одно важное преимущество - данные не исчезают (т.к. записываются на диск в отличии от таблиц Performance Schema, которые хранятся в памяти и не используют постоянное дисковое хранилище). В недавно вышедшей MySQL 8.0.14 лог медленных запросов стал более удобным инструментом за счет добавления дополнительных параметров выполнения запроса.

По умолчанию дополнительная информация не записывается в лог, чтобы включить эту опцию используйте переменную log_slow_extra:

SET PERSIST используется для того, чтобы изменения конфигурации сохранились при перезагрузке сервера MySQL. Если вы хотите сначала попробовать новую возможность (а потом уже решать оставить ли её включенной по умолчанию), используйте SET GLOBAL.

Это всё что требуется для настройки. Теперь можно выполнить "медленный" запрос и посмотреть данные, записанные в лог. Простейший способ получить медленный запрос - выполнить DO SLEEP(...), где вместо троеточия указать количество секунд, на которое будет произведена задержка. Однако, это не очень интересный для анализа запрос. Другой путь - уменьшить long_query_time настолько, чтобы запрос попал в лог. Если установить значение long_query_time равным нулю, в лог будут записываться все запросы:

Обратите внимание: Включение лога медленных запросов несет дополнительные накладные расходы. Не рекомендуется использовать его непрерывно с long_query_time = 0 для всех подключений. Это может привести к проблемам с производительностью.

Поэтому в примере выше указан SET SESSION, чтобы изменение long_query_time повлияло только на текущую сессию.

Давайте выполним следующий запрос: