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などのステートメントを使用してテーブルのデータ構造をオンラインで直接変更すると、このテーブルは更新操作 ( DELETE 、 UPDATE 、 DELETE ) を実行できなくなります。
したがって、直接的な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 をよろしくお願いいたします。

以下もご興味があるかもしれません: