Для детального анализа особенностей использования индексов в 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.../
Обсуждается: http://www.opennet.dev/tips/info/2491.shtml