[MySQL] 断片化した InnoDB テーブルを最適化する

2019-12-13環境SQL,高速化

MySQL の InnoDB では、断片化（フラグメンテーション）という現象が発生する。
フラグメンテーションが発生すると、クエリ処理が遅くなったり、サーバーの容量をたくさん使い問題が起こる。フラグメンテーションを解消するには最適化をおこなう。
断片化についてと最適化の方法に関するメモ。

断片化（フラグメンテーション）とは

断片化とは、ディスク上のインデックスページの物理的な順序がページ上のレコードのインデックス順序とかけ離れているか、またはインデックスに割り当てられた 64 ページのブロック内に未使用のページが多数存在することを示します。

MySQL 5.6 リファレンスマニュアル – 14.10.4 テーブルのデフラグ

DB で DELETE クエリを実行すると、すぐに物理的な削除は行われない。削除フラグ的なのがつけられる論理削除となる。
なので、このレコードがあった場所には穴が空いた状態となる。場所を取っている分、容量も食っている。

こんなイメージ

この「穴が空いた状態」のせいで、効率が悪くなり SELECT , UPDATE 処理が遅くなる。そして容量を食っているので、いずれサーバー全体の容量不足となり、ダウンする。（実際なった。。。）

パージ処理によって、 DELETE レコードの物理削除と、領域の回収（領域の回収によって、無駄だった領域が解放され再び使えるようになる。また、データが詰め直され穴あきが解消される。）が行われる。

パージ処理がいつ行われるのか？は、

> 「古い不要なUndoログの削除」と「削除された行の本当の削除」を実行するという「パージ処理」は、どのようなタイミングで実行されるのでしょうか？

バージョンによりますが、メインスレッドもしくはパージスレッドで行われます。つまりトランザクションとは非同期です。

大人のためのInnoDBテーブルとの正しい付き合い方。 のコメント欄

「メインスレッドもしくはパージスレッド」が具体的にいつ行われるのかは調べても分からなかった。

パージ処理を待っていると間に合わない場合は、「最適化」という作業をおこない、フラグメンテーションを解消する。

最適化の例

WordPress の wp_options テーブルが異様な容量を食って、サーバーの容量を使い切った場面の対応をしたことがある。 wp_options テーブルを最適化すると、容量が小さくなった。

最適化前の状態
wp_options.ibd 4.0 GB

最適化後
wp_options.ibd 10 MB

フラグメンテーションの確認方法

無駄な領域がどれくらいあるのか確認する。

SELECT table_schema, table_name, data_free, table_rows
FROM information_schema.tables
WHERE table_schema = '対象の DB 名';

+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| wordpress    | wp_options |   4194304 |        221 |
+--------------+------------+-----------+------------+

data_free : 割り当てられているが、未使用スペースのバイト数。

最適化の方法

ALTER TABLE

ALTER TABLE テーブル名 ENGINE INNODB;

無駄領域があるテーブルにこのクエリを実行すると。

+------------+-----------+------------+
| table_name | data_free | table_rows |
+------------+-----------+------------+
| test1      |   4194304 |      23049 |
+------------+-----------+------------+

ALTER TABLE test1 ENGINE INNODB;

+------------+-----------+------------+
| table_name | data_free | table_rows |
+------------+-----------+------------+
| test1      |         0 |      23520 |
+------------+-----------+------------+

data_free が 0 になる。

OPTIMIZE TABLE

OPTIMIZE TABLE テーブル名;

OPTIMIZE TABLE と ALTER TABLE は裏では同じ処理をしている。
ただし、 MySQL 5.6.17 より前のバージョンだと、 OPTIMIZE TABLE 実行中はテーブルロックがかかる。 5.6.17 からは、オンライン DDL が使える。

phpMyAdmin の管理画面から

テーブル一覧画面で対象のテーブルにチェックを付け、ページ下部のセレクトボックスから「テーブルを最適化する」を選択する。