Dieser Abschnitt listet eine Anzahl verschiedener Tipps zur Verbesserung der Abfrageverarbeitungsgeschwindigkeit auf:
Verwenden Sie permanente Verbindungen zur Datenbank, um die
zusätzliche Belastung durch das Herstellen und Abbauen von
Verbindungen zu umgehen. Können Sie keine
Permanentverbindungen verwenden und stellen Sie viele neue
Verbindungen zur Datenbank her, dann sollten Sie den Wert
der Variablen thread_cache_size
ändern.
Siehe auch Abschnitt 7.5.2, „Serverparameter feineinstellen“.
Überprüfen Sie immer, ob alle Ihre Abfragen wirklich die
Indizes verwenden, die Sie in den Tabellen erstellt haben.
In MySQL können Sie dies mit der
EXPLAIN
-Anweisung tun. Siehe auch
Abschnitt 7.2.1, „EXPLAIN
-Syntax (Informationen über ein
SELECT
erhalten)“.
Versuchen Sie komplexe SELECT
-Abfragen an
häufig aktualisierte MyISAM
-Tabellen zu
umgehen, um Probleme in Verbindung mit der Tabellensperrung
zu vermeiden, die aufgrund des konkurrierenden Zugriffs
durch Leser und Schreiber entstehen.
Bei MyISAM
-Tabellen, bei denen in der
Mitte keine gelöschten Datensätze vorhanden sind, können
Sie Datensätze am Ende einfügen, während gleichzeitig
eine andere Abfrage aus der Tabelle liest. Wenn ein solches
Verhalten wichtig ist, sollten Sie die Tabelle so verwenden,
dass möglichst keine Datensätze gelöscht werden. Eine
andere Möglichkeit besteht darin, die Tabelle mit
OPTIMIZE TABLE
zu defragmentieren, wenn
Sie viele Datensätze gelöscht haben. Siehe auch
Abschnitt 14.1, „Die MyISAM
-Speicher-Engine“.
Um komprimierungsbedingte Probleme zu beheben, die im
Zusammenhang mit ARCHIVE
-Tabellen
auftreten können, können Sie OPTIMIZE
TABLE
verwenden. Siehe auch
Abschnitt 14.8, „Die ARCHIVE
-Speicher-Engine“.
Verwenden Sie ALTER TABLE … ORDER BY
, wenn
Sie Datensätze gewöhnlich in der Reihenfolge
expr1
,
expr2
, …
abrufen. Wenn Sie diese Option nach umfangreicheren
Änderungen in der Tabelle verwenden, können Sie die
Leistung unter Umständen steigern.
expr1
,
expr2
, …
In manchen Fällen kann es sinnvoll sein, eine Spalte hinzuzufügen, die auf den Daten in anderen Spalten basierende „Hash-Werte“ enthält. Wenn diese Spalte kurz und ausreichend eindeutig ist, kann dies wesentlich schneller sein als ein „breiter“ Index über viele Spalten. In MySQL ist die Verwendung dieser Zusatzspalte ganz einfach:
SELECT * FROMtbl_name
WHEREhash_col
=MD5(CONCAT(col1
,col2
)) ANDcol1
='constant
' ANDcol2
='constant
';
Bei MyISAM
-Tabellen, die sich häufig
verändern, sollten Sie die Verwendung von Spalten variabler
Länge (VARCHAR
, BLOB
und TEXT
) möglichst unterlassen. Die
Tabelle verwendet das dynamische Datensatzformat, sobald
auch nur eine einzige Spalte variabler Länge vorhanden ist.
Siehe auch Kapitel 14, Speicher-Engines und Tabellentypen.
Es ist normalerweise nicht sinnvoll, eine Tabelle in mehrere
getrennte Tabellen zu unterteilen, weil die Datensätze dann
sehr groß werden. Beim Zugriff auf einen Datensatz besteht
die größte Anforderung aus leistungstechnischer Sicht im
Suchvorgang, der auf der Festplatte ausgeführt wird, um das
erste Byte des Datensatzes zu finden. Wurde der Beginn der
Daten gefunden, dann können die meisten modernen
Festplatten den vollständigen Datensatz für die meisten
Anwendungen ausreichend schnell lesen. Die einzigen Fälle,
in denen das Aufteilen einer Tabelle einen nennenswerten
Unterschied macht, liegen vor, wenn es sich um eine
MyISAM
-Tabelle handelt, die das
dynamische Datensatzformat verwendet, welches Sie in eine
feste Datensatzgröße umwandeln können, oder wenn Sie die
Tabelle sehr häufig scannen müssen, die meisten Spalten
aber nicht benötigen. Siehe auch
Kapitel 14, Speicher-Engines und Tabellentypen.
Müssen Sie häufig Ergebnisse wie beispielsweise Summenwerte basierend auf Daten aus einer Vielzahl von Datensätzen berechnen, dann kann es praktisch sein, eine neue Tabelle einzurichten und den Zähler in Echtzeit zu aktualisieren. Eine Änderung der folgenden Form ist sehr schnell:
UPDATEtbl_name
SETcount_col
=count_col
+1 WHEREkey_col
=constant
;
Dies ist sehr wichtig, wenn Sie MySQL-Speicher-Engines wie
MyISAM
verwenden, die nur Sperren auf
Tabellenebene bieten (d. h. mehrere Leser bei einem
Schreiber). Dies bietet auch bei den meisten anderen
Datenbanksystemen eine bessere Performance, da der
Datensatzsperrmanager in diesem Fall weniger zu tun hat.
Wenn Sie Statistiken aus großen Logtabellen ermitteln müssen, sollten Sie Zusammenfassungstabellen verwenden, statt die gesamte Logtabelle zu scannen. Die Erstellung der Zusammenfassungen sollte wesentlich schneller sein als die Berechnung von „Echtzeitstatistiken“. Die Neuerstellung von Übersichtstabellen aus den Logs ist, wenn sich (abhängig von Geschäftsentscheidungen) Faktoren ändern, schneller als eine Anpassung der laufenden Anwendung.
Sofern möglich, sollten Sie Berichte als „Echtzeitberichte“ oder als „statistische Berichte“ klassifizieren, wobei die für die statistischen Berichte erforderlichen Daten nur aus Zusammenfassungstabellen erzeugt werden, die ihrerseits in regelmäßigen Abständen aus den Echtzeitdaten erstellt werden.
Nutzen Sie die Tatsache, dass Spalten Standardwerte haben. Fügen Sie Werte nur dann explizit ein, wenn sich der einzufügende Wert von der Vorgabe unterscheidet. Hierdurch verringern Sie die Analysearbeit für MySQL und erhöhen die Einfügegeschwindigkeit.
In manchen Fällen ist es sinnvoll, Daten zu packen und in
einer BLOB
-Spalte zu speichern. In diesem
Fall muss Ihre Anwendung zwar Code zum Packen und Entpacken
der Daten enthalten, aber Sie sparen sich auf einer gewissen
Ebene viele Zugriffe. Dies ist praktisch, wenn Sie Daten
haben, die sich nicht gut in die zeilen- und spaltenbasierte
Struktur einer Tabelle einfügen lassen.
Normalerweise sollten Sie alle Daten nichtredundant halten. (Beachten Sie dabei die in der Datenbanktheorie so genannte Dritte Normalform.) Allerdings gibt es auch Situationen, in denen die Duplizierung von Daten oder die Erstellung zusammenfassender Tabellen zur Geschwindigkeitssteigerung beitragen können.
Bei einigen Aufgaben können etwa gespeicherte Routinen oder benutzerdefinierte Funktionen Optionen zur Optimierung der Leistung sein. Weitere Informationen finden Sie in Kapitel 19, Gespeicherte Prozeduren und Funktionen, und Abschnitt 26.3, „Hinzufügen neuer Funktionen zu MySQL“.
Sie können immer einen kleinen Geschwindigkeitsvorteil gewinnen, indem Sie Abfragen oder Antworten in Ihrer Anwendung in einem Cache ablegen und dann zahlreiche Einfüge- oder Aktualisierungsvorgänge gemeinsam ausführen. Wenn Ihr Datenbanksystem Tabellensperren unterstützt (wie es beispielsweise bei MySQL und Oracle der Fall ist), dann sollten Sie so sicherstellen können, dass der Index-Cache nur einmal nach Durchführung aller Änderungen synchronisiert wird. Sie können auch vom Abfrage-Cache von MySQL profitieren, um ähnliche Ergebnisse zu erhalten (siehe auch Abschnitt 5.14, „MySQL-Anfragen-Cache“).
Verwenden Sie INSERT DELAYED
, wenn Sie
nicht unbedingt wissen müssen, wann Ihre Daten geschrieben
werden. Dies verringert die Gesamtauswirkungen von
Einfügeoperationen, weil zahlreiche Datensätze im Zuge
eines einzigen Schreibvorgangs auf die Festplatte
geschrieben werden.
Verwenden Sie INSERT LOW_PRIORITY
, wenn
Sie SELECT
-Anweisungen Vorrang vor den
Einfügeoperationen gewähren wollen.
Verwenden Sie SELECT HIGH_PRIORITY
, um
abgerufene Datensätze in der Warteschlange nach vorne zu
setzen: In diesem Fall wird die
SELECT
-Anweisung auch ausgeführt, wenn
ein anderer Client darauf wartet, schreiben zu können.
Verwenden Sie INSERT
-Anweisungen für
mehrere Datensätze, um zahlreiche Daten mit einer
SQL-Anweisung zu speichern. Dies wird von vielen SQL-Servern
(einschließlich MySQL) unterstützt.
Laden Sie mit LOAD DATA INFILE
große
Datenmengen. Dies ist schneller als die Verwendung von
INSERT
-Anweisungen.
Erzeugen Sie mit AUTO_INCREMENT
eindeutige Werte.
Verwenden Sie hin und wieder OPTIMIZE
TABLE
, um eine Fragmentierung von
MyISAM
-Tabellen mit dynamischem
Datensatzformat zu verhindern. Siehe auch
Abschnitt 14.1.3, „MyISAM-Tabellenformate“.
Verwenden Sie, sofern möglich,
MEMORY
-Tabellen, um die Geschwindigkeit
zu erhöhen. Siehe auch
Abschnitt 14.4, „Die MEMORY
-Speicher-Engine“.
MEMORY
-Tabellen sind für nichtkritische
Daten nützlich, auf die häufig zugegriffen wird, also etwa
Informationen zum zuletzt angezeigten Werbebanner für
Benutzer, in deren Webbrowser Cookies nicht aktiviert sind.
In vielen Webanwendungen stellen Benutzersitzungen eine
andere Alternative für den Umgang mit flüchtigen
Statusdaten dar.
Bei Webservern sollten Bilder und andere Binärressourcen normalerweise als Dateien gespeichert werden: In der Datenbank speichern Sie also statt der Datei selbst nur einen Verweis darauf. Die meisten Webserver können Dateien besser zwischenspeichern als Datenbankinhalte, weswegen die Verwendung von Dateien in der Regel schneller ist.
Spalten mit identischen Daten in verschiedenen Tabellen sollten mit identischen Datentypen deklariert werden, damit auf den entsprechenden Spalten basierende Joins beschleunigt werden.
Verwenden Sie möglichst einfache Spaltennamen. In einer
Tabelle namens customer
sollten Sie statt
einer Spalte customer_name
besser einfach
den Spaltennamen name
benutzen. Damit die
Namen auf andere SQL-Server portierbar sind, sollten Sie sie
stets kürzer als 18 Zeichen halten.
Wenn Geschwindigkeit für Sie ein absoluter Schlüsselaspekt
ist, sollten Sie einen Blick auf maschinennahe
Schnittstellen zur Datenspeicherung werfen, die von
verschiedenen SQL-Servern unterstützt werden. Indem Sie
beispielsweise direkt auf die
MyISAM
-Speicher-Engine von MySQL
zugreifen, können Sie die Geschwindigkeit im Vergleich zur
SQL-Schnittstelle um das Zwei- bis Fünffache erhöhen. Zu
diesem Zweck müssen die Daten auf demselben Server wie die
Anwendung liegen, und der Zugriff sollte möglichst nur von
einem Prozess aus erfolgen (da die externe Dateisperrung
wirklich langsam ist). Man könnte diese Probleme
beseitigen, indem man maschinennahe
MyISAM
-Befehle am MySQL Server
implementiert (dies wäre eine einfache Möglichkeit, bei
Bedarf mehr Leistung herauszuholen). Durch sorgfältiges
Design der Datenbankschnittstelle könnte es recht einfach
sein, diesen Optimierungstyp zu unterstützen.
Wenn Sie numerische Daten verwenden, ist es in vielen Fällen schneller, auf Daten in einer Datenbank (über eine laufende Verbindung) statt in einer Textdatei zuzugreifen. Die Daten in der Datenbank sind wahrscheinlich in einem kompakteren Format gespeichert als in der Textdatei, weswegen beim Zugriff weniger Festplattenzugriffe auftreten. Außerdem können Sie Code in Ihrer Anwendung einsparen, denn Sie müssen die Textdateien nicht analysieren, um Zeilen- und Spaltenbegrenzungen zu finden.
Bei einigen Operationen kann die Replikation einen Leistungsgewinn bringen. Sie können die Abrufvorgänge durch die Clients auf mehrere Server verteilen, um einen Lastausgleich zu erzielen. Damit der Master bei der Erstellung von Backups nicht verlangsamt wird, können Sie Sicherungen mit einem Slave-Server erstellen. Siehe auch Kapitel 6, Replikation bei MySQL.
Die Deklaration einer MyISAM
-Tabelle mit
der Tabellenoption DELAY_KEY_WRITE=1
beschleunigt Indexaktualisierungen, da diese erst auf
Festplatte geschrieben werden, wenn die Tabelle geschlossen
wird. Der Nachteil besteht darin, dass Sie, wenn der Server
terminiert wird, während eine solche Tabelle geöffnet ist,
sicherstellen müssen, dass die Tabelle unbeschädigt ist,
indem Sie den Server mit der Option
--myisam-recover
ausführen oder
myisamchk verwenden, bevor Sie den Server
neu starten. (Allerdings sollten Sie auch in diesem Fall
keine Daten verlieren, wenn Sie
DELAY_KEY_WRITE
verwenden, weil die
Schlüsselinformationen sich immer aus den Datensätzen in
der Datendatei ableiten lassen.)
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.