Zenn
😺

MySQLのプライマリーキーには何を使用するのがいいのか

2023/10/23に公開
MySQL
SQL
#
RDB
tech

内容

現時点でRDBのprimary keyには何を使用するのがいいのかを自分なりに考えた備忘録です。
候補は以下になります。

  • AUTO_INCREMENT
  • UUID V4
  • ULID
  • UUID V7(Draft状態)
    １つずつメリット・デメリットを見ていこうと思います。

AUTO_INCREMENT

AUTO_INCREMENTを利用しているプロジェクトは多いのではないでしょうか。
MySQLで採番し、テーブル内での一意性も保証してくれるのでとりあえずこれにしとけば問題ないように思います。では、メリット・デメリットを見ていきます。

メリット

  • 一意性と簡単な管理: AUTO_INCREMENTを使用することで、各行に一意の数値が割り当てられます。このため、データの一意性が確保され、データの管理が簡単になります。同じ値が誤って挿入されるリスクが低減します。

  • 高速な検索と結合: 整数値をプライマリーキーとして使用することは、データの検索と結合のパフォーマンスを向上させます。整数は非常に高速に比較できるため、クエリの実行が迅速に行えます。

  • データベースのコンパクト化: 整数値を使用することで、プライマリーキーのインデックスは非常に効率的に管理できます。これにより、データベースのストレージ効率が向上し、ディスクスペースを節約できます。

デメリット

  • 初期値の管理: AUTO_INCREMENTを使用する場合、データベースは新しい行の値を自動的に増分します。しかし、一部のケースでは特定の値から始めたい場合があるため、初期値の管理が必要になることがあります。

  • DB統合時の競合: 複数のDBを統合したい場合、AUTO_INCREMENTだと採番されたプライメリーキーが衝突する可能性が高くなります。

  • 上限がある: INT 型だと約21億4700万、unsignedが指定されていると倍の約42億9400万が上限です。上限に達したあとは空いている番号の小さい順からまた採番が始まります。

  • DBにINSERTされてから採番される: DDDなどのアーキテクチャを採用しているプロジェクトだとDBにINSERTする前に一意な値が欲しい場合もあると思います。そのような場合、AUTO_INCREMENTだとINSERTする前にプライマリーキーを指定できません。

UUID V4

UUID v4は、ランダムな値に基づいて生成される128ビットの識別子です。 これは、生成するたびに完全にランダムな値が生成されるため、非常に高いユニーク性を持ちます。 衝突可能性は0ではないものの、現実的には衝突しないため安全に使用できる識別子になります。

メリット

  • 一意性: UUID v4 は理論的に一意であり、異なる場所や時間で生成されても、ほぼ同じ値が生成される可能性は極めて低いです。これにより、データベース内で一意性が確保されます。

  • 分散データベースとレプリケーション: 複数の分散データベースを使用する場合やデータベースのレプリケーションを行う場合、UUIDは一意性を維持しやすいため、データの整合性を簡単に確保できます。

  • データのプライバシー: UUIDは数値の連続値とは異なり、個人を特定する情報を含まないため、プライバシーに関する規制に適しています。

  • アプリで任意のタイミングで生成できる: これはAUTO_INCREMENTでのデメリットに関係するのですが、DBにINSERTする前にプライマリーキーを生成することができます。

デメリット

  • パフォーマンス: UUIDは通常、整数値よりも大きなデータ型であり、比較やインデックス化にコストがかかることがあります。データベースのパフォーマンスに影響を与える可能性があります。もう少し具体的に触れると、大規模テーブル（クラスタインデックス）からランダムなリーフページを読み込むストレージのI/O処理にコストがかかりパフォーマンスが悪くなります。この辺りはこちらの記事がとても参考になりました。
    https://techblog.raccoon.ne.jp/archives/1627262796.html

  • ソート不可能: UUID V4は完全にランダムな値で構成されているので何かの基準に基づいてのソートができません。

  • データの可読性: UUIDはランダム性を持つため、データベース内の値が人間にとって理解しやすい連続した整数値ではなく、ランダムな文字列となります。これはデータの可読性とデバッグの複雑性に影響を与える可能性があります。

  • インデックスのフラグメンテーション: UUIDをプライマリーキーとして使用する場合、インデックスがフラグメンテーションする可能性があるため、データベースのパフォーマンスに影響を与えることがあります。

総合的に、UUID v4をプライマリーキーとして使用することは一意性と分散環境でのメリットがありますが、パフォーマンスやデータの可読性に関する課題も存在します。特にパフォーマンスについては、ご紹介した参考記事の中でも触れている通り、対規模なデータになるほどパフォーマンス悪化が顕著になります。場合によりますが、UUID V4はメリットが大きい分、デメリットも大きな印象です。

ULID

ULIDとは、UUIDと同様にランダムな識別子です。UUID v4とは違い、先頭48ビットにタイムスタンプを置くことにより、ソート可能となっています。1ミリ秒単位でソート順が保証されており、同一の生成器であれば2^80までソート順が保証されます。

メリット

  • パフォーマンス: ULIDはランダム性を持つ UUID v4に比べて、ソートや比較において効率的です。データベースのパフォーマンスにプラスの影響を与えることが少ないです。完全にシーケンシャルな値とはなりませんが、UUID V4が抱えるパフォーマンスの問題について大きな優位性を持ちます。

  • 一意性: ULIDはUUID v4と同様に一意性が高いです。異なる場所や時間で生成されても、ほぼ同じ値が生成される可能性は極めて低いです。

  • ソート可能: ULIDは時間を基にソート可能な値であり、生成された順序にデータを並べ替えるのに適しています。この特性はデータのタイムスタンプ順にアクセスする場合に有用です

デメリット

  • パターンの予測: ULIDは時間に基づいているため、一部の攻撃者がデータを予測する可能性があります。UUID v4のように完全なランダム性を提供しないため、セキュリティに関しては慎重に扱う必要があります。

総合的に、ULIDは一意性、ソート可能性、パフォーマンスなどの面で優れた特性を持ちます。個人的な意見ですが、UUID V4を適用するほとんどのシーンでULIDで代替できるのではないでしょうか。

UUID V7(おまけ)

最後にご紹介するのはUUID V7です。今現在、UUID V6, 7, 8は標準化団体IETFによってDraftが提出されている状態となっています。なので公式には規格外のUUIDとなっています。 ですが、pythonなどの一部の言語ではDraftの内容をを読み取ってUUID v7がサポートされているようです。
https://pypi.org/project/uuid7/

UUID Version 6, 7, 8 の目的を簡単にまとめると「タイムスタンプ情報を使ってソートできる ID を採番できるようにする」ことです。
UUID v7は、現在の時刻を表すタイムスタンプに基づいて生成されます。このタイムスタンプには、秒単位の精度だけでなく、ナノ秒単位の精度も含まれます。これにより、UUID v7は、生成時刻の順序が保証され、生成時刻を基準にソートすることができます。

まとめ

プライマリーキーとして採択するにはそれぞれメリット・デメリットが存在しますが、自分たちのプロジェクトに合わせて採用することが大事だと思います。
プロジェクトの規模が小さくなりそうならAUTO_INCREMENTが簡単で管理しやすそうですし、もう少し規模が大きくなったりセキュリティが心配なるのであればUUID V4が良いかもしれません。さらに規模が大きくなってパフォーマンスに問題が出てくればULIDにするのが良いかもしれません。
また、将来的にUUID v7が公式の規格になればUUID V4やULIDではなくUUID V7一択になる時も来るかもしれません。
自分たちのプロジェクトに合ったプライマリーキーの選択ができる一助になれば幸いです。

Discussion