Detaillierte Ansicht versteckter Spalten in MySQL

Bei der Einführung der Multi-Version Concurrency Control mvcc von MySQL haben wir erwähnt, dass es in MySQL einige versteckte Spalten gibt, wie z. B. Zeilenkennungen, Transaktions-IDs, Rollback-Zeiger usw. Ich frage mich, ob Sie genauso neugierig waren wie ich, wie Sie die Werte dieser versteckten Spalten tatsächlich sehen können.

In diesem Artikel konzentrieren wir uns auf die Zeilenkennung DB_ROW_ID unter vielen ausgeblendeten Spalten. Tatsächlich ist es nicht korrekt, die Zeilenkennung eine ausgeblendete Spalte zu nennen, da es sich nicht um eine echte Spalte handelt. DB_ROW_ID ist eigentlich ein Alias ​​für eine nicht leere eindeutige Spalte. Bevor wir das Geheimnis lüften, werfen wir einen Blick auf die offizielle Dokumentation:

Wenn eine Tabelle einen PRIMARY KEY oder UNIQUE NOT NULL -Index hat, der aus einer einzigen Spalte mit einem Integer-Typ besteht, können Sie _rowid verwenden, um in SELECT -Anweisungen auf die indizierte Spalte zu verweisen.

Einfach ausgedrückt: Wenn die Tabelle einen Primärschlüssel oder einen nicht leeren eindeutigen Index enthält und diese nur aus einer ganzzahligen Spalte besteht, können Sie die SELECT Anweisung verwenden, um _rowid direkt abzufragen, und der Wert dieser _rowid verweist auf den Wert der Indexspalte.

Achten Sie besonders auf die im Dokument erwähnten Schlüsselwörter: Primärschlüssel, eindeutiger Index, nicht leer, einzelne Spalte und numerischer Typ. Als Nächstes werden wir aus diesen Perspektiven beginnen und das mysteriöse versteckte Feld _rowid untersuchen.

1. Primärschlüssel vorhanden

Betrachten wir zunächst den Fall, in dem der Primärschlüssel festgelegt ist und einen numerischen Typ hat. Verwenden Sie die folgende Anweisung, um eine Tabelle zu erstellen:

CREATE TABLE `table1` (
  `id` bigint(20) NICHT NULL PRIMÄRSCHLÜSSEL,
  `name` varchar(32) STANDARD NULL
)ENGINE=InnoDB;

Führen Sie nach dem Einfügen der drei Testdaten die folgende Abfrageanweisung aus und fragen Sie _rowid direkt in der select Abfrageanweisung ab:

Wählen Sie *, _rowid aus Tabelle1

Überprüfen Sie die Ausführungsergebnisse. _rowid kann normal abgefragt werden:

Es ist ersichtlich, dass _rowid direkt auf den Wert des Primärschlüsselfelds verweist, wenn der Primärschlüssel festgelegt ist und das Primärschlüsselfeld vom Typ „numerisch“ ist. Diese Situation, die mit select -Anweisung abgefragt werden kann, wird als explizite rowid bezeichnet.

Lassen Sie uns einige Schlüsselwörter im zuvor erwähnten Dokument überprüfen und sie separat analysieren. Da der Primärschlüssel ein Feld ungleich Null sein muss, sehen wir uns den Fall an, in dem der Primärschlüssel ein nicht numerisches Feld ist. Die Tabelle wird wie folgt erstellt:

CREATE TABLE `table2` (
  `id` varchar(20) NICHT NULL PRIMÄRSCHLÜSSEL,
  `name` varchar(32) STANDARD NULL
)ENGINE=InnoDB;

Führen Sie dieselbe Abfrage wie oben für table2 aus. Das Ergebnis ist ein Fehler, dass _rowid nicht abgefragt werden kann. Dies beweist, dass _rowid nicht direkt abgefragt werden kann, wenn das Primärschlüsselfeld vom Typ nicht numerisch ist.

2. Kein Primärschlüssel, aber ein eindeutiger Index vorhanden

Nachdem wir die beiden oben genannten Primärschlüsseltypen getestet haben, betrachten wir die Situation, in der die Tabelle keinen Primärschlüssel enthält, aber ein eindeutiger Index vorhanden ist. Testen Sie zunächst den Fall, in dem einem numerischen Feld ein nicht leerer eindeutiger Index hinzugefügt wird, und erstellen Sie eine Tabelle wie folgt:

CREATE TABLE `table3` (
  `id` bigint(20) NICHT NULL EINZIGARTIGER SCHLÜSSEL,
  `Name` varchar(32)
)ENGINE=InnoDB;

