SNR Blogs

Взято: http://debian-help.ru/web-servers/mysql-indexes-explain-profiling/

Лог медленных запросов 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).

Также можно добавить директиву 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);

Коннектимся к mysql из командной строки и выполняем запрос добавив перед ним EXPLAIN. Получаем следующий результат:

В столбце possible_keys видно какие есть индексы. В данном случае для таблицы t (phpbb_thanks) индексы не используются. Как следствие для выполнения запроса приходится анализировать всю таблицу в 404508 строк, что не эффективно (сравните с 25 строками для таблицы p).

Более подробную информацию по вариантам содержимого столбцов вывода EXPLAIN можно найти на странице http://phpclub.ru/mysql/doc/explain.html.

Теперь четко видно, что для таблицы phpbb_thanks требуется построить индекс по столбцу user_id. При этом для таблицы phpbb_posts создано два индекса, а используется только один из них. Вероятно, второй индекс можно удалить.

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

mysql> CREATE INDEX user_id ON phpbb_thanks (user_id);

Результат:

Query OK, 404576 rows affected (2.88 sec)
Records: 404576  Duplicates: 0  Warnings: 0

В команде используются следующие параметры:

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

Стоит учесть, что если запускать создание индекса на боевой машине, то запросам UPDATE, DELETE и INSERT придется подождать, что замедлит обслуживание пользовательских задач.

Если после создания индекса выполнить EXPLAIN с кодом запроса, то получим:

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

Создание индексов посмотрели, теперь обратимся к их удалению:

mysql> DROP INDEX poster_id ON phpbb_posts;

Результат:

Query OK, 220908 rows affected (3 min 58.02 sec)
Records: 220908  Duplicates: 0  Warnings: 0

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

С Using temporary можно бороться с помощью увеличения значения параметра sort_buffer_size в настройках MySQL. Что касается Using filesort, который встретится ниже, то в этом случае стоит включить столбец, по которому производится сортировка, в составной индекс после столбцов из WHERE.

Составные индексы

При выполнении запроса может использоваться только один индекс для одной таблицы. Однако можно создавать индексы для нескольких столбцов сразу - составные индексы.

Посмотрим на примере.

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

mysql> explain SELECT topic_id, COUNT(post_id) AS num_posts FROM phpbb_posts WHERE poster_id = 9544 AND post_postcount = 1 AND (post_approved = 1) AND forum_id NOT IN (42, 91) GROUP BY topic_id ORDER BY num_posts DESC LIMIT 1;

Для примера приведем разные варианты создания составных индексов по нескольким столбцам:

create index posts1 on phpbb_posts (post_id, poster_id);
create index posts2 on phpbb_posts (forum_id,post_approved);
create index posts3 on phpbb_posts (topic_id, forum_id, poster_id);
create index posts4 on phpbb_posts (post_id, poster_id);
create index posts5 on phpbb_posts (poster_id, forum_id);
create index posts6 on phpbb_posts (poster_id, forum_id, post_approved, post_postcount);
create index posts7 on phpbb_posts (poster_id, forum_id, post_id);

Из них оптимизатор использовал только posts2 и posts5, среди которых posts5 оказался лучшим. То есть для построения индекса стоит использовать столбцы из параметра WHERE, но не все. В данном случае запрос из движка phpbb, где все посты публикуются автоматически (post_approved = 1) и во всех столбцах post_postcount стоит значение 1. Как следствие, нет смысла строить индекс с учетом этих столбцов, так как данные в них не меняются.

Результат EXPLAIN после простановки индекса posts5:

Как мы видем type изменился c index на range, а rows с 225526 на 25, что не может не радовать. Однако опечалить может, что ничего не изменилось в Extra.

По поводу type: range лучше, чем index, так как в этом случае идет просмотр только некоторого диапазона в индексе, а в случае type index происходит просмотр всего индекса. Самый плохой вариант - ALL. В этом случае идет просмотр всей таблицы без индекса. Самый лучший - const, когда результатом запроса будет уникальное значение из индекса, что возможно только в простых конкретных запросах.

Понять, какой индекс будет использован можно с помощью информации о доступных индексах для таблицы:

mysql> show indexes from phpbb_posts;

Видно, что индекс posts7 не выигрывает у posts5 за счет включения данных по столбцу post_id, так как в этом случае приходится в любом случае просматривать все строки: cardinality равно 225520. Cardinality - это количество уникальных значений в индексе.

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

SELECT something FROM some_table FORCE INDEX (posts7) WHERE content=123

Профайлер (profiler)

Узнать время выполнения запросов и, как следствие, стали ли они работать быстрее после создания индексов можно с помощью профайлера (profiler), который встроен в MySQL начиная с пятой версии. Профайлер включается только на время рабочей сессии в консольном клиенте. Включить можно таким образом:

# mysql -p
mysql> set profiling=1;

Посмотреть профили:

mysql> show profiles;

Посмотреть детальную информацию по профилю:

mysql> show profile for query 3;

Изменить длину истории профилей (по умолчанию, в истории 15 профилей):

mysql> set profiling_history_size=30;

Посмотрим возможности профиля на еще одном реальном примере.

При включенном профайлере выполняем запрос:

SELECT topic_id, COUNT(*) AS topic_thanks FROM phpbb_thanks WHERE topic_id IN ('7805', '1389', '3257', 29, 79, 36, 2949, 2711, 65, 3178, 1094, 164, 3137, 1461, 33, 747, 1809, 611, 4284, 1409, 3814, 1742, 2705, 3292, 3271, 1109, 1296, 2863, 206, 2301, 1855, 2820, 1962) GROUP BY topic_id;

Создаем индекс:

CREATE INDEX profiler1 ON phpbb_thanks (topic_id);

Выполняем аналогичный запрос с другими значениями, а затем его же еще раз. Профайлер выдасть следующую информацию:

Интересную картину видим. Создание индекса замедлило выполнение запроса. Кстати, повторное выполнение запроса дает результат категорически быстрее за счет кэширования.

Теперь посмотрим на подробные профили запросов. Первый:

Второй:

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

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

Полезные ссылки

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
http://dev.mysql.com/doc/refman/5.5/en/group-by-optimization.html
http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html

http://www.slideshare.net/phpcodemonkey/mysql-explain-explained
http://www.slideshare.net/manikandakumar/mysql-query-and-index-tuning