При работе с высоконагруженными проектами часто приходиться заниматься оптимизацией запросов на низком уровне, т.е. в тех случаях, когда просто EXPLAIN уже не спасает. Мы хотим знать сколько времени уходит на чтение с диска, блокировку таблиц, выгрузку в память; также очень полезной информацией может быть объем использованной памяти или загрузка процессора на каждом этапе выполнения запроса.

Я решил написать этот пост, так как осознал: люди с большим опытом работы с MySQL порой не знают, что  база имеет встроенную возможность профилирования запросов. Появилась она еще с MySQL 5.0.37.

В MySQL есть 2 sql–команды для работы с профайлером:

SHOW PROFILE

SHOW PROFILES

Они показывают профили запросов сделанных за время текущей сессии.

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

mysql> set profiling=1;

Включили? Теперь выполните несколько произвольных запросов … и посмотрим что нам скажет профайлер:

mysql> show profiles;

Мы увидим нумерованный список запросов. По умолчанию профайлер хранит последние 15 запросов(можно настроить set profiling_history_size = 50). Чтобы посмотреть подробную информацию о каком либо из них:

mysql> show profile for query 5; // 5 - номер в списке

И мы должны увидеть табличку с двумя колонками:

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| checking query cache for query | 0.000054 |
| Opening tables                 | 0.000025 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000036 |
| init                           | 0.000014 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000015 |
| preparing                      | 0.000011 |
| executing                      | 0.000005 |
| Sending data                   | 0.000117 |
| end                            | 0.000008 |
| query end                      | 0.000003 |
| freeing items                  | 0.000031 |
| storing result in query cache  | 0.000006 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

Duration – это длительность выполнения операции(как подсказывает кэп), а Status – сама операция. Полный список возможных операций можно посмотреть тут.

Информацию из профайлера можно получить не только командами SHOW PROFILE, но и обычным SELECT запросом к таблице PROFILING из INFORMATION_SCHEMA, т.е.:

mysql> select STATE, FORMAT(DURATION, 6) as DURATION 
              from INFORMATION_SCHEMA.PROFILING where QUERY_ID = 5;

Успешного вам анализа и оптимизации!