My book recommendations

MySQL Optimierung

MySQL, es gehört zum Alltag eines jeden Webprogrammierers!
Doch viele wissen nicht, bzw. achten nicht darauf, das man mit falschen Querys starke Perfomanceeinbußen fahren kann.
Daher will ich euch ein paar Best Practices mit auf den Weg geben, die grundsätzlich für eine bessere Performance sorgen!

1) Vermeide SELECT * (sternchen)

Über SELECT * werden ALLE Spalten einer Tabelle eingelesen! Aber nicht immer benötigt man wirklich alle Spalten aus einer Tabelle! Meißt benötigt man nur 2-3 Spalten wie den “title” oder die “id” und trotzem ist man zu faul die Spaltennamen auszuschreiben. Also knallen wir einfach mal das * (sternchen) hin, ist ja einfacher nicht wahr?! Jaha… nur bei einem * (sternchen) werden wie gesagt ALLE Spalten einer Tabelle eingelesen und das kostet Performance.

Benötigen wir also zum Beispiel aus einer Tabelle nur die Spalte “id” und “title” sollten wir unbedingt darauf achten auch nur diese beiden Spalten einzulesen:

// Nicht empfehlenswert
$result = mysql_query('SELECT * FROM tabelle WHERE id = 1');

// Empfehlenswert
$result = mysql_query('SELECT id, title FROM tabelle WHERE id = 1');

2) Vermeide ORDER BY RAND()

Ein sehr beliebtes Feature! Sie möchten auf Ihrer Webseite per Zufall “einen” Artikel aus Ihrer Tabelle darstellen!
Was schreiben wir also, ganz easy:

$result = mysql_query('SELECT title FROM tabelle ORDER BY RAND() LIMIT 1');

Sehr cool! Wir bekommen also per Zufall immer nur einen Artikel (eine Row), ganz wie wir es uns vorgestellt haben!
Jedoch muss ich euch sagen, ORDER BY RANDOM() ernährt sich unheimlich gerne von unserer köstlichen und wertvollen Zutat namens Performance. Wie also können wir unsere Abfrage so optimieren dass wir das gleiche Ergebnis bekommen ?

Ganz einfach, wir besorgen uns erstmal die Anzahl aller verfügbaren Zeilen, generieren uns dann eine Zufallszahl zwischen 0 und der Gesamtanzahl und nutzen LIMIT :-)

Moment, *taschenrechner.. klick klick*… hum, das sind ja jetzt mehr Zeilen als mein schöner Einzeiler da! Ja richtig, aber er frist garantiert nicht so viel Performance! Also, hier der Code:

$result = mysql_query('SELECT COUNT(*) FROM tabelle);
$row = mysql_fetch_row($result);
$randomizeNumber = mt_rand(0, $row[0]-1);
$result = mysql_query('SELECT title FROM tabelle LIMIT ' . $randomizeNumber . ', 1');

Diese 4 Zeilen führen zum gleichen Ergebnis und sind nicht so Performancelastig.
Hier wird gezielt per LIMIT nur eine Row gelesen. Bei ORDER BY RAND() werden erst ALLE Rows gelesen und dann erst per LIMIT begrenzt.

3) Benutze LIMIT 1 bei eindeutigen Abfragen

Es kommt sehr oft vor das wir genau wissen welche Zeile wir brauchen. Also schreibt man folgende Query

$result = mysql_query('SELECT title FROM tabelle WHERE title = "Hello World"');

1 row in set (0.36 sec)

Wir wissen also daß wir nur eine Zeile zurückbekommen!
Auch hier können wir ein minimale Performacesteigerung erzwingen, und wie ? Naja, wir wissen das es nur eine Zeile gibt, lasst uns also ein LIMIT 1 dran hängen:

$result = mysql_query('SELECT id, title, crdate FROM tabelle WHERE title = "Hello World" LIMIT 1');

1 row in set (0.17 sec)

