MySQL-Datenbank-Entwicklungsspezifikationen [empfohlen]

Vor Kurzem haben wir SQL zur Optimierung online erfasst, während wir die Entwicklungsspezifikationen so organisiert haben, dass die Anzahl neuer problematischer SQLs, die in die Produktionsdatenbank gelangen, minimiert wird. Heute hatten wir auch eine Schulung zur Unternehmensentwicklung. Ich werde die PPT hier nicht hochladen, aber es waren ungefähr zehn SQL-Produktionsfälle darin. Da die meisten Spezifikationen universell sind, haben wir auch die Spezifikationen von Qunar und Ganji herangezogen. Beim Schreiben dieses Artikels basierte jede Spezifikation jedoch auf einem Negativbeispiel, das als Referenz in der Arbeit verwendet wurde. Wenn die Zeit es erlaubt, werde ich einen Teil davon herausgreifen, um die Prinzipien zu analysieren oder sie anhand von Fällen zu veranschaulichen.

1. Namenskonventionen

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

(1)MySQL hat einen Konfigurationsparameter lower_case_table_names=1, was bedeutet, dass die Tabellennamen in Kleinbuchstaben gespeichert werden und die Groß-/Kleinschreibung nicht beachtet wird. Wenn der Wert 0 ist, wird der Name der Bibliothekstabelle so wie er ist gespeichert, wobei die Groß-/Kleinschreibung beachtet wird. Wenn der Wert 2 ist, wird er so wie er ist gespeichert, aber in Kleinbuchstaben verglichen.

(2) Wenn Groß- und Kleinbuchstaben gemischt werden, können mehrere Tabellen wie abc, Abc, ABC usw. nebeneinander existieren, was leicht zu Verwirrung führen kann.

(3) Bei Feldnamen wird die Groß-/Kleinschreibung beachtet, bei der tatsächlichen Verwendung ist dies jedoch nicht der Fall. Das heißt, Sie können nicht zwei Felder mit demselben Namen, aber unterschiedlicher Groß-/Kleinschreibung erstellen.

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

2. Der Datenbankname beginnt mit d, der Tabellenname beginnt mit t und der Feldname beginnt mit f_

(1) Beispielsweise stellt in der Tabelle t_crm_relation das crm in der Mitte den Namen des Geschäftsmoduls dar

(2) Ansichten beginnen mit view_, Ereignisse beginnen mit event_, Trigger beginnen mit trig_, gespeicherte Prozeduren beginnen mit proc_ und Funktionen beginnen mit func_

(3) Der gemeinsame Index heißt idx_col1_col2 und der eindeutige Index heißt uk_col1_col2 (der Teil f_public kann entfernt werden). Wie zum Beispiel idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time)

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

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

4. Temporäre Datenbank- und Tabellennamen müssen mit tmp plus Datum versehen sein

Wie beispielsweise t_crm_relation_tmp0425. Die Sicherungstabelle ist ähnlich und hat die Form _bak20160425.

5. Die nach Datum und Uhrzeit unterteilte Tabelle muss dem Format _JJJJ[MM][TT] entsprechen.

Dies dient auch der Vorbereitung auf die Möglichkeit separater Tabellen in der Zukunft, wie z. B. t_crm_ec_record_201403, aber t_crm_contact_at201506 verstößt gegen diese Spezifikation.
Diejenigen ohne Zeitmerkmale werden direkt als t_tbname_001 benannt.

2. Grundlegende Spezifikationen von Datenbanktabellen

1. Verwenden Sie die Innodb-Speicher-Engine

Ab Version 5.5 ist InnoDB die Standardspeicher-Engine von MySQL. Ab Version 5.7 wird MyISAM für Systemtabellen nicht mehr verwendet.

2. Der Tabellenzeichensatz verwendet einheitlich UTF8

(1) Der UTF8-Zeichensatz benötigt 3 Bytes zum Speichern chinesischer Zeichen und 1 Byte zum Speichern englischer Zeichen

(2) Der Zeichensatz für das Korrekturlesen verwendet den Standard utf8_general_ci

(3) Der verbundene Client verwendet ebenfalls UTF-8. Geben Sie beim Verbindungsaufbau den Zeichensatz oder SET NAMES UTF8; an. (Für diejenigen, die Latin1 schon lange in ihren Projekten verwenden, gibt es keine Möglichkeit, sie zu speichern.)

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

3. Kommentare zu allen Tabellen hinzufügen

(1) Versuchen Sie, den Feldern Kommentare hinzuzufügen

(2) Der Statustyp muss die Bedeutung des Hauptwertes angeben, z. B. „0-offline, 1-online“

4. Kontrollieren Sie die Anzahl der Felder in einer einzelnen Tabelle

(1) Die Obergrenze für die Anzahl der Felder in einer einzelnen Tabelle liegt bei etwa 30. Wenn mehr Felder vorhanden sind, sollten Sie eine vertikale Aufteilung der Tabelle in Erwägung ziehen. Trennen Sie erstens heiße und kalte Daten. Trennen Sie zweitens große Felder. Trennen Sie drittens keine Spalten, die häufig zusammen für Bedingungen und Rückgabespalten verwendet werden.

(2) Die Tabellenfelder werden auf präzise und prägnante Weise gesteuert, wodurch die E/A-Effizienz verbessert und mehr gültige Daten im Speicher zwischengespeichert werden können, wodurch die Reaktionsgeschwindigkeit und die Parallelitätsfähigkeiten verbessert werden und auch nachfolgende Änderungstabellenvorgänge schneller ablaufen.

5. Alle Tabellen müssen explizit einen Primärschlüssel angeben

(1) Der Primärschlüssel sollte so oft wie möglich automatisch inkrementiert werden. Die InnoDB-Tabelle ist eigentlich eine indexorganisierte Tabelle. Sequentielle Speicherung kann die Zugriffseffizienz verbessern und den Speicherplatz voll ausnutzen. Es wird auch für einige komplexe Abfragen benötigt, die zur Optimierung möglicherweise einen Self-Join erfordern.

(2) Wenn ein global eindeutiger Primärschlüssel erforderlich ist, verwenden Sie einen externen Ticketserver (im Aufbau)

(3) Wenn kein Primärschlüssel oder eindeutiger Index vorhanden ist, sucht die Update/Delete-Operation durch alle Felder nach der Zeile, was einem vollständigen Tabellenscan für jede Zeile entspricht.

(4) In einigen Fällen kann ein gemeinsamer eindeutiger Primärschlüssel verwendet werden. Dies erfordert jedoch die Rücksprache mit dem DBA.

6. Fremdschlüsselverweise werden nicht erzwungen

Auch wenn die Felder der beiden Tabellen eine klare Fremdschlüsselreferenzbeziehung aufweisen, wird FOREIGN KEY nicht verwendet, da neue Datensätze mit der Primärschlüsseltabelle verglichen werden, was die Leistung beeinträchtigt.

7. Verwenden Sie gespeicherte Prozeduren und Ansichten angemessen und verbieten Sie die Verwendung von Triggern und Ereignissen

(1) Obwohl gespeicherte Prozeduren den Code auf der Geschäftsseite vereinfachen können und in traditionellen Unternehmen beim Schreiben komplexer Logik verwendet werden können, werden in Internetunternehmen sehr häufig Änderungen vorgenommen. Es ist ziemlich mühsam, eine gespeicherte Prozedur zu aktualisieren, wenn es separate Datenbanken und Tabellen gibt. Und weil keine Protokolle aufgezeichnet werden, ist das Debuggen von Leistungsproblemen nicht praktisch. Wenn Sie eine Prozedur verwenden, denken Sie unbedingt darüber nach, was passiert, wenn die Ausführung fehlschlägt.

(2) Bis zu einem gewissen Grad dient die Verwendung von Ansichten auch dazu, die Komplexität von SQL im Code zu verringern. Manchmal geht die Universalität der Ansicht jedoch auf Kosten der Leistung (z. B. durch die Rückgabe unnötiger Felder).

(3) Dasselbe gilt für Trigger, allerdings sollten sie nicht verwendet werden, um die Datenkonsistenz einzuschränken. MySQL unterstützt nur „zeilenbasiertes Triggern“, d. h. ein Trigger zielt immer auf einen Datensatz und nicht auf die gesamte SQL-Anweisung. Wenn der zu ändernde Datensatz sehr groß ist, ist die Effizienz sehr gering. Das Verbergen der Arbeit hinter einer SQL-Anweisung kann im Falle eines Problems katastrophale Folgen haben und ist zudem schwierig zu analysieren und schnell zu lokalisieren. Darüber hinaus kann das pt-osc-Tool nicht verwendet werden, wenn DDL erforderlich ist. Fügen Sie es in die Transaktionsausführung ein.

(4) Ereignisse sind auch eine Form von Faulheit. Wir sind auf mehrere Fälle gestoßen, in denen das Geschäft durch den Ausfall geplanter Aufgaben beeinträchtigt wurde und MySQL keine Fehlerwarnung dafür ausgeben konnte. Richten Sie eine dedizierte Job-Scheduler-Plattform ein.

a. Die Datenmenge in einer einzelnen Tabelle wird innerhalb von 5000 W kontrolliert

b. Klartext-Passwörter dürfen nicht in der Datenbank gespeichert werden

3. Feldspezifikation

1. Definition von Zeichenfolgentypen wie Char, Varchar, Text usw.

(1) Für Spalten mit einer festen Länge ist char geeignet, wenn die Spalte häufig aktualisiert wird.

(2) Obwohl varchar Zeichenfolgen mit variabler Länge speichern kann, darf der Datentyp nicht zu klein oder zu groß sein. UTF8 kann bis zu 21844 chinesische Zeichen oder 65532 englische Zeichen speichern

(3) varbinary(M) speichert binäre Zeichenfolgen. Es speichert Bytes statt Zeichen, daher gibt es kein Konzept eines Zeichensatzes. Die Länge von M beträgt 0-255 (Bytes). Wird nur für Groß- und Kleinschreibungstypen beim Sortieren oder Vergleichen verwendet, nicht für die Kennwortspeicherung

(4) Der Typ TEXT ähnelt VARCHAR, da er variable Längen speichert und eine maximale Grenze von 2^16 hat. Der Inhalt nach 20 Bytes wird jedoch außerhalb der Datenseite gespeichert (row_format=dynamic). Seine Verwendung erfordert eine weitere Adressierung und hat keinen Standardwert.

Es wird im Allgemeinen zum Speichern von Werten verwendet, die eine große durchschnittliche Kapazität haben und nicht so häufig betrieben werden wie andere Felder.

Einige Artikel im Internet raten dazu, die Verwendung von Text und Blob zu vermeiden. Sie sollten wissen, dass die ausschließliche Verwendung von varchar zu einem Zeilenüberlauf führen kann. Der Effekt ist ähnlich, aber da jede Zeile zu viele Bytes belegt, verringert sich die Anzahl der Datenzeilen und -seiten, die buffer_pool zwischenspeichern kann. Darüber hinaus werden Indizes im Allgemeinen nicht für Text und Blob erstellt. Stattdessen werden Volltextsuchmaschinen von Drittanbietern wie Sphinx verwendet. Wenn tatsächlich ein (Präfix-)Index erstellt wird, wirkt sich dies auf die Leistung aus. Alles hängt vom jeweiligen Szenario ab.

Teilen Sie Text/Blob wenn möglich auch in eine andere Tabelle auf.

(5) BLOB ist eine erweiterte Version von varbinary. Der Inhalt wird in Binärzeichenfolgen gespeichert, hat keinen Zeichensatz und ist case-sensitiv. Es gibt ein Szenario, das oft erwähnt, aber nicht verwendet wird: Speichern Sie keine Bilder in der Datenbank.

2. Definition numerischer Typen wie int, tinyint, decimal usw.

(1) Verwenden Sie tinyint anstelle von enum und boolean
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.
Tinyint verwendet 1 Byte und wird im Allgemeinen für Status-, Typ- und Flag-Spalten verwendet.

(2) Es wird empfohlen, UNSIGNED zum Speichern nicht negativer Werte zu verwenden. Im Vergleich zur Nichtverwendung von unsigned kann der Bereich der verfügbaren Werte verdoppelt werden.

(3) int verwendet einen festen 4-Byte-Speicher. Der Unterschied zwischen int(11) und int(4) liegt nur in der Anzeigebreite.

(4) Verwenden Sie Decimal statt float/double, um präzise Gleitkommazahlen zu speichern. Verwenden Sie für Typen wie Währung und Betrag Dezimalzahlen, z. B. decimal(9,2). Standardmäßig kann die Gleitkommazahl nur auf 6 signifikante Ziffern genau sein.

3.Zeitstempel und Datums-/Uhrzeitauswahl

(1) Die Datums- und Zeitstempeltypen belegen unterschiedlichen Speicherplatz, 8 Bytes für den ersten und 4 Bytes für den zweiten. Daher sind die Zeitbereiche, die sie darstellen können, unterschiedlich. Ersteres reicht vom 01.01.1000 00:00:00 bis 31.12.9999 23:59:59, letzteres reicht vom 01.01.1970 08:00:01 bis 19.01.2038 11:14:07. Daher ist der von TIMESTAMP unterstützte Bereich kleiner als der von DATATIME.

