Полезные команды MySQL

Моя подборка полезных команд для MySQL.

Узнать размер базы данных

SELECT table_schema "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM   information_schema.tables
GROUP  BY table_schema
ORDER BY Round(Sum(data_length + index_length) / 1024 / 1024, 1) DESC;

Выводим 15 наиболее фрагментированных таблиц

SELECT CONCAT(TABLE_SCHEMA, ":",TABLE_NAME) AS TABLENAME, 
ROUND(DATA_FREE * 100/ DATA_LENGTH,1) AS FRAG_PERCENTAGE 
FROM information_schema.tables 
ORDER BY FRAG_PERCENTAGE DESC
LIMIT 15;

где FRAG_PERCENTAGE - соотношение свободного места в таблице к занятному.

Стоит заметить, что этот метод не работает, когда вы используете InnoDB таблицы без включенной опции innodb_file_per_table. Когда эта опция выключена, все таблицы innodb хранятся в одном файле: /var/lib/mysql/ibdata1. В таком случае результат будет одинаков для всех таблиц. Дефрагментация в данном случае тоже поможет, все таблицы и индексы будут упорядочены в этом файле, однако файл ibdata1 всё равно будет занимать столько же места, сколько и до дефрагментации (просто свободное место в нём будет упорядочено и аккуратно размечено:) ).

Чтобы можно было получать адекватные значение по каждой InnoDB таблице, надо выполнить следующее:

• Сделать дамп всех баз
• Включить опцию innodb_file_per_table
• Удалить файл /var/lib/mysql/ibdata1
• Залить обратно дамп

Ниже более интересная команда:

Выводим 15 наиболее фрагментированных таблиц, их размер и движок

SELECT CONCAT(TABLE_SCHEMA, ":",TABLE_NAME) AS TABLENAME,
ROUND(DATA_FREE * 100/ DATA_LENGTH,1) AS FRAG_PERCENTAGE,
ENGINE , (DATA_LENGTH+INDEX_LENGTH)/(1024*1024) AS "SIZE (MB)" 
FROM information_schema.tables 
ORDER BY FRAG_PERCENTAGE DESC 
LIMIT 15;

Дефрагментируем таблицы

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

OPTIMIZE TABLE tablename

где tablename - название дефрагментированной таблицы. Стоит заметить, что для InnoDB таблиц OPTIMIZE TABLE - всего лишь обертка для ALTER TABLE tablename ENGINE="INNODB", которая перестраивает таблицу и оптимизирует индексы.