Kennen Sie die häufigsten MySQL-Designfehler?

Dank der Entwicklung des Internets können wir die fragmentierte Zeit, die wir beispielsweise beim Warten auf den Bus oder bei der Fahrt mit der U-Bahn verbringen, nutzen, um jederzeit und überall zu lernen und Informationen abzurufen. Gleichzeitig ermöglicht das entwickelte Internet den Menschen, ihr Wissen schnell zu teilen und mit Freunden zu diskutieren, die dieselben Interessen und Bedürfnisse haben.

Durch die allzu bequeme Weitergabe von Wissen ist dieses jedoch auch vielfältiger geworden, sodass die Menschen leicht an falsche Informationen gelangen können. Die meisten dieser Fehler sind auf die schnelle Entwicklung der Technologie und den Mangel an Freizeit zur Aktualisierung bereits veröffentlichter Inhalte zurückzuführen. Um Missverständnissen bei späteren Lernenden vorzubeugen, werfen wir heute einen Blick auf einige häufige Fehler in MySQL-Designspezifikationen.

Primärschlüsseldesign

Falsche Designspezifikation: Es wird empfohlen, einen automatisch inkrementierenden ID-Wert als Primärschlüssel zu verwenden und nicht UUID, MD5, HASH oder Zeichenfolge als Primärschlüssel zu verwenden.

Diese Designspezifikation ist in vielen Artikeln zu finden. Zu den Vorteilen von Auto-Increment-Primärschlüsseln gehören der geringe Platzbedarf, die Ordnung und die einfache Verwendung.

Schauen wir uns zunächst die Nachteile des Auto-Increment-Primärschlüssels an:

• Da der Autoinkrementwert auf der Serverseite generiert wird, muss er durch eine Autoinkrement-AI-Sperre geschützt werden. Wenn zu diesem Zeitpunkt eine große Anzahl von Einfügeanforderungen vorliegt, kann es zu einem Leistungsengpass kommen, der durch den Autoinkrementwert verursacht wird, sodass ein gleichzeitiges Leistungsproblem auftritt.
• Der als Primärschlüssel verwendete selbstinkrementierte Wert kann nur in der aktuellen Instanz eindeutig sein, nicht jedoch global eindeutig, was seine Verwendung in einer verteilten Architektur unmöglich macht.
• Öffentliche Datenwerte können leicht zu Sicherheitsproblemen führen. Wenn unsere Produkt-ID ein automatisch inkrementierter Primärschlüssel ist, können Benutzer Produkte erhalten, indem sie den ID-Wert ändern. In schwerwiegenden Fällen können sie erfahren, wie viele Produkte in unserer Datenbank gespeichert sind.
• Leistungsprobleme, die durch MGR (MySQL Group Replication) verursacht werden können;

Da der Autoinkrementwert auf dem MySQL-Server generiert wird, muss er durch eine Autoinkrement-AI-Sperre geschützt werden. Wenn zu diesem Zeitpunkt eine große Anzahl von Einfügeanforderungen vorliegt, kann es durch das Autoinkrement zu einem Leistungsengpass kommen. Beispielsweise wird in der MySQL-Datenbank der Parameter innodb_autoinc_lock_mode verwendet, um die Dauer zu steuern, für die die Auto-Increment-Sperre aufrechterhalten wird. Obwohl wir den Parameter innodb_autoinc_lock_mode anpassen können, um die maximale Leistung der automatischen Inkrementierung zu erzielen, gibt es immer noch andere Probleme. Daher wird in gleichzeitigen Szenarien empfohlen, UUID als Primärschlüssel zu verwenden oder den vom Unternehmen generierten Primärschlüssel anzupassen.

Wir können die Funktion UUID() direkt in MySQL verwenden, um den UUID-Wert zu erhalten.

MySQL> UUID auswählen();
+--------------------------------------+
| UUID() |
+--------------------------------------+
| 23ebaa88-ce89-11eb-b431-0242ac110002 |
+--------------------------------------+
1 Zeile im Satz (0,00 Sek.)

