Zusammenfassung der Fallstricke bei der Verwendung von Primärschlüsseln und RowIDs in MySQL

Vorwort

Wir haben vielleicht schon vom Konzept der RowID in MySQL gehört, es ist jedoch schwierig zu testen und in der Praxis anzuwenden, und es treten zwangsläufig einige Zweifel auf, beispielsweise:

• So erkennen Sie die Existenz von Rowid.
• Welche Beziehung besteht zwischen RowID und Primärschlüssel?
• Welche versteckten Gefahren birgt die Verwendung von Primärschlüsseln?
• So verstehen Sie potenzielle RowID-Engpässe und debuggen die Validierung.

In diesem Artikel werden diese Probleme mit Ihnen besprochen. Die Testumgebung basiert auf MySQL Version 5.7.19.

Frage 1: Wie erkennt man die Existenz einer Rowid?

Lassen Sie uns dies anhand eines Falls veranschaulichen.

Ich erinnere mich, dass ich eines Tages beim Zählen der Sicherungsdaten eine SQL-Anweisung schrieb. Als ich das Ausführungsergebnis sah, stellte ich fest, dass die SQL-Anweisung nicht vollständig war. Nachdem ich die statistische Arbeit abgeschlossen hatte, bereitete ich mich darauf vor, diese SQL-Anweisung zu analysieren.

mysql> wähle Backup-Datum, Anzahl (*) Stückzahl aus Redis-Backup-Ergebnis;

+-------------+----------+

| Sicherungsdatum | Stücknummer |

+-------------+----------+

| 14.08.2018 |

+-------------+----------+

1 Zeile im Satz (0,03 Sek.)

Den Besonderheiten des Unternehmens zufolge dürfte es an einem Tag nicht so viele Datensätze geben. Das ist offensichtlich falsch. Was ist schiefgelaufen?

Ich habe mir das SQL genau angesehen und festgestellt, dass es keine Gruppierung nach gab. Wir haben zufällig 10 Datenstücke gefunden.

mysql> wähle Backup_Datum aus Redis_Backup_Result-Limit 10;

+-------------+

| Sicherungsdatum |

+-------------+

| 14.08.2018 |

| 14.08.2018 |

| 14.08.2018 |

| 15.08.2018 |

| 15.08.2018 |

| 15.08.2018 |

| 15.08.2018 |

| 15.08.2018 |

| 15.08.2018 |

| 15.08.2018 |

+-------------+

10 Zeilen im Satz (0,00 Sek.)

In früheren Versionen ist der Datenbankparameter sql_mode standardmäßig leer und dieser Teil wird nicht überprüft. Aus grammatikalischer Sicht ist dies zulässig; in späteren Versionen, z. B. Version 5.7 und höher, wird dies jedoch nicht unterstützt. Daher ist die Lösung sehr einfach. Nach dem Hinzufügen von group by ist das Ergebnis wie erwartet.

mysql> wähle Backup_Datum, Anzahl(*) Stückzahl aus Redis_Backup_Result-Gruppe nach Backup_Datum;

+-------------+----------+

| Sicherungsdatum | Stücknummer |

+-------------+----------+

| 14.08.2018 | 3 |

| 15.08.2018 | 121 |

| 16.08.2018 | 184 |

| 17.08.2018 | 3284 |

| 18.08.2018 | 7272 |

| 19.08.2018 | 7272 |

| 20.08.2018 | 7272 |

| 21.08.2018 | 7272 |

| 22.08.2018 | 8226 |

+-------------+----------+

9 Reihen im Satz (0,06 Sek.)

Aber ich bin neugierig auf die Analyselogik. Es scheint, dass SQL die erste Zeile analysiert und dann die count(*)-Operation ausgibt. Offensichtlich sind das Informationen, die nicht aus dem Ausführungsplan gewonnen werden können.

Wenn wir unsere Denkweise ändern, können wir sehen, dass diese Tabelle mehr als 40.000 Datensätze enthält.

mysql> wähle count(*) aus redis_backup_result;

+----------+

| Anzahl(*) |

+----------+

|40944|

+----------+

1 Zeile im Satz (0,01 Sek.)

Zur Überprüfung können wir _rowid verwenden, um eine vorläufige Überprüfung durchzuführen.

InnoDB-Tabellen generieren einen automatisch wachsenden Primärschlüssel von 6 Bytes, wenn kein Standardprimärschlüssel vorhanden ist. Sie können ihn mit select _rowid from table wie folgt abfragen:

mysql> wähle _rowid aus redis_backup_result-Limit 5;

+--------+

| _rowid |

+--------+

| 117 |

| 118 |

| 119 |

| 120 |

| 121 |

+--------+

5 Zeilen im Satz (0,00 Sek.)

Dann können wir eine erste Idee umsetzen.

mysql> wähle _rowid,count(*)aus redis_backup_result;

+--------+----------+

| _rowid | Anzahl(*) |

+--------+----------+

| 117 | 41036 |

+--------+----------+

1 Zeile im Satz (0,03 Sek.)