(2) Der Zeitstempel kann das Zeitfeld beim Einfügen/Aktualisieren von Zeilen automatisch aktualisieren (z. B. f_set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP), eine Tabelle kann jedoch nur eine solche Definition haben.

(3) Die Anzeige des Zeitstempels ist zeitzonenbezogen und wird intern immer in UTC-Millisekunden gespeichert. Unterliegt ebenfalls strengen Modusbeschränkungen

(4) Der Zeitstempel wird bevorzugt, Datum und Uhrzeit sind jedoch ebenfalls akzeptabel.

(5) Verwenden Sie in der Where-Bedingung keine Zeitfunktionen auf Zeitspalten

4. Es wird empfohlen, alle Felder als NOT NULL zu definieren

(1) Wenn es sich um ein Indexfeld handelt, muss es als ungleich null definiert sein. Denn Nullwerte können die Koordinatenstatistik beeinflussen und die Indexwahl des Optimierers beeinflussen.

(2) Wenn nicht garantiert werden kann, dass beim Einfügen ein Wert vorhanden ist, verwenden Sie bei der Definition den Standardwert '' oder 0.

5. Felddefinitionen mit gleicher Bedeutung müssen gleich sein

Wenn es beispielsweise f_user_id-Felder in verschiedenen Tabellen gibt, sollten ihre Typen und Feldlängen gleich sein.

4. Indexspezifikation

1. Alle neuen Auswahl-, Aktualisierungs- und Löschvorgänge müssen zuerst erklärt werden, um die Indexverwendung anzuzeigen.

Versuchen Sie, die Anzeige zusätzlicher Spalten zu vermeiden: Verwenden Sie die Dateisortierung, die temporäre Verwendung und seien Sie beim Online-Zugriff vorsichtig, wenn die Zeilenanzahl 1.000 überschreitet.
erklären

(1) Typ: ALL, Index, Bereich, Ref, Eq_Ref, Const, System, NULL (von links nach rechts, Leistung von der schlechtesten zur besten)

(2) Possible_keys: Gibt an, welchen Index MySQL verwenden kann, um Datensätze in der Tabelle zu finden. Wenn es einen Index für das in der Abfrage enthaltene Feld gibt, wird der Index aufgelistet, er darf jedoch nicht in der Abfrage verwendet werden.

(3) Schlüssel: Gibt den Schlüssel (Index) an, den MySQL tatsächlich verwenden möchte
Wenn kein Index ausgewählt wurde, ist der Schlüssel NULL. Um MySQL zu zwingen, den Index der Spalte „possible_keys“ zu verwenden oder zu ignorieren, verwenden Sie in Ihrer Abfrage FORCE INDEX, USE INDEX oder IGNORE INDEX.

(4) ref: Gibt an, welche Spalten oder Konstanten zum Suchen der Werte in der Indexspalte verwendet werden.

(5) Zeilen: Die geschätzte Anzahl der Zeilen, die zum Auffinden der erforderlichen Datensätze basierend auf Tabellenstatistiken und Indexauswahl erforderlich sind.

(6) Zusätzliche Informationen

a. Temporär verwenden: Gibt an, dass MySQL eine temporäre Tabelle zum Speichern des Ergebnissatzes verwenden muss, was beim Sortieren und Gruppieren von Abfragen üblich ist
b. Verwenden von Filesort: Der Sortiervorgang, der in MySQL nicht mit Indizes abgeschlossen werden kann, wird als "Dateisortierung" bezeichnet.

1. Begrenzung der Anzahl der Indizes

(1) Indizes sind ein zweischneidiges Schwert. Sie erhöhen den Wartungsaufwand, erhöhen den IO-Druck und vervielfachen den von den Indizes belegten Speicherplatz.

(2) Die Anzahl der Indizes in einer einzelnen Tabelle sollte auf maximal 5 begrenzt werden oder 20 % der Anzahl der Tabellenfelder nicht überschreiten. Wenn mehrere Felder in einer einzelnen Tabelle für Abfrageanforderungen separate Indizes verwenden müssen, ist eine DBA-Auswertung erforderlich.

2. Vermeiden Sie redundante Indizes

