Manuals > MySQL-Referenzhandbuch für Version 4.1.1-alpha - 6 MySQL-Optimierung

6 MySQL-Optimierung

Optimierung ist eine komplizierte Aufgabe, weil sie ein umfassendes Verständnis des gesamten Systems voraussetzt. Es ist möglich, einige lokale Optimierungen Ihres Systems oder Ihrer Applikation mit geringem Wissen durchzuführen. Je optimaler Sie allerdings Ihr System gestalten wollen, desto mehr müssen Sie darüber wissen.

Dieses Kapitel erklärt und gibt Beispiele für verschiedene Möglichkeiten, MySQL zu optimieren. Denken Sie allerdings daran, dass es immer noch zusätzliche Möglichkeiten gibt, das System noch schneller zu machen.

6.1 Überblick über Optimierung

Der wichtigste Teil, um ein System schnell zu machen, ist natürlich das grundlegende Design. Ausserdem müssen Sie wissen, welche Dinge Ihr System macht und was die Flaschenhälse sind.

Die wichtigsten Flaschenhälse sind:

• Suchvorgänge auf Festplatte. Die Festplatte benötigt Zeit, um ein Stück Daten zu finden. Bei modernen Festplatten (Stand: 1999) ist die mittlere Zugriffszeit üblicherweise weniger als 10 ms, daher können theoretisch etwa 1.000 Suchvorgänge pro Sekunde durchgeführt werden. Bei neueren Festplatten wird diese Zeit allmählich besser. Für einzelne Tabellen ist sie sehr schwer zu optimieren. Eine Möglichkeit, das zu optimieren, besteht darin, Daten auf mehr als eine Platte zu verteilen.
• Lesen von / Schreiben auf Festplatte. Wenn die Festplatte in der richtigen Position ist, um die Daten zu lesen, die wir brauchen, kann sie bei modernen Platten (Stand: 1999) etwas 10 bis 20 MB pro Sekunde heraus geben. Das ist leichter zu optimieren als Suchvorgänge, weil man von mehrfachen Festplatten parallel lesen kann.
• CPU-Zyklen. Wenn die Daten im Hauptspeicher sind (oder bereits dort waren), müssen sie verarbeitet werden, um das Ergebnis zu erhalten. Kleine Tabellen im Vergleich zum Arbeitsspeicher ist der Faktor, der am meisten begrenzt. Auf der anderen Seite ist Geschwindigkeit bei kleinen Tabellen üblicherweise nicht das Problem.
• Speicher-Bandbreite. Wenn der Prozessor mehr Daten braucht, als in den CPU-Cache passen, wird die Bandbreite des Hauptspeichers zum Flaschenhals. Auf den meisten Systemen ist das ein ungewöhnlicher Flaschenhals, aber man sollte sich dessen bewusst sein.

6.1.1 MySQL-Design-Einschränkungen

Weil MySQL extrem schnelles Tabellensperren beherrscht (mehrfache Leser / einzelne Schreiber), ist das größte verbleibende Problem eine Mischung aus einem laufenden Strom von Einfügevorgängen und langsamen Selects auf dieselbe Tabelle.

Wir glauben, dass diese Wahl auf einer sehr großen Anzahl von Systemen letztlich einen Gewinn darstellt. Auch dieser Fall ist üblicherweise dadurch zu lösen, dass man mehrfache Kopien der Tabelle vorhält, aber man benötigt mehr Anstrengung und Hardware.

Wir arbeiten auch an einigen Erweiterungen, um dieses Problem in Hinsicht auf einige häufige Applikationsnischen zu lösen.

6.1.2 Portabilität

Weil alle SQL-Server unterschiedliche Teile von SQL implementieren, ist es immer Arbeit, portable SQL-Applikationen zu schreiben. Bei sehr einfachen Selects und Inserts ist das sehr einfach, aber je mehr Sie brauchen, desto schwieriger wird es. Wenn Sie eine Applikation wollen, die bei vielen Datenbanken noch schnell läuft, wird es sogar noch schwieriger!

Um eine komplexe Applikation portabel zu machen, müssen Sie sich für eine Reihe von SQL-Servern entscheiden, mit denen sie funktionieren soll.

Sie können das MySQL-Crash-me-Programm bzw. die Webpage http://www.mysql.com/information/crash-me.php benutzen, um Funktionen, Typen und Einschränkungen zu finden, die Sie mit einer Auswahl von Datenbank-Servern benutzen können. Crash-me testet bei weitem nicht alles, was möglich ist, aber mit etwa 450 unterschiedlichen Dingen ist es recht umfassend.

Sie sollten zum Beispiel keine Spaltennamen benutzen, die länger als 10 Zeichen sind, wenn Sie auch Informix oder DB2 benutzen wollen.

Sowohl die MySQL-Benchmarks als auch die Crash-me-Programme sind sehr Datenbank-abhängig. Indem Sie einen Blick darauf werfen, wie wir damit umgegangen sind, bekommen Sie ein Gefühl dafür, was Sie in Ihrer Applikation schreiben müssen, damit diese Datenbank-unabhängig läuft. Die Benchmark-Tests selbst befinden sich im `sql-bench'-Verzeichnis der MySQL-Quelldistribution. Sie sind in Perl mit der DBI-Datenbank-Schnittstelle geschrieben (die den Zugriffsteil des Problems löst).

Siehe http://www.mysql.com/information/benchmarks.html wegen der Ergebnisse aus diesem Benchmark-Test.

Wie Sie an den Ergebnissen sehen, haben alle Datenbanken einige Schwachpunkte, das heißt, sie haben verschiedene Design-Kompromisse, die zu unterschiedlichem Verhalten führen.

Wenn Sie nach Datenbank-Unabhängigkeit streben, müssen Sie ein gutes Gefühl für die Flaschenhälse jedes SQL-Servers bekommen. MySQL ist SEHR schnell beim Abrufen und Aktualisieren von Dingen, hat aber Probleme, wenn gleichzeitig langsame Leser / Schreiber auf dieselbe Tabelle zugreifen. Oracle hat ein großes Problem, wenn Sie versuchen, auf Zeilen zuzugreifen, der kürzlich aktualisiert wurden (solange, bis sie auf Platte zurückgeschrieben wurden). Transaktionale Datenbanken sind allgemein nicht sehr gut darin, Zusammenfassungstabellen aus Log-Tabellen zu erzeugen, weil in diesem Fall Sperren auf Zeilenebene fast nutzlos ist.

Um Ihre Applikation wirklich Datenbank-unabhängig zu machen, müssen Sie eine leicht erweiterbare Schnittstelle definieren, über die Sie Ihre Daten manipulieren. Weil auf den meisten Systemen C++ verfügbar ist, ist es sinnvoll, C++-Klassen als Schnittstellen zu den Datenbanken zu benutzen.

Wenn Sie irgend ein spezifisches Feature einer Datenbankbenutzung (wie den REPLACE-Befehl in MySQL), sollten Sie eine Methode für die anderen SQL-Server codieren, um dasselbe Feature (wenngleich langsamer) zu implementieren. Bei MySQL können Sie die /*! */-Syntax benutzen, um MySQL-spezifische Schlüsselwörter in einer Anfrage zu verwenden. Der Code innerhalb von /**/ wird von den meisten anderen SQL-Servern als Kommentar behandelt (ignoriert).

Wenn WIRKLICH hohe Performance wichtiger als Exaktheit ist, wie bei einigen Web-Applikationen, besteht eine Möglichkeit darin, eine Applikationsebene zu erzeugen, die alle Ergebnisse cachet, um Ihnen noch höhere Performance zu bringen. Indem Sie alte Ergebnisse nach einer Weile 'auslaufen' lassen, können Sie den Cache in vernünftiger Weise 'frisch' halten. Das ist in Fällen extrem hoher Last recht nett, wobei Sie den Cache dynamisch vergrößern und die Verfallszeit (Expire Timeout) höher setzen können, bis wieder Normalauslastung eintritt.

In diesem Fall sollte die Tabellenerzeugungsinformation Informationen über die ursprüngliche Cache-Größe enthalten und darüber, wie oft die Tabelle normalerweise aktualisiert (refresh) werden sollte.

6.1.3 Wofür benutzen wir MySQL?

In der anfänglichen Phase der Entwicklung von MySQL wurden die Features von MySQL für unseren größten Kunden gemacht. Dieser macht Data-Warehousing für eine Reihe der größten Einzelhändler in Schweden.

Aus allen Verkaufsstellen erhalten wir wöchentliche Zusammenfassungen aller Bonuskarten-Transaktionen, und es wird erwartet, dass daraus nützliche Informationen für die Eigentümer der Verkaufsstellen zur Verfügung gestellt werden, damit diese herausfinden können, wie ihre Werbemaßnahmen ihre Kunden beeinflussen.

Die Datenmenge ist recht riesig (etwa 7 Millionen Zusammenfassungs-Transaktionen pro Monat), und wir haben Daten von 4 bis 10 Jahren, die wir dem Benutzer präsentieren müssen. Wir bekamen wöchentliche Anfragen von Kunden, die 'sofortigen' Zugriff auf neue Berichte aus diesen Daten wollten.

Die Lösung bestand darin, alle Informationen monatsweise in komprimierten 'Transaktions-' Tabellen zu speichern. Wir haben einen Satz einfacher Makros (ein Skript), die aus diesen Tabellen Zusammenfassungstabellen erzeugen, die nach verschiedenen Kriterien gruppiert sind (Produktgruppe, Kunden-ID, Verkaufsstelle usw.). Die Berichte sind Web-Seiten, die dynamisch durch ein kleines Perl-Skript erzeugt werden, das eine Web-Seite parst, die enthaltenen SQL-Statements ausführt und die Ergebnisse einfügt. Wir hätten statt dessen PHP oder mod_perl benutzt, aber diese waren damals noch nicht verfügbar.

Für grafische Darstellungen schrieben wir ein einfaches Werkzeug in C, das GIFs auf der Grundlage der Ergebnisse einer SQL-Anfrage erzeugen kann (nach einigem Verarbeiten des Ergebnisses). Dieses wird ebenfalls dynamisch durch ein Perl-Skript ausgeführt, das die HTML-Dateien parst.

In den meisten Fällen kann ein neuer Bericht einfach durch das Kopieren eines bestehenden Skripts und das Verändern der SQL-Anfrage darin erzeugt werden. In einigen Fällen müssen wir einer bestehenden Zusammenfassungstabelle weitere Felder hinzufügen oder eine neue generieren, aber auch das ist recht einfach, weil wir alle Transaktionstabellen auf Platte haben. (Momentan haben wir mindestens 50 GB an Transaktionstabellen und 200 GB weiterer Kundendaten.)

Wir lassen unsere Kunden auch direkt mit ODBC auf die Transaktionstabellen zugreifen, so dass fortgeschrittene Benutzer selbst mit den Daten experimentieren können.

Wir hatten mit der Handhabung keinerlei Probleme, auf einer recht bescheidenen Sun Ultra SPARCstation (2x200 MHz). Kürzlich haben wir einen unserer Server auf eine mit 2 Prozessoren bestückte 400 MHz-UltraSPARC erweitert und planen jetzt, Transaktionen auf Produktebene zu handhaben, was eine zehnfache Steigerung der Datenmenge bedeuten würde. Wir glauben, dass wir auch damit Schritt halten können, indem wir unseren Systemen einfach mehr Festplattenplatz hinzufügen.

Wir experimentieren auch mit Intel-Linux, um in der Lage zu sein, mehr CPU-Power preisgünstiger zu erhalten. Jetzt, wo wir das binäre portable Datenbankformat haben (neu seit Version 3.23), werden wir dieses für einige Teile der Applikation benutzen.

Unser anfängliches Gefühl sagt uns, dass Linux viel besser bei geringer bis mittlerer Last ist, während Solaris wegen der extremen Festplatten-Eingabe-/Ausgabe-Geschwindigkeit (Disk-IO) bei Hochlast besser ist, aber wir können noch nichts Endgültiges darüber aussagen. Nach einigen Diskussionen mit den Linux-Kernel-Entwicklern ist das eventuell ein Seiteneffekt von Linux, das dem Stapel-Job so viel Ressourcen gibt, dass die interaktive Performance sehr gering wird. Dadurch scheint die Maschine sehr langsam und unempfänglich für Eingaben zu lassen, während große Stapel-Jobs abgearbeitet werden. Wir hoffen, dass dies in zukünftigen Linux-Kernels besser gehandhabt wird.

6.1.4 Die MySQL-Benchmark-Suite

Dieser Abschnitt sollte eine technische Beschreibung der MySQL- Benchmark-Suite (und von crash-me) enthalten, aber diese Beschreibung wurde noch nicht geschrieben. Momentan können Sie eine gute Idee über den Benchmark bekommen, wenn Sie einen Blick auf den Code und die Ergebnisse im `sql-bench'-Verzeichnis jeder MySQL-Quelldistribution werfen.

Diese Benchmark-Suite ist als Benchmark gedacht, der jedem Benutzer mitteilt, welche Dinge in einer gegebenen SQL-Implementation gut performen und welche schlecht.

Beachten Sie, dass dieser Benchmark single-threaded ist. Daher misst er die minimale Zeit der Operationen. In Zukunft planen wir, auch etliche multi-threaded Test hinzuzufügen.

Beispiele (die auf derselben NT-4.0-Maschine liefen):

Im obigen Test lief MySQL mit einem 8 MB Index-Cache.

Weitere Benchmark-Ergebnisse haben wir unter http://www.mysql.com/information/benchmarks.html gesammelt.

Beachten Sie, dass Oracle nicht beinhaltet ist, weil sie gebeten haben, entfernt zu werden. Alle Oracle-Benchmarks müssen von Oracle freigegeben werden! Wir glauben, dass das die Aussagefähigkeit von Oracle-Benchmarks SEHR zweifelhaft erscheinen läßt, weil alle obigen Benchmarks dafür da sind zu zeigen, was eine Standard-Installation bei einem einzelnen Client machen kann.

Um eine Benchmark-Suite laufen zu lassen, müssen Sie eine MySQL-Quelldistribution herunter laden, den Perl-DBI-Treiber und den Perl-DBD-Treiber für die gewünschte Datenbank installieren und dann folgendes tun:

Wobei # einer der unterstützten Server ist. Sie erhalten eine Auflistung aller Optionen und unterstützten Server, indem Sie run-all-tests --help ausführen.

Crash-me versucht herauszufinden, welche Features eine Datenbank unterstützt und wo ihre Fähigkeiten und Einschränkungen sind, indem tatsächliche Anfragen ausgeführt werden. Beispielsweise stellt es fest:

• Welche Spaltentypen unterstützt werden.
• Wie viele Indexe unterstützt werden.
• Welche Funktionen unterstützt werden.
• Wie Groß eine Anfrage sein kann.
• Wie Groß eine VARCHAR-Spalte sein kann.

Sie finden die Ergebnisse von Crash-me für viele verschiedene Datenbanken unter http://www.mysql.com/information/crash-me.php.

6.1.5 Wie Sie Ihre eigenen Benchmarks benutzen

Sie sollten Ihre Applikation und Datenbank auf jeden Fall einem Benchmark-Test unterziehen um herauszufinden, wo Flaschenhälse sind. Indem Sie einen Flaschenhals beseitigen (oder ihn durch ein 'Dummy-Modul' ersetzen), können Sie leicht den nächsten Flaschenhals herausfinden (usw.). Selbst wenn die insgesamte Performance für Ihre Applikation ausreichend ist, sollten Sie zumindest einen Plan für jeden Flaschenhals aufstellen und entscheiden, auf welche Weise dieser beseitigt werden soll, wenn Sie eines Tages die zusätzliche Performance benötigen.