Die Abfrage wird einwandfrei ausgeführt und _rowid verweist auf den Wert der Spalte, in der sich der eindeutige Index befindet:

Der Unterschied zwischen einem eindeutigen Index und einem Primärschlüssel besteht darin, dass das Feld, in dem sich der eindeutige Index befindet, NULL sein kann. In table3 oben wird der Spalte, in der sich der eindeutige Index befindet, eine NOT NULL Einschränkung hinzugefügt. Wenn wir diese Einschränkung löschen, können wir _rowid dann immer noch explizit abfragen? Lassen Sie uns eine weitere Tabelle erstellen. Der Unterschied besteht darin, dass der Spalte, in der sich der eindeutige Index befindet, keine Nicht-Null-Einschränkung hinzugefügt wird:

CREATE TABLE `table4` (
  `id` bigint(20) EINDEUTIGER SCHLÜSSEL,
  `Name` varchar(32)
)ENGINE=InnoDB;

Führen Sie die Abfrageanweisung aus. In diesem Fall kann _rowid nicht explizit abgefragt werden:

Ähnlich wie beim Primärschlüssel testen wir den Fall, in dem der eindeutige Index zu einem nicht numerischen Feld hinzugefügt wird. Als nächstes wird beim Erstellen einer Tabelle dem Feld vom Typ „Zeichen“ ein eindeutiger Index und eine Nicht-Null-Einschränkung hinzugefügt:

CREATE TABLE `table5` (
  `id` bigint(20),
  `name` varchar(32) NICHT NULL EINZIGARTIGER SCHLÜSSEL
)ENGINE=InnoDB;

Dieselbe Abfrage kann _rowid nicht anzeigen:

Basierend auf den Testergebnissen der oben genannten drei Situationen können wir den Schluss ziehen, dass, wenn kein Primärschlüssel, aber ein eindeutiger Index vorhanden ist, _rowid nur explizit abgefragt werden kann, wenn der eindeutige Index einem numerischen Feld hinzugefügt wird und dem Feld eine Nicht-Null-Einschränkung hinzugefügt wird und _rowid sich auf den Wert dieses eindeutigen Indexfelds bezieht.

3. Es gibt einen gemeinsamen Primärschlüssel oder einen gemeinsamen eindeutigen Index

In den obigen Tests haben wir den Primärschlüssel oder den eindeutigen Index auf eine einzelne Spalte angewendet. Was passiert, wenn ein zusammengesetzter Primärschlüssel oder ein zusammengesetzter eindeutiger Index verwendet wird? Schauen wir uns zunächst die Anweisungen in der offiziellen Dokumentation an:

_rowid bezieht sich auf die PRIMARY KEY -Spalte, wenn ein PRIMARY KEY vorhanden ist, der aus einer einzelnen Integer-Spalte besteht. Wenn ein PRIMARY KEY vorhanden ist, dieser jedoch nicht aus einer einzelnen Integer-Spalte besteht, kann _rowid nicht verwendet werden.

Einfach ausgedrückt: Wenn ein Primärschlüssel vorhanden ist und dieser nur aus einer Spalte vom numerischen Typ besteht, dann verweist der Wert von _rowid auf den Primärschlüssel. Wenn der Primärschlüssel aus mehreren Spalten besteht, ist _rowid nicht verfügbar.

Basierend auf dieser Beschreibung testen wir nun die Situation des gemeinsamen Primärschlüssels. Die folgende Tabelle wird mit zwei Spalten numerischer Felder als gemeinsamen Primärschlüssel erstellt:

CREATE TABLE `table6` (
  `id` bigint(20) NICHT NULL,
  `no` bigint(20) NICHT NULL,
  `Name` varchar(32),
  PRIMÄRSCHLÜSSEL(`id`,`nein`)
)ENGINE=InnoDB;

Führen Sie die Abfrage aus, deren Ergebnisse nicht in _rowid angezeigt werden können:

In ähnlicher Weise kann diese Theorie auch auf eindeutige Indizes angewendet werden. Wenn ein nicht leerer eindeutiger Index nicht aus einer einzelnen Spalte besteht, kann _rowid nicht direkt abgefragt werden. Dieser Testvorgang entfällt und Interessierte können es selbst ausprobieren.

4. Es gibt mehrere eindeutige Indizes

In MySQL kann jede Tabelle nur einen Primärschlüssel, aber mehrere eindeutige Indizes haben. Wenn es also mehrere eindeutige Indizes gibt, die die Regeln gleichzeitig erfüllen, auf welchen wird als Wert von _rowid verwiesen? Schauen wir uns wie üblich die offizielle Dokumentation an, um Antworten zu erhalten:

