Оптимизация и дефрагментация таблиц MySQL

Ранее я уже писал об удалении неиспользуемых индексов в таблицах. Сегодня мы продолжим рассматривать оптимизацию MySQL баз данных, но в этот раз речь пойдёт о повреждении таблиц и фрагментации данных.

Индексы могут со временем подвергаться фрагментации, в этом случае поиск по диапазону и полное сканирование индекса могут выполняться во много раз медленнее.

Помимо этого, сами данные (строки, страницы и свободное пространство) могут также подвергаться фрагментации, что снижает скорость доступа и понижает общую производительность базы данных.

Для начала определим потенциальных кандидатов на оптимизацию, чем выше отношение data_free_mb к data_length_mb, тем более фрагментированы данные в таблице:

SELECT 
    table_name,
    ROUND(data_length/1024/1024) as data_length_mb,
    ROUND(data_free/1024/1024) as data_free_mb
FROM information_schema.tables
    WHERE round(data_free/1024/1024) > 500
    ORDER BY data_free_mb;

Чтобы оптимизировать таблицу — выполнить дефрагментацию и перестроить индексы — мы можем использовать инструкцию OPTIMIZE TABLE, однако для InnoDB это будет равнозначно запуску так называемой null alter операции:

ALTER TABLE my_table FORCE;

Для более детального анализа возможных проблем можно воспользоваться конструкцией CHECK TABLE, которая имеет несколько типов проверок и показывает сообщения о проблемах в указанной таблице:

CHECK TABLE my_table QUICK;

Большинство проблем, найденных таким образом, можно исправить той же ALTER TABLE операцией. Здесь, однако, стоит учитывать, что это блокирует БД и может выполняться продолжительное время для таблиц с большим количеством данных.