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ミニゲームを実装する詳細なプロセス

推薦する

Dockerコンテナのインポ​​ートとエクスポートに関するチュートリアル

背景Docker の人気は、コンテナの共有と移植が容易であることと密接に関係しています。ユーザーは、...

MySQL データベース シェル import_table データ インポート

目次MySQL Shell import_table データのインポート1. import_tabl...

CSS グリッドレイアウトを使用してレスポンシブな縦棒グラフを作成する方法

私はしばらくの間チャートをいじっていましたが、好奇心から、CSS を使用してチャートを作成するより良...

MySQLでヘッダー付きのCSVファイルをエクスポートする方法

公式ドキュメント http://dev.mysql.com/doc/refman/5.7/en/se...

MYSQL パフォーマンス アナライザー EXPLAIN 使用例分析

この記事では、例を使用して MYSQL パフォーマンス アナライザー EXPLAIN の使用方法を説...

MySQL 検査スクリプト (必読)

以下のように表示されます。 #!/usr/bin/env python3.5 psutilをインポー...

MySQLストレージ時間タイプの選択に関する問題の説明

MySQL では、datetime 型は通常、時間を保存するために使用されますが、現在では多くのシス...

MySQL で null 値と空文字 ('') を区別する

日常の開発では、データベースの追加、削除、変更、クエリが一般的に行われるため、Mysql で NUL...

NodeJs の高メモリ使用量のトラブルシューティング実戦記録

序文これは、オンライン コンテナーの拡張によって発生した調査です。最終的には、実際の OOM が原因...

vue+element で動的スキニングを実装するためのサンプルコード

プロジェクトのテーマがすべての人の美的感覚を満足できないこともあります。このとき、スキン変更機能は非...

Grafana+Prometheus を使用して MySQL サービスのパフォーマンスを監視する

Prometheus (プロメテウスとも呼ばれる) 公式サイト: https://prometheu...

Navicat Premium15 でクラウド サーバーに接続する際のデータベースの問題と落とし穴

クラウドサーバーを使用するとデータベースに接続できる場合もありますが、Navicat Premium...

Vue-router プログラムナビゲーションの 2 つの実装コード

ページをナビゲートする2つの方法宣言型ナビゲーション: リンクをクリックしてナビゲーションを実現する...

Vue+js 矢印をクリックして画像を切り替える

この記事の例では、矢印をクリックして画像を切り替えるVue + jsの具体的なコードを共有しています...

Mysqlリモート接続構成を実装する2つの方法

Mysqlリモート接続構成を実装する2つの方法会社で働いていると、誰かのコンピュータに保存されている...