Einmal lang statt mehrmals kurz

Ach da ist mir wieder ein grandioser Titel eingefallen, unter dem sich erstmal niemand vorstellen kann, worum es in diesem Beitrag eigentlich gehen soll. Also: Ich sehe es recht oft in Scripten (auch meinen eigenen), dass bei einem Seitenaufruf mehrmals sehr ähnliche Abfragen ausgeführt werden, die unterschiedliche Zwecke haben – aber eben doch sehr ähnlich sind. Jedoch ist aufgrund dieser Ähnlichkeit ein oftmals ähnlich großer Aufwand zur Ermittlung des Ergebnisses notwendig. Besser wäre es deshalb, wenn man die Querys etwas klüger aufbaut und so mit weniger Abfragen je Scriptaufruf auskommt.

Zuerst möchte ich an einem Beispiel beschreiben, worum es genau geht.
Es wird vom Besucher eines Online-Shops die Kategorien-Übersichtsseite kategorie.php?kat_id=1 aufgerufen. Der Sinn dieses Scripts ist es, alle Produkte aus dieser Kategorie anzuzeigen. Hier eine typische Abfolge im Script:

  1. Sind in der Kategorie 1 überhaupt Produkte enthalten? Wenn nein, dann vielleicht irgendwohin umleiten
  2. Die Namen der Produkte dieser Kategorie auslesen, um Sie schon in den Meta-Keywords- und -Description-Tags unterzubringen
  3. Produktnamen, -texte, Preise und Fotos auslesen, um den Body damit zu füllen

Oft ist diese Liste noch viel länger. Jedenfalls unterscheiden sich die für die hier dargestellten Schritte nötigen Querys meist nur durch die zu selektierenden Spalten, seltener vielleicht noch um einen Join. Die WHERE-Klausel ist aber meist gleich.

Man könnte meinen, dass MySQL da ja den Cache nutzen könnte, wenn die Abfragen sich so ähnlich sind. Das funktioniert aber nicht, da der Query-Cache wirklich nur auf identische Querys reagiert (also wenn ich die exakt gleiche Query mehrmals hintereinander ausführe).
Das bedeutet also in obigem Beispiel müsste MySQL 3 mal den gleichen Aufwand ausführen (und entsprechend kann die Ladezeit verzögert werden).

Bei solch ähnlichen Abfragen (insbesondere, wenn mehrere Abfragen im Script sich nur in den Spalten unterscheiden) sollte deshalb lieber am Anfang gleich alles selektiert werden, was im Folgenden noch benötigt wird.
Am Beispiel: Ich selektiere gleich am Anfang Schritt 3, denn die nötigen Daten für die Schritte 1 und 2 sind Untermengen von 3:
zu 1.: wenn ich Artikel aus der 3. Abfrage bekomme, enthält die Kategorie auf jeden Fall Artikel
zu 2.: wenn ich die Produktnamen, -texte usw. selektiere, kann daraus ja prima auch nur der Name genutzt werden. Die restlichen Daten werden in dieser while-Schleife eben erstmal liegen gelassen. Selbst wenn ich 20 Produkte selektiert habe, in die Meta-Tags aber nur 5 schreiben möchte, kann man das einfach mit einer Zählvariable in PHP lösen.

Wenn ihr das jetzt beherzigt, werdet ihr aber feststellen, dass es nicht funktioniert. Sobald ihr das erste Mal das Result Set durchlaufen habt, kommen keine Ergebnisse mehr (es sei denn ihr setzt mysql_result() ein). Das liegt daran, dass das Result Set einen internen Zeiger hat, der auf den aktuellen Datensatz zeigt. Mit diesem Zeiger arbeiten Funktionen wie mysql_fetch_array oder mysql_fetch_assoc. Wenn er auf dem letzten Datensatz steht und ihr die Schleife erneut ausführen wollt, geht PHP natürlich gar nicht erst in die Schleife rein.
Deshalb müssen wir den Zeiger zurücksetzen. Ähnlich wie bei einer Videokassette, wenn wir die gleiche Szene nochmal sehen möchten, müssen wir eben zurückspulen.

