Zusammenfassung der MySQL-Entwicklungsstandards und -Nutzungskenntnisse

1. Namenskonventionen

1. Datenbanknamen, Tabellennamen und Feldnamen müssen Kleinbuchstaben enthalten und durch Unterstriche getrennt sein.

a)MySQL hat einen Konfigurationsparameter lower_case_table_names, der nicht dynamisch geändert werden kann. Der Standardwert des Linux-Systems ist 0, was bedeutet, dass die Bibliotheks- und Tabellennamen entsprechend der tatsächlichen Situation gespeichert werden und zwischen Groß- und Kleinschreibung unterscheiden. Wenn der Wert 1 ist, wird er in Kleinbuchstaben gespeichert, Groß-/Kleinschreibung wird nicht beachtet. Wenn 2, so speichern, aber in Kleinbuchstaben vergleichen.

b) Bei der Vermischung von Groß- und Kleinbuchstaben können mehrere Tabellen wie z. B. abc, Abc, ABC usw. nebeneinander bestehen, was leicht zu Verwechslungen führen kann.

c) Bei Feldnamen wird die Groß-/Kleinschreibung beachtet, aber sie werden nicht wirklich verwendet. Das heißt, Sie können nicht zwei Felder mit dem gleichen Namen, aber unterschiedlicher Groß-/Kleinschreibung erstellen.

d) Um Standards zu vereinheitlichen, verwenden Sie Kleinbuchstaben für Bibliotheksnamen, Tabellennamen und Feldnamen.

2. Der Bibliotheksname, Tabellenname und Feldname dürfen nicht länger als 32 Zeichen sein.

Der Bibliotheksname, Tabellenname und Feldname unterstützen maximal 64 Zeichen. Aus Gründen der Einheitlichkeit, einfachen Identifizierung und Reduzierung des Übertragungsvolumens sind jedoch nicht mehr als 32 Zeichen zulässig.

3. Verwenden Sie die INNODB-Speicher-Engine.

Die INNODB-Engine ist die Standard-Engine nach MySQL Version 5.5. Sie unterstützt Transaktionen und Sperren auf Zeilenebene, verfügt über bessere Datenwiederherstellungsfunktionen, eine bessere Parallelitätsleistung und eine bessere Unterstützung für Multi-Core, großen Speicher, SSD und andere Hardware. Sie unterstützt Hot-Data-Backups usw. Daher hat INNODB gegenüber MyISAM klare Vorteile.

4. Reservierte MySQL-Wörter sind in Bibliotheksnamen, Tabellennamen und Feldnamen verboten.

Wenn Attribute wie Bibliotheksnamen, Tabellennamen und Feldnamen reservierte Wörter enthalten, müssen SQL-Anweisungen Backticks verwenden, um die Attributnamen in Anführungszeichen zu setzen, was das Schreiben von SQL-Anweisungen und das Escapen von Variablen in Shell-Skripten sehr kompliziert macht.

5. Die Verwendung von Partitionstabellen ist verboten.

Partitionierte Tabellen stellen strenge Anforderungen an Partitionsschlüssel. Wenn partitionierte Tabellen größer werden, wird es schwieriger, DDL, SHARDING und die Wiederherstellung einzelner Tabellen auszuführen. Daher ist die Verwendung von Partitionstabellen verboten und auf der Geschäftsseite wird manuelles SHARDING empfohlen.

6. Es wird empfohlen, UNSIGNED zum Speichern nicht negativer Werte zu verwenden.

Bei gleicher Byte-Anzahl verfügt die nicht-negative Speicherung über einen größeren Wertebereich. Beispielsweise hat TINYINT einen vorzeichenbehafteten Wert von -128-127 und einen vorzeichenlosen Wert von 0-255.

7. Es wird empfohlen, INT UNSIGNED zum Speichern von IPV4 zu verwenden.

Die Verwendung von UNSINGED INT zum Speichern einer IP-Adresse benötigt 4 Bytes, während CHAR(15) 15 Bytes benötigt. Darüber hinaus verarbeiten Computer ganze Zahlen schneller als Zeichenfolgen. Verwenden Sie INT UNSIGNED statt CHAR(15), um IPv4-Adressen zu speichern, und konvertieren Sie sie mit den MySQL-Funktionen inet_ntoa und inet_aton. Für IPv6-Adressen gibt es derzeit keine Konvertierungsfunktion, daher müssen diese mit DECIMAL oder zwei BIGINTs gespeichert werden.

Zum Beispiel:

WÄHLEN SIE INET_ATON('209.207.224.40'); 3520061480
WÄHLEN SIE INET_NTOA(3520061480); 209.207.224.40

