MySQL: テーブルからデータを削除してもディスク容量が減らない？その原因と解決策

2024-05-24

MySQL InnoDB でテーブルからデータ行を削除してもディスク領域が解放されない問題とその解決策

innodb_file_per_table オプションが有効になっている場合、各テーブルのデータとインデックスは個別の表領域ファイルに保存されます。このオプションを有効にすると、テーブルを削除したり、データを削除したりしても、使用済みディスク領域はすぐに解放されません。解放されるのは、その領域が別の操作で必要になったときだけです。

解決策

以下のいずれかの方法で、innodb_file_per_table オプションを無効にすることができます。

サーバー設定ファイルを変更する
ALTER TABLE ステートメントを使用する

古いバージョンの MySQL には、行の削除後もディスク領域を解放しないバグが存在する可能性があります。

MySQL を最新バージョンに更新してください。

テーブルの再構築

OPTIMIZE TABLE ステートメントを使用して、テーブルを再構築すると、未使用のディスク領域が解放される可能性があります。

パージ操作

InnoDB は、不要になったデータを自動的に削除するパージ操作を実行します。しかし、この操作は定期的に実行されるわけではないため、すぐにディスク領域が解放されるとは限りません。

INNODB_PURGE_SCHEDULER パラメータを使用して、パージ操作の実行頻度を調整することができます。

テーブルスペースがいっぱいになっている場合は、古いデータを削除したり、テーブルスペースを増やしたりする必要があります。
ディスク I/O のパフォーマンスが問題になっている場合は、innodb_buffer_pool_size パラメータなどのパフォーマンス設定を調整する必要があります。

注: 上記の情報は、MySQL 8.0 を対象としています。古いバージョンの MySQL を使用している場合は、異なる動作をする可能性があります。

-- Create a table
CREATE TABLE mytable (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

-- Insert some data
INSERT INTO mytable (name, email) VALUES ('John Doe', '[email protected]'), ('Jane Doe', '[email protected]'), ('Peter Jones', '[email protected]');

-- Delete some data
DELETE FROM mytable WHERE id IN (1, 2);

-- Check the table size
SELECT DATA_LENGTH, INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mytable';

-- Recreate the table
ALTER TABLE mytable ENGINE = InnoDB;

-- Check the table size again
SELECT DATA_LENGTH, INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mytable';

In this example, the DELETE statement will delete the rows with IDs 1 and 2 from the mytable table. However, the disk space used by these rows will not be immediately reclaimed.

To reclaim the disk space, the ALTER TABLE statement is used to recreate the table. This will cause the table to be reorganized and the unused disk space to be reclaimed.

The SELECT statements are used to check the table size before and after the ALTER TABLE statement. As you can see, the table size is significantly smaller after the table has been recreated.

Note: The ALTER TABLE statement will block access to the table while it is being recreated. If you cannot afford to block access to the table, you can use the OPTIMIZE TABLE statement instead. However, OPTIMIZE TABLE may not reclaim as much disk space as ALTER TABLE.

I hope this helps!