MySQL-Referenzhandbuch :: 6.2.11 Weitere Optimierungstipps

Benutzen Sie persistente Verbindungen zur Datenbank, um Verbindungs-Overhead zu vermeiden. Wenn Sie keine persistenten Verbindungen benutzen können und viele neue Verbindungen zur Datenbank aufmachen, sollten Sie den Wert der Thread_cache_size-Variablen ändern. See Abschnitt 6.5.2, „Serverparameter tunen“.

Überprüfen Sie immer, dass alle Ihre Anfragen tatsächlich die Indexe benutzen, die Sie in den Tabellen erzeugt haben. In MySQL kann man das mit dem EXPLAIN-Befehl tun. See Explain: (Handbuch) Explain.

Versuchen Sie, komplexe SELECT-Anfragen auf Tabellen zu vermeiden, die viel aktualisiert werden, um Probleme mit Tabellensperren zu vermeiden.

Die neuen MyISAM-Tabellen können Zeilen in eine Tabelle ohne gelöschte Zeile zur gleichen Zeit einfügen, wie eine andere Tabelle aus ihr liest. Wenn das für Sie wichtig ist, sollten Sie Methoden in Betracht ziehen, bei denen Sie keine Zeilen löschen müssen, oder OPTIMIZE TABLE laufen lassen, nachdem Sie viele Zeilen gelöscht haben.

Benutzen Sie ALTER TABLE ... ORDER BY ausdruck1,ausdruck2,..., wenn Sie Zeilen zumeist in der Reihenfolge ausdruck1,ausdruck2,... abrufen. Wenn Sie diese Option nach großen Änderungen in der Tabelle nutzen, erzielen Sie eventuell höhere Performance.

In einigen Fällen kann es sinnvoll sein, eine Spalte einzuführen, die auf der Grundlage von Informationen aus anderen Spalten 'gehashed' ist. Wenn diese Spalte kurz und halbwegs eindeutig ist, kann das schneller sein als ein großer Index auf mehrere Spalten. In MySQL ist es sehr einfach, eine solche zusätzliche Spalte zu benutzen: SELECT * FROM tabelle WHERE hash=MD5(concat(spalte1,spalte2)) AND spalte_1='constant' AND spalte_2='constant'

Bei Tabellen, die sich viel ändern, sollten Sie versuchen, alle VARCHAR- oder BLOB-Spalten zu vermeiden. Sonst erhalten Sie dynamische Zeilenlängen, sobald Sie eine einzige VARCHAR- oder BLOB-Spalte verwenden. See Kapitel 8, MySQL-Tabellentypen.

Normalerweise nützt es nichts, eine Tabelle in verschiedene Tabellen aufzuteilen, nur weil die Zeile 'viel' werden. Um auf eine Zeile zuzugreifen, ist das wichtigste, was die Performance betrifft, der Suchvorgang nach dem ersten Byte der Zeile auf der Platte. Nachdem die Daten gefunden wurden, können die meisten neuen Platten die gesamte Zeile für die meisten Applikationen schnell genug lesen. Der einzige Fall, wo es wirklich etwas ausmacht, wenn eine Tabelle aufgeteilt wird, ist, wenn die Tabelle dynamische Zeilenlänge hat (siehe oben), was nicht in eine feste Zeilenlänge umgewandelt werden kann, oder wenn Sie die Tabelle sehr oft scannen müssen, die meisten der Spalten hierfür aber nicht benötigen. See Kapitel 8, MySQL-Tabellentypen.

Wenn Sie sehr oft etwas auf der Grundlage von Informationen aus sehr vielen Zeilen berechnen müssen (zum Beispiel Dinge zählen), ist es wahrscheinlich besser, eine neue Tabelle einzuführen und den Zähler in Echtzeit zu aktualisieren. Eine Aktualisierung des Typs UPDATE tabelle set zaehler=zaehler+1 where index_spalte=konstante ist sehr schnell!

