MySQL で主キーを設計する方法

この記事では、Mysql の主キーの問題について説明し、Mysql の主キーに関連する知識をその理由の観点から理解し、主キー生成ソリューションにまで拡張します。 Mysql について尋ねられたときに、CRUD しか知らないという不安はもうありません。

1. 主キーが必要な理由

データ レコードは一意である必要があります (第一正規形)

データは結合に関連付ける必要があります

データベースの基礎となるインデックスは、データの取得に使用されます。

「情報はランダムな不確実性を排除するために使用されるものです」(シャノン)。人は、自然や社会からさまざまな情報を得て識別し、異なるものを区別することで世界を理解し、変革することができます。データとは、客観的な物事の属性を反映した記録であり、情報を具体的に表現したものです。データは処理されると情報になりますが、情報を保存して送信するには、その前に情報をデータにデジタル変換する必要があります。データベースはデータレコードを保存するために使用されます。そうすると、記録は決定的（相対的）な情報であり、その確実性は一意性である。最初の理由は次のとおりです:

1. データ レコードは一意である必要があります

世界は客観的な存在とその関係で構成されています。データはデジタルでモデル化された関係の中に存在します。データ自体の説明的価値に加えて、その価値は相互接続性にあります。関連付けの精度を実現するには、データを外部で相互に関連付ける必要があります。したがって、データ ストレージに反映されるように、主キーの 2 番目の役割は存在の 2 番目の要素でもあります:

2. データを関連付ける必要があります

データは客観的な現実を記述するために使用されます。それ自体は無意味です。主観的なニーズに従って整理し、物事を一定の方法で理解して人々を満足させた後でのみ、それは意味を持つことができます。したがって、データを取得して整理する必要があります。次に主キーの 3 番目の役割:

3. データベースの基礎となるインデックスはデータの取得に使用されます

2. 主キーが長すぎてはいけない理由

これ 問題は長さです。では、長いよりも短いことにはどのような利点があるのでしょうか? （おいおいおい、含意） - 短くてスペースを取りません。ただし、このような少量のディスク領域はデータ ボリューム全体に比べれば取るに足らないものであり、通常、主キー列はあまり使用されません。そうすると、その理由は速度にあるはずで、元のデータとはあまり関係がありません。このことから、当然のことながら、それはインデックスに関連しており、インデックスの読み取りに関連していると結論付けられます。では、なぜ長い主キーがインデックスのパフォーマンスに影響を与えるのでしょうか?

上記は Innodb のインデックス データ構造です。左側はクラスター化インデックスで、主キーによってデータ レコードを検索します。右側はセカンダリ インデックスで、列データにインデックスを付け、列データを通じてデータの主キーを検索します。セカンダリ インデックスを介してデータがクエリされる場合、プロセスは図に示すように、まずセカンダリ インデックス ツリーから主キーが検索され、次にクラスター化インデックスの主キーを介してデータ行が検索されます。セカンダリ インデックスのリーフ ノードは、主キー ポインターではなく、主キー値を直接格納します。したがって、主キーが長すぎると、副インデックスツリーに格納できるインデックスレコード数が減少し、限られたインデックスバッファ内でのディスク読み取り回数が増加するため、パフォーマンスが低下します。 。

3. 自動インクリメント ID の使用が推奨される理由

InnoDB は、上の図に示すようにクラスター化インデックスを使用し、データ レコード自体は次の場所に保存されます。リーフ ノードのメイン インデックス ( B Tree )。これには、同じリーフ ノード (1 つのメモリ ページまたはディスク ページのサイズ) 内の各データ レコードが主キーの順序で格納される必要があるため、新しいレコードが挿入されるたびに、MySQL はその主キーに基づいて適切なノードにそのレコードを挿入します。 . と位置、ページが負荷係数 (InnoDB のデフォルトは 15/16) に達すると、新しいページ (ノード) が開きます。

テーブルで自動インクリメント主キーが使用されている場合、新しいレコードが挿入されるたびに、レコードは現在のインデックス ノードの後続の位置に順次追加されます。ページがいっぱいになると、新しいレコードが追加されます。ページが自動的に開きます。これにより、ほぼ順次に埋められるコンパクトなインデックス構造が得られます。次の図の左側に示すように、既存のデータを挿入するたびに移動する必要がないため、非常に効率的であり、インデックスの維持に多くのオーバーヘッドが追加されません。それ以外の場合、毎回挿入される主キーの値はほぼランダムであるため、新しいレコードはそれぞれ既存のインデックス ページの中央のどこかに挿入する必要があり、MySQL は新しいレコードを適切な位置に挿入するためにデータを移動する必要があります。これにより、下図の右側に示すように、ある程度のオーバーヘッドが発生します。このため、Mysql はインデックスを維持するためにバッファを頻繁に更新する必要がある場合があり、これによりメソッド ディスク IO の数が増加し、インデックス構造の再編成が必要になることがよくあります。

4. ビジネス キー VS 論理キー

