MySQLのプライマリーキー指定
MySQL以外でもほとんどのRDBシステムが行の一意性の確保のためにプライマリーキーの指定を強く推奨しています.
多くの場合,サロゲートキーとしてidカラムが作成されたり,論理的に一意性が保証できるキー(の集合)をプライマリーキーにします.
ではこのプライマリーキーの型指定はどうなっているでしょうか.
稀にですが,文字列型(char
やvarchar
)を含むプライマリーキーを見かけることがあります.十分に検討された上での選択なら仕方がないですが,余程のことがない限りこれらは避けるべきです.
MySQLのプライマリーキーに文字列型を使用すべきでない3つの理由
一意性の確保が面倒
MySQLはauto_increment
を付けることで,データの挿入時に必ず現在入ってるどのレコードのIDよりも大きな値をなるべく連番になるよう自動で割り振ってくれます.
auto_increment
はMySQLの再起動時に初期化されることでバグが生じる,という話がありますが外部キー制約を適切に貼っておけば問題ありません.
アプリケーション側で一意性のある文字列を生成するのは面倒ですしあまり意味がありません.
時系列が不明
よくレコードの生成された時間を知るためにcreated
のようなカラムをつけDEFAULT CURRENT_TIMESTAMP
を指定することがあります.このカラムを使えば「レコードの挿入された時系列」を知ることは容易です.
ですが,idをauto_increment
にしていると時系列情報をidから辿れます.ソートする必要もなくseq_scanが可能です.
idを文字列にするとうまくやらない限りrandom I/Oが発生します.
プライマリーキーはサイズが小さい方が良い
これが最大の理由です.
MySQLはインデックスを貼る際にB+木を作成します.プライマリーインデックスならばリーフノードには実データが格納され,セカンダリインデックスならリーフノードにはプライマリーキーのみが保存されます.
つまり,プライマリーインデックス以外のインデックスを使用する際は,B+木を利用してリーフノードのプライマリーキーを取得し,プライマリーインデックスを引き直して実データを取ってきます(実行計画にUsing index
と出る場合はプライマリーインデックスを引き直さないこともあります).
なので,もしプライマリーキーのサイズが大きいと,インデックスはどんどん肥大していきます.
MySQLのint型は4Bytesですが,文字列はutf-8なら1文字で1~4Bytes持って行きます.varchar
なら文字列長を格納するためにさらに1~2Bytes持って行きます.
また,idカラムは他のテーブルに外部キーとして使用される場合もあります.「このテーブルはサイズが大きくないからセカンダリインデックスの肥大化くらい気にしないよ」と思っていても,将来巨大なテーブルの外部キーに使用されれば,それは無視できないサイズになります.
たまにint
型じゃ行が溢れちゃうよ!という声を聞きます.
unsigned bigint
で最大で18446744073709551616件保存できます.サイズは8Bytesです.
ログテーブル以外はunsigned int
の4294967296件で足りる気もしますが...
まとめ
サロゲートキーとしてのidカラムには可能な限りunsigned int
とauto_increment
を使用しましょう.
もしcharやvarchar(textは論外です)がプライマリーキーに入るようならそれらはuniqueキーにしてサロゲートキーとしてidカラムを付けたほうがよくないか検討しよう.