Andernfalls bezieht sich _rowid auf die Spalte im ersten UNIQUE NOT NULL -Index, wenn dieser Index aus einer einzelnen Integer-Spalte besteht. Wenn der erste UNIQUE NOT NULL -Index nicht aus einer einzelnen Integer-Spalte besteht, kann _rowid nicht verwendet werden.

Einfach ausgedrückt: Wenn der erste nicht leere eindeutige Index in der Tabelle nur aus einem Feld vom Typ „Integer“ besteht, verweist _rowid auf den Wert dieses Felds. Andernfalls ist _rowid nicht verfügbar, wenn der erste eindeutige Index ungleich Null diese Bedingung nicht erfüllt.

Erstellen Sie in der folgenden Tabelle zwei eindeutige Indizes, die beide die Regeln erfüllen:

CREATE TABLE `table8_2` (
  `id` bigint(20) NICHT NULL,
  `no` bigint(20) NICHT NULL,
  `Name` varchar(32),
  EINZIGARTIGER SCHLÜSSEL (nein),
  EINDEUTIGER SCHLÜSSEL(id)
)ENGINE=InnoDB;

Sehen Sie sich die Ergebnisse der Ausführung der Abfrageanweisung an:

Sie können sehen, dass der Wert von _rowid derselbe ist wie der Wert der Spalte no , was beweist, dass _rowid strikt den ersten erstellten eindeutigen Index als Referenz auswählt.

Wenn also der erste in der Tabelle erstellte eindeutige Index nicht den Referenzregeln von _rowid entspricht, der zweite eindeutige Index aber die Regeln erfüllt, kann in diesem Fall _rowid explizit abgefragt werden? Für diesen Fall erstellen wir eine Tabelle wie folgt. Der erste Index in der Tabelle ist ein gemeinsamer eindeutiger Index und der zweite Index ist ein einspaltiger eindeutiger Index. Dann testen wir ihn:

CREATE TABLE `table9` (
  `id` bigint(20) NICHT NULL,
  `no` bigint(20) NICHT NULL,
  `Name` varchar(32),
  EINDEUTIGER SCHLÜSSEL `index1`(`id`,`no`),
  EINDEUTIGER SCHLÜSSEL `index2`(`id`)
)ENGINE=InnoDB;

Die Abfrage der Tabelle zeigt, dass zwar ein einspaltiger, nicht leerer eindeutiger Index vorhanden ist, die erste nacheinander ausgewählte Spalte jedoch nicht den Anforderungen entspricht, sodass _rowid nicht direkt abgefragt werden kann:

Wenn die Reihenfolge der Anweisungen zum Erstellen des eindeutigen Indexes oben umgekehrt wird, kann _rowid explizit abgefragt werden.

5. Primärschlüssel und eindeutiger Index existieren gleichzeitig

Aus dem obigen Beispiel können wir erkennen, dass die Definitionsreihenfolge des eindeutigen Index bestimmt, welcher Index _rowid angewendet wird. Wenn also sowohl Primärschlüssel als auch eindeutige Indizes vorhanden sind, wird die Definitionsreihenfolge dann ihre Referenzen beeinflussen?

Erstellen Sie mit den folgenden Anweisungen zwei Tabellen. Der einzige Unterschied besteht in der Reihenfolge, in der der Primärschlüssel und der eindeutige Index erstellt werden:

CREATE TABLE `table11` (
  `id` bigint(20) NICHT NULL,
  `no` bigint(20) NICHT NULL,
  Primärschlüssel (ID),
  EINZIGARTIGER SCHLÜSSEL (nein)
)ENGINE=InnoDB;

CREATE TABLE `table12` (
  `id` bigint(20) NICHT NULL,
  `no` bigint(20) NICHT NULL,
  EINDEUTIGER SCHLÜSSEL(id),
  PRIMÄRSCHLÜSSEL (nein)
)ENGINE=InnoDB;

Sehen Sie sich die Laufergebnisse an:

Daraus lässt sich schließen, dass, wenn sowohl ein qualifizierter Primärschlüssel als auch ein eindeutiger Index vorhanden sind, _rowid Vorrang vor dem Wert des Primärschlüsselfelds hat, unabhängig von der Reihenfolge, in der sie erstellt werden.

6. Kein Primärschlüssel oder eindeutiger Index, der die Anforderungen erfüllt

Oben nennen wir _rowid , die direkt über select -Anweisung abgefragt werden kann, explizit _rowid. In anderen Fällen existiert _rowid immer, obwohl sie nicht explizit abgefragt werden kann. In diesem Fall können wir sie implizit _rowid nennen.

