Sie sind hier : sebastian1012.bplaced.net/ homepage-neu / kreuz-und-quer / tutorials-info-neuigkeiten-datenbank / aufwaendiges-grouping-vermeiden.php

Aufwändiges GROUPing vermeiden

Ich habe ja bereits an einigen Stellen hier im Blog geschrieben, dass man das Potential des DBMS möglichst ausschöpfen sollte und demzufolge alles dort schon berechnen, filtern und verarbeiten lässt, was das DBMS kann. In den meisten Fällen bleibe ich auch bei dieser Aussage, aber einige Fallstricke gibt es dabei. Einen möchte ich in diesem Beitrag beschreiben.

Ein Grundsatz, den ich immer wieder gern predige, ist: „SQL-Abfragen, die in Schleifen ausgeführt werden, sollten überprüft werden, ob Sie nicht bereits außerhalb der Schleife mit abgedeckt werden können.“ Oft sind die benannten Schleifen ja die while-Schleifen, die aus dem Durchlaufen der Datensätze eines Result-Sets kommen. Wenn innerhalb dieser Schleife dann in jedem Durchlauf eine Extra-Abfrage ausgeführt wird, ist das eher suboptimal. Meistens kann in die äußere Abfrage die innere (in der Schleife) mit eingebaut werden, da ja ein funktionaler Zusammenhang besteht – meistens über Fremdschlüssel, denn sonst könnte ja die innere Abfrage gar nicht für den aktuell durchlaufenen Datensatz der äußeren Abfrage ausgeführt werden. Oh Mann, ich hoffe man kann mir folgen…
Ich versuchs mal noch mit einem Beispiel zu verdeutlichen:

$rs_aussen = mysql_query("SELECT a,b FROM tabelle WHERE c=123"); while($data = mysql_fetch_assoc($rs_aussen)) {   echo $data['a']." ".$data['b'];   $rs_innen = mysql_query("SELECT x FROM tabelle2 WHERE y='".$data['a']."'");   echo mysql_result($rs_innen, 0); }

Man sieht, also, dass tabelle2.y der Fremdschlüssel von tabelle.a ist. Demzufolge könnte die innere Abfrage auch in die äußere eingebaut werden mit einem einfachen INNER JOIN:

$rs_aussen = mysql_query("SELECT a,b,x FROM tabelle INNER JOIN tabelle2 ON tabelle.a=tabelle2.y WHERE c=123"); while($data = mysql_fetch_assoc($rs_aussen)) {   echo $data['a']." ".$data['b'];   echo $data['x']; }

Bei solch einer 1:1-Relation ist das natürlich simpel. Problematischer wird es bei 1:n-Relationen – und noch schlimmer bei 1:(0..n). Denn dann entstehen durch das INNER JOIN natürlich mehrere (Pseudo-)Datensätze, die wir eigentlich gar nicht haben wollen. Dies ist oft der Fall, wenn man eine Tabelle „produkte“ sowie eine „bilder“ hat. Ein Produkt kann natürlich mehrere Bilder haben (hat aber eventuell auch gar keines). Für eine Übersichtsseite wollen wir nun aber jedes Produkt anzeigen, jedes soll aber nur maximal 1 Bild haben.
Es führt demzufolge kein Weg am GROUP BY vorbei, damit wir wirklich nur maximal 1 Bild zu jedem Produkt bekommen. Außerdem müssen wir einen LEFT JOIN einsetzen, weil sonst alle Artikel ohne Bild herausfallen würden. Unsere Abfrage sähe also so aus:

SELECT produkte.name,bilder.url  FROM produkte  LEFT JOIN bilder ON produkte.ID=bilder.produkte_ID  GROUP BY produkte.ID LIMIT 0,20

Diese Abfrage geht so natürlich nur in MySQL. Andere DBMS werfen an der Stelle einen Fehler, weil nicht alle Spalten, nach denen gruppiert werden soll in der Selektionsliste enthalten sind. Das kann man MySQL auch abgewöhnen, aber ich finds toll – erspart jede Menge Subquerys.