Es ist zu beachten, dass beim Speichern der Zeit die UUID in umgekehrter Reihenfolge entsprechend den Zeitbits gespeichert wird, d. h. die Bits mit niedriger Zeit werden vorne und die Bits mit hoher Zeit am Ende gespeichert. Das heißt, die ersten 4 Bytes der UUID ändern sich mit der Zeit weiterhin „zufällig“ und steigen nicht monoton an. Nicht zufällige Werte erzeugen beim Einfügen diskrete E/A und verursachen somit einen Leistungsengpass. Dies ist auch der größte Nachteil von UUID im Vergleich zur Auto-Inkrementierung.

Um dieses Problem zu lösen, wurde in MySQL 8.0 die Funktion UUID_TO_BIN eingeführt, die den UUID-String konvertieren kann:

• Durch die Verwendung von Parametern, um das Zeitbit mit dem höchsten Wert an den Anfang zu setzen, wird das Unordnungsproblem beim Einfügen der UUID gelöst.
• Unnötige Zeichenfolge „-“ entfernt, um Speicherplatz zu sparen;
• Die Zeichenfolge wird zur Speicherung in einen Binärwert umgewandelt und der Speicherplatz wird schließlich von 36 Byte auf 16 Byte verkürzt.

Als nächstes konvertieren wir die vorherige UUID-Zeichenfolge 23ebaa88-ce89-11eb-b431-0242ac110002 durch die Funktion UUID_TO_BIN und der Binärwert ist wie folgt:

MySQL> WÄHLEN SIE UUID_TO_BIN('23ebaa88-ce89-11eb-b431-0242ac110002',TRUE) als UUID_BIN;
+------------------------------------+
| UUID_BIN |
+------------------------------------+
| 0x11EBCE8923EBAA88B4310242AC110002 |
+------------------------------------+
1 Zeile im Satz (0,01 Sek.)

Darüber hinaus bietet MySQL 8.0 auch die Funktion BIN_TO_UUID, die das Umkehren von Binärwerten in UUID-Zeichenfolgen unterstützt.

Obwohl es vor MySQL 8.0 keine UUID_TO_BIN/BIN_TO_UUID-Funktion gibt, kann das Problem dennoch durch Anpassen der Funktion gelöst werden. Für die Anwendungsschicht können Sie entsprechende Funktionen gemäß Ihrer eigenen Programmiersprache schreiben.

Natürlich sind viele Studenten auch besorgt über die Leistung und den Speicherplatz, den UUID belegt. Hier habe ich auch einige relevante Einfügungsleistungstests durchgeführt und die Ergebnisse sind in der folgenden Tabelle aufgeführt:

Wie Sie sehen, weist die von MySQL 8.0 bereitgestellte sortierte UUID die beste Leistung auf, sogar besser als die Auto-Increment-ID. Da das Ergebnis der UUID_TO_BIN-Konvertierung 16 Bytes beträgt, also nur 8 Bytes mehr als die Auto-Increment-ID, ist der endgültige Speicherplatz nur 3 G größer als die Auto-Increment-ID.

Und da UUID eine globale Eindeutigkeit garantieren kann, sind die Vorteile der Verwendung von UUID weitaus größer als bei einer selbstinkrementierenden ID. Sie sind möglicherweise daran gewöhnt, die automatische Inkrementierung als Primärschlüssel zu verwenden. In gleichzeitigen Szenarien empfiehlt es sich jedoch, einen global eindeutigen Wert wie UUID als Primärschlüssel zu verwenden.

Obwohl UUID gut ist, muss der Primärschlüssel in einem verteilten Szenario natürlich einige zusätzliche Informationen hinzufügen, um die Abfrageeffizienz nachfolgender Sekundärindizes sicherzustellen. Es wird empfohlen, den Primärschlüssel entsprechend der Geschäftsanpassung zu generieren. Wenn die Parallelität und das Datenvolumen jedoch nicht so groß sind, wird die Verwendung einer selbstinkrementierenden UUID empfohlen. Denken Sie nicht, dass UUID nicht als Primärschlüssel verwendet werden kann.

