Äußerst detaillierte Freigabe der MySQL-Nutzungsspezifikation

In letzter Zeit waren viele datenbankbezogene Vorgänge erforderlich, und die vorhandenen Spezifikationen des Unternehmens sind nicht sehr umfassend. Basierend auf den relevanten Spezifikationen verschiedener Experten im Internet habe ich einige Standardverwendungen für meinen eigenen Gebrauch zusammengestellt. Ich hoffe, Sie können mich korrigieren.

Datenbankumgebung

dev: Entwicklungsumgebung

Entwickler können die Tabellenstruktur lesen, schreiben und ändern. Entwickler können die Tabellenstruktur und die darin enthaltenen Daten nach Belieben ändern, müssen jedoch sicherstellen, dass dadurch keine Auswirkungen auf andere Entwicklungskollegen auftreten.

test: Testumgebung

Die Entwicklung kann lesen und schreiben und Entwickler können die Tabellenstruktur mithilfe von Tools ändern.

online: Online-Umgebung

Entwickler dürfen keine Datenbankoperationen direkt in der Online-Umgebung durchführen. Wenn Operationen erforderlich sind, müssen sie einen DBA finden, der die Operationen durchführt und entsprechende Aufzeichnungen macht. Stresstests sind verboten.

Der entscheidende Punkt besteht darin, dass die den MySQL-Servern in jeder Umgebung entsprechenden Benutzerberechtigungen klar aufgeteilt und erkennbar sein müssen und eine spezifische Unterscheidung zwischen Geschäftsszenarien ermöglichen müssen.

Namenskonventionen

Grundlegende Benennungsregeln

• Verwenden Sie aussagekräftige englische Wörter und trennen Sie die Wörter durch Unterstriche. (Verwenden Sie kein Pinyin)
• Es dürfen nur englische Buchstaben, Zahlen und Unterstriche verwendet werden und der Name muss mit einem englischen Buchstaben beginnen.
• Bibliotheken, Tabellen und Felder sollten alle in Kleinbuchstaben geschrieben sein. Verwenden Sie kein Camel Case.
• Vermeiden Sie die Verwendung von für ORACLE und MySQL reservierten Wörtern wie „desc“ und Schlüsselwörtern wie „index“.
• Der Name darf nicht länger als 32 Zeichen sein. Die Bedeutung des Namens muss klar sein. Es wird empfohlen, Substantive anstelle von Verben zu verwenden.
• Datenbanken und Tabellen verwenden alle das Präfix
• Temporäre Datenbank- und Tabellennamen müssen mit „tmp“ beginnen und das Datum als Suffix aufweisen.
• Die Sicherungsdatenbank und -tabelle müssen mit dem Präfix „bak“ und dem Suffix „Datum“ versehen sein.

Warum sind alle Bibliotheken, Tabellen und Felder kleingeschrieben?

In MySQL entsprechen Datenbanken und Tabellen Verzeichnissen und Dateien innerhalb dieser Verzeichnisse. Daher bestimmt die Sensibilität des Betriebssystems die Groß- und Kleinschreibung von Datenbank- und Tabellennamen.

• Windows unterscheidet nicht zwischen Groß- und Kleinschreibung.
• Linux-Groß-/Kleinschreibung
• Bei Datenbanknamen und Tabellennamen muss die Groß-/Kleinschreibung streng beachtet werden.
• Bei Tabellenaliasen wird streng zwischen Groß- und Kleinschreibung unterschieden.
• Bei Spaltennamen und Spaltenaliasnamen wird in allen Fällen die Groß-/Kleinschreibung nicht beachtet.
• Auch bei Variablennamen muss die Groß-/Kleinschreibung streng beachtet werden.
• Wie kann das Problem gelöst werden, wenn die Camel-Case-Benennung festgelegt wurde? Sie müssen lediglich lower_case_table_names = 1 in die MySQL-Konfigurationsdatei my.ini einfügen.
  

Tabellenbenennung

Tabellen im selben Modul sollten möglichst dasselbe Präfix verwenden und die Tabellennamen sollten so aussagekräftig wie möglich sein. Alle Protokolltabellen beginnen mit log_

Feldbenennung

• Ein englisches Wort oder eine Abkürzung, die seine tatsächliche Bedeutung ausdrückt. Boolesche Felder haben das Präfix is_, gefolgt vom Perfektpartizip des Verbs.
• Felder mit gleicher Bedeutung sollten in jeder Tabelle den gleichen Namen haben. Felder mit derselben Bedeutung in verschiedenen Tabellen werden mit dem Tabellennamen_Feldnamen abzüglich des Modulpräfixes benannt.
• Das Fremdschlüsselfeld verwendet Tabellenname_Feldname, um seine Assoziationsbeziehung anzugeben.
• Als Primärschlüssel einer Tabelle wird üblicherweise die ID vereinbart, bei der es sich um einen Autoinkrementtyp handelt, und die Fremdschlüssel anderer Tabellen werden in der Form xxx_id ausgedrückt.

