Beherrschen Sie MYSQL Advanced

Kostenlose Lernempfehlung: MySQL-Video-Tutorial

Artikelverzeichnis

• 1 Vorwort
• • 1.1 Datenbankarchitektur
  • 1.2 Überwachungsinformationen
• 2 Faktoren, die die Datenbank beeinflussen
• 2.1 Ultrahohe QPS und TPS 2.5.2 Die Auswirkungen großer Tabellen auf DDL-Operationen
  • 2.5.3 Wie man mit großen Tabellen in der Datenbank umgeht
  • 2.6 Große Transaktionen
  • 2.6.1 Was ist eine Transaktion
  • 2.6.2 Atome von Transaktionen ATOMICITY
    • 2.6.3 KONSISTENZ von Transaktionen
    • 2.6.4 ISOLATION von Transaktionen
    • 2.6.5 HALTBARKEIT von Transaktionen
    2.6.7 Was ist eine große Transaktion
  • • 1 Einführung
    • A Ein großer Teil des Drucks auf den Server geht von der Leistung der Datenbank aus. Wenn keine stabile Datenbank- und Serverumgebung vorhanden ist, ist der Server anfällig für Ausfälle oder sogar Ausfallzeiten, was zu Konsequenzen führt. Es ist auch nicht messbar, daher ist die Datenbankleistung optimiert essentiell.
    • 1.1 Datenbankarchitektur
    • Die allgemeine Datenbankarchitektur ist ein Master-Server mit mehreren oder einem Dutzend Slave-Servern für die Master-Slave-Synchronisation. Wenn der Master-Server ausfällt, muss der Programmierer manuell einen Slave-Server auswählen mit den neuesten Daten übernimmt vom Master-Server und synchronisiert sich dann mit dem neuen Master-Server. Da es jedoch viele Slave-Server gibt, ist dieser Vorgang manchmal recht zeitaufwändig und stellt auch eine Herausforderung für die Kapazität der Netzwerkkarte dar.
  1.2 Überwachungsinformationen

QPS & TPS: Je höher der Wert, desto besser. Parallelität: Die Anzahl der gleichzeitig verarbeiteten Anfragen. CPU-Auslastung: Je niedriger, desto besser.

Disk IO: Je höher die Lese- und Schreibleistung, desto besser.

Hinweis: Generell wird Unternehmen davon abgeraten, vor und nach größeren Werbeaktionen Datenbanksicherungen auf der Hauptdatenbank durchzuführen oder solche Pläne vor Großveranstaltungen abzubrechen, da dies die Leistung des Servers ernsthaft beeinträchtigen würde.

2 Faktoren, die die Datenbank beeinflussen

Es gibt viele Faktoren, die die Datenbank beeinflussen, wie zum Beispiel: SQL-Abfragegeschwindigkeit, Serverhardware, Netzwerkkartenverkehr, Festplatten-E/A usw. Wir werden es später im Detail erklären Feedback aus einigen Überwachungsinformationen, die uns gegeben wurden, und wie wir diese optimieren sollten.

2.1 Ultrahohe QPS und TPS

Aufgrund ineffizienter SQL besteht häufig das Risiko extrem hoher QPS und TPS. Während eines allgemeinen Aktionszeitraums wird die Anzahl der Besuche auf der Website stark erhöht und auch die QPS und TPS der Datenbank werden erhöht.
Was ist QPS: Pro Sekunde verarbeitete Abfragen. Wenn wir beispielsweise eine CPU haben und eine SQL in 10 ms verarbeiten können, können wir 100 SQLs in 1 Sekunde verarbeiten, QPS<=100. Wenn wir jedoch nur eine SQL in 100 ms verarbeiten, können wir 10 SQLs in 1 verarbeiten Zweitens, QPS< =10, diese beiden Situationen sind sehr unterschiedlich, also versuchen Sie, die SQL-Leistung zu optimieren.


2.2 Massive Parallelität und extrem hohe CPU-Auslastung

