Je kleiner eine Tabelle, desto höher ist der Anteil, der davon im Hauptspeicher gehalten werden kann. Finden sich die Daten nicht im Cache, so kann eine kleinere, optimierte Tabelle zumindest mit weniger Leseoperationen auf der Festplatte durchsucht werden. Daher ist es bei der Beschleunigung einer Datenbankanwendung immer sinnvoll, zunächst die zugehörigen Tabellen zu verkleinern.
Verwenden Sie keine langen Spalten, wenn kurze Spalten ausreichen
Wenn Sie CHAR-Spalten fester Länge verwenden, sollten Sie diese nicht unnötig lang machen. Wenn der längste Wert, den Sie in einer Spalte ablegen, 40 Zeichen umfasst, sollten Sie sie nicht als CHAR(255) deklarieren, sondern als CHAR(40). Wenn Sie MEDIUMINT statt BIGINT verwenden können, wird Ihre Tabelle kleiner (weniger Festplattenzugriffe), und die Werte können in Berechnungen schneller verarbeitet werden. Und wenn Sie die Spalten indizieren, dann ist der Leistungsgewinn bei kürzeren Spalten noch größer, denn der Index beschleunigt nicht nur die Anfragen, sondern kürzere Indexwerte werden auch schneller verarbeitet als lange.
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |
Optimales Zeilenspeicherformat wählen
Bei MyISAM- und ISAM-Tabellen sollten Sie Spalten fester statt variabler Breite verwenden. Das gilt insbesondere für Tabellen, die häufig geändert werden und damit eher einer Fragmentierung unterliegen. Beispielsweise sollten Sie alle Zeichenspalten als CHAR statt als VARCHAR deklarieren. Damit braucht Ihre Tabelle zwar mehr Speicherplatz, aber die Zeilen fester Länge können viel schneller verarbeitet werden als Zeilen variabler Länge.
Bei InnoDB-Tabellen unterscheidet sich das interne Speicherformat für Spalten mit fester und für Spalten mit variabler Breite nicht (alle Zeilen verwenden einen Header, der Zeiger enthält, die auf die Spaltenwerte verweisen); es gibt hier also keinen Grund, CHAR gegenüber VARCHAR den Vorzug zu geben. Vielmehr sollten Sie VARCHAR sogar vorziehen, da der Speicherbedarf im Vergleich zu CHAR im Schnitt niedriger liegt; Sie können auf diese Weise also Speicherplatz sparen und die Anzahl der Festplattenzugriffe bei der Verarbeitung von Zeilen verringern.
Auch bei BDB-Tabellen gibt es zwischen den Speicherformaten keinen großen Unterschied. Sie können je eine Tabelle mit einem der Formate anlegen und dann empirisch prüfen, ob es auf Ihrem System einen bedeutenden Unterschied gibt.
Anmerkung: In dieser Beschreibung steht der Begriff »BLOB-Typen« immer stellvertretend für BLOB- und TEXT-Typen.
Deklarieren Sie Spalten als NOT NULL
Damit erzielen Sie eine schnellere Verarbeitung und brauchen weniger Speicherplatz. Außerdem werden die Anfragen manchmal vereinfacht, weil Sie nicht auf NULL als Sonderfall testen müssen.
Verwenden Sie ENUM-Spalten
Wenn Sie eine Zeichenkettenspalte haben, in der nur begrenzt viele unterschiedliche Werte vorkommen können, sollten Sie sie als ENUM-Spalte verwenden. ENUM-Werte können sehr schnell verarbeitet werden, weil sie intern als numerische Werte behandelt werden.
Verwenden Sie PROCEDURE ANALYSE()
Wenn Sie MySQL 3.23 oder höher einsetzen, führen Sie PROCEDURE ANALYSE() aus, um Informationen über die Spalten in Ihrer Tabelle zu erhalten:
SELECT * FROM tbl_name PROCEDURE ANALYSE();SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);
Eine Spalte des Ergebnisses zeigt einen Vorschlag für den optimalen Spaltentyp für jede Spalte in Ihrer Tabelle an. Das zweite Beispiel weist PROCEDURE ANALYSE() an, keine ENUM-Typen vorzuschlagen, in denen mehr als 16 Werte verwendet oder mehr als 256 Byte belegt werden (Sie können diese Werte natürlich beliebig abändern). Ohne diese Beschränkungen könnte die Ausgabe sehr lang sein; ENUM-Deklarationen sind häufig schwierig zu lesen.
Mithilfe der Ausgabe von PROCEDURE ANALYSE() können Sie feststellen, ob Ihre Tabelle dahingehend geändert werden sollte, einen effizienteren Typ zu verwenden. Spaltentypen können mit ALTER TABLE geändert werden.
Verwenden Sie OPTIMIZE TABLE zum Aufräumen fragmentierter Tabellen
Tabellen, die häufig verändert werden – insbesondere Tabellen mit Spalten variabler Länge – unterliegen häufig einer Fragmentierung. Die Fragmentierung ist von Nachteil, weil Speicher in den Festplattenblöcken, in denen Ihre Tabelle abgelegt wird, ungenutzt bleibt.
Mit der Zeit müssen Sie immer mehr Blöcke lesen, um ganze Zeilen zu erhalten, und die Performance sinkt. Das gilt für alle Tabellen mit Zeilen variabler Länge, betrifft aber insbesondere BLOB-Spalten, weil deren Größe so stark variieren kann. Die regelmäßige Ausführung von OPTIMIZE TABLE hilft Ihnen, die Performance Ihrer Tabellen aufrechtzuerhalten.
OPTIMIZE TABLE funktioniert bei MyISAM- und BDB-Tabellen, defragmentiert werden aber tatsächlich nur MyISAM-Tabellen. Eine Defragmentierungsmethode, die bei allen Tabellentypen funktioniert, besteht darin, den Tabelleninhalt mit mysqldump zu sichern und die Tabelle dann zu löschen und mit der Sicherungsdatei erneut zu erstellen:
% mysqldump --opt db_name tbl_name > dump.sql% mysql db_name < dump.sql
Legen Sie Ihre Daten in einer BLOB-Spalte ab
Wenn Sie ein BLOB verwenden, um Daten zu speichern, die Sie in Ihrer Anwendung packen bzw. entpacken, dann können Sie möglicherweise alles mit einer einzigen Suchoperation (statt mit mehreren) ermitteln. Das kann auch sinnvoll für Daten sein, die in einer Standardtabellenstruktur nicht einfach darzustellen sind oder sich mit der Zeit ändern. In der Beschreibung der Anweisung ALTER TABLE in einem anderen Kapitel des Buches ging es in einem Beispiel um eine Tabelle, die Ergebnisse aus den Feldern eines webbasierten Fragebogens aufnahm. In diesem Beispiel wurde gezeigt, wie der Tabelle mit ALTER TABLE Spalten hinzugefügt werden, wenn Sie die Umfrage um eine Frage ergänzen.
Ein anderer Ansatz zur Lösung dieses Problems bestünde darin, es dem Anwendungsprogramm zu überlassen, die Daten aus dem Web in irgendeine Datenstruktur zu packen und sie dann in eine einzige BLOB-Spalte einzulesen. So könnten Sie beispielsweise die Ergebnisse der Befragung mithilfe von XML darstellen und den entsprechenden XML-String in der BLOB-Spalte ablegen. Das bedeutet für die Anwendung einen zusätzlichen Aufwand bei der Kodierung der Daten (ebenso wie bei der späteren Dekodierung, wenn sie aus der Tabelle geladen werden), aber die Tabellenstruktur wird dadurch vereinfacht, und man muss sie nicht ändern, wenn der Fragebogen geändert wird.
Andererseits können auch BLOB-Werte Probleme verursachen, insbesondere, wenn Sie sehr viele DELETE- oder UPDATE-Operationen ausführen. Das Löschen eines BLOB kann ein sehr großes Loch in der Tabelle hinterlassen, das später vielleicht mit einem oder mehreren Datensätzen möglicherweise unterschiedlicher Größen gefüllt wird (wie Sie mit solchen Löchern umgehen, beschreibt der vorangegangene Punkt).
Verwenden Sie einen künstlichen Index
Künstliche Indexspalten können manchmal sehr nützlich sein. Eine mögliche Technik wäre, einen von den anderen Spalten abhängigen Hash-Wert zu erzeugen und diesen in einer separaten Spalte abzulegen; anschließend finden Sie die Zeilen, indem Sie nach den Hash-Werten suchen.
Diese Vorgehensweise ist allerdings nur bei Anfragen mit exakter Übereinstimmung sinnvoll (Hash-Werte sind nicht geeignet für Bereichssuchen mit Operatoren wie < oder >=). Hash-Werte werden seit MySQL 3.23 mit der Funktion MD5() erzeugt. Weitere Optionen sind SHA1() oder CRC32(), die mit MySQL 4.0.2 bzw. 4.1 eingeführt wurden.
Ein Hash-Index kann insbesondere für BLOB-Spalten sehr praktisch sein. Zum einen konnten Sie diese Typen vor MySQL 3.23 nicht indizieren. Aber selbst bei 3.23.2 oder neueren Versionen kann es schneller gehen, BLOB-Werte mithilfe eines Hash-Werts statt über die eigentliche BLOB-Spalte zu suchen.
Vermeiden Sie, große BLOB-Werte zu suchen, wenn das nicht unbedingt erforderlich ist
Beispielsweise ist eine Anfrage vom Typ SELECT * wenig sinnvoll, sofern Sie nicht sicher sind, dass die Ergebnisse in der WHERE-Klausel so beschränkt werden, dass Sie nur die gewünschten Zeilen erhalten. Andernfalls rufen Sie möglicherweise sehr große BLOB-Werte über das Netzwerk ab, ohne sie wirklich zu brauchen.
Auch hier kann es wieder sinnvoll sein, BLOB-Informationen in einer separaten Spalte abzulegen. Sie können nach dieser Spalte suchen, um die gewünschten Zeilen zu ermitteln, und dann den BLOB-Wert aus den gefundenen Zeilen holen.
Sammeln Sie BLOB-Werte in einer separaten Tabelle
Manchmal kann es sinnvoll sein, BLOB-Werte aus einer Tabellenspalte in eine separate Tabelle zu verschieben, wenn Sie dadurch die Zeilen der ursprünglichen Tabelle in ein Format fester Länge umwandeln können. Damit wird die Fragmentierung der ursprünglichen Tabelle reduziert, und Sie können die Leistungsvorteile von Zeilen fester Länge nutzen. Außerdem können Sie bei dieser Vorgehensweise Anfragen vom Typ SELECT * in der Primärtabelle ausführen, ohne umfangreiche BLOB-Werte über das Netzwerk zu holen.
Ausblick
Dieser Teil der Serie hat sich mit der Speicherplatzoptimierung der Tabellen und der darin enthaltenen Spalten beschäftigt. Im nächsten Artikel geht es darum, wie die Daten effizient geladen werden können. (mzu)
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |