0
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

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

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

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

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

ファイルフォーマット

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

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

行フォーマット

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

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

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

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

行の最大サイズ

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

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

フィールドの文字コードをutf8mb4にすると、文字列あたりの最大byte数が4byteになるので、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 使用されているファイル形式の識別

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
0
Help us understand the problem. What are the problem?