Auch wenn nicht wirklich spürbar, aber der Query ist schneller!
Wenn wir nach einer Spalte suchen die einen Index hat, wie zB die Spalte id, diese ist ja unique, macht es keinen Sinn da über den Index das Ergebnis schon rasend schnell geliefert wird.

4) Query Cache nutzen

Seit MySQL Version 4 steht uns der MySQL Query Cache zur Verfügung der in den meisten Fällen unsere Abfragen beschleunigt. Vorausgesetzt ist natürlich das dieser aktiviert und konfiguriert ist! Die Betonung im ersten Satz liegt übrigens auf “in den meisten Fällen” 😉

Was macht dieser Query Cache genau ?
Hier werden Querys und deren Ergebnismengen abgespeichert. Wenn die gleiche Abfrage mehrmals benutzt wird und sich die betroffenen Datensätze nicht geändert haben, wird das Ergebnis aus diesem Cache geliefert anstatt eine neue Abfrage zu generieren.

Jetzt ist es so das wir als Programmierer eigentlich nichts spezielles beachten müssen. Unsere Querys landen eigentlich automatisch im Cache. Stop! Nicht immer! Nutzen wir in unserer Abfrage eine nichtdeterministische Funktion wird der Cache nicht benutzt!

Ok, halten wir mal kurz an! Ihr fragt euch jetzt bestimmt: “Was für eine Funktion ? nichtdeterwas ?” :-) hehe! Hier ein kleines Zitat aus der MySQL Doku:

Eine Prozedur oder Funktion gilt als “deterministisch”, wenn sie für gleiche Eingabeparameter immer gleiche Resultate erzeugt; ansonsten ist sie “nichtdeterministisch”. Wenn in der Definition der Routine weder DETERMINISTIC noch NOT DETERMINISTIC steht, ist die Voreinstellung NOT DETERMINISTIC.

Soweit klar ? Super… dann können wir ja weiter machen.
Also, sobald wir eine solche Funktion in unserer Abfrage nutzen wird nicht mehr mit dem Cache gearbeitet! Soweit klar, hier ein Beispiel:

$result = mysql_query('SELECT title FROM tabelle WHERE myDate < = CURDATE()');

Mit diesem Query holen wir uns alle Zeilen wo die Spalte “myDate” kleiner gleich dem aktuellen Datum ist! CURDATE ist in diesem Fall eine nichtdeterministische Funktion! NOW() oder RAND() wären andere Beispiele!

Diesen Query können wir natürlich optimieren. Und zwar so das auch der Cache benutzt wird. Wir verzichten einfach auf diese komische nichtdeterministische Funktion und nutzen ein wenig PHP

$curdate = date('Y-m-d');
$result = mysql_query('SELECT title FROM tabelle WHERE date <= ' . $curdate);

5) Nutze das Werkzeug EXPLAIN

Wenn man einer SELECT Anweisung das Schlüsselwort EXPLAIN voranstellt, zeigt MySQL Informationen des Optimierers zum Ausführungsplan der Abfrage an. MySQL erläutert also, wie es die SELECT-Anweisung verarbeiten würde, und gibt zudem an, wie und in welcher Reihenfolge Tabellen miteinander verknüpft werden.

Zitat aus der MySQL Doku : http://dev.mysql.com/doc/refman/5.1/de/explain.html

Super, bedeutet wir haben ein Werkzeug mit dessen Hilfe wir Step by Step einen SELECT Query optimieren können!

Wir können jetzt also beim entwickeln unserer Applikation jedes mal wenn wir einen SELECT bauen direkt schauen ob dieser performant ist! Da wir die Applikation ja gerade erst bauen, die Datenbank nur ein paar Testeinträge besitzt, geht alles in allem recht flot und wir stempeln unseren Query als Performant ab!

1 Jahr später, die Applikation ist gut besucht, die Datenbank ist fett mit wunderbaren Informationen gefüllt, fragen wir uns, warum ist meine Webseite so langsam. Als ich die gebaut hab war doch alles so schnell. Mit der Zeit hat sich viel verändert, als wir beim entwickeln getestet haben hatten wir auch noch keinen Bezug auf die Realität, keine volle Datenbank, keine X-hundert Hits am Tag!

Wir wollen also versuchen unsere Webseite im MySQL Bereich zu optimieren. Um jetzt nicht jede einzelne Query wieder zu suchen und von Hand zu testen nutzen wir ein wunderbares Feature! Wir können MySQL sagen ab welcher Ausführungszeit eine Abfrage als “zu langsam” gilt. Und diese lassen wir dann mitloggen! Extrem cool nicht war.. wir haben also jetzt eine Möglichkeit Querys ausfindig zu machen die langsam sind. Diese nehmen wir uns jetzt genauer unter die Lupe und zwar mit unserem Werkzeug EXPLAIN!

EXPLAIN analysiert unsere Abfrage und stellt dar was die Datenbank mit der Abfrage genau macht.
Beispiel:

$result = mysql_query('EXPLAIN SELECT title FROM tabelle');

Was uns das Ergebnis genau sagt können wir auf der MySQL Seite nachlesen: http://dev.mysql.com/doc/refman/5.1/de/explain.html eine ausführliche Antwort um dieses zu verstehen würde sonst diesen Beitrag sprengen :-)

6) Suchfelder indizieren

Oft müssen wir per MySQL nach irgendwelchen Sachen suchen. Nehmen wir an, wir haben einen selbstprogrammierten Blog. Der User möchte nach einem Artikel suchen wo er sich nur schwach an den Titel erinnern kann. Irgendwas mit “Hello” war das….

Statisch umgesetzt könnte das so aussehen:

$result = mysql_query('SELECT COUNT(*) FROM tabelle WHERE title LIKE "Hello%"');

Wenn wir uns die Zeit dazu mal anschauen sehen wir das das schon ziemlich lange dauert: 1 row in set (0.31 sec)

Also, legen wir einen INDEX auf die Spalte “title” in der gesucht werden soll:

[mysql]ALTER TABLE tabelle ADD INDEX (“title”);[/mysql]

Führen wir unseren Query erneut aus:

$result = mysql_query('SELECT COUNT(*) FROM tabelle WHERE title LIKE "Hello%"');

sieht unser Ergebnis schon ganz anders aus: 1 row in set (0.07 sec)

Mit einem INDEX kann man also extreme Performancesteigerungen erreichen!

Kleiner Tip: Wo welcher Index verwendet wird können wir ganz einfach mit der Abfrage

SHOW INDEX FROM tabelle;

rausbekommen!

Es gibt bestimmt noch dutzende weitere Möglichkeiten seine Scripte zu optimieren! Ich möchte hier aber erst mal einen Cut machen. Wenn Ihr noch gute Optimierungsmöglichkeiten kennt die ich hier mit aufnehmen sollte, benutzt bitte die Kommentarfunktion!

Jedenfalls hoffe ich das euch der Artikel gefallen und vor allem geholfen hat! Ihr könnt diesen Artikel auch unten Bewerten oder per Twitter ein wenig pushen :-)

Lieben Gruß
Julian

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn
  • Pingback: Julian Kleinhans()

  • Pingback: t3n.de/socialnews()

  • Hi,

    hast du für “Benutze LIMIT 1 bei eindeutigen Abfragen” irgendwelche statistiken oder was, weil das wär sicher sehr interessant

  • Pingback: Anton Kejr()

  • Hi Georg, danke du hast mich da in der Tat auf einen Fehler aufmerksam gemacht den ich schon korrigiert habe. Natürlich macht es bei Feldern mit einem Index keinen Sinn, und das Feld id ist Unique und hat somit einen Index. Sinn macht es bei Feldern wie title ect die keinen Index besitzen. Jedoch sollten wir uns hier auch sicher sein das wir nur ein Ergebnis erwarten! Zeitangaben habe ich ergänzt!

  • Pingback: Web Tuts()