Tatsächlich generiert innoDB eine 6-Byte-Zahl ohne Vorzeichen als automatisch wachsende _rowid , wenn kein Standardprimärschlüssel vorhanden ist. Daher beträgt der Maximalwert 2^48-1 . Nach Erreichen des Maximalwerts wird wieder bei 0 gezählt. Als nächstes erstellen wir eine Tabelle ohne Primärschlüssel und mit einem eindeutigen Index und untersuchen basierend auf dieser Tabelle die implizite _rowid .

CREATE TABLE `table10` (
  `id` bigint(20),
  `Name` varchar(32)
)ENGINE=InnoDB;

Zuerst müssen wir die pid des MySQL-Prozesses finden:

ps -ef | grep mysqld

Wie Sie sehen können, ist die pid des MySQL-Prozesses 2068:

Bevor wir beginnen, müssen wir einige Vorbereitungen treffen. In innoDB wird tatsächlich eine globale Variable dictsys.row_id verwaltet. Tabellen, für die kein Primärschlüssel definiert ist, teilen sich diese row_id . Beim Einfügen von Daten wird diese globale row_id als eigener Primärschlüssel verwendet und dann wird diese globale Variable um 1 erhöht.

Als Nächstes müssen wir die mit gdb -Debugging verbundene Technologie verwenden. gdb ist ein Debugging-Tool unter Linux, mit dem ausführbare Dateien debuggt werden können. Installieren Sie auf dem Server zunächst gdb mit yum install gdb . Nachdem die Installation abgeschlossen ist, ändern Sie row_id mit dem folgenden gdb -Befehl in 1:

gdb -p 2068 -ex 'p dict_sys->row_id=1' -batch

Ergebnisse der Befehlsausführung:

Fügen Sie 3 Datenzeilen in eine leere Tabelle ein:

INSERT INTO table10 VALUES (100000001, 'Hydra');
INSERT INTO table10 VALUES (100000002, 'Trunks');
INSERT INTO Tabelle10 VALUES (100000003, 'Susan');

Sehen Sie sich die Daten in der Tabelle an. Die entsprechende _rowid ist theoretisch 1 bis 3:

Verwenden Sie dann den gdb -Befehl, um row_id auf den Maximalwert von 2^48 zu ändern, der den Maximalwert dictsys.row_id überschreitet:

gdb -p 2068 -ex 'p dict_sys->row_id=281474976710656' -batch

Ergebnisse der Befehlsausführung:

Fügen Sie drei weitere Datensätze in die Tabelle ein:

INSERT INTO table10 VALUES (100000004, 'König');
INSERT INTO table10 VALUES (100000005, 'Königin');
INSERT INTO table10 VALUES (100000006, 'Jack');

Betrachtet man alle Daten in der Tabelle, sieht man, dass zwei der drei zuerst eingefügten Daten überschrieben wurden:

Warum kommt es zur Datenabdeckung? Lassen Sie uns dieses Ergebnis analysieren. Erstens ist _rowid vor dem Einfügen der ersten Daten 1 und _rowid die den drei eingefügten Daten entsprechen, sind 1, 2 und 3. Wie in der folgenden Abbildung dargestellt:

Wenn _rowid manuell auf den Maximalwert eingestellt wird, beginnt die eingefügte _rowid beim nächsten Einfügen von Daten wieder bei 0, sodass _rowid der drei Daten, die beim zweiten Mal eingefügt werden, 0, 1 und 2 sein sollte. Die einzufügenden Daten sind folgende:

Wenn dieselbe _rowid erscheint, überschreiben die neu eingefügten Daten die Originaldaten entsprechend _rowid . Der Vorgang ist in der Abbildung dargestellt:

Wenn der Primärschlüssel oder der eindeutige Index in der Tabelle die oben genannten Anforderungen nicht erfüllt, ist daher die von innoDB verwendete implizite _rowid gefährdet. Obwohl der Wert von 2^48 groß ist, kann er dennoch aufgebraucht werden. Wenn _rowid aufgebraucht ist, werden die vorherigen Datensätze überschrieben. Aus diesem Blickwinkel können wir auch jeden daran erinnern, dass beim Erstellen einer Tabelle ein Primärschlüssel erstellt werden muss, da es sonst zu Überschreibungen der Daten kommen kann.

Dieser Artikel wurde basierend auf MySQL 5.7.31 getestet

Offizielle Dokumentation: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

Dies ist das Ende dieses Artikels über die spezifische Verwendung von ausgeblendeten Spalten in MySQL. Weitere relevante ausgeblendete MySQL-Spalten finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!