(1.) Die InnoDB-Tabelle ist eine indexorganisierte Tabelle. Der Primärschlüssel ist ein gruppierter Index, der zusammen mit den Daten platziert wird. Der normale Index zeigt letztendlich auf die Adresse des Primärschlüssels, daher ist es redundant, den Primärschlüssel als letzte Spalte zu setzen. Wenn f_crm_id als Primärschlüssel verwendet wird, ist die f_crm_id im gemeinsamen Index (f_user_id, f_crm_id) vollständig redundant.

(2) (a,b,c), (a,b), letzterer ist ein redundanter Index. Der Präfixindex kann zur Beschleunigung und Reduzierung des Wartungsaufwands verwendet werden

3. Wenn keine besonderen Anforderungen bestehen, verwenden Sie die Auto-Increment-ID als Primärschlüssel

(1.) Der Primärschlüssel ist ein gruppierter Index und wird sequenziell geschrieben. Wenn der kombinierte eindeutige Index als Primärschlüssel verwendet wird, wird er zufällig geschrieben und eignet sich für Tabellen mit wenigen Schreib- und vielen Lesevorgängen.

(2) Primärschlüssel-Updates sind nicht zulässig

4. Versuchen Sie, Indizes für Spalten mit hoher Selektivität zu erstellen

(1) Erstellen Sie keine Indizes für Spalten mit niedriger Kardinalität, wie etwa Geschlecht und Typ. Es gibt jedoch einen Fall, idx_feedbackid_type (f_feedback_id,f_type). Wenn f_type=1 häufig zum Vergleich verwendet wird und 90 % der Zeilen herausgefiltert werden können, lohnt es sich, diesen zusammengesetzten Index zu erstellen. Manchmal verwendet dieselbe Abfrageanweisung aufgrund unterschiedlicher Bedingungswerte unterschiedliche Indizes, und es gilt dasselbe Prinzip.

(2) Berechnungsmethode für die Indexselektivität (Kardinalität ÷ Anzahl der Datenzeilen)

Selektivität = Kardinalität / Gesamtzahl der Zeilen = select count(distinct col1)/count(*) from tbname. Je näher der Wert bei 1 liegt, desto besser ist der Filtereffekt bei Verwendung des Index auf col1.

(3) Wenn die Anzahl der vom Index gescannten Zeilen 30 % überschreitet, wechseln Sie zum vollständigen Tabellenscan

5. Prinzip des ganz linken Präfixes

(1) Wenn MySQL einen gemeinsamen Index verwendet, wird von links nach rechts abgeglichen. Wenn eine Trennung oder eine Bereichsabfrage auftritt, können die nachfolgenden Indexspalten nicht verwendet werden. Beispielsweise entspricht der Index idx_c1_c2_c3 (c1,c2,c3) der Erstellung von drei Indizes (c1), (c1,c2) ​​und (c1,c2,c3). Der Index kann für Feldvergleiche verwendet werden, die die drei oben genannten Bedingungen erfüllen. Wenn jedoch beispielsweise c1=a und c3=c ist, kann nur der Index der Spalte c1 verwendet werden. In Fällen wie c2=b und c3=c kann dieser Index überhaupt nicht verwendet werden.

(2) Die Indexübereinstimmung wird auch dann gestoppt, wenn eine Bereichsabfrage (>, <, zwischen, wie) auftritt. Wenn beispielsweise c1=a und c2 > 2 und c3=c ist, kann nur der Vergleich der Spalten c1 und c2 den Index verwenden, und der Index der Anordnung (c1, c2, c3) kann vollständig verwendet werden.

(3) Die Reihenfolge der Felder in der Where-Bedingung hat nichts mit der Indexreihenfolge zu tun. Der MySQL-Optimierer passt die Reihenfolge automatisch an.

6. Präfixindex

(1) Wenn Sie einen Index für eine Spalte mit einer Länge von mehr als 30 Zeichen erstellen, sollten Sie die Verwendung eines Präfixindex in Betracht ziehen. Beispielsweise bedeutet idx_cs_guid2 (f_cs_guid(26)), dass die ersten 26 Zeichen als Index verwendet werden. Dies kann die Sucheffizienz verbessern und Platz sparen.

(2) Präfixindizes haben auch ihre Nachteile. Sie können nicht verwendet werden, wenn ORDER BY oder GROUP BY auf die Spalte angewendet wird, und sie können nicht als überdeckende Indizes verwendet werden.

(3) Wenn Sie einen Präfixindex für eine binäre Speicherspalte wie varbinary oder blob erstellen, müssen Sie den Zeichensatz berücksichtigen. Die Anzahl der Bytes in Klammern ist