Indexbenennung

• Nicht eindeutige Indizes müssen wie folgt benannt werden: „idx_Feldname_Feldname[_Feldname]“
• Eindeutige Indizes müssen wie folgt benannt werden: „uniq_Feldname_Feldname[_Feldname]“
  

Benennung von Einschränkungen

• Primärschlüsseleinschränkung: pk_table-Name.
• Eindeutige Einschränkung: uk_Tabellenname_Feldname. (In der Anwendung ist außerdem eine Logik zur Eindeutigkeitsprüfung erforderlich.)
  

Spezifikationen für das Tabellendesign

Die Tabellen-Engine hängt vom tatsächlichen Anwendungsszenario ab. MyISAM wird für Protokoll- und Berichtstabellen empfohlen, und InnoDB wird für Tabellen empfohlen, die sich auf Transaktionen, Audits und Beträge beziehen. Sofern nicht anders angegeben, wird beim Erstellen der Tabellen die InnoDB-Engine verwendet.

Der Standardzeichensatz ist utf8mb4 und die Datenbanksortierregel ist utf8mb4_general_ci. (Da die Datenbankdefinition den Standard verwendet, kann die Datentabelle neu definiert werden. Aus Sicherheitsgründen wird jedoch empfohlen, Folgendes zu schreiben:

Warum wählt der Zeichensatz nicht utf8 und die Sortierung verwendet nicht utf8_general_ci?

MySQL mit UTF-8-Kodierung kann keine Emoji-Ausdrücke speichern, die 4 Bytes als Platzhalter verwenden. Damit das Backend-Projekt vom Client eingegebene Emoji-Ausdrücke vollständig unterstützt, ist ein Upgrade der Kodierung auf utf8mb4 die beste Lösung. Wenn die Zeichenkodierung der JDBC-Verbindungszeichenfolge auf utf8 eingestellt ist oder die Emoji-Daten nach der obigen Konfiguration nicht normal eingefügt werden können, müssen Sie im Code den Zeichensatz der Verbindung als utf8mb4 angeben.

Alle Tabellen und Felder sollten das Kommentarspaltenattribut verwenden, um die wahre Bedeutung der Tabelle oder des Felds zu beschreiben. Wenn es sich um einen Aufzählungswert handelt, wird empfohlen, den im Feld verwendeten Inhalt zu definieren.

Sofern nicht anders angegeben, muss das erste ID-Feld in der Tabelle der Primärschlüssel sein und automatisch hochzählen. Es ist verboten, es als Kontext oder Bedingung für die Datenübertragung außerhalb einer Transaktion zu verwenden. Verwenden Sie varchar nicht als Primärschlüssel-Anweisungsentwurf.

Sofern nicht anders angegeben, muss die Tabelle die Felder „create_time“ und „modify_time“ enthalten, d. h. die Tabelle muss Felder enthalten, die den Erstellungszeitpunkt und den Änderungszeitpunkt aufzeichnen.

Sofern nicht anders angegeben, muss die Tabelle is_del enthalten, um anzuzeigen, ob die Daten gelöscht wurden. Das physische Löschen von Datenbankdaten ist grundsätzlich nicht zulässig.

• Verwenden Sie so wenig Speicherplatz wie möglich, um die Daten eines Feldes zu speichern
• Verwenden Sie nicht char oder varchar, wenn Sie int verwenden können.
• Verwenden Sie nicht int, wenn Sie tinyint verwenden können
• Verwenden Sie UNSIGNED, um nicht negative Werte zu speichern.
• Es wird nicht empfohlen, ENUM- und SET-Typen zu verwenden. Verwenden Sie stattdessen TINYINT.
• Verwenden Sie kurze Datentypen. Wenn der Wertebereich beispielsweise 0-80 ist, verwenden Sie TINYINT UNSIGNED.
• Um genaue Gleitkommazahlen zu speichern, muss DECIMAL anstelle von FLOAT und DOUBLE verwendet werden.
• Das Zeitfeld verwendet, außer in Sonderfällen, int zum Aufzeichnen des unix_timestamp
• Verwenden Sie zum Speichern von Jahren den Typ YEAR.
• Verwenden Sie zum Speichern von Daten den Typ DATE.
• Es wird empfohlen, den Typ TIMESTAMP zum Speichern der Zeit (sekundengenau) zu verwenden, da TIMESTAMP 4 Bytes und DATETIME 8 Bytes verwendet.
• Es wird empfohlen, zum Speichern von IPV4 INT UNSIGNED zu verwenden.
• Vermeiden Sie möglichst die Verwendung von TEXT- und BLOB-Typen.
• Es ist verboten, VARBINARY und BLOB in der Datenbank zum Speichern von Bildern, Dateien usw. zu verwenden. Es wird empfohlen, andere Speichermethoden (TFS/SFS) zu verwenden. MySQL speichert nur Zeigerinformationen.
• Die Größe eines einzelnen Datensatzes darf 8 KB nicht überschreiten (Spaltenlänge (Chinesisch)_3 (UTF8) + Spaltenlänge (Englisch)_1).

Was ist der Unterschied zwischen Datum und Uhrzeit und Zeitstempel?

Ähnlichkeiten:

Das Anzeigeformat einer TIMESTAMP-Spalte ist dasselbe wie das einer DATETIME-Spalte. Die Anzeigebreite ist auf 19 Zeichen festgelegt und das Format ist JJJJ-MM-TT HH:MM:SS.

Unterschiede:

ZEITSTEMPEL

• 4 Bytes werden zum Speichern des Zeitbereichs verwendet: 1970-01-01 08:00:01 ~ 2038-01-19 11:14:07. Der Wert wird im UTC-Format gespeichert, was eine Zeitzonenkonvertierung beinhaltet. Die aktuelle Zeitzone wird beim Speichern konvertiert und beim Abrufen wieder in die aktuelle Zeitzone zurückkonvertiert.
• datetime wird in 8 Bytes gespeichert und der Zeitbereich ist: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Das tatsächliche Format wird gespeichert, unabhängig von der Zeitzone
  

Wie verwende ich die automatische Zuweisungseigenschaft von TIMESTAMP?

Verwenden Sie die aktuelle Zeit als Standardwert für ts: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP. Wenn eine Zeile aktualisiert wird, aktualisieren Sie den Wert von ts: ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP.

Sie können 1 und 2 kombinieren: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

Wie verwende ich INT UNSIGNED zum Speichern von IPs?

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 und sie müssen mit DECIMAL oder zwei bigINTs gespeichert werden.

• Wenn keine Bemerkung vorhanden ist, werden alle Felder auf NOT NULL gesetzt und Standardwerte festgelegt.
• Speichern Sie keine Passwörter im Klartext in der Datenbank
• Wenn keine Anmerkungen vorhanden sind, muss für alle booleschen Felder, beispielsweise „is_hot“ und „is_deleted“, der Standardwert 0 festgelegt sein.
• Wenn kein Vermerk vorhanden ist, wird das Sortierfeld order_id im Programm standardmäßig absteigend sortiert;
• Erweitern Sie die Integer-Definition nicht um Längen. Verwenden Sie beispielsweise INT statt INT[4].
  

INT[M], was stellt der M-Wert dar?

Beachten Sie, dass die Zahl nach der Klammer des numerischen Typs lediglich die Breite angibt und nichts mit dem Speicherbereich zu tun hat. Viele Leute denken, dass die Wertebereiche von INT(4) und INT(10) (-9999 bis 9999) bzw. (-9999999999 bis 99999999999) betragen. Das ist ein Irrtum. Tatsächlich können die Spaltenwerte gleich breit gemacht werden, wenn der M-Wert im Integer-Typ in Kombination mit dem ZEROFILL-Attribut verwendet wird. Unabhängig vom Wert von M in INT[M] beträgt sein Wertebereich (-2147483648 bis 2147483647 bei Vorzeichen), (0 bis 4294967295 bei Vorzeichenlosigkeit).

Die Anzeigebreite schränkt weder den Wertebereich ein, der in der Spalte enthalten sein kann, noch schränkt sie die Anzeige von Werten ein, die die angegebene Breite der Spalte überschreiten. Bei Verwendung in Verbindung mit dem optionalen erweiterten Attribut ZEROFILL werden zusätzliche Leerzeichen standardmäßig durch Nullen ersetzt. Beispielsweise wird für eine als INT(5) ZEROFILL deklarierte Spalte der Wert 4 als 00004 abgerufen. Beachten Sie, dass Sie beim Speichern eines Wertes, der die Anzeigebreite in einer Integer-Spalte überschreitet, möglicherweise auf Probleme stoßen, wenn MySQL temporäre Tabellen für komplexe Joins generiert, da MySQL in diesen Fällen davon ausgeht, dass die Daten in die ursprüngliche Spaltenbreite passen. Wenn Sie ZEROFILL für eine numerische Spalte angeben, fügt MySQL der Spalte automatisch das Attribut UNSIGNED hinzu.

Verwenden von VARBINARY zum Speichern von Zeichenfolgen mit variabler Länge und unter Berücksichtigung der Groß-/Kleinschreibung

Wann sollte CHAR und wann VARCHAR verwendet werden?

Die Typen CHAR und VARCHAR sind ähnlich, werden aber unterschiedlich gespeichert und abgerufen. Sie unterscheiden sich außerdem hinsichtlich der maximalen Länge und ob nachstehende Leerzeichen beibehalten werden. Die für die Typen CHAR und VARCHAR angegebene Länge gibt die maximale Anzahl der Zeichen an, die Sie speichern möchten. Beispielsweise kann CHAR(30) 30 Zeichen umfassen.

Die Länge einer CHAR-Spalte ist auf die Länge festgelegt, die beim Erstellen der Tabelle deklariert wird. Die Länge kann jeden Wert zwischen 0 und 255 haben. Beim Speichern von CHAR-Werten werden diese rechts mit Leerzeichen auf die angegebene Länge aufgefüllt. Beim Abrufen von CHAR-Werten werden nachstehende Leerzeichen entfernt. Es erfolgt keine Fallkonvertierung während der Speicherung oder Abfrage.

Die Werte in einer VARCHAR-Spalte sind Zeichenfolgen mit variabler Länge. Die Länge kann als Wert von 0 bis 65.535 angegeben werden. (Die maximale effektive Länge eines VARCHAR wird durch die maximale Zeilengröße und den verwendeten Zeichensatz bestimmt. Die maximale Gesamtlänge beträgt 65.532 Bytes.) Im Gegensatz zu CHAR werden VARCHAR-Werte nur mit der erforderlichen Anzahl von Zeichen plus einem zusätzlichen Byte zum Aufzeichnen der Länge gespeichert (zwei Bytes werden verwendet, wenn die deklarierte Länge der Spalte 255 überschreitet). VARCHAR-Werte werden ohne Auffüllung gespeichert. Nachfolgende Leerzeichen bleiben beim Speichern und Abrufen von Werten gemäß Standard-SQL erhalten.

char eignet sich zum Speichern des MD5-Hashes des Benutzerkennworts und seine Länge ist immer gleich. Bei Werten, die sich häufig ändern, ist char auch besser als varchar, da Zeilen mit fester Länge weniger wahrscheinlich fragmentiert werden. Bei sehr kurzen Spalten ist char auch effizienter als varchar. Eine char(1)-Zeichenfolge belegt bei einem Einzelbyte-Zeichensatz nur ein Byte, eine varchar(1)-Zeichenfolge belegt jedoch zwei Bytes, da ein Byte zum Speichern der Längeninformationen verwendet wird.

Spezifikationen für den Indexentwurf

Die MySQL-Abfragegeschwindigkeit hängt von einem guten Indexdesign ab. Daher sind Indizes für eine hohe Leistung von entscheidender Bedeutung. Ein angemessener Index beschleunigt die Abfrage (einschließlich der Geschwindigkeit von UPDATE und DELETE. MySQL lädt die Seite mit der Zeile in den Speicher und führt dann den UPDATE- oder DELETE-Vorgang aus). Ein unangemessener Index verlangsamt die Abfrage. Die MySQL-Indexsuche ähnelt der Pinyin- und Radikalsuche des Xinhua-Wörterbuchs. Wenn die Pinyin- und Radikalindizes nicht vorhanden sind, können sie nur durch Umblättern der Seiten nacheinander durchsucht werden. Wenn eine MySQL-Abfrage einen Index nicht verwenden kann, führt MySQL einen vollständigen Tabellenscan durch, der viele IO verbraucht. Der Zweck des Index: Deduplizierung, beschleunigte Positionierung, Vermeidung von Sortierung, Abdeckung des Index.

Was ist ein Deckungsindex?

In der InnoDB-Speicher-Engine speichert der Sekundärindex (Nicht-Primärschlüsselindex) 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. Das Konzept eines abdeckenden Indexes besteht darin, dass die Abfrage in einem Index abgeschlossen werden kann. Die Effizienz eines abdeckenden Indexes ist höher, und die Primärschlüsselabfrage ist ein natürlicher abdeckender Index. Eine sinnvolle Indexerstellung und ein sinnvoller Einsatz von Abfrageanweisungen kann bei der Verwendung von abdeckenden Indizes zu einer Leistungssteigerung führen. Beispiel: SELECT email,uid FROM user_email WHERE uid=xx. Wenn uid nicht der Primärschlüssel ist, können Sie zur Leistungssteigerung einen Index als index(uid,email) hinzufügen.

Grundlegende Indexspezifikationen

• Kontrolle der Indexmenge: Die Anzahl der Indizes in einer einzelnen Tabelle darf 5 nicht überschreiten und die Anzahl der Felder in einem einzelnen Index darf 5 nicht überschreiten.
• Umfassende Auswertung der Datendichte und -verteilung
• Berücksichtigen Sie das Abfrage- und Aktualisierungsverhältnis
  

Warum kann eine Tabelle nicht zu viele Indizes enthalten?

Der sekundäre Index von InnoDB verwendet b+tree zur Speicherung, daher muss b+tree während UPDATE, DELETE und INSERT angepasst werden. Zu viele Indizes verlangsamen den Aktualisierungsprozess.

Verwenden Sie einen Präfixindex für Zeichenfolgen. Die Länge des Präfixindex sollte 8 Zeichen nicht überschreiten. Es wird empfohlen, dem Präfixindex Priorität einzuräumen. Fügen Sie bei Bedarf eine Pseudospalte hinzu und erstellen Sie einen Index.

Indizieren Sie keine Blob-/Textfelder und keine großen Felder, da der Index dadurch zu viel Speicherplatz beansprucht.

Was ist ein Präfixindex?

Vereinfacht ausgedrückt besteht die Präfixindizierung darin, einen Index für die ersten paar Zeichen des Textes zu erstellen (die genaue Anzahl der Zeichen wird beim Erstellen des Index angegeben). Dadurch wird ein kleinerer Index erstellt, sodass Abfragen schneller sind. Durch den Präfixindex kann die Größe von Indexdateien effektiv reduziert und die Indizierungsgeschwindigkeit verbessert werden. Präfixindizes haben jedoch auch ihre Nachteile: MySQL kann Präfixindizes weder in ORDER BY noch in GROUP BY verwenden, noch können sie als überlagernde Indizes verwendet werden.

Die Syntax zum Erstellen eines Präfixindex lautet: ALTER TABLE table_name ADD KEY(column_name(prefix_length));

Primärschlüsselkriterien

• Die Tabelle muss einen Primärschlüssel haben
• Verwenden Sie keine häufig aktualisierten Spalten
• Versuchen Sie, keine Zeichenfolgenspalten auszuwählen
• Verwenden Sie keinen UUID MD5 HASH
• Verwenden Sie standardmäßig einen eindeutigen Schlüssel ungleich Null.
• Es wird empfohlen, Auto-Inkrement oder Nummerngenerator zu wählen
  

Wichtiges SQL muss indiziert werden, und Kern-SQL sollte beim Abdecken von Indizes Vorrang erhalten.

• WHERE-Bedingungsspalte für UPDATE- und DELETE-Anweisungen
• Felder für ORDER BY, GROUP BY und DISTINCT
• Felder für Multi-Table-JOIN
  

Das herausragendste Feld steht an erster Stelle

• Wählen Sie Felder mit besseren Filtereigenschaften aus und platzieren Sie diese an erster Stelle, beispielsweise Bestellnummer, Benutzer-ID usw. Es wird im Allgemeinen nicht empfohlen, Felder mit besseren Filtereigenschaften wie Typ und Status an erster Stelle zu platzieren.
• Der Index folgt dem Prinzip des linken Präfixes. Wenn ein gemeinsamer Index (a, b, c) erstellt wird, kann der Index nur verwendet werden, wenn die Abfragebedingung (a) oder (a, b) oder (a, b, c) enthält. Wenn (a, c) als Bedingung verwendet wird, kann nur der Index der Spalte a verwendet werden. Daher muss sichergestellt werden, dass die Anzahl der von a zurückgegebenen Spalten nicht zu groß ist, da sonst das Anweisungsdesign unangemessen ist. (b, c) kann nicht verwendet werden.
• Erstellen Sie gemeinsame Indizes entsprechend (vermeiden Sie Redundanz), (a,b,c) ist gleichwertig mit (a), (a,b), (a,b,c)
  

Index Tabus

• Erstellen Sie keine Indizes für Spalten mit niedriger Kardinalität, wie etwa „Geschlecht“.
• Führen Sie keine mathematischen Operationen oder Funktionsoperationen auf Indexspalten aus.
• Indizieren Sie keine häufig verwendeten kleinen Tabellen
• Vermeiden Sie die Verwendung von Fremdschlüsseln
• Fremdschlüssel dienen zum Schutz der referenziellen Integrität und können auf der Geschäftsseite implementiert werden.
• Operationen an der übergeordneten und der untergeordneten Tabelle beeinflussen sich gegenseitig und verringern die Verfügbarkeit.
• INNODBs eigene Einschränkungen für Online-DDL
  

Einschränkungen von Indizes in MySQL

Die Gesamtlänge des MYISAM-Speicher-Engine-Index darf 1000 Byte nicht überschreiten
Spalten vom Typ BLOB und TEXT können nur Präfixindizes erstellen
MySQL unterstützt derzeit nicht die Verwendung funktionaler Indizes. Wenn Ungleichheit (!= oder <>) verwendet wird, kann MySQL den Index nicht verwenden.
Nach der Verwendung von Funktionsoperationen (wie z. B. abs (Spalte)) auf dem Filterfeld kann MySQL den Index nicht verwenden.
Wenn die Join-Bedingungsfeldtypen in der Join-Anweisung inkonsistent sind, kann MySQL den Index nicht verwenden. Wenn bei Verwendung der LIKE-Operation die Bedingung mit einem Platzhalter beginnt (z. B. „%abc...“), kann MySQL den Index nicht verwenden.
Bei der Verwendung von Abfragen mit ungleichen Werten kann MySQL keine Hash-Indizes verwenden.

Aussagekräftige Designvorgaben

Verwenden vorbereiteter Anweisungen

• Die reine Parameterübergabe ist effizienter als die Übergabe von SQL-Anweisungen.
• Einmal analysieren, oft verwenden
• Reduzieren Sie die Wahrscheinlichkeit einer SQL-Injection
  

Vermeiden impliziter Konvertierungen

Dies führt dazu, dass der Index fehlschlägt

Nutzen Sie Präfixindizes

• Muss das äußerste linke Präfix sein
• Es ist nicht möglich, zwei Bereichsbedingungen gleichzeitig zu verwenden
• Abfragen, die nicht das führende Zeichen % verwenden, wie etwa „%ab“
  

Verwenden Sie keine negativen Abfragen, wie z. B. „nicht in/wie“

• Index kann nicht verwendet werden, was zu einem vollständigen Tabellenscan führt
• Vollständiger Tabellenscan führt zu reduzierter Pufferpoolauslastung
  

Vermeiden Sie die Verwendung gespeicherter Prozeduren, Trigger, UDFs, Ereignisse usw.

• Lassen Sie die Datenbank tun, was sie am besten kann
• Reduzieren Sie die Geschäftskopplung, um Platz für Sharding und Sharding zu lassen
• Vermeidung von BUGs
  

Vermeiden Sie JOINs bei großen Tabellen

MySQL eignet sich am besten für Primärschlüssel-/Sekundärindexabfragen für einzelne Tabellen.
JOIN verbraucht mehr Speicher und generiert temporäre Tabellen

Vermeiden Sie Berechnungen in der Datenbank

• MySQL ist nicht gut bei mathematischen Operationen und logischen Urteilen
• Index kann nicht verwendet werden
  

Reduzieren Sie die Anzahl der Interaktionen mit der Datenbank

• EINFÜGEN ... BEIM UPDATE DES DOPPELTEN SCHLÜSSELS
• ERSETZEN IN, EINFÜGEN IGNORIEREN, EINFÜGEN IN WERTE(),(),()
• AKTUALISIEREN ... WO ID IN (10,20,50,…)

Paging angemessen nutzen

Begrenzen Sie die Anzahl der in der Seitennummerierung angezeigten Seiten. Nur die vorherige und die nächste Seite können angeklickt werden. Es wird eine verzögerte Zuordnung verwendet.

Wie verwendet man Paging richtig?

Angenommen, es gibt eine Paging-Anweisung wie die folgende: SELECT * FROM table ORDER BY id LIMIT 10000, 10. MySQL behandelt LIMIT OFFSET so, dass alle Daten von OFFSET+LIMIT abgerufen werden, dann OFFSET entfernt wird und das untere LIMIT zurückgegeben wird. Wenn der OFFSET-Wert groß ist, ist die Abfrageleistung von MySQL daher sehr niedrig. Dies kann durch die Verwendung von id > n gelöst werden:

Die Methode, ID > n zu verwenden, hat Einschränkungen. Das Problem diskontinuierlicher IDs kann gelöst werden, indem beim Umblättern gleichzeitig die letzte ID übergeben wird.

http://example.com/page.php?last=100 
Wähle * aus Tabelle, in der ID <100, Sortierung nach ID, Abstiegsgrenze 10 
//Vorherige Seite http://example.com/page.php?first=110 
Wählen Sie * aus der Tabelle, in der die ID>110 ist. Sortieren nach ID, Abstiegslimit 10

Der größte Nachteil dieser Methode besteht darin, dass die Seite nicht aktualisiert wird, wenn während des Durchsuchens ein Einfüge-/Löschvorgang stattfindet, und die Gesamtzahl der Seiten möglicherweise immer noch auf Grundlage der neuen Anzahl (*) berechnet wird, was letztendlich dazu führen kann, dass auf einige Datensätze nicht mehr zugegriffen werden kann. Um dieses Problem zu beheben, können Sie weiterhin die aktuelle Seitenzahl eingeben und prüfen, ob es Vorgänge wie Einfügen/Löschen gab, die sich auf die Gesamtzahl der Datensätze seit dem letzten Seitenumblättern auswirken, und diese zwischenspeichern.

Wählen Sie * aus der Tabelle, wobei ID >= (Wählen Sie ID aus der Tabelle, sortiert nach ID-Limit #offset#, 1)

• Lehnen Sie großen SQL-Code ab und teilen Sie ihn in kleinen SQL-Code auf.
• Nutzen Sie den Abfrage-Cache
• Nutzen Sie die Vorteile von Multi-Core-CPUs
• Verwenden Sie „in“ anstelle von „oder“. Der Wert von „in“ darf 1000 nicht überschreiten.
• Verwenden Sie nicht order by rand()
• Verwenden Sie die EXPLAIN-Diagnose, um die Erstellung temporärer Tabellen zu vermeiden
  

Die EXPLAIN-Anweisung (ausgeführt im MySQL-Client) kann Informationen darüber erhalten, wie MySQL SELECT-Anweisungen ausführt. Durch Ausführen von EXPLAIN für die SELECT-Anweisung können Sie feststellen, ob MySQL beim Ausführen der SELECT-Anweisung Indizes, vollständige Tabellenscans, temporäre Tabellen, Sortierungen und andere Informationen verwendet. Versuchen Sie, MySQL zu vermeiden, vollständige Tabellenscans durchzuführen, temporäre Tabellen zu verwenden, zu sortieren usw. Weitere Einzelheiten finden Sie in der offiziellen Dokumentation.

Verwenden Sie union all anstelle von union

Was ist der Unterschied zwischen „Union All“ und „Union“?

Die Schlüsselwörter „union“ und „union all“ führen beide zwei Ergebnismengen zu einer zusammen, unterscheiden sich jedoch hinsichtlich Verwendung und Effizienz.

Nachdem die Union-Tabelle verknüpft wurde, werden die doppelten Datensätze herausgefiltert. Nachdem die Tabelle verknüpft wurde, wird der resultierende Ergebnissatz sortiert, die doppelten Datensätze werden gelöscht und anschließend das Ergebnis zurückgegeben. wie:

wähle * aus test_union1 
Union-Auswahl * aus test_union2

Wenn dieses SQL ausgeführt wird, ruft es zuerst die Ergebnisse der beiden Tabellen ab, verwendet dann den Sortierbereich zum Sortieren und Löschen doppelter Datensätze und gibt schließlich den Ergebnissatz zurück. Wenn die Datenmenge in der Tabelle groß ist, kann dies zu einer Datenträgersortierung führen.

Union all führt einfach die beiden Ergebnisse zusammen und gibt sie zurück. Wenn in den beiden zurückgegebenen Ergebnissätzen doppelte Daten vorhanden sind, enthält der zurückgegebene Ergebnissatz auf diese Weise die doppelten Daten.

In Bezug auf die Effizienz ist „union all“ viel schneller als „union“. Wenn Sie also bestätigen können, dass die beiden zusammengeführten Ergebnismengen keine doppelten Daten enthalten, verwenden Sie „union all“ wie folgt:

wähle * aus test_union1 Vereinigung alles wähle * aus test_union2

• Das Programm sollte über einen Mechanismus zum Erfassen von SQL-Ausnahmen verfügen
• Verhindern, dass eine einzelne SQL-Anweisung mehrere Tabellen gleichzeitig aktualisiert
• Verwenden Sie nicht select *. Die SELECT-Anweisung ruft nur die erforderlichen Felder ab.
• Verbraucht CPU und IO, verbraucht Netzwerkbandbreite
• Überdeckende Indizes können nicht verwendet werden
• Reduzieren Sie die Auswirkungen von Änderungen in der Tabellenstruktur
• Da es groß ist, kann select/join eine temporäre Tabelle erzeugen
• UPDATE- und DELETE-Anweisungen verwenden kein LIMIT
• Die INSERT-Anweisung muss den Feldnamen explizit angeben und darf nicht INSERT INTO table() verwenden.
• INSERT-Anweisungen werden stapelweise übermittelt (INSERT INTO table VALUES(),(),()…) und die Anzahl der Werte überschreitet 500 nicht.
• Verwenden Sie zum Zählen der Anzahl der Datensätze in einer Tabelle COUNT(*) anstelle von COUNT(primary_key) und COUNT(1). Hinweis: Dies gilt nur für MyISAM.
• Zur Datenaktualisierung wird empfohlen, mithilfe eines Sekundärindex zuerst den Primärschlüssel abzufragen und dann die Daten basierend auf dem Primärschlüssel zu aktualisieren.
• Datenbankübergreifende Abfragen sind verboten
• Unterabfragen sind verboten. Es wird empfohlen, Unterabfragen in zugehörige Abfragen umzuwandeln.
• Überprüfen Sie bei der Programmverarbeitung von Feldern des Typs varchar die Benutzereingaben und überschreiten Sie nicht die voreingestellte Länge.
  

Tabellenspezifikationen

Wenn das Datenvolumen einer einzelnen Tabelle 5 Millionen übersteigt oder die Datenkapazität innerhalb von ein bis zwei Jahren 10 G übersteigt, sollten Sie eine Aufteilung der Tabelle in Betracht ziehen. Dabei ist es notwendig, die Migration historischer Daten oder die Selbstlöschung historischer Daten durch die Anwendung im Voraus in Betracht zu ziehen. Sie können eine gleichmäßige und ausgewogene Aufteilung oder eine Aufteilung gemäß den Geschäftsregeln verwenden. Die aufzuteilenden Datentabellen müssen die Aufteilungsstrategie mit dem DBA besprechen

• Verwenden Sie HASH, um die Tabelle zu streuen, verwenden Sie eine Dezimalzahl als Suffix des Tabellennamens und der Index beginnt bei 0
• Die nach Datum und Uhrzeit unterteilte Tabelle muss dem Format JJJJ[MM][tt][HH] entsprechen.
• Wenden Sie geeignete Strategien zum Sharding von Datenbanken und Tabellen an. Beispielsweise 1000 Bibliotheken und 10 Tabellen, 100 Bibliotheken und 100 Tabellen usw.
• Die Verwendung partitionierter Tabellen ist verboten. Partitionierte Tabellen stellen strenge Anforderungen an Partitionsschlüssel. Je größer partitionierte Tabellen werden, desto schwieriger wird es, DDL, SHARDING und die Wiederherstellung einzelner Tabellen durchzuführen.
• Teilen Sie große Felder und Felder mit geringer Zugriffshäufigkeit auf, um heiße und kalte Daten zu trennen
  

Verhaltenskodex

• Das Importieren und Exportieren von Daten in Stapeln muss dem DBA vorab zur Unterstützung und Beobachtung gemeldet werden.
• Online-Backend-Management und statistische Abfragen aus der Datenbank verbieten
• Anwendungskonten mit Superberechtigungen sind verboten
• Wenn ein Produkt aufgrund eines Problems ausfällt, das nicht durch die Datenbank verursacht wurde, benachrichtigen Sie umgehend den DBA, um bei der Fehlerbehebung zu helfen.
• Werbeaktivitäten oder neue Funktionen müssen dem DBA im Voraus zur Verkehrsbewertung gemeldet werden
• Wenn Datenbankdaten verloren gehen, kontaktieren Sie den DBA rechtzeitig zur Wiederherstellung
• Mehrere Änderungsvorgänge an einer einzelnen Tabelle müssen zu einem Vorgang zusammengefasst werden.
• Speichern Sie keine Geschäftslogik in der MySQL-Datenbank
• Die Auswahl der Datenbanklösung und das Design größerer Projekte müssen dem DBA im Voraus mitgeteilt werden
• Bei besonders wichtigen Datenbanken und Tabellen sollten Sie im Voraus mit dem DBA kommunizieren, um die Prioritäten für Wartung und Sicherung festzulegen.
• Aktualisieren oder fragen Sie die Datenbank während der Hauptgeschäftszeiten nicht stapelweise ab. Weitere Spezifikationen
• Bei der Übermittlung von Anforderungen zur Online-Tabellenerstellung und -änderung müssen alle relevanten SQL-Anweisungen detailliert angegeben werden
  

Andere Spezifikationen

Es wird nicht empfohlen, Protokolldaten auf MySQL zu speichern. Hbase oder OceanBase sollten Vorrang erhalten. Wenn Speicher erforderlich ist, bitten Sie den DBA, die Verwendung komprimierter Tabellen zur Speicherung zu prüfen.

Oben sind die Details der äußerst detaillierten MySQL-Nutzungsspezifikationen aufgeführt. Weitere Informationen zu MySQL-Nutzungsspezifikationen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!