MySQLでのデータ型とストレージスペースの関係

得に指定がない場合はMySQL5のMyIsamとInnoDBについて説明しています。

数値型

整数

- TINYINT, SMALLINT, MEDIUMINT, INT, BIGINTはそれぞれ1,2,3,4,5byteの長さ。
- 固定長。上記した型によって型が決定され、行の値に関係なく固定長。
- （多分だけど）実際にストレージされる長さ = byte長 + 1 bit(正負を表すためのbit)
- UNSINED属性で正の数だけを扱うことができる。TINYINT = -128 to 127, TINYINT UNSINED = 0 to 255。ストレージスペースとパフォーマンスは変わらない。
- 上記のinteger型全てが64bitのBIGINTとして計算される。
- INT(1)は表示上の制限でしかない。長さを指定するのはMySQLのツールが表示の目的のための文字を指定しているだけ。ストレージと計算の目的においてはINT(1)とINT(20)は同一
- Falconの場合はストレージスペースの容量の指定ができない。
- INTEGER, BOOL, NUMERICはエイリアス。パフォーマンスに影響しない

実数

- FLOAT, DOUBLEはそれぞれ 4, 8 byte
- DECIMALは可変です。最小 1byte - 最大 33bytes
必要になるbyte数は小数点の左の桁数と右の桁数によって別々に計算されます。各端のbyte数は９桁毎に４byte必要になります。余りに対して必要になるbyte数が以下のテーブルによって決定されます。

桁数　byte数
1-2 1
3-4 2
5-6 3
7-8 4

例、DECIMAL(20, 10)
DECIMAL(M, D)はMが全体の桁数、Dが小数点以下の桁数を表します。Mは最大65, Dは最大30です。

小数点の左が１０桁、右が１０桁です。各端にフォーカスします。9桁のために4byteが使用され、余りの1桁のために1byteが使用されます。そのため各端に必要なbyte数は5byteです。

DECIMAL(M, D)
- 最小必要容量
Mの最小が1、Dが0。小数点を表すbyteが必要ないとして、最小必要容量は1byte

- 最大必要容量
Mの最大が65。65を9で割った余りが2。左：右を 63:2とするより 62:3とする方が消費される容量が大きい。62 = 9*6 + 8。最大必要容量は 左＝6*4 + 4 右＝4 小数点＝1 の合計の33byte。

文字列型

VARCHAR

- MyIsam
- ROW_FORMAT=DYNAMICの場合
- 一つの行が断片化して保存される。検索に時間がかかることになるので、頻繁に更新があるテーブルには適さない。OPTIMIZE TABLEでデフラグできるがあまり実用的ではない。
- データファイルポインタのサイズが可変である。MyIsamはインデクスの中に行の場所を示すポインタがあるが、行を見つけるためにbyte長を使用する。大きなテーブルの場合、ポインタが大きくなるのでオーバーヘッドが発生する。
- テーブルが破損した場合修復に失敗する場合がある。ある行が更新に失敗して破損した場合、byte長で制御されているので後続の行のポインタが正確な場所を示さなくなり修復に失敗する可能性がある。

- ROW_FORMAT=FIXEDの場合
- 行が断片化しない。行が断片化しない。
- データファイルポインタのサイズが固定である。行を見つけるために行番号を使用するため検索が早い。
- テーブルが破損しても修復が容易全ての行が同じサイズのため、最悪破損した行のみが修復不可能になる。

CHAR

- CHAR(M)。Mは文字列で0から255。最大は4バイト文字（Mysql 5.5.4から）を使った1020byte
- 固定長で保存される
- 文字数がCHAR(M)のMより少ない場合はMになるまで空白文字でパッドされる
- 検索時に後続スペースは削除される
- けれどもメモリ上ではしばしば固定長のメモリが割り当てられる。メモリ内一時テーブルを使ったソートに悪影響。ディスク上でのファイルソートでも同じことが起きる。
- つまるところ、CHAR(5)とCHAR(100)に同じ長さの文字列を格納した場合、消費されるストレージスペースは同じだが、検索時にCHAR(5)の方が検索がずっと早くなることがある。

BLOBとTEXT

TEXT

- TINYTEXT, SMALLTEXT/TEXT(シノニム), MEDIUMTEXT, LONGTEXTはそれぞれ最長で255, 65535, 65535, 4GB

BLOB

- TINYBLOB, SMALLBLOB/BLOB(シノニム), MEDIUMBLOB, LONGBLOBはそれぞれ最長で255, 65535, 65535, 4GB
- 先頭の768bytesまでが通常の行と同じ領域に格納される
- InnoDBには1行あたりおよそ8KBまでというサイズ制限があるためひとつのテーブルにBLOBカラムは10個までしか作成することが出来ない

ENUM

- 列挙値を最大65535個まで定義できる
- 列挙値255個までが1byte。列挙値65535個までが2bytes
- 列挙値を格納するテーブルと、表示用の値を格納するテーブルがある
- 必ず結合が発生するので検索時にオーバーヘッドがあるが、大概はサイズが小さいことにより解決される
- CHAR,VACHARとENUMをJOINするのはCHAR,VACHARをCHAR, VARCHARにJOINするより時間がかかる
- 但しCHAR, VARCHARを使用するより大きくストレージスペースを少なくできるので結果的に処理が早い

DATEとTIME

DATETIME

- 8bytes固定長

TIMESTAMP

- 4bytes固定長
- January, 1, 1970(Greenwich Mean Time)からの時間の経過を格納しているためストレージスペースが小さい
- unix_timestamp()でユニクスタイムスタンプと同じ形式で取得可能

> select unix_timestamp(a) from t;
+-------------------+
| unix_timestamp(a) |
+-------------------+
|         315532800 | 
+-------------------+
1 row in set (0.00 sec)

Bit-Packed

BIT

- ストレージエンジンによって必要なストレージスペースが異なる
- 最大64bitsまで定義できる
- MyIsamは全てのbitを一つのカラムに格納する
- MemoryとInnoDBは定義されたビット数を表現するのに必要なだけのintegerを代わりに使用する
- MySQLはBITを以下の様に文字列として扱ってしまい混乱の種になる。そのためあまり使用が推奨されない。

mysql> CREATE TABLE bittest(a bit(8));
mysql> INSERT INTO bittest VALUES(b'00111001');
mysql> SELECT a, a + 0 FROM bittest;
+------+-------+
| a | a + 0 |
+------+-------+
| 9 | 57 |
+------+-------+

aの値9は57をASCII文字コードで変換したものである。

もし、 true/falseを1ビットのストレージスペースに格納したい場合はNULLを許容するCHAR(0)カラムを使うことができる。このカラムは、NULLか0長値（空文字）を扱うことができる。

SET

- ラベル付きのビット演算（ACLなど）に便利
- ALTER TABLEをラージテーブルに対して行うとexpensive

mysql> CREATE TABLE acl (
-> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
-> );
mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');
mysql> SELECT perms FROM acl WHERE FIND_IN_SET('CAN_READ', perms);
+---------------------+
| perms |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+