Если ваше приложение выполняет много операций удаления и обновления базы данных на MySQL, то существует высокая вероятность того, что ваши файлы данных MySQL , будут фрагментированы.
Это приведет к множеству неиспользованного пространства, а также может повлиять на производительность.
Таким образом, настоятельно рекомендуется дефрагментировать таблицы в MySQL на постоянной основе.
В этом руководстве объясняется, как оптимизировать и дефрагментировать таблицы в MySQL.
1. Определение таблиц для оптимизации
Первый шаг заключается в определении, есть ли у вас фрагментации на вашей базе данных MySQL.
Подключитесь к базе данных MySQL, и выполните следующий запрос, который будет отображать, сколько неиспользуемого пространства доступны в каждой таблице.
mysql> use AndreyExBase; mysql> 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; +------------+----------------+--------------+ | table_name | data_length_mb | data_free_mb | +------------+----------------+--------------+ | MANAGERS | 7841 | 4872 | | DEPARTMENT | 14892 | 13613 | | EMPLOYEE | 21031 | 19531 | +------------+----------------+--------------+
В приведенном выше выводе:
- Отображает список всех таблиц, которые имеет минимум 500MB неиспользованного пространства. Как мы видим выше, в данном примере, есть 3 таблицы, которые имеют более чем 500MB неиспользованного пространства.
- Колонка data_length_mb показывает общий размер таблицы в MB. Например, размер таблицы EMPLOYEE составляет около 21GB.
- Столбец data_free_mb отображает общее неиспользуемое пространство в этой конкретной таблице. Например, таблица EMPLOYEE имеет 19MB неиспользованного пространства.
- Все эти три таблицы (EMPLOYEE, DEPARTMENT и MANAGERS) сильно фрагментированы, и они должны быть оптимизированы, чтобы освободить неиспользуемое пространство.
На уровне файловой системы вы можете увидеть размер отдельных файлов таблицы, как показано ниже.
Размер файла будет таким же, как вы видите в колонке “data_length_mb” в приведенном выше выводе.
# ls -lh /var/lib/mysql/AndreyExBase/ .. -rw-rw----. 1 mysql mysql 7.8G Apr 13 09:35 MANAGERS.MYD -rw-rw----. 1 mysql mysql 16G Apr 13 13:33 DEPARTMENT.MYD -rw-rw----. 1 mysql mysql 21G Apr 13 13:13 EMPLOYEE.MYD ..
В этом примере файл EMPLOYEE.MYD занимает 21GB на уровне файловой системы, но он имеет много неиспользованного пространства. Если мы оптимизируем эту таблицу, размер этого файла должен сильно уменьшится.
2. Дефрагментация с помощью команды OPTIMIZE TABLE
Есть два способа оптимизации таблицы.
Первый способ заключается в использовании команды Optimize table, как показано ниже.
Следующий пример позволит оптимизировать таблицу EMPLOYEE.
mysql> use AndreyExBase; mysql> OPTIMIZE TABLE EMPLOYEE;
Вы также можете оптимизировать несколько таблиц в одной команде, как показано ниже.
mysql> OPTIMIZE TABLE EMPLOYEE, DEPARTMENT, MANAGERS
Несколько моментов, надо иметь в виду при оптимизации таблиц:
- Оптимизация таблицы может быть выполнена для InnoDB или MyISAM или таблиц архивов.
- Для таблиц MyISAM, будет произведен анализ таблицы, и будет дефрагментировать соответствующий MySQL файл данных, и освободит неиспользуемое пространство.
- Для таблиц InnoDB, после оптимизации таблицы надо будет просто выполнить alter table, чтобы восстановить пространство.
- Если у вас есть индексы, они также изменят страницы индекса, и обновят статистику.
В процессе оптимизации, MySQL создаст временную таблицу для таблицы, и после оптимизации удалить исходную таблицу, и переименовать эту временную таблицу в исходную таблицы.
В приведенной выше оптимизации, таблица EMPLOYEE представляет собой таблицу MyISAM.
Для этого примера, перед оптимизацией, вы увидите следующий файл с расширением .MYD для таблицы.
# ls -lh /var/lib/mysql/AndreyExBase/EMPLOYEE.* -rw-rw----. 1 mysql mysql 21G Apr 13 13:13 EMPLOYEE.MYD
Во время работы команды OPTIMIZE TABLE, вы можете увидеть, что он создал временный файл для таблицы с расширением .TMD. Размер этого временного файла будет продолжать расти, пока таблица оптимизируются.
# ls -lh /var/lib/mysql/AndreyExBase/EMPLOYEE.* -rw-rw----. 1 mysql mysql 21G Apr 13 13:13 EMPLOYEE.MYD -rw-rw----. 1 mysql mysql 500M Apr 13 13:20 EMPLOYEE.TMD
После завершения команды оптимизируют таблицы, вы не увидите временную таблицу. Вместо этого вы увидите оригинальный файл EMPLOYEE.MYD, который оптимизирован и с уменьшенным размером файла.
# ls -lh /var/lib/mysql/AndreyExBase/EMPLOYEE.* -rw-rw----. 1 mysql mysql 3G Apr 13 13:18 EMPLOYEE.MYD
3. Дефрагментация с помощью команды mysqlcheck
Второй метод для оптимизации таблицы используется команда mysqlcheck, как показано ниже.
Следующий пример позволит оптимизировать таблицу отделов. Вы выполнить эту команду из командной строки Linux (а не в MySQL).
# mysqlcheck -o AndreyExBase DEPARTMENT -u root -pMySQLSecretsPwd11 AndreyExBase.DEPARTMENT OK
Примечание: Внутренняя команда mysqlcheck использует команду “OPTIMIZE TABLE”.
В приведенном выше примере:
- mysqlcheck это команда, которая выполняется из командной строки Linux.
- -o параметр, чтобы указать, что mysqlcheck должен выполнять операцию “OPTIMIZE TABLE”.
- AndreyExBase является база данных
- DEPARTMENT является таблица внутри базы данных AndreyExBase, которая должна быть оптимизирована
- -u root указывает на то, что команда mysqlcheck должна использовать “root” в качестве пользователя mysql для подключения
- -p указывает пароль для root MySQL. Обратите внимание, что нет пространства между опцией -p и паролем.
Помимо оптимизации, вы можете также использовать команду mysqlcheck, чтобы проверить, анализировать и ремонтировать таблицы в вашей базе данных MySQL.
4. Дефрагментация всех таблиц или всех баз данных
Если вы хотите оптимизировать все таблицы в определенной базе данных MySQL, используйте следующую команду.
Следующая команда будет оптимизировать все таблицы, расположенные в базе данных AndreyExBase.
mysqlcheck -o AndreyExBase -u root -pMySQLSecretsPwd11
Если у вас есть несколько рабочих баз данных в вашей системе, вы можете оптимизировать все таблицы, расположенные в базе данных вашей системе, используя следующую команду.
Ниже будет оптимизировать все базы данных в вашей системе.
mysqlcheck -o --all-databases -u root -pMySQLSecretsPwd11
5. После оптимизации
После оптимизации, используя следующий запрос, проверьте total-size и unused-space-size для трех таблиц, которые мы оптимизировали в этом примере.
mysql> use AndreyExBase; mysql> 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 table_name in ( 'EMPLOYEE', 'DEPARTMENT', 'MANAGERS' ); +------------+----------------+--------------+ | table_name | data_length_mb | data_free_mb | +------------+----------------+--------------+ | MANAGERS | 2863 | 0 | | DEPARTMENT | 881 | 0 | | EMPLOYEE | 1695 | 0 | +------------+----------------+--------------+
Как видно из приведенных выше выходных данных, то data_length_mb сильно уменьшилась для этих таблиц после оптимизации. Кроме того, data_free_mb теперь равна 0, так как нет никакой фрагментации больше.
Размер файла для этих таблиц теперь намного меньше по сравнению с исходным размером. Теперь мы оптимизировали много неиспользуемого пространства на уровне файловой системы для этих таблиц.
# ls -lh /var/lib/mysql/AndreyExBase/ .. -rw-rw----. 1 mysql mysql 3G Apr 13 13:22 MANAGERS.MYD -rw-rw----. 1 mysql mysql 880M Apr 13 13:28 DEPARTMENT.MYD -rw-rw----. 1 mysql mysql 2G Apr 13 13:44 EMPLOYEE.MYD ..
В этом примере, мы освободили около 37GB неиспользованного пространства после оптимизации этих трех таблиц.