MySQL でテーブルスペースの断片化を解消する詳細な例

MySQL でテーブルスペースの断片化を解消する詳細な例

断片化の原因

（1）テーブルのストレージは断片化されます。行が削除されるたびに、スペースが空になり、空のままになります。一定期間にわたって多数の削除操作が行われると、空のスペースがテーブルの内容を格納するために使用されるスペースよりも大きくなります。

（2）挿入操作を実行する場合、MySQLは空き領域を使用しようとします。ただし、空き領域が適切なサイズのデータ​​で占有されていない場合、空き領域を完全に占有することができず、断片化が発生します。

（３）MySQLがデータをスキャンする際、実際にはテーブルの容量要件の上限、つまりデータが書き込まれる領域のピーク部分をスキャンします。

例えば：

テーブルには 10,000 行あり、各行は 10 バイトで、100,000 バイトのストレージ スペースを占有します。削除操作を実行すると、1 行のみが残り、実際のコンテンツは 10 バイトのみになります。ただし、MySQL がそれを読み取ると、100,000 バイトのテーブルとして扱われます。したがって、フラグメントの数が多いほど、クエリのパフォーマンスに影響します。

テーブルフラグメントのサイズを確認する

（１）テーブルのフラグメントサイズを確認する

mysql> SHOW TABLE STATUS LIKE 'テーブル名';

結果の「Data_free」列の値はフラグメントサイズです。

（２）断片化されたテーブルを全てリストする

mysql> table_schema db、table_name、data_free、engine を選択   
information_schema.tables から 
table_schema が ('information_schema', 'mysql') に含まれず、data_free > 0 の場合;

テーブルの断片化を解消

（１）MyISAMテーブル

mysql> テーブルテーブル名を最適化する

（２）InnoDBテーブル

mysql> テーブル名を変更する エンジン = InnoDB

OPTIMIZE の動作はエンジンによって異なります。MyISAM はインデックスとデータが分離されているため、OPTIMIZE はデータ ファイルをソートし、インデックスを再配置することができます。

OPTIMIZE 操作はテーブルを一時的にロックし、データ量が多いほど時間がかかります。結局のところ、単純なクエリ操作ではありません。したがって、プログラムに Optimize コマンドを配置するのは適切ではありません。ヒット率をどれだけ低く設定しても、アクセス回数が増えると、全体のヒット率も増加し、プログラムの実行効率に大きな影響を与えることは間違いありません。より良い方法は、シェルを作成し、mysql のinformation_schema.TABLESフィールドを定期的にチェックし、DATA_FREE フィールドをチェックすることです。0 より大きい場合は、断片化があることを意味します。

提案

クリア操作により、テーブルが一時的にロックされます。データ量が多いほど、時間がかかります。スクリプトを作成し、アクセスが少ない時間帯に定期的に実行することができます。たとえば、毎週水曜日の夜明けに DATA_FREE フィールドを確認します。想定される警告値より大きい場合は、一度クリーンアップします。

ご質問がありましたら、メッセージを残すか、コミュニティに参加して話し合いましょう。お読みいただきありがとうございます。お役に立てれば幸いです。このサイトをサポートしていただき、ありがとうございます。