Профилирование и оптимизация Sql запросов

Профилирование запросов

Профилирование запросов применяется для оценки производительности приложения. Для управления профилированием используйте profiling .

SET profiling = 1;

SHOW PROFILES отображает список самых последних запросов, отправленных на сервер. Размер списка контролируется profiling_history_size переменной сеанса, которая имеет значение по умолчанию 15. Максимальное значение - 100. Установка значения 0 имеет практический эффект отключения профилирования.

По умолчанию SHOW PROFILE отображает Query и Duration. SHOW PROFILE имеет необязательные type значения могут быть указаны для отображения определенных дополнительных типов информации:

• ALL отображает всю информацию
• BLOCK IO отображает количество операций ввода и вывода блока
• CONTEXT SWITCHES отображает количество произвольных и непроизвольных переключений контекста
• CPU отображает время использования ЦП пользователя и системы
• IPC отображает количество отправленных и полученных сообщений
• MEMORY в настоящее время не реализовано
• PAGE FAULTS отображает количество основных и второстепенных ошибок страницы
• SOURCE отображает имена функций из исходного кода вместе с именем и номером строки файла, в котором функция встречается
• SWAPS отображает количество свопов

SHOW PROFILE ALL FOR QUERY 2; # Отображает всю информацию о втором запросе

Оптимизация запросов

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

• Использовать подготовленные запросы.
• Избегать запросов в цикле.
• Избегать SELECT *
• Проверить запрос в EXPLAIN. Если поиск идет часто по одному полю и у него нет индекса, то можно сделать индекс по этому полю.
• Использовать LIMIT 1, когда нужна 1 строка.
• В каждой таблице нужно поле id, которое будет PRIMARY KEY, AUTO_INCREMENT, а так же иметь тип INT.
• Использовать NOT NULL, где это возможно.
• При пакетной выборке в больших объемов данных не стоит использовать OFFSET . Подробнее о проблеме и решении можно почитать на habr.com

Explain, План выполнения

mysql> explain select cp.id, cp.name from products as p inner join product_types pt on p.type_id=pt.id;
+----+-------------+-------+--------+----------------------+----------------------+---------+-----------------------------+-------+--------------------------+
| id | select_type | table | type   | possible_keys        | key                  | key_len | ref                         | rows  | Extra                    |
+----+-------------+-------+--------+----------------------+----------------------+---------+-----------------------------+-------+--------------------------+
|  1 | SIMPLE      | pt    | index  | IDX_147342311664BC41 | IDX_147342311664BC41 | 5       | NULL                        | 52272 | Using where; Using index |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY              | PRIMARY              | 4       | p.type_id                   |     1 | NULL                     |
+----+-------------+-------+--------+----------------------+----------------------+---------+-----------------------------+-------+--------------------------+

Количество строк EXPLAIN - это количество таблиц, участвующих в запросе, а значение столбцов результата:

• id : уникальный идентификатор запроса
• select_type : тип запроса
• table : таблица запросов, которая может быть таблицей / представлением в базе данных или подзапросом из FROM
• type : метод поиска данных
• possible_keys : возможные индексы
• key : окончательное решение использовать
• key_len : количество байтов, используемых индексом запроса. Обычно чем меньше, тем лучше
• ref : столбец или константа запроса
• rows : количество сканируемых строк, оценка. Обычно чем меньше, тем лучше.
• Extra : дополнительная информация

select_type :

• SIMPLE — Простой запрос SELECT без подзапросов или UNION’ов
• PRIMARY – данный SELECT – самый внешний запрос в JOIN’е
• DERIVED – данный SELECT является частью подзапроса внутри FROM
• SUBQUERY – первый SELECT в подзапросе
• DEPENDENT SUBQUERY – подзапрос, который зависит от внешнего запроса
• UNCACHABLE SUBQUERY – не кешируемый подзапрос (существуют определенные условия для того, чтобы запрос кешировался)
• UNION – второй или последующий SELECT в UNION’е
• DEPENDENT UNION – второй или последующий SELECT в UNION’е, зависимый от внешнего запроса
• UNION RESULT – результат UNION’а

type :

• null : может быть сделано без доступа к индексам и таблицам, пример:SELECT 1;
• const : в таблице есть только одно совпадение, которое считывается как константа при декомпозиции плана запроса. Система является частным случаем константного типа.
• eq_ref : используйте PRIMARY KEY или UNIQUE KEY для связанных запросов.
• ref : запрос с использованием индекса, который позволяет дублировать