Для детального анализа особенностей использования индексов в MySQL удобно использовать утилиту mk-index-usage, входящую в пакет [[http://code.google.com/p/maatkit/ 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-запроса в древовидном виде.
URL: http://www.mysqlperformanceblog.com/2010/11/11/advanced-inde.../
Обсуждается: https://www.opennet.ru/tips/info/2491.shtml