Das ist sehr wichtig, wenn Sie Datenbanken wie MySQL benutzen, die nur Tabellensperren haben (viele Leser / einzelne Schreiber). Bei den meisten sonstigen Datenbanken ergibt das ebenfalls bessere Performance, weil der Zeilensperr-Manager weniger zu tun haben wird.

Wenn Sie Statistiken aus großen Log-Tabellen gewinnen wollen, benutzen Sie Zusammenfassungstabellen, statt die gesamte Tabelle zu scannen. Die Wartung der Zusammenfassungen sollte wesentlich leichter sein, als die Statistiken 'live' zu generieren. Es ist viel schneller, neue Zusammenfassungstabellen aus den Logs zu erzeugen, wenn sich Dinge ändern (abhängig von Geschäftsentscheidungen) als eine laufende Applikation ändern zu müssen!

Wenn möglich sollte man Berichte als 'live' oder 'statistisch' klassifizieren, wobei die Daten, die für statistische Berichte benötigt werden, nur auf der Grundlage von Zusammenfassungstabellen erzeugt werden, die aus den eigentlichen Daten generiert werden.

Ziehen Sie Vorteile aus der Tatsache, dass Spalten Vorgabewerte haben. Fügen Sie nur dann explizit Werte ein, wenn der einzufügende Wert vom Vorgabewert abweicht. Das verringert das Parsen, das MySQL durchführen muss, und erhöht die Einfügegeschwindigkeit.

In einigen Fällen ist es bequem, Daten zu komprimieren und in einem Blob zu speichern. In diesem Fall müssen Sie in Ihrer Applikation etwas zusätzlichen Code unterbringen, um die Dinge im Blob zu packen bzw. zu entpacken. Das kann aber in manchen Phasen etliches an Zugriffen einsparen. Das ist praktisch, wenn Sie Daten haben, die mit einer statischen Tabellenstruktur nicht konform sind.

Normalerweise sollten Sie versuchen, alle Daten nicht redundant zu halten (was sich in der Datenbanktheorie dritte Normalform nennt). Scheuen Sie sich aber nicht davor, Dinge zu duplizieren oder Zusammenfassungstabellen zu erzeugen, wenn Sie dies brauchen, um mehr Geschwindigkeit zu erzielen.

Gespeicherte Prozeduren (Stored Procedures) oder UDF (user defined functions, benutzerdefinierte Funktionen) sind eine gute Möglichkeit, mehr Performance zu erzielen. Sie sollten jedoch immer eine andere (langsamere) Möglichkeit parat haben, wenn Sie eine Datenbank benutzen, die gespeicherte Prozeduren nicht unterstützt.

Man erreicht immer etwas, wenn man Anfragen / Antworten in der Applikation cachet und versucht, viele Einfüge- oder Aktualisierungsvorgänge zugleich durchzuführen. Wenn Ihre Datenbank Tabellensperren unterstützt (wie MySQL und Oracle), sollte das dazu führen, dass der Index-Cache nur einmal auf Platte zurück geschrieben wird, nachdem alles Einfügen / Aktualisieren ausgeführt ist.

Benutzen Sie INSERT /*! DELAYED */, wenn Sie nicht wissen brauchen, wann Ihre Daten geschrieben werden. Das erhöht die Geschwindigkeit, weil viele Datensätze mit einem einzige Festplattenschreibzugriff geschrieben werden können.

Benutzten Sie INSERT /*! LOW_PRIORITY */, wenn Sie wollen, dass Ihre Selects höhere Priorität haben.

Benutzen Sie SELECT /*! HIGH_PRIORITY */, um zu bewirken, dass Selects in der Wartereihe nach vorn springen. Das heißt, der Select wird sogar dann durchgeführt, wenn jemand darauf wartet, etwas zu schreiben.

Benutzen Sie das mehrzeilige INSERT-Statement, um viele Zeilen mit einem SQL-Befehl zu speichern (viele SQL-Server unterstützen das).

Benutzen Sie LOAD DATA INFILE, um größere Datenmengen zu laden. Das ist schneller als normale Einfügevorgänge und wird noch schneller, wenn myisamchk in mysqld integriert wird.

