InnoDBのファイルフォーマット、行フォーマットについてまとめ(Mysql5.6の話)

以前調べてまとめた内容になります。

mysql５．６時代にまとめているので、現在の最新バージョンと違う箇所もあるかもしれないので注意してください
間違いがあったらご指摘いただけるとありがたいです

InnoDBではテーブル作成時に指定されるファイル形式と行フォーマットによって、
データ格納時のストレージの使用率が変動する。

ファイルフォーマット

・Antelope
	MySQL 5.6までのデフォルトファイルフォーマットで、可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)
	の先頭768バイトを B-tree ノードのインデックスレコードに格納し、残りをオーバーフローページに格納する。

・Barracuda
	可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の全てを外部のオーバーフローページに格納し、
	クラスタインデックスレコードにそのページへのポインタ(20B)のみを格納する。
	所謂８kbの壁の中で、Antelope以上に多くのカラムを指定することができるようになる。
	また、Barracudaにしても行フォーマットでCOMPACTなどを選択していると、
	そのテーブルはAntelopeのファイル形式が適用される

行フォーマット

・REDUNDANT
	古いフォーマットでchar系での消費ストレージは指定した文字数×charasetの最大サイズになる
	char(8)のutf-8で指定すれば8*3=24が固定で消費されるということ

・COMPACT
	UTF-8 文字列の最適化などにより REDUNDANT よりもデータサイズが小さくなる。
	REDUNDANTと違い消費されるストレージは入力された文字のバイト数が計算される。
	つまりアルファベットが入力されれば１バイトしか消費されない。

・DYNAMIC
	Barracudaでないと指定できない。
	上記したクラスタインデックスレコードにそのページへのポインタ(20B)のみを格納することで、
	8kbの壁を超えて(正確には実際に超えている訳ではなく、２０Bだけインデックスレコードに登録することで、
	データの保存量を増やす)データを登録出来るようになる。
	Barracudaを選択してもDYNAMICかCOMPRESSEDを使用しないとこれは使えない。
	またDYNAMICの場合はデータ容量が少ない場合はオフページではなくBツリーノードに残る可能性がある。
	データの圧縮を行わない。

・COMPRESSED
	Barracudaでないと指定できない。DYNAMICと同じ仕様を持ちさらにデータの圧縮を行う。
	圧縮を行うことで、I/Oが軽減され、スループットが改善される。
	ただし、CPUの使用率が増加するという問題があるので、サーバーのスペック次第というところもある。
	また、圧縮機能はメモリが多く搭載されている場合で読み込み負荷の高いアプリケーションを実行する場合特に有効

行の最大サイズ

InnoDBには１行あたりインラインに登録できるデータは8kbまでという決まりがあるが、
そもそも指定したフィールドのデータ型のサイズの合計が65535バイトを超えてはならないという決まりがある。これはvarcharなどで文字列を指定した場合に適用される。text型などはこの範疇には入らない。
なので、想定文字数が多い場合はvarcharではなく、text型にすべき

Index column size too large. The maximum column size is 767 bytesについて

フィールドの文字コードをutf8mb4にすると、文字列あたりの最大byte数が４byteになるので、utf8では指定できたindexがサイズオーバーで登録出来ない場合ある。indexの最大サイズはデフォルトで767であるが、行フォーマットをDYNAMICかCOMPRESSEDに変更することで、innodb_large_prefixオプションでindexのサイズを最大3072byteまで拡張できる。

以下参考サイト
InnoDBの制限とファイルフォーマットAntelopeとBarracudaの違い - かみぽわーる
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13.7 物理的な行構造
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.8.3 使用されているファイル形式の識別