7. Sinnvoller Einsatz von Covering-Indizes zur Reduzierung von IO

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. Ein überdeckender Index kann alle benötigten Datenspalten in einem Index abrufen und so eine zweite Suche in der Tabelle vermeiden, IO einsparen und somit effizienter sein.

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. Versuchen Sie, keine Indizes für häufig aktualisierte Spalten zu erstellen

Wenn Sie keinen Index für die Spalte mit definiertem ON UPDATE CURRENT_STAMP erstellen, sind die Wartungskosten zu hoch (glücklicherweise verfügt MySQL über einen Einfügepuffer, der die Indexeinfügung zusammenführt).

5. SQL-Design

1. Vermeiden Sie die direkte Verwendung von SELECT * zum Lesen aller Felder

Selbst wenn alle Felder benötigt werden, wird der Verbrauch der Netzwerkbandbreite reduziert, abdeckende Indizes können effektiv genutzt werden und Änderungen in der Tabellenstruktur haben kaum Auswirkungen auf das Programm

2. Wenn Sie sicher sind, dass nur ein Ergebnis zurückgegeben wird, verwenden Sie das Limit 1

Unter der Voraussetzung, dass die Daten korrekt sind und die Anzahl der Ergebnissätze ermittelt werden kann, verwenden Sie „limit“ häufiger, um die Ergebnisse so schnell wie möglich zurückzugeben.

3. Seien Sie vorsichtig bei impliziten Typkonvertierungen

(1) Umrechnungsregeln

a. Wenn mindestens einer der beiden Parameter NULL ist, ist das Ergebnis des Vergleichs ebenfalls NULL. Die Ausnahme ist, dass beim Vergleichen von zwei NULL-Werten mit <=> 1 zurückgegeben wird. In beiden Fällen ist keine Typkonvertierung erforderlich.

b. Beide Parameter sind Zeichenfolgen und werden als Zeichenfolgen ohne Typkonvertierung verglichen.

c. Beide Parameter sind Ganzzahlen, daher werden sie als Ganzzahlen ohne Typkonvertierung verglichen.

d. Wenn ein Hexadezimalwert mit einer nicht-stelligen Zahl verglichen wird, wird er als Binärzeichenfolge behandelt.

e. Wenn ein Parameter TIMESTAMP oder DATETIME ist und der andere Parameter eine Konstante, wird die Konstante in einen Zeitstempel umgewandelt.

f. Wenn ein Parameter vom Typ Dezimal ist und der andere Parameter eine Dezimalzahl oder Ganzzahl ist, wird die Ganzzahl zum Vergleich in eine Dezimalzahl umgewandelt. Wenn der andere Parameter eine Gleitkommazahl ist, wird die Dezimalzahl zum Vergleich in eine Gleitkommazahl umgewandelt.

g. In allen anderen Fällen werden beide Argumente vor dem Vergleich in Gleitkommazahlen umgewandelt.

(2) Wenn ein Index auf der Grundlage des Typs „String“ erstellt wird und dieses Feld mit einem Wert vom Typ „Int“ verglichen wird, entspricht dies dem Abschnitt g. Wenn beispielsweise der Typ von f_phone als varchar definiert ist, aber f_phone in (098890) in where verwendet wird, werden beide Parameter als Gleitkommatypen behandelt. Das Auftreten dieser impliziten Konvertierung ist nicht das Schlimmste. Das Schlimmste ist, dass MySQL den Index nach der Konvertierung der Zeichenfolge in Float nicht verwenden kann, was zu Leistungsproblemen führt. Wenn f_user_id = „1234567“, wird die Anforderung von Klausel b erfüllt und die Zahlen werden direkt als Zeichenfolgen verglichen.

4. Verwenden Sie keine Funktionen für Where-Bedingungsspalten

(1) Dadurch wird der Index ungültig, z. B. lower(email), f_qq % 4. Kann mit der Konstanten rechts berechnet werden

(2) Wenn der zurückgegebene Ergebnissatz nicht sehr groß ist, können Sie Funktionen auf die zurückgegebenen Spalten anwenden, um die Programmentwicklung zu vereinfachen

5. Verwenden Sie Fuzzy-Matching und setzen Sie % nicht an die erste Stelle

Dies führt dazu, dass der Index fehlschlägt. Wenn Sie diese Suchanforderung haben, ziehen Sie andere Lösungen in Betracht, wie z. B. die Sphinx-Volltextsuche

