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

Лог медленных запросов 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. Получаем следующий результат:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p range PRIMARY, poster_id PRIMARY 3 NULL 25 Using where; Using temporary
2 SIMPLE t ALL NULL NULL NULL NULL 404508 Using where; Using join buffer


В столбце 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 с кодом запроса, то получим:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p range PRIMARY, poster_id PRIMARY 3 NULL 25 Using where; Using temporary
2 SIMPLE t ref user_id user_id 3 p.poster_id 343  


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

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

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;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE phpbb_posts index forum_id, post_approved topic_id 3 NULL 225526 Using where; Using temporary; Using filesort


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

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:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE phpbb_posts range forum_id, post_approved, posts5 posts5 6 NULL 25 Using where; Using temporary; Using filesort


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

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

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

mysql> show indexes from phpbb_posts;
Table Non_unique Key_name Seq_in_index Column_name Cardinality
phpbb_posts 0 PRIMARY 1 post_id 225520
phpbb_posts 1 forum_id 1 forum_id 82
phpbb_posts 1 topic_id 1 topic_id 1927
phpbb_posts 1 poster_ip 1 poster_ip 56380
phpbb_posts 1 post_approved 1 post_approved 2
phpbb_posts 1 tid_post_time 1 topic_id 1927
phpbb_posts 1 tid_post_time 2 post_time 225520
phpbb_posts 1 post_username 1 post_username 497
phpbb_posts 1 posts5 1 poster_ip 1436
phpbb_posts 1 posts5 2 forum_id 17347
phpbb_posts 1 posts7 1 poster_ip 1436
phpbb_posts 1 posts7 2 forum_id 17347
phpbb_posts 1 posts7 3 post_id 225520


Видно, что индекс 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);

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

Query_ID Duration Query
1 0.35822850 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
2 2.76522025 create index profiler1 on phpbb_thanks (topic_id)
3 0.41008000 SELECT topic_id, COUNT(*) AS topic_thanks FROM phpbb_thanks WHERE topic_id IN ('7804', '1389', '3157', 22, 79, 36, 2969, 2711, 65, 3170, 1094, 164, 3137, 1460, 32, 747, 1809, 610, 4284, 1409, 3813, 1741, 2703, 3295, 3277, 1108, 1396, 2883, 203, 2300, 1855, 2800, 1963) GROUP BY topic_id
4 0.00004475 SELECT topic_id, COUNT(*) AS topic_thanks FROM phpbb_thanks WHERE topic_id IN ('7804', '1389', '3157', 22, 79, 36, 2969, 2711, 65, 3170, 1094, 164, 3137, 1460, 32, 747, 1809, 610, 4284, 1409, 3813, 1741, 2703, 3295, 3277, 1108, 1396, 2883, 203, 2300, 1855, 2800, 1963) GROUP BY topic_id


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

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

Status Duration
starting 0.000022
checking query cache for query 0.000071
Opening tables 0.000011
System lock 0.000005
Table lock 0.000029
init 0.000022
optimizing 0.000009
statistics 0.000010
preparing 0.000010
Creating tmp table 0.000030
executing 0.000003
Copying to tmp table 0.357379
Sorting result 0.000024
Sending data 0.000028
end 0.000003
removing tmp table 0.000206
end 0.000005
query end 0.000004
freeing items 0.000338
storing result in query cache 0.000015
logging slow query 0.000003
cleaning up 0.000005


Второй:

Status Duration
starting 0.000018
checking query cache for query 0.000071
Opening tables 0.000171
System lock 0.000005
Table lock 0.000027
init 0.000021
optimizing 0.000009
statistics 0.000024
preparing 0.000011
executing 0.000007
Sorting result 0.000003
Sending data 0.408909
end 0.000008
query end 0.000003
freeing items 0.000776
storing result in query cache 0.000009
logging slow query 0.000004
cleaning up 0.000006


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

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

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

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

Наверх

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