Размер базы данных

Что определяет размер базы данных?

Общий размер базы данных – это сумма размеров ее таблиц.

Размер таблицы – это не только размер строк, которые можно увидеть в таблице, но и дополнительные функции таблицы (различные типы ключей и индексов), а также неиспользуемое пространство, оставшееся в результате запросов и действий.

Размер таблицы базы данных состоит из трех частей:

• данные
• индексы
• оверхед

Данные для каждого типа данных хранятся на сервере базы данных в файлах, которые занимают свободное дисковое пространство на сервере.

Как просмотреть размер базы данных и ее таблиц?

Размер базы данных и ее таблиц можно посмотреть с помощью различных клиентских программ и phpMyAdmin или непосредственно из SSH-терминала.

Для этого мы будем использовать phpMyAdmin.

Войдя в phpMyAdmin, выберите нужную базу данных в левой колонке, для которой на первой вкладке Structure отобразятся все таблицы, а в самом конце, в последней строке таблицы, будет показан общий размер таблиц. Однако это не включает оверхед таблиц:

Чтобы узнать реальный размер таблицы (данные + индексы + оверхед), нужно перейти на вкладку Structure таблицы и найти таблицу Space usage в подразделе Information. В строке Effective – фактический размер таблицы на сервере базы данных:

Что такое оверхед таблицы?

Оверхед – это неосвобожденное пространство таблицы базы данных MySQL / MariaDB, которое выделено для таблицы, но не используется.

Это можно увидеть в таблице information_schema.TABLES в столбце Data_free. Размер отображается в байтах.

Причиной Data_free является неиспользованное и фрагментированное пространство из-за удаления строк из таблицы (DELETE), обновления строк (UPDATE) с большими значениями, уменьшения и перегруппировки пространства таблицы, резервирования места для будущих запросов (INSERT, UPDATE).

Как найти таблицы с большими оверхед?

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

SHOW TABLE STATUS WHERE `Data_free` > 0;

Столбец Data_free таблицы результатов запроса показывает объем таблицы, размеры отображаются в байтах:

Более подробный SQL-запрос, показывающий размеры в мегабайтах каждого из трех типов данных в отдельности и общий фактический размер таблицы (данные + индексы + оверхед).
Замените в команде [DATABASE_NAME] на фактическое имя базы данных:

SELECT `TABLE_SCHEMA` AS "Database", `TABLE_NAME` AS "Table", `ENGINE`, `ROW_FORMAT`, `TABLE_ROWS`, ROUND(((`DATA_LENGTH`) / 1024 / 1024), 2) AS "Data Size (MiB)", ROUND(((`INDEX_LENGTH`) / 1024 / 1024), 2) AS "Index Size (MiB)", ROUND(((`DATA_FREE`) / 1024 / 1024), 2) AS "Overhead (MiB)", ROUND(((`DATA_LENGTH` + `INDEX_LENGTH` + `DATA_FREE`) / 1024 / 1024), 2) AS "Total Size (MiB)" FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = '[DATABASE_NAME]' AND `DATA_FREE` > 0 ORDER BY `DATA_FREE` DESC;

Видно, что размер таблицы опций WordPress составляет 113,3 MiB, а под неосвобожденное пространство засунуто 8337 MiB или 8,1 GiB – это более чем в 70 раз больше, чем размер данных таблицы!

Освобождение неиспользуемого пространства – оптимизация таблиц

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

Для оптимизации таблицы существует ссылка Optimize table в таблице Space usage на странице вкладки Structure, а также на странице вкладки Operations.

В качестве альтернативы можно составить SQL-запрос для оптимизации таблицы (замените [DATABASE_NAME] и [TABLE_NAME] в команде на название базы данных и таблицы):

OPTIMIZE TABLE `[DATABASE_NAME]`.`[TABLE_NAME]`;