6. Wenn es um komplexes SQL geht, beziehen Sie sich unbedingt auf das vorhandene Indexdesign und erklären Sie es zuerst.

(1) Einfache SQL-Aufteilung, ohne die Komplexität der Codeverarbeitung als Entschuldigung zu benutzen.

(2) Beispielsweise haben in der ODER-Bedingung: f_phone='10000' oder f_mobile='10000' beide Felder Indizes, aber nur eines davon kann verwendet werden. Es kann in zwei SQL-Anweisungen aufgeteilt oder alles zusammengeführt werden.

(3) Der Vorteil der ersten Erklärung besteht darin, dass Sie weitere Abfragebeschränkungen hinzufügen können, um den Index zu nutzen.

7. Versuchen Sie bei Verwendung von Join, den Index in derselben Tabelle in der Where-Bedingung zu verwenden

(1) Wählen Sie beispielsweise t1.a,t2.b * aus t1,t2 und t1.a=t2.a und t1.b=123 und t2.c= 4. Wenn die Felder t1.c und t2.c gleich sind, dann verwendet der Index (b,c) auf t1 nur b. Wenn Sie jetzt t2.c=4 in der Where-Bedingung in t1.c=4 ändern, können Sie den vollständigen Index verwenden.

(2) Diese Situation kann auftreten, wenn das Felddesign redundant ist (Antinormalisierung).

(3) Inner Join und Left Join richtig auswählen

8. Verwenden Sie weniger Unterabfragen und mehr Verknüpfungen

In Versionen vor 5.6 sind Unterabfragen sehr ineffizient, anders als bei Oracle, das zuerst die Unterabfrage und dann die äußere Abfrage berechnet. Version 5.6 wurde optimiert

9. Erwägen Sie die Verwendung von Union All, verwenden Sie Union seltener und achten Sie auf die Deduplizierung

(1) union all entfernt keine Duplikate, erfordert aber keine Sortiervorgänge und ist daher schneller als union. Wenn keine Duplikate entfernt werden müssen, ist union all vorzuziehen.

(2) Wenn im UNION-Ergebnis limit verwendet wird, fügen Sie limit zu jeder der beiden Unter-SQL-Anweisungen hinzu, wenn viele Rückgabewerte möglich sind. Wenn weiterhin eine Order by vorliegt, wenden Sie sich bitte an den DBA.

10. Der Inhalt von IN sollte 200 Wörter nicht überschreiten.

Verwenden Sie den Batchmodus für mehr als 500 Werte. Andernfalls beeinträchtigt eine Ausführung die Parallelität der Datenbank, da ein einzelnes SQL immer nur eine einzige CPU belegen kann und zu Verzögerungen bei der Master-Slave-Replikation führen kann.

11. Sagen Sie Nein zu großen Dingen

Wenn beispielsweise in einer Transaktion mehrere Auswahl- und Aktualisierungsvorgänge ausgeführt werden und es sich dabei um eine Hochfrequenztransaktion handelt, wird die Parallelitätsfähigkeit von MySQL erheblich beeinträchtigt, da die von der Transaktion gehaltenen Sperren und anderen Ressourcen nur freigegeben werden können, wenn für die Transaktion ein Rollback/Commit durchgeführt wird. Gleichzeitig müssen wir aber auch die Konsistenz der Datenschreibung berücksichtigen.

12. Vermeiden Sie Vergleiche wie „ist null“ und „ist nicht null“

13.Sortieren nach .. Limit

Diese Art von Abfrage wird durch Indizes besser optimiert, aber die Sortierung nach Feld ist wichtig. Wenn beispielsweise der Primärschlüssel „id“ und „f_time“ beide in aufsteigender Reihenfolge sind, können Sie eine Sortierung nach „id“ statt nach „f_time“ in Betracht ziehen.

14.c1 < eine Reihenfolge von c2

Der Unterschied zum Obigen besteht darin, dass vor der Sortierung eine Bereichsabfrage erfolgt. Wie aus dem vorherigen Inhalt ersichtlich ist, ist ein Index wie (c1, c2) nicht erforderlich, aber der Index (c2, c1) kann verwendet werden. Es kann auch als Join-Methode neu geschrieben werden.

15. Paging-Optimierung

Es wird empfohlen, eine sinnvolle Paging-Methode zu verwenden, um die Paging-Effizienz zu verbessern. Verwenden Sie bei großen Seiten kein Jump-Paging.

Wenn eine Paging-Anweisung ähnlich der folgenden vorliegt:

