Расширенный анализ эффективности индексов в MySQL

Для детального анализа особенностей использования индексов в MySQL удобно использовать утилиту mk-index-usage, входящую в пакет Maatkit (написан на языке Perl). По умолчанию утилита выявляет неиспользуемые индексы и предлагает готовые конструкции ALTER для их удаления. Дополнительно утилита может сохранять накопленную статистику по использованию индексов при реальной рабочей нагрузке и сохранять ее в виде SQL-таблиц для последующего анализа. В качестве источника используется лог запросов, включаемых опцией log-slow-queries или general_log. Простейший формат вызова выглядит как: mk-index-usage slow.log --host localhost При этом утилита построит детальный отчет на основе выполнения EXPLAIN-операции для каждого фигурирующего в логе запроса. Так как это достаточно ресурсоемкая операция, рекомендуется запустить на отдельной машине тестовый MySQL-сервер, перенести туда дамп анализируемой базы и лог медленных запросов, после чего выполнять анализ не на первичном сервере, а на его копии: mk-index-usage -h 127.0.0.1 -P 9999 -p XXXX slow_query.log \ --save-results-database h=127.0.0.1,P=12345,u=msandbox,p=msandbox,D=index_usage \ --create-save-results-database В данном случае параметр "--save-results-database" определяет в какую базу сохранять результаты анализа. После выполнения указанной команды в базе index_usage появятся следующие таблицы: mysql> show tables; | index_alternatives | | index_usage | | indexes | | queries | | tables | Несколько примеров просмотра статистики. Посмотрим какие запросы используют время от времени используют разные индексы и как выбранный индекс коррелирует с временем выполнения запроса: SELECT iu.query_id, CONCAT_WS('.', iu.db, iu.tbl, iu.idx) AS idx, variations, iu.cnt, iu.cnt / total_cnt * 100 AS pct FROM index_usage AS iu INNER JOIN ( SELECT query_id, db, tbl, SUM(cnt) AS total_cnt, COUNT(*) AS variations FROM index_usage GROUP BY query_id, db, tbl HAVING COUNT(*) > 1 ) AS qv USING(query_id, db, tbl); | query_id | | variations | cnt | pct | | 7675136724153707161 | mpb_wordpress.wp_posts.post_status | 2 | 18 | 97.5871 | | 7675136724153707161 mpb_wordpress.wp_posts.type_status_date | 2 | 728 | 2.4129 | .... В первой строке указано, что запрос с идентификатором 7675136724153707161 имеет два варианта использования индексов. Первый вариант использует индекс mpb_wordpress.wp_posts.post_status в 97% случаев, второй - индекс mpb_wordpress.wp_posts.type_status_date в 2% случаев. Посмотрим что из себя представляет запрос 7675136724153707161: mysql> select * from queries where query_id = 7675136724153707161\G query_id: 7675136724153707161 fingerprint: select * from wp_comments where comment_post_id = ? and comment_type not regexp ? and comment_approved = ? sample: SELECT * FROM wp_comments WHERE comment_post_ID = 2257 AND comment_type NOT REGEXP '^(trackback|pingback)$' AND comment_approved = '1' 1 row in set (0.00 sec) Теперь посмотрим для каких индексов имеется большое число альтернатив, какие индексы вызываются вместо других и при каких запросах: mysql> SELECT CONCAT_WS('.', db, tbl, idx) AS idx, GROUP_CONCAT(alt_idx) AS alternatives, GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt FROM index_alternatives GROUP BY db, tbl, idx HAVING COUNT(*) > 1 limit 2; | idx | alternatives | queries | cnt | | mpb_forum.f.PRIMARY | fud26_forum_i_c,fud26_forum_i_c,fud26_forum_i_lpi | 6095451542512376951,11680437198542055892 | 20 | | mpb_forum.fud26_msg.fud26_msg_i_ta | PRIMARY,fud26_msg_i_a | 5971938384822841613 | 2 | Как видно первичные ключи преобладают над некоторыми индексами. Некоторые другие утилиты из пакета Maatkit: mk-archiver - архивирование строк из таблицы MySQL в другую таблицу или в файл; mk-deadlock-logger - выявление и сохранение информации о взаимных блокировках; mk-duplicate-key-checker - поиск дублирующихся индексов и внешних ключей; mk-find - аналог утилиты find для выполнения поиска по таблицам и выполнения действий над результатами; mk-heartbeat - мониторинг задержки при выполнении репликации; mk-kill - удаляет запросы, соответствующие определенным критериям; mk-loadavg - следит за нагрузкой на базу и выполняет указанные действия при обнаружении перегрузки; mk-log-player - позволяет повторно выполнить запросы из лога; mk-parallel-dump/mk-parallel-restore - создание и восстановление дампа таблиц в параллельном режиме; mk-purge-logs - чистит бинарный лог в соответствии с заданными правилами; mk-query-advisor - анализирует запросы и выявляет возможные проблемы; mk-query-digest - парсит лог и анализирует, фильтрует и преобразует запросы в логе, формируя в итоге полезный суммарный отчет; mk-query-profiler - выполняет SQL-запросы и выводит статистику или измеряет активность других процессов; mk-table-checksum - генерация контрольных сумм с целью проверки целостности реплицированных данных; mk-table-sync - эффективная синхронизация содержимого нескольких таблиц; mk-upgrade - запускает запрос одновременно на нескольких серверах и проверяет идентичность ответов; mk-variable-advisor - анализирует переменные MySQL и выявляет возможные проблемы; mk-visual-explain - выводит результат выполнения EXPLAIN-запроса в древовидном виде.

Источник статьи