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を使用してシンプルなカルーセル効果を実現する

推薦する

CSS の複雑なセレクターと CSS のフォントスタイルと色属性の詳細な説明

これまでに CSS の基本的なセレクターをいくつか学習しましたが、今日は CSS の複雑なセレクター...

Vue.js フロントエンドプロジェクト向け多言語ソリューションのアイデアと実践

目次1. 通常どのようなコンテンツを処理する必要があるか2. 基本的な考え方3. 具体的な実践の詳細...

Linuxにおけるumaskコマンドの使用原理と計算方法の詳しい解説

目次umask umaskの使用法原理1. umask値2. ファイルディレクトリの最大権限3. 従...

VC6.0をWIN10にインストールすると使用できない問題の解決方法

VC6.0は確かに古すぎるVC6.0は昔の開発ツールです。現在のwin10では対応していません。しか...

画像ボタンをフォームのリセットボタンとして使用する方法

フォームを作成するときに、送信ボタンとリセットボタンを配置することがよくあります。ページの外観を考慮...

MySql インポート CSV ファイルまたはタブ区切りファイル

別のライブラリから別のライブラリにデータをインポートする必要がある場合があり、このデータは CSV ...

MySQLは、where in()順序ソートを実装するためにfind_in_set()関数を使用します。

この記事では、MySQL で find_in_set() 関数を使用して where in() の順...

MySQLスケーラブル設計の基本原則

目次序文1. スケーラビリティとは何ですか?スケールアウトの利点:スケールアウトのデメリット:スケー...

弾幕効果を実現するためのjQuery

この記事では、弾幕効果を実現するためのjQueryの具体的なコードを参考までに共有します。具体的な内...

Vueプロジェクトでよく使われる実践的なスキルのまとめ

目次序文1. マルチレベルのデータとイベントの配信には$attrsと$listenersを使用する2...

Windows Server 2008 R2 リモート デスクトップのポート 3389 を変更する方法

Windows サーバー リモート デスクトップのデフォルトのポート番号は 3389 です。職場でサ...

HTML 終了タグの問題と W3C 標準

W3C の原則によれば、XML の各開始タグには対応する終了タグが必要です。つまり、<html...

MySQL スロークエリを通じて MySQL のパフォーマンスを最適化する方法

アクセス数が増えると、MySQL データベースへの負荷が増大します。MySQL アーキテクチャを使用...

Linux における nohup と & の使い方と違いの詳細な説明

例:例として、Python コード loop_hello.py を使用します。このコードは、ループ回...