Als Beispiel für ein portables Benchmark-Programm schauen Sie sich die MySQL-Benchmark-Suite an. See section 6.1.4 Die MySQL-Benchmark-Suite. Sie können jedes Programm dieser Suite nehmen und es Ihren Bedürfnissen entsprechend abändern. Wenn Sie das tun, können Sie unterschiedliche Lösungen für Ihr Problem finden und testen, was bei Ihnen wirklich die schnellste Lösung ist.

Es ist häufig der Fall, dass Probleme nur dann auftreten, wenn das System unter schwerer Last läuft. Viele Kunden nahmen mit uns Kontakt auf, nachdem sie ein (getestetes) System in eine Produktionsumgebung stellten und Lastprobleme bekamen. Bei jedem dieser Fälle gab es bislang entweder Probleme mit dem Grund-Design (Tabellen-Scans laufen NICHT gut unter hoher Last) oder im Zusammenhang mit dem Betriebssystem / den Bibliotheken. Das meiste davon wäre SEHR viel einfacher zu beheben, wenn die Systeme nicht bereits in einer Produktionsumgebung liefen.

Um solcherlei Probleme zu vermeiden, sollten Sie einige Anstrengung darauf verwenden, Ihre gesamte Applikation unter der schlimmstmöglichen Last zu benchmarken! Hierfür können Sie Super Smack benutzen, das Sie hier erhalten: http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz. Wie der Name nahelegt, kann es Ihr System auf die Knie zwingen, wenn Sie das wollen. Achten Sie daher darauf, es nur auf Entwicklungssystemen zu verwenden.

6.2 SELECTs und andere Anfragen optimieren

Zunächst etwas, das alle Anfragen betrifft: Je komplexer das Berechtigungssystem, das Sie einrichten, desto mehr Overhead bekommen Sie.

Falls Sie noch keinerlei GRANT-Statements ausgeführt haben, optimiert MySQL die Berechtigungsprüfung zum Teil. Wenn Sie also sehr hohe Zugriffszahlen haben, kann es einen Zeitvorteil darstellen, Grants zu vermeiden. Ansonsten können mehr Berechtigungsprüfungen in einem größeren Overhead resultieren.

Wenn Sie Probleme bei einer bestimmten MySQL-Funktion haben, können Sie den Zeitbedarf jederzeit wie folgt mit dem MySQL-Client feststellen:

Das Ergebnis zeigt, dass MySQL 1.000.000 +-Operationen in 0,32 Sekunden auf einer PentiumII-400MHz-Maschine ausführen kann.

Alle MySQL-Funktionen sollten sehr optimiert sein, aber es mag einige Ausnahmen geben und benchmark(schleifenzaehler,ausdruck) ist ein großartiges Werkzeug, um herauszufinden, ob das das Problem bei Ihrer Anfrage darstellt.

6.2.1 EXPLAIN-Syntax (Informationen über ein SELECT erhalten)

EXPLAIN tabelle ist ein Synonym für DESCRIBE tabelle oder SHOW COLUMNS FROM tabelle.

Wenn Sie einem SELECT-Statement das Schlüsselwort EXPLAIN voran stellen, erklärt MySQL explains, wie er das SELECT ausführen würde, indem Informationen darüber gemacht werden, wie Tabellen verknüpft (Join) werden und in welcher Reihenfolge.

Mit der Hilfe von EXPLAIN können Sie erkennen, wo Sie Tabellen Indexe hinzufügen müssen, um ein schnelleres SELECT zu erhalten, das Indexe benutzt, um die Datensätze zu finden. Ausserdem sehen Sie, ob der Optimierer die Tabellen in optimaler Reihenfolge verknüpft. Um den Optimierer zu zwingen, eine spezielle Verknüpfungsreihenfolge bei einem SELECT-Statement einzuhalten, fügen Sie eine STRAIGHT_JOIN-Klausel hinzu.

Bei nicht einfachen Verknüpfungen (Joins) gibt EXPLAIN für jede Tabelle, die im SELECT-Statement benutzt wurde, eine Informationszeile zurück. Die Tabellen sind in der Reihenfolge aufgelistet, in der sie gelesen werden würden. MySQL löst alle Joins mit einer Single-Sweep-Multi-Join-Methode auf. Das bedeutet, dass MySQL eine Zeile aus der ersten Tabelle liest, dann die passende Zeile in der zweiten Tabelle sucht, dann in der dritten Tabelle usw. Wenn alle Tabellen verarbeitet wurden, gibt er die ausgewählten Spalten aus und geht rückwärts durch die Tabellenliste durch, bis eine Tabelle gefunden wird, bei der es weitere passende Zeilen gibt. Die nächste Zeile wird aus dieser Tabelle gelesen, und der Prozess fährt mit der nächsten Tabelle fort.

Die Ausgabe von EXPLAIN enthält folgende Spalten:

table

Die Tabelle, auf die sich die Ausgabezeile bezieht.

type

Der Join-Typ. Informationen über die verschiedenen Typen finden Sie weiter unten.

possible_keys

Die possible_keys-Spalte gibt an, welche Indexe MySQL verwenden konnte, um Zeilen in dieser Tabelle zu finden. Beachten Sie, dass diese Spalte völlig unabhängig von der Reihenfolge der Tabellen ist. Das heißt, dass einige der Schlüssel in possible_keys möglicherweise bei der tatsächlich verwendeten Tabellenreihenfolge nicht verwendbar sind. Wenn diese Spalte leer ist, gibt es keine relevanten Indexe. In diesem Fall können Sie die Performance Ihrer Anfrage womöglich verbessern, indem Sie die WHERE-Klausel untersuchen, um festzustellen, ob diese auf eine oder mehrere Spalten verweist, die zweckmäßigerweise indiziert werden sollten. Wenn das der Fall ist, erzeugen Sie einen entsprechenden Index und prüfen Sie die Anfrage noch einmal mit EXPLAIN. See section 7.5.4 ALTER TABLE-Syntax. Um zu sehen, welche Indexe eine Tabelle hat, benutzen Sie SHOW INDEX FROM tabelle.

key

Die key-Spalte gibt den Schlüssel an, den MySQL tatsächlich benutzen wird. Der Schlüssel ist NULL, wenn kein Index gewählt wurde. Wenn MySQL den falschen Index wählt, können Sie ihn wahrscheinlich zwingen, einen anderen Index zu nehmen, indem Sie myisamchk --analyze oder section 5.4.6.1 Aufrufsyntax von myisamchk ausführen oder USE INDEX/IGNORE INDEX benutzen. See section 7.4.1.1 JOIN-Syntax.

key_len

Die key_len-Spalte gibt die Länge des Schlüssels an, den MySQL benutzen wird. Die Länge ist NULL, wenn key NULL ist. Beachten Sie, dass Ihnen das angibt, wie viele Teile eines mehrteiligen Schlüssels MySQL tatsächlich benutzen wird.

ref

Die ref-Spalte zeigt an, welche Spalten oder Konstanten beim key benutzt werden, um Zeilen aus der Tabelle auszuwählen.

rows

die rows-Spalte gibt die Anzahl von Zeilen an, von denen MySQL annimmt, dass es sie untersuchen muss, um die Anfrage auszuführen.

Extra

Diese Spalte enthält zusätzliche Informationen darüber, wie MySQL die Anfrage auflösen wird. Folgende unterschiedliche Text-Zeichenketten können in dieser Spalte stehen:

Distinct

MySQL wird die Suche nach weiteren Zeilen für die aktuelle Zeilenkombination nicht fortsetzen, nachdem er die erste passende Zeile gefunden hat.

Not exists

MySQL war in der Lage, eine LEFT JOIN-Optimierung der Anfrage durchzuführen, und wird keine weiteren Spalten in dieser Tabelle für die vorherige Zeilenkombination mehr untersuchen, nachdem er eine Zeile gefunden hat, die den LEFT JOIN-Kriterien entspricht. Hier ist ein Beispiel dafür: SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; Angenommen, t2.id ist mit NOT NULL definiert. In diesem Fall scannt MySQL t1 und schlägt die Zeilen in t2 über t1.id nach. Wenn MySQL eine übereinstimmende Zeile in t2 findet, weiß er, dass t2.id nie NULL sein kann und scannt nicht durch den Rest der Zeilen in t2, die dieselbe id haben. Mit anderen Worten, für jede Zeile in t1 muss MySQL nur ein einziges Mal in t2 nachschlagen, unabhängig davon, wie viel übereinstimmende Zeilen es in t2 gibt.