Dieses Zurückspulen funktioniert bei MySQL mit der PHP-Funktion mysql_data_seek. Über den zweiten Parameter kann man angeben, zu welcher Position wir „spulen“ wollen. In den meisten Fällen sollte hier eine 0 stehen, um wieder ganz an den Anfang des Result Sets zu gelangen.

In Code sieht das ganze also so aus:

$rs = mysql_query("SELECT name,text,preis,bild FROM produkte INNER JOIN fotos ON produkte.ID=fotos.produkte_ID WHERE kategorien_ID='".$_GET['kat_id']."' LIMIT 0,20");
 
// Schritt 1 aus dem Beispiel
if(mysql_num_rows($rs)==0) header("Location: index.php");
 
// Schritt 2: 5 Produktnamen in den Meta-Tags anzeigen
$i = 0;
while(++$i<=5 && $data = mysql_fetch_assoc($rs)) {
  echo $data['name'].", ";
}
 
// internen Zeiger zurücksetzen
mysql_data_seek($rs,0);
 
// Schritt 3
while($data = mysql_fetch_assoc($rs)) {
  echo "<img src='".$data['bild']."' />".$data['name']."<br />".$data['text'];
}

Man sucht also nach ähnlichen Abfragen und nimmt dann die mengenmäßig umfangreichste, sodass daraus auch die anderen Aufgaben erledigt werden können. Es sollte aber nur so vorgegangen werden, wenn die Abfragen sowieso ausgeführt werden müssen. Es lohnt sich hingegen nicht, einen aufwändigen Join oder ein GROUP BY auszuführen, wenn dieser in den meisten Fällen von Seitenaufrufen gar nicht benötigt wird.

Das Ergebnis ist die Verringerung der Anzahl der Anfragen (im Beispiel 1 statt 3) und somit auch eine Verringerung der Last und der Antwortzeit.
Also schaut mal eure Scripte durch, vielleicht könnt ihr die Anzahl der Abfragen durch mysql_data_seek und das geschickte Abfragen verringern.

Jan hat 152 Beiträge geschrieben

6 Kommentare zu “Einmal lang statt mehrmals kurz

  1. Ist der MySQL-Server _nicht_ auf dem gleichen System wie der Webserver, so kostet jede SQL-Query deutlich mehr Zeit, da hier alle Anfragen und Antworten über das Netzwerk geschickt werden.
    Je nach Netzwerk kann eine Query somit einige Millisekunden benötigen.

  2. Michael sagt:

    In den meisten Umgebungen, wo es auf Performance ankommt, oder wenn die Datenbankgröße entsprechende Größen annimmt, muss das so sein.
    Ob das jetzt per TCP läuft oder über den lokalen UNIX Socket, der Zeitunterschied ist trivial.

    Wir betreiben ein soziales Netzwerk mit knapp 1Mrd Klicks im Monat, wir betreiben 20 Webserver und 12 Datenbankenserver. Hier muss alles zwangsläufig übers Netzwerk laufen, performanceeinbußen gibts es hierdurch allerdings nicht.

  3. GhostGambler sagt:

    Funktioniert die Replikation denn einwandfrei?
    Für gewöhnlich kommt es – gerade bei großen Datenmengen – dabei theoretisch/oft zu inkonsistenten Zuständen zwischen den Servern was natürlich für die Benutzer verwirrend ist, wenn z.B. beim ersten Aufruf ein Posting da ist, beim zweiten Aufruf noch nicht. Oder wenn ein Benutzer immer auf dem gleichen Server landet, der eine Benutzer schon mehr sieht als z.B. ein anderer Benutzer.

  4. Michael sagt:

    also wir haben soweit keine probleme.
    innoDB macht das recht gut (auch wenn mal einer den Geist aufgibt oder neugestartet werden muss).

    Es tauchen selten mal unstimmigkeiten auf, aber meistens sind wir selbst schuld daran.

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>