Оптимизация InnoDB основного движка хранения данных MySQL

InnoDB — основной движок для MySQL, который с версии 5.5 стал дефолтным. Поддерживает транзакции, репликацию, построчную блокировку. В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB в настройках по умолчанию хранятся в больших совместно используемых файлах. То есть данные для всех таблиц и всех баз данных хранятся в одном файле, изменить это можно с помощью настроек опции innodb_file_per_table.

MySQL по умолчанию все таблички innodb хранит в одном файле - когда их накапливается приличное количество - файл значительно разрастается. Плюс не забывайте, что при удалении данных в innodb - размер файла не уменьшается - он растет только в большую сторону. Так что если данных в базе у вас много или идет активное удаление - рано или поздно вы задумаетесь о том, чтобы выполнить подобное разделение. Что нужно сделать:

1. Выключаем всех клиентов MySQL, не должно быть никаких обращений.
2. Делаем ПОЛНЫЙ бекап всех баз данных и конфигурационных файлов
3. Удаляем все таблицы из БД
4. Выключаем mysqld
5. В /etc/my.cnf удаляем старое значение innodb_data_file_path и добавляем

   innodb_data_file_path=ibdata1:10M:autoextend
   innodb_file_per_table=1

6. удаляем cледующие файлы или сколько там файлов ibdata - оставлять старые logfile нельзя!

   /var/lib/ibdata1
   /var/lib/ib_logfile0
   /var/lib/ib_logfile1

7. запускаем mysqld
8. Загружаем таблицы обратно из бэкапов
9. Проверяем наличие свежесозданных файликов *.ibd

Все!

Скрипт для оптимизации файлов InnoDB конкретной базы данных. Команда OPTIMIZE TABLE уменьшит размер базы данных за счет: если таблица имеет удаленные или порванные строки, ремонтирует таблицу; если индексные страницы не отсортированы, сортирует их. Если статистика не современна (и ремонт не может быть выполнен с сортировкой индекса), модифицирует ее. Команда OPTIMIZE TABLE должна использоваться после удаления большей части таблицы или если в таблице было внесено много изменений в строки переменной длины (таблицы, в которых есть столбцы VARCHAR, BLOB или TEXT). Удаленные записи поддерживаются при помощи связного списка, и последующие операции INSERT повторно используют позиции старых записей. Чтобы перераспределить неиспользуемое пространство и дефрагментировать файл данных.

#!/bin/bash
# Author of the script DOR
DB_NAME="battery_hp"
TABLES=$(mysql  -N -B -e "SHOW TABLES FROM $DB_NAME")
for TABLE1 in $TABLES
do
  mysql  -e "OPTIMIZE TABLE $TABLE1" $DB_NAME
done

Второй вариант оптимизации заключается в пересоздании базы данных. Чтобы уменьшить размер своей базы данных с движком InnoDB, необходимо воспользоваться mysqldump, чтобы сделать дамп всех своих таблиц, создать новую базу данных и импортировать таблицы в новую базу данных.

Изучите статью my.cnf примеры конфигурации MySQL, MariaDB