Let the DB do the job

Oft benötigt man für Statistikfunktionen oder auch innerhalb der ganz normalen Anwendung mathematische Ergebnisse, Stringbearbeitung oder Ähnliches. Viele Entwickler nutzen dazu aber nicht die Datenbank, sondern berechnen aufwendig in PHP die Ergebnisse. Das gilt es allerdings zu vermeiden, da die Geschwindigkeit der Datenbank nur selten übertroffen werden kann. Eine Datenbank sollte deshalb nicht nur zur Speicherung eingesetzt werden, sondern kann durchaus auch Logik übernehmen.

Das simpelste Beispiel sind sicherlich Aggregatsfunktionen (z.B. Summe, Durchschnitt, Maximum, Minimum, Anzahl). Hierbei nehmen wir eine Punkte-Tabelle, in der für jeden User mehrere Einträge stehen (bspw. bei einem Tippspiel ist dies vorstellbar – pro User und Spieltag steht ein Datensatz in der Tabelle, wie viele Punkte der User bekommen hat). Die denkbar übelste Art, die Summe zu berechnen funktioniert so:

$erg = 0;
$rs = mysql_query("SELECT punkte FROM tabelle WHERE user='username'");
while($result = mysql_fetch_assoc($einlesen)) {
  $erg += $result['punkte'];
}

Diese Abfrage ist aus zweierlei Hinsicht schlecht: Erstens werden recht viele Datensätze selektiert, zweitens müssen all diese Datensätze in der Schleife durchlaufen werden, um die Werte aufzuaddieren. Viel einfacher geht es da mittels

SELECT SUM(punkte) FROM tabelle WHERE USER='username'

Diese Query gibt genau eine Zahl zurück – und das ist genau die, die wir suchen: die Gesamtpunktzahl des Users. Genauso verhält es sich mit dem Durchschnitt. Dabei ist SUM() lediglich durch AVG() auszutauschen, dann erhält man die durchschnittliche Punktzahl des Users pro Spieltag.
Ein etwas häufiger anzutreffendes Beispiel des gleichen Typs ist sicherlich – beispielsweise in einem Online-Shop – die Ermittlung des Gesamtpreises der Artikel, die ein User in seinem Warenkorb hat.

Ähnlich schlecht ist es, die Anzahl der Datensätze per PHP auszulesen. Wir wollen beispielsweise wissen, wie viele Mitglieder das Hobby Fahrradfahren haben. Das könnten wir so ermitteln:

$rs = mysql_query("SELECT ID FROM users WHERE hobby='Fahrradfahren'");
$cnt = mysql_num_rows($rs);

Das ist aber nur suboptimal. Viel besser ist es, die Anzahl gleich von MySQL ermitteln zu lassen:

SELECT COUNT(*) FROM users WHERE hobby='Fahrradfahren'

Dadurch wird lediglich ein Datensatz zurückgeliefert und der enthält exakt das gleiche Ergebnis wie oben.

Wer Aggregatfunktionen zusätzlich zu irgendwelchen anderen Daten selektieren möchte, muss zusätzlich GROUP BY verwenden. Das Tolle an MySQL ist, dass nicht alle Spalten, die selektiert werden, auch gruppiert werden müssen (in Oracle oder PostgreSQL beispielsweise ist das so). Wenn wir also unseren besten Kunden suchen und zusätzlich seinen Gesamtumsatz ermitteln wollen, können wir abfragen:

SELECT ID, username, anschrift, SUM(rechnungsbetrag) summe FROM kaeufe GROUP BY username ORDER BY summe DESC

Aber MySQL kann noch viel mehr: unter anderem auch mit Zeitangaben umgehen.
Wenn wir beispielsweise wissen möchten, wie sich unser Umsatz über die Jahre entwickelt hat, können wir allein mithilfe der Tabelle mit bezahlten Rechnungen genau das herausfinden:

SELECT YEAR(bestelldatum) jahr,SUM(rechnungsbetrag) umsatz FROM tabelle GROUP BY YEAR(bestelldatum)

Wer sich über die mächtigen Datumsfunktionen genauer anshen möchte, dem empfehle ich den Beitrag im MySQL Reference Manual.

Und zu guter Letzt möchte ich noch auf GROUP BY an sich aufmerksam machen. Besonders bei JOINS kann es passieren, dass zu einem Eintrag der einen Tabelle mehrere der anderen Tabelle passen (1:n-Beziehung). Dann ist es hilfreich, wenn man GROUP BY einsetzt. Ein häufig gesehenes Beispiel ist ein Vorschaubild eines Artikels, der aber noch mehr Bilder hat.

SELECT artikel.name,bilder.bildurl FROM artikel LEFT JOIN bilder ON artikel.ID=bilder.artikel_ID GROUP BY artikel.name

Das garantiert, dass der gleiche Artikel auch nur ein mal zurück gegeben wird, denn wir benötigen ja nur ein Bild des Artikels.

Jan hat 152 Beiträge geschrieben

5 Kommentare zu “Let the DB do the job

  1. TB sagt:

    Zwar wird die Gesamtperformance durch solche Maßnahmen erhöht, doch wenn es darum geht, den DB-Server zu entlasten, da idR. viele Applicationserver und nur 1 DB-Server zur Verfügung stehen, nehme ich lieber eine schlechtere Gesamtperformance in Kauf, wenn dadurch der DB-Server entlastet wird.

  2. GhostGambler sagt:

    Replikation, zweiter DB-Server, „aufwändige“ SELECTs auf den Slave
    Die CPU wird zwar durch Konstrukte wie der erste PHP-Code entlastet, dafür sind die Verbindungen länger offen, was mMn in einer schlechteren Gesamtperformance der Anwendung resultiert. D.h. besser einen Web-Server als DB-Server-Slave aufsetzen, sodass die Webserver generell schneller arbeiten können, als die Performance der Anwendung durch sinnloses schaufeln von Daten zu verringern.

  3. kuk sagt:

    [quote]die Geschwindigkeit der Datenbank nur selten übertroffen werden kann[/quote]

    Die Geschwindigkeit der DB ist oft sehr schlecht, weil sie oft überlastet ist. Und die arithmetischen Operationen erledigen alle Rechner sowieso in einem Takt. Die wirkliche Optimierung steckt in der Reduzierung der zu übertragenden Datenmengen. Denn die Verbindung ist immer der Flaschenhals. Je mehr Daten zwischen Webserver und DB unnötig übertragen werden, desto schlimmer ist es. Also die hier aufgeführten SQL Berechnungen haben nur Sinn bei den Daten aus der DB. Nicht dass jemand auf die Gedanken kommt normale Additionen oder andere Operationen auf die DB zu übertragen

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>