MySQL の隠し列の詳細表示

1. 主キーが存在する
2. 主キーはないが、一意のインデックスが存在する
3. 共同主キーまたは共同ユニークインデックスがある
4. 複数の一意のインデックスがある
5. 主キーとユニークインデックスが同時に存在する
6. 要件を満たす主キーまたは一意のインデックスがない

MySQL のマルチバージョン同時実行制御mvcc紹介する過程で、MySQL には行識別子、トランザクション ID、ロールバックポインターなどのいくつかの隠し列があることについて説明しました。皆さんも私と同じように、これらの隠し列の値を実際にどのように確認できるのか気になったのではないでしょうか。

この記事では、多くの非表示列のうち、行識別子DB_ROW_IDに焦点を当てます。実際には、行識別子は実際の列ではないため、非表示列と呼ぶのは正確ではありませんDB_ROW_IDは実際には空でない一意の列の別名です。その謎を解き明かす前に、公式ドキュメントを見てみましょう。

テーブルに、整数型の単一の列で構成されるPRIMARY KEYまたはUNIQUE NOT NULLインデックスがある場合、 SELECTステートメントで_rowidを使用してインデックス列を参照できます。

簡単に言えば、テーブルに主キーまたは空でない一意のインデックスがあり、それが 1 つの整数列のみで構成されている場合は、 SELECTステートメントを使用して_rowid直接照会することができ、この_rowidの値はインデックス列の値を参照します。

ドキュメントに記載されているキーワード（主キー、一意のインデックス、空でない、単一の列、数値型）に特に注意してください。次に、これらの観点から始めて、謎の隠しフィールド_rowidを調べます。

1. 主キーが存在する

まず、主キーが設定されていて数値型である場合を見てみましょう。次のステートメントを使用してテーブルを作成します。

テーブル `table1` を作成します (
  `id` bigint(20) NOT NULL 主キー、
  `name` varchar(32) デフォルト NULL
)ENGINE=InnoDB;

3 つのテストデータを挿入した後、次のクエリステートメントを実行し、 selectクエリステートメントで_rowid直接クエリします。

テーブル1から*,_rowidを選択

実行結果を確認すると、 _rowid正常にクエリできます。

主キーが設定され、主キーフィールドが数値型の場合、 _rowid主キーフィールドの値を直接参照することがわかります。 selectステートメントによってクエリできるこの状況は、明示的なrowidと呼ばれます。

前述の文書内のいくつかのキーワードを確認し、個別に分析してみましょう。主キーは非 NULL フィールドである必要があるため、主キーが非数値フィールドである場合を見てみましょう。テーブルは次のように作成されます。

テーブル `table2` を作成します (
  `id` varchar(20) NOT NULL 主キー、
  `name` varchar(32) デフォルト NULL
)ENGINE=InnoDB;

上記と同じクエリをtable2で実行すると、 _rowidをクエリできないというエラーが表示されます。これは、主キーフィールドが数値以外の型の場合、 _rowid直接クエリできないことを示しています。

2. 主キーはないが、一意のインデックスが存在する

上記の 2 種類の主キーをテストした後、テーブルに主キーが存在しないが、一意のインデックスが存在する状況を見てみましょう。まず、空でない一意のインデックスが数値フィールドに追加される場合をテストし、次のようにテーブルを作成します。

テーブル `table3` を作成します (
  `id` bigint(20) NOT NULL ユニークキー、
  `名前` varchar(32)
)ENGINE=InnoDB;

クエリは正常に実行され、 _rowid一意のインデックスが配置されている列の値を参照します。

一意のインデックスと主キーの違いは、一意のインデックスが配置されているフィールドがNULLなる可能性があることです。上記のtable3では、一意のインデックスが配置されている列にNOT NULL制約が追加されています。この制約を削除しても、 _rowid明示的にクエリすることはできますか?別のテーブルを作成しましょう。違いは、一意のインデックスが配置されている列に非 NULL 制約が追加されていないことです。

テーブル `table4` を作成します (
  `id` bigint(20) ユニークキー、
  `名前` varchar(32)
)ENGINE=InnoDB;

クエリステートメントを実行します。この場合、 _rowidを明示的にクエリすることはできません。

主キーと同様に、一意のインデックスが数値以外のフィールドに追加される場合をテストします。次に、テーブルを作成するときに、文字型フィールドに一意のインデックスが追加され、NOT NULL 制約が追加されます。

