MySQL の自動増分主キーが使い果たされた場合の対処方法

MySQL の自動増分主キーが使い果たされた場合の対処方法

面接では、次のようなシナリオを経験する必要があります。

インタビュアー: 「MySQL を使用したことがありますか? 自動増分主キーまたは UUID を使用していますか?」

あなた: 「自動増分主キーを使用しました」

インタビュアー:「なぜ自動増分主キーなのですか?」

あなた:「自動増分主キーが使用されるため、データは物理構造に順番に格納され、パフォーマンスが最高になります、などなど...」

インタビュアー: 「自動増分主キーが最大値に達して使い果たされた場合はどうなりますか?」

あなた:「え?レビューしてないよ!!」(それなら戻って通知を待てばいいですよ!)

この質問はファンから聞いたのですが、意(KENG)思(B)!
そこで、今日はこの自動増分主キーが使い果たされたときに何をすべきかについてお話します。

文章

シンプルバージョン

まず、MySQLではInt整数の範囲は次のようになることを理解しましょう。

符号なし整数を例に挙げてみましょう。保存範囲は 0 から 4294967295 で、約 43 億になります。まず、自動増分IDが最大値に達した後、データが挿入され続けると、主キー競合例外が次のように報告されるとします。

//キー 'PRIMARY' の重複エントリ '4294967295'

解決策も非常に簡単です。Int型をBigInt型に変更します。BigIntの範囲は次のとおりです。

たとえ1秒あたり1万件のデータ項目があり、それを100年間実行したとしても、1つのテーブル内のデータは
10000*24*3600*365*100=31536000000000
この数値はまだ BigInt の上限からは程遠いので、自動インクリメント ID を BigInt 型に設定すれば、自動インクリメント ID が最大値に達する問題を心配する必要はありません。
しかし、面接での答えが

あなた: 「簡単です。自動増分主キーの型を BigInt に変更するだけです!」

次に、面接官はもっと難しい質問をするかもしれません。

インタビュアー: 「オンラインで列のデータ型を変更するにはどうすればよいですか?」

あなた:「えっ!通知を待つだけ!」

変更方法

現在、業界ではテーブル構造のオンライン変更に3つのソリューションがあります。私の知る限り、一般的には次の3つがあります。

方法1: MySQL 5.6以降が提供するオンライン変更機能を使用する

MySQL 自体が提供するいわゆる関数は、MySQL 独自のネイティブ ステートメントです。たとえば、元のフィールド名とタイプを変更する場合などです。

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

MySQL 5.5 より前では、これは一時テーブルをコピーすることによって実現されていました。 ALTERステートメントを実行すると、新しい構造の一時テーブルが作成され、元のテーブルのすべてのデータが一時テーブルにコピーされ、名前の変更が実行されて作成操作が完了します。このプロセスでは、元のテーブルは読み取り可能ですが、書き込みはできません。
MySQL 5.6 以降では、データベース テーブルのオンライン変更がサポートされています。テーブルの変更プロセス中、ほとんどの操作で元のテーブルを読み書きできます。
では、列のデータ型を変更するなどの操作では、元のテーブルに書き込むことはできますか?さあ、MySQL 8.0バージョンの写真を探すために公式サイトに行きました

図に示すように、データ型の変更などの操作では同時 DML 操作はサポートされていません。つまり、 ALTERなどのステートメントを使用してテーブルのデータ構造をオンラインで直接変更すると、このテーブルは更新操作 ( DELETEUPDATEDELETE ) を実行できなくなります。
したがって、直接的なALTERは受け入れられません。

その場合、方法2または方法3のみを使用できます

方法2: サードパーティツールを使用する

業界には、テーブル構造のオンライン変更をサポートできるサードパーティ ツールがいくつかあります。これらのサードパーティ ツールを使用すると、 ALTER操作を実行してもテーブルがブロックされなくなります。他にも有名なものが2つあります

  • pt-online-schema-change 、略してpt-osc
  • GitHubは、 gh-ostと呼ばれるオープンソース形式のツールのリリースを正式に発表した。

pt-osc例にとると、その原理は次のようになります。

1. 変更されたデータ テーブル構造を持つ新しいテーブルを作成します。このテーブルは、ソース データ テーブルから新しいテーブルにデータをインポートするために使用されます。

2. データがコピーされた後、ソース データ テーブルのデータの変更を継続する操作を記録するトリガーを作成します。データがコピーされた後、これらの操作を実行して、データが失われないようにします。

3. ソース データ テーブルから新しいテーブルにデータをコピーします。

4. ソース データ テーブルの名前を古いテーブルの名前に変更し、新しいテーブルの名前をソース テーブルの名前に変更して、古いテーブルを削除します。

5. トリガーを削除します。

しかし、実際にはこれら 2 つのツールは意(KENG)思(B) 。 。 。驚いた。 。 。良い!テーブルにトリガーと外部キーがある場合、これら 2 つのツールは機能しません。
実際にデータベース内でトリガーや外部キーに遭遇した場合は、強制的に実行することしかできません。方法 3 を参照してください。
方法3:スレーブテーブル構造を変更し、マスターとスレーブを切り替える<br /> この方法は非常に面倒で、プロのプレイヤーが操作する必要があります。 MySQL アーキテクチャは一般的に読み取りと書き込みが分離されたアーキテクチャであるため、スレーブは読み取りに使用されます。スレーブ データベースの読み取り操作をブロックせずに、スレーブ データベース上のテーブル構造を直接変更します。変更後は、マスターとスレーブを切り替えることができます。注意する必要があるのは、マスターとスレーブの切り替えプロセス中にデータが失われる可能性があることだけです。

