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.

Jan hat 152 Beiträge geschrieben

12 Kommentare zu “Aufwändiges GROUPing vermeiden

  1. GhostGambler sagt:

    Trick 17 hilft in dem Fall weiter:
    Index auf produkte.id und produkte.name, dann gibt es kein temptable und kein filesort

    Wenn man ganz viele zusätzliche Spalten auswählen möchte macht das ggf. keinen Sinn.
    Dann bietet sich etwas wie folgt an:
    SELECT pro2.name, pro2.something, bilder.url
    FROM produkte
    LEFT JOIN bilder ON produkte.ID=bilder.produkte_ID
    INNER JOIN produkte pro2 ON produkte.id = pro2.id
    GROUP BY produkte.ID
    LIMIT 0,20
    Wieder kein temptable und kein filesort.

    Natürlich wie immer kein Allheilmittel und immer situationsabhängig. Aber das ist MySQL-Optimierung ja eh immer…

  2. Jan sagt:

    Hochinteressant. Nur damit ich weiß, was bei der Abfrage mit vielen Selekions-Spalten das Trickreiche ist: Ist es der Self Join mit Produkte?
    Dann kann für jede Produkte-Tabelle ein anderer Index genutzt werden. Ist das das Geheimnis dahinter? Also das probiere ich morgen direk mal aus. Danke Dir schon mal!

    Und ich bin gespannt auf Deine 16 anderen Tricks, wenn dies der 17. ist. 😉

  3. GhostGambler sagt:

    Wie „für jede Produkte-Tabelle“?

    Bei vielen selektierten Spalten macht es natürlich keinen Sinn einen riesigen Index auf alle Spalten der Tabelle zu legen. Da macht die Variante mit dem doppelten Join mehr Sinn.

  4. Jan sagt:

    „Für jede Produkt-Tabelle ein eigener Index“, damit meinte ich: Da Du durch den Self Join ja 2 mal die Tabelle Produkte in Deiner Query hast (produkte, pro2). Und dadurch können ja 2 verschiedene Indizes benutzt werden.
    Oder was ist das Geheimnis, weshalb dann keine temporäre Tabelle benötigt wird?

    Und zu den falschen Anführungszeichen: Das macht WordPress leider so (die Blogsoftware hier). Ich denke es versteht jeder und es ist mir zu viel Aufwand WordPress das abzugewöhnen. Wenn jemand ein Plugin kennt, dass diese krummen Anführungszeichen in normale Hochkomas umwandelt, würde ich mich über eine Nachricht freuen.

  5. Alex sagt:

    Danke für den Beitrag!
    Ich bin auch der Verfechter der Methode, möglichst alle Daten außerhalb der Schleife zusammenstellen. Hierbei habe ich aber festgestellt, dass bei vielen LEFT JOINS (ich rede von 4-10) die „dazugejointen“ Daten nicht mehr eindeutig sind. Also der 5te Left Join übernimmt schon mal den Wert vom vorhergehenden, insbesondere dann, wenn es ein COUNT() ist. Daher muss ich wieder in die Schleife ausweichen. Kommen die LEFT JOINS sich also gegenseitig in die Quere?

    Eine weitere Frage wirft der Beitrag bei mir auf, Zitat: „Problematisch anzusehen ist dabei auch die Ausführungsreihenfolge aller DBMS: Zuerst werden die Tabellen gejoint, anschließend gruppiert und dann wird erst selektiert.“
    Heißt das etwa, dass man durch selektieren von benötigten Feldern (statt SELECT *) keine Performance gewinnt, da sowieso alle Felder „geholt“ werden? Das würde mich jetzt doch interessieren.

  6. Jan sagt:

    Die Frage mit den LEFT JOINs verstehe ich nicht ganz. Wenn Du mehrere LEFT JOINs nutzt und dann ein COUNT darauf ausführst, wirst Du eventuell Tupel erhalten, die gar nicht wirklich existieren (im Gegensatz zu INNER JOIN). Daher kann das COUNT(*) Werte ausspucken, die scheinbar falsch sind.

    Zur zweiten Frage: Auf keinen Fall SELECT * verwenden! Ich meinte damit lediglich, dass die Datensätze gejoint werden. Zu diesem Zeitpunkt werden aber nur die IDs genutzt (PK). Die Nutzdaten werden erst später ausgewählt. Und außerdem würden bei SELECT * unnötigerweise mehr Daten an PHP geschickt, was einen höheren Speicherbedarf zur Folge hat.

  7. Alex sagt:

    Ich habe mal einen Benutzer aus der DB abgefragt und über LEFT JONS die Anzahl seiner Bilder, Posts, Kommentare, Bewertungen etc über jeweils einen LEFT JOIN mit einem COUNT(*) ermittelt. Komischerweise war die Anzahl Fotos gleich der Anzahl Kommentare wenn alle LEFT JOINS in der gleichen Abfrage waren. Habe ich die LEFT JOINS in der Abfrage testweise von 6 auf 2 reduziert, dann stimmten die Zahlen der einzelnen LEFT JOINS wieder. Daher meine Frage, ob sich viele LEFT JOINS in einer einzigen Abfrage irgendwie gegenseitig dazwischenfunken.
    Die Beziehungen in der ON-Klausel waren alle richtig, denn wie falsch die Werte ermittelt wurden, hing auch davon ab, in welcher Reihenfolge die LEFT JOINS angewendet wurden.
    Aber das ist vielleicht ein Thema für ein Support-Forum, ich bin damals jedenfalls verzweifelt.

    Zu SELECT *: da bin ich wieder beruhigt!

  8. GhostGambler sagt:

    @Alex
    Mach einfach einzelne COUNT-Queries…
    Einen einzigen Monster-Join zu basteln ist auch nicht wirklich gut.

    Wenn du unbedingt eine Lösung zu deinem Query haben willst, musst du den mal posten.

  9. Alex sagt:

    Die Query habe ich natürlich nicht mehr, da ich damals umdenken musste. Ich habe mir eben gedacht, dort vielleicht auf einen Bug gestoßen zu haben.
    Trotzdem danke.

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>