圧縮テーブルは、file-per-table テーブルスペースまたは general tablespaces で作成できます。 テーブル圧縮は、InnoDB system tablespace では使用できません。 システムテーブルスペース (領域 0、.ibdata files) には、ユーザーが作成したテーブルを含めることができますが、圧縮されない内部システムデータも含まれます。 したがって、圧縮は file-per-table または general テーブルスペースに格納されているテーブル (およびインデックス) にのみ適用されます。
File-Per-Table テーブルスペースでの圧縮テーブルの作成
file-per-table テーブルスペースに圧縮テーブルを作成するには、innodb_file_per_table を有効にする必要があります (デフォルト)。 このパラメータは、MySQL 構成ファイル (my.cnf または my.ini) で設定するか、SET ステートメントを使用して動的に設定できます。
innodb_file_per_table オプションの構成後、CREATE TABLE ステートメントまたは ALTER TABLE ステートメントで ROW_FORMAT=COMPRESSED 句または KEY_BLOCK_SIZE 句 (あるいはその両方) を指定して、file-per-table テーブルスペースに圧縮テーブルを作成します。
たとえば、次のステートメントを使用できます:
SET GLOBAL innodb_file_per_table=1;
CREATE TABLE t1
(c1 INT PRIMARY KEY)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;一般テーブルスペースでの圧縮テーブルの作成
一般的なテーブルスペースに圧縮テーブルを作成するには、テーブルスペースの作成時に指定される一般的なテーブルスペースに対して FILE_BLOCK_SIZE を定義する必要があります。 FILE_BLOCK_SIZE 値は、innodb_page_size 値に関連する有効な圧縮ページサイズである必要があり、CREATE TABLE または ALTER TABLE KEY_BLOCK_SIZE 句で定義された圧縮テーブルのページサイズは FILE_BLOCK_SIZE/1024 と同じである必要があります。 たとえば、innodb_page_size=16384 および FILE_BLOCK_SIZE=8192 の場合、テーブルの KEY_BLOCK_SIZE は 8 である必要があります。 詳細は、セクション15.6.3.3「一般テーブルスペース」を参照してください。
次の例は、一般的なテーブルスペースの作成および圧縮テーブルの追加を示しています。 この例では、デフォルトの innodb_page_size が 16K であると想定しています。 8192 の FILE_BLOCK_SIZE では、圧縮テーブルの KEY_BLOCK_SIZE が 8 である必要があります。
mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;メモ
MySQL 8.0 では、圧縮テーブルのテーブルスペースファイルは
InnoDBページサイズではなく物理ページサイズを使用して作成されるため、空の圧縮テーブルのテーブルスペースファイルの初期サイズは以前の MySQL リリースより小さくなります。ROW_FORMAT=COMPRESSEDを指定する場合は、KEY_BLOCK_SIZEを省略できます。KEY_BLOCK_SIZE設定のデフォルトはinnodb_page_size値の半分です。有効な
KEY_BLOCK_SIZE値を指定する場合は、ROW_FORMAT=COMPRESSEDを省略できます。圧縮は自動的に有効になります。KEY_BLOCK_SIZE,の最適な値を決定するには、通常、この句に異なる値を指定して同じテーブルの複数のコピーを作成し、生成される.ibdファイルのサイズを測定して、各ファイルが現実的な workload でどのように動作するかを確認します。 一般的なテーブルスペースの場合、テーブルを削除しても、一般的なテーブルスペースの.ibdファイルのサイズが小さくなることはなく、ディスク領域がオペレーティングシステムに戻されることもないことに注意してください。 詳細は、セクション15.6.3.3「一般テーブルスペース」を参照してください。KEY_BLOCK_SIZE値は、ヒントとして処理されます。InnoDBでは、必要に応じて異なるサイズが使用される可能性があります。 file-per-table テーブルスペースの場合、KEY_BLOCK_SIZEはinnodb_page_size値以下にのみできます。innodb_page_size値を超える値を指定した場合は、指定された値が無視され、警告が発行されます。また、KEY_BLOCK_SIZEはinnodb_page_size値の半分に設定されます。innodb_strict_mode=ONの場合、無効なKEY_BLOCK_SIZE値を指定するとエラーが返されます。 一般的なテーブルスペースの場合、有効なKEY_BLOCK_SIZE値はテーブルスペースのFILE_BLOCK_SIZE設定によって異なります。 詳細は、セクション15.6.3.3「一般テーブルスペース」を参照してください。InnoDBは 32KB および 64KB のページサイズをサポートしていますが、これらのページサイズは圧縮をサポートしていません。 詳細は、innodb_page_sizeのドキュメントを参照してください。InnoDBデータページのデフォルトの非圧縮サイズは、16K バイトです。 オプション値の組合せに応じて、MySQL では、テーブルスペースデータファイル (.ibdファイル) に 1KB、2KB、4KB、8KB または 16KB のページサイズが使用されます。 実際の圧縮アルゴリズムは、KEY_BLOCK_SIZE値の影響を受けません。この値によって、各圧縮済みチャンクの大きさが決定されるため、各圧縮済みページに詰め込むことができる行数が影響を受けます。file-per-table テーブルスペースに圧縮テーブルを作成する場合、
KEY_BLOCK_SIZEをInnoDBpage size と同等に設定しても、通常は圧縮があまり発生しません。 たとえば、InnoDBのページサイズは 16K バイトであるため、一般にKEY_BLOCK_SIZE=16を設定しても、大量の圧縮は発生しません。 多くの場合、このような値で適切に圧縮されるため、この設定は多くの長いBLOB、VARCHAR、またはTEXTカラムを持つテーブルで引き続き役立つことがあります。したがって、セクション15.9.1.5「InnoDB テーブルでの圧縮の動作」で説明したように、必要となるオーバーフローページが少なくなる可能性もあります。 一般的なテーブルスペースの場合、InnoDBページサイズと等しいKEY_BLOCK_SIZE値は許可されません。 詳細は、セクション15.6.3.3「一般テーブルスペース」を参照してください。テーブルのすべてのインデックス (クラスタ化されたインデックスを含む) は、
CREATE TABLEまたはALTER TABLEステートメントで指定されたものと同じページサイズを使用して圧縮されます。ROW_FORMATやKEY_BLOCK_SIZEなどのテーブル属性は、InnoDBテーブルのCREATE INDEX構文の一部ではなく、指定されている場合は無視されます (ただし、指定されている場合はSHOW CREATE TABLEステートメントの出力に表示されます)。パフォーマンス関連の構成オプションについては、セクション15.9.1.3「InnoDB テーブルの圧縮の調整」 を参照してください。
圧縮テーブル上の制約
圧縮テーブルは、
InnoDBシステムテーブルスペースに格納できません。一般テーブルスペースには複数のテーブルを含めることができますが、圧縮テーブルと非圧縮テーブルを同じ一般テーブルスペース内に共存させることはできません。
句の名前が
ROW_FORMATであるにもかかわらず、圧縮は個別の行にではなく、テーブル全体およびそれに関連付けられたすべてのインデックスに適用されます。InnoDBでは、圧縮一時テーブルはサポートされていません。innodb_strict_modeが有効な場合 (デフォルト)、ROW_FORMAT=COMPRESSEDまたはKEY_BLOCK_SIZEが指定されていると、CREATE TEMPORARY TABLEはエラーを返します。innodb_strict_modeが無効な場合は、警告が発行され、圧縮されていない行形式を使用して一時テーブルが作成されます。 一時テーブルに対するALTER TABLE操作にも同じ制限が適用されます。