Optimierung

Diese Lektion enthält eine Liste von Optimierungstechniken, die nicht weiter untergliedert sind, da viele Techniken unter mehrere verschiedene Kategorien fallen. Falls man eine Datenbankanwendung optimieren will, kann man einfach alle Techniken durchgehen und die anwendbaren Techniken ausprobieren.

Allgemeines

Durch Optimierung von Abfragen kann sich die Verständlichkeit der Abfragen verschlechtern, es können sich dabei auch Fehler einschleichen. Daher sollten Optimierungstechniken nur eingesetzt werden, wenn es auch wirklich erwiesen ist, daß etwa zu langsam ist.

Optimierung bedeutet hier, alles so zu gestalten, daß Abfragen und Kommandos möglichst schnell sind.

In der Praxis ist die Optimierung oft tatsächlich eines der Hauptprobleme.

Bei allen Umsetzungen von Ideen zur Optimierung stets durch Zeitmessung überprüfen, ob diese überhaupt wirklich eine Verbesserung bewirken!

Beispielsweise kann ein Register bei einer kleinen Tabelle die Geschwindigkeit manchmal verringern.

Techniken, die eine Datenbankanwendung unter einem Datenbankprodukt beschleunigen, könnten dieselbe Anwendung verlangsamen, wenn sie unter einem anderen Datenbankprodukt eingesetzt werden.

Der Abfrageoptimierer

Bei einer Gleichheitsverbindung wird beispielsweise normalerweise nicht  zuerst zeitaufwendig ein riesiges kartesisches Produkt aufgebaut und abgespeichert und anschließend eine Auswahl daraus vorgenommen. Die meisten Datenbankprogramme enthalten schon einen Abfrageoptimierer, der die einzelnen Bestandteile einer Abfrage so anordnet und miteinander verbindet, daß diese möglichst schnell erledigt werden.

Analyse

Abfrageanalysen: MySQL Enterprise Monitor's query analyzer, pt-query-digest von Percona Toolkit, siehe auch: myisamchk, OPTIMIZE TABLE, PROCEDURE ANALYSE()

Register

Schon genannt: Register (Indizes) und Explain

Register sind besonders für Spalten hilfreich, die in Tabellenverbindungen innerhalb der Bedingung hinter »ON« verwendet werden, oder in denen oft gesucht wird (wenn das Suchmuster so beschaffen ist, daß eine Sortierung dafür offensichtlich hilfreich ist).

Mit EXPLAIN kann erkannt werden, ob Indizes genutzt werden

Programmatische Zugriffe

Falls mit einer prozeduralen Sprache auf eine SQL-Datenbank zugegriffen wird, dann sollten die Tabelle nicht eingelesen und in der prozeduralen Sprache verarbeitet werden, sondern so viele Verarbeitungsvorgänge wie möglich (z.B. JOINs) sollten von der Datenbank erledigt werden. Die prozedurale Sprache soll nur das tun, was nicht mit SQL geht. Daher sollten entsprechende Arbeitsaufträge nicht an Programmierer mit guten Kenntnissen in der prozeduralen Sprache aber nur geringen SQL-Kenntnissen vergeben werden (diese neigen dazu die Daten mit der prozeduralen Sprache zu verarbeiten, weil sie gar nicht wissen, was mit SQL alles möglich ist).

Denormalisierung

Manchmal kann es schneller sein, wenn Tabellen nicht normalisiert sind, also beispielsweise bestimmte Informationen redundant gespeichert werden

Wenn bestimmte Tabellen sich nur selten ändern, kann es helfen, die Ergebnisse häufig benötigter Verbindungen als Basistabellen redundant vorrättig zu halten

Falls eine langsame Abfrage von Tabellen abhängt, die sich nur selten ändern, könnte ihr Ergebnisse dauerhaft gespeichert werden (diese Speicherung muß dann bei Änderungen aktualisiert werden.)

Tabellen im Speicher halten

Häufig benötigte kleine Tabellen mit entsprechender ENGINE dauerhauft im Hauptspeicher halten

Datentypen

DECIMAL durch DOUBLE ersetzen, DOUBLE durch INTEGER ersetzen; kleine numerische Datentypen konstanter Länge bevorzugen

IDs sollten Zahlentypen, wie INTEGER, sein

Variable Feldbreiten durch konstante Feldbreiten ersetzen: Es kann schneller sein, wenn alle Felder eines Datensatzes konstante Größe haben (VARCHAR durch CHAR ersetzen) VARCHAR kann Speicherplatz sparen, aber bei vielen Änderungen an einer Tabelle auch zu Fragmentierung führen.

? Alle Tabelle möglichst klein halten, Feldbreiten eher knapp bemessen

Es kann helfen, wenn möglichst viele Spalten mit NOT NULL deklariert wurden

Komplexe Abfragen

Bei JOIN sollten beide Spalten indiziert sein, gleichen Typ und gleiche Codierung haben

Bei mehreren Tests (WHERE) hintereinander: Schnelle Tests zuerst bei großer Datenmenge, dann langsame bei schon verkleinerter Datenmenge danach

Ausprobieren ob eine Abfrage schneller mit Unterabfragen oder mit Views geht (manchmal gibt es auch keinen Unterschied zwischen beiden)

Zusammengesetzte Schlüssel sind manchmal weniger effizient als einfache Schlüssel

Einzelne Optimierungstechniken

Count

• count( * )> 0 ⇒ if exists

Suchen

• left / regexp … ⇒ LIKE 'abc%' (nutzt Schlüssel, geht nicht bei Zahlenspalte)
• Man kann ausprobieren, ob in einer bestimmten Situation LIKE, RLIKE, REGEXP oder InnoDB-FULLTEXT schneller ist
• Man kann ausprobieren, ob das Hinzufügen eines Registers für die Spalte hilft (EXPLAIN verwenden!)
• Falls mehrere Vergleiche durchgeführt werden sollen, sollten die schnelleren Vergleich zuerst  erfolgen

Verbindungen

• Alle an einer Verbindung (hinter »ON«) beteiligten Spalten sollten registriert sein
• Spalten die miteinander verglichen werden, sollten den gleichen Datentyp haben

»LIMIT«

• Seitenabrufe mit LIMIT und Versatz können mehr Arbeit machen als man erwartet, besonders hintere

»UNION«

• Es kann schneller sein »UNION ALL« zu verwenden und Wiederholung gleicher Zeilen dann auf andere Weise zu vermeiden.

Maschinen

• Mit verschiedenen Maschinen experimentieren: MyISAM kann für SELECT schneller sein als InnoDB, wenn keine Transaktionen benötigt werden

Speichermedien

• Basistabellen können teilweise im Hauptspeicher gehalten werden
• Es ist möglich einige Basistabellen auf einen separaten Massenspeicher auszulagern, so daß gleichzeitige Nutzungen verschiedener Basistabellen beschleunigt werden

PHP oder andere Programmiersprachen

• sich mit mysql_unbuffered_query() vertraut machen
• Prepare nicht vor jedem Bind-Value neu machen