[MySQL] InnoDBにおける各データ型のサイズまとめ

始めに

MySQLのスキーマ設計においてデータ型ごとにストレージ上のサイズを考慮することはディスク容量の最適化の上で重要です。
この記事では各データ型ごとのサイズについてご紹介します。

1. 数値型のサイズ

データ型	サイズ
TINYINT	1バイト
SMALLINT	2バイト
MEDIUMINT	3バイト
INT	4バイト
BIGINT	8バイト
FLOAT(p)	0<=p<=24の場合は4バイト、25<=p<=53の場合は8バイト
FLOAT	4バイト
DOUBLE	8バイト
DECIMAL(M, D)	下記を参照
BIT(M)	(M+7)/8を丸めたバイト数

Decimalのデータサイズ計算について

整数部と小数部は分けて格納されます。 整数部、小数部ともに９桁ごとに４バイト消費されます。桁数が９で割り切れない場合は以下の表に従って追加のバイト数が決まります。

たとえば、DECIMAL(18,9)の場合は小数点の両側に9桁あるため、整数部と小数部のそれぞれに4バイトが必要となり、計8バイト消費されます。DECIMAL(20,6)の場合は整数部に14桁、小数部に6桁あります。 整数部の桁は、そのうちの9桁に4バイト、残りの5桁に3バイトが必要です。小数部の6桁には3バイトが必要であり、計１０バイト消費されます。

余りの桁	バイト数
0	0
1-2	1
3-4	2
5-6	3
7-9	4

Bitのデータサイズ計算について

Bit(M)はMビットを表現するデータ型です。実際のデータ自体はMビットですが、MySQLのデータ型のサイズはバイト単位であるため、(M+7)/8を整数値に丸めたバイト数がBit(M)のデータサイズとなります。

2. 日時型のサイズ

MySQL5.6.4以降ではTIME、DATETIME、TIMESTAMPがコンマ秒の時間を格納できるようになったため、それ以前のバージョンとストレージ上のサイズが異なります。

データ型	MySQL 5.6.4 より前で必要なストレージ	MySQL 5.6.4 以降で必要なストレージ
YEAR	1バイト	1バイト
DATE	3バイト	3バイト
TIME	3バイト	3バイト+コンマ秒用のストレージ
DATETIME	8バイト	5バイト+コンマ秒用のストレージ
TIMESTAMP	4バイト	4バイト+コンマ秒用のストレージ

• コンマ秒用の追加ストレージ

TIME、DATETIME、TIMESTAMPのコンマ秒はマイクロ秒(小数第6位)までの精度を指定でき、指定した精度に応じて必要なストレージが変わります。

コンマ秒精度	必要なストレージ
0	0バイト
1-2	1バイト
3-4	2バイト
5-6	3バイト

2. 文字列型のサイズ

以下、Mは定義されたカラムの長さ, Lは格納された文字列の実際の長さを表すとします。

可変長文字列のサイズ計算について

可変長文字列はデータの先頭に可変長文字列の文字列長を格納し、実際のデータが後につづきます。
文字列長が$2^8$未満であれば１バイトで長さを表現できるため、L+1バイトのサイズとなります。
同様に文字列長が$2^{8n}$未満であればnバイトで長さを表現でき、L+nバイトのサイズとなります。
以下はhelloという文字列をVARCHARに格納したときのストレージを図解したものです。長方形１つで１バイトを表します。

文字セットを指定できる型のサイズ

文字セットを指定できる型（CHAR, VARCHAR, TEXTなど）のサイズは使用している文字セットによって異なります。ここでは各文字のバイト数が一定(wバイトとする)と仮定します。

データ型	サイズ
CHAR(M)	M*wバイト
VARCHAR(M)	M*w < 255なら、M*w + 1バイト、M*w >= 255 バイトならM*w + 2バイト
TINYTEXT	L*w + 1バイト ただしL*w < $2^8$
TEXT	L*w + 2バイト　ただしL*w < $2^{16}$
MEDIUMTEXT	L*w + 3バイト　ただしL*w < $2^{24}$
LONGTEXT	L*w + 4バイト　ただしL*w < $2^{32}$

文字セットを指定しない型のサイズ

データ型	サイズ
BINARY(M)	Mバイト
VARBINARY(M)	M < 255なら、M + 1バイト、M >= 255 バイトならM + 2バイト
TINYBLOB	L + 1バイト ただしL < $2^8$
BLOB	L + 2バイト ただしL < $2^{16}$
MEDIUMBLOB	L + 3バイト ただしL < $2^{24}$
LONGBLOB	L + 4バイト ただしL < $2^{32}$
ENUM('value1','value2',...)	列挙値の数 (最大 65,535 個の値) により1または2バイト
SET('value1','value2',...)	集合のメンバー数 (最大 64 メンバー) により、1、2、3、4、または 8 バイト

ENUMのデータサイズ計算について

ENUMでは列挙値に対して1から順に整数値を割り当てます。そのため、列挙値の数が255以下であれば１バイトにおさまります。255以上の場合は2バイトが必要となります。

SETのデータサイズ計算について

SETでは各メンバーに対してビットフラグにより集合内に含まれるかを表現します。そのためメンバー数分のビット数（Ｎとする）が必要になります。
MySQLのデータ型サイズはバイト単位で決定されるため、(N+7)/8を整数値に丸めたバイト数がSETのサイズとなります。

参考資料

https://dev.mysql.com/doc/refman/8.0/ja/storage-requirements.html#data-types-storage-reqs-strings

関連記事リンク

MySQLの整数値型　詳解
MySQLの小数値型　詳解
MySQLの文字列型　CHAR、VARCHAR　詳解
MySQLのバイナリデータ型 BINARY, VARBINARY　詳解
MySQLのTEXT、BLOB　詳解
MySQLのENUM型　詳解
MySQLの時間データ型　詳解
MySQLのJSON型　詳解
MySQL - innodbにおける各データ型のサイズまとめ