実践ハイパフォーマンスMySQLを読んで考えたことをまとめました。
通常は小さい方がいい
当たり前だがデータ型が小さい方がパフォーマンスが良くなるので、なるべく小さいデータ型を使うよううにする。
しかし、この時にデータ型の大きさを過小評価すると、あとでデータ型の範囲を広げる必要が出てくる。
単純なものがいい
データ型が単純であるほど、操作に時間がかからない。
例えば、日付と時刻は文字列ではなくMySQLの組み込み型として格納すべき。
IPアドレスは文字列でなく整数型で格納すべき。(.は人間が認識しやすいようにつけられているだけだから)
できればNULLを使用しない
NULL値を設定できる列はインデックス、インデックス統計、値の比較を複雑にするので、それらを参照するクエリをMySQLで最適化するのは難しい。NULL値を格納できる列はより多くの記憶域を使用し、内部でも特殊な処理を要求するから。
ただし通常NULL値を格納できる列をNOT NULLにしてもパフォーマンスの向上はわずかなので、列にインデックスをつける予定がある時以外は優先しなくていい。
ここのリンクにも書いてある通り、NOT NULLを使わずに例えば''を使うことはSQLアンチパターンとして知られているので、慎重に考える必要がありそう。
NOT NULLを使わずに''とかでNULLを表現すると''がNULLであることを開発者が覚えておく必要があるので、生産性を下げる原因になりうる。
整数
整数型には
- TINYINT
- SMALLINT
- MEDIUMINT
- INT
- BIGINT
がある。
整数型はUNSIGNED属性を持つことで、格納できる正の数の上限を約2倍にすることができる。
実数
実数とは少数部分を持つ数字のこと。
BIGINTに収まらない大きな整数を格納するためにDECIMALを使うこともできる。
不正確な型
- FLOAT
- DOUBLE
どちらも標準の浮動小数点演算の近似計算をサポートする。
それぞれの違いについては
https://www.cc.kyoto-su.ac.jp/~yamada/programming/float.html
に詳しく書いている。
正確な型
DECIMAL型
記憶域と計算のコストが増えるため、財務データを格納するなど少数部分の正確な結果が必要な時だけにする
文字列型
VARCHAR型
可変長の文字列を格納する最も一般的なデータ型。
必要な記憶域しか使用しないので、固定長の型ほど多くの記憶域を使用しない。
格納する値が短ければ短いほど使用する記憶域も小さい。
VARCHAR型は値の長さを保存するために1か2バイトを使用する。
CHAR型
固定長の文字列データ型。
nバイトに満たない場合は文字列の右側に空白が追加され、10バイトぴったりに調整される。
BLOB型とTEXT型
BLOB型は大量のバイナリデータを格納する。
TEXT型は大量の文字列データを保存する。
他のすべてのデータ型と異なり、MySQLはBLOB型とTEXT型の値を独自のIDを持つオブジェクトとして処理する。
VARCHAR型とTEXT型どちらを使うべき？
ここのサイトに詳しく書いてある。
ストレージエンジンごとに違いはあるが、TEXT型は外部記憶領域を使ってDBにはそのポインタが格納される。
一方VARCHAR型は値そのものをDB内に保存するためアクセス速度が早い。
基本的にはVARCHAR型を使った方がいい。
日付と時刻型
DATETIME
1001~9999年までの値を格納することができる。精度は1秒。YYYYMMDDHHMMSS形式で整数にアンパックする。これには8バイトの記憶域が使用される。
TIMESTAMP型
1970年1月1日午前0時からの経過時間を秒数で格納する。記憶域を4バイトしか使用しないのでDATETIME型より範囲はずっと狭い（1970年から2038年の途中まで）
他のデータ型と違いTIMESTAMP型の列はデフォルトでNOT NULL。
どちらを使うべき？
記憶域が小さいことを考えると基本的にTIMESTAMPだが2038年問題や特集な挙動があるようなので注意が必要。