MySQLの自動増分IDについて知っておくべきこと

MySQLの自動増分IDについて知っておくべきこと

はじめに: MySQL を使用してテーブルを作成する場合、通常は自動インクリメント フィールド (AUTO_INCREMENT) を作成し、このフィールドを主キーとして使用します。この記事では、自動増分 ID に関するすべてを質問と回答の形式で説明します。

注: この記事はすべて Innodb ストレージ エンジンに基づいています。

1. MySQL ではなぜ自動インクリメント列 ID を主キーとして設定することを推奨しているのですか?

  • 主キー (PRIMARY KEY) を定義すると、InnoDB は主キーをクラスター化インデックスとして選択します。主キーが明示的に定義されていない場合、InnoDB は NULL 値を含まない最初の一意のインデックスを主キー インデックスとして選択します。そのような一意のインデックスがない場合、InnoDB は組み込みの 6 バイト ROWID を暗黙のクラスター化インデックスとして選択します (ROWID は行レコードが書き込まれるにつれて増加します。この ROWID は ORACLE の ROWID のように参照可能ではなく、暗黙的です)。
  • データ レコード自体は、プライマリ インデックス (B+ ツリー) のリーフ ノードに格納されます。これには、同じリーフ ノード (メモリ ページまたはディスク ページのサイズ) 内の各データ レコードが主キーの順序で格納される必要があります。したがって、新しいレコードが挿入されるたびに、MySQL はそれを適切なノードに挿入し、主キーに従って配置します。ページが負荷係数 (InnoDB のデフォルトは 15/16) に達すると、新しいページ (ノード) が開かれます。
  • テーブルが自動増分主キーを使用している場合、新しいレコードが挿入されるたびに、そのレコードは現在のインデックス ノードの後続の位置に順番に追加されます。ページがいっぱいになると、新しいページが自動的に開かれます。
  • 自動増分しない主キー(ID番号や学生ID番号など)を使用する場合、主キーの値はほぼランダムであるため、新しいレコードはそれぞれ既存のインデックスページの中央に挿入されます。このとき、MySQLはデータを移動して新しいレコードを適切な位置に挿入する必要があります。ターゲットページはディスクに書き戻され、キャッシュからクリアされた後、ディスクから読み戻す必要があります。これにより、多くのオーバーヘッドが追加されます。同時に、頻繁な移動とページング操作により、大量の断片化が発生し、インデックス構造が十分にコンパクトではなくなります。OPTIMIZE TABLEを使用してテーブルを再構築し、充填ページを最適化する必要があります。

要約すると、自動インクリメント列を主キーとして使用すると、アクセス効率が最高になります。

2. 自動インクリメント列 ID は必ず連続している必要がありますか?

自動増分 ID の増分は必ずしも連続的ではありません。

まず、MySQL の自動インクリメント値の保存戦略を見てみましょう。

InnoDB エンジンの自己増分値は、実際にはメモリに保存されます。MySQL 8.0 になって初めて、「自己増分永続性」、つまり「再起動が発生した場合、テーブルの自己増分値を MySQL 再起動前の値に復元できる」機能が実現されました。具体的な状況は次のとおりです。
MySQL 5.7 以前のバージョンでは、自動インクリメント値はメモリに保存され、永続化されません。再起動のたびに、テーブルが初めて開かれたときに、自動インクリメント値の最大値 max(id) が検索され、その後、max(id)+1 がテーブルの現在の自動インクリメント値として使用されます。
たとえば、テーブルの現在のデータ行の最大 ID が 10 の場合、AUTO_INCREMENT=11 になります。このとき、id=10 の行は削除され、AUTO_INCREMENT は 11 のままです。ただし、インスタンスをすぐに再起動すると、このテーブルの AUTO_INCREMENT は再起動後に 10 になります。
つまり、MySQL を再起動すると、テーブルの AUTO_INCREMENT 値が変更される可能性があります。
MySQL 8.0 では、自動インクリメント値の変更は redo ログに記録されます。再起動時には redo ログを使用して再起動前の値を復元します。

自動増分 ID が不連続になる原因となる状況としては、次のようなものが考えられます。

  • 1. ユニークキーの競合
  • 2. トランザクションのロールバック
  • 3. 自動増分 ID を一括適用する SELECT ステートメントを挿入します。

3.自動増分IDに上限はありますか?

自動インクリメント ID は整数フィールドです。成長 ID を定義するために int 型を使用することが多いのですが、int 型には上限があるため、成長 ID にも上限があります。

次の表に、int および bigint フィールド タイプの範囲を示します。

タイプサイズ範囲(符号付き)範囲(符号なし)
整数4バイト(-2147483648,2147483647) (0,4294967295)
ビッグイント8バイト(-9223372036854775808,9223372036854775807) (0,18446744073709551615)

