6 простых советов по уменьшению размера базы данных MySQL

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

Резервное копирование

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

# Backup Single DB
mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql
# Backup Entire DBMS
mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p

Если вы используете MariaDB, у вас есть возможность полного или инкрементального резервного копирования с помощью утилиты mariabackup.

$ mariabackup --backup --target-dir=/var/mariadb/backup/ --user=myuser --password=mypassword

Список таблиц и индексов MySQL

Используйте следующие запросы для мониторинга и оценки таблицы и размера индекса.
Следующий запрос возвращает размер каждой базы данных в МБ:

MariaDB [(none)]> SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
 FROM information_schema.tables
 GROUP BY table_schema;
+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| hope               |       75714.0 |
| information_schema |           0.2 |
| mysql              |           1.9 |
| performance_schema |           0.0 |
+--------------------+---------------+

Следующий запрос возвращает размер каждой таблицы в Мб:

SELECT table_schema as `Database`,table_name AS `Table`,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC
LIMIT 5;       -- adjust it according to your needs
+----------+------------------------+------------+
| Database | Table                  | Size in MB |
+----------+------------------------+------------+
| hope     | eth_products           |   44029.54 |
| hope     | eth_customers          |   28868.08 |
| hope     | eth_emails             |    1423.92 |
| hope     | eth_id                 |    1392.43 |
| mysql    | help_topic             |       1.38 |
+----------+------------------------+------------+

Query below returns index size ordered from the ones using the most. Следующий запрос возвращает размер индекса, упорядоченный по частоте использования:

SELECT table_schema as database_name,table_name,round(index_length/1024/1024,2) as index_size
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
     and table_schema not in ('information_schema', 'sys','performance_schema', 'mysql')
     and table_schema = 'your database name'
ORDER BY index_size desc;
+---------------+------------------------+------------+
| database_name | table_name             | index_size |
+---------------+------------------------+------------+
| hope          | eth_products           |   18561.74 |
| hope          | eth_customers          |   12037.89 |
| hope          | eth_emails             |     638.70 |
| hope          | eth_id                 |     607.20 |
| hope          | eth_temp               |       0.00 |
+---------------+------------------------+------------+

Удаление неиспользуемых данных

Самый простой и самый сложный способ уменьшить размер MySQL — удалить все ненужные данные.
Администраторы БД обычно заполняют таблицы или столбцы БД ненужными данными.
Переоценка схемы БД необходима для выявления таких случаев.
Следующий запрос помогает определить дату последнего обновления таблицы:

SELECT table_schema,table_name,create_time,update_time from information_schema.tables 
WHERE table_schema not in ('information_schema','mysql') 
    and engine is not null and ((update_time < (now() - interval 1 day)) or update_time is NULL) 
LIMIT 5;
+--------------+------------------------+---------------------+---------------------+
| table_schema | table_name             | create_time         | update_time         |
+--------------+------------------------+---------------------+---------------------+
| MariaDB      | eth_table1             | 2019-08-23 20:52:51 | 2019-08-23 22:54:34 |
| MariaDB      | eth_table2             | 2019-08-23 19:20:23 | 2019-08-23 19:20:23 |
| MariaDB      | eth_table3             | 2019-08-23 19:20:29 | 2019-08-23 19:20:29 |
| MariaDB      | eth_table4             | 2019-08-26 19:18:04 | 2019-08-26 21:05:10 |
| MariaDB      | eth_temp               | 2019-08-25 01:52:33 | 2019-08-25 21:16:16 |
+--------------+------------------------+---------------------+---------------------+
Если все неиспользуемые данные неопознаны, следующие команды помогут вам удалить их:

**Be extra careful when using delete/drop commands ! Deleted data cannot be recovered!**
DELETE FROM table1 / TRUNCATE table1        --Deletes all Records
DELETE FROM table1 WHERE condition          --Deletes records based on a condition
DROP TABLE table                            --Deletes table
DROP DATABASE                               --Deleting database
ALTER TABLE table_name DROP column_name;    --Deletes a column

Поиск и уделение неиспользуемых индексов

A general rule of thumb is that the more indexes you have on a table, the slower INSERT, UPDATE, and DELETEoperations will be and more disk space will be consumed. It is essential to track down unused indexes that consume disk space and slow down your database.

By default, statistics are not collected. This is to ensure that statistics collection does not cause any extra load on the server unless desired.

To enable statistics dynamically execute the following command :

Общее правило заключается в том, что чем больше индексов в таблице, тем медленнее будут выполняться операции INSERT, UPDATE и DELETE и будет потребляться больше дискового пространства.
Очень важно отслеживать неиспользуемые индексы, которые занимают дисковое пространство и замедляют работу вашей базы данных.
По умолчанию статистика не собирается. Это делается для того, чтобы сбор статистики не вызывал дополнительной нагрузки на сервер, если это не требуется.
Чтобы включить статистику динамически, выполните следующую команду: 

SET GLOBAL userstat=1;
Теперь каждый запрос к базе данных обновляет статистические таблицы TABLE_STATISTICS и INDEX_STATISTICS.
В первой таблице показано количество строк, прочитанных из таблицы, и количество строк, измененных в таблице.
Вторая таблица показывает статистику использования индекса.

SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
+--------------+------------------------+-----------+--------------+------------------------+
| TABLE_SCHEMA | TABLE_NAME             | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+--------------+------------------------+-----------+--------------+------------------------+
| hope         | eth_table1             |      1004 |            0 |                      0 |
| hope         | eth_table2             |  14343683 |            0 |                      0 |
| hope         | eth_table3             |      1002 |            0 |                      0 |
+--------------+------------------------+-----------+--------------+------------------------+
SELECT * FROM INDEX_STATISTICS;
+--------------+-------------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME        | INDEX_NAME | ROWS_READ |
+--------------+-------------------+------------+-----------+
| hope         | eth_table1        | PRIMARY    |         2 |
| hope         | eth_table2        | PRIMARY    |         4 |
+--------------+-------------------+------------+-----------+
С помощью новых таблиц вы сможете найти все неиспользуемые индексы одним запросом:

SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS
ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND
   s.TABLE_NAME=INDXS.TABLE_NAME AND
   s.INDEX_NAME=INDXS.INDEX_NAME)
WHERE INDXS.TABLE_SCHEMA IS NULL;

В заключение, для удаления неиспользуемых индексов введите команду:

DROP INDEX index_name ON table_name;

После окончания работ не забудьте вернуть параметр userstat=0.

Подрезка и оптимиизация MySQL

Обычно MySQL InnoDB не освобождает дисковое пространство после удаления строк данных из таблицы. Он сохраняет место для повторного использования позже.
OPTIMIZE TABLE реорганизует физическое хранилище данных таблицы и связанных с ней индексных данных, чтобы уменьшить объем памяти и повысить эффективность ввода-вывода при доступе к таблице. Точные изменения, внесенные в каждую таблицу, зависят от механизма хранения, используемого этой таблицей.
Оптимизация доступна только тогда, когда включен innodb_file_per_table.
Проверьте свою конфигурацию следующим образом:

MariaDB [(none)]> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

Вы можете использовать OPTIMIZE TABLE, чтобы освободить неиспользуемое пространство и дефрагментировать файл данных.

OPTIMIZE table MyTable;
+-------------------+----------+----------+----------+
| Table             | Op       | Msg_type | Msg_text |
+-------------------+----------+----------+----------+
| testDB.MyTable    | optimize | status   | OK       |
+-------------------+----------+----------+----------+

Когда innodb_file_per_table выключен, все данные будут храниться в файлах ibdata.
Если вы удалите некоторые таблицы и удалите некоторые данные, есть два способа вернуть неиспользуемый диск:
- Полностью сбросить базу данных, затем удалить ее и, наконец, перезагрузить.
- Измените механизм хранения и верните его к предыдущей конфигурации.
Это окончательно воссоздаст таблицу и индексы с самого начала, а неиспользуемое дисковое пространство будет освобождено.

ALTER TABLE my_table ENGINE = MyISAM;
ALTER TABLE my_table ENGINE = InnoDB;

Всякий раз, когда вы запускаете OPTIMIZE или ALTER TABLE, MySQL создает новый файл данных до тех пор, пока операция не завершится. Не забудьте обеспечить достаточное количество дискового пространства для успешного завершения операций! Если вы хотите оптимизировать таблицу размером 10 ГБ, убедитесь, что на диске имеется более 10 ГБ свободного места.

Оптимизация типов данных для столбцов

Выбор правильного типа данных для поля в таблице может существенно повлиять на использование диска.
Хэш из 40 цифр может быть определен с использованием разных типов данных. Если хэш определен как константа VA(40), длина ключа будет равна 40. 

EXPLAIN SELECT * 
FROM eth_myhashes 
WHERE hash='0000032f265716fc8107f49f15336ec291169679'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customers
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 40
          ref: const
         rows: 1

Если хеш определен как BINARY, то длина ключа равна 20, поэтому использование диска намного меньше. Не забудьте преобразовать двоичный код в шестнадцатеричный с помощью x.

EXPLAIN SELECT * 
FROM eth_newtable 
WHERE address=x'0000032f265716fc8107f49f15336ec291169679'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customers
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 20
          ref: const
         rows: 1

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

Включить сжатие столбцов (только InnoDB)

Формат строк таблицы определяет, как физически хранятся ее строки, что, в свою очередь, может влиять на производительность запросов, операций DML и использование диска.

Формат строки COMPRESSED аналогичен формату строки COMPACT, но таблицы, использующие формат строки COMPRESSED, могут хранить еще больше данных на страницах переполнения, чем в таблицах, использующих формат строк COMPACT.
Это приводит к более эффективному хранению данных, чем таблицы, использующие формат строк COMPACT, особенно для таблиц, содержащих столбцы, использующие типы данных VARBINARY, VARCHAR, BLO и TEXT.

ALTER TABLE `myTable`
    ROW_FORMAT=COMPRESSED;

Сжатие таблиц (только MyISAM)

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

Полученные таблицы доступны только для чтения и обычно уменьшают размер MySQL примерно на 40–70%.

$sudo /etc/init.d/mysql stop                         # Stop DB
$cd /var/lib/mysql/
$myisamchk database.MYI                              # DB is compressed
$myisamchk -rq --sort-index --analyze database.MYI   # Index Regenaration
$mysqladmin flush-tables                             # Close open tables and flush
$sudo /etc/init.d/mysql start                        # Start DB

Обновлено: 17.01.2024