Was sind also die Risiken in dieser Situation? Bei hoher Parallelität kann die Anzahl der Datenbankverbindungen erschöpft sein. Versuchen Sie in diesem Fall, die Datenbankparameter

auf einen größeren Wert einzustellen (der Standardwert ist 100). Wenn dieser Wert überschritten wird, wird ein Fehler von 500 angezeigt Situation gemeldet werden.

Bei extrem hoher CPU-Auslastung kann es aufgrund der Erschöpfung der CPU-Ressourcen zu Ausfallzeiten kommen.

2.3 Disk IO

Einer der Engpässe der Datenbank ist Disk IO, was die folgenden Risiken mit sich bringt:

Die Leistung von Disk IO sinkt plötzlich Dies passiert oft, wenn die heißen Daten größer sind als der verfügbare Speicher der Server. Normalerweise kann diese Situation nur durch die Verwendung eines schnelleren Festplattengeräts gelöst werden.

Andere geplante Aufgaben, die viel Festplattenleistung verbrauchen

Wir haben dies auch oben erwähnt. Es ist am besten, die Sicherung auf der Master-Datenbank vor dem großen Verkauf zu vermeiden oder sie auf dem Slave-Server durchzuführen, geplante Aufgaben anzupassen und die Festplatte auszuführen Wartung.
max_connections

2.4 Netzwerkkartenverkehr

Offensichtlich besteht bei übermäßigem Netzwerkkartenverkehr das Risiko, dass die Netzwerkkarten-E/A voll ist.

Die allgemeine Netzwerkkarte ist eine Gigabit-Netzwerkkarte (1000 MB/8 ≈ 100 MB).

Wenn die Anzahl der Verbindungen die maximale Kapazität der Netzwerkkarte überschreitet, kann der Dienst keine Verbindung herstellen. Wie können wir also verhindern, dass keine Verbindung hergestellt werden kann? die Datenbank:

1. Reduzieren Sie die Anzahl der Slave-Server
   Da jeder Slave-Server Protokolle vom Master-Server kopieren muss, ist der Netzwerkkartenverkehr umso größer, je mehr Slave-Server vorhanden sind.
2. Führen Sie hierarchisches Caching durch
   Vermeiden Sie unbedingt den plötzlichen Anstieg der Back-End-Besuche, der durch den plötzlichen Ausfall des Front-End-Cache verursacht wird.
3. Vermeiden Sie die Verwendung von select * zum Abfragen.
   Dies ist die einfachste Methode zur Datenbankoptimierung. Das Abfragen unnötiger Felder verbraucht ebenfalls viel Netzwerkverkehr.
4. Trennung des Unternehmensnetzwerks und des Servernetzwerks
   Dadurch kann vermieden werden, dass die Master-Slave-Synchronisierung oder Netzwerksicherung die Netzwerkleistung beeinträchtigt.

2,5 große Uhr

Welche Art von Uhr kann man als große Uhr bezeichnen? Tatsächlich ist alles relativ und es wird unterschiedliche Einschränkungen für verschiedene Speicher-Engines geben. Die Datenspeicherung wie NoSQL begrenzt die Anzahl der Zeilen in der Tabelle nicht. Theoretisch kann sie so lange gespeichert werden, wie es die Festplattenkapazität zulässt. Wenn jedoch die Anzahl der Zeilen in einer Tabelle mehrere zehn Millionen Zeilen überschreitet, hat dies große Auswirkungen auf die Leistung der Datenbank. Dann können wir die Eigenschaften großer Tabellen zusammenfassen:

• Die Anzahl der Datensatzzeilen ist riesig, eine einzelne Tabelle übersteigt zig Millionen Zeilen
• Die Tabellendatendatei ist riesig, die Tabellendatendatei übersteigt 10G

