MySQL で自動インクリメントされる ID が不足する問題を解決する方法の詳細な分析

この記事は、mysql に関する関連知識を提供します。主に自己増加 ID に関する関連事項を紹介します。ID には上限があります。上限があるため、常に使用されます。完了しました、ID がなくなったらどうすればいいですか? それでは、見てみましょう。

推奨学習: mysql ビデオ チュートリアル

最近、このような面接の質問を目にしました。 MySQL で自動増加する ID が不足しました。どうすればよいですか? 以下は、このインタビューの質問に対する答えです。

MySQL を使用したことがある、または MySQL を理解している場合は、自動インクリメント主キーについて知っている必要があります。各自動インクリメント ID には初期値が定義されており、その後、指定されたステップ サイズ (デフォルトのステップ サイズは 1) に従って増加します。自然数には上限がありませんが、テーブル構造を設計する際にフィールド長を指定することが多いため、idには上限が設けられています。上限があるのでいつかは使い切ってしまうのですが、IDを使い切ってしまったらどうなりますか？今日は一緒に学びましょう。

自動インクリメント ID

自動インクリメント ID といえば、テーブル構造を設計するときに自動インクリメント ID フィールドをカスタマイズすることが最初に考えられると思いますが、ここで問題が発生します。データの挿入時に、一意の主キーの衝突、SQL トランザクションのロールバック、バッチ挿入、自動インクリメント値のバッチ適用などが発生し、自動インクリメント ID が不連続になる可能性があります。

テーブルに定義されたオンライン後の自動インクリメント値のロジックは、次の ID を申請するときに同じ値 (最大値) が取得されるというものです。 SQL を挿入して ID を最大値に設定し、その後、ID をアクティブに設定しないステートメントを挿入して、この結論を検証できます。この時点で再度挿入すると、主キーの競合が報告されます~

ここで注意してください: 232-1 (4294967295) は、特に大きな数字ではありません。頻繁にデータの挿入と削除を行うテーブルの場合、完成品として使用できます。したがって、テーブルを作成する際には、テーブルがこの上限に達する可能性が高いかどうかを確認し、可能であれば 8 バイトの符号なし bigint として作成する必要があります。

InnoDB システムは row_id を自動的にインクリメントします

作成する InnoDB テーブルで主キーが指定されていない場合、InnoDB は長さ 6 バイトの非表示の row_id を作成します。 InnoDB はグローバルな dict_sys.row_id 値を維持します。主キーのないすべての InnoDB テーブルでは、データ行が挿入されるたびに、現在の dict_sys.row_id 値が挿入されるデータの row_id として使用され、次に dict_sys の値が使用されます。 .row_id が 1 増加します。

実際、コードが実装されると、row_id は長さ 8 バイトの符号なし長整数 (符号なし bigint) になります。ただし、InnoDB が設計されたとき、row_id の長さは 6 バイトしか残されておらず、このようにして、データ テーブルに書き込むときに最後の 6 バイトだけが書き込まれました。そのため、データ テーブルに書き込むことができる row_id 値は 2 つあります。データ テーブルの機能:

テーブルに書き込まれる row_id の値の範囲は 0 ～ 248-1 です;

dict_sys.row_id=2^ の場合48、再度データを挿入する行為がある場合はrow_idを申請する必要があり、取得後は最後の6バイトが0となります。

2^48 という数字はすでに非常に大きいですが、システムは長時間稼働する可能性があるため、上限に達する可能性があることは誰もが知っているはずです。元のレコードは上書きされます。したがって、このオプションは選択しないようにしてください。

Xid

MySQL の REDO ログと binlog を組み合わせると、Xid と呼ばれる共通のフィールドがあります。 MySQLのトランザクションに対応するために使用されます。

MySQL は内部的にグローバル変数 global_query_id を保持しており、ステートメントが実行されるたびに Query_id に代入され、この変数に 1 が加算されます。現在のステートメントがこのトランザクションによって実行される最初のステートメントである場合、MySQL はこのトランザクションの Xid に Query_id も割り当てます。 Global_query_id は純粋なメモリ変数であり、再起動後にクリアされます。したがって、同じデータベース インスタンス内では、異なるトランザクションの XID が同じになる可能性があります。