ビジネス キー、つまり、注文シリアル番号をキーとして使用するなど、ビジネス上重要な ID をキーとして使用します。主キー 順序テーブルのキー。論理キー、つまり業務に関係のないキーは、キーの自動インクリメントなど、一定のルールに従ってキーを生成します。

ビジネス キーの利点

キーにはビジネス上の重要性があり、クエリ時に検索キーワードとして直接使用できます

追加の列やインデックスは必要ありませんスペース

を使用すると、一部の結合操作を減らすことができます。

ビジネスキーのデメリット

ビジネスが変わると主キーの変更が必要になる場合がある

複数のカラムがあると操作が難しくなるのキーが関与する

ビジネス キーは多くの場合長く、より多くのスペースを占有するため、ディスク IO が大きくなります

データはキーが決定されるまで永続化できません。データ キーが決定していない場合、最初にレコードを追加してからビジネス キーを更新したい場合があります。

両方とも簡単なキー生成を設計する使いやすさとパフォーマンスの向上 ソリューションはより困難です

論理キーの利点

ビジネスの変化によってキー ロジックを変更する必要がありません

シンプル操作性と管理の容易さ

論理キーは多くの場合、小さくてパフォーマンスが優れています

論理キーは一意性を確保するのが簡単です

最適化が簡単です

論理キーの欠点

主キー列と主キー インデックスのクエリには追加のディスク領域が必要です

データの挿入および更新時に追加の IO が必要です

結合が増える可能性があります

一意性ポリシーの制限がない場合、重複キーが発生しやすくなります

テスト環境と正式環境のキーが一致しないため、トラブルシューティングに役立ちません

キーはデータに関連付けられておらず、3 つのパラダイムにも準拠していません

キーワードの検索には使用できません

さまざまなデータベース システムの特定の実装に依存します。基盤となるデータベースの置き換え

5. 主キーの生成

通常の状況では、テーブルの主キーとして Mysql の自動インクリメント ID を使用します。とてもシンプルで、以上のことから性能も最高です。ただし、サブデータベースやサブテーブルの場合、ID の自動インクリメントではニーズを満たすことができません。さまざまなデータベースがどのように ID を生成するかを見て、いくつかの分散 ID 生成ソリューションについても見てみましょう。これは、独自の分散 ID 生成サービスを検討し、実装するのにも役立ちます。

データベースの実装

Mysql 自動インクリメント

Mysql はメモリ内に自動インクリメント カウンタを維持します。 -increment counter にアクセスすると、InnoDB はステートメントの終わりまで AUTO-INC という名前のロックを追加します (ロックはステートメントの終わりまでのみ保持され、トランザクションの終わりまでではないことに注意してください)。 AUTO-INC ロックは、auto_increment を含む列の同時挿入性を向上させるために使用される特別なテーブル レベルのロックです。

分散環境では、実際には ID 生成に別のサービスとデータベースを使用し、それでも Mysql のテーブル ID 自動インクリメント機能を利用してサードパーティ サービスの ID を均一に生成できます。パフォーマンス上の理由から、ビジネスごとに異なるテーブルを使用できます。

Mongodb ObjectId

主キーの競合を防ぐために、Mongodb は主キー ID として ObjectId を設計します。これは、次の部分を含む 12 バイトの 16 進数で構成されます:

Time: タイムスタンプ。 4バイト。秒。

マシン: マシンの識別。 3バイト。一般に、これはマシンのホスト名のハッシュ値です。これにより、異なるホストが異なるマシン ハッシュ値を生成し、配布内で競合がなく、同じマシンが同じ値を持つことが保証されます。

PID: プロセス ID。 2バイト。上記の Machine は、異なるマシン上で生成された objectId が競合しないようにするためのものであり、pid は、同じマシン上の異なる mongodb プロセスによって生成された objectId が競合しないようにするためのものです。

INC: 自己増加カウンター。 3バイト。最初の 9 バイトは、1 秒以内に異なるマシン上の異なるプロセスによって生成された objectId が競合しないことを保証します。自己増加カウンタは、同じ 1 秒以内に生成された objectId が競合しないことを保証するために使用されます。256 の 3 乗を許可すると等しいです。 16777216 エントリまで。レコードの一意性。

Cassandra TimeUUID

Cassandra は次のルールを使用して一意の ID を生成します: time MAC シーケンス

Scheme

Zookeeper auto-インクリメント: zk の自動インクリメント メカニズムによって実現されます。

Redis の自己インクリメント: Redis の自己インクリメント メカニズムを通じて実現されます。

UUID: UUID 文字列をキーとして使用します。

スノーフレーク アルゴリズム: Mongodb の実装と同様、1 符号ビット、41 ビット タイムスタンプ (ミリ秒レベル)、10 ビット データ マシン ビット、ミリ秒以内の 12 ビット シーケンス。

オープンソース実装

Baidu UidGenerator: スノーフレーク アルゴリズムに基づいています。

Meituan Leaf: Mysql 自動インクリメント (最適化) とスノーフレーク アルゴリズムに基づくメカニズムも実装しています。

以上がMySQL で主キーを設計する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。