Определение лишних индексов в MySQL

База данных чаще всего становится слабым местом высоконагруженного веб-приложения. Так что даже лишние, неиспользуемые индексы способны замедлить работу MySQL.

Самый простой способ избавления от таких индексов — утилита check-unused-keys. Она собирает информацию о неиспользуемых таблицах и индексах в MySQL при помощи другой утилиты User Statistics. Патч добавляет таблицы INFORMATION_SCHEMA (включая нужную нам INDEX_STATISTICS со статистикой использования индексов), несколько команд и переменную userstat.

Установка

Для работы утилиты требуется патч User Statistics, скачать можно по ссылке, а лучше использовать Percona Server для MySQL, который из коробки поддерживает user_stats.

Когда все готово, нужно задействовать UserStats, которые по умолчанию выключены:

mysql> SET GLOBAL userstat_running = 1;

# Установка глобальной переменной MySQL

Теперь UserStats будет собирать все статистику об использовании индексов, нагрузка на CPU минимальная.

После этого можно установить утилиту check-unused-keys:

wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/check-unused-keys/check-unused-keys
chmod +x check-unused-keys

# Скачать в директорию, дать разрешение на выполнение

Использование

Когда UserStats насобирал достаточное количество статистики, можно использовать утилиту:

./check-unused-keys --help
Options:
   -d, --databases=<dbname>  Список баз данных, через запятую
   -h, --help                Показать это сообщение
   -H, --hostname=<hostname> Требуемый хост MySQL
   --[no]create-alter        Вывести операторы ALTER для каждой таблицы
   --ignore-databases        Список игнорируемых баз данных, через запятую
   --ignore-indexes          Список игнорируемых индексов, через запятую
                                 db_name.tbl_name.index_name
   --ignore-tables           Список игнорируемых таблиц, через запятую
                                 db_name.tbl_name
   --[no]ignore-primary      Включить или игнорировать PRIMARY KEY
   --[no]ignore-unique       Включить или игнорировать индексы UNIQUE
   --options-file            Использовать файл опций
   --[no]print-unused-tables 
                             Выводить или нет список неиспользуемых таблиц
                                 (индексы этих таблиц никогда не использовались)
   -p, --password=<password> Пароль пользователя MySQL
   -i, --port=<portnum>      Порт MySQL
   -s, --socket=<sockfile>   Использовать указанный MySQL unix-сокет 
   -t, --tables=<tables>     Список таблиц для оценки, через запятую
                                 db_name.tbl_name
   --[no]summary             Отобразить краткую информацию
   -u, --username=<username> Пользователь для подключения к MySQL
   -v, --verbose             Повысить детальность вывода
   -V, --version             Вывод версии программы и выход

# Вывод параметров check-unused-keys

Для запуска утилиты достаточно выполнить:

./check-unused-keys -u mysql -p password --print-unused-tables --create-alter

# Выполнение программы с указанием пользователя и пароля, выводом неиспользуемых таблиц и оператором Alter

Команда выводит неиспользуемые таблицы (с индексами), для каждой из которых будет создан “ALTER TABLE” для удаления неиспользуемых индексов. Если нужно, анализ можно провести только по требуемым БД и таблицам.

Самое главное

Утилита поможет избавиться от неиспользуемых индексов. Будьте внимательны, под удаление попадают все индексы, использованные 0 раз. Так что под раздачу попадут таблицы из cron-jobs, которые не успели выполниться за время сбора статистики.