Человек прекрасно слышит голос разума, но он ему противен (Авессалом Подводный).

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

1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...

Статья опубликована: 15 января 2017

Как оптимизировать таблицы и дефрагментировать для оптимизации пространства в MySQL
Если ваше приложение выполняет много операций удаления и обновления базы данных на MySQL, то существует высокая вероятность того, что ваши файлы данных MySQL , будут фрагментированы.

Это приведет к множеству неиспользованного пространства, а также может повлиять на производительность.

Таким образом, настоятельно рекомендуется дефрагментировать таблицы в MySQL на постоянной основе.

В этом руководстве объясняется, как оптимизировать и дефрагментировать таблицы в MySQL.

1. Определение таблиц для оптимизации

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

Подключитесь к базе данных MySQL, и выполните следующий запрос, который будет отображать, сколько неиспользуемого пространства доступны в каждой таблице.

В приведенном выше выводе:

  • Отображает список всех таблиц, которые имеет минимум 500MB неиспользованного пространства. Как мы видим выше, в данном примере, есть 3 таблицы, которые имеют более чем 500MB неиспользованного пространства.
  • Колонка data_length_mb показывает общий размер таблицы в MB. Например, размер таблицы EMPLOYEE составляет около 21GB.
  • Столбец data_free_mb отображает общее неиспользуемое пространство в этой конкретной таблице. Например, таблица EMPLOYEE имеет 19MB неиспользованного пространства.
  • Все эти три таблицы (EMPLOYEE, DEPARTMENT и MANAGERS) сильно фрагментированы, и они должны быть оптимизированы, чтобы освободить неиспользуемое пространство.

На уровне файловой системы вы можете увидеть размер отдельных файлов таблицы, как показано ниже.

Размер файла будет таким же, как вы видите в колонке “data_length_mb” в приведенном выше выводе.

В этом примере файл EMPLOYEE.MYD занимает 21GB на уровне файловой системы, но он имеет много неиспользованного пространства. Если мы оптимизируем эту таблицу, размер этого файла должен сильно уменьшится.

2. Дефрагментация с помощью команды OPTIMIZE TABLE

Есть два способа оптимизации таблицы.

Первый способ заключается в использовании команды Optimize table, как показано ниже.

Следующий пример позволит оптимизировать таблицу EMPLOYEE.

Вы также можете оптимизировать несколько таблиц в одной команде, как показано ниже.

Несколько моментов, надо иметь в виду при оптимизации таблиц:

  • Оптимизация таблицы может быть выполнена для InnoDB или MyISAM или таблиц архивов.
  • Для таблиц MyISAM, будет произведен анализ таблицы, и будет дефрагментировать соответствующий MySQL файл данных, и освободит неиспользуемое пространство.
  • Для таблиц InnoDB, после оптимизации таблицы надо будет просто выполнить alter table, чтобы восстановить пространство.
  • Если у вас есть индексы, они также изменят страницы индекса, и обновят статистику.

В процессе оптимизации, MySQL создаст временную таблицу для таблицы, и после оптимизации удалить исходную таблицу, и переименовать эту временную таблицу в исходную таблицы.

В приведенной выше оптимизации, таблица EMPLOYEE представляет собой таблицу MyISAM.

Для этого примера, перед оптимизацией, вы увидите следующий файл с расширением .MYD для таблицы.

Во время работы команды OPTIMIZE TABLE, вы можете увидеть, что он создал временный файл для таблицы с расширением .TMD. Размер этого временного файла будет продолжать расти, пока таблица оптимизируются.

После завершения команды оптимизируют таблицы, вы не увидите временную таблицу. Вместо этого вы увидите оригинальный файл EMPLOYEE.MYD, который оптимизирован и с уменьшенным размером файла.

3. Дефрагментация с помощью команды mysqlcheck

Второй метод для оптимизации таблицы используется команда mysqlcheck, как показано ниже.

Следующий пример позволит оптимизировать таблицу отделов. Вы выполнить эту команду из командной строки Linux (а не в MySQL).

Примечание: Внутренняя команда 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.

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

Ниже будет оптимизировать все базы данных в вашей системе.

5. После оптимизации

После оптимизации, используя следующий запрос, проверьте total-size и unused-space-size для трех таблиц, которые мы оптимизировали в этом примере.

Как видно из приведенных выше выходных данных, то data_length_mb сильно уменьшилась для этих таблиц после оптимизации. Кроме того, data_free_mb теперь равна 0, так как нет никакой фрагментации больше.

Размер файла для этих таблиц теперь намного меньше по сравнению с исходным размером. Теперь мы оптимизировали много неиспользуемого пространства на уровне файловой системы для этих таблиц.

В этом примере, мы освободили около 37GB неиспользованного пространства после оптимизации этих трех таблиц.


Читайте также

    Добавить комментарий

    Войти с помощью: 

    Ваш e-mail не будет опубликован. Обязательные поля помечены *

    Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

    badge
    Обратный звонок 1
    Отправить
    galka

    Спасибо! Ваша заявка принята

    close
    galka

    Спасибо! Ваша заявка принята

    close