am 2. Dezember 2009
Auch wenn es derzeit die neuen und heiß diskutierten Alternativen zu relationalen Datenbanken gibt (siehe Reihe “Ein Leben nach SQL“), wird die Ehe PHP und MySQL sicherlich noch lange halten. Doch oft wird vergessen, dass Ehepartner gleichberechtigt sind und so wird MySQL in Projekten oft nur stiefmütterlich behandelt.
MySQL bietet derzeit sicherlich einen der einfachsten Wege, eine persistente Datenhaltungsschicht aufzubauen. Die freie Datenbank ist schnell, vielseitig, stabil, ausgereift, bei fast jedem Hoster vorinstalliert und sehr gut dokumentiert bzw. kommentiert. Daher ist MySQL für beinahe jedes Projekt bestens geeignet. Auch die meisten PHP Frameworks setzen auf MySQL und bieten eine einfache Integration. Doch gerade diese gute Unterstützung führt häufig zu einer Vernachlässigung der Datenbank. Der folgende Teil soll einen schnellen Überblick über einige grundlegende Optimierungsansätze und häufige Fehlerquellen bei MySQL bzw. RDBMS geben.
Mein Framework erledigt das mit der Datenbank schon
Frameworks, die Datenbanken beinahe komplett kapseln, verleiten leicht dazu, die Datenbank zu vergessen, da man keine oder nur wenig Berührungspunkte mit der Datenbank hat. Für Rapid Application Development ist dies bestens geeignet, doch wer weiß eigentlich, wie das Framework wirklich arbeitet?
Wird eine einfache Abfrage, wie zum Beispiel “Gib mir alle Kunden und ihre Projekte“, über einen schnellen Join realisiert, oder erstellt das Framework für jeden Kunden eine neue Query, die dann die jeweiligen Projekte abfragt?
Wenn in einer Zeile mehrere Spaltenwerte geändert werden, wie im folgenden Codebeispiel gezeigt, wird die gesamte Änderung in einem oder in mehreren UPDATE Statements abgearbeitet?
$datennsatz->change('Spalte1','wert1);
$datennsatz->change('Spalte2','wert1);
$datennsatz->change('Spalte3','wert1);
Resultiert in welchen SQL Statements?
Dies sind nur zwei sehr einfache und naheliegende Beispiele, die zeigen, dass diese Kapselung oder Abstraktion der Datenpersistenzschicht je nach Implementierung nicht ganz ohne Nachteile auskommt. Daher ist es umso wichtiger, dass man das verwendete Framework auch etwas unter der Haube kennt.
Die meisten guten Frameworks bieten die Option, sich die eigentlichen SQL Statements ausgeben zu lassen [4,5]. Dies hilft nicht nur das Framework besser zu verstehen und dementsprechend besser damit umzugehen, sondern auch SQL Statement-Sünder zu entlarven.
Meine Datenbank ist so langsam!?
Wenn die Persistenzschicht einmal zu langsam wird und man den Fehler im Programm- und Frameworkteil ausschließen kann, gibt es zwei sehr einfache Möglichkeiten etwas mehr über die Datenbank und ihre Problemfall-Queries zu erfahren:
MySQL Log-Slow-Queries [3]
Dieser Parameter wird in der Konfigurationsdatei von MySQL my.cnf angegeben und definiert eine Log Datei, in die alle Queries geschrieben werden, die zu viel Zeit benötigen.
log-slow-queries = /var/log/mysqld_slow.log
long_query_time = 3
In diesem Beispiel werden alle Queries, die über 3 Sekunden zur Ausführung benötigen, aufgezeichnet. Je nach Art der Anwendung sollte der Wert von long_query_time so gewählt werden, dass Ausnahmen leicht gefunden werden, die Datei aber nicht überfüllt wird. Der nächste Schritt, ohne dem eine Log Datei nur bedingt Sinn macht, ist die Kontrolle. Die beste Aufzeichnung ist nutzlos, wenn die Log Dateien nicht regelmäßig überprüft werden.
MySQL Explain [2]
Hat man einmal die “bösen” Statements festgemacht, können diese mit Hilfe des Befehls explain analysiert werden. Der Befehl zeigt, welchen Ausführungsplan, also die Art der Zugriffe auf die Datenbank, der MySQL Optimierer wählt.
Betrachten wir folgendes Beispiel:
mysql> EXPLAIN SELECT * FROM Projekte WHERE Kunde = 'Maier'
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | projekte | ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
Grundsätzlich sollte man vermeiden, sogenannte Full Table Scans zu erreichen. Anfragen, die keinen Index nutzen können, wie in diesem Beispiel, müssen über die gesamte Tabelle (Full Table) den gewünschten Eintrag suchen. In diesem Fall, da in der Spalte key bzw. possible_keys kein Index aufscheint, müssen 100.000 Zeilen durchsucht werden (siehe Spalte rows).
Verwendet eine häufig gestellte Query aus dem Slow-Log keinen Index, muss kontrolliert werden, ob weitere Indizes erzeugt oder alte angepasst werden können, sodass die Query schneller abgearbeitet werden kann.
Index? Habe ich alles, ich habe ja einen Primary Key
Betrachtet man die Datenbankmodellierung vieler Projekte (auch vieler OpenSource Projekte), dann könnte man annehmen, dass Entwickler nur den PrimaryKey (als Form des Index) kennen. Zusätzliche sinnvoll gesetzte Indizes können Datenbanken jedoch um ein Vielfaches beschleunigen, werden jedoch trotzdem nur selten oder rudimentär verwendet.
Genau genommen handelt es sich bei einem PrimaryKey nicht um einen Index. Die meisten Datenbanken, so auch MySQL, legen jedoch automatisch auf den PrimaryKey (eine oder mehrere Spalten) einen Index an. Man spricht daher in diesem Fall auch von einem Primärindex.
Wie beim PrimaryKey, über den sehr oft auf Daten zugegriffen wird, sollten auch andere Spalten einen Index (Sekundärindex) erhalten.
Um das oben angeführte Beispiel zu optimieren, kann zum Beispiel ein Index über die Spalte Kunde angelegt werden:
CREATE INDEX my_index ON Projekte(Kunde);
Ein neuerliches Explain zeigt den Unterschied:
mysql> EXPLAIN SELECT * FROM Projekte WHERE Kunde = 'Maier'
+----+-------------+-----------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | projekte | ref | my_index | my_index | 257 | const | 1 | Using where |
+----+-------------+-----------+------+---------------+----------+---------+-------+------+-------------+
Mit dem neuen Index müssen nicht mehr alle 100.000 Zeilen durchsucht werden, sondern es kann direkt auf die eine gewünschte Zeile (bei der gilt Kunde=Maier) zugegriffen werden. Dies ist in der Ausgabe von explain an der Spalte rows ersichtlich, die in diesem Beispiel jetzt nur mehr 1 beträgt.
Grundsätzlich sollte jede Spalte, über die oft auf Daten zugegriffen wird (wie Kunde in diesem Beispiel) oder über die ein Join durchgeführt wird, einen Index erhalten. Auch bei sehr kleinen Datenmengen kann dies einen enormen Geschwindigkeitsschub bedeuten, wenn auch die Einfügegeschwindigkeit von neuen Datensätzen durch Indizes etwas sinken kann.
Grundsatzregeln haben aber wie jede Regel Ausnahmen, in diesem Fall sogar sehr viele, doch mehr dazu gibts bei Interesse (Bitte hierzu um Kommentare) in einem nächsten Artikel…oder in den unten angeführten Links.
Weiterführende Links:
- Sehr guter Mysql Blog, der aber teilweise technisch in die Tiefe geht – http://www.mysqlperformanceblog.com/
- Das Explain Statement genau erklärt
- MySQL Slow Query Log
- Zend Framework DB Profiler in Verbindung mit FirePHP, dem Firebug Plugin
- MySQL Query Profiler
XPLAIN SELECT * FROM Projekte WHERE Kunde = 'Maier'