8. Es wird dringend empfohlen, TINYINT anstelle des ENUM-Typs zu verwenden.

Wenn der ENUM-Typ Enumerationswerte ändern oder hinzufügen muss, ist Online-DDL erforderlich, was kostspielig ist. Wenn der ENUM-Spaltenwert numerische Typen enthält, kann dies zu Verwirrung hinsichtlich des Standardwerts führen.

9. Verwenden Sie VARBINARY, um Zeichenfolgen mit variabler Länge oder Binärinhalte unter Berücksichtigung der Groß- und Kleinschreibung zu speichern.

VARBINARY unterscheidet standardmäßig zwischen Groß- und Kleinschreibung, hat kein Konzept von Zeichensätzen und ist schnell.

10.INT-Typ belegt 4 Byte Speicherplatz

Beispielsweise bedeutet INT(4) lediglich, dass die Anzeigezeichenbreite 4 Bit beträgt, nicht aber die Speicherlänge. Die Zahl nach der Klammer eines numerischen Typs gibt nur die Breite an und hat nichts mit dem Speicherbereich zu tun. Beispielsweise zeigt INT(3) standardmäßig 3 Ziffern an, wobei Leerzeichen hinzugefügt werden, um die Breite aufzufüllen. Wenn die Breite den Grenzwert überschreitet, ist die Anzeige normal. Python- und Java-Clients verfügen nicht über diese Funktion.

11. Unterscheiden Sie zwischen der Verwendung von DATETIME und TIMESTAMP.

Verwenden Sie zum Speichern von Jahren den Typ YEAR. Verwenden Sie zum Speichern von Daten den Typ DATE. Es wird empfohlen, zum Speichern der Zeit (auf Sekunden genau) den Typ TIMESTAMP zu verwenden.

Sowohl DATETIME als auch TIMESTAMP sind auf Sekunden genau. TIMESTAMP wird bevorzugt, da TIMESTAMP nur 4 Bytes hat, während DATETIME 8 Bytes hat. Gleichzeitig verfügt TIMESTAMP über die Eigenschaften der automatischen Zuweisung und automatischen Aktualisierung. Hinweis: In den Versionen 5.5 und früher kann, wenn eine Tabelle mehrere Zeitstempelspalten hat, höchstens eine Spalte über die Funktion zur automatischen Aktualisierung verfügen.

Wie verwende ich das automatische Zuweisungsattribut von TIMESTAMP?

a) Automatische Initialisierung und automatische Aktualisierung:

Spalte1 ZEITSTEMPEL STANDARD CURRENT_TIMESTAMP BEI UPDATECURRENT_TIMESTAMP

b) Einfach automatisch initialisieren:

Spalte1 ZEITSTEMPEL STANDARD AKTUELLER ZEITSTEMPEL

c) Automatisches Update, der Initialwert ist 0:

Spalte1 TIMESTAMP DEFAULT 0 BEI UPDATE CURRENT_TIMESTAMP

d) Der Anfangswert ist 0:

Spalte1 TIMESTAMP DEFAULT 0

12. Alle Felder sind als NOT NULL definiert.

a) Für jede Zeile der Tabelle ist für jede NULL-Spalte zusätzlicher Platz erforderlich, um sie zu identifizieren.

b) B-Baum-Indizes speichern keine NULL-Werte. Wenn das Indexfeld also NULL sein kann, verringert sich die Index-Effizienz.

c) Es wird empfohlen, anstelle von NULL-Werten 0, Sonderwerte oder leere Zeichenfolgen zu verwenden.

Tipps zur MySQL-Nutzung

1. Teilen Sie große Felder und Felder mit geringer Zugriffshäufigkeit zur Trennung heißer und kalter Daten zur Speicherung in separate Tabellen auf.

Dies trägt zur effektiven Nutzung des Caches bei, verhindert das Lesen nutzloser „kalter“ Daten, reduziert die Datenträger-E/A und stellt sicher, dass „heiße“ Daten im Speicher verbleiben, um die Cache-Trefferquote zu verbessern.

2. Es ist verboten, Passwörter im Klartext in der Datenbank zu speichern.

Verwenden Sie verschlüsselte Zeichenfolgen zum Speichern von Passwörtern und stellen Sie sicher, dass die Passwörter nicht entschlüsselt werden können. Verwenden Sie zufällige Zeichenfolgen und Salt, um die Passwortsicherheit zu gewährleisten.

3. Die Tabelle muss einen Primärschlüssel haben. Es wird empfohlen, eine UNSIGNED-Auto-Increment-Spalte als Primärschlüssel zu verwenden.

