なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

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した結果です。

f:id:atsuizo:20161212184623p:plain

こんな感じで格納されます。

投入した文字の文字コードは以下のようになります。

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のときは、列数の多いテーブルを組むときは別で計算したほうが良いですね。


まとめ

MySQLInnoDB)は物理格納フォーマットの指定パラメータがあり、バージョンによってデフォルトが違う
・最近のデフォルトだと、必要領域はcharは宣言サイズ×キャラクタセットの文字サイズではない
・調達ストレージサイズ見積りの時、DEFAULT CHARSETのmaxlen×文字数で積み上げた数字だと確実に余るよ。