Aber sogar Wenn es die oben genannten Merkmale erfüllt, hat dies möglicherweise auch keine großen Auswirkungen auf die Leistung unserer Datenbank. Daher ist diese Aussage beispielsweise in der Protokolltabelle einer allgemeinen Datenbank relativ, selbst wenn die Anzahl der aufgezeichneten Zeilen groß ist Die Dateigröße ist groß, wir fügen sie im Allgemeinen nur hinzu und fragen sie ab, nicht Es erfordert eine große Anzahl von i-Änderungs- und Löschvorgängen, sodass es keinen großen Einfluss auf die Datenbankleistung hat.
Aber wenn es eines Tages aufgrund geschäftlicher Veränderungen notwendig wird, Spalten zu dieser 10G-Protokolltabelle hinzuzufügen, dann wird der Arbeitsaufwand zweifellos enorm sein.

2.5.1 Die Auswirkungen großer Tabellen auf Abfragen

Große Tabellen führen häufig zu langsamen Abfragen, was es schwierig macht, die erforderlichen Daten innerhalb eines bestimmten Zeitraums herauszufiltern.
Beispielsweise gibt es in einer Protokolltabelle mit mehreren zehn Millionen Daten ein Feld namens Bestellquelle, das die Plattform aufzeichnet, auf der die Bestellung generiert wurde. Wenn das Unternehmen dies zu Beginn nicht benötigt, hat dies keine Auswirkungen auf die Datenbankleistung. Aufgrund späterer Geschäftsanforderungen muss jedoch überprüft werden, von welcher Plattform diese zig Millionen Daten stammen. Das ist ein großes Problem.
Da es für diese Aufträge nur wenige Quellkanäle gibt, ist der Unterschied sehr gering. Wenn Sie also bestimmte Daten aus mehreren zehn Millionen Daten abfragen, verbraucht dies viel Festplatten-E/A und verringert die Effizienz der Festplatte erheblich. Jedes Mal, wenn ein Benutzer eine Bestellung ansieht, wird die Quelle der Bestellung aus der Datenbank abgefragt, was zu einer großen Anzahl langsamer Abfragen führt.

2.5.2 Die Auswirkungen großer Tabellen auf DDL-Operationen

Die Auswirkungen großer Tabellen auf DDL-Operationen, welche Risiken bringt dies für uns mit sich?

1. Das Erstellen eines Indexes dauert lange
   Vor MySQL-Version 5.5 sperrte die Datenbank die Tabelle beim Erstellen des Indexes, obwohl die Tabelle aufgrund des MySQL-Replikationsmechanismus nicht gesperrt war auf dem neuen Host ausgeführt und dann über den Protokollmodus an den Slave gesendet. Dies führt zu einer langen Master-Slave-Verzögerung und beeinträchtigt den normalen Betrieb.
2. Das Ändern der Tabellenstruktur erfordert das Sperren der Tabelle für eine lange Zeit.
   Das Sperren der Tabelle während des Änderungsprozesses der Tabellenstruktur birgt das Risiko einer langen Master-Slave-Verzögerung. Aufgrund des Master-Slave-Replikationsmechanismus unseres MySQL werden häufig alle Tabellenstrukturoperationen zuerst auf dem Host ausgeführt und dann über den Protokollmodus für denselben Vorgang an den Slave übertragen. Anschließend wird die Master-Slave-Replikation der Tabellenstruktur abgeschlossen .
   Angenommen, wir ändern die Struktur einer Tabelle und die Änderungszeit auf dem Master-Server beträgt 480 Sekunden, dann beträgt unsere Änderungszeit auf der Slave-Datenbank ebenfalls 480 Sekunden. Da MySQL derzeit einen einzelnen Thread für die Master-Slave-Replikation verwendet, können nach der Änderung einer großen Tabelle keine anderen Datenänderungsvorgänge ausgeführt werden, bis die relevanten Vorgänge auf dem Slave-Server abgeschlossen sind. Dies führt daher zu einer Master-Slave-Verzögerung von at mindestens 480er.
   Gleichzeitig wird der normale Datenbetrieb beeinträchtigt, was dazu führt, dass alle Einfügungsvorgänge blockiert werden, die Anzahl der Verbindungen erheblich zunimmt und der Server überlastet wird, was zu einem 500-Verbindungsfehler auf dem Server führt.

2.5.3 Umgang mit großen Tabellen in der Datenbank

1. Teilen Sie eine große Tabelle in mehrere kleine Tabellen auf
   Schwierigkeit:
   1. Wählen Sie den Primärschlüssel der Tabelle
   2. Partitionsübergreifende Daten nach dem Teilen der Tabelle Abfrage und Statistik
2. Historische Datenarchivierung großer Tabellen
   Funktion: Reduzieren Sie die Auswirkungen auf das Front-End- und Back-End-Geschäft
   Schwierigkeit:
   1. Auswahl des Archivierungszeitpunkts
   2. So führen Sie Archivierungsvorgänge durch

2.6 Große Transaktionen

2.6.1 Was ist eine Transaktion

1. Transaktionen sind eines der wichtigen Merkmale, die das Datenbanksystem von allen anderen Dateisystemen unterscheiden.

2. Eine Transaktion ist eine Reihe atomarer SQL-Anweisungen oder eine unabhängige Arbeitseinheit. +
   Daher müssen Transaktionen die folgenden vier Merkmale erfüllen: Atomizität, Konsistenz, Isolation und Haltbarkeit.

2.6.2 ATOMICITY von Transaktionen

Definition: Eine Transaktion muss als unteilbare Mindestarbeitseinheit betrachtet werden. Alle Vorgänge in der gesamten Transaktion werden entweder erfolgreich übermittelt oder alle sind fehlgeschlagen. Bei einer Transaktion ist es unmöglich, nur einen Teil der Vorgänge auszuführen.
Beispiel:
A möchte 1.000 Yuan an B überweisen. Wenn 1.000 Yuan vom Konto von A abgebucht werden, wird der Kontostand von A in der Datenbank um 1.000 abgezogen, aber wenn er zum Kontostand von B addiert wird, schlägt der Server fehl auf das Konto von A zurückgreifen, die Atomizität der Transaktion aufrechterhalten und entweder gemeinsam erfolgreich sein oder scheitern.

2.6.3 Transaktionskonsistenz (KONSISTENZ)

Definition: Konsistenz bedeutet, dass eine Transaktion die Datenbank von einem Konsistenzzustand in einen anderen Konsistenzzustand umwandelt, und zwar vor Beginn der Transaktion und nach Ende der Transaktion. Die Integrität der Daten in der Datenbank ist nicht gefährdet.
Beispiel:
Die 1000 Blöcke von A in der Bank werden an B übertragen, der Kontostand von A ist 0, der Kontostand von B ändert sich von 0 auf 1000, aber von Anfang bis Ende ist A+B = 1000 (Kontostand von A) + 0 (Kontostand von B) = 0 (Saldo von A) + 1000 (Saldo von B), das heißt, der Gesamtsaldo von A und B bleibt unverändert, immer noch 1.000 Yuan von Anfang bis Ende.

2.6.4 Transaktionsisolation (ISOLATION)

Definition: Isolation erfordert, dass die Änderung von Daten in der Datenbank durch eine Transaktion für andere Transaktionen nicht sichtbar ist, bevor sie abgeschlossen ist.
Beispiel:
A in der Bank hebt 500 Yuan vom Kontostand von A ab. Bevor die Abhebungstransaktion übermittelt wird, wird eine Transaktion ausgeführt, um den Kontostand von A abzufragen. Da die Auszahlungstransaktion vor der Übermittlung noch nicht übermittelt wurde, ist der Transaktionsprozess für andere Unternehmen unsichtbar.

