MySQLのChar型のデータサイズについて小ネタ
MySQL Casual Advent Calendar 2016 - Qiita の 18日目です。
がんばった。もう小ネタしか出てこない。
MySQLのchar型宣言時の数字は「データサイズ」ではなくて、「文字数」
っていうのは、ワリと有名。
Oracleからやってくると戸惑うところではあるけど、ワリとすぐに慣れるよね。
NCHAR使ってた人ならば、そんなに違和感ないだろうし。
MySQLのchar型で消費するストレージは、必ずしも文字コードベース×文字数「ではない」
char(10)な列を宣言した時、latin1だから1Byte×10、SJISだから2Byte×10、utf8だから3Byte×10、utf8mb4だから4byte×10、と、「かならずしもなるわけではない」のです。
「かならずしも」がポイント。
話をInnoDBに絞ります
こちとらMySQL歴やっと1年、デフォルトがInnoDBになってからのことしか知らないっす。
MyISAM？そんな見捨てられた子のことは知らないっす。
トランザクションが使えないRDBMS使うとか、考えたことも無いっす（言い過ぎ
ちゃんとマニュアルに書いてある
InnoDB は、後続領域を切り取ることで内部的に UTF-8 CHAR(N) カラムを N バイトで格納しようとします。(REDUNDANT 行フォーマットでは、このようなカラムで 3 × N バイトが占有されます。)多くの場合は、最小領域 N を予約すると、インデックスページの断片化が発生せずに、カラムの更新を正常に実行できます。
http://dev.mysql.com/doc/refman/5.6/ja/innodb-physical-record.html
ここでOracleから来た人には馴染みのない、行フォーマットとファイルフォーマットの話が出てきますが、ここは先人達が散々語っている話なので、さらっと触れるだけで。
ファイルフォーマットと行フォーマット（ROW_FORMAT）の組み合わせで、物理ファイルに対するデータ各方法方が4通り指定できます。
ファイルフォーマットに対して、選択可能な行フォーマットが決まっているので、その対応表を。
| ▼FILE_FORMAT | REDUNDANT | COMPACT | DYNAMIC | COMPRESSED |
|---|---|---|---|---|
| Antelope | ● | ● | × | × |
| Barracuda | ● | ● | ● | ● |
ROW_FORMATの超概略。詳細はマニュアル読んでね。
| REDUNDANT | 昔のフォーマット。charをフル桁で領域確保する |
|---|---|
| COMPACT | 最近のデフォルト。charの領域確保で節約しようとする |
| DYNAMIC | 最近のフォーマット。5.7.9からデフォルト。可変長文字列の格納方法変更し、テーブルあたりの格納サイズ上限を緩和。 |
| COMPRESSED | 最近のフォーマット。圧縮するので格納効率上がるが、圧縮解凍コスト高いので人によってはクソ扱い |
で、上で引用したマニュアルの一文は、ファイルフォーマットが「COMPACT」「DYNAMIC」の時に発生します。
具体的に、どう格納しているか。
ibdファイルをバイナリエディタで見るのが手っ取り早いです。
innodb_file_per_table=1で、utf8mb4で作成したテーブル/カラムに、幾つかのパターンでデータをInsertした結果です。
こんな感じで格納されます。
投入した文字の文字コードは以下のようになります。
| あ | E38182 |
| い | E38184 |
| う | E38186 |
| A | 41 |
| B | 42 |
| C | 43 |
| 空白 | 20 |
1(左上)：CHRA(10)に対し、英字1文字INSERT
英字1Byte×1文字＋空白9Byte、計10Byteで生成。
2（右上）：CHRA(10)に対し、英字10文字INSERT
英字1Byte×10文字、計10Byteで生成。
3（左下）：CHRA(10)に対し、ひらがな1文字INSERT
ひらがな3Byte×1文字＋空白7Byte、計10Byteで生成。
4（右下）：CHRA(10)に対し、ひらがな10文字INSERT
ひらがな3Byte×10文字、計30Byteで生成。
例ではUTF8での3Byte文字までしかテストしてませんが、CHARACTER SETがutf8mb4を指定してあっても、char(10)でリアルに40Byte消費するのは、4Byte文字を10個ぶち込んだときだけです。だからといって、40Byte以内ならば4Byte未満の文字をたくさん詰め込めるかといったら、それはもちろんダメ。
寿司ビール問題やら何やらで、文字コードはとりあえずutf8mb4にしとけ！って感じですが、必要ストレージサイズ計算の時、全部4Byte計算するととんでもないサイズが必要ってことになってしまいます。
実際には4Byte文字を格納する比率って、業務アプリだとほぼ皆無、SNS的なライトなコミュニケーション機能を付けない限り、ほとんど格納されないでしょうから、ストレージ調達の見積りにおいてはざっくり削れる所かと思います。
なお、1テーブルあたりのサイズ上限チェックは、DEFAULT CHARSETのmaxlen×文字数で計算されて、上限を超えている場合にはエラーになります。*1
DYNAMICのときはほとんど気にしなくて良いと思いますが、COMPACTのときは、列数の多いテーブルを組むときは別で計算したほうが良いですね。
