Поговорите с человеком о нем, и он будет слушать вас часами (Б. Дизраэли).

Как оптимизировать таблицы и дефрагментировать для оптимизации пространства в MySQL

Главное меню » Базы данных » База данных MySQL » Как оптимизировать таблицы и дефрагментировать для оптимизации пространства в MySQL

15.01.2017

Если ваше приложение выполняет много операций удаления и обновления базы данных на 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 неиспользованного пространства после оптимизации этих трех таблиц.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Просмотров поста: 874