• Vier im SQL-Standard definierte Isolationsstufen

  • READ UNCOMMITED

    • Nicht festgeschriebene Transaktionen sind für die Außenwelt sichtbar, was wir oft als Dirty Read bezeichnen, und die abgefragten Daten werden als Dirty Data bezeichnet .

  • READ COMMITED

    • Die Standardisolationsstufe in vielen Daten kann erst nach Übermittlung der Transaktion gelesen werden, dh die Transaktion ist für die Außenwelt nicht sichtbar.

  • WIEDERHOLBARES LESEN

    • Eine höhere Ebene als die festgeschriebene Lesetransaktion. Bei einer wiederholbaren Leseisolationsebene werden die Daten in der Tabelle in einer nicht festgeschriebenen Transaktion abgefragt und in einer anderen wird ein Datenelement in diese Tabelle eingefügt Wenn Sie jedoch zur nicht festgeschriebenen Transaktion zurückkehren und die Tabellendaten erneut abfragen, sind die Ergebnisse dieselben wie bei der letzten Abfrage.
    • Aber Sie können die Daten gerade jetzt in der lesefestgeschriebenen Isolationsstufe finden.
    • Sehen Sie sich die Isolationsstufenanweisung der aktuellen Datenbank an:
      zeige Variablen wie „%iso%“show variables like '%iso%'
    • 修改当前数据库隔离级别语句：
      set session tx_isolation='read-committed'
    Änderung Aktuelle Datenbank Isolationsstufenanweisung:
    set session tx_isolation='read-committed'

  • Serialisierbar (SERIALIZABLE)
    Die höchste Isolationsstufe. Vereinfacht ausgedrückt ist jedes gelesene Datenelement gesperrt, was zu einer großen Anzahl von Sperrzeitüberschreitungen und Sperrbelegungsproblemen führen kann. Daher verwenden wir diese Isolationsstufe in der Praxis selten. Wir werden die Verwendung dieser Isolationsstufe nur dann in Betracht ziehen, wenn die Datenkonsistenz nicht unbedingt erforderlich ist und keine Parallelität akzeptabel ist.

  • Isolation:
    Uncommitted Read< Wiederholbare Read> Parallelität:

  • Uncommitted Read> ; Serialisierbar
  2.6 .5 Transaktionsdauerhaftigkeit (DAUERHAFTIGKEIT)

  Definition: Sobald eine Transaktion festgeschrieben wurde, werden ihre Änderungen dauerhaft in der Datenbank gespeichert. Selbst wenn das System zu diesem Zeitpunkt abstürzt, gehen die übermittelten geänderten Daten nicht verloren (mit Ausnahme physischer Faktoren wie Festplattenschäden). Beispiel:

  Benutzer A zahlt 1.000 Yuan auf sein Konto ein, auch wenn das Banksystem nach der Wiederherstellung abstürzt, es sei denn, A bearbeitet den Saldo. Dies ändert sich nicht ist die Dauerhaftigkeit der Transaktion.

  
  
  2.6.7 Was ist ein Großereignis?

  So viel gesagt: Was ist ein Großereignis? Unter großen Transaktionen versteht man Transaktionen, deren Ausführung lange dauert und die große Datenmengen verarbeiten. Es gibt beispielsweise ein Finanzprodukt, das jeden Tag das Einkommen aller Benutzer zählt und es auf den Kontostand des Benutzers aktualisiert, wenn ein Rollback fällig ist Bei einem Fehler in der Mitte wird die für die Transaktion erforderliche Zeit noch unermesslicher. Wenn der Aktualisierungsprozess nicht berücksichtigt wird, wird das Guthaben des Benutzers gesperrt, was zu dem Problem führt, dass nicht alle Benutzer das Guthaben verwenden können.

  
  

  Welche Risiken werden große Transaktionen mit sich bringen
  :
  Sperren Sie zu viele Daten, was zu vielen Blockierungen und Sperrzeitüberschreitungen führt
  Die für das Rollback erforderliche Zeit ist relativ lang
  1. Lange Ausführungszeit, leicht zu Master- Sklavenverzögerung
  Wie vermeide ich große Dinge
  ?
  Vermeiden Sie die gleichzeitige Verarbeitung zu vieler Daten.
  Entfernen Sie unnötige SELECT-Operationen in Transaktionen.
  2. Wenn Sie die beiden oben genannten Punkte befolgen können, kann das Auftreten großer Ereignisse grundsätzlich vermieden werden.

  Weitere verwandte kostenlose Lernempfehlungen: MySQL-Tutorial(Video)

  Das obige ist der detaillierte Inhalt vonBeherrschen Sie MYSQL Advanced. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!