MySQL: индексы, EXPLAIN, прфилирование.

Опубликовано: 18.06.2017

Лог медленных запросов MySQL

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

Предварительно создаем файл лога и устанавливаем владельца и группу:

touch /var/log/mysqld-slow-query.log chown mysql:mysql /var/log/mysqld-slow-query.log

Затем в файл /etc/mysql/my.cnf (в Debian 7, в других ОС файл может быть расположен прямо в директории /etc) следует добавить строки:

long_query_time = 3 log-slow-queries = /var/log/mysqld-slow-query.log log-slow-admin-statements

В директиве long_query_time указывается время в секундах при превышении которого запрос будет записан в лог. log-slow-admin-statements - указывает, что в лог также следует записывать медленные административные запросы (OPTIMIZE TABLE, ALTER TABLE, ANALYZE TABLE).

09 - Погружение в СУБД. Оптимизация выполнения запросов

Также можно добавить директиву log-queries-not-using-indexes , благодаря использованию которой в лог будут писаться все запросы совершаемые без использования индексов. Однако стоит учесть, что в этом случае лог может расти достаточно активно.

Провести анализ лога можно с помощью скрипта, доступного по адресу http://hackmysql.com/mysqlsla , который позволяет, кроме прочего, построить топ часто повторяющихся медленных запросов и предоставить статистику по ним. Также можно воспользоваться стандартной утилитой mysqldumpslow.

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

EXPLAIN

К примеру имеем следующий реальный запрос:

SELECT DISTINCT t.user_id, t.post_id, t.poster_id FROM (phpbb_thanks t, phpbb_posts p) WHERE p.poster_id = t.user_id AND (p.post_id =235449 OR p.post_id =235498 OR p.post_id =235677 OR p.post_id =235862 OR p.post_id =235888 OR p.post_id =236303 OR p.post_id =236314 OR p.post_id =236321 OR p.post_id =236326 OR p.post_id =236340 OR p.post_id =236529 OR p.post_id =236577 OR p.post_id =236584 OR p.post_id =236586 OR p.post_id =236614 OR p.post_id =236618 OR p.post_id =236637 OR p.post_id =236705 OR p.post_id =236753 OR p.post_id =236794 OR p.post_id =236811 OR p.post_id =236816 OR p.post_id =236818 OR p.post_id =236825 OR p.post_id =236835);