MySQLのInnoDB テーブルの断片化(フラグメンテーション)を解消する

#概要
InnoDB では、追加・更新・削除操作を繰り返していると、断片化（フラグメンテーション）という現象が発生します。
これはいわば、ゴミみたいなもので、テーブルのデータを削除してもディスク容量が減りません。
このゴミが増えてくると、クエリ処理が遅くなる可能性があリます。

例えばレコードが100万件あるテーブルの内、99万9999件を削除し1件の状態にしても、テーブルが占有している領域は100万件分使っているということになります。

今回は実際にテストデータを作成し、フラグメンテーションの発生とその解消法について確認していきたいと思います。

フラグメンテーションの詳細については本記事では述べないので、気になる方は下記記事が分かりやすかったのでご参照ください。

#前提

• MySQL 5.7.31 InnoDB

#テストデータの挿入

まずテスト用のDBとテーブルを作り、約100万件テストデータを挿入します。

mysql> CREATE DATABASE test;

insert.sql

use test;

CREATE TABLE test (
  id INT PRIMARY KEY AUTO_INCREMENT,
  data1 VARCHAR(10) DEFAULT 'data1',
  data2 VARCHAR(10) DEFAULT 'data2',
  data3 VARCHAR(10) DEFAULT 'data3',
  data4 VARCHAR(10) DEFAULT 'data4',
  data5 VARCHAR(10) DEFAULT 'data5',
  created_at DATETIME
);

INSERT INTO test () VALUES ();
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;
INSERT INTO test (id) SELECT 0 FROM test;

UPDATE test SET
  created_at = ADDTIME(CONCAT_WS(' ','2021-01-01' + INTERVAL RAND() * 180 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401))));

importする。

$ mysql -u root -p test < insert.sql

#検証

フルスキャンすると2.10secかかる。

mysql> select * from test;
1048576 rows in set (2.10 sec)

データを1件にしてselectすると、なんと1件なのに1.64sec...?

mysql> select * from test;
+----+-------+-------+-------+-------+-------+---------------------+
| id | data1 | data2 | data3 | data4 | data5 | created_at          |
+----+-------+-------+-------+-------+-------+---------------------+
|  1 | data1 | data2 | data3 | data4 | data5 | 2021-04-25 17:50:36 |
+----+-------+-------+-------+-------+-------+---------------------+
1 row in set (1.64 sec)

テーブルの空き領域を確認すると、data_freeが残っている。
この値が高いほどフラグメンテーション率が高いと言うことになります。

mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='test';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| test         | test       | 145752064 |          0 |
+--------------+------------+-----------+------------+
1 row in set (0.03 sec)

フラグメンテーションを解消する。

mysql> alter table test engine innodb;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

再度テーブルの空き領域を確認すると、data_freeが0になっていることが確認できます。

mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='test';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| test         | test       |         0 |          1 |
+--------------+------------+-----------+------------+
1 row in set (0.02 sec)

フラグメンテーションが解消できたところで、再度フルスキャンすると0.00secになりました。わーい。

mysql> select * from test;
+----+-------+-------+-------+-------+-------+---------------------+
| id | data1 | data2 | data3 | data4 | data5 | created_at          |
+----+-------+-------+-------+-------+-------+---------------------+
|  1 | data1 | data2 | data3 | data4 | data5 | 2021-04-25 17:50:36 |
+----+-------+-------+-------+-------+-------+---------------------+
1 row in set (0.00 sec)

以上です。