range checked for each record (index map: #)

MySQL hat keinen gut geeigneten Index zum Benutzen gefunden. Statt dessen wird er für jede Zeilenkombination in der vorherigen Tabelle eine Prüfung vornehmen, welchen Index er benutzen soll (falls überhaupt) und diesen Index benutzen, um Zeilen aus der Tabelle abzurufen. Das ist nicht sehr schnell, aber immer noch schneller, als einen Join ohne Index durchzuführen.

Using filesort

MySQL braucht einen zusätzlichen Durchgang, um herauszufinden, wie die Zeilen in sortierter Reihenfolge abgerufen werden sollen. Die Sortierung wird durchgeführt, indem in Abhängigkeit vom join type durch alle Zeilen durchgegangen wird und der Sortierschlüssel und Zeiger auf die Zeilen für alle Zeilen gespeichert wird, die dem WHERE entsprechen. Danach werden die Schlüssel sortiert. Schließlich werden die Zeilen in sortierter Reihenfolge abgerufen.

Using index

Die Spalteninformation wird aus der Tabelle abgerufen, indem nur Informationen aus dem Index-Baum benutzt werden, ohne dass zum Suchen zusätzlich in den tatsächlichen Zeilen gelesen werden muss. Das kann gemacht werden, wenn alle benutzten Spalten der Tabelle Teil desselben Indexes sind.

Using temporary

Um die Anfrage aufzulösen muss MySQL eine temporäre Tabelle erzeugen, die das Ergebnis enthält. Das passiert typischerweise, wenn Sie ein ORDER BY auf eine andere Spalte setzen als auf die, die Sie im GROUP BY angegeben haben.

Where used

Eine WHERE-Klausel wird benutzt, um zu begrenzen, bei welchen Zeilen auf Übereinstimmung in der nächsten Tabelle gesucht wird oder welche Zeilen an den Client geschickt werden. Wenn Sie diese Information nicht haben und die Tabelle vom Typ ALL oder index ast, ist vielleicht in Ihrer Anfrage etwas falsch (falls Sie nicht vorhaben, alle Zeilen aus der Tabelle zu holen / zu untersuchen).

Wenn Sie wollen, dass Ihre Anfragen so schnell wie möglich laufen, sollten Sie auf Using filesort und Using temporary achten.

Die verschiedenen Join-Typen sind unten aufgeführt, sortiert vom besten zum schlechtesten Typ:

system

Die Tabelle hat nur eine Zeile (= Systemtabelle). Das ist ein spezieller Fall des const-Join-Typs.

const

Die Tabelle hat höchsten eine übereinstimmende Zeile, die am Anfang der Anfrage gelesen werden wird. Weil es nur eine Zeile gibt, können Spaltenwerte in dieser Zeile vom Optimierer als Konstanten betrachtet werden. const-Tabellen sind sehr schnell, weil sie nur einmal gelesen werden!

eq_ref

Aus dieser Tabelle wird für jede Zeilenkombination der vorherigen Tabellen eine Zeile gelesen. Das ist der bestmögliche Join-Typ, ausgenommen die const-Typen. Er wird benutzt, wenn alle Teile eines Indexes vom Join benutzt werden und der Index UNIQUE oder ein PRIMARY KEY ist.

ref

Alle Zeilen mit übereinstimmenden Index-Werten werden für jede Zeilenkombination der vorherigen Tabellen gelesen. ref wird benutzt, wenn der Join nur das am weitesten links stehende Präfix des Schlüssels benutzt, oder wenn der Schlüssel nicht UNIQUE oder ein PRIMARY KEY ist (mit anderen Worten, wenn der Join auf der Grundlage des Schlüsselwerts keine einzelne Zeile auswählen kann). Wenn der Schlüssel, der benutzt wird, nur mit einigen wenigen Zeilen übereinstimmt, ist dieser Join-Typ gut.

range

Nur Zeilen, die innerhalb eines angegebenen Bereichs sind, werden abrufen, wobei ein Index benutzt wird, um die Zeilen auszuwählen. Die key-Spalte gibt an, welcher Index benutzt wird. key_len enthält den längsten Schlüsselteil, der benutzt wurde. Die ref-Spalte ist für diesen Typ NULL.

index

Das ist dasselbe wie ALL, ausser dass nur der Index-Baum gescannt wird. Das ist üblicherweise schneller als ALL, weil die Index-Datei üblicherweise kleiner ist als die Daten-Datei.

ALL

Für jede Zeilenkombination der vorherigen Tabellen wird ein kompletter Tabellenscan durchgeführt. Das ist normalerweise nicht gut, wenn die Tabelle die erste Tabelle ist, die nicht als const gekennzeichnet ist, und üblicherweise sehr schlecht in allen anderen Fällen. Sie können ALL normalerweise vermeiden, indem Sie mehr Indexe hinzufügen, so dass die Zeile auf der Grundlage der Konstanten-Werte oder Spaltenwerte von früheren Tabellen abgerufen werden kann.

Sie erhalten einen guten Anhaltspunkt, wie gut ein Join ist, wenn Sie alle Werte in der rows-Spalte der EXPLAIN-Ausgabe multiplizieren. Das sollte grob aussagen, wie vielen Zeilen MySQL untersuchen muss, um die Anfrage auszuführen. Diese Anzahl wird auch benutzt, wenn Sie Anfragen mit der max_join_size-Variablen begrenzen. See section 6.5.2 Serverparameter tunen.

Das folgende Beispiel zeigt, wie ein JOIN progressiv optimiert werden kann, indem die Informationen genutzt werden, die EXPLAIN bereit stellt.

Angenommen, Sie haben unten stehendes SELECT-Statement, das Sie mit EXPLAIN untersuchen:

Nehmen wir bei diesem Beispiel folgendes an:

• Die Spalten, die verglichen werden, wurden wie folgt deklariert:

  Tabelle	Spalte	Spaltentyp
  tt	ActualPC	CHAR(10)
  tt	AssignedPC	CHAR(10)
  tt	ClientID	CHAR(10)
  et	EMPLOYID	CHAR(15)
  do	CUSTNMBR	CHAR(15)

• Die Tabellen haben die unten stehenden Indexe:

  Tabelle	Index
  tt	ActualPC
  tt	AssignedPC
  tt	ClientID
  et	EMPLOYID (primary key)
  do	CUSTNMBR (primary key)

• Die tt.ActualPC-Werte sind nicht gleichmäßig verteilt.

Anfangs, bevor die Optimierung durchgeführt wurde, ergab das EXPLAIN-Statement folgende Informationen:

Weil type bei jeder Tabelle ALL ist, zeigt die Ausgabe, dass MySQL eine komplette Verknüpfung (Full Join) aller Tabellen durchführt! Das dauert recht lange, weil das Produkt der Zeilenanzahl in jeder Tabelle untersucht werden muss! In diesem Fall ist das 74 * 2.135 * 74 * 3.872 = 45.268.558.720 Zeilen. Wenn die Tabellen größer wären, können Sie sich vorstellen, wie lange das dauern würde.

Ein Problem liegt hier darin, dass MySQL (noch) keine Indexe auf Spalten effizient benutzen kann, wenn sie unterschiedlich deklariert sind. In diesem Zusammenhang sind VARCHAR und CHAR dasselbe, es sei denn, sie sind mit unterschiedlichen Längen deklariert. Weil tt.ActualPC als CHAR(10) und et.EMPLOYID als CHAR(15) deklariert ist, gibt eine Unstimmigkeit der Längen.

Um diese Ungleichheit der Spaltenlängen zu beheben, benutzen Sie ALTER TABLE, um ActualPC von 10 auf 15 Zeichen zu verlängern:

Jetzt sind tt.ActualPC und et.EMPLOYID beide VARCHAR(15). Eine erneute Ausführung des EXPLAIN-Statements ergibt dieses Ergebnis:

Das ist nicht perfekt, aber viel besser (das Produkt der rows-Werte ist jetzt um einen Faktor 74 niedriger). Diese Version wird innerhalb von ein paar Sekunden ausgeführt.

Eine zweite Änderung kann durchgeführt werden, um die Unstimmigkeit der Spaltenlängen für die tt.AssignedPC = et_1.EMPLOYID- und tt.ClientID = do.CUSTNMBR-Vergleiche zu beheben:

Jetzt ergibt EXPLAIN folgende Ausgabe:

Das ist fast so gut, wie es überhaupt geht.

Das verbleibende Problem besteht darin, dass MySQL vorgabemäßig annimmt, dass die Werte in der tt.ActualPC-Spalte gleichmäßig verteilt sind, was in der tt-Tabelle nicht der Fall ist. Glücklicherweise ist es einfach, MySQL darüber zu informieren:

Jetzt ist der Join perfekt und EXPLAIN ergibt dieses Ergebnis:

Beachten Sie, dass die rows-Spalte in der Ausgabe von EXPLAIN eine gehobene Form von Vermutung des MySQL-Join-Optimierers ist. Um eine Anfrage zu optimieren, sollten Sie überprüfen, ob diese Zahlen der Wahrheit nahe kommen. Wenn nicht, erhalten Sie eventuell bessere Performance, wenn Sie STRAIGHT_JOIN in Ihrem SELECT-Statement benutzen und versuchen, die Tabellen in der FROM-Klausel in anderer Reihenfolge anzugeben.

6.2.2 Anfragen-Performance abschätzen

In den meisten Fällen können Sie die Performance schätzen, indem Sie Suchvorgänge auf Festplatte zählen. Bei kleinen Tabellen können Sie die Zeile üblicherweise mit 1 Festplatten-Suchvorgang finden (weil der Index wahrscheinlich im Cache ist). Bei größeren Tabellen können Sie schätzen, dass Sie (bei der Benutzung von B++-Baum-Indexen) brauchen werden: log(zeilen_zahl) / log(index_block_laenge / 3 * 2 / (index_laenge + daten_zeiger_laenge)) + 1 Suchvorgänge, um die Zeile zu finden.

In MySQL ist ein Index-Block üblicherweise 1024 Bytes lang und der Daten-Zeiger üblicherweise 4 Bytes. Eine 500.000-Zeilen-Tabelle mit einer Indexlänge von 3 (medium integer) ergibt: log(500.000)/log(1024/3*2/(3+4)) + 1 = 4 Suchvorgänge.

Da der obige Index etwa 500.000 * 7 * 3/2 = 5,2 MB benötigen würde (angenommen, dass die Index-Puffer zu 2/3 gefüllt sind, was ein typischer Wert sit), haben Sie wahrscheinlich viel vom Index im Arbeitsspeicher und werden wahrscheinlich nur 1 bis 2 Betriebssystem-Aufrufe benötigen, um Daten zu lesen, um die Zeile zu finden.

Bei Schreibvorgängen brauchen Sie jedoch 4 Suchanfragen (wie oben), um herauszufinden, wo der neue Index platziert wird, und normalerweise 2 Suchvorgänge, um den Index zu aktualisieren und die Zeile zu schreiben.

Beachten Sie, dass oben Gesagtes nicht bedeutet, dass Ihre Applikation allmählich mit N log N verfällt! Solange alles durch das Betriebssystem oder den SQL-Server gecachet wird, werden die Dinge nur marginal langsamer, wenn die Tabellen größer werden. Wenn die Daten zu Groß werden, um gecachet zu werden, werden die Dinge anfangen, viel langsamer zu laufen, bis Ihre Applikation schließlich komplett durch Suchvorgänge auf Festplatte ausgebremst wird (die mit N log N zunehmen). Um das zu vermeiden, vergrößern Sie den Index-Cache, wenn die Daten wachsen. See section 6.5.2 Serverparameter tunen.

6.2.3 Geschwindigkeit von SELECT-Anfragen

Wenn Sie ein langsames SELECT ... WHERE schneller machen wollen, ist im Allgemeinen das erste, was zu prüfen ist, ob Sie einen Index hinzufügen können oder nicht. See section 6.4.3 Wie MySQL Indexe benutzt. Alle Verweise (Reference) zwischen verschiedenen Tabellen sollten üblicherweise mit Indexen gemacht werden. Sie können den EXPLAIN-Befehl benutzen, um herauszufinden, welche Indexe für ein SELECT benutzt werden. See section 6.2.1 EXPLAIN-Syntax (Informationen über ein SELECT erhalten).

Einige allgemeine Tipps:

• Um MySQL zu helfen, Anfragen besser zu optimieren, lassen Sie myisamchk --analyze auf eine Tabelle laufen, nachdem sie mit relevanten Daten gefüllt wurde. Das aktualisiert einen Wert für jeden Index-Teil, der die durchschnittliche Anzahl von Zeilen angibt, die denselben Wert haben. (Bei eindeutigen Indexen ist das natürlich immer 1). MySQL benutzt diesen Wert, um zu entscheiden, welcher Index benutzt werden soll, wenn Sie zwei Tabellen mit einem 'nicht konstanten Ausdruck' verbinden. Sie können das Ergebnis nach dem Laufenlassen von analyze mit SHOW INDEX FROM tabelle überprüfen und die Cardinality-Spalte untersuchen.
• Um einen Index und Daten gemäß einem Index zu sortieren, benutzen Sie myisamchk --sort-index --sort-records=1 (wenn Sie nach Index 1 sortieren wollen). Wenn Sie einen eindeutigen Index haben, von dem Sie alle Datensätze gemäß der Reihenfolge dieses Indexes lesen wollen, ist das eine gute Art, das schneller zu machen. Beachten Sie jedoch, dieses Sortieren nicht optimal geschrieben wird und bei einer großen Tabelle lange dauert!

6.2.4 Wie MySQL WHERE-Klauseln optimiert

Die WHERE-Optimierungen wurden hier in den SELECT-Teil aufgenommen, weil sie meist in Verbindung mit SELECT benutzt werden, aber dieselben Optimierungen treffen für WHERE bei DELETE- und UPDATE-Statements zu.

Beachten Sie auch, dass dieser Abschnitt nicht vollständig ist. MySQL führt viele Optimierungen durch und wir hatten noch keine Zeit, alle davon zu dokumentieren.

Einige der Optimierungen, die MySQL durchführt, sind unten aufgeführt:

• Entfernung unnötiger Klammern: ((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d)
• Konstanten-'Falten' (Folding): (a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
• Bedingungsentfernung bei Konstanten (notwendig wegen Konstanten-'Falten'): (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
• Konstante Ausdrücke, die von Indexen benutzt werden, werden nur einmal ausgewertet.
• COUNT(*) auf eine einzelne Tabelle ohne ein WHERE wird direkt aus der Tabelleninformation abgerufen. Das wird auch bei jeglichen NOT NULL-Ausdrücken gemacht, wenn diese nur für eine Tabelle benutzt werden.
• Früherkennung ungültiger Konstanten-Ausdrücke. MySQL stellt schnell fest, dass einige SELECT-Statements unmöglich sind, und gibt keine Zeilen zurück.
• HAVING wird mit WHERE vereinigt, wenn Sie GROUP BY oder Gruppen-Funktionen (COUNT(), MIN() usw.) nicht benutzen.
• Für jeden Sub-Join wird ein einfacheres WHERE konstruiert, um eine schnelle WHERE-Evaluierung für jeden Sub-Join zu erzielen, und auch, um Datensätze so bald wie möglich überspringen zu können.
• Alle Konstanten-Tabellen werden zuerst gelesen, vor jeder anderen Tabelle in der Anfrage. Eine Konstanten-Tabelle ist:
  • Eine leere Tabelle oder eine Tabelle mit 1 Zeile.
  • Eine Tabelle, die bei einer WHERE-Klausel auf einen UNIQUE-Index oder einen PRIMARY KEY benutzt wird, wobei alle Index-Teile mit konstanten Ausdrücken benutzt werden und die Index-Teile als NOT NULL definiert sind.
  Alle folgenden Tabellen werden als Konstanten-Tabellen benutzt: mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
• Die beste Join-Kombination, um Tabellen zu verknüpfen, wird gefunden, wenn man alle Möglichkeiten probiert. Wenn alle Spalten in ORDER BY und in GROUP BY aus derselben Tabelle stammen, wird diese Tabelle vorzugsweise vorn hingestellt, wenn verknüpft wird.
• Wenn es eine ORDER BY-Klausel und eine andere GROUP BY-Klausel gibt, oder wenn ORDER BY oder GROUP BY Spalten aus Tabellen enthält, die nicht aus der ersten Tabelle in der Join-Reihe stammen, wird eine temporäre Tabelle erzeugt.
• Wenn Sie SQL_SMALL_RESULT benutzen, benutzt MySQL eine temporäre Tabelle im Arbeitsspeicher.
• Jeder Tabellen-Index wird abgefragt und der beste Index, der weniger als 30% der Zeilen überspannt, wird benutzt. Wenn ein solcher Index nicht gefunden werden kann, wird ein schneller Tabellenscan benutzt.
• In einigen Fällen kann MySQL Zeilen vom Index lesen, ohne überhaupt in der Daten-Datei nachzuschlagen. Wenn alle Spalten, die vom Index benutzt werden, numerisch sind, wird nur der Index-Baum benutzt, um die Anfrage aufzulösen.
• Bevor jeder Datensatz herausgegeben wird, werden die, die nicht mit der HAVING-Klausel übereinstimmen, übersprungen.

Einige Beispiele von Anfragen, die sehr schnell sind:

Die folgenden Anfragen werden aufgelöst, indem nur der Index-Baum benutzt wird (unter der Annahme, dass die indizierten Spalten numerisch sind):

Die folgenden Anfragen benutzen Indexierung, um die Zeilen in sortierter Reihenfolge abzufragen, ohne einen separaten Sortierdurchgang:

6.2.5 Wie MySQL DISTINCT optimiert

DISTINCT wird für alle Spalten in GROUP BY umgewandelt, DISTINCT in Kombination mit ORDER BY benötigt in vielen Fällen ebenfalls eine temporäre Tabelle.

Wenn LIMIT # mit DISTINCT kombiniert wird, hält MySQL an, sobald er # eindeutige Zeilen findet.

Wenn Sie nicht Spalten aus allen benutzten Tabellen verwenden, hält MySQL mit dem Scannen der nicht benutzten Tabellen an, sobald er die erste Übereinstimmung gefunden hat.

Im Beispiel wird angenommen, dass t1 vor t2 benutzt wird (überprüfen Sie das mit EXPLAIN). In diesem Fall hört MySQL auf, von t2 zu lesen (für diese bestimmte Zeile in t1), sobald die erste Zeile in t2 gefunden wurde.

6.2.6 Wie MySQL LEFT JOIN optimiert

A LEFT JOIN B ist in MySQL wie folgt implementiert:

• Die Tabelle B wird als abhängig von Tabelle A und allen Tabellen, von denen A abhängig ist, gesetzt.
• Die Tabelle A wird als abhängig von allen Tabellen (ausser B) gesetzt, die in der LEFT JOIN-Bedingung aufgeführt sind.
• Alle LEFT JOIN-Bedingungen werden zu WHERE-Klausel verschoben.
• Alle Standard-Join-Optimierungen werden durchgeführt, mit der Ausnahme, dass eine Tabelle immer nach allen Tabellen gelesen wird, von denen sie abhängig ist. Wenn es eine zirkuläre Abhängigkeit gibt, gibt MySQL einen Fehler aus.
• Alle Standard-WHERE-Optimierungen werden durchgeführt.
• Wenn es eine Zeile in A gibt, die mit der WHERE-Klausel übereinstimmt, aber keine Zeile in B, die mit der LEFT JOIN-Bedingung übereinstimmt, wird eine zusätzliche Zeile für B erzeugt, deren Spalten alle auf NULL gesetzt sind.
• Wenn Sie LEFT JOIN benutzen, um Zeilen zu finden, die in einer Tabelle nicht existieren, und Sie folgendes im WHERE-Teil angeben: spalten_name IS NULL, wobei spalten_name eine Spalte ist, die als NOT NULL deklariert ist, hört MySQL mit der Suche nach weiteren Zeilen auf (für eine bestimmte Schlüsselkombination), nachdem er eine Zeile gefunden hat, die mit der LEFT JOIN-Bedingung übereinstimmt.

RIGHT JOIN ist analog zu LEFT JOIN implementiert.

Die Lese-Reihenfolge der Tabellen, die von LEFT JOIN und STRAIGHT JOIN erzwungen wird, hilft dem Optimierer (der berechnet, in welcher Reihenfolge die Tabellen verknüpft werden sollen), seine Arbeit schneller durchzuführen, weil weniger Tabellenvertauschungen überprüft werden müssen.

Beachten Sie, dass das oben Gesagte bedeutet, dass bei einer Anfrage des folgenden Typs:

MySQL einen kompletten Scan von b durchführen wird, weil der LEFT JOIN erzwingt, dass diese vor d gelesen wird.

Das läßt sich in diesem Fall beheben, indem die Anfrage wie folgt geändert wird:

6.2.7 Wie MySQL LIMIT optimiert

In einigen Fällen handhabt MySQL die Anfrage unterschiedlich, wenn Sie LIMIT # statt HAVING benutzen:

• Wenn Sie nur einige wenige Zeilen mit LIMIT auswählen, benutzt MySQL in einigen Fällen Indexe, wenn er ansonsten vorzugsweise einen vollständigen Tabellenscan durchführen würde.
• Wenn Sie LIMIT # mit ORDER BY benutzen, beendet MySQL das Sortieren, sobald er die ersten # Zeilen gefunden hat, anstatt die gesamte Tabelle zu sortieren.
• Wenn Sie LIMIT # mit DISTINCT kombinieren, hört MySQL auf, sobald er # eindeutige Zeilen gefunden hat.
• In einigen Fällen kann GROUP BY aufgelöst werden, indem der Schlüssel in der Reihenfolge gelesen wird (oder der Schlüssel sortiert wird) und danach Zusammenfassungen berechnet werden, bis sich der Schlüsselwert ändert. In diesem Fall berechnet LIMIT # keine unnötigen GROUP BY's.
• Sobald MySQL die ersten # Zeilen an den Client geschickt hat, wird die Anfrage abgebrochen.
• LIMIT 0 gibt immer schnell eine leere Ergebnismenge (empty set) zurück. Das ist nützlich, um die Anfrage zu überprüfen und die Spaltentypen der Ergebnisspalten zu erhalten.
• Die Größe der temporären Tabellen benutzt LIMIT #, um zu berechnen, wieviel Platz benötigt wird, um die Anfrage aufzulösen.

6.2.8 Geschwindigkeit von INSERT-Anfragen

Die Zeit, einen Datensatz einzufügen, besteht ungefähr aus:

• Verbindung: (3)
• Anfrage an den Server schicken: (2)
• Anfrage parsen: (2)
• Datensatz einfügen: (1 x Größe des Datensatzes)
• Indexe einfügen: (1 x Anzahl der Indexe)
• Schließen: (1)

Wobei die Zahlen in etwa proportional zur Gesamtzeit sind. Diese Berechnung zieht den anfänglichen Overhead, um Tabellen zu öffnen, nicht in Betracht (was einmal für jede gleichzeitig laufende Anfrage gemacht wird).

Die Größe der Tabelle verlangsamt das Einfügen von Indexen um N log N (B-Bäume).

Einige Möglichkeiten, die Geschwindigkeit von Einfügeoperationen zu steigern:

• Wenn Sie viele Zeilen vom selben Client aus zur gleichen Zeit einfügen, benutzen Sie mehrfache Werte (Liste) im INSERT-Statements. Das geht viel schneller (in manchen Fälle um Faktoren) als separate INSERT-Statements zu benutzen. Tunen Sie die myisam_bulk_insert_tree_size-Variable, um das sogar noch zu beschleunigen. See section 5.5.5.4 SHOW VARIABLES.
• Wenn Sie viele Zeilen von unterschiedlichen Clients aus einfügen, können Sie mehr Geschwindigkeit erzielen, wenn Sie das INSERT DELAYED-Statement benutzen. See section 7.4.3 HANDLER-Syntax.
• Beachten Sie, dass Sie mit MyISAM-Tabellen Zeilen zur selben Zeit einfügen können, zu der SELECTs laufen, wenn es keine gelöschten Zeilen in den Tabellen gibt.
• Wenn Daten in eine Tabelle aus einer Textdatei eingeladen werden, benutzen Sie LOAD DATA INFILE. Das ist üblicherweise 20 mal schneller als viele INSERT-Statements zu benutzen. See section 7.4.9 LOAD DATA INFILE-Syntax.
• Mit etwas zusätzlicher Mühe ist es möglich, LOAD DATA INFILE noch schneller laufen zu lassen, wenn die Tabelle viele Indexe hat. Gehen Sie wie folgt vor:
  1. Optional erzeugen Sie die Tabelle mit CREATE TABLE, zum Beispiel mit mysql oder über die Perl-DBI.
  2. Führen Sie ein FLUSH TABLES-Statement oder den Shell-Befehl mysqladmin flush-tables aus.
  3. Geben Sie myisamchk --keys-used=0 -rq /pfad/zu/db/tabelle ein. Dadurch entfernen Sie die Benutzung aller Indexe von der Tabelle.
  4. Fügen Sie Daten in die Tabelle mit LOAD DATA INFILE ein. Dadurch werden keine Indexe aktualisiert, was deswegen sehr schnell läuft.
  5. Wenn Sie in Zukunft nur noch aus der Tabelle lesen, benutzen Sie myisampack, um sie kleiner zu machen. See section 8.1.2.3 Kennzeichen komprimierter Tabellen.
  6. Erzeugen Sie die Indexe mit myisamchk -r -q /pfad/zu/db/tabelle neu. Hierdurch wird der Index-Baum im Speicher erzeugt, bevor er auf die Platte geschrieben wird, was viel schneller ist, weil viele Suchvorgänge auf Platte vermieden werden. Der sich ergebende Index-Baum ist ausserdem perfekt ausbalanciert.
  7. Führen Sie ein FLUSH TABLES-Statement oder den Shell-Befehl mysqladmin flush-tables aus.
  Diese Prozedur wird in Zukunft in LOAD DATA INFILE eingebaut werden. Ab MySQL 4.0 können Sie auch ALTER TABLE tabelle DISABLE KEYS anstelle von myisamchk --keys-used=0 -rq /pfad/zu/db/tabelle und ALTER TABLE tabelle ENABLE KEYS anstelle von myisamchk -r -q /pfad/zu/db/tabelle benutzen. Damit können Sie auch die FLUSH TABLES-Schritte überspringen.
• Sie können die Einfügegeschwindigkeit steigern, indem Sie Tabellen sperren: mysql> LOCK TABLES a WRITE; mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33); mysql> INSERT INTO a VALUES (8,26),(6,29); mysql> UNLOCK TABLES; Der hauptsächliche Geschwindigkeitsunterschied liegt darin, dass der Index-Puffer nur einmal auf Platte zurück geschrieben wird, nachdem alle INSERT-Statements fertig sind. Normalerweise würden die Index-Puffer so oft zurück geschrieben wie es INSERT-Statements gibt. Das Sperren wird nicht benötigt, wenn Sie alle Zeilen mit einem einzigen Statement einfügen können. Durch das Sperren wird auch die Gesamtzeit von Tests auf mehrere Verbindungen gesenkt, aber die maximale Wartezeit für einige Threads wird erhöht (weil sie auf Sperren warten). Beispiel: Thread 1 führt 1000 Einfügevorgänge durch. Thread 2, 3 und 4 fügen 1 Einfügevorgang durch. Thread 5 führt 1000 Einfügevorgänge durch. Wenn Sie kein Sperren benutzen, sind die Threads 2, 3 und 4 vor 1 und 5 fertig. Wenn Sie Sperren benutzen, sind 2, 3 und 4 wahrscheinlich nicht vor 1 oder 5 fertig, aber die Gesamtzeit sollte etwa 40% geringer sein. Weil INSERT-, UPDATE- und DELETE-Operationen in MySQL sehr schnell sind, erhalten Sie bessere Performance über alles, wenn Sie um alles herum Sperren hinzufügen, was mehr als etwa 5 Einfügeoperationen oder Aktualisierungen (Updates) in einer Zeile durchführt. Wenn Sie sehr viele Einfügeoperationen in einer Zeile durchführen, können Sie ein LOCK TABLES machen, gefolgt von einem gelegentlichen UNLOCK TABLES (etwa alle 1.000 Zeilen), um anderen Threads zu gestatten, auf die Tabelle zuzugreifen. Das Ergebnis wäre ebenfalls ein netter Geschwindigkeitsgewinn. Natürlich ist LOAD DATA INFILE zum Einladen von Daten viel schneller.

Um sowohl für LOAD DATA INFILE als auch für INSERT mehr Geschwindigkeit zu erzielen, vergrößern Sie den Schlüssel-Puffer. See section 6.5.2 Serverparameter tunen.

6.2.9 Geschwindigkeit von UPDATE-Anfragen

Update-Anfragen werden wie eine SELECT-Anfrage optimiert, mit dem zusätzlichen Overhead eines Schreibvorgangs. Die Geschwindigkeit des Schreibvorgangs hängt von der Größe der Daten und von der Anzahl der Indexe, die aktualisiert werden, ab. Indexe, die nicht geändert werden, werden nicht aktualisiert.

Eine weitere Möglichkeit, Aktualisierungen (Updates) schnell zu machen, ist, sie zu verzögern und dann später viele Aktualisierungen hintereinander zu machen. Viele Aktualisierungen hintereinander sind viel schneller als nur eine zugleich, wenn Sie die Tabelle sperren.

Beachten Sie, dass die Aktualisierung eines Datensatzes bei dynamischem Datensatzformat dazu führen kann, dass der Datensatz aufgespalten wird. Wenn Sie das oft durchführen, ist es daher sehr wichtig, gelegentlich OPTIMIZE TABLE auszuführen. See section 5.5.1 OPTIMIZE TABLE-Syntax.

6.2.10 Geschwindigkeit von DELETE-Anfragen

Wenn Sie alle Zeilen in der Tabelle löschen wollen, sollten Sie TRUNCATE TABLE tabelle benutzen. See section 7.4.7 TRUNCATE-Syntax.

Die Zeit, die für das Löschen eines Datensatzes benötigt wird, ist exakt proportional zur Anzahl der Indexe. Um Datensätze schneller zu löschen, können Sie die Größe des Index-Caches herauf setzen. See section 6.5.2 Serverparameter tunen.

6.2.11 Weitere Optimierungstipps

Ungeordnete Liste von Tipps für schnellere Systeme:

• 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 section 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 section 6.2.1 EXPLAIN-Syntax (Informationen über ein SELECT erhalten).
• 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 section 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 section 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 section 5.5.1 OPTIMIZE TABLE-Syntax.
• Benutzen Sie - wenn möglich - HEAP-Tabellen, um mehr Geschwindigkeit zu erzielen. See section 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 section 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.

6.3 Sperren (Locking)

6.3.1 Wie MySQL Tabellen sperrt

Im Anhang finden Sie eine Erörterung zu den unterschiedlichen Sperrmethoden. See section E.4 Sperrmethoden.

Jedes Sperren in MySQL ist blockierungsfrei. Das wird erreicht, indem alle Sperren zugleich am Anfang einer Anfrage angefordert werden, und indem Tabellen immer in derselben Reihenfolge gesperrt werden.

The Sperrmethode, die MySQL für WRITE-Sperren benutzt, funktioniert wie folgt:

• Falls es keine Sperren auf die Tabelle gibt, wird eine Schreibsperre gemacht.
• Ansonsten wird die Sperranforderung in die Schreibsperren-Warteschlange eingereiht.

Die Sperrmethode, die MySQL für READSperren benutzt, funktioniert wie folgt:

• Falls es keine Schreibsperren auf die Tabelle gibt, wird eine Lesesperre gemacht.
• Ansonsten wird die Sperranforderung in die Lesesperren-Warteschlange eingereiht.

Wenn eine Sperre aufgehoben wird, wird die Sperren den Threads in der Schreibsperren-Warteschlange verfügbar gemacht, danach den Threads in der Lesesperren-Warteschlange.

Das bedeutet, wenn Sie viele Aktualisierungen auf eine Tabelle haben, warten SELECT-Statements, bis es keine Aktualisierungen mehr gibt.

Um das für den Fall zu umgehen, dass es viele INSERT- und-SELECT-Operationen auf eine Tabelle gibt, können Sie Zeilen in eine temporäre Tabelle einfügen und die echte Tabelle gelegentlich aus den Daten der temporäre Tabelle aktualisieren.

Das machen Sie wie folgt: mysql> LOCK TABLES echte_tabelle WRITE, einfuege_tabelle WRITE; mysql> insert into echte_tabelle select * von einfuege_tabelle; mysql> TRUNCATE TABLE einfuege_tabelle; mysql> UNLOCK TABLES;

Sie können bei INSERT, UPDATE oder DELETE die LOW_PRIORITY-Option oder bei SELECT die HIGH_PRIORITY-Option benutzen, wenn Sie dem Abruf von Daten in bestimmten Fällen Priorität einräumen wollen. Sie können auch mysqld mit --low-priority-updates starten, um dasselbe Verhalten zu erreichen.

Die Benutzung von SQL_BUFFER_RESULT kann ebenfalls helfen, Tabellensperren kürzer zu machen. See section 7.4.1 SELECT-Syntax.

Sie können auch den Sperr-Code in `mysys/thr_lock.c' ändern, um eine einzige Warteschlagen zu benutzen. In diesem Fall haben Schreibsperren und Lesesperren dieselbe Priorität, was bei einigen Applikationen eventuell hilfreich ist.

6.3.2 Themen, die Tabellensperren betreffen

Der Tabellensperren-Code in MySQL ist blockierungsfrei.

MySQL benutzt Tabellensperren (anstelle von Zeilensperren oder Spaltensperren) für alle Tabellentypen ausser BDB-Tabellen, um eine sehr hohe Sperrgeschwindigkeit zu erzielen. Bei großen Tabellen ist Tabellensperren bei den meisten Applikationen VIEL besser als Zeilensperren, aber es gibt natürlich ein paar Fallstricke.

Bei BDB- und InnoDB-Tabellen benutzt MySQL Tabellensperren, wenn Sie die Tabelle explizit mit LOCK TABLES sperren oder einen Befehl ausführen, der jede Zeile in der Tabelle ändern wird, wie ALTER TABLE. Bei diesen Tabellentypen empfehlen wir, LOCK TABLES überhaupt nicht zu benutzen.

Ab MySQL-Version 3.23.7 können Sie Zeilen in MyISAM-Tabellen zur gleichen Zeit einfügen, während andere Threads aus der Tabelle lesen. Beachten Sie, dass das momentan nur funktioniert, wenn es zu der Zeit, zu der das Einfügen vorgenommen wird, keine durch gelöschte Zeilen verursachte Löcher in der Tabelle gibt. Wenn alle Löcher mit neuen Daten gefüllt wurden, werden gleichzeitige Einfügevorgänge automatisch wieder aktiviert.

Tabellensperren ermöglicht, dass viele Threads gleichzeitig aus einer Tabelle lesen, aber bevor ein Thread in die Tabelle schreiben kann, muss er zunächst exklusiven Zugriff erhalten. Während der Aktualisierung müssen andere Threads, die auf diese Tabelle zugreifen wollen, warten, bis die Aktualisierung fertig ist.

Weil Aktualisierung von Tabellen normalerweise als wichtiger erachtet werden als SELECT, erhalten alle Statements, die eine Tabelle aktualisieren, eine höhere Priorität als Statements, die Informationen aus der Tabelle abrufen. Das sollte sicherstellen, dass Aktualisierungen nicht 'verhungern', wenn viele große Anfragen auf eine bestimmte Tabelle durchgeführt werden. (Sie können das ändern, indem Sie bei dem Statement, dass die Aktualisierung durchführt, LOW_PRIORITY verwenden, oder beim SELECT-Statement HIGH_PRIORITY.)

Ab MySQL-Version 3.23.7 können Sie die max_write_lock_count-Variable benutzen, um MySQL zu zwingen, temporär allen SELECT-Statements, die auf eine Tabelle warten, nach einer bestimmten Anzahl von Einfügevorgängen auf eine Tabelle höhere Priorität einzuräumen.

Tabellensperren ist jedoch bei folgendem Szenario nicht sehr gut:

• Ein Client führt ein SELECT aus, das lange Zeit läuft.
• Ein anderer Client führt danach ein UPDATE auf die benutzte Tabelle aus. Dieser Client wartet, bis das SELECT fertig ist.
• Ein weiterer Client führt ein weiteres SELECT-Statement auf dieselbe Tabelle aus. Weil UPDATE höhere Priorität als SELECT hat, wartet dieses SELECT, bis das UPDATE fertig ist. Es wartet auch darauf, dass das erste SELECT fertig ist!
• Ein Thread wartet bei etwas wie Platte voll. In diesem Fall warten alle anderen Threads, die auf die problemverursachende Tabelle zugreifen wollen, bis mehr Speicher verfügbar gemacht wurde.

Mögliche Lösungen dieses Problems sind:

• Versuchen Sie, SELECT-Statements schneller ablaufen zu lassen. Hierfür müssen Sie eventuell Zusammenfassungstabellen erzeugen.
• Starten Sie mysqld mit --low-priority-updates. Das gibt allen Statements, die eine Tabelle aktualisieren (ändern), geringere Priorität als einem SELECT-Statement. Im vorstehenden Szenario würde das SELECT-Statement vor dem INSERT-Statement ausgeführt werden.
• Sie können auch einem bestimmten INSERT-, UPDATE- oder DELETE-Statement mit dem LOW_PRIORITY-Attribut geringere Priorität geben.
• Starten Sie mysqld mit einem niedrigen Wert für max_write_lock_count, um READ-Sperren nach einer bestimmten Anzahl von WRITE-Sperren zu ermöglichen.
• Sie können festlegen, dass alle Aktualisierungen von einem bestimmten Thread mit niedriger Priorität ausgeführt werden, indem Sie den SQL-Befehl SET SQL_LOW_PRIORITY_UPDATES=1 benutzen. See section 6.5.6 SET-Syntax.
• Sie können mit dem HIGH_PRIORITY-Attribut festlegen, dass ein bestimmtes SELECT sehr wichtig ist. See section 7.4.1 SELECT-Syntax.
• Wenn Sie Probleme mit INSERT in Kombination mit SELECT haben, stellen Sie auf die neuen MyISAM-Tabellen um, weil diese gleichzeitige SELECTs und INSERTs unterstützen.
• Wenn Sie hauptsächlich INSERT- und SELECT-Statements mischen, wird das DELAYED-Attribut für INSERT wahrscheinlich Ihre Probleme lösen. See section 7.4.3 HANDLER-Syntax.
• Wenn Sie Probleme mit SELECT und DELETE haben, mag die LIMIT-Option für DELETE helfen. See section 7.4.6 DELETE-Syntax.

6.4 Optimierung der Datenbank-Struktur

6.4.1 MySQL-Datenbank-Design-Überlegungen

MySQL speichert Zeilendaten und Indexdaten in separaten Dateien. Viele (fast alle) anderen Datenbanken vermischen Zeilen- und Indexdaten in derselben Datei. Wir glauben, dass die Wahl, die MySQL getroffen hat, für einen sehr weiten Bereich moderner Systeme besser ist.

Eine weitere Möglichkeit, Zeilendaten zu speichern, besteht darin, die Information für jede Spalten in einem separaten Bereich zu halten (Beispiele sind SDBM und Focus). Das verursacht Performance-Einbussen für jede Anfrage, die auf mehr als eine Spalte zugreift. Weil das so schnell schlechter wird, wenn auf mehr als eine Spalte zugegriffen wird, glauben wir, dass dieses Modell für Mehrzweck-Datenbanken nicht gut ist.

Der häufigere Fall ist, dass Index und Daten zusammen gespeichert sind (wie bei Oracle, Sybase usw.). In diesem Fall befindet sich die Zeileninformation auf der Leaf-Page des Indexes. Das Gute daran ist, dass man sich damit - abhängig davon, wie gut der Index gecachet ist - einen Festplatten-Lesezugriff spart. Das Schlechte an diesem Layout sind folgende Dinge:

• Tabellenscannen geht viel langsamer, weil man durch alle Indexe lesen muss, um an die Daten zu kommen.
• Man kann nicht nur die Index-Tabelle benutzen, um Daten einer Anfrage abzurufen.
• Man verliert viel Speicherplatz, weil man Indexe von den Nodes duplizieren muss (weil man die Zeile nicht in den Nodes speichern kann).
• Löschvorgänge werden die Tabelle im Zeitablauf zersetzen (weil Indexe in Nodes üblicherweise bei Löschvorgängen nicht aktualisiert werden).
• Ist es schwieriger, NUR die Index-Daten zu cachen.

6.4.2 Wie Sie Ihre Daten so klein wie möglich bekommen

Eine der grundlegendsten Optimierungen besteht darin, Ihre Daten (und Indexe) dazu zu bekommen, dass sie möglichst wenige Platz auf der Platte (und im Arbeitsspeicher) benutzen. Das kann zu gewaltigen Verbesserungen führen, weil Lesezugriffe von der Platte schneller ablaufen und normalerweise weniger Hauptspeicher benutzt wird. Das Indexieren nimmt darüber hinaus weniger Ressourcen in Anspruch, wenn es auf kleinere Spalten durchgeführt wird.

MySQL unterstützt viele verschiedene Tabellentypen und Zeilenformate. Wenn Sie das richtige Tabellenformat benutzen, kann Ihnen das große Performance-Gewinne bringen. See section 8 MySQL-Tabellentypen.

Sie erhalten bessere Performance auf eine Tabelle und minimieren den benötigten Speicherplatz, wenn Sie die unten aufgeführten Techniken verwenden:

• Benutzen Sie die effizientesten (kleinsten) möglichen Typen. MySQL hat viele spezialisierte Typen, die Plattenplatz und Arbeitsspeicher sparen.
• Benutzen Sie - falls möglich - die kleineren Ganzzahl-Typen, um kleinere Tabellen zu erhalten. MEDIUMINT zum Beispiel ist oft besser als INT.
• Deklarieren Sie Spalten - falls möglich - als NOT NULL. Das macht alles schneller und Sie sparen ein Bit pro Spalte. Beachten Sie, dass, wenn Sie wirklich NULL in Ihrer Applikation benötigen, Sie dieses natürlich benutzen sollten. Vermeiden Sie nur, einfach alle Spalten vorgabemäßig auf NULL zu haben.
• Wenn Sie keine Spalten variabler Länge haben (VARCHAR, TEXT oder BLOB-Spalten), wird ein Festgrößenformat benutzt. Das ist schneller, mag aber leider etwas Speicherplatz verschwenden. See section 8.1.2 MyISAM-Tabellenformate.
• Der primäre Index einer Tabelle sollte so kurz wie möglich sein. Das macht die Identifikation einer Zeile schnell und effizient.
• Bei jeder Tabelle müssen Sie entscheiden, welche Speicher- / Index-Methode benutzt werden soll. See section 8 MySQL-Tabellentypen.
• Erzeugen Sie nur die Indexe, die Sie tatsächlich brauchen. Indexe sind gut für das Abfragen von Daten, aber schlecht, wenn Sie Dinge schnell speichern müssen. Wenn Sie meist auf eine Tabelle zugreifen, indem Sie nach einer Kombination von Spalten suchen, legen Sie einen Index auf diese. Der erste Index-Teil sollte die meistbenutzte Spalte sein. Wenn Sie IMMER viele Spalten benutzen, sollten Sie die Spalte zuerst benutzen, die mehr Duplikate hat, um eine bessere Kompression des Indexes zu erzielen.
• Wenn es sehr wahrscheinlich ist, dass eine Spalte ein eindeutiges Präfix auf der ersten Anzahl von Zeichen hat, ist es besser, nur dieses Präfix zu indexieren. MySQL unterstützt einen Index auf einem Teil einer Zeichen-Spalte. Kürzere Indexe sind nicht nur schneller, weil sie weniger Plattenplatz brauchen, sondern auch, weil Sie mehr Treffer im Index-Cache erhalten und daher weniger Festplattenzugriffe benötigen. See section 6.5.2 Serverparameter tunen.
• Unter manchen Umständen kann es vorteilhaft sein, eine Tabelle zu teilen, die sehr oft gescannt wird. Das gilt insbesondere, wenn diese ein dynamisches Tabellenformat hat und es möglich ist, durch die Zerlegung eine kleinere Tabelle mit statischem Format zu erhalten, die benutzt werden kann, um die relevanten Zeilen zu finden.

6.4.3 Wie MySQL Indexe benutzt

Indexe werden benutzt, um Zeilen mit einem bestimmten Spaltenwert schnell zu finden. Ohne Index müsste MySQL mit dem ersten Datensatz anfangen und dann durch die gesamte Tabelle lesen, bis er die relevanten Zeilen findet. Je größer die Tabelle, desto mehr Zeit kostet das. Wenn die Tabellen für die infrage kommenden Zeilen einen Index hat, kann MySQL schnell eine Position bekommen, um mitten in der Daten-Datei loszusuchen, ohne alle Daten zu betrachten. Wenn eine Tabelle 1.000 Zeilen hat, ist das mindestens 100 mal schneller als sequentielles Lesen. Wenn Sie jedoch auf fast alle 1.000 Zeilen zugreifen müssen, geht sequentielles Lesen schneller, weil man mehrfache Festplattenzugriffe einspart.

Alle MySQL-Indexe (PRIMARY, UNIQUE und INDEX) sind in B-Bäumen gespeichert. Zeichenketten werden automatisch präfix-komprimiert, ebenfalls werden Leerzeichen am Ende komprimiert. See section 7.5.7 CREATE INDEX-Syntax.

Indexe werden benutzt, um:

• Schnell die Zeilen zu finden, die mit einer WHERE-Klausel übereinstimmen.
• Zeilen aus anderen Tabellen abzurufen, wenn Sie Joins durchführen.
• Den MAX()- oder MIN()-Wert für eine spezielle indizierte Spalte zu finden. Das wird durch einen Präprozessor optimiert, der überprüft, ob Sie WHERE schluessel_teil_# = constant auf allen Schlüsselteilen < N verwenden. In diesem Fall führt MySQL ein einzige Schlüsselnachschlagen durch und ersetzt den MIN()-Ausdruck mit einer Konstanten. Wenn alle Ausdrücke durch Konstanten ersetzt sind, gibt die Anfrage sofort ein Ergebnis zurück: SELECT MIN(schluessel_teil2),MAX(schluessel_teil2) FROM tabelle where schluessel_teil1=10
• Eine Tabelle zu sortieren oder zu gruppieren, wenn das Sortieren oder Gruppieren mit dem am weitesten links stehenden Präfix eines benutzbaren Schlüssels durchgeführt wird (zum Beispiel ORDER BY schluessel_teil_1,schluessel_teil_2). Der Schlüssel wird in umgekehrter Reihenfolge gelesen, wenn allen Schlüsselteilen DESC folgt. Der Index kann auch benutzt werden, selbst wenn ORDER BY nicht exakt mit dem Index übereinstimmt, solange alle unbenutzten Indexteile und alle zusätzlichen ORDER BY-Spalten Konstanten in der WHERE-Klausel sind. Folgende Anfragen werden einen Index benutzen, um den ORDER BY-Teil aufzulösen: SELECT * FROM foo ORDER BY schluessel_teil1,schluessel_teil2,schluessel_teil3; SELECT * FROM foo WHERE spalte=konstante ORDER BY spalte, schluessel_teil1; SELECT * FROM foo WHERE schluessel_teil1=konstante GROUP BY schluessel_teil2;
• In einigen Fällen kann eine Anfrage so optimiert werden, dass Sie Werte abruft, ohne in der Daten-Datei nachzuschlagen. Wenn alle benutzten Spalten einer Tabelle numerisch sind und ein ganz links stehendes Präfix für einen Schlüssel ergeben, können die Werte mit größerer Geschwindigkeit aus dem Index-Baum abgerufen werden: SELECT schluessel_teil3 FROM tabelle WHERE schluessel_teil1=1

Angenommen, Sie führen folgendes SELECT-Statement aus:

Wenn es einen mehrspaltigen Index auf spalte1 und spalte2 gibt, können die entsprechenden Zeilen direkt geholt werden. Wenn es separate einspaltige Indexe auf spalte1 und spalte2 gibt, versucht der Optimierer, den restriktivsten Index zu finden, indem er entscheidet, welcher Index weniger Zeilen finden wird, und diesen Index dann benutzen, um Zeilen abzurufen.

Wenn die Tabelle einen mehrspaltigen Index hat, kann jedes Präfix auf der linken Seite vom Optimierer verwendet werden, um Zeilen zu finden. Wenn Sie zum Beispiel einen dreispaltigen Index auf (spalte1,spalte2,spalte3) haben, haben Sie Suchmöglichkeiten auf (spalte1), (spalte1,spalte2) und (spalte1,spalte2,spalte3) indiziert.

MySQL kann keinen teilweisen Index verwenden, wenn die Spalten kein ganz linkes Präfix des Indexes bilden. Angenommen, Sie haben folgende SELECT-Statements:

Wenn es einen Index auf (spalte1,spalte2,spalte3) gibt, benutzt nur die erste der drei Anfragen den Index. Die zweite und dritte Anfrage umfassen indizierte Spalten, aber (spalte2) und (spalte2,spalte3) sind nicht die ganz linken Präfixe von (spalte1,spalte2,spalte3).

MySQL benutzt Indexe auch für LIKE-Vergleiche, wenn das Argument für LIKE eine Zeichenketten-Konstante ist, die nicht mit einem Platzhalterzeichen anfängt. Die folgenden SELECT-Statements zum Beispiel benutzen Indexe:

Im ersten Statement werden nur Zeilen mit "Patrick" <= schluessel_spalte < "Patricl" berücksichtigt. Im zweiten Statement werden nur Zeilen mit "Pat" <= schluessel_spalte < "Pau" berücksichtigt.

Die folgenden SELECT-Statements benutzen keine Indexe: mysql> select * from tabelle where schluessel_spalte LIKE "%Patrick%"; mysql> select * from tabelle where schluessel_spalte LIKE andere_spalte;

Im ersten Statement fängt der LIKE-Wert mit einem Platzhalterzeichen an. Im zweiten Statement ist der LIKE-Wert keine Konstante.

Suchen mit spalte IS NULL benutzt Indexe, wenn spalte ein Index ist.

MySQL benutzt normalerweise den Index, der die geringste Anzahl von Zeilen findet. Ein Index wird benutzt für Spalten, die Sie mit folgenden Operatoren vergleichen: =, >, >=, <, <=, BETWEEN und einem LIKE ohne Platzhalter-Präfix wie 'etwas%'.

Jeder Index, der nicht alle AND-Ebenen in der WHERE-Klausel umfasst, wird nicht benutzt, um die Anfrage zu optimieren. Mit anderen Worte: Um einen Index benutzen zu können, muss ein Präfix des Indexes in jeder AND-Gruppe benutzt werden.

Die folgenden WHERE-Klauseln benutzen Indexe: ... WHERE index_teil1=1 AND index_teil2=2 AND andere_spalte=3 ... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */ ... WHERE index_teil1='hello' AND index_teil_3=5 /* optimiert "index_teil1='hello'" */ ... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3; /* kann den Index auf index1 benutzen, aber nicht auf index2 oder index 3 */

Die folgenden WHERE-Klauseln benutzen KEINE Indexe: ... WHERE index_teil2=1 AND index_teil3=2 /* index_teil_1 wird nicht benutzt */ ... WHERE index=1 OR A=10 /* Index wird nicht in beiden AND-Teilen benutzt */ ... WHERE index_teil1=1 OR index_teil2=10 /* Kein Index umfasst alle Zeilen */

Beachten Sie, dass MySQL in manchen Fällen keinen Index benutzt, selbst wenn einer verfügbar wäre. Einige solcher Fälle sind hier aufgeführt:

• Wenn die Benutzung des Indexes erfordern würde, dass MySQL auf mehr als 30% der Zeilen in der Tabelle zugreift. (In diesem Fall ist ein Tabellenscan wahrscheinlich viel schneller, weil dieser weniger Festplattenzugriffe braucht.) Beachten Sie, dass MySQL den Index dennoch benutzt, wenn eine Anfrage LIMIT benutzt, um nur ein paar Zeilen abzufragen, weil er dann schneller die wenigen Zeilen im Ergebnis finden kann.

6.4.4 Spalten-Indexe

Alle MySQL-Spaltentypen können indiziert werden. Die Benutzung von Indexen auf den relevanten Spalten ist die beste Art, die Performance von SELECT-Operationen zu verbessern.

Die maximale Anzahl von Schlüsseln und die maximale Index-Länge ist durch den Tabellen-Handler vorgegeben. See section 8 MySQL-Tabellentypen. Bei allen Tabellen-Handlern können Sie zumindest 16 Schlüssel und eine Gesamtindexlänge von zumindest 256 Bytes haben.

Bei CHAR- und VARCHAR-Spalten können Sie ein Präfix einer Spalte indexieren. Das ist viel schneller und erfordert weniger Plattenspeicher als das Indexieren einer ganzen Spalte. Die Syntax, die im CREATE TABLE-Statement benutzt wird, um ein Spaltenpräfix zu indexieren, sieht wie folgt aus:

Das unten stehende Beispiel erzeugt einen Index auf die ersten 10 Zeichen der name-Spalte:

Bei BLOB- und TEXT-Spalten müssen Sie ein Präfix der Spalte indexieren. Sie können nicht die gesamte Spalte indexieren.

Ab MySQL-Version 3.23.23 können Sie auch spezielle FULLTEXT-Indexe erzeugen. Sie werden für die Volltextsuche benutzt. Nur der MyISAM-Tabellentyp unterstützt FULLTEXT-Indexe. Sie können nur auf VARCHAR- und TEXT-Spalten erzeugt werden. Die Indexierung erfolgt immer über die gesamte Spalte; teilweises Indexieren wird nicht unterstützt. Siehe section 7.8 MySQL-Volltextsuche für Details.

6.4.5 Mehrspaltige Indexe

MySQL kann Indexe auf mehrfache Spalten erzeugen. Ein Index darf aus bis zu 15 Spalten bestehen. (Auf CHAR- und VARCHAR-Spalten können Sie auch ein Präfix der Spalte als Teil eines Indexes benutzen).

Ein mehrspaltiger Index kann als sortiertes Array betrachtet werden, das Werte enthält, die durch die Verkettung der Werte der indizierten Spalten erzeugt werden.

MySQL benutzt mehrspaltige Indexe in einer Art, dass Anfragen schnell werden, wenn Sie eine bekannte Menge für die erste Spalte des Indexes in einer WHERE-Klausel angeben, selbst wenn Sie keine Werte für die anderen Spalten angeben.

Angenommen, einen Tabelle wurde wie folgt erzeugt:

Dann ist der Index name ein Index über nachname und vorname. Der Index wird für Anfragen benutzt, die Werte in einem bekannten Bereich für nachname angeben, oder sowohl für nachname als auch für und vorname. Daher wird der name-Index in folgenden Anfragen benutzt:

In folgenden Anfragen wird der name-Index jedoch NICHT benutzt:

Weitere Informationen über die Art, wie MySQL Indexe benutzt, um die Anfragen-Performance zu verbessern, finden Sie unter section 6.4.3 Wie MySQL Indexe benutzt.

6.4.6 Wie MySQL Tabellen öffnet und schließt

table_cache, max_connections und max_tmp_tables beeinflussen die maximale Anzahl von Dateien, die der Server offen halten kann. Wenn Sie einen oder mehrere dieser Werte erhöhen, können Sie an eine Begrenzung stoßen, die durch Ihr Betriebssystem in Bezug auf die Anzahl offener Datei-Deskriptoren pro Prozess festgelegt wird. Diese Begrenzung kann man jedoch auf vielen Systemen erhöhen. Sehen Sie im Handbuch Ihres Betriebssystems nach, wie man das macht, weil die Methode, wie die Begrenzung geändert wird, sich von System zu System stark unterscheidet.

table_cache ist verwandt mit max_connections. Für 200 gleichzeitig laufende Verbindungen sollten Sie zum Beispiel einen Tabellen-Cache von mindestens 200 * n haben, wobei n die maximale Anzahl von Tabellen in einem Join ist. Zusätzlich müssen Sie einige externe Datei-Deskriptoren für temporäre Tabellen und Dateien reservieren.

Stellen Sie sicher, dass Ihr Betriebssystem die Anzahl offener Datei-Deskriptoren handhaben kann, die durch die table_cache-Einstellung impliziert wird. Wenn table_cache zu hoch gesetzt wird, hat MySQL eventuell keine Datei-Deskriptoren mehr und verweigert Verbindungen, führt keine Anfragen mehr aus und läuft sehr unzuverlässig. Beachten Sie auch, dass der MyISAM-Tabellen-Handler zwei Datei-Deskriptoren für jede einzelne offene Tabelle benötigt. Sie können die Anzahl von Datei-Deskriptoren, die für MySQL verfügbar sind, in der --open-files-limit=#-Startoption angeben. See section A.2.16 File Not Found.

Der Cache offener Tabellen kann bis auf table_cache anwachsen (Vorgabewert 64; das kann mit der -O Tabellen-Cache=#-Option für mysqld geändert werden). Eine Tabelle wird nie geschlossen, ausser wen der Cache voll ist und ein anderer Thread versucht, eine Tabelle zu öffnen, oder wenn Sie mysqladmin refresh oder mysqladmin flush-tables benutzen.

Wenn sich der Tabellen-Cache füllt, benutzt der Server folgenden Prozedur, um einen Cache-Eintrag für die Benutzung zu finden:

• Tabellen, die momentan nicht in Benutzung sind, werden freigegeben, in der Reihenfolge der kürzlich am wenigsten benutzten Tabellen.
• Wenn der Cache voll ist und keine Tabellen freigegeben werden können, aber eine neue Tabelle geöffnet werden muss, wird der Cache temporär wie benötigt vergrößert.
• Wenn der Cache gerade im Zustand temporärer Erweiterung ist und eine Tabelle vom Zustand benutzt in den Zustand nicht benutzt wechselt, wird die Tabelle geschlossen und vom Cache freigesetzt.

Eine Tabelle wird für jeden gleichzeitigen Zugriff geöffnet. Das bedeutet, dass die Tabelle zweimal geöffnet werden muss, wenn Sie zwei Threads haben, die auf dieselbe Tabelle zugreifen oder einen Thread, der auf die Tabelle zweimal in derselben Anfrage zugreift (mit AS). Das erste öffnen jeder Tabelle benötigt nur einen Datei-Deskriptor. Der zusätzliche Deskriptor wird für die Index-Datei benötigt; dieser Deskriptor wird mit allen Threads geteilt (shared).

Wenn Sie eine Tabelle mit dem HANDLER tabelle OPEN-Statement öffnen, wird dem Thread ein dediziertes Tabellenobjekt zugewiesen. Diese Tabellenobjekt wird nicht mit anderen Threads geteilt und wird solange nicht geschlossen, bis der Thread HANDLER tabelle CLOSE aufruft oder stirbt. See section 7.4.3 HANDLER-Syntax.

Sie können prüfen, ob Ihr Tabellen-Cache zu klein ist, indem Sie die mysqld-Variable opened_tables ansehen. Wenn diese recht Groß ist, selbst wenn Sie nicht viele FLUSH TABLES ausgeführt haben, sollten Sie Ihren Tabellen-Cache vergrößern. See section 5.5.5.3 SHOW STATUS.

6.4.7 Nachteile der Erzeugung großer Mengen von Tabellen in derselben Datenbank

Wenn Sie viele Dateien in einem Verzeichnis haben, werden open-, close- und create-Operationen langsam. Wenn Sie ein SELECT-Statements auf viele unterschiedliche Tabellen ausführen, gibt es ein bisschen Overhead, wenn der Tabellen-Cache voll ist, weil für jede Tabelle, die geöffnet wird, eine andere geschlossen werden muss. Sie können diese Overhead verringern, indem Sie den Tabellen-Cache größer machen.

6.4.8 Warum gibt es so viele offene Tabellen?

Wenn Sie mysqladmin status ausführen, werden Sie etwa folgendes sehen:

Das kann etwas verwirrend sein, wenn Sie nur 6 Tabellen haben.

MySQL ist multi-threaded, daher kann er viele Anfragen auf dieselbe Tabelle simultan verarbeiten. Um das Problem zu minimieren, dass zwei Threads verschiedene Zustände in Bezug auf dieselbe Datei haben, wird die Tabelle unabhängig für jeden gleichzeitigen Thread geöffnet. Das benötigt etwas Arbeitsspeicher und einen externen Datei-Deskriptor für die Daten-Datei. Der Index-Datei-Deskriptor wird mit allen Threads geteilt.

6.5 Optimierung des MySQL-Servers

6.5.1 System / Kompilierzeitpunkt und Tuning der Startparameter

Wir fangen mit den Dingen auf Systemebene an, weil einige dieser Entscheidungen sehr früh getroffen werden müssen. In anderen Fällen mag ein kurzer Blick auf diesen Teil ausreichen, weil er nicht so wichtig für große Verbesserungen ist. Es ist jedoch immer nett, ein Gefühl dafür zu bekommen, wie viel man gewinnen kann, wenn man Dinge auf dieser Ebene ändert.

Es ist wirklich wichtig, dass vorgabemäßige Betriebssystem zu kennen! Um das meiste aus Mehrprozessor-Maschinen herauszuholen, sollte man Solaris benutzen (weil die Threads wirklich gut funktionieren) oder Linux (weil der 2.2-Kernel wirklich gute Mehrprozessor-Unterstützung bietet). Linux hat auf 32-Bit-Maschinen vorgabemäßig eine Dateigrößenbeschränkung von 2 GB. Das wird hoffentlich bald behoben, wenn neue Dateisysteme herausgebracht werden (XFS/Reiserfs). Wenn Sie dringen Unterstützung für größere Datei als 2 GB auf Linux-Intel-32-Bit benötigen, sollten Sie den LFS-Patch für das ext2-Dateisystem holen.

Weil wir MySQL noch nicht auf allzu vielen Plattformen in einer Produktionsumgebung getestet haben, empfehlen wir, dass Sie Ihre geplante Plattform testen, bevor Sie sich dafür entscheiden.

Weitere Tipps:

• Wenn Sie genug Arbeitsspeicher haben, könnten Sie alle Swap-Geräte entfernen. Einige Betriebssysteme benutzen in bestimmten Zusammenhängen ein Swap-Gerät, selbst wenn Sie freien Arbeitsspeicher haben.
• Benutzen Sie die --skip-locking-MySQL-Option, um externe Sperren zu vermeiden. Beachten Sie, dass das die Funktionalität von MySQL nicht tangiert, solange Sie nur einen Server laufen lassen. Denken Sie lediglich daran, den Server herunterzufahren (oder die relevanten Teile zu sperren), bevor Sie myisamchk laufen lassen. Auf manchen Systemen ist diese Umschaltung zwingend erforderlich, weil externes Sperren in keinem Fall funktioniert. Die --skip-locking-Option ist vorgabemäßig angeschaltet, wenn Sie mit MIT-pThreads kompilieren, weil flock() von MIT-pThreads nicht vollständig auf allen Plattformen unterstützt wird. Auch für Linux ist es vorgabemäßig angeschaltet, weil Linux-Dateisperren bis jetzt nicht zuverlässig funktionieren. Der einzige Fall, wo Sie --skip-locking nicht benutzen können, sit, wenn Sie mehrfache MySQL-Server (nicht Clients) auf denselben Daten laufen lassen, oder wenn Sie myisamchk auf eine Tabelle ausführen, ohne zuerst die mysqld-Server-Tabellen auf Platte zurückzuschreiben und zu sperren. Sie können immer noch LOCK TABLES / UNLOCK TABLES benutzen, selbst wenn Sie --skip-locking benutzen.

6.5.2 Serverparameter tunen

Sie erhalten die Puffer-Größen, die der mysqld-Server benutzt, mit diesem Befehl:

Dieser Befehl erzeugt eine Auflistung aller mysqld-Optionen und konfigurierbaren Variablen. Die Ausgabe enthält die Vorgabewerte und sieht etwa wie folgt aus:

Wenn aktuell ein mysqld-Server läuft, können Sie feststellen, welche Werte er für die Variablen tatsächlich benutzt, wenn Sie diesen Befehl ausführen:

Sie finden eine komplette Beschreibung aller Variablen im SHOW VARIABLES-Abschnitt dieses Handbuchs. See section 5.5.5.4 SHOW VARIABLES.

Wenn Sie SHOW STATUS eingeben, können Sie einige statistische Informationen des Servers sehen. See section 5.5.5.3 SHOW STATUS.

MySQL benutzt Algorithmen, die sehr skalierbar sind, daher können Sie üblicherweise mit sehr wenig Arbeitsspeicher fahren. Wenn Sie MySQL jedoch mehr Speicher geben, erzielen Sie damit normalerweise auch bessere Performance.

Wenn Sie einen MySQL-Server tunen, sind die zwei wichtigsten Variablen key_buffer_size und table_cache. Sie sollten zunächst sicher sein, dass diese beiden richtig gesetzt sind, bevor Sie versuchen, irgend eine der anderen Variablen zu ändern.

Wenn Sie viel Arbeitsspeicher haben (>= 256 MB) und viele Tabellen und maximale Performance bei einer mäßigen Anzahl von Clients haben wollen, sollten Sie etwas wie das Folgende benutzen:

Wenn Sie nur 128 MB und nur wenige Tabellen haben, aber viele Sortiervorgänge durchführen, können Sie etwas wie das Folgende benutzen:

Wenn Sie wenig Arbeitsspeicher und viele Verbindungen haben, können Sie etwas wie das Folgende benutzen:

Oder sogar:

Wenn Sie GROUP BY oder ORDER BY auf Dateien anwenden, die größer als Ihr verfügbarer Arbeitsspeicher sind, sollten Sie den Wert von record_rnd_buffer heraufsetzen, um das Lesen von Zeilen nach Sortiervorgängen zu beschleunigen.

Wenn Sie MySQL installiert haben, enthält das `Support-files'-Verzeichnis einige unterschiedliche my.cnf-Beispiel-Dateien: `my-huge.cnf', `my-large.cnf', `my-medium.cnf' und `my-small.cnf'. Diese können Sie als Grundlage nehmen, um Ihr System zu optimieren.

Wenn es sehr viele Verbindungen gibt, können ``Swapping-Probleme'' auftauchen, wen Sie mysqld nicht so konfiguriert haben, dass er für jede Verbindung sehr wenig Speicher benutzt. mysqld bringt natürlich bessere Leistungsdaten, wenn Sie genug Speicher für alle Verbindungen haben.

Beachten Sie, dass Änderungen einer Option für mysqld sich nur auf diese Instanz des Servers auswirken.

Um die Auswirkung einer Parameteränderung zu sehen, geben Sie folgendes ein:

Stellen Sie sicher, dass die --help-Option zuletzt kommt, ansonsten wird die Auswirkung jeglicher Optionen, die danach auf der Kommandozeile kommen, in der Ausgabe nicht gezeigt. output.

6.5.3 Wie Kompilieren und Linken die Geschwindigkeit von MySQL beeinflusst

Die meisten der folgenden Tests wurden mit den MySQL-Benchmarks unter Linux durchgeführt, aber sie sollten einen guten Anhaltspunkt für andere Betriebssysteme und Auslastungen geben.

Sie erhalten die schnellste ausführbare Datei, wenn Sie mit -static linken.

Unter Linux erhalten Sie den schnellsten Code, wenn Sie mit pgcc und -O3 kompilieren. Um `sql_yacc.cc' mit diesen Optionen zu kompilieren, brauchen Sie etwa 200 MB Arbeitsspeicher, weil gcc/pgcc viel Speicher benötigt, um alle Funktionen inline zu machen. Sie sollten beim Konfigurieren von MySQL auch CXX=gcc setzen, um das Einschließen der libstdc++-Bibliothek zu vermeiden (die nicht benötigt wird). Beachten Sie, dass bei einigen Version von pgcc der erzeugte Code nur auf echten Pentium-Prozessoren läuft, selbst wenn Sie in den Compiler-Optionen angeben, dass Sie wollen, dass der Code auf alle Prozessoren vom Typ x586 läuft (wie AMD).

Einfach durch die Benutzung eines besseren Compilers und / oder besserer Compiler-Optionen können Sie eine 10-30%-ige Geschwindigkeitssteigerung in Ihrer Applikation erhalten. Das ist besonders wichtig, wenn Sie den SQL-Server selbst kompilieren!

Wir haben sowohl Cygnus CodeFusion als auch Fujitsu-Compiler getestet, aber es stellte sich heraus, dass keiner von beiden ausreichend Bug-frei war, damit MySQL mit angeschalteten Optimierungen kompiliert werden konnte.

Wenn Sie MySQL kompilieren, sollten Sie nur Unterstützung für die Zeichensätze einschließen, die Sie benutzen werden (Option --with-charset=xxx). Die Standard-MySQL-Binärdistributionen werden mit Unterstützung für alle Zeichensätze kompiliert.

Hier ist eine Auflistung einiger Messungen, die wir durchgeführt haben:

• Wenn Sie pgcc benutzen und alles mit -O6 kompilieren, ist der mysqld-Server 1% schneller als mit gcc 2.95.2.
• Wenn Sie dynamisch linken (ohne -static), ist das Ergebnis unter Linux 13% langsamer. Beachten Sie, dass Sie dennoch dynamisch gelinkte MySQL-Bibliotheken benutzen können. Nur beim Server ist das kritisch in Bezug auf Performance.
• Wenn Sie Ihre mysqld-Binärdatei mit strip libexec/mysqld strippen, ist die resultierende Binärdatei bis zu 4% schneller.
• Wenn Sie sich über TCP/IP statt über Unix-Sockets verbinden, ist das auf demselben Computer 7,5% langsamer. (Wenn Sie sich zu localhost verbinden, benutzt MySQL vorgabemäßig Sockets.)
• Wenn Sie sich über TCP/IP von einem anderen Computer über ein 100-MBit-Ethernet verbinden, ist das 8% bis 11% langsamer.
• Wenn Sie mit --with-debug=full kompilieren, verlangsamen sich die meisten Anfragen um 20%, manche Anfragen jedoch werden wesentlich langsamer (der MySQL-Benchmarks zeigte 35%). Wenn Sie --with-debug benutzen, beträgt die Verlangsamung nur 15%. Wenn Sie eine mysqld-Version, die mit --with-debug=full kompiliert wurde, mit --skip-safemalloc starten, ist die Geschwindigkeit etwa dasselbe, als wenn Sie mit --with-debug konfigurieren.
• Auf einer Sun SPARCstation 20 ist SunPro C++ 4.2 5% schneller als gcc 2.95.2.
• Das Kompilieren mit gcc 2.95.2 für ultrasparc mit der Option -mcpu=v8 -Wa,-xarch=v8plusa ergibt 4% mehr Performance.
• Auf Solaris 2.5.1 sind MIT-pThreads 8% bis 12% langsamer als Solaris-native Threads, auf einem Einprozessorsystem. Bei mehr Last / Prozessoren sollte der Unterschied größer werden.
• Laufenlassen mit --log-bin macht MySQL 1% langsamer.
• Wenn beim Kompilieren unter Linux-x86 mit gcc keine Frame-Pointers -fomit-frame-pointer oder -fomit-frame-pointer -ffixed-ebp verwendet werden, ist mysqld 1% bis 4% schneller.

Die MySQL-Linux-Distribution, die von MySQL AB zur Verfügung gestellt wird, wurde früher mit pgcc kompiliert, aber wir mussten zum normalen gcc zurück gehen, weil es einen Bug in pgcc gibt, der Code erzeugt, der nicht auf AMD läuft. Wir werden gcc solange benutzen, bis dieser Bug behoben ist. Bis dahin können Sie, falls Sie keine AMD-Maschine haben, eine schnellere Binärdatei erhalten, wenn Sie mit pgcc kompilieren. Die Standard-MySQL-Linux-Binärdatei wird statisch gelinkt, um sie schneller und portierbarer zu machen.

6.5.4 Wie MySQL Speicher benutzt

Die unten stehende Liste zeigt einige Möglichkeiten, wie der mysqld-Server Speicher benutzt. Wo es zutrifft, wird der Name der für die Speicherbenutzung relevanten Servervariablen angegeben.

• Der Schlüssel-Puffer (Variable key_buffer_size) wird von allen Threads geteilt. Andere Puffer, die vom Server benutzt werden, werden bei Bedarf zugewiesen. See section 6.5.2 Serverparameter tunen.
• Jede Verbindung benutzt etwas Thread-spezifischen Platz: Einen Stack (Vorgabe 64 KB, Variable thread_stack), einen Verbindungspuffer (Variable net_buffer_length) und a Ergebnispuffer (Variable net_buffer_length). Die Verbindungspuffer und Ergebnispuffer werden bei Bedarf dynamisch bis zu max_allowed_packet vergrößert. Wenn eine Anfrage läuft, wird auch eine Kopie der aktuellen Anfragezeichenkette zugewiesen.
• Alle Threads teilen sich denselben grundlegenden Speicher.
• Nur die komprimierten ISAM- / MyISAM-Tabellen werden Speicher-gemappt. Das liegt daran, dass der 32-Bit-Adressraum von 4 GB für die meisten großen Tabellen nicht Groß genug ist. Wenn Systeme mit 64-Bit-Adressraum gebräuchlicher werden, werden wir vielleicht eine allgemeine Unterstützung für Speicher-Mapping hinzufügen.
• Jeder Anfrage, die einen sequentiellen Scan über eine Tabelle durchführt, wird ein Lesepuffer zugewiesen (Variable record_buffer).
• Wenn Zeilen in 'zufälliger' Reihenfolge gelesen werden (zum Beispiel nach einem Sortiervorgang), wird ein Zufalls-Lesepuffer zugewiesen, um Suchvorgänge auf Festplatte zu vermeiden. (Variable record_rnd_buffer).
• Alle Joins werden in einem Durchgang durchgeführt und die meisten Joins können sogar ohne Benutzung einer temporären Tabelle durchgeführt werden. Die meisten temporären Tabellen sind Speicher-basierende (HEAP-) Tabellen. Temporäre Tabellen mit großer Datensatzlänge (berechnet als Summe aller Spaltenlängen) oder die BLOB-Spalten enthalten, werden auf Festplatte gespeichert. Ein Problem in MySQL-Versionen vor Version 3.23.2 ist, dass Sie den Fehler The table tabelle is full erhalten, wenn die Größe der HEAP-Tabelle tmp_table_size überschreitet. In neueren Versionen wird dies so gehandhabt, dass die Speicher-basierende (HEAP-) Tabelle bei Bedarf automatisch in eine Festplatten-basierende Tabelle (MyISAM) umgewandelt wird. Um das Problem zu umgehen, können Sie die Größe von temporären Tabellen durch Setzen der tmp_table_size-Option für mysqld ändern, oder durch Setzen der SQL-Option SQL_BIG_TABLES im Client-Programm. See section 6.5.6 SET-Syntax. In MySQL-Version 3.20 war die maximale Größe der temporären Tabelle record_buffer*16. Wenn Sie also diese Version benutzen, müssen Sie den Wert von record_buffer herauf setzen. Sie können mysqld auch mit der --big-tables-Option starten, um temporäre Tabellen immer auf Festplatte zu speichern. Das wird jedoch die Geschwindigkeit vieler komplizierter Anfragen beeinflussen.
• Den meisten Sortier-Anfragen werden ein Sortierpuffer und 0 bis 2 temporäre Dateien zugewiesen, abhängig von der Größe der Ergebnismenge. See section A.4.4 Wohin MySQL temporäre Dateien speichert.
• Fast alles Parsen und Berechnen wird in einem lokalen Speicherbereich durchgeführt. Für kleine Sachen wird kein Speicher-Overhead benötigt, und das normale, langsame Zuweisen und Freimachen von Speicher wird vermieden. Speicher wird nur für unerwartet lange Zeichenketten zugewiesen (das wird mit malloc() und free() gemacht).
• Jede Index-Datei wird einmal geöffnet. Die Daten-Datei wird einmal für jeden gleichzeitig laufenden Thread geöffnet. Für jeden gleichzeitigen Thread wird eine Tabellenstruktur, Spaltenstrukturen für jede Spalte und ein Puffer der Größe 3 * n zugewiesen, wobei n die maximale Zeilenlänge ist (BLOB-Spalten werden nicht mitgerechnet). Eine BLOB-Spalte benutzt 5 bis 8 Bytes plus die Länge der BLOB-Daten. Der ISAM- / MyISAM-Tabellen-Handler benutzt einen zusätzlichen Zeilenpuffer für internen Gebrauch.
• Bei jeder Tabelle, die BLOB-Spalten enthält, wird ein Puffer dynamisch vergrößert, um größere BLOB-Werte einzulesen. Wenn Sie eine Tabelle scannen, wird ein Puffer so Groß wie der größte BLOB-Wert zugewiesen.
• Tabellen-Handler für alle Tabellen in Benutzung werden in einem Cache gespeichert und als FIFO verwaltet. Normalerweise hat der Cache 64 Einträge. Wenn eine Tabelle gleichzeitig von zwei laufenden Threads benutzt wurde, enthält der Cache zwei Einträge für die Tabelle. See section 6.4.6 Wie MySQL Tabellen öffnet und schließt.
• Ein mysqladmin flush-tables-Befehl schließt alle Tabellen, die nicht in Benutzung sind, und kennzeichnet alle Tabellen in Benutzung als zu schließen, sobald der aktuell ausführende Thread fertig ist. Das setzt effektiv den meisten benutzten Speicher frei.

ps und andere System-Status-Programme berichten vielleicht, dass mysqld viel Arbeitsspeicher benutzt. Das kann durch Thread-Stacks auf verschiedenen Speicheradressen verursacht werden. ps der Solaris-Version zum Beispiel zählt den unbenutzten Speicher zwischen Stacks zum benutzten Speicher hinzu. Das können Sie bestätigen, wenn Sie den verfügbaren Swap mit swap -s überprüfen. Wir haben mysqld mit kommerziellen Memory-Leak-Detektoren getestet, daher sollte es keine Memory-Leaks geben.

6.5.5 Wie MySQL DNS benutzt

Wenn sich ein neuer Thread mit mysqld verbindet, erzeugt mysqld einen neuen Thread, um die Anfrage zu handhaben. Dieser Thread prüft zuerst, ob der Hostname im Hostnamen-Cache ist. Falls nicht, ruft der Thread gethostbyaddr_r() und gethostbyname_r() auf, um den Hostname aufzulösen.

Wenn das Betriebssystem die oben genannten Thread-sicheren Aufrufe nicht unterstützt, sperrt der Thread ein Mutex und ruft statt dessen gethostbyaddr() und gethostbyname() auf. Beachten Sie, dass in diesem Fall kein anderer Thread andere Hostnamen auflösen kann, die nicht im Hostnamen-Cache sind, bis der erste Thread fertig ist.

Sie können das DNS-Nachschlagen von Hostnamen (DNS-Lookup) abschalten, indem Sie mysqld mit --skip-name-resolve starten. In diesem Fall können Sie jedoch in den MySQL-Berechtigungstabellen nur IP-Nummern verwenden.

Wenn Sie ein sehr langsames DNS und viele Hosts haben, können Sie mehr Performance erzielen, wenn Sie entweder das DNS-Nachschlagen von Hostnamen (DNS-Lookup) abschalten (mit --skip-name-resolve) oder HOST_CACHE_SIZE (Vorgabe: 128) erhöhen und mysqld neu kompilieren.

Sie können den Hostnamen-Cache mit --skip-host-cache abschalten. Sie können den Hostnamen-Cache mit FLUSH HOSTS oder mysqladmin flush-hosts löschen.

Wenn Sie keine Verbindungen über TCP/IP zulassen wollen, starten Sie mysqld mit --skip-networking.

6.5.6 SET-Syntax

SET OPTION setzt verschiedene Optionen, die die Arbeitsweise des Servers oder Ihrer Clients beeinflussen. Jede Option, die Sie setzen, bleibt in Kraft, bis die aktuelle Sitzung beendet wird, oder bis Sie die Option auf einen anderen Wert setzen.

characterset zeichensatz_name | DEFAULT

Das mappt alle Zeichenketten von und zum Client auf das angegebene Mapping. Momentan ist die einzige Option für zeichensatz_name cp1251_koi8, aber Sie können leicht neue Mappings hinzufügen, indem Sie die `sql/convert.cc'-Datei in der MySQL-Quelldistribution editieren. Das vorgabemäßige Mapping kann durch Setzen des zeichensatz_name-Werts auf DEFAULT wieder hergestellt werden. Beachten Sie, dass sich die Syntax für das Setzen der characterset-Option von der Syntax für das Setzen anderer Optionen unterscheidet.

PASSWORD = PASSWORD('ein_passwort')

Setzt das Passwort für den aktuellen Benutzer. Jeder nicht anonyme Benutzer kann sein eigenes Passwort ändern!

PASSWORD FOR benutzer = PASSWORD('ein_passwort')

Setzt das Passwort für einen bestimmten Benutzer auf dem aktuellen Server-Host. Das kann nur ein Benutzer mit Zugriff auf die mysql-Datenbank tun. Der Benutzer sollte im user@hostname-Format eingegeben werden, wobei user und hostname exakt so sind, wie sie in den User- und Host-Spalten des mysql.user-Tabelleneintrags aufgelistet sind. Wenn Sie zum Beispiel in den Spalten User und Host die Einträge 'bob' und '%.loc.gov' haben wollen, schreiben Sie: mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass"); oder mysql> UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' und host="%.loc.gov";

SQL_AUTO_IS_NULL = 0 | 1

Falls auf 1 gesetzt (Vorgabe), wird mit folgendem Konstrukt die letzte eingefügte Zeile einer Tabelle mit einer auto_increment-Zeile gefunden: WHERE auto_increment_spalte IS NULL. Das wird von einigen ODBC-Programme wie Access benutzt.

AUTOCOMMIT= 0 | 1

Falls auf 1 gesetzt, werden alle Änderungen einer Tabelle auf einmal durchgeführt. Um eine Transaktion aus mehreren Befehlen anzufangen, müssen Sie das BEGIN-Statement benutzen. See section 7.7.1 BEGIN/COMMIT/ROLLBACK-Syntax. Falls auf 0 gesetzt, müssen Sie COMMIT / ROLLBACK benutzen, um diese Transaktion zu akzeptieren / zu widerrufen. See section 7.7.1 BEGIN/COMMIT/ROLLBACK-Syntax. Beachten Sie, dass MySQL nach dem Umschalten vom AUTOCOMMIT-Modus zum AUTOCOMMIT-Modus ein automatisches COMMIT auf alle offenen Transaktionen durchführt.

SQL_BIG_TABLES = 0 | 1

Falls auf 1 gesetzt, werden alle temporären Tabellen auf Platte statt im Arbeitsspeicher gespeichert. Das ist etwas langsamer, aber Sie erhalten nicht den Fehler The table tabelle is full, wenn Sie große SELECT-Operationen ausführen, die eine große temporäre Tabelle erfordern. Der Vorgabewert für eine neue Verbindung ist 0 (das heißt, temporäre Tabellen im Arbeitsspeicher benutzen).

SQL_BIG_SELECTS = 0 | 1

Falls auf 0 gesetzt, bricht MySQL ab, wenn ein SELECT versucht wird, das wahrscheinlich sehr lange dauern wird. Das ist nützlich, wenn ein unratsames WHERE-Statement abgesetzt wurde. Ein große Anfrage ist definiert als ein SELECT, das wahrscheinlich mehr als max_join_size Zeilen untersuchen muss. Der Vorgabewert für eine neue Verbindung ist 1 (was alle SELECT-Statements zuläßt).

SQL_BUFFER_RESULT = 0 | 1

SQL_BUFFER_RESULT erzwingt, dass das Ergebnis von SELECT's in eine temporäre Tabelle geschrieben wird. Das hilft MySQL, die Tabellensperren frühzeitig aufzuheben, und ist hilfreich in Fällen, wo es lange dauert, das Ergebnis an den Client zu senden.

SQL_LOW_PRIORITY_UPDATES = 0 | 1

Falls auf 1 gesetzt, warten alle INSERT-, UPDATE-, DELETE- und LOCK TABLE WRITE-Statements, bis es kein anhängiges SELECT oder LOCK TABLE READ für die betroffene Tabelle gibt.

SQL_MAX_JOIN_SIZE = wert | DEFAULT

Nicht zulassen, dass SELECTs, die wahrscheinlich mehr als value Zeilenkombinationen untersuchen müssen, ausgeführt werden. Wenn Sie diesen Wert setzen, können Sie SELECTs abfangen, bei denen Schlüssel nicht korrekt verwendet werden und die wahrscheinlich sehr lange dauern. Wenn dieser Wert auf etwas anderes als DEFAULT gesetzt wird, wird der SQL_BIG_SELECTS-Flag zurückgesetzt. Wenn Sie den SQL_BIG_SELECTS-Flag wieder setzen, wird die SQL_MAX_JOIN_SIZE-Variable ignoriert. Sie können für diese Variable einen Vorgabewert setzen, wenn Sie mysqld mit -O max_join_size=# starten.

SQL_SAFE_UPDATES = 0 | 1

Falls auf 1 gesetzt, bricht MySQL ab, wenn ein UPDATE oder DELETE versucht wird, das keinen Schlüssel oder kein LIMIT in der WHERE-Klausel benutzt. Das ermöglicht das Abfangen falscher Aktualisierungen, wenn SQL-Befehle von Hand eingegeben werden.

SQL_SELECT_LIMIT = wert | DEFAULT

Die maximale Anzahl von Datensätzen, die von SELECT-Statements zurückgegeben werden. Wenn ein SELECT eine LIMIT-Klausel hat, hat das LIMIT Vorrang vor dem Wert von SQL_SELECT_LIMIT. Der Vorgabewert für eine neue Verbindung ist ``unbegrenzt.'' Wenn Sie diese Begrenzung geändert haben, kann der Vorgabewert wieder hergestellt werden, indem Sie einen SQL_SELECT_LIMIT-Wert von DEFAULT verwenden.

SQL_LOG_OFF = 0 | 1

Falls auf 1 gesetzt, wird für diesen Client kein Loggen ins Standard-Log durchgeführt, wenn der Client die process-Berechtigung hat. Das betrifft nicht die Update-Log-Datei!

SQL_LOG_UPDATE = 0 | 1

Falls auf 0 gesetzt, wird für diesen Client kein Loggen in die Update-Log-Datei durchgeführt, wenn der Client die process-Berechtigung hat. Das betrifft nicht das Standard-Log!

SQL_QUOTE_SHOW_CREATE = 0 | 1

Falls auf 1 gesetzt, setzt SHOW CREATE TABLE Tabellen- und Spaltennamen in Anführungszeichen. Das ist vorgabemäßig angeschaltet, damit Replikation von Tabellen mit merkwürdigen Spaltennamen funktioniert. section 5.5.5.8 SHOW CREATE TABLE.

TIMESTAMP = zeitstempel_wert | DEFAULT

Setzt die Zeit für diesen Client. Das wird benutzt, um den Original-Zeitstempel zu erhalten, wenn sie die Update-Log-Datei benutzen, um Zeilen wiederherzustellen. zeitstempel_wert sollte ein UNIX-Epoche-Zeitstempel sein, kein MySQL-Zeitstempel.

LAST_INSERT_ID = #

Setzt den Wert, der von LAST_INSERT_ID() zurückgegeben wird. Dieser wird in der Update-Log-Datei gespeichert, wenn Sie LAST_INSERT_ID() in einem Befehl benutzen, der eine Tabelle aktualisiert.

INSERT_ID = #

Setzt den Wert, der von einem folgenden INSERT- oder ALTER TABLE-Befehl benutzt wird, wenn ein AUTO_INCREMENT-Wert eingefügt wird. Das wird hauptsächlich zusammen mit der Update-Log-Datei benutzt.

6.6 Festplatte, Anmerkungen

• Wie bereits erwähnt sind Suchvorgänge auf der Festplatte ein großer Performance-Flaschenhals. Die Probleme werden mehr und mehr deutlich, wenn die Datenmenge wächst, so dass effizientes Caching unmöglich wird. Bei großen Datenbanken, in denen Sie auf Daten mehr oder weniger zufällig zugreifen, können Sie sicher davon ausgehen, dass Sie zumindest eine Plattenzugriff brauchen, um zu lesen, und eine Reihe weiterer Plattenzugriffe, um Dinge zu schreiben. Um dieses Problem zu minimieren, benutzen Sie Platten mit geringen Zugriffszeiten!
• Erhöhen Sie die Anzahl verfügbarer Festplattenscheiben (und verringern Sie dadurch den Such-Overhead), indem Sie entweder Dateien auf andere Platten symbolisch verknüpfen (SymLink) oder die Platten 'stripen'.

  Using Symbolische Links

  Das bedeutet, dass Sie die Index- und / oder Daten-Datei(en) aus dem normalen Daten-Verzeichnis auf eine andere Festplatte verknüpfen (die auch 'gestriped' sein kann). Das macht sowohl den Suchvorgang als auch die Lesezeiten besser (wenn die Platten nicht für andere Dinge benutzt werden). See section 6.6.1 Symbolische Links benutzen.

  Stripen

  'Stripen' heißt, dass Sie viele Festplatten haben und den ersten Block auf die erste Platte legen, den zweiten Block auf die zweite Platte und den n-ten Block auf die n-te Platte usw. Das bedeutet, wenn Ihre normale Datengröße weniger als die Stripe-Größe ist (oder perfekt passt), dass Sie wesentlich bessere Performance erhalten. Beachten Sie, dass Stripen sehr stark vom Betriebssystem und von der Stripe-Größe abhängig ist. Machen Sie Benchmark-Tests Ihrer Applikation mit unterschiedlichen Stripe-Größen. See section 6.1.5 Wie Sie Ihre eigenen Benchmarks benutzen. Beachten Sie, dass der Geschwindigkeitsunterschied für das Stripen sehr stark vom Parameter abhängig ist. Abhängig davon, wie Sie den Stripe-Parameter setzen und von der Anzahl von Festplatten erhalten Sie Unterschiede in der Größenordnung von Faktoren. Beachten Sie, dass Sie entscheiden müssen, ob Sie für zufällige oder sequentielle Zugriffe optimieren.

• Aus Gründen der Zuverlässigkeit sollten sie vielleicht RAID 0 + 1 nehmen (Stripen + Spiegeln), doch in diesem Fall brauchen Sie 2 * n Laufwerke, um n Datenlaufwerke zu haben. Das ist wahrscheinlich die beste Option, wenn Sie genug Geld dafür haben! Sie müssen jedoch eventuell zusätzlich in Software für die Verwaltung von Volumes investieren, um das effizient zu handhaben.
• Eine gute Option ist es, nicht ganz so wichtige Daten (die wieder hergestellt werden können) auf RAID-0-Platten zu halten, während wirklich wichtige Daten (wie Host-Informationen und Log-Dateien) auf einer RAID-0+1- oder RAID-N-Platte gehalten werden. RAID-N kann ein Problem darstellen, wenn Sie viele Schreibzugriffe haben, weil Zeit benötigt wird, die Paritätsbits zu aktualisieren.
• Sie können auch den Parameter für das Dateisystem setzen, das die Datenbank benutzt. Eine einfache Änderung ist, das Dateisystem mit der noatime-Option zu mounten. Das bringt es dazu, das Aktualisieren der letzten Zugriffszeit in der Inode zu überspringen und vermeidet dadurch einige Platten-Suchzugriffe.
• Unter Linux können Sie viel mehr Performance erhalten (bis zu 100% unter Last ist nicht ungewöhnlich), wenn Sie hdpram benutzen, um die Schnittstelle Ihrer Festplatte zu konfigurieren! Das folgende Beispiel sollte recht gute hdparm-Optionen für MySQL (und wahrscheinlich viele andere Applikationen) darstellen: hdparm -m 16 -d 1 Beachten Sie, dass Performance und Zuverlässigkeit beim oben Genannten von Ihrer Hardware abhängen, daher empfehlen wir sehr, dass Sie Ihr System gründlich testen, nachdem Sie hdparm benutzt haben! Sehen Sie in der Handbuchseite (ManPage) von hdparm nach weiteren Informationen! Wenn hdparm nicht vernünftig benutzt wird, kann das Ergebnis eine Beschädigung des Dateisystems sein. Machen Sie eine Datensicherung von allem, bevor Sie experimentieren!
• Auf vielen Betriebssystemen können Sie die Platten mit dem 'async'-Flag mounten, um das Dateisystem auf asynchrone Aktualisierung zu setzen. Wenn Ihr Computer ausreichend stabil ist, sollte Ihnen das mehr Performance geben, ohne zu viel Zuverlässigkeit zu opfern. (Dieser Flag ist unter Linux vorgabemäßig angeschaltet.)
• Wenn Sie nicht wissen müssen, wann auf eine Datei zuletzt zugegriffen wurden (was auf einem Datenbank-Server nicht wirklich nötig ist), können Sie Ihr Dateisystem mit dem noatime-Flag mounten.

6.6.1 Symbolische Links benutzen

Sie können Tabellen und Datenbanken vom Datenbank-Verzeichnis an andere Stellen verschieben und sie mit symbolischen Links auf neue Speicherorte ersetzen. Das könnten Sie zum Beispiel tun, um eine Datenbank auf ein Dateisystem mit mehr freiem Speicherplatz zu verlagern oder um die Geschwindigkeit Ihres System durch Verteilen Ihrer Tabellen auf unterschiedliche Platten zu steigern.

Die empfohlene Art, das zu tun, ist, nur Datenbanken auf unterschiedliche Platten per SymLink zu verknüpfen, und das bei Tabellen nur im Notfall zu tun.

6.6.1.1 Benutzung symbolischer Links für Datenbanken

Um eine Datenbank per SymLink zu verknüpfen, legt man zuerst ein Verzeichnis auf einer Platte mit freiem Speicherplatz an und erzeugt dann einen SymLink vom MySQL-Datenbank-Verzeichnis aus darauf:

MySQL unterstützt nicht das Verknüpfen eines Verzeichnisses zu mehrfachen Datenbanken. Wenn Sie ein Datenbank-Verzeichnis mit einem symbolischen Link ersetzen, funktioniert das solange gut, wie Sie keinen symbolischen Link zwischen Datenbanken machen. Angenommen, Sie haben eine Datenbank datenbank1 unter dem MySQL-Daten-Verzeichnis und machen dann einen Symlink datenbank2, der auf datenbank1 zeigt:

Jetzt erscheint für jede Tabelle tabelle_a in datenbank1 auch eine Tabelle tabelle_a in datenbank2. Wenn ein Thread datenbank1.tabelle_a aktualisiert und ein anderer Thread datenbank2.tabelle_a aktualisiert, gibt es Probleme.

Wenn Sie das wirklich brauchen, müssen Sie folgenden Code in `mysys/mf_format.c' ändern:

zu:

Unter Windows können Sie interne symbolische Links auf Verzeichnisse benutzen, indem Sie MySQL mit -DUSE_SYMDIR kompilieren. Das erlaubt Ihnen, verschiedene Datenbanken auf verschiedene Platte zu legen. See section 3.6.2.5 Daten auf verschiedenen Platten unter Win32 aufteilen.

6.6.1.2 Benutzung symbolischer Links für Tabellen

Vor MySQL 4.0 konnten Sie Tabellen nicht per SymLink verknüpfen, wenn Sie nicht sehr sorgfältig dabei vorgingen. Das Problem liegt darin, dass bei ALTER TABLE, REPAIR TABLE oder OPTIMIZE TABLE auf eine per Symlink verknüpfte Datei die SymLinks entfernt und durch die Original-Dateien verknüpft werden. Das geschieht, weil beim obigen Befehl eine temporäre Datei im Datenbank-Verzeichnis erzeugt wird, und wenn der Befehl ausgeführt ist, die Original-Datei durch die temporäre Datei ersetzt wird.

Sie sollten Tabellen auf Systemen, die keinen vollständig funktionierenden realpath()-Aufruf haben, nicht per SymLink verknüpfen. (Zumindest Linux und Solaris unterstützen realpath().)

In MySQL 4.0 werden Symlinks nur für MyISAM-Tabellen vollständig unterstützt. Bei anderen Tabellentypen erhalten Sie wahrscheinlich merkwürdige Probleme, wenn Sie einen der obigen Befehle ausführen.

Die Handhabung symbolischer Links in MySQL 4.0 funktioniert auf folgende Art (das gilt meist nur für MyISAM-Tabellen):

• Im Daten-Verzeichnis liegen immer die Tabellendefinitionsdatei und die Daten-/Index-Dateien.
• Sie können die Index-Datei und die Daten-Datei unabhängig voneinander auf unterschiedliche Verzeichnisse per SymLink verknüpfen.
• Das Erzeugen der SymLinks kann durch das Betriebssystem (wenn mysqld nicht läuft) oder mit dem INDEX/DATA directory="pfad-zum-verzeichnis"-Befehl in CREATE TABLE durchgeführt werden. See section 7.5.3 CREATE TABLE-Syntax.
• myisamchk ersetzt keinen Symlink mit der Index-/Datendatei, sondern arbeitet direkt mit den Dateien, auf die die SymLinks verweisen. Jegliche temporäre Dateien werden im selben Verzeichnis erzeugt, wo die Daten-/Index-Datei ist.
• Wenn Sie eine Tabelle löschen, die Symlinks benutzt, werden sowohl der Symlink als auch die Datei, auf die der SymLink zeigt, gelöscht. Das ist ein guter Grund dafür, mysqld NICHT als Root laufen zu lassen und niemandem zu erlauben, Schreibzugriff auf die MySQL-Datenbankverzeichnisse zu haben.
• Wenn Sie eine Tabelle mit ALTER TABLE RENAME umbenennen und nicht die Datenbank ändern, wird der Symlink im Datenbank-Verzeichnis auf den neuen Namen umbenannt und die Daten-/Index-Datei wird entsprechend umbenannt.
• Wenn Sie ALTER TABLE RENAME benutzen, um eine Tabelle in eine andere Datenbank zu verschieben, wird die Tabelle in das andere Datenbank-Verzeichnis verschoben und die alten SymLinks und die Dateien, auf die sie zeigen, werden gelöscht.
• Wenn Sie keine Symlinks benutzen, sollten Sie die --skip-symlink-Option für mysqld benutzen, damit niemand eine Datei ausserhalb des mysqld Daten-Verzeichnisses löschen oder umbenennen kann.

Dinge, die noch nicht unterstützt werden:

• ALTER TABLE ignoriert alle INDEX/DATA directory="pfad"-Optionen.
• CREATE TABLE berichtet nicht, wenn eine Tabelle symbolische Links hat.
• mysqldump gibt die Information über symbolische Links nicht in der Ausgabe aus.
• BACKUP TABLE und RESTORE TABLE respektieren keine symbolischen Links.

Go to the first, previous, next, last section, table of contents.