MySQL: индексы, EXPLAIN, прфилирование.
2017-02-17 10:35:09    150    0    0
murphy

Взято: 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. Получаем следующий результат:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEprangePRIMARY, poster_idPRIMARY3NULL25Using where; Using temporary
2SIMPLEtALLNULLNULLNULLNULL404508Using 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 с кодом запроса, то получим:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEprangePRIMARY, poster_idPRIMARY3NULL25Using where; Using temporary
2SIMPLEtrefuser_iduser_id3p.poster_id343 


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

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

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;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEphpbb_postsindexforum_id, post_approvedtopic_id3NULL225526Using 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:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEphpbb_postsrangeforum_id, post_approved, posts5posts56NULL25Using 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;
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCardinality
phpbb_posts0PRIMARY1post_id225520
phpbb_posts1forum_id1forum_id82
phpbb_posts1topic_id1topic_id1927
phpbb_posts1poster_ip1poster_ip56380
phpbb_posts1post_approved1post_approved2
phpbb_posts1tid_post_time1topic_id1927
phpbb_posts1tid_post_time2post_time225520
phpbb_posts1post_username1post_username497
phpbb_posts1posts51poster_ip1436
phpbb_posts1posts52forum_id17347
phpbb_posts1posts71poster_ip1436
phpbb_posts1posts72forum_id17347
phpbb_posts1posts73post_id225520


Видно, что индекс 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_IDDurationQuery
10.35822850SELECT 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
22.76522025create index profiler1 on phpbb_thanks (topic_id)
30.41008000SELECT 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
40.00004475SELECT 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


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

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

StatusDuration
starting0.000022
checking query cache for query0.000071
Opening tables0.000011
System lock0.000005
Table lock0.000029
init0.000022
optimizing0.000009
statistics0.000010
preparing0.000010
Creating tmp table0.000030
executing0.000003
Copying to tmp table0.357379
Sorting result0.000024
Sending data0.000028
end0.000003
removing tmp table0.000206
end0.000005
query end0.000004
freeing items0.000338
storing result in query cache0.000015
logging slow query0.000003
cleaning up0.000005


Второй:

StatusDuration
starting0.000018
checking query cache for query0.000071
Opening tables0.000171
System lock0.000005
Table lock0.000027
init0.000021
optimizing0.000009
statistics0.000024
preparing0.000011
executing0.000007
Sorting result0.000003
Sending data0.408909
end0.000008
query end0.000003
freeing items0.000776
storing result in query cache0.000009
logging slow query0.000004
cleaning up0.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

Pre: Обои календаря iDeco

Next: Джон Сильвер - квартирмейстер

150
Sign in to leave a comment.
No Leanote account? Sign up now.
0 comments
Table of content