InnoDB テーブルの BLOB 列と TEXT 列のストレージ効率を最適化します。

まず、MySQL InnoDB エンジンのストレージ形式に関する重要なポイントをいくつか紹介します。

1. InnoDB では、共有テーブルスペースまたは独立テーブルスペースを選択できます。管理とメンテナンスを容易にするために、独立テーブルスペースを使用することをお勧めします。 innodb_file_per_tableオプションを有効にします。5.5 以降では、このオプションをオンラインで動的に変更して有効にし、 ALTER TABLE xx ENGINE = InnoDBを実行して既存のテーブルを独立したテーブルスペースに変換できます。5.5 より前のバージョンでは、このオプションを変更した後、有効にするには再起動する必要があります。

2. InnoDB データ ページのデフォルト サイズは 16KB です。バージョン 5.6 以降では、新しいオプションinnodb_page_sizeを変更できます。バージョン 5.6 より前では、ソース コードを変更して再コンパイルすることしかできません。ただし、この構成のメリットとデメリットを十分に理解していない限り、この構成を変更することはお勧めしません。

3. InnoDB データ ページに新しいデータが書き込まれると、スペースの 1/16 が予約されます。予約されたスペースは、その後の新しいレコードの書き込みに使用できるため、新しいデータ ページを頻繁に追加することによるオーバーヘッドが削減されます。

4. 各データ ページには少なくとも 2 行のレコードを格納する必要があります。したがって、行レコードの最大長は理論上は 8 KB ですが、格納する InnoDB 内部データ構造がいくつかあるため、実際にはこれより小さくなります。

5. InnoDB のストレージ方式の制限により、データが順次書き込まれる場合、データ ページの理想的な充填率は 15/16 ですが、完全な順次書き込みを保証することは通常不可能です。そのため、データ ページの充填率は一般的に 1/2 から 15/16 になります。したがって、新しいレコードをできるだけ順番に書き込めるように、各 InnoDB テーブルに自動インクリメント列を主キーとして設定するのが最適です。

6. データ ページの充填率が 1/2 未満になると、InnoDB はそれを縮小して空き領域を解放します。

7. MySQL 5.6 の InnoDB エンジンは現在、 COMPACT 、 REDUNDANT 、 DYNAMIC 、 COMPRESSED 4 つの形式をサポートしています。デフォルトの形式は COMPACT です。 COMPRESSED はほとんど使用されておらず、推奨されません (次の項目を参照)。圧縮機能を使用する必要がある場合は、TokuDB エンジンを直接検討できます。

8. COMPACT 行形式では、REDUNDANT と比較して約 20% のストレージ スペースを節約でき、COMPRESSED では COMPACT と比較して約 50% のストレージ スペースを節約できますが、TPS は 90% 低下します。したがって、COMPRESSED 行形式の使用は強く推奨されません。

9. 行形式が DYNAMIC または COMPRESSED の場合、TEXT/BLOB などの長い列 (特定の状況によっては、必ずしも TEXT/BLOB 型だけでなく、他の長い列である場合もある長い列) は独立したデータ ページに完全に保存され、クラスター化インデックス ページは新しいページを指すために 20 バイトのポインターのみを使用します。これはいわゆるオフページであり、ORACLE の行移行に似ていますが、大量のディスク領域を浪費し、I/O パフォーマンスが低下します。したがって、長さが 255 を超える BLOB、TEXT、または VARCHAR 列タイプを使用しないことを強くお勧めします。

10. InnoDB ファイル形式 ( innodb_file_format ) が Antelope に設定され、行形式が COMPACT または REDUNDANT の場合、BLOB、TEXT、または長い VARCHAR 列には、クラスター化インデックス ページの最初の 768 バイトのみが格納されます (最大 768 バイトは、プレフィックス インデックスの作成を容易にするために使用されます)。残りのコンテンツは、1 バイトだけ多い場合でも、追加のページに格納されます。したがって、すべての列の長さは可能な限り短くする必要があります。

11. オフページに格納されている BLOB、TEXT、または long VARCHAR 列のページは排他的であり、共有できません。したがって、テーブル内で複数の長い列を使用することは強くお勧めしません。

要約すると、実際のビジネスで InnoDB テーブルに BLOB、TEXT、または長い VARCHAR 列を保存する必要がある場合は、次の提案があります。

1. 複数のオフページを回避するために、すべてのデータをシリアル化して圧縮し、同じ列に保存するようにしてください。

2. 実際の最大格納長が 255 未満の列をVARCHAR型またはCHAR型に変換します (可変長データの場合は両者に違いはありません。固定長データの場合は CHAR 型を使用します)。

3. すべての列を 1 つの列に統合できない場合は、2 番目の選択肢として、各列の最大長に応じて複数のサブテーブルに分割することができます。オフページの頻度を減らすために、各サブテーブルの合計行長を 8KB 未満にするようにしてください。

4. 上記の提案は、データ ページがデフォルトの 16 KB であるという前提に基づいています。8 KB または他のサイズに変更する場合は、上記の理論に基づいて自分でテストし、最も適切な値を見つけてください。

5. 文字列の長さが 255 未満の場合、CHAR または VARCHAR がストレージに使用されているか、または VARCHAR 列の長さが 255 に定義されているかに関係なく、実際の表スペースは増加しません。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。