Gestaltung von Finanzfeldern

Falsche Entwurfsspezifikation: Für Finanzdaten muss der Dezimaltyp verwendet werden, da sowohl Float als auch Double ungenaue Gleitkommatypen sind, während Decimal ein genauer Gleitkommatyp ist. Daher wird beim Entwerfen von Finanzfeldern wie Benutzerguthaben und Produktpreisen im Allgemeinen der Dezimaltyp verwendet, der auf den Cent genau sein kann.

In den Designstandards für Massen-Internetdienste wird der Typ DECIMAL jedoch nicht empfohlen. Stattdessen wird empfohlen, DECIMAL in einen Integer-Typ zu konvertieren. Mit anderen Worten: Aus finanziellen Gründen empfiehlt es sich, Daten eher in Cent als in Yuan zu speichern. Beispielsweise wird 1 Yuan als Ganzzahltyp 100 in der Datenbank gespeichert.

Im Folgenden sind die Vorteile des Bigint-Typs aufgeführt:

• Dezimal ist eine binäre Kodierungsmethode und die Berechnungseffizienz ist nicht so gut wie die von Bigint.
• Wenn Bigint verwendet wird, ist das Feld ein Feld mit fester Länge, das effizient gespeichert werden kann. Dezimalstellen werden durch die definierte Breite bestimmt. Beim Datendesign bietet die Speicherung mit fester Länge eine bessere Leistung.
• Um Mengen aufgeteilt in Einheiten zu speichern, können Sie mit bigint auch Mengen im Gigabyte-Bereich speichern, was völlig ausreichend ist.

Verwendung von Aufzählungsfeldern

Schlechte Designpraxis: Vermeiden Sie die Verwendung von ENUM-Typen

Wenn in früheren Entwicklungsprojekten Felder wie das Geschlecht des Benutzers, ob das Produkt im Regal steht, ob der Kommentar ausgeblendet ist usw. auftraten, wurden die Felder einfach als tinyint entworfen und dann in den Notizen die Felder mit 0 und 1 für den Status vermerkt.

Auch die Probleme dieser Konstruktion liegen auf der Hand:

• Unklarer Ausdruck: Diese Tabelle wurde möglicherweise von anderen Kollegen entworfen. Wenn Sie keinen tiefen Eindruck davon haben, müssen Sie jedes Mal die Feldkommentare lesen und manchmal sogar in die Datenbank gehen, um die Bedeutung des Felds beim Codieren zu bestätigen.
• Schmutzige Daten: Obwohl die eingefügten Werte durch Code auf Anwendungsebene eingeschränkt werden können, können die Werte dennoch über SQL und Visualisierungstools geändert werden.

Für diese Art von festen Optionswertfeldern wird empfohlen, den Aufzählungszeichenfolgentyp ENUM sowie den strikten Modus von SQL_MODE zu verwenden.

In MySQL 8.0.16 und späteren Versionen können Sie den Check-Constraint-Mechanismus direkt verwenden, ohne den Enumerationsfeldtyp zu verwenden.

Darüber hinaus verwenden wir beim Definieren von Aufzählungswerten normalerweise einzelne Zeichen wie „Y“ und „N“, was nicht viel Platz beansprucht. Wenn die Optionswerte jedoch nicht festgelegt sind und sich im Laufe der Geschäftsentwicklung erhöhen können, ist die Verwendung von Aufzählungsfeldern nicht zu empfehlen.

Indexnummernbegrenzung

Falsche Designspezifikation: Begrenzen Sie die Anzahl der Indizes für jede Tabelle. Eine Tabelle kann nicht mehr als 5 Indizes haben.