Dann können wir es weiter verbessern, indem wir rownum verwenden. Natürlich wird diese Funktion in MySQL nicht nativ unterstützt und muss indirekt implementiert werden.

mysql> SELECT @rowno:=@rowno+1 als rowno,r._rowid aus redis_backup_result

r, (Auswahl @rowno:=0) t-Limit 20;

+----------+--------+

| _rowid |

+----------+--------+

| 1 | 117 |

| 2 | 118 |

| 3 | 119 |

| 4 | 120 |

| 5 | 121 |

| 6 | 122 |

| 7 | 123 |

| 8 | 124 |

| 9 | 125 |

| 10 | 126 |

| 11 | 127 |

| 12 | 128 |

| 13 | 129 |

| 14 | 130 |

| 15 | 131 |

| 16 | 132 |

| 17 | 133 |

| 18 | 134 |

| 19 | 135 |

| 20 | 136 |

+----------+--------+

20 Zeilen im Satz (0,00 Sek.)

Schreiben Sie eine vollständige Aussage wie folgt:

mysql> SELECT @rowno:=@rowno+1 als rowno,r._rowid,backup_date,count(*)

von redis_backup_result r, (wählen Sie @rowno:=0) t;

+-------+--------+----------+----------+

| Rowno | _Rowid | Sicherungsdatum | Anzahl(*) |

+-------+--------+----------+----------+

| 1 | 117 | 14.08.2018 | 41061 |

+-------+--------+----------+----------+

1 Zeile im Satz (0,02 Sek.)

In diesem Fall können wir deutlich erkennen, dass es sich um den Datensatz in Zeile 1 handelt. Anschließend wird die Operation count(*) ausgeführt.

Unser Ziel ist es natürlich, einige der Beziehungen zwischen RowID und Primärschlüssel zu verstehen, daher untersuchen wir auch die versteckten Gefahren bei der Verwendung des Primärschlüssels.

Frage 2: Welche Beziehung besteht zwischen RowID und Primärschlüssel?

Beim Studium der Indexspezifikationen der MySQL-Entwicklungsspezifikationen wurde ein wichtiger Punkt hervorgehoben: Es wird empfohlen, dass jede Tabelle einen Primärschlüssel hat. Lassen Sie uns hier kurz analysieren, warum?

Zusätzlich zu den Spezifikationen in Bezug auf Speichermethoden werden Tabellen in der InnoDB-Speicher-Engine in der Reihenfolge der Primärschlüssel gespeichert, die wir als gruppierte Indextabellen oder indexorganisierte Tabellen (IOTs) bezeichnen. Die Referenzbasis für den Primärschlüssel in der Tabelle lautet wie folgt:

• Erstellen Sie explizit einen Primärschlüssel.
• Bestimmen Sie, ob in der Tabelle ein nicht leerer eindeutiger Index vorhanden ist. Wenn ja, handelt es sich um den Primärschlüssel.
• Wenn keine der oben genannten Bedingungen erfüllt ist, wird ein 6-Byte-Bigint-Wert ohne Vorzeichen generiert.

Aus dem Obigen können wir ersehen, dass MySQL einen Wartungsmechanismus für Primärschlüssel hat und einige allgemeine Indizes auch entsprechende Auswirkungen haben. Beispielsweise sind eindeutige Indizes, nicht eindeutige Indizes, abdeckende Indizes usw. alle Hilfsindizes (Sekundärindex, auch Sekundärindex genannt). Aus Sicht der Speicherung enthält die Sekundärindexspalte standardmäßig die Primärschlüsselspalte. Wenn der Primärschlüssel zu lang ist, nimmt der Sekundärindex auch viel Platz ein.

Frage 3: Welche Gefahren sind bei der Verwendung von Primärschlüsseln verborgen?

Dies wirft das wichtigste Leistungsproblem auf, das in der Branche sehr häufig auftritt. Dies ist kein einzelnes Problem und erfordert eine kontinuierliche Transformation in Richtung MySQL, um technischen und geschäftlichen Wert zu kombinieren. Ich habe in vielen Unternehmen Autoinkrementspalten eingerichtet gesehen, aber in den meisten Fällen hat diese Autoinkrementspalte keine tatsächliche geschäftliche Bedeutung. Obwohl die Primärschlüsselspalte die Eindeutigkeit der ID garantiert, können Geschäftsentwickler keine direkten Abfragen basierend auf der Autoinkrementspalte des Primärschlüssels durchführen. Daher müssen sie neue Geschäftsattribute finden, eine Reihe eindeutiger Indizes, nicht eindeutiger Indizes usw. hinzufügen, sodass es zu Abweichungen zwischen den von uns eingehaltenen Spezifikationen und der Art und Weise kommt, wie das Unternehmen sie verwendet.

Aus einer anderen Perspektive ist unser Verständnis von Primärschlüsseln voreingenommen. Wir können nicht einfach davon ausgehen, dass Primärschlüssel ganzzahlige Typen sein müssen, die bei 1 beginnen. Wir müssen sie im Zusammenhang mit Geschäftsszenarien betrachten. Unser Personalausweis ist beispielsweise ein gutes Beispiel. Die Ausweisnummer ist zum Abrufen und Verwalten in mehrere Abschnitte unterteilt. Oder die Seriennummer, die man beim Auswärtsessen erhält, enthält bestimmte Geschäftsattribute, die für uns eine gute Referenz sind, um die Verwendung des Geschäfts zu verstehen.

Frage 4: Wie kann man den potenziellen Engpass von Rowid verstehen und debuggen und überprüfen?

Wir wissen, dass die Zeilen-ID nur 6 Bytes lang ist, der Maximalwert also 2^48 beträgt. Sobald die Zeilen-ID diesen Wert überschreitet, wird sie also weiter ansteigen. Gibt es in diesem Fall eine versteckte Gefahr?

Reden ohne Übung ist leeres Gerede. Um dies zu verdeutlichen, können wir einen Test durchführen.

1) Wir erstellen eine Tabelle test_inc ohne Index.

Tabelle erstellen test_inc(id int) engine=innodb;

2) Holen Sie sich die entsprechende Prozessnummer über ps -ef|grep mysql und verwenden Sie gdb, um mit der Debugging-Konfiguration zu beginnen. Denken Sie daran! Dies sollte Ihre eigene Testumgebung sein.

[root@dev01 mysql]# gdb -p 3132 -ex 'p dict_sys->row_id=1' -batch

[Neues LWP 3192]

[Neues LWP 3160]

[Neues LWP 3159]

[Neues LWP 3158]

[Neues LWP 3157]

[Neues LWP 3156]

[Neues LWP 3155]

[Neues LWP 3154]

[Neues LWP 3153]

[Neues LWP 3152]

[Neues LWP 3151]

[Neues LWP 3150]

[Neues LWP 3149]

[Neues LWP 3148]

[Neues LWP 3147]

[Neues LWP 3144]

[Neues LWP 3143]

[Neues LWP 3142]

[Neues LWP 3141]

[Neues LWP 3140]

[Neues LWP 3139]

[Neues LWP 3138]

[Neues LWP 3137]

[Neues LWP 3136]

[Neues LWP 3135]

[Neues LWP 3134]

[Neues LWP 3133]

[Thread-Debugging mit libthread_db aktiviert]

0x00000031ed8df283 in poll () von /lib64/libc.so.6

1 USD = 1

3) Wir führen einige grundlegende Prüfungen durch, um die Anweisung zur Tabellenerstellung abzurufen und sicherzustellen, dass der Test den Erwartungen entspricht.

mysql> zeigen erstellen Tabelle test_inc\G

*************************** 1. Reihe ***************************

  Tabelle: test_inc

Tabelle erstellen: CREATE TABLE `test_inc` (

 `id` int(11) STANDARD NULL

) ENGINE=InnoDB STANDARD-CHARSET=utf8

1 Zeile im Satz (0,00 Sek.)

4) Fügen Sie einige Daten ein, sodass die Zeilen-ID weiter zunimmt.

mysql> in test_inc Werte einfügen(1),(2),(3);

Abfrage OK, 3 Zeilen betroffen (0,08 Sek.)

Datensätze: 3 Duplikate: 0 Warnungen: 0

5) Wir setzen die Zeilen-ID zurück und passen sie auf 2^48 an

mysql> wähle Leistung (2,48);

+-----------------+

| Leistung (2,48) |

+-----------------+

|281474976710656 |

+-----------------+

1 Zeile im Satz (0,00 Sek.)

 

[root@dev01 mysql]# gdb -p 3132 -ex 'p dict_sys->row_id=281474976710656' -batch

. . .

. . .

[Thread-Debugging mit libthread_db aktiviert]

0x00000031ed8df283 in poll () von /lib64/libc.so.6

1 USD = 281474976710656

6) Schreiben Sie weiterhin einige Daten. Beispielsweise schreiben wir drei Datenzeilen: 4, 5 und 6.

mysql> in test_inc Werte einfügen(4),(5),(6); 

Abfrage OK, 3 Zeilen betroffen (0,07 Sek.)

Datensätze: 3 Duplikate: 0 Warnungen: 0

7) Überprüfen Sie die Datenergebnisse und stellen Sie fest, dass die Zeilen 1 und 2 überschrieben wurden.

mysql> wähle * aus test_inc;

+------+

|Ich würde|

+------+

| 4 |

| 5 |

| 6 |

| 3 |

+------+

4 Zeilen im Satz (0,00 Sek.)

Daraus können wir ersehen, dass nach der Inkrementierung der Zeilen-ID immer noch ein Nutzungsengpass besteht. Natürlich ist die Wahrscheinlichkeit dafür sehr gering. Der Wert der Auto-Increment-Spalte muss 281 Billionen erreichen, was eine sehr große Zahl ist. Aus funktionaler Sicht ist es sinnvoller, einen Fehler beim Schreiben doppelter Werte auszulösen.

Mit dem Primärschlüssel scheint der oben genannte Engpass nicht mehr zu bestehen.

>>>> Referenzen

Rowid-Debugging bezieht sich auf Ding Qis Blog

https://www.jb51.net/article/172262.htm

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.