MySQL: Optimieren Sie Ihre Abfragen

SQL-Server wie MySQL sind keine plumpen Datenspeicher, sondern Spezialisten mit hoch entwickelten Fähigkeiten. Diese Stärken sollte man beim Einsatz mit Skriptsprachen wie PHP voll nutzen, um schnelle und elegante Anwendungen zu entwickeln.

Ein Beispiel für die Wichtigkeit der richtigen Aufgabenverteilung zwischen Skriptsprache und Datenbankserver: Aus dem Inhalt einer Tabelle soll ein zufälliger Datensatz ausgewählt werden. Eine Methode wäre die Übernahme aller vorhandenen Datensätze in ein PHP-Array, aus dem dann in PHP zufällig ein Wert ermittelt wird. Diese Vorgehensweise ist aber ineffizient. Denn dazu muss der Datenbank-Server alle Daten von der Festplatte lesen und sie

dem Webserver übergeben. Der muss sie in das Array übernehmen.

Verwenden Sie dagegen folgenden MySQL-Befehl, um die übertragene Datenmenge gering zu halten:

SELECT * FROM TABELLE ORDER BY RAND() LIMIT 1

Hier wird über die Funktion RAND die zufällige Auswahl bereits in der Datenbank getroffen. Darum muss lediglich der ermittelte Datensatz an PHP übergeben werden.

Das Prinzip, aus Effizienzgründen möglichst wenig Informationen zwischen

der Datenbank und PHP zu übertragen, lässt sich auch auf andere Anwendungsfälle übertragen. Ein Beispiel dazu:

Es soll ermittelt werden, wie viele Datensätze eine bestimmte Bedingung erfüllen.

Dafür scheint die PHP-Funktion mysql_num_rows() eine gute Wahl zu sein. Sie müssen lediglich einen SELECT-Befehl mit passender WHERE-Bedingung

absenden und bekommen durch den Aufruf von mysql_num_rows() daraufhin die

Anzahl der ermittelten Datensätze geliefert.

Nur: Bei dieser Methode werden alle ausgewählten Datensätze zu PHP

geschaufelt, obwohl nur ihre Anzahl interessiert. Besser ist es, die Zählung MySQL zu überlassen. Sie verwenden also einen SQL-Befehl wie

SELECT COUNT(*) FROM rechnungen WHERE bezahlt='N'

und bekommen die Anzahl aller unbezahlten Rechnungen geliefert. Nur dieser Zahlenwert wird vom MySQL-Server zu PHP übertragen, was zu kürzeren Antwortzeiten und schonendem Umgang mit Hauptspeicher verhilft. Denn mit der ersten Methode müsste PHP einen großer Puffer für die Speicherung der Ergebnisse der Abfrage einrichten. Zusammenfassungen holen

Umgang mit großen Tabellen

Gerade, wenn in Tabellen viele Datensätze stecken, sind Zusammenfassungen unumgänglich. Sind etwa in einer Tabelle einzelne Umsätze eines Online-Shops verzeichnet, ist vielleicht die tägliche Aufsummierung der Beträge interessant. Auch hier ist es sinnvoll, die Ergebnisse schon vom Datenbank-Server aufbereiten zu lassen, statt sie Satz für Satz in PHP zu addieren. Dazu gibt es den SQL-Schlüsselausdruck GROUP BY. Der fasst alle Datensätze zusammen, die einen identischen Wert im genannten Feld haben.

Bei der Verwendung der Gruppierung dürfen nach dem SELECT nur bestimmte Ausdrücke stehen. So hat im obigen Fall beispielsweise eine Kundennummer nichts verloren. Sie ist in der Regel in den einzelnen Umsätzen eines Tages unterschiedlich. Für die Kundennummern dieser Umsätze gibt keine sinnvolle Zusammenfassung und der SQL-Server findet keine praktikable Methode, diese Nummern zusammenzufassen. Darum akzeptiert MySQL so ein Feld auch nicht und verweigert die Abfrage wie die folgende mit einer Fehlermeldung:

SELECT kundennr FROM umsatz group by datum

Der einzige hier direkt verwendbare Feldname ist der, nach dem zusammengefasst wird, also der Spaltenname nach dem GROUP BY. Ansonsten sind nur Aggregatfunktionen erlaubt. Das sind Funktionen, die die Zusammenfassung unterstützen, etwa für das Zählen von Datensätzen, Bilden von Summen oder Errechnen von Durchschnittswerten.

Gruppierung nach Zeiteinheiten

Angenommen, Sie hätten eine Tabelle, in der neben jedem Umsatz auch da Datum und die Uhrzeit vermerkt sind. Dann würde folgende Abfrage für je den Tag das Datum, die Anzahl der Umsätze sowie deren Summe liefern:

SELECT datum,COUNT(*),SUM(betrag) FROM umsatz GROUP BY datum

Die Funktion COUNT(*) wirkt hier innerhalb jeder Gruppe separat, zählt also die Anzahl der Umsätze je Tag. SUM(betrag) summiert die einzelnen Beträge auf.

