Недавно у меня был заказчик, которому нужно было быстро уменьшить размер своей базы данных на диске без большого количества грязной схемы и перекодировки приложений. Они не хотели отбрасывать какие-либо фактические данные, и их использование индекса было довольно высоким, поэтому мы решили искать неиспользуемые индексы, которые можно было бы удалить.
Сбор данных
Собрать статистику использования индексов в Percona Server (и других) довольно просто, используя патч User Statistics . Включив ‘userstat_running’, мы начинаем получать информацию в таблице INFORMATION_SCHEMA.INDEX_STATISTICS. Этот сбор данных добавляет некоторые накладные расходы к вашему работающему серверу, но важно оставить его включенным на долгое время, чтобы получить хороший набор данных, который представляет как можно большую часть вашей рабочей нагрузки.
Если вы пропускаете сбор статистики индекса во время выполнения некоторых случайных запросов, вы рискуете отбросить индексы, которые (используются редко), но все еще важны для здоровья вашей системы. Это может повлиять или не повлиять на вас, но я настоятельно рекомендую вам вручную просмотреть список неиспользуемых индексов, сгенерированных выше, прежде чем просто отбросить их.
В зависимости от вашей чувствительности к производственной нагрузке, вы можете захотеть запустить это несколько дней или просто попробовать разные короткие окна во время нормального пика производства. В любом случае вы можете захотеть сравнить или повторить этот анализ индекса, поэтому давайте настроим отдельную схему для этого. Важно, чтобы этот анализ индекса выполнялся на сервере с загруженным полным набором производственных данных, но он мог быть ведущим или просто ведомым (просто будьте осторожны, чтобы не нарушить репликацию!).
mysql> create schema index_analysis;
Если наши index_statistics собираются на одном и том же сервере, то мы можем просто получить их снимок в нашу схему с помощью одной команды:
mysql> create table index_analysis.used_indexes select * from information_schema.index_statistics;
Если статистика поступает с какого-либо другого сервера, вам может потребоваться выгрузить и загрузить копию этой таблицы в вашу рабочую схему index_analysis.
Слияние статистики с нескольких серверов
В случае этого клиента у них был мастер и несколько рабов, принимающих трафик чтения. Рабочая нагрузка индекса на этих двух наборах серверов была разной, и я хотел убедиться, что рассмотрел статистику индекса из обоих этих источников. Убедитесь, что вы включили всю релевантную статистику индекса по всем аспектам вашего приложения, в противном случае — мусор на входе, мусор и вы рискуете отбросить необходимые индексы.
Чтобы выполнить объединение нескольких наборов результатов, я собрал статистику как из главного, так и из ведомого устройств и загрузил их в свою схему в виде отдельных таблиц. Затем я просто создал представление UNION DISTINCT для этих двух таблиц:
mysql> create view used_indexes as
(select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats )
UNION DISTINCT
(select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from slave_index_stats)
ORDER BY TABLE_SCHEMA, TABLE_NAME;
Теперь я могу запросить «all_known_index_usage» и увидеть объединение обоих этих наборов данных. Это, конечно, можно распространить на все наборы данных, которые вы хотите.
Интерпретация данных
Итак, это все хорошо, но как нам тогда легко определить список индексов, которые не используются? Хорошо, для этого нам нужно вернуться к INFORMATION_SCHEMA, чтобы получить список ВСЕХ индексов в моей системе (или, по крайней мере, схемы, в которых я хочу рассмотреть удаление индексов). Давайте продолжим использовать представления, чтобы они динамически обновлялись по мере изменения схемы:
mysql> create view all_indexes as select t.table_schema as TABLE_SCHEMA, t.table_name as TABLE_NAME, i.index_name as INDEX_NAME, i.NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ',') as COLUMN_NAMES from information_schema.tables t join information_schema.statistics i using (table_schema, table_name) where t.table_schema like 'sakila%' group by t.table_schema, t.table_name, i.index_name;
Теперь я могу запросить это представление, чтобы увидеть мои индексы:
mysql> select * from all_indexes limit 1 \G *************************** 1. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: actor INDEX_NAME: idx_actor_last_name NON_UNIQUE: 1 COLUMN_CNT: 1 COLUMN_NAMES: last_name 1 row in set (0.03 sec)
Теперь мне нужен способ найти набор индексов в all_indexes, но не в used_indexes. Эти индексы (если наша исходная статистика индекса хорошая) являются кандидатами на удаление:
create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index_name as index_name from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where used_indexes.INDEX_NAME is NULL and all_indexes.INDEX_NAME != 'PRIMARY' and all_indexes.NON_UNIQUE = 1;
Обратите внимание, что мы также хотим избежать отбрасывания индексов PRIMARY и UNIQUE, так как они, как правило, навязывают важные ограничения данных приложения, поэтому мы добавили некоторые дополнительные критерии в конец нашего SELECT.
Теперь я могу выбрать мои неиспользуемые (неиспользуемые) индексы из этого представления:
mysql> select * from droppable_indexes; +--------------+---------------+-----------------------------+ | table_schema | table_name | index_name | +--------------+---------------+-----------------------------+ | sakila | actor | idx_actor_last_name | | sakila | address | idx_fk_city_id | | sakila | city | idx_fk_country_id | | sakila | customer | idx_fk_address_id | | sakila | customer | idx_fk_store_id | | sakila | customer | idx_last_name | | sakila | film | idx_fk_language_id | | sakila | film | idx_fk_original_language_id | | sakila | film | idx_title | | sakila | film_actor | idx_fk_film_id | | sakila | film_category | fk_film_category_category | | sakila | film_text | idx_title_description | | sakila | inventory | idx_fk_film_id | | sakila | inventory | idx_store_id_film_id | | sakila | payment | fk_payment_rental | | sakila | payment | idx_fk_customer_id | | sakila | payment | idx_fk_staff_id | | sakila | rental | idx_fk_customer_id | | sakila | rental | idx_fk_inventory_id | | sakila | rental | idx_fk_staff_id | | sakila | staff | idx_fk_address_id | | sakila | staff | idx_fk_store_id | | sakila | store | idx_fk_address_id | +--------------+---------------+-----------------------------+ 23 rows in set (0.02 sec)
Отсюда я могу использовать некоторый умный SQL для генерации точных операторов ALTER TABLE, чтобы отбросить эти индексы, упражнение, оставленное читателю. 
Оценка размера этих индексов
Но что, если мы хотим посмотреть, стоит ли это делать в первую очередь? Действительно ли эти индексы представляют собой достаточно значительный объем дискового пространства, чтобы оно того стоило?
Нам нужна дополнительная информация, чтобы ответить на этот вопрос, но, к счастью, в Percona Server она есть в таблице INFORMATION_SCHEMA.INNODB_INDEX_STATS и столбце index_total_pages. Страница в Innodb (обычно) 16 КБ, поэтому некоторые простые математические вычисления должны помочь нам узнать, сколько дискового пространства использует индекс.
Давайте обновим наше представление all_indexes, чтобы включить эту информацию:
mysql> drop view if exists all_indexes; mysql> create view all_indexes as select t.table_schema as TABLE_SCHEMA, t.table_name as TABLE_NAME, i.index_name as INDEX_NAME, i.NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ',') as COLUMN_NAMES, s.index_total_pages as index_total_pages, (s.index_total_pages * 16384 ) as index_total_size from information_schema.tables t join information_schema.statistics i using (table_schema, table_name) join information_schema.innodb_index_stats s using (table_schema, table_name, index_name) where t.table_schema like 'sakila%' group by t.table_schema, t.table_name, i.index_name;
Теперь мы можем увидеть информацию о размере индекса в представлении all_indexes:
mysql> select * from all_indexes\G ... *************************** 33. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: rental INDEX_NAME: rental_date NON_UNIQUE: 0 COLUMN_CNT: 3 COLUMN_NAMES: rental_date,inventory_id,customer_id index_total_pages: 27 index_total_size: 442368 ...
Теперь нам просто нужно обновить наше представление droppable_indexes, чтобы использовать эту информацию:
mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index_name as index_name, ROUND(all_indexes.index_total_size / ( 1024 * 1024 ), 2) as index_size_mb from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where used_indexes.INDEX_NAME is NULL and all_indexes.INDEX_NAME != 'PRIMARY' and all_indexes.NON_UNIQUE = 1 order by index_size_mb desc;
Теперь мы можем легко увидеть, насколько велик каждый индекс, если мы его отбросим (не большой в этом случае с тестовыми данными):
mysql> select * from droppable_indexes; +--------------+---------------+-----------------------------+---------------+ | table_schema | table_name | index_name | index_size_mb | +--------------+---------------+-----------------------------+---------------+ | sakila | payment | fk_payment_rental | 0.27 | | sakila | rental | idx_fk_customer_id | 0.27 | | sakila | rental | idx_fk_inventory_id | 0.27 | | sakila | rental | idx_fk_staff_id | 0.19 | | sakila | payment | idx_fk_staff_id | 0.17 | | sakila | payment | idx_fk_customer_id | 0.17 | | sakila | inventory | idx_store_id_film_id | 0.11 | | sakila | inventory | idx_fk_film_id | 0.08 | | sakila | film_actor | idx_fk_film_id | 0.08 | | sakila | film | idx_title | 0.05 | | sakila | film | idx_fk_original_language_id | 0.02 | | sakila | city | idx_fk_country_id | 0.02 | | sakila | film_category | fk_film_category_category | 0.02 | | sakila | customer | idx_last_name | 0.02 | | sakila | store | idx_fk_address_id | 0.02 | | sakila | actor | idx_actor_last_name | 0.02 | | sakila | customer | idx_fk_address_id | 0.02 | | sakila | staff | idx_fk_address_id | 0.02 | | sakila | film | idx_fk_language_id | 0.02 | | sakila | address | idx_fk_city_id | 0.02 | | sakila | customer | idx_fk_store_id | 0.02 | | sakila | staff | idx_fk_store_id | 0.02 | +--------------+---------------+-----------------------------+---------------+ 22 rows in set (0.02 sec)
Восстановление пространства файловой системы
Теперь проницательные эксперты innodb поймут, что это не конец истории, когда дело доходит до восстановления дискового пространства. Возможно, вы удалили индексы, но табличные пространства на диске остались прежнего размера. Если вы используете innodb_file_per_table, вы можете перестроить табличное пространство для вашей таблицы, просто выполнив:
mysql> alter table mytable ENGINE=Innodb;
Тем не менее, это блоки и на большой стол может занять довольно много времени. Все обычные хитрости и советы по длительному изменению схемы блокировки, не влияющие на вашу производственную среду, применимы здесь и выходят за рамки этого сообщения в блоге.
Удачной охоты за этими неиспользованными индексами!