AUSWAHL AUS Tabelle1 ORDER BY ftime DESC LIMIT 10000,10;

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

Empfohlene Paging-Methode:

SELECT FROM Tabelle1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10

Das heißt, übergeben Sie den Grenzwert der vorherigen Paging-

SELECT * FROM Tabelle als t1 inner JOIN (SELECT id FROM Tabelle ORDER BY Zeit LIMIT 10000，10) als t2 ON t1.id=t2.id

16. zählen

(1) Erstens gibt es Unterschiede zwischen count(), count(1) und count(col1). Count() gibt die Anzahl der Datensätze im gesamten Ergebnissatz an, während count(1) die Anzahl der Datensätze im Ergebnissatz angibt, die nach Primärschlüssel gezählt werden. In den meisten Fällen haben count() und count(1) dieselbe Wirkung, aber count(col1) gibt die Anzahl der Datensätze in der Spalte col1 an, die im Ergebnissatz NICHT null sind. Bevorzugen Sie count()

(2) Das Zählen großer Datenmengen ist ein ressourcenintensiver Vorgang und kann sogar die gesamte Datenbank verlangsamen. Wenn das Abfrageleistungsproblem nicht gelöst werden kann, sollte das Produktdesign neu strukturiert werden. Wenn beispielsweise häufig Zählabfragen erforderlich sind, können Sie die Verwendung einer Übersichtstabelle in Erwägung ziehen.

(3) Im Falle von „distinct“ kann die Methode „Group by“ effizienter sein.

17. Ändern Sie die Lösch- und Aktualisierungsanweisungen in „Auswählen und dann erklären“

Auswahlvorgänge können lediglich zu einer Verlangsamung der Datenbank führen, Schreibvorgänge sind jedoch die Ursache für das Sperren von Tabellen.

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

(1) INSERT ... ON DUPLICATE KEY UPDATE ...: Wenn die eingefügte Zeile einen doppelten Wert in einem UNIQUE-Index oder PRIMARY KEY ergibt, wird die alte Zeile aktualisiert. Wenn kein Duplikat vorhanden ist, wird die alte Zeile direkt eingefügt, was sich auf eine Zeile auswirkt.

(2) REPLACE INTO ist ähnlich, löscht aber alte Zeilen, wenn ein Konflikt auftritt. INSERT IGNORE macht das Gegenteil, indem die alten Zeilen beibehalten und die neuen einzufügenden Zeilen verworfen werden.

(3) INSERT INTO VALUES(),(),(), Merge-Einfügen.

19. Beseitigen Sie gefährliches SQL

(1) Entfernen Sie die bedeutungslose oder immer zutreffende Bedingung „wobei 1=1“. Dies ist furchtbar, wenn Sie auf Update/Delete oder SQL-Injection stoßen.

(2) DDL-Anweisungen sind in SQL nicht zulässig. Im Allgemeinen werden Berechtigungen wie Erstellen/Ändern nicht erteilt, aber Alibaba Cloud RDS unterscheidet nur zwischen Lese- und Schreibbenutzern.

VI. Verhaltenskodex

(1) Es ist nicht erlaubt, Live-Netzwerkdaten ohne das Wissen des DBA zu importieren.

(2) Vermeiden Sie bei umfangreichen Aktualisierungen, wie z. B. der Reparatur von Daten, Spitzenzeiten und benachrichtigen Sie den DBA. Die direkte Ausführung von SQL erfolgt durch Betriebs- und Wartungskollegen oder DBA-Kollegen

(3) Zeitnahe Verarbeitung von SQL-Anweisungen für Offline-Dienste

(4) Komplexe SQL-Online-Überprüfung

Da es derzeit keinen SQL-Überprüfungsmechanismus gibt, sollten komplexe SQL-Anweisungen wie Multi-Table-Join, Count und Group By proaktiv dem DBA zur Überprüfung gemeldet werden.

(5) Die Auswahl und Gestaltung wichtiger Datenbanklösungen muss dem DBA vorab mitgeteilt werden

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für alle hilfreich sein.

siehe:

Detaillierte Erklärung des MySQL-Prepare-Prinzips

Mehrere wichtige MySQL-Variablen

Detaillierte Erklärung des Codes zwischen der MySQL-Masterbibliothek Binlog (Master-Log) und der Slave-Bibliothek Relay-Log

Vielen Dank fürs Lesen und ich hoffe, dass meine Freunde diese Site unterstützen werden!