上記の表から、自動インクリメントフィールドが int signed 型を使用する場合、最大値は 2147483647 に達し、21 億を超え、int unsigned 型を使用する場合、最大値は 4294967295 に達し、42 億を超えることがわかります。もちろん、bigint はより広い範囲を表すことができます。

次に、自動インクリメント ID が最大値に達し、データを再度挿入すると何が起こるかをテストします。

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;insert into t values(null);// 行 4294967295 が正常に挿入されましたshow create table t;/* CREATE TABLE `t` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4294967295;*/
insert into t values(null);//キー 'PRIMARY' のエントリ '4294967295' が重複しています

実験から、自動インクリメント ID が最大値に達すると、拡張できないことがわかります。最初の挿入ステートメントでデータが正常に挿入された後、このテーブルの AUTO_INCREMENT は変更されず (4294967295 のまま)、2 番目の挿入ステートメントで同じ自動インクリメント ID 値が取得されます。挿入ステートメントを再度実行しようとすると、主キーの競合エラーが報告されます。

4. 自動インクリメント列をどのように維持すればよいですか?

メンテナンスに関しては、次の 2 つの提案が提供されています。

  • 1. フィールド タイプの選択: int unsigned タイプの使用をお勧めします。テーブル内のデータ量が非常に大きくなることが予想される場合は、代わりに bigint unsigned タイプを使用できます。
  • 2. 主キーのオーバーフローを防ぐために、大きなテーブルの自動インクリメント値にさらに注意を払います。

上記は、MySQL 自動増分 ID について知っておく必要のある詳細です。MySQL 自動増分 ID の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQLの自動増分IDの開始値を変更する方法
  • MySQL の自動増分 ID を 0 に戻す方法
  • MySQLテーブルにおける自己増分IDの問題の解決
  • MySQL 自動インクリメント ID のオーバーサイズ問題のトラブルシューティングと解決策
  • MySQL の自動増分 ID (主キー) が不足した場合の解決策
  • MySQL 自動インクリメント ID 枯渇の例
  • MySQL の自動増分 ID に関するいくつかの小さな問題の要約
  • MySQLの自己増分IDがなくなったらどうするか

<<:  Ubuntu 20.04 LTS で Java 開発環境を構成する

>>:  jsを使用してシンプルなカルーセル効果を実現する

推薦する

Ubuntu 18.04の下のディレクトリにディスクをマウントします

導入この記事では、Ubuntu 18.04 デスクトップ システムでディスクを目的のディレクトリにマ...

HTML CSS の 3 つの一般的なスタイル セレクター

1: タグセレクタータグセレクターはすべてのタグに使用されます。ここでは p を例に挙げます。つまり...

iframeを指すaタグのターゲットの名前とIDの違い

コードをコピーコードは次のとおりです。 <iframe id="myFrameId&...

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

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

画像ソーシャルネットワーキングサイトのUIアプリケーションの比較分析(図)

私たちの生活、仕事、勉強において、ソーシャル ネットワークは徐々に将来のインターネット発展のトレンド...

MySQL PXC は IST 送信のみで新しいノードを構築します (推奨)

需要シナリオ: 既存の PXC 環境には大量のデータがあります。新しく購入したサーバーをこのクラスタ...

Firebug ツールを使用して iPad でページをデバッグする

iPad でページをデバッグするにはどうすればいいですか? iOS 5 をご利用の場合、iPad の...

チャットバブル効果を実現するCSS

1. レンダリングJD効果シミュレーション効果 2. 原則高さと幅が0のボックスを用意しますこのボ...

MongoDB データベースの状態を監視する Zabbix3.4 メソッド

Mongodb には db.serverStatus() コマンドがあり、これを使用して Mongo...

ウェブページ作成のテスト問題を全て解けますか?

Web ページのデザインに関する質問です。すべてに答えられるでしょうか? 1. 単一選択の質問 (...

vuex での Getter の使用法の詳細な説明

序文Vuex を使用すると、ストア内に「ゲッター」を定義できます (これはストアの計算されたプロパテ...

jQueryでフルスクリーンスクロール効果を実現

この記事の例では、フルスクリーンスクロールを実現するためのjQueryの具体的なコードを参考までに共...

MySQL レプリケーション メカニズムの原理の説明

背景レプリケーションはデータの完全なコピーです。レプリケーションが必要な理由として、まず思い浮かぶの...

Linux bash: ./xxx: バイナリ ファイルを実行できません エラー

今日、Ubuntu 用の小さなツールを顧客に送りましたが、ユーザーはそれを受け取った後、実行できませ...

Vue でシンプルな無限ループスクロールアニメーションを実装する例

この記事では主に、Vue でシンプルな無限ループスクロールアニメーションを実装し、みんなで共有する例...