Sie sind hier : sebastian1012.bplaced.net/ homepage-neu / kreuz-und-quer / tutorials-info-neuigkeiten-php / gepufferter-und-ungepufferter-zugriff-auf-mysql-tabellen.php

Gepufferter und ungepufferter Zugriff auf MySQL-Tabellen

Wer schon mal mit höheren Programmiersprachen zu tun hatte, kennt gepufferte und ungepufferte Datenströme. Auch MySQL bietet uns die Möglichkeit das Result-Set einer SELECT-Anfrage gepuffert und ungepuffert zurückzugeben. PHP bietet aus diesem Grund auch zwei verschiedene Funktionen dafür an – je nach gewünschter Pufferung: mysql_query() (gepufferter Datenstrom) und mysql_unbuffered_query (ungepuffert, sagt ja der Name schon 😉 ). Was sind nun die Vor- und Nachteile der beiden unterschiedlichen Arten des Zugriffs? Dieser Beitrag soll es erklären.

Zuerst möchte ich kurz erklären, was Pufferung eigentlich bedeutet. Inputs und Outputs sind in modernen Programmiersprachen nicht über Strings oder primitive Datentypen zugänglich sondern über Streams (Datenströme). Um aus einem Strom nun eine bestimmte Einheit herauszulösen, muss man auf ein bestimmtes Stopp-Zeichen warten (meist \n, also newline bzw. LF=line feed). Durch Zusammenbauen der Zeichen zwischen den Stoppzeichen entsteht eine Gruppe, die man nun auch wieder als String behandeln kann. Das bedeutet, dass während des Wartens auf das Stopp-Zeichen nichts weiter passiert als dass gewartet wird. Außerdem müssen die vorher eingelesenen Zeichen natürlich zwischengespeichert werden, damit anschließend der String zusammengebaut werden kann.
So viel kurz zur Theorie im Hintergrund, wir wollen uns ja eigentlich hier mit Scriptsprachen (insbesondere PHP) beschäftigen, wo man sich darum eigentlich nicht kümmern muss.

Warum habe ich nun so ausgeholt? Ganz einfach: MySQL kann das auch 😉
mysql_query() weist MySQL an, dass das Result-Set zuerst seitens MySQL gepuffert werden soll. Das bedeutet, dass das Result-Set komplett am Stück an PHP übergeben wird, um es anschließend dort weiter verarbeiten zu können.
Viel schlauer wäre es doch aber, wenn man nicht warten müsste bis das gesamte Result-Set zur Verfügung steht, sondern direkt mit der Verarbeitung beginnen könnte, sobald der erste Datensatz feststeht. Und genau da kommt mysql_unbuffered_query ins Spiel. Das bedeutet insbesondere eine Speicherersparnis beim PHP Interpreter, da dieser stets nur 1 Datensatz im Speicher hat (was aber auch Nachteile birgt –> siehe unten).

Hier ist das beim Test zum Einsatz gekommene Script:

$rs = mysql_query("SELECT * FROM hotels WHERE name IS NOT NULL"); //bzw. mit mysql_unbuffered_query() while($data = mysql_fetch_assoc($rs)) {   echo $data['name']; }

Diese Variante liefert einfach alle Hotel-Datensätze, deren Name nicht NULL ist. Die Query liefert ungefähr 19000 Datensätze zurück, sie dauert laut PHPMyAdmin ca. 0,0011 s.

Das Ergebnis sieht dabei so aus:

Datei Gesamtlaufzeit durchschnittliche Laufzeit pro Durchlauf Verhältnis zur schnellsten Variante
result_mysql_ unbuffered_query.php 86.864906 s 868.649 ms 100 %
result_mysql_ buffered_query.php 88.397108 s 883.971 ms 102 % (+ 2%)

Die ungepufferte Variante ist demzufolge etwas schneller.

Trotzdem verwundert mich der geringe Unterschied – hätte mehr erwartet. Habe noch andere Querys durchgetestet, aber der Unterschied war nirgends größer als hier. Teilweise war sogar die gepufferte Variante schneller. Und das obwohl selbst die PHP-Doku von einer Beschleunigung spricht:

On the one hand, this saves a considerable amount of memory with SQL queries that produce large result sets. On the other hand, you can start working on the result set immediately after the first row has been retrieved: you don’t have to wait until the complete SQL query has been performed.

Zumal die ungepufferte Variante noch einige Nachteile mit sich bringt: So können beispielweise die Funktionen mysql_num_rows(), mysql-result() und mysql_data_seek() nicht genutzt werden. Außerdem sind Konstrukte wie folgendes nicht möglich (nur mit separater DB-Verbindung):

$rs = mysql_unbuffered_query("SELECT * FROM table"); while($data = mysql_fetch_assoc($rs)) {   $rs1 = mysql_query("SELECT * FROM table2 WHERE fk_ID='".$data['ID']."'"); }

Bedeutet also das komplette Result-Set muss erst abgeholt werden, bevor die nächste Anfrage abgeschickt werden kann. Und das bedeutet wiederum, dass man beim Verarbeiten der Daten möglichst schnell sein sollte, weil in der Zwischenzeit die gesamte Tabelle gelockt bleibt. David beschreibt das in den User Comments von php.net so:

Also note that if you are using this function, you should be quick about processing the result set, or you will tie up the MySQL server (other threads will be unable to write to the tables you are reading from).

Habt ihr noch Ideen, was ich übersehen haben könnte? Oder sind meine 19000 Datensätze einfach noch zu wenig? Ich finde die Laufzeiten nämlich jetzt schon recht hoch. Außerdem habe ich extra eine schnelle Query, die recht viele Daten zurückliefert genutzt, damit die Pufferung erst richtig ins Gewicht schlagen kann.
Oder wirkt sich das wirklich alleinig auf den Speicherverbrauch aus, wie es ein Buch bei Google Books suggeriert.

Die Kommentare bei diesem Beitrag haben neue Denkansätze geliefer, die ich umgehend in einem Nachtest untergebracht habe. Dort werden teilweise Aussagen aus diesem Beitrag relativiert, also bitte auch lesen!