База данных чаще всего становится слабым местом высоконагруженного веб-приложения. Так что даже лишние, неиспользуемые индексы способны замедлить работу 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, которые не успели выполниться за время сбора статистики.