Полезные команды 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», которая перестраивает таблицу и оптимизирует индексы.

Автор: AlexWinner