テーブル `table5` を作成します (
  `id` ビッグイント(20)、
  `name` varchar(32) NOT NULL 一意キー
)ENGINE=InnoDB;

同じクエリでは_rowidを表示できません:

上記の 3 つの状況のテスト結果に基づいて、主キーが存在しないが一意のインデックスが存在する場合、一意のインデックスが数値フィールドに追加され、フィールドに null 以外の制約が追加され、 _rowidこの一意のインデックスフィールドの値を参照する場合にのみ、 _rowid明示的にクエリできると結論付けることができます。

3. 共同主キーまたは共同ユニークインデックスがある

上記のテストでは、主キーまたは一意のインデックスを単一の列に適用しました。複合主キーまたは複合一意のインデックスを使用するとどうなるでしょうか?まずは公式ドキュメントの手順を見てみましょう。

_rowid 、単一の整数列で構成されるPRIMARY KEY PRIMARY KEY列を参照します。PRIMARY PRIMARY KEYはあっても、単一の整数列で構成されていない場合は、 _rowid使用できません。

簡単に言えば、主キーが存在し、それが数値型の列 1 つだけで構成されている場合、 _rowidの値は主キーを参照します。主キーが複数の列で構成されている場合、 _rowid使用できません。

この説明に基づいて、共同主キーの状況をテストしてみましょう。次のテーブルは、数値型フィールドの 2 つの列を共同主キーとして作成されています。

テーブル `table6` を作成します (
  `id` bigint(20) NULLではない、
  `no` bigint(20) NOT NULL、
  `名前` varchar(32),
  主キー(`id`,`no`)
)ENGINE=InnoDB;

_rowidに結果を表示できないクエリを実行します。

同様に、この理論は一意のインデックスにも適用できます。空でない一意のインデックスが単一の列で構成されていない場合、 _rowid直接クエリすることはできません。このテストのプロセスは省略されており、興味のある人は自分で試すことができます。

4. 複数の一意のインデックスがある

MySQL では、各テーブルには主キーを 1 つしか持てませんが、一意のインデックスを複数持つことができます。したがって、ルールを同時に満たす一意のインデックスが複数ある場合、 _rowidの値として参照されるのはどれでしょうか?いつものように、答えについては公式ドキュメントを見てみましょう。

それ以外の場合、 _rowid 、最初のUNIQUE NOT NULLインデックスが単一の整数列で構成されている場合、そのインデックス内の列を参照します。最初のUNIQUE NOT NULLインデックスが単一の整数列で構成されていない場合、 _rowid使用できません。

簡単に言えば、テーブル内の最初の空でない一意のインデックスが 1 つの整数型フィールドのみで構成されている場合、 _rowidこのフィールドの値を参照します。それ以外の場合、最初の NULL 以外の一意のインデックスがこの条件を満たさない場合、 _rowid使用できません。

次の表で、ルールを満たす 2 つの一意のインデックスを作成します。

テーブル `table8_2` を作成します (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL、
  `名前` varchar(32),
  ユニークキー(いいえ)
  ユニークキー(id)
)ENGINE=InnoDB;

クエリステートメントを実行した結果を見てみましょう。

_rowidの値がno列の値と同じであることがわかります。これは、 _rowid最初に作成された一意のインデックスを参照として厳密に選択することを証明しています。

したがって、テーブルに作成された最初の一意のインデックスが_rowidの参照ルールを満たしておらず、2 番目の一意のインデックスがルールを満たしている場合、この場合、 _rowid明示的にクエリできますか?このような状況では、次のようにテーブルを作成します。テーブルの最初のインデックスは結合一意インデックスで、2 番目のインデックスは単一列一意インデックスです。次に、これをテストします。

テーブル `table9` を作成します (
  `id` bigint(20) NULLではない、
  `no` bigint(20) NOT NULL、
  `名前` varchar(32),
  ユニークキー `index1`(`id`,`no`),
  ユニークキー `index2`(`id`)
)ENGINE=InnoDB;

テーブルをクエリすると、単一列の空でない一意のインデックスがあるにもかかわらず、順番に選択された最初の列が要件を満たしていないため、 _rowid直接クエリできないことがわかります。

上記の一意のインデックスを作成するためのステートメントの順序を逆にすると、 _rowid明示的に照会できます。

5. 主キーとユニークインデックスが同時に存在する

上記の例から、ユニークインデックスの定義順序によって、 _rowid適用されるインデックスが決まることがわかります。では、主キーとユニークインデックスの両方がある場合、定義順序はそれらの参照に影響するのでしょうか?