Innodb trx_id

InnoDB は内部的に max_trx_id グローバル変数を維持します。新しい trx_id を適用する必要があるたびに、max_trx_id の現在の値が取得され、max_trx_id が 1 ずつ増加します。

InnoDB データ可視性の中心的な考え方は次のとおりです: データの各行には、それを更新する trx_id が記録されます。トランザクションがデータ行を読み取るとき、データが可視であるかどうかを判断する方法は、トランザクションの一貫性によって行われます。ビューは、このデータ行の trx_id と比較されます。ただし、このプロセスではダーティ リードが行われるため、ID はアトミックではなく、重複する可能性があります。

thread_id

実際、スレッド ID は MySQL で最も一般的な自己増加 ID です。通常、さまざまなシーンをチェックするとき、show processlist の最初の列は thread_id です。

thread_id のロジックは簡単に理解できます。システムはグローバル変数 thread_id_counter を保存します。新しい接続が作成されるたびに、thread_id_counter がこの新しい接続のスレッド変数に割り当てられます。

thread_id_counter の定義されたサイズは 4 バイトであるため、232-1 に達すると 0 にリセットされ、増加し続けます。結果は row_id と同じになり、元のレコードは上書きされます。

上記では、MySQL 独自の自動インクリメント ID の一部を紹介しています。実際、実際のアプリケーションでは、外部の自動インクリメント主キーを選択し、それらをデータベースに永続化して、データベース独自の自動インクリメント ID を置き換えることもできます。 ID。IDを増やします。以下でそれについて話しましょう。

Redis の自己増加主キー

実際、外部の自己増加主キーを生成する方法はたくさんあります。それは実際の応用において多くの利点を発見したからです。

Redis 自体はアトミックであるため、高い同時実行性もスレッドセーフです。主キー フィールドの長さが 20 であると仮定すると、時間を自動増分数値として使用して主キーを形成します (たとえば、8 桁の日付と 12 桁の自動増分数値)。そして、ビジネスの性質に応じて、時間を年、月、日、またはミリ秒レベルで決定でき、ミリ秒間の自動インクリメント数値が繰り返される確率は非常に小さいため、次のような用途に適用できます。基本的なビジネス。

まとめ

上記でいくつかの自己増加IDを紹介しましたが、それぞれの自己増加IDには独自の適用シナリオがあり、上限に達した後の性能も異なります。

#1,

テーブルの自動インクリメント ID が上限に達すると、再度適用しても値は変更されず、データの挿入を続けると主キーの競合エラーが発生します 2,
row_id に達しました 上限に達すると 0 に戻り、再度増加します 同じ row_id が出現した場合、後から書き込まれたデータが前のデータを上書きします 3,
Xid は、同じ binlog ファイル内に重複した値を持たないようにする必要があるだけです。理論的には重複した値が出現しますが、その確率は非常に小さいため無視できます。 4,
InnoDB の max_trx_id 増分値は MySQL が再起動されるたびに保存されるため、記事で述べたダーティ リードはこの例は、必ず現れるバグです。幸いなことに、まだ時間は十分にあります。5.
thread_id は、私たちが使用する最も一般的なものであり、最もよく処理される自動インクリメント ID でもあります。ロジック。6,
redis 外部自己インクリメント、ミリ秒レベル、理論的には値が繰り返されますが、確率は非常に小さいため無視できます 7,
実際、各自己増加 ID には独自の適用シナリオがあり、日常使用の具体的なシナリオに応じて選択できます。ただし、システムの稼働時間やデータの保存場所などを考慮する必要があるため、雨の日に備えて、総合的に考慮して、システム運用中にすぐに同じことが繰り返されないものを選択してください。習ったことがありますか？ 推奨学習:

mysql ビデオ チュートリアル

以上がMySQL で自動インクリメントされる ID が不足する問題を解決する方法の詳細な分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。