Und wenn man sich nun die EXPLAINs ansieht, wird es einem mitunter ganz anders. Meistens werden für das GROUP BY nämlich temporäre Tabellen angelegt und mitunter auch noch ein Filesort hinterhergeschoben (vor allem, wenn man nicht nur gruppiert sondern danach noch nach einer anderen Spalte sortiert – z.B. dem Preis). Das ist nicht unbedingt das, was man gute Performance nennt. Nun wissen wir ja, woher der Fehler kommt: Vom GROUP BY. Also haben wir doch schnell mal ins Manual geschaut. Nun wird das Problem deutlich: die Indizes können nicht fürs Gruppieren benutzt werden, weil wir eben wie bereits erwähnt, andere Spalten selektieren als nach denen wir gruppieren. Alle bei MySQL.com beschriebenen Optimierungsstrategien beziehen sich auf Anfragen, bei denen nach den Spalten gruppiert wird, die auch selektiert werden (zumindest grob gesagt – lest euch die Artikel dort aber auf jeden Fall mal durch).

Bei uns ist dies aber nicht der Fall.

Problematisch anzusehen ist dabei auch die Ausführungsreihenfolge aller DBMS: Zuerst werden die Tabellen gejoint, anschließend gruppiert und dann wird erst selektiert. Es ist allerdings anders auch nicht möglich. Das bedeutet, dass bei uns zuerst alle Bilder-Datensätze gruppiert werden (sehr aufwändig!), denn sonst könnte das LIMIT nicht garantieren, dass genau 20 herauskommen. Wenn erst selektiert und dann gruppiert würde, könnte es ja sein, dass ein Artikel mit mehreren Bildern unter den 20 ausgewählten ist. Würde danach gruppiert, blieben am Ende weniger als 20 Datensätze übrig.

Die Lösung, die ich für mich nun gefunden habe, weil ich MySQL partout nicht überreden konnte einen Index für eine ähnliche, etwas komplexere Anfrage als die obige zu verwenden, ist, dass ich die Bilder wieder in der Schleife hole. Das spart den LEFT JOIN sowie das GROUPing. Und letztlich müssen dann eben nur die Bilder für die ausgewählten 20 Artikel geholt werden. Außerdem sind die Abfragen natürlich sehr schnell, weil der Fremdschlüssel indiziert ist und ich ja noch ein LIMIT 1 anhängen kann.

Natürlich kann man auch eine Subquery benutzen, die zuerst 20 Produkte holt und zu diesen dann 0-1 Bilder holt. Auch diese Lösung funktioniert – bei mir allerdings etwas langsamer als die Bilder in der Schleife zu holen. Ich meine also so:

SELECT t.name,bilder.url FROM (   SELECT produkte.name,produkte.ID    FROM produkte    ORDER BY preis /* ORDER ist auch problemlos möglich */   LIMIT 0,20) t LEFT JOIN bilder ON t.ID=bilder.produkte_ID  GROUP BY t.ID  ORDER BY NULL /* wichtig, wenn die Reihenfolge der Subquery übernommen werden soll */

Natürlich umgehen wir so das Problem nicht, dass trotzdem der Index für das GROUP BY nicht benutzt werden kann, aber zumindest müssen nicht mehr so viele Datensätze gruppiert werden.

Man muss es eben mal durchmessen, welche Variante schneller ist. Erste Priorität sollte es immer haben, die Anfrage und die Indizes so anzupassen, dass es mit einer äußeren Abfrage unter der nutzung von Indizes schön flott geht. Gelingt das aber nicht, kann man den Weg mehrerer Abfragen zumindest mal ausprobieren. Ihr solltet dazu ein Profiling-Tool verwenden, um die Geschwindigkeiten zu messen.