次のステートメントを使用して 2 つのテーブルを作成します。唯一の違いは、主キーと一意のインデックスが作成される順序です。

テーブル `table11` を作成します (
  `id` bigint(20) NULLではない、
  `no` bigint(20) NOT NULL、
  主キー(id)、
  ユニークキー（いいえ）
)ENGINE=InnoDB;

テーブル `table12` を作成します (
  `id` bigint(20) NULLではない、
  `no` bigint(20) NOT NULL、
  ユニークキー(id)、
  主キー (いいえ)
)ENGINE=InnoDB;

実行結果を表示します:

修飾された主キーと一意のインデックスの両方が存在する場合、作成順序に関係なく、 _rowid主キーフィールドの値よりも優先されることがわかります。

6. 要件を満たす主キーまたは一意のインデックスがない

上記では、 selectステートメントを通じて直接クエリできる_rowid 、明示的な _rowid と呼びます。他のケースでは、 _rowid明示的にクエリすることはできませんが、常に存在します。この場合は、暗黙的な_rowidと呼ぶことができます。

実際、デフォルトの主キーがない場合、 innoDB自動的に増加する_rowidとして 6 バイトの符号なし数値を生成します。したがって、最大値は2^48-1です。最大値に達した後は、0 からカウントを開始します。次に、主キーと一意のインデックスのないテーブルを作成し、このテーブルに基づいて暗黙の_rowidを調べます。

テーブル `table10` を作成します (
  `id` ビッグイント(20)、
  `名前` varchar(32)
)ENGINE=InnoDB;

まず、mysql プロセスのpidを見つける必要があります。

ps -ef | grep mysqld

ご覧のとおり、mysql プロセスのpidは 2068 です。

始める前に、いくつか準備が必要です。 innoDBでは、実際にはグローバル変数dictsys.row_idが維持されています。主キーが定義されていないテーブルは、このrow_idを共有します。データを挿入すると、このグローバルrow_id独自の主キーとして使用され、その後、このグローバル変数が 1 増加します。

次に、 gdbデバッグ関連テクノロジを使用する必要があります。gdb gdb 、実行可能ファイルのデバッグに使用できる Linux のデバッグツールです。サーバー上で、まずyum install gdbを使用して gdb をインストールします。インストールが完了したら、次のgdbコマンドを使用してrow_idを 1 に変更します。

gdb -p 2068 -ex 'p dict_sys->row_id=1' -バッチ

コマンド実行結果:

空のテーブルに 3 行のデータを挿入します。

テーブル10に値(100000001、'Hydra')を挿入します。
テーブル10に値（100000002、'トランク'）を挿入します。
テーブル10に値(100000003、'スーザン')を挿入します。

テーブル内のデータを表示します。対応する_rowidは理論的には 1 ～ 3 です。

次に、 gdbコマンドを使用してrow_id最大値2^48に変更します。これは、 dictsys.row_id最大値を超えています。

gdb -p 2068 -ex 'p dict_sys->row_id=281474976710656' -バッチ

コマンド実行結果:

テーブルにさらに 3 つのレコードを挿入します。

テーブル10に値(100000004、'キング')を挿入します。
テーブル10に値(100000005、'Queen')を挿入します。
テーブル10に値(100000006、'Jack')を挿入します。

テーブル内のすべてのデータを表示すると、最初に挿入された 3 つのデータのうち 2 つが上書きされていることがわかります。

なぜデータカバレッジが発生するのでしょうか? この結果を分析してみましょう。まず、最初のデータが挿入される前は、 _rowidは 1 であり、挿入された 3 つのデータに対応する_rowid 1、2、3 です。次の図に示すように:

_rowid手動で最大値に設定すると、次にデータが挿入されるときに、挿入された_rowidは再び 0 から始まるため、2 回目に挿入された 3 つのデータの_rowid 0、1、2 になります。挿入するデータは次のとおりです。

同じ_rowidが出現すると、新しく挿入されたデータは_rowidに従って元のデータを上書きします。このプロセスは図に示されています。

したがって、テーブル内の主キーまたは一意のインデックスが上記の要件を満たしていない場合、 innoDBが使用する暗黙の_rowid危険にさらされます。2 2^48という値は大きいですが、それでも使い果たされる可能性があります。_rowid _rowid使い果たされると、以前のレコードが上書きされます。この観点から、テーブルを構築するときには主キーを作成する必要があることも皆さんに思い出していただきたいです。そうしないと、データが上書きされる可能性があります。