上級バージョン

実際、上記の質問に答えると、この記事はほぼ完成します。しかし、最初に言ったことを思い出してください。これは非常に意(KENG)思(B)です、なぜでしょうか?
テーブル内の自動インクリメント フィールドが符号付き Int 型であるとします。つまり、フィールドの範囲は -2147483648 ~ 2147483648 です。
すべてが正しく行われます。自動増分 ID は 0 から始まるため、使用可能な範囲は 0 から 2147483648 になります。
表内の実際のデータ ID には必ず驚くようなものがあり、ID は必ずしも連続しているわけではないことを明確にしておきましょう。例えば、次のような状況が発生する

テーブル `t` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 主キー (`id`)、
) 日本語

次のSQLを実行します

t 値に挿入します(null);
// 挿入された行は (1) です
始める;
t 値に挿入します(null);
ロールラック;
t 値に挿入します(null);
// 挿入された行は (3) です

したがって、テーブル内の実 ID は必然的に不連続になります。
これで、自動インクリメント主キー ID のデータ範囲は 0 から 2147483648 になりました。つまり、1 つのテーブルに 21 億のレコードがあることになります。 IDの不連続性を考慮すると、実際のデータはおそらく最大で18億です。
おい、1 つのテーブルにはすでに 18 億件のレコードがあるんだから、それを別のデータベースとテーブルに分割したらどうだ?データベースとテーブルを分割すると、各テーブルの自動増分 ID を使用してデータをグローバルに一意に識別することはできなくなります。この時点で、シャーディングライブラリとテーブルの環境をサポートするために、グローバルに一意の ID 番号生成戦略を提供する必要があります。

したがって、実際には、自動インクリメント主キーが使い果たされるまで待つことはできません。したがって、専門家の答えは次のようになります。

インタビュアー: 「自動増分主キーが最大値に達して使い果たされた場合はどうなりますか?」

あなた: 「自動増分主キーに int 型を使用しており、通常は最大値に到達できないため、データベースとテーブルを分割して、この問題に遭遇したことはありません。」

MySQL の自動インクリメント主キーが不足した場合の対処法についてはこれで終了です。MySQL の自動インクリメント主キーが不足した場合の対処法の詳細については、123WORDPRESS.COM の過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL の主キーとその自動増分の設定に関するチュートリアル
  • MySQL で自動増分主キーの型を int から char に変更する例
  • MySQL の自動増分 ID (主キー) が不足した場合の解決策
  • MySQL 8 の新機能: 自動増分主キーの永続性に関する詳細な説明
  • MySQL の非主キー自己増分使用例の分析
  • MySQLの自動増分主キーIDはこのように処理されません
  • MySQLの自動増分主キーの実装の詳細な説明
  • MySQL の自動インクリメント主キーが連続していないのはなぜですか?

<<:  Dockerコンテナオーケストレーション実装プロセス分析

>>:  WeChatアプレットに2048ミニゲームを実装する詳細なプロセス

推薦する

MySQL LOAD_FILE() 関数メソッドの概要

MySQL では、LOAD_FILE() 関数はファイルを読み取り、その内容を文字列として返します。...

CSSアニメーション属性キーフレームの詳細な説明

コラムを更新してからどれくらい経ったでしょうか?半年ですか?今年の後半は、まさに離陸、つまり文字通り...

IframeとFRAMEの違いの分析

1. Iframe タグの使用<br />Iframe については、「忘れられた隅」に放...

Vue3 ドキュメント クイックスタート

目次1. セットアップ1. セットアップ関数の最初のパラメータ - props 2. 文脈2. セッ...

HTML2 キャンバス SVG が認識されない場合の解決策

ウェブページを画像としてキャプチャする新機能があったので、人気のhtml2canvasを使ってみまし...

MySql の知識ポイント: トランザクション、インデックス、ロックの原則、使用状況の分析

この記事では、トランザクション、インデックス、ロックなどの MySQL の知識ポイントの原理と使用法...

コード分​​析を実現するためのFastDFSとNginxの統合

FastDFSとNginxの統合:トラッカーは、負荷分散と高可用性のために Nginx と組み合わせ...

mysqld_multi を使用して単一のマシンに複数のインスタンスをデプロイする方法に関する MySQL チュートリアル

目次1. MySQLのコンパイルとインストール: 2. 最初のマルチインスタンス3307を準備する3...

ROS2のインストールとdocker環境の使い方について

目次Docker を使用する理由は何ですか? DockerのインストールROSイメージを取得するRO...

...

HTMLフォーム送信方法のケーススタディ

フォームの送信方法をまとめると次のようになります。 1. 送信ボタンを使用して送信します。送信ボタン...

CSS @font-face パフォーマンス最適化の詳細な理解

この記事では主に、フォント読み込みの最適化に関する一般的な戦略を紹介します。内容の大部分は参考資料と...

Linuxサービスの監視と運用および保守

目次1. psutilパッケージをインストールする次に、オペレーティングシステム内のすべてのサービス...

MySQL が InnoDB テーブルが独立したテーブルスペースか共有テーブルスペースかを判断する方法の詳細な説明

序文InnoDB はデータをテーブルスペースに保存します。デフォルト設定では、初期サイズが 10 M...

Vueパンくずコンポーネントのカプセル化方法

Vueはパンくずコンポーネントをカプセル化して参照します。具体的な内容は次のとおりです。効果を達成す...