Wenn die Tabelle keinen Primärschlüssel hat, legt INNODB standardmäßig eine versteckte Primärschlüsselspalte fest. In einer Tabelle ohne Primärschlüssel ist es sehr schwierig, Datenzeilen zu finden, und dies verringert auch die Effizienz der zeilenbasierten Replikation.

4. Redundante Indizes sind verboten.

Die Indizierung ist ein zweischneidiges Schwert, das den Wartungsaufwand erhöht und den IO-Druck steigert. (a,b,c), (a,b), letzterer ist ein redundanter Index. Zur Beschleunigung und Reduzierung des Wartungsaufwands können Präfixindizes verwendet werden.

5. Eine doppelte Indizierung ist untersagt.

Primärschlüssel a;eindeutiger Index a;Doppelte Indizes erhöhen den Wartungsaufwand und belegen Speicherplatz, ohne einen Nutzen zu bringen.

6. Erstellen Sie keine Indizes für Spalten mit niedriger Kardinalität, wie etwa „Geschlecht“.

In den meisten Szenarien bietet die präzise Suche mit Indizes auf Spalten mit niedriger Kardinalität keinen Vorteil gegenüber vollständigen Tabellenscans ohne Indizes und erhöht die IO-Last.

7. Sinnvoller Einsatz von abdeckenden Indizes, um die E/A zu reduzieren und Sortierungen zu vermeiden.

Durch abdeckende Indizes können alle erforderlichen Felder aus dem Index abgerufen werden. Dadurch wird eine Rückkehr zur Tabelle für eine zweite Suche vermieden und E/A gespart.

In der INNODB-Speicher-Engine speichert der Sekundärindex (Nicht-Primärschlüsselindex, auch Hilfsindex, Sekundärindex genannt) die Zeilenadresse nicht direkt, sondern den Primärschlüsselwert.

Wenn der Benutzer eine Datenspalte abfragen muss, die nicht im Sekundärindex enthalten ist, muss er zuerst den Primärschlüsselwert über den Sekundärindex finden und dann andere Datenspalten über den Primärschlüssel abfragen, sodass die Abfrage zweimal ausgeführt werden muss. Durch abdeckende Indizes können alle erforderlichen Daten in einem Index abgerufen werden, sodass sie effizienter sind.

Beispiel SELECT email，uid FROM user_email WHERE uid=xx . Wenn uid nicht der Primärschlüssel ist, können Sie zur Verbesserung der Leistung einen Index als index(uid, email) hinzufügen.

8. Verwenden Sie IN statt OR. Die Anzahl der in der IN-Klausel der SQL-Anweisung enthaltenen Werte sollte nicht zu groß und kleiner als 1000 sein.

IN ist eine Bereichssuche. MySQL sortiert die IN-Listenwerte vor der Suche intern, was effizienter ist als OR.

9. Der Tabellenzeichensatz verwendet UTF8, und Sie können bei Bedarf die Verwendung des Zeichensatzes UTF8MB4 beantragen.

a) Der UTF8-Zeichensatz benötigt 3 Bytes zum Speichern chinesischer Zeichen und ein Byte zum Speichern englischer Zeichen.

b) UTF8 ist einheitlich und universell und es besteht keine Gefahr von verstümmelten Zeichen während der Transkodierung.

c) Wenn Sie Emoticons wie EMOJ speichern müssen, können Sie die Verwendung des Zeichensatzes UTF8MB4 beantragen.

10. Verwenden Sie UNION ALL statt UNION.

UNION ALL muss den Ergebnisset nicht sortieren.

11. Verwenden Sie nicht die Reihenfolge nach rand().

order by rand() fügt der Tabelle eine Pseudospalte hinzu, verwendet dann die Funktion rand(), um den rand()-Wert für jede Datenzeile zu berechnen und sortiert dann basierend auf der Zeile. Dadurch wird normalerweise eine temporäre Tabelle auf der Festplatte generiert, was sehr ineffizient ist. Es wird empfohlen, zuerst mit der Funktion rand() einen zufälligen Primärschlüsselwert zu erhalten und dann die Daten über den Primärschlüssel abzurufen.

12. Es wird empfohlen, eine sinnvolle Paging-Methode zu verwenden, um die Paging-Effizienz zu verbessern.

Wenn eine Paging-Anweisung ähnlich der folgenden vorliegt:

Wählen Sie * aus der Tabelle aus. Bestellen Sie nach Zeit desc Limit 10000, 10;

Diese Paging-Methode verursacht eine Menge IO, da MySQL eine Read-Ahead-Strategie verwendet.

Empfohlene Paging-Methode:

SELECT * FROM Tabelle WHERE TIME<letzte_ZEIT ORDER BY TIME DESC LIMIT 10.
SELECT * FROM Tabelle inner JOIN (SELECT id FROM Tabelle ORDER BY TIME LIMIT 10000，10) als t
VERWENDEN(id)

13.SELECT ruft nur die erforderlichen Felder ab. SELECT * ist verboten.

Reduzieren Sie den Verbrauch der Netzwerkbandbreite;

Kann abdeckende Indizes effektiv verwenden;

Änderungen an der Tabellenstruktur haben kaum Auswirkungen auf das Programm.

14. Vermeiden Sie die Verwendung von Funktionen mit unsicheren Ergebnissen wie now(), rand(), sysdate() und current_user() in SQL.

In Replikationsszenarien auf Anweisungsebene sind Master-Slave-Daten inkonsistent; von Funktionen mit unsicheren Werten generierte SQL-Anweisungen können QUERY CACHE nicht verwenden.

15. Wenden Sie geeignete Strategien zum Sharding von Datenbanken und Tabellen an. Beispielsweise tausend Lagerhäuser und zehn Tische, zehn Lagerhäuser und hundert Tische usw.

Die Einführung einer geeigneten Strategie zum Sharding von Datenbanken und Tabellen fördert die schnelle horizontale Aufteilung der Datenbank in der späteren Phase der Geschäftsentwicklung. Gleichzeitig kann das Sharding von Datenbanken die Multithread-Replikationsfunktion von MySQL effektiv nutzen.

16. Reduzieren Sie die Anzahl der Interaktionen mit der Datenbank und versuchen Sie, Batch-SQL-Anweisungen zu verwenden.

Verwenden Sie die folgende Anweisung, um die Anzahl der Interaktionen mit der Datenbank zu reduzieren:

a) EINFÜGEN ... BEIM UPDATE DES DOPPELTEN SCHLÜSSELS

b)Ersetzen durch

c) IGNORE EINFÜGEN

d) INSERT INTO VALUES()

17. Teilen Sie komplexes SQL in mehrere kleine SQLs auf, um große Transaktionen zu vermeiden.

Einfaches SQL kann problemlos den QUERY CACHE von MySQL verwenden, die Tabellensperrzeit (insbesondere bei MyISAM) reduzieren und Multi-Core-CPUs verwenden.

18. Mehrere Änderungsvorgänge an derselben Tabelle müssen zu einem Vorgang zusammengefasst werden.

Die meisten Änderungen an MySQL-Tabellen erfordern das Sperren und Neuerstellen der Tabelle. Das Sperren der Tabelle wirkt sich auf das Online-Geschäft aus. Um diese Auswirkungen zu reduzieren, müssen mehrere Änderungsvorgänge an der Tabelle zu einem Vorgang zusammengefasst werden. Um beispielsweise ein Feld b zur Tabelle t hinzuzufügen und einen Index für das vorhandene Feld aa zu erstellen,

Das übliche Vorgehen gliedert sich in zwei Schritte:

Tabelle ändern, Spalte b hinzufügen, varchar (10);

Fügen Sie dann den Index hinzu:

Tabelle ändern, Index idx_aa(aa) hinzufügen;

So gehen Sie richtig vor:

Tabelle ändern, Spalte b hinzufügen, varchar (10), Index hinzufügen, idx_aa (aa);

19. Vermeiden Sie die Verwendung gespeicherter Prozeduren, Trigger, Ansichten, benutzerdefinierter Funktionen usw.

Diese erweiterten Funktionen weisen Leistungsprobleme und viele unbekannte Fehler auf. Das Einfügen von Geschäftslogik in die Datenbank erschwert Datenbank-DDL, SCALE OUT, SHARDING usw.

20. Anwendungskonten mit Superberechtigungen sind verboten.

Sicherheit geht vor. Die Superberechtigung macht den Lesezugriff ungültig und verursacht viele merkwürdige Probleme, die schwer zu verfolgen sind.

21. Speichern Sie keine Geschäftslogik in der MySQL-Datenbank.

Die Datenbank ist ein zustandsbehafteter Dienst, der komplex ist und sich nur langsam ändert. Wenn Sie Geschäftslogik in die Datenbank einfügen, wird dies die schnelle Entwicklung des Geschäfts einschränken. Es wird empfohlen, die Geschäftslogik vorab in die Front-End- oder mittlere Logikschicht zu platzieren und die Datenbank als Speicherschicht zu verwenden, um eine Trennung von Logik und Speicher zu erreichen.

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. Wenn Sie mehr darüber erfahren möchten, schauen Sie sich bitte die folgenden Links an