7. December 2006, 18:46, by Silvan Mühlemann

Rassige SQL-Queries mit UNION

Hand in HandWas? Schon wieder überlastet? Jetzt haben wir doch gerade vier neue Server gekauft. Wollen wir nun weiter Hardware kaufen? Nein: Das erste, was wir in solchen Fällen machen, ist die Analyse des Slow Query Log der MySQL.

Hoppla: Da haben wir eine Query, welche 12 Sekunden dauert und auf 6 Tabellen zugreift. Diese Tabellen sind somit während dieser Zeit gelockt. INSERTs und UPDATEs müssen warten. Die auslösenden PHP-Prozesse auf den Webservern hängen, belegen einen Apache-Slot und damit Speicher. Mit der Zeit sind die all 512 Slots belegt, der Speicher gefüllt und der Benutzer hat seine Seite immer noch nicht gesehen.

Not nice.

Die Query (stark vereinfacht):

SELECT name
FROM persons
INNER JOIN regions ON persons.region_id=regions.region_id
WHERE persons.region_id=5 OR regions.country_id=1

“OR-Alarm”!

“OR-Alarm”? Das ist, wenn im WHERE-Statement einer Query ein OR benutzt wird. Und dieses OR verbindet zwei unterschiedliche Felder. Diese beiden Felder können zwar Indexiert sein, aber der Index kann wegen em OR nicht verwendet werden. Andere WHERE-Bedingungen, welche die Verwendung einens Indexes zulassen würden, sind nicht vorhanden. Dies kann man mit dem EXPLAIN SELECT-Kommando leicht überprüfen.

Wenn der Index nicht verwendet werden kann, muss die WHERE-Condition für alle Kombinationen von persons und regions überprüft werden. Bei 50’000 Personen und 100 Regionen sind das 5’000’000 Kombinationen, welche sequenziell durchaufen werden müssen. Das Ergebnis ist klar: Die Abfrage dauert lange 12 Sekunden.

Lässt man die eine Bedingung fallen, verschwindet das OR und der Index auf region_id kann verwendet werden:

SELECT name
FROM persons
INNER JOIN regions ON persons.region_id=regions.region_id
WHERE regions. country_id =1

Ueberprüfung mit EXPLAIN SELECT: Kein kartesisches Produkt mehr notwendig. Kein Table Scan mehr. Nur noch 10ms. Das gleiche gilt, wenn man die andere Bedingung fallen lässt.

Aufgeteilt mehr Leistung

Nun könnte man im Client diese beiden Queries hintereinander ausführen lassen und die resultierenden Zeilen in einem Array sammeln. Ergibt 20ms Ausführungszeit. Aber wenig eleganten Code.

Schöner ist die UNION-Clause: Hier kann man die Ergebnisse von mehreren Queries vereinigen.

SELECT name
FROM persons
INNER JOIN regions ON persons.region_id=regions.region_id
WHERE regions. country_id =1
UNION
SELECT name
FROM persons
WHERE persons.region_id=5

Intern werden nur beide Teilqueries separat ausgeführt. Da die Teilqueries kein OR mehr besitzen, kann der Index verwendet werden. Beide Teilqueries sind somit rasend schnell.

Resultat: Das gleiche wie bei der Query mit dem OR. Ausführungszeit 1000x höher.

Filed under: Uncategorized —

6 Comments

  1. Guter Post, werde mir das in Zukunft für unsere Projekte merken. Hast du irgendeine Buch-Empfehlung was SQL-Performancetuning angeht?
    Grüße,
    Schakko
    —- http://wap.ecw.de —-

    Comment by Schakko — 7. December 2006 @ 18:50

  2. Mein Buch war das Online-Manual von MySQL. :-)

    Im Buch “Building Scalable Websites” hat’s auch noch ein paar Performance-Tipps.

    Wenn gewünscht, schreibe ich mal einen Artikel über meine Erfahrungen in Sachen Query-Performance.

    Comment by Silvan Mühlemann — 7. December 2006 @ 18:55

  3. moin silvan,
    ein artikel darueber waere klasse! ist ein spannendes thema.

    Comment by Schakko — 8. December 2006 @ 08:49

  4. ‘Building Scalable Websites’ hab ich auch, kann ich allgemein als gutes und leicht lesbares Buch empfehlen.

    Comment by Colin — 8. December 2006 @ 09:56

  5. Silvan

    Wirklich ein toller Blog hier. Habe schon vieles augeschnappt bei Dir. Eigentlich habe ich noch keine Performance Probleme aber die Run-Time Information von phpMyAdmin zeigt doch ein paar Probleme auf. Ich denke es ist gut die Sache früh anzugehen, noch bevor man vor einem wirklichen Problem steht und dann im Schnellschuss was schrauben muss. Besser vorsorgen.

    Comment by Werner — 8. December 2006 @ 17:47

  6. Zum Punkt 2: Super intressant, gib uns mehr ;o)

    Comment by stejan — 20. December 2006 @ 09:17

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

© 2017 tilllate Schweiz AG - Powered by WordPress