Langsame MySQL-Statements finden und analysieren
Auf großen Webseiten bemerkt man als Webmaster oft gar nicht, dass Performance-Probleme auftreten, weil man nicht ständig alle Scripts aufruft und das wohlmöglich noch mit allen möglichen Eingabeparametern. Das wurde am Anfang mal gemacht und dann für performant befunden. Mit der Größe der Datenbank wächst allerdings oft auch der Aufwand für Joins und Ähnliches, deshalb können diese für performant gehaltenen Abfragen mit der Zeit echte Bremsen werden. Doch wie kann man ohne viel Aufwand trotzdem bemerken, ob irgendwelche Queries langsam sind?
MySQL kann ja (wie jedes andere DBMS auch) die Zeit ermitteln, wie lange eine Abfrage dauert. In PHPMyAdmin wird das dann auch angezeigt. Nun wäre es aber viel zu mühsam, jede Query zu testen und das wohlmöglich noch jede Woche, damit man sieht, wie die Abfragen skalieren. Als schlauer MySQL-Programmierer lässt man deshalb die Datenbank selbst diese Arbeit übernehmen. Man „sagt“ ihr einfach, ab welcher Ausführungszeit eine Abfrage als zu langsam gilt und loggt diese dann mit.
Das Loggen langsamer Queries kann durch folgende Einträge in die my.cnf aktiviert werden. Das Logfile erscheint im MySQL-Datenverzeichnis mit dem Dateinamen Servername-slow.log.
log-slow-queries long_query_time = 3
Der Parameter long_query_time gibt dabei an, ab welcher Ausführungszeit (in Sekunden) eine Abfrage als langsam eingestuft wird und somit mitgeloggt wird. Allerdings ist nicht jede Abfrage schlecht optimiert sondern es kann auch vorkommen, dass eine Abfrage aufgrund erhöhter Abfragefrequenz durch viele User auf einer Website gebremst wird. Man sollte sich also vor allem die Abfragen ansehen, die öfter als langsam geloggt werden.
Nun haben wir also die Bremsen identifiziert, mehr als dass sie eine langsame Ausführungszeit haben, wissen wir allerdings nicht. Deshalb mussdie Abfrage nun analysiert werden. Das geschieht durch den MySQL-Befehl EXPLAIN. Explain analysiert eine Abfrage und stellt dar, was die Datenbank mit der Abfrage macht bzw. wie sie behandelt wird.
Ein Beispiel:
EXPLAIN SELECT inhalt, vname, name, eingabedatum FROM zitate WHERE name= "Goethe" AND vname = "Johann Wolfgang von" ORDER BY eingabedatum; |
Die Aussage könnte dann so aussehen:
id: 1 select_type: SIMPLE table: zitate type: ref possible_keys: name, vname key: name key_len: 150 ref: const rows: 1229 Extra: Using where; Using filesort
Wichtig sind vor allem die possible_keys (Indizes, die in Frage kämen, um für die Abfrage genutzt zu werden) sowie der tatsächlich genutzte Key (der Index, der letztlich tatsächlich zum Einsatz kommt). Es ist dabei zu beachten, dass pro Tabelle, die an der Abfrage beteiligt ist, nur ein Schlüssel verwendet werden kann. In diesem Fall wird der key name verwendet, da die erste WHERE-Bedingung auf diese Spalte geht. Der Einsatz des Indizes auf der Spalte vname würde hier nichts bringen. Die Reihenfolge der Spalten im Index ist nicht ganz unwichtig.
Für diese Abfrage würde sich ein kombinierter Schlüssel anbieten, der die Spalten name und vname enthält (in dieser Reihenfolge).
Die zweite, ebenfalls sehr wichtige Aussage von EXPLAIN ist Extra. Using where bedeutet einfach, dass wir eine WHERE-Bedingung haben – das kann man auch selbst erkennen :-). Viel wichtiger ist aber das using filesort. Eine Sortierung braucht oft recht lange, ist meist sogar das Aufwendigste an der ganzen Abfrage. Wenn allerdings kein Index für die Sortierung verwendet werden kann, sortiert MySQL recht aufwendig eben über dieses filesort. Das ist seeehr langsam und bei größeren Abfragen gilt es stets zu vermeiden, dass using filesort zum Einsatz kommt.
Als Tipp kann man sagen, dass für einfache Abfragen (WHERE + ORDER BY) der Reihenfolge nach, wie es in der Query steht, alle Spalten in einen Index getan werden. In diesem Beispiel wäre also ein Index auf name, vname und eingabedatum zu setzen, denn dadurch ist die Ergebnismenge, die durch die WHERE-Bedingungen festgelegt wird, bereits sortiert. Nun verschwindet das using filesort beim EXPLAIN und die Abfrage ist viel schneller (statt 0,6 Sekunden nur noch 0,003).
Eines sei noch gesagt: Wenn man alles schön analysiert und beschleunigt hat, sollte das Slow-Logging wieder deaktiviert werden, da es ebenfalls Performance kostet.