Mysql 主キー UUID と自動増分主キーの違いと利点と欠点

導入

私はしばらくの間、postgresql データベースを使用していました。クラウドに移行した後、自動増分主キーから uuid に変更しました。uuid はグローバルに一意であり、非常に便利だと感じています。

最近 MySQL を使用したところ、すべての MySQL 主キーが自動増分主キーであることがわかりました。慎重に比較した結果、MySQL が自動増分主キーを選択する理由と、その違いがわかりました。

MySQL 5.0 より前では、複数のマスター レプリケーション環境がある場合、自動インクリメント プライマリ キーは重複する可能性があるため使用できません。バージョン 5.0 以降では、自動インクリメント オフセットを構成することで問題全体が解決されます。

どのような状況でuuidを使用するのか

1. 重複を回避し、拡張性を容易にします。これが、クラウド サービスを構築するときに UUID を選択する主な理由です。

2. 倉庫に入る前にIDを知ることができます

3. 比較的安全。uuid から単純に情報を取得することはできません。ただし、自己増分されている場合は、情報を公開するのは簡単です。顧客 ID が 123456 の場合、123456 の顧客 ID があることは簡単に推測できます。

UUIDの何が問題なのか

1. UUIDは16バイトで、int（4バイト）やbigint（8バイト）よりも多くのストレージスペースを占有します。

2. サイズと乱雑さにより、パフォーマンスの問題が発生する可能性があります

MySQL の UUID 原則

MySQL の InnoDB ストレージ エンジンがストレージを処理する方法は、クラスター化インデックスを使用することです。

クラスター化インデックスとは、データベース テーブル行内のデータの物理的な順序が、キー値の論理的な (インデックス) 順序と同じである場合です。テーブルの物理的な順序は 1 つのケースのみに限られるため、テーブルにはクラスター化インデックスを 1 つだけ持つことができます。

1. uuidを主キーとして使用する理由

（１）実際、InnoDBストレージエンジンでは、自己増分IDを主キーとして使用した場合のパフォーマンスが最高に達しています。保存速度、読み取り速度ともに最速で、占有する保存スペースも最小です。

（2）しかし、実際のプロジェクトでは問題に遭遇します。履歴データテーブルの主キーIDは、データテーブルのIDと重複します。自動増分IDを主キーとする2つのテーブルをマージすると、必ずIDの競合が発生します。ただし、それぞれのIDが他のテーブルにも関連付けられている場合、操作が非常に難しくなります。

（３）UUIDを使用する場合、生成されるIDはテーブルに依存しないだけでなく、データベースにも依存しません。これは将来のデータ操作にとって非常に有益であり、一度限りのソリューションであると言えます。

2. UUIDのメリットとデメリット

デメリット: 1. 挿入速度に影響し、ハードディスクの使用率が低下する

2. UUID のサイズの比較は数値の比較よりもはるかに遅く、クエリの速度に影響します。

3. UUID は多くのスペースを占有します。作成するインデックスの数が増えるほど、影響は深刻になります。

利点: データを分割して統合して保存すると、グローバルな一意性が実現されます。

3. 最善の解決策

（１）InnoDBエンジンテーブルはB+ツリーに基づくインデックス構成テーブルです。

（２）B+ツリー：B+ツリーは、ディスクやその他の直接アクセス補助装置用に設計されたバランス検索ツリーです。B+ツリーでは、すべてのレコードノードがキー値の順序で同じ層のリーフノードに格納され、リーフノードポインタが接続されています。

（3）InnoDBプライマリインデックス：リーフノードには完全なデータレコードが含まれています。このタイプのインデックスはクラスター化インデックスと呼ばれます。 InnoDB インデックスは、非常に高速な主キー検索パフォーマンスを提供します。ただし、セカンダリ インデックスには主キー列も含まれるため、主キーが大きく定義されている場合は、他のインデックスも大きくなります。テーブルに多数のインデックスを定義する場合は、主キーをできるだけ小さく定義するようにしてください。 InnoDBはインデックスを圧縮しない

（4）このクラスター化インデックスの実装方法により、主キーによる検索は非常に効率的になりますが、補助インデックス検索では、最初に補助インデックスを検索して主キーを取得し、次に主キーを使用して主インデックスを検索してレコードを取得するという2つのインデックス検索が必要になります。

上記に基づいて、次のようになります。

（１）InnoDBテーブルのデータ書き込み順序がB+ツリーインデックスのリーフノード順序と一致している場合、アクセス効率は最も高くなります。ストレージとクエリのパフォーマンスのために、自己増分 ID を主キーとして使用する必要があります。

（2）InnoDBのプライマリインデックスの場合、データはプライマリキーに従ってソートされます。UUIDの乱れにより、InnoDBは大きなIOプレッシャーを生成します。このとき、UUIDを物理プライマリキーとして使用するのは適切ではありません。論理プライマリキーとして使用できます。物理プライマリキーは、引き続き自動インクリメントIDを使用します。グローバルな一意性を確保するには、uuid を他のテーブルを関連付けるインデックスとして、または外部キーとして使用する必要があります。

4. UUID を主キーとして使用する必要がある場合は、次の提案を参考にしてください。

マスタースレーブまたは MS モードの場合、MySQL 組み込みの uuid 関数を使用して一意のプライマリ キーを生成しないことをお勧めします。これは、マスター テーブルによって生成された uuid がスレーブ テーブルに関連付けられている場合、データベースにアクセスして uuid を見つける必要があり、データベースとのやり取りが 1 回多く必要になるためです。この時間差の間に、マスター テーブルがデータを生成する可能性が高く、関連付けられた uuid にエラーが発生しやすくなります。どうしても uuid を使いたい場合は、Java で生成して DB に直接保存することができます。このとき、マスターとスレーブの uuid は同じになります。

補足：MySQLのuuid()主キーは重複しています

1. mysqlのuuid()主キーが重複している

MySQLはNavicatクライアントを使用し、次のSQLを実行すると

t_user u から、id、u.user_id として replace(uuid(), '-', '') を選択します。

生成された uuid が重複していることが判明しました。

調査の結果、Navicat の問題であることが判明しました。SQL ステートメントを次のように調整する必要がありました。

t_user u から replace(convert(uuid() using utf8mb4), '-', ''), u.user_id を選択します。

結果は次のとおりです。

2. 他の解決策を使用する:

uuid に対して再度 md5 を実行します。

t_user u から id、u.user_id として md5(uuid()) を選択します。

上記は私の個人的な経験です。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。間違いや不備な点がありましたら、遠慮なくご指摘ください。