Interpretieren von MySQL-Client- und Serverprotokollen

Wenn Sie eine hohe Leistung Ihres MySQL-Servers benötigen, sollten Sie sich am besten mit der Funktionsweise der Optimierung und Ausführung von Abfragen durch MySQL befassen. Wenn Sie diese verstehen, sind die meisten Abfrageoptimierungen fundiert und der gesamte Abfrageoptimierungsprozess wird logischer. Das folgende Diagramm zeigt den Vorgang, wie MySQL eine Abfrage ausführt:

1. Der Client sendet die SQL-Anweisung an den Server.
2. Der Server überprüft den Abfragecache. Wenn sich Daten im Cache befinden, wird das zwischengespeicherte Ergebnis direkt zurückgegeben. Andernfalls wird die SQL-Anweisung an den nächsten Link weitergegeben.
3. Nachdem der Server die SQL-Anweisung analysiert, vorverarbeitet und optimiert hat, übergibt er sie an den Abfrageoptimierer, um einen Abfrageplan zu erstellen.
4. Die Abfrageausführungs-Engine führt den Abfrageplan aus, indem sie die Speicher-Engine-Schnittstelle aufruft.
5. Der Server gibt die Abfrageergebnisse an den Client zurück.

Die oben genannten Schritte sind alle komplex und in den nächsten Artikeln wird jeder Link ausführlich beschrieben. Der Abfrageoptimierungsprozess ist besonders komplex und es ist wichtig, ihn zu verstehen.

MySQL-Client/Server-Protokoll

Obwohl es nicht notwendig ist, die internen Details des MySQL-Client/Server-Protokolls zu verstehen, ist es wichtig, zu verstehen, wie es auf einer höheren Anwendungsebene funktioniert. Dieses Protokoll ist Halbduplex, was bedeutet, dass der MySQL-Server nicht gleichzeitig Nachrichten senden und empfangen und Nachrichten nicht in mehrere kürzere Nachrichten aufteilen kann. Einerseits macht dieser Mechanismus die MySQL-Kommunikation einfach und schnell, andererseits bringt er aber auch einige Einschränkungen mit sich. Dies bedeutet beispielsweise, dass eine Flusskontrolle unmöglich ist und dass, nachdem eine Partei eine Nachricht gesendet hat, die andere Partei die gesamte Nachricht empfangen muss, bevor sie antworten kann. Es ist wie beim Tischtennisspiel: Hin und Her. Immer hat nur eine Seite den Ball, und nur wenn man den Ball bekommt, kann man ihn zurückschlagen.

Der Client sendet Abfragen in einem einzigen Paket an den Server. Daher ist es wichtig, bei großen Abfragen max_allowed_packet zu konfigurieren. Sobald der Client eine Abfrage sendet, kann er nur auf die Rückgabe der Ergebnisse warten.

Im Gegensatz dazu besteht die Antwort des Servers normalerweise aus mehreren Paketen. Sobald der Server antwortet, muss der Client den gesamten Ergebnisset abrufen. Der Client kann nicht einfach ein paar Zeilen abrufen und dem Server dann sagen, dass er die restlichen Daten nicht senden soll. Wenn der Client nur die ersten paar Datenzeilen zurückgeben muss, kann er nur warten, bis der Server alle Daten zurückgegeben hat, und dann die unnötigen Daten verwerfen oder die Verbindung grob trennen. Keine der beiden Methoden ist eine gute Wahl, daher ist eine geeignete LIMIT-Klausel sehr wichtig.

Die meisten MySQL-Verbindungsbibliotheken unterstützen das Abrufen des gesamten Ergebnissatzes und das Zwischenspeichern im Speicher oder das Abrufen der erforderlichen Datenzeilen. Das Standardverhalten besteht normalerweise darin, den gesamten Ergebnisset abzurufen und im Speicher zwischenzuspeichern. Dies ist wichtig zu wissen, da der MySQL-Server die Sperren und Ressourcen für diese Abfrage erst freigibt, wenn alle angeforderten Zeilen zurückgegeben wurden. Bei den meisten Client-Bibliotheken wird angenommen, dass die Daten vom Server abgerufen werden. Tatsächlich werden sie jedoch möglicherweise nur aus dem Cache gelesen. Dies ist in den meisten Fällen kein Problem, eignet sich jedoch nicht für große Datenabfragen, die lange dauern oder viel Speicher beanspruchen. Wenn Sie angeben, dass Abfrageergebnisse nicht zwischengespeichert werden sollen, ist der Speicherverbrauch geringer und die Ergebnisse können schneller verarbeitet werden. Der Nachteil besteht darin, dass diese Methode bei Abfragen zu serverseitigen Sperren und Ressourcennutzung führt.

Am Beispiel von PHP sind die folgenden Abfragecodes in PHP häufig zu finden:

<?php
$link = mysql_connect('localhost', 'Benutzer', 'Passwort');
$result = mysql_query('AUSWÄHLEN * AUS riesige_Tabelle', $link);
während ($row = mysql_fetch_array($result)) {
  //Datenergebnisse verarbeiten}

?>

Dieser Code scheint nur die Zeilen abzurufen, die benötigt werden. Allerdings legt diese Abfrage nach dem Aufruf von mysql_query tatsächlich alle Ergebnisse im Speicher ab. Die While-Schleife iteriert tatsächlich die Daten im Speicher. Wenn Sie hingegen mysql_unbuffered_query anstelle von mysql_query verwenden, werden die Ergebnisse nicht zwischengespeichert.

<?php
$link = mysql_connect('localhost', 'Benutzer', 'Passwort');
$result = mysql_unbuffered_query('AUSWÄHLEN * AUS riesige_Tabelle', $link);
während ($row = mysql_fetch_array($result)) {
  //Datenergebnisse verarbeiten}

?>

Verschiedene Programmiersprachen handhaben das Überschreiben des Caches unterschiedlich. Beispielsweise muss der Perl-Treiber DBD::mysql die Clientbibliothek in der Sprache C (der Standardwert ist mysql_buffer_result) über das Attribut mysql_use_result angeben, wie unten gezeigt:

#!/usr/bin/perl

DBI verwenden;
mein $dbn = DBI->connect('DBI:mysql:;host=localhost', 'Benutzer', 'Passwort');
mein $sth = $dbn->prepare('SELECT * FROM huge_table', {mysql_use_result => 1});
$sth->execute();
während (meine $row = $sth->fetchrow_array()) {
	#Datenergebnisse verarbeiten}

Beachten Sie, dass „Prepare“ angibt, das Ergebnis zu verwenden, anstatt es zwischenzuspeichern. Es kann auch beim Verbinden angegeben werden, was dazu führt, dass nicht jede Abfrage zwischengespeichert wird.

mein $dbn = DBI->connect('DBI:mysql:;mysql_use_result=1;host=localhost', 'Benutzer', 'Passwort');

Oben finden Sie eine detaillierte Interpretation der MySQL-Client- und -Serverprotokolle. Weitere Informationen zu MySQL-Client- und -Serverprotokollen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!