Может ли иметь таблица пространство WASTED/FRAGMENTED без удаления данных (DELETE) в MySQL?

• Думаете, фрагментация таблицы будет происходить только с DELETE?
• Верите ли вы, что INSERT также приведет к фрагментации таблицы?

Да, INSERT с ROLLBACK также может создавать фрагментацию таблицы. В этой статье мы собираемся объяснить, как INSERT вызывает фрагментацию таблицы.

Как происходит фрагментация таблицы с INSERT и ROLLBACK?

Когда вы запустите INSERT, он начнет записывать данные в файл табличного пространства (.ibd). Правильно?

Из образа мы создали таблицу и начали транзакцию (НАЧАЛО) с INSERT. В рамках транзакции INSERT была завершена, но мы не зафиксировали/откатили транзакцию.

Вы можете проверить рост файла табличного пространства до 1,2 ГБ.

Теперь мы собираемся откатить транзакцию.

После завершения ROLLBACK размер файла табличного пространства по-прежнему составляет 1,2 ГБ (дисковое пространство не уменьшается). Вы можете увидеть, что в таблице нет записей в разделе COUNT(*).

Есть смысл?

Теперь мы хотели бы проверить фрагментацию таблицы. Обычный запрос с INFORMATION_SCHEMA.TABLES не даст точного размера фрагментации. Используем запрос чтобы вычислить фрагментировано/неиспользуемое дисковое пространство. Запрос будет дополнительно использовать таблицу IINFORMATION_SCHEMA.NNODB_TABLESPACES.

Запрос:

SELECT NAME, TABLE_ROWS, format_bytes(data_length) DATA_SIZE,
format_bytes(index_length) INDEX_SIZE,
format_bytes(data_length+index_length) TOTAL_SIZE,
format_bytes(data_free) DATA_FREE,
format_bytes(FILE_SIZE) FILE_SIZE,
format_bytes((FILE_SIZE/10 – (data_length/10 + index_length/10))*10) WASTED_SIZE
FROM information_schema.TABLES as t
JOIN information_schema.INNODB_TABLESPACES as it
ON it.name = concat(table_schema,”/”,table_name) WHERE t.table_schema=’jc’ and t.table_name=’test_frag_Ins’;

 

Итак, теперь нам нужно восстановить таблицу для восстановления дискового пространства?

Да, дисковое пространство восстановлено.

Является ли фрагментированное дисковое пространство пригодным для повторного использования?

Да, его можно использовать повторно, приведенный ниже пример объяснит это.

Из приведенного выше снимка сначала мы создали фрагментированное пространство (1,2 ГБ) с помощью INSERT/ROLLBACK. Затем мы снова запустили тот же INSERT, но на этот раз он совершил транзакцию. Тем не менее дисковое пространство составляет 1,2 ГБ.

Есть смысл?

Надеюсь, что этот блог дает дополнительную информацию и поможет понять фрагментацию таблицы в MySQL!

Спасибо что были с нами!!! Если у вас есть какие-либо вопросы или отзывы, не стесняйтесь комментировать ниже.

Редактор: AndreyEx