Wünschen Sie eine andere Gruppierung als auf Tagesbasis, etwa nach Wochen oder Monaten, muss eine Funktion eingesetzt werden, um den gewünschten Gruppierungbereich anzugeben. Das trifft insbesondere dann zu wenn Zeit und Datum gemeinsam in einem Feld vom Typ datetime oder timestamp untergebracht sind. Denn in diesem Fall würde obige SQL-Zeile nur Umsätze gruppieren, die in exakt denselben Sekunden getätigt wurden.

Einige Datumsfunktionen von MySQL würden sich bei GROUP BY anbieten, um einen bestimmten Zeitraum zusammenzufassen. Am praktikabelsten ist jedoch DATE_FORMAT(datum,formatstring), denn hierbei können Sie mit einer Funktion eine Vielzahl von Gruppierungsvarianten realisieren und müssen lediglich den Formatstring anpassen. Um beispielsweise alle Wochenumsätze zusammenzufassen, verwenden Sie folgende Gruppierung

...GROUP BY DATE_FORMAT(datum,'%Y-%V');

Das “%V” steht dabei für die Kalenderwoche. Alleine damit würden allerdings

beispielsweise die KW 1 von 2005 und 2006 zusammengefasst werden. Darum muss noch das Jahr hinzukommen, wofür der Formatparameter %Y steht. Damit ermittelt MySQL im Beispiel einmal 2005-01 und einmal 2006-01 und sortiert die zugehörigen Umsätze separat. Die vollständige Liste der möglichen Parameter bietet das MySQL-Manual.

Wenn Sie in jeder Zeile der Wochenumsätze auch die KW ausgeben möchten, müssen Sie die Konstruktion DATE_FORMAT(…) auch in die Feldliste nach dem SELECT aufnehmen. Um den SQL-Befehl dabei übersichtlich zu halten, geben Sie der berechneten Kalenderwoche einmalig den Alias “kw” und beziehen sich dann bei der erneuten Verwendung mit GROUP BY nur noch auf diesen Alias:

SELECT DATE_FORMAT(datum,'%Y-%V') AS kw, SUM(betrag) FROM umsatz GROUP BY kw

Sie sehen: Mit der Datenbank-Power von MySQL sparen Sie eine Menge Rechenzeit – Sie müssen nur die richtigen Befehle kennen und konsequent einsetzen.

Weitere Aggregatfunktionen

Zu den Funktionen, die in SQL-Befehlen mit der GROUP BY-Klausel verwendet werden können, gehören auch MAX() und MIN(). Die liefern den maximalen oder minimalen Wert innerhalb einer Gruppe. Die Funktionen kann man neben ihrer eigentlichen Bestimmung auch als Workaround für einen Sonderfall einsetzen. Existiert in der Tabelle ein Feld, dessen Wert wie das GROUP BY-Feld innerhalb jeder Gruppe gleich ist, soll das vielleicht auch in der Ausgabe erscheinen. Das würde MySQL aber nicht erlauben, weil es nur Felder zulässt, die im GROUP BY genannt sind. In diesem Fall packen Sie das Feld einfach in MAX oder MIN. So akzeptiert der Server dieses Feld bei der Verwendung in einer gruppierten Abfrage.

Ein Beispiel wäre ein Umsatzbericht mit quartalsweiser Gruppierung. Dann möchten Sie vielleicht der besseren Übersicht wegen die Jahreszahl in einem eigenen Feld ausgeben und nehmen sie mit MAX(YEAR(datum)) in die Liste der ausgegebenen Felder auf.

Die Funktion AVG liefert den Durchschnittswert (englisch “Average”) eines Feldes innerhalb der Gruppe. Für statistische Zwecke existieren auch noch weitere Funktionen, etwa VARIANCE und STD, um Varianz und Standardabweichung von Werten zu ermitteln. Mehr Informationen zu den Aggregat-Funktionen finden Sie auf der MySQL-Homepage unter <ahref=”http://dev.mysql.com/doc/ refman/4.1/en/group-byfunctions.html”>http://dev.mysql.com/doc/ refman/4.1/en/group-byfunctions.html

Neue Gruppierungsoption ab MySQL 4.1

Ab MySQL 4.1 gibt es mit GROUP_CONCAT() eine Funktion, die aus den einzelnen Werten des verwendeten Ausdrucks eine Komma-separierte Liste generiert. Wozu das nützlich sein kann? Angenommen, Sie hätten eine Umsatztabelle, in der die Kunden mit ihrem Namen erscheinen. Sie möchten eine nach Monaten gruppierte Übersicht erzeugen, die Anzahl und Summe der Beträge auflistet. Interessant wäre auch, welcher Kunde überhaupt in diesem Monat eingekauft hat. Für diese Fragestellung erzeugt der Ausdruck group_concat(kundenname) die gewünschte Liste. Um doppelte Nennungen zu vermeiden, ist noch die Zusatzangabe DISTINCT vorgesehen. Mit GROUP_CONCAT(DISTINCT kundenname) wird dann jeder Kunde nur einmal aufgeführt.

Ähnliche Beiträge