Es gibt keine Begrenzung für die Anzahl der Indizes für eine einzelne MySQL-Tabelle. Wenn ein spezieller Bedarf für Geschäftsabfragen besteht, können Sie diese erstellen. Seien Sie nicht abergläubig, was die Begrenzung betrifft.

Verwenden von Unterabfragen

Schlechte Designpraxis: Unterabfragen vermeiden

Tatsächlich ist diese Spezifikation für ältere MySQL-Versionen korrekt. Da frühere Versionen der MySQL-Datenbank nur eine eingeschränkte Optimierung für Unterabfragen bieten, verlangen wir in vielen OLTP-Geschäftsszenarien, dass Online-Unternehmen Unterabfragen so weit wie möglich vermeiden.

In MySQL 8.0 wurde die Optimierung von Unterabfragen jedoch erheblich verbessert, sodass Sie Unterabfragen in der neuen Version von MySQL sicher verwenden können.

Unterabfragen sind für Menschen leichter zu verstehen als JOIN. Beispielsweise möchten wir jetzt die Anzahl der Studierenden überprüfen, die im Jahr 2020 keine Artikel veröffentlicht haben.

ANZAHL AUSWÄHLEN(*)
VON Benutzer
WO id nicht in (
    SELECT Benutzer-ID
    vom Blog
    wobei Veröffentlichungszeit >= "2020-01-01" UND Veröffentlichungszeit <= "2020-12-31"
)

Wie Sie sehen, ist die Logik der Unterabfrage sehr klar: Nicht IN wird verwendet, um die Benutzer der Artikeltabelle abzufragen.

Wenn wir den Left Join verwenden, um zu schreiben

SELECT-Anzahl(*)
VON Benutzer LINKS TEILNEHMEN Blog
EIN user.id = blog.user_id und blog.publish_time >= "2020-01-01" und blog.publish_time <= "2020-12-31"
wobei blog.user_id NULL ist;

Es zeigt sich, dass LEFT JOIN zwar auch die oben genannten Anforderungen erfüllen kann, aber nicht leicht zu verstehen ist.

Wir verwenden „explain“, um die Ausführungspläne der beiden SQL-Anweisungen anzuzeigen, und stellen fest, dass sie identisch sind.

Aus der obigen Abbildung ist deutlich ersichtlich, dass sowohl die Unterabfrage als auch der LEFT JOIN schließlich in einen Left Hash Join umgewandelt werden, sodass die Ausführungszeit der beiden obigen SQL-Anweisungen gleich ist. Das heißt, in MySQL 8.0 optimiert der Optimierer die IN-Unterabfrage automatisch in den besten JOIN-Ausführungsplan, was die Leistung erheblich verbessert.

Zusammenfassen

Nach dem Lesen des vorherigen Inhalts glaube ich, dass jeder ein neues Verständnis von MySQL hat. Diese häufigen Fehler können wie folgt zusammengefasst werden:

• UUID kann auch als Primärschlüssel verwendet werden. Die Leistung einer automatisch inkrementierenden UUID ist besser als die eines automatisch inkrementierenden Primärschlüssels, und der zusätzlich benötigte Speicherplatz ist vernachlässigbar.
• Zusätzlich zur Dezimalzahl können Sie für Finanzfelder auch Bigint ausprobieren, um in Einheiten unterteilte Daten zu speichern.
• Für Felder mit festen Optionwerten wird empfohlen, vor MySQL 8 Aufzählungsfelder und nach MySQL 8 Prüffunktionseinschränkungen zu verwenden. Verwenden Sie nicht 0, 1 oder 2 zur Darstellung
• Für die Anzahl der Indizes in einer Tabelle gibt es keine Begrenzung; sie darf 5 nicht überschreiten. Sie können sie je nach Geschäftsbedingungen hinzufügen oder löschen.
• MySQL8 verfügt über optimierte Unterabfragen und kann bedenkenlos verwendet werden.

Dies ist das Ende dieses Artikels über häufige fehlerhafte MySQL-Designspezifikationen. Weitere relevante fehlerhafte MySQL-Designspezifikationen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!