Расширенный анализ эффективности индексов в 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-запроса в древовидном виде.