テーブルのインデックスでランダムな挿入または削除が行われると、インデックスがフラグメント化されることがあります。断片化とは、ディスクでのインデックスページの物理的な順序が、ページでのレコードのインデックス順とかけ離れていること、またはインデックスに割り当てられた 64 ページのブロック内に多数の未使用ページがあることを意味します。

断片化の兆候の 1 つは、テーブルが 「必要とする」 以上の領域を取るということです。それがどの程度なのかということを正確に決めるのは困難です。すべての InnoDB データとインデックスは B ツリー内に格納され、それらの充てん比は 50% から 100% で異なっています。その他の断片化の兆候は、このようなテーブル走査に 「必要」 以上の時間がかかるということです。

SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;

(このクエリーでは、SQL オプティマイザを 「だまして」 二次インデックスの代わりにクラスタインデックスを走査するよう仕向けている。) ほとんどのディスクが 1 秒に付き 10M バイトから 50M バイトで読み込むことができるので、テーブル走査がどれくらいのスピードでなければいけないかを概算できます。

「空の」 ALTER TABLE 操作を定期的に実行すると、MySQL によってそのテーブルが再構築されるため、インデックスの走査が高速になる可能性があります。

ALTER TABLE tbl_name ENGINE=INNODB

デフラグ操作を行う別の方法は、テーブルをテキストファイルにダンプし、テーブルを削除し、そしてそれをダンプファイルから再ロードするために mysqldump を利用することです。

インデックスへの挿入が常に昇順で行われ、レコードが必ず末尾から削除される場合は、InnoDB のファイル領域管理アルゴリズムによってインデックスの断片化が発生しないことが保証されます。