このページは機械翻訳したものです。
テーブルの行形式によって、その行が物理的に格納される方法が決まり、クエリーおよび DML 操作のパフォーマンスに影響を与える可能性があります。 単一のディスクページに収まる行数が多いほど、クエリーおよびインデックス検索の動作が速くなり、バッファプールに必要なキャッシュメモリーが少なくなり、更新された値を書き出すために必要な I/O が少なくなります。
各テーブルのデータは複数のページに分かれています。 各テーブルを構成するページは、B ツリーインデックスと呼ばれるツリーデータ構造で配置されます。 テーブルデータとセカンダリインデックスはどちらも、このタイプの構造を使用します。 テーブル全体を表す B ツリーインデックスは、クラスタ化されたインデックスと呼ばれます。これは、主キーカラムに従って編成されます。 クラスタ化されたインデックスデータ構造のノードには、行のすべてのカラムの値が含まれます。 セカンダリインデックス構造のノードには、インデックスカラムと主キーカラムの値が含まれます。
可変長カラムは、カラム値が B ツリーインデックスノードに格納されるというルールの例外です。 長すぎて B ツリーページに収まらない可変長カラムは、オーバーフローページと呼ばれる個別に割り当てられたディスクページに格納されます。 このようなカラムは、オフページカラムと呼ばれます。 オフページカラムの値は、オーバーフローページの単一リンクリストに格納され、このような各カラムには 1 つ以上のオーバーフローページの独自のリストがあります。 カラムの長さに応じて、可変長のカラム値のすべてまたは接頭辞が B ツリーに格納され、記憶域が無駄になり、別のページを読み取る必要がなくなります。
InnoDB
ストレージエンジンは 4 つの行フォーマットをサポートしています: REDUNDANT
, COMPACT
, DYNAMIC
および COMPRESSED
。
表 15.15 InnoDB の行フォーマットの概要
行フォーマット | コンパクトなストレージ特性 | 可変長カラム記憶域の拡張 | 大きいインデックスキー接頭辞のサポート | 圧縮のサポート | サポートされるテーブルスペースタイプ |
---|---|---|---|---|---|
REDUNDANT |
いいえ | いいえ | いいえ | いいえ | システム、file-per-table、一般 |
COMPACT |
はい | いいえ | いいえ | いいえ | システム、file-per-table、一般 |
DYNAMIC |
はい | はい | はい | いいえ | システム、file-per-table、一般 |
COMPRESSED |
はい | はい | はい | はい | file-per-table、general |
次のトピックでは、行形式の格納特性と、テーブルの行形式を定義および決定する方法について説明します。
REDUNDANT
形式は、古いバージョンの MySQL との互換性を提供します。
REDUNDANT
行フォーマットを使用するテーブルでは、可変長カラム値 (VARCHAR
、VARBINARY
、BLOB
および TEXT
タイプ) の最初の 768 バイトが B ツリーノード内のインデックスレコードに格納され、残りはオーバーフローページに格納されます。 768 バイト以上の固定長カラムは可変長カラムとしてエンコードされ、オフページに格納できます。 たとえば、utf8mb4
と同様に、文字セットの最大バイト長が 3 より大きい場合、CHAR(255)
カラムは 768 バイトを超えることがあります。
カラムの値が 768 バイト以下の場合、オーバーフローページは使用されず、値は完全に B ツリーノードに格納されるため、I/O である程度節約できます。 これは比較的短い BLOB
カラム値には適切に機能しますが、B ツリーノードがキー値ではなくデータを埋めるため、効率が低下する可能性があります。 BLOB
カラムが多数あるテーブルでは、B ツリーノードがいっぱいになりすぎて行が少なすぎるため、行が短い場合やカラム値がオフページに格納された場合よりもインデックス全体の効率が低下する可能性があります。
REDUNDANT 行形式の記憶特性
REDUNDANT
の行形式には、次のような記憶特性があります:
各インデックスレコードには、6 バイトのヘッダーが含まれています。 ヘッダーは、連続するレコードをリンクし、行レベルロックに使用されます。
クラスタ化されたインデックス内のレコードには、すべてのユーザー定義カラムのフィールドが含まれます。 さらに、6 バイトのトランザクション ID フィールドと 7 バイトのロールポインタフィールドも含まれています。
テーブルに主キーが定義されていない場合は、クラスタ化された各インデックスレコードにも 6 バイトの行 ID フィールドが含まれます。
各セカンダリインデックスレコードには、セカンダリインデックスにないクラスタ化されたインデックスキーに定義されたすべての主キーカラムが含まれます。
レコードには、そのレコードの各フィールドへのポインタが含まれます。 レコード内のフィールド長の合計が 128 バイト未満の場合はポインタが 1 バイト、128 バイト以上の場合はポインタが 2 バイトになります。 ポインタの配列はレコードディレクトリと呼ばれます。 ポインタが指す領域は、レコードのデータ部分です。
CHAR(10)
などの固定長文字カラムは、内部的に固定長形式で格納されます。 末尾の空白は、VARCHAR
カラムから切り捨てられません。768 バイト以上の固定長カラムは可変長カラムとしてエンコードされ、オフページに格納できます。 たとえば、
utf8mb4
と同様に、文字セットの最大バイト長が 3 より大きい場合、CHAR(255)
カラムは 768 バイトを超えることがあります。SQL の
NULL
値では、レコードディレクトリに 1 バイトまたは 2 バイトが予約されます。 SQLNULL
値は、可変長カラムに格納されている場合、レコードのデータ部分にゼロバイトを予約します。 固定長カラムの場合、カラムの固定長はレコードのデータ部分で予約されます。NULL
値の固定領域を予約すると、インデックスページの断片化を発生させることなく、NULL
からNULL
以外の値にカラムをインプレースで更新できます。
COMPACT
の行形式では、REDUNDANT
の行形式と比べて行の記憶領域が約 20% 削減されますが、一部の操作で CPU 使用率が増加します。 ワークロードが、キャッシュヒット率とディスク速度によって制限される通常のワークロードであれば、COMPACT
形式が高速になる可能性があります。 ワークロードが CPU 速度によって制限されている場合、圧縮形式が遅くなる可能性があります。
COMPACT
行フォーマットを使用するテーブルでは、可変長カラム値 (VARCHAR
、VARBINARY
、BLOB
および TEXT
タイプ) の最初の 768 バイトが B-tree ノード内のインデックスレコードに格納され、残りはオーバーフローページに格納されます。 768 バイト以上の固定長カラムは可変長カラムとしてエンコードされ、オフページに格納できます。 たとえば、utf8mb4
と同様に、文字セットの最大バイト長が 3 より大きい場合、CHAR(255)
カラムは 768 バイトを超えることがあります。
カラムの値が 768 バイト以下の場合、オーバーフローページは使用されず、値は完全に B ツリーノードに格納されるため、I/O である程度節約できます。 これは比較的短い BLOB
カラム値には適切に機能しますが、B ツリーノードがキー値ではなくデータを埋めるため、効率が低下する可能性があります。 BLOB
カラムが多数あるテーブルでは、B ツリーノードがいっぱいになりすぎて行が少なすぎるため、行が短い場合やカラム値がオフページに格納された場合よりもインデックス全体の効率が低下する可能性があります。
COMPACT 行形式の格納特性
COMPACT
の行形式には、次のような記憶特性があります:
各インデックスレコードには、前に可変長ヘッダーが付く可能性のある 5 バイトのヘッダーが含まれています。 ヘッダーは、連続するレコードをリンクし、行レベルロックに使用されます。
レコードヘッダーの可変長部分には、
NULL
カラムを示すビットベクトルが含まれています。NULL
にすることができるインデックス内のカラム数がN
である場合は、ビットベクトルでCEILING(
バイトが占有されます。 (たとえば、N
/8)NULL
にすることができるカラムが 9 から 16 までの任意の数だけ存在する場合は、ビットベクトルで 2 バイトが使用されます。) このベクトル内のビット以外の領域は、NULL
のカラムで占有されません。 ヘッダーの可変長部分には、可変長カラムの長さも含まれています。 各長さは、カラムの最大長に応じて、1 バイトと 2 バイトのいずれかになります。 インデックス内のすべてのカラムがNOT NULL
でかつ固定長である場合、レコードヘッダーには可変長部分が含まれません。非
NULL
可変長フィールドごとに、レコードヘッダーに 1 バイトまたは 2 バイトのカラム長が含まれます。 2 バイトが必要なのは、カラムの一部がオーバーフローページに外部的に格納されている場合、または最大長が 255 バイトを超え、実際の長さが 127 バイトを超える場合のみです。 カラムが外部に格納された場合、2 バイトの長さは、内部に格納された部分の長さに、外部に格納された部分への 20 バイトのポインタを加えた長さを示します。 内部の部分は 768 バイトであるため、長さは 768+20 になります。 20 バイトのポインタには、そのカラムの実際の長さが格納されます。レコードヘッダーの後に、
NULL
以外のカラムのデータコンテンツが続きます。クラスタ化されたインデックス内のレコードには、すべてのユーザー定義カラムのフィールドが含まれます。 さらに、6 バイトのトランザクション ID フィールドと 7 バイトのロールポインタフィールドも含まれています。
テーブルに主キーが定義されていない場合は、クラスタ化された各インデックスレコードにも 6 バイトの行 ID フィールドが含まれます。
各セカンダリインデックスレコードには、セカンダリインデックスにないクラスタ化されたインデックスキーに定義されたすべての主キーカラムが含まれます。 主キーカラムのいずれかが可変長の場合、セカンダリインデックスが固定長カラムに定義されていても、各セカンダリインデックスのレコードヘッダーには長さを記録する可変長部分があります。
-
内部的には、可変長文字セットの場合、
CHAR(10)
などの固定長文字カラムは固定長形式で格納されます。末尾の空白は、
VARCHAR
カラムから切り捨てられません。 -
内部的に、
utf8mb3
やutf8mb4
などの可変長文字セットの場合、InnoDB
は末尾の空白を切り捨ててCHAR(
をN
)N
バイトに格納しようとします。CHAR(
カラム値のバイト長がN
)N
バイトを超える場合、後続の空白はカラム値のバイト長の最小値に切り捨てられます。CHAR(
カラムの最大長は、最大文字バイト長×N
)N
です。N
の最小バイト数はCHAR(
用に予約されています。 多くの場合、N
)N
の最小領域を予約すると、インデックスページの断片化を発生させずにカラムの更新を実行できます。 比較すると、CHAR(
カラムは、N
)REDUNDANT
行形式を使用している場合、最大文字バイト長のN
を占有します。768 バイト以上の固定長カラムは可変長フィールドとしてエンコードされ、オフページに格納できます。 たとえば、
utf8mb4
と同様に、文字セットの最大バイト長が 3 より大きい場合、CHAR(255)
カラムは 768 バイトを超えることがあります。
DYNAMIC
の行形式では、COMPACT
の行形式と同じ記憶特性が提供されますが、長い可変長カラムの拡張記憶域機能が追加され、大規模なインデックスキー接頭辞がサポートされます。
ROW_FORMAT=DYNAMIC
を使用してテーブルを作成する場合、InnoDB
では、オーバーフローページへの 20 バイトポインタのみを含むクラスタインデックスレコードを使用して、長い可変長のカラム値 (VARCHAR
、VARBINARY
、BLOB
および TEXT
タイプの場合) を完全にオフページに格納できます。 768 バイト以上の固定長フィールドは、可変長フィールドとしてエンコードされます。 たとえば、utf8mb4
と同様に、文字セットの最大バイト長が 3 より大きい場合、CHAR(255)
カラムは 768 バイトを超えることがあります。
カラムがオフページに格納されるかどうかは、ページサイズおよび行の合計サイズによって異なります。 行が長すぎる場合、クラスタ化されたインデックスレコードが B-tree ページに収まるまで、最も長いカラムがオフページ記憶域として選択されます。 40 バイト以下の TEXT
および BLOB
カラムは、行に格納されます。
DYNAMIC
の行形式では、(COMPACT
および REDUNDANT
形式の場合と同様に) インデックスノードに行全体を格納する効率が維持されますが、DYNAMIC
の行形式では、B ツリーノードに大量の長いカラムのデータバイトを入力する問題が回避されます。 DYNAMIC
の行形式は、長いデータ値の一部がオフページに格納されている場合、通常は値全体をオフページに格納する方が効率的です。 DYNAMIC
形式では、B ツリーノードに短いカラムが残る可能性があるため、特定の行に必要なオーバーフローページの数が最小限に抑えられます。
DYNAMIC
の行形式では、3072 バイトまでのインデックスキー接頭辞がサポートされます。
DYNAMIC
行形式を使用するテーブルは、システムテーブルスペース、file-per-table テーブルスペースおよび一般テーブルスペースに格納できます。 DYNAMIC
テーブルをシステムテーブルスペースに格納するには、innodb_file_per_table
を無効にして通常の CREATE TABLE
ステートメントまたは ALTER TABLE
ステートメントを使用するか、CREATE TABLE
または ALTER TABLE
で TABLESPACE [=] innodb_system
テーブルオプションを使用します。 innodb_file_per_table
変数は、一般的なテーブルスペースには適用されず、TABLESPACE [=] innodb_system
テーブルオプションを使用して DYNAMIC
テーブルをシステムテーブルスペースに格納する場合にも適用されません。
DYNAMIC 行フォーマットの格納特性
DYNAMIC
の行フォーマットは、COMPACT
の行フォーマットのバリエーションです。 記憶特性については、COMPACT 行形式の格納特性 を参照してください。
COMPRESSED
の行形式は、DYNAMIC
の行形式と同じ記憶特性および機能を提供しますが、テーブルおよびインデックスのデータ圧縮のサポートが追加されています。
COMPRESSED
行フォーマットは、オフページストレージに関して DYNAMIC
行フォーマットと同様の内部の詳細を使用するほか、追加のストレージ、圧縮されるテーブルおよびインデックスデータからのパフォーマンスの考慮事項、および小さいページサイズを使用します。 COMPRESSED
の行形式では、KEY_BLOCK_SIZE
オプションによって、クラスタインデックスに格納されるカラムデータの量およびオーバーフローページに配置される量が制御されます。 COMPRESSED
の行形式の詳細は、セクション15.9「InnoDB のテーブルおよびページの圧縮」 を参照してください。
COMPRESSED
の行形式では、3072 バイトまでのインデックスキー接頭辞がサポートされます。
COMPRESSED
行形式を使用するテーブルは、file-per-table テーブルスペースまたは一般テーブルスペースに作成できます。 システムテーブルスペースは、COMPRESSED
の行形式をサポートしていません。 file-per-table テーブルスペースに COMPRESSED
テーブルを格納するには、innodb_file_per_table
変数を有効にする必要があります。 innodb_file_per_table
変数は、一般的なテーブルスペースには適用できません。 一般テーブルスペースでは、物理ページサイズが異なるために圧縮テーブルと非圧縮テーブルを同じ一般テーブルスペースに共存できないという注意事項を含むすべての行形式がサポートされています。 詳細は、セクション15.6.3.3「一般テーブルスペース」を参照してください。
圧縮された行形式の格納特性
COMPRESSED
の行フォーマットは、COMPACT
の行フォーマットのバリエーションです。 記憶特性については、COMPACT 行形式の格納特性 を参照してください。
InnoDB
テーブルのデフォルトの行形式は、DYNAMIC
のデフォルト値を持つ innodb_default_row_format
変数によって定義されます。 デフォルトの行フォーマットは、ROW_FORMAT
テーブルオプションが明示的に定義されていない場合、または ROW_FORMAT=DEFAULT
が指定されている場合に使用されます。
テーブルの行形式は、CREATE TABLE
ステートメントまたは ALTER TABLE
ステートメントの ROW_FORMAT
テーブルオプションを使用して明示的に定義できます。 例:
CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;
明示的に定義された ROW_FORMAT
設定は、デフォルトの行フォーマットをオーバーライドします。 ROW_FORMAT=DEFAULT
を指定することは、暗黙のデフォルトを使用することと同じです。
innodb_default_row_format
変数は動的に設定できます:
mysql> SET GLOBAL innodb_default_row_format=DYNAMIC;
有効な innodb_default_row_format
オプションには、DYNAMIC
、COMPACT
および REDUNDANT
があります。 システムテーブルスペースでの使用がサポートされていない COMPRESSED
行形式は、デフォルトとして定義できません。 これは、CREATE TABLE
ステートメントまたは ALTER TABLE
ステートメントでのみ明示的に指定できます。 innodb_default_row_format
変数を COMPRESSED
に設定しようとすると、エラーが返されます:
mysql> SET GLOBAL innodb_default_row_format=COMPRESSED;
ERROR 1231 (42000): Variable 'innodb_default_row_format'
can't be set to the value of 'COMPRESSED'
新しく作成されたテーブルでは、ROW_FORMAT
オプションが明示的に指定されていない場合、または ROW_FORMAT=DEFAULT
が使用されている場合に、innodb_default_row_format
変数で定義された行形式が使用されます。 たとえば、次の CREATE TABLE
ステートメントでは、innodb_default_row_format
変数で定義された行形式が使用されます。
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;
ROW_FORMAT
オプションが明示的に指定されていない場合、または ROW_FORMAT=DEFAULT
が使用されている場合、テーブルを再構築する操作によって、テーブルの行形式が innodb_default_row_format
変数で定義された形式に暗黙的に変更されます。
テーブルの再構築操作には、テーブルの再構築が必要な ALGORITHM=COPY
または ALGORITHM=INPLACE
を使用する ALTER TABLE
操作が含まれます。 詳しくはセクション15.12.1「オンライン DDL 操作」をご覧ください。 OPTIMIZE TABLE
は、テーブルの再構築操作でもあります。
次の例は、明示的に定義された行形式なしで作成されたテーブルの行形式を暗黙的に変更するテーブル再構築操作を示しています。
mysql> SELECT @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic |
+-----------------------------+
mysql> CREATE TABLE t1 (c1 INT);
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 54
NAME: test/t1
FLAG: 33
N_COLS: 4
SPACE: 35
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
mysql> SET GLOBAL innodb_default_row_format=COMPACT;
mysql> ALTER TABLE t1 ADD COLUMN (c2 INT);
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 55
NAME: test/t1
FLAG: 1
N_COLS: 5
SPACE: 36
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
既存のテーブルの行形式を REDUNDANT
または COMPACT
から DYNAMIC
に変更する前に、次の潜在的な問題を考慮してください。
-
REDUNDANT
およびCOMPACT
の行形式では 767 バイトのインデックスキー接頭辞の最大長がサポートされますが、DYNAMIC
およびCOMPRESSED
の行形式では 3072 バイトのインデックスキー接頭辞の長さがサポートされます。 レプリケーション環境では、ソースでinnodb_default_row_format
変数がDYNAMIC
に設定され、レプリカでCOMPACT
に設定されている場合、行形式を明示的に定義しない次の DDL ステートメントはソースで成功しますが、レプリカでは失敗します:CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(5000), KEY i1(c2(3070)));
関連情報については、セクション15.22「InnoDB の制限」を参照してください。
行フォーマットを明示的に定義しないテーブルをインポートすると、ソースサーバーの
innodb_default_row_format
設定が宛先サーバーの設定と異なる場合にスキーマの不一致エラーが発生します。 詳細は、セクション15.6.1.3「InnoDB テーブルのインポート」を参照してください。
テーブルの行形式を確認するには、SHOW TABLE STATUS
を使用します:
mysql> SHOW TABLE STATUS IN test1\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2016-09-14 16:29:38
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
または、INFORMATION_SCHEMA.INNODB_TABLES
テーブルをクエリーします:
mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test1/t1';
+----------+------------+
| NAME | ROW_FORMAT |
+----------+------------+
| test1/t1 | Dynamic |
+----------+------------+