Benutzen Sie AUTO_INCREMENT-Spalten, um eindeutige Werte zu erzeugen.

Benutzen Sie gelegentlich OPTIMIZE TABLE, um Fragmentierungen zu vermeiden, wenn Sie das dynamische Tabellenformat verwenden.

See Abschnitt 5.5.1, „OPTIMIZE TABLE-Syntax“.

Benutzen Sie - wenn möglich - HEAP-Tabellen, um mehr Geschwindigkeit zu erzielen. See Kapitel 8, MySQL-Tabellentypen.

Bei einer normalen Webserver-Konfiguration sollten Bilder als separate Dateien gespeichert werden. Das heißt, speichern Sie nur einen Verweis zur Datei in der Datenbank. Der Hauptgrund ist, dass normale Webserver viel besser darin sind, Dateien zu cachen als Datenbankinhalte. Daher ist es viel einfacher, ein schnelles System zu bekommen, wenn Sie Dateien benutzen.

Benutzen Sie für nicht kritische Daten, auf die oft zugegriffen wird, Tabellen im Arbeitsspeicher (zum Beispiel Informationen über die Banner, die Benutzern ohne Cookies zuletzt präsentiert wurden).

Spalten mit identischen Informationen in unterschiedlichen Tabellen sollten identisch deklariert sein und identische Namen haben. Vor Version 3.23 konnte man ansonsten langsame Joins erhalten.

Versuchen Sie, die Namen einfach zu halten (benutzen Sie name anstelle von kunde_name in der Kundentabelle). Um Namen für andere SQL-Server portabel zu halten, sollten Sie sie kürzer als 18 Zeichen halten.

Wenn Sie WIRKLICH hohe Geschwindigkeit brauchen, sollten Sie einen Blick auf die Low-Level-Schnittstellen zur Datenspeicherung werfen, die die unterschiedlichen SQL-Server unterstützen! Wenn Sie zum Beispiel auf MyISAM direkt zugreifen, erhalten Sie eine Geschwindigkeitssteigerung um den Faktor 2 bis 5, im Vergleich zur Benutzung der SQL-Schnittstelle. Um das durchführen zu können, müssen die Daten auf demselben Server liegen wie die Applikation und üblicherweise sollte auf sie nur von einem Prozess zugegriffen werden (weil externes Dateisperren reichlich langsam ist). Man könnte die oben genannten Probleme beseitigen, indem Low-Level-MyISAM-Befehle in den MySQL-Server eingebaut werden (das wäre eine einfache Möglichkeit, bei Bedarf mehr Performance zu erlangen). Indem die Datenbankshnittstelle sorgfältig entworfen wird, sollte es recht einfach sein, diese Arten von Optimierung zu unterstützen.

In vielen Fällen ist es schneller, auf Daten aus einer Datenbank (mit einer direkten Verbindung) als über eine Textdatei zuzugreifen, schon deshalb, weil die Datenbank wahrscheinlich kompakter ist als die Textdatei (wenn Sie numerische Daten benutzen) und hierdurch weniger Festplattenzugriffe erforderlich sind. Ausserdem wird Code eingespart, weil Sie Ihre Textdateien nicht parsen müssen, um Zeilen- und Spaltenbegrenzungen zu finden.

Ausserdem können Sie Replikation benutzen, um die Geschwindigkeit zu steigern. See Abschnitt 5.10, „Replikation bei MySQL“.

Wenn eine Tabelle mit DELAY_KEY_WRITE=1 deklariert wird, werden Aktualisierungen auf Indexe schneller, weil diese nicht auf Platte geschrieben werden, bis die Datei geschlossen wird. Der Nachteil ist, dass Sie auf diesen Tabellen myisamchk laufen lassen sollten, bevor Sie mysqld starten, um sicherzustellen, dass diese in Ordnung sind, falls irgend etwas mysqld mittendrin killt. Weil die Schlüssel-Informationen jederzeit aus den Daten erzeugt werden können, sollten Sie durch DELAY_KEY_WRITE nichts verlieren.