MySQL における primary key について
MySQL における primary key （プライマリキー、主キーとも呼ばれる）は、テーブルの中のカラムに対して指定できるもので、以下のような特徴を持ちます
- テーブルで一つのみ指定できる
- NULLは許容しない
- 一意の（ユニークな） ID を付与する
- インデックスが自動で構築される
インデックス構造の基礎知識についてはこちらの記事が非常にわかりやかったのでぜひ参考にしてみてください
MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ
primary key として何を利用するか？
ここで議論となるのは、primary key として何を利用するかという点です。
AUTO_INCREMENT
よくあるのは、DB側の機能として AUTO_INCREMENT を利用し、DB側で採番するという方法です。
メリットとしては以下のようなものが挙げられます。
- アプリケーション側の実装が不要
- 連番なので必ずキーが被ることはない
- 連番なのでインデックス構築の効率が良い
上記の利点からよく採用される AUTO_INCREMENT ですが、以下のようなデメリットもあります。
- DBに書き込まれるまで primary key が定まらず、アプリケーション側では一度書き込んだ後の値を使用する必要がある
- 複数DBで採番すると ID が重複するため、DBの台数を増やすことができない
採番テーブル
次によくあるのは、採番テーブルを用意し、そこで連番を管理する方法だと思います。こちらではDBにデータを書き込む前に primary key を取得するため、 AUTO_INCREMENT におけるデメリットの1を解決できます。
一方で、DBの台数を増やすことができないデメリットは残しますし、primary key を取得する際に毎回採番テーブルへのIOが発生する点は新しいデメリットです。
UUID
今までのように DB側で primary key を使うのではなく、アプリケーション側で一意な primary key を生成する方法もあります。
その場合はこれまでのように primary key を生成する処理をDBに依存する必要がないのが利点です。
一意な ID と聞くと、UUID が候補として挙げられます。ここではその中でも UUID version1 を取り上げます。UUID を使う場合はパフォーマンス面での懸念があります。次の記事で非常に丁寧に解説されているので読んでみてください。
MySQLでプライマリキーをUUIDにする前に知っておいて欲しいこと | Raccoon Tech Blog
自分の言葉でまとめると、こんな感じになります。
- インサート時、読み込み時ともに、UUID のうちランダムな部分が原因となり、インデックス構築時にクラスタインデックス（B-treeの発展版なようなものという理解）の全リーフのインデックスを読み込む可能性がある。
- 前提として、UUID を生成する際にタイムスタンプが参照されるが、タイムスタンプを表すビットの中で、上位ビットと下位ビットを入れ替えたりするため、時系列でシーケンシャルな値になっていない。
- もしシーケンシャルであれば、近い値のインデックスは場所的にも近いので、一部分だけ読み込めば良い。キャッシュにもヒットする可能性が高い。
UUID を使う場合でも、MySQL の uuid_to_bin という関数を使えば、パフォーマンス問題が解決するようです。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.24 その他の関数
まず、UUID を16進数表現からバイナリに変換することで 32byte から 16byte に変換することができます。これにより、インデックスの保持に必要な容量が減りパフォーマンス若干向上します。
文字列 UUID をバイナリ UUID に変換し、結果を返します。 (
IS_UUID()関数の説明には、許可されている文字列 UUID 形式がリストされます。) 戻りバイナリ UUID はVARBINARY(16)値です。 UUID 引数がNULLの場合、戻り値はNULLです。 無効な引数がある場合は、エラーが発生します。
また、インデックスとして使用している場合は次の引数が重要です。 swap_flag を 1 にすることで、UUID 生成の際にスワップされてしまったタイムスタンプ部分の上位ビットと下位ビットを再度交換し、ほぼシーケンシャルな ID として利用することができるようになります。
swap_flagが 1 の場合、戻り値の形式は異なります: time-low 部分と time-high 部分 (それぞれ 16 進数の最初と 3 番目のグループ) がスワップされます。 これにより、より迅速に変化する部分が右側に移動し、結果がインデックス付けされたカラムに格納されている場合はインデックス付けの効率を向上させることができます。
ULID
ULID は UUID の問題点であったタイムスタンプがそのまま保持されていない点を解消し、タイムスタンプを上位ビットにそのまま保持し、下位ビットにランダム列が入ります。
ulid/spec: The canonical spec for ulid
そのためデフォルトでほぼシーケンシャルとなり、インデックス周りのパフォーマンス低下を抑えることができます。
他にも、
- 128bit (=16byte)
- UUID は文字列で36文字必要なところ、26文字で抑えられる
- 1msあたり、1.21e+24 のユニークな ULID が生成される
あたりの特徴があります。くわしくはリンク先の仕様を見てください。
UUIDv1, UUIDv4, v7, ULID の比較はこんなところでしょう。
| フォーマット | ソート可能性 | 単調増加性 | ランダムさ程度 |
|---|---|---|---|
| UUIDv1 | 基本なし（binary変換すればあり） | なし | （調べたが不明。MACアドレスをもとに生成されるため推測される） |
| UUIDv4 | なし | なし | 122 bits |
| UUIDv7 | あり | あり | 62 bits |
| ULID | あり | あり | 80 bits |
ULIDs and Primary Keys | Dave Allie の表に UUIDv1 を追加して記載しました。
別に考える必要のあること
- そもそも UUID version1 はなぜ生成時にタイムスタンプの上位ビットと下位ビットをスワップするような作りなのか
- UUID version6, 7, 8 はソート可能なように提案されているようだが、現在どの程度使用されているのか。他のアルゴリズムと比べてパフォーマンスはどうなのか。
- ULID の場合は、バイナリ形式で保持するとパフォーマンス上昇はどの程度あるのか
- 分散システムで厳密にシーケンシャルな ID が必要な場合は別に色々と考える必要がある
