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


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:

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):

2.000.000 Zeilen vom Index lesen
Sekunden Sekunden
mysql 367 249
mysql_odbc 464
db2_odbc 1206
informix_odbc 121126
ms-sql_odbc 1634
oracle_odbc 20800
solid_odbc 877
sybase_odbc 17614
350.768 Zeilen einfügen
Sekunden Sekunden
mysql 381 206
mysql_odbc 619
db2_odbc 3460
informix_odbc 2692
ms-sql_odbc 4012
oracle_odbc 11291
solid_odbc 1801
sybase_odbc 4802

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:

cd sql-bench
perl run-all-tests --server=#

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:

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:

mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

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
oder EXPLAIN SELECT select_optionen

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:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

Nehmen wir bei diesem Beispiel folgendes an:

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

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)

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:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

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

table type   possible_keys   key     key_len rew         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    where used
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1

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:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

Jetzt ergibt EXPLAIN folgende Ausgabe:

table type   possible_keys   key     key_len rew            rows     Extra
et    ALL    PRIMARY         NULL    NULL    NULL           74
tt    rew    AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

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:

shell> myisamchk --analyze PFAD_ZU_MYSQL_DATENBANK/tt
shell> mysqladmin refresh

Jetzt ist der Join perfekt und EXPLAIN ergibt dieses Ergebnis:

table type   possible_keys   key     key_len ref            rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL    3872    where used
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC    1
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

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:

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:

Einige Beispiele von Anfragen, die sehr schnell sind:

mysql> SELECT COUNT(*) FROM tabelle;
mysql> SELECT MIN(schluessel_teil1),MAX(schluessel_teil1) FROM tabelle;
mysql> SELECT MAX(schluessel_teil2) FROM tabelle
           WHERE schluessel_teil_1=konstante;
mysql> SELECT ... FROM tabelle
           ORDER BY schluessel_teil1,schluessel_teil2,... LIMIT 10;
mysql> SELECT ... FROM tabelle
           ORDER BY schluessel_teil1 DESC,schluessel_teil2 DESC,... LIMIT 10;

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

mysql> SELECT schluessel_teil1,schluessel_teil2 FROM tabelle WHERE schluessel_teil1=val;
mysql> SELECT COUNT(*) FROM tabelle
           WHERE schluessel_teil1=val1 AND schluessel_teil2=val2;
mysql> SELECT schluessel_teil2 FROM tabelle GROUP BY schluessel_teil1;

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

mysql> SELECT ... FROM tabelle ORDER BY schluessel_teil1,schluessel_teil2,... ;
mysql> SELECT ... FROM tabelle ORDER BY schluessel_teil1 DESC,schluessel_teil2 DESC,... ;

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.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

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:

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:

SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key

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:

SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key

6.2.7 Wie MySQL LIMIT optimiert

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

6.2.8 Geschwindigkeit von INSERT-Anfragen

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

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:

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:

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:

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

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:

Mögliche Lösungen dieses Problems sind:

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:

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:

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:

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

mysql> SELECT * FROM tabelle WHERE spalte1=val1 AND spalte2=val2;

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:

mysql> SELECT * FROM tabelle WHERE spalte1=wert1;
mysql> SELECT * FROM tabelle WHERE spalte2=wert2;
mysql> SELECT * FROM tabelle WHERE spalte2=wert2 AND spalte3=wert3;

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:

mysql> select * from tabelle where schluessel_spalte LIKE "Patrick%";
mysql> select * from tabelle where schluessel_spalte LIKE "Pat%_ck%";

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:

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:

KEY index_name (spalten_name(laenge))

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

mysql> CREATE TABLE test (
           name CHAR(200) NOT NULL,
           KEY index_name (name(10)));

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:

mysql> CREATE TABLE test (
           id INT NOT NULL,
           nachname CHAR(30) NOT NULL,
           vorname CHAR(30) NOT NULL,
           PRIMARY KEY (id),
           INDEX name (nachname,vorname));

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:

mysql> SELECT * FROM test WHERE nachname="Widenius";

mysql> SELECT * FROM test WHERE nachname="Widenius"
                          AND vorname="Michael";

mysql> SELECT * FROM test WHERE nachname="Widenius"
                          AND (vorname="Michael" OR vorname="Monty");

mysql> SELECT * FROM test WHERE nachname="Widenius"
                          AND vorname >="M" AND vorname < "N";

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

mysql> SELECT * FROM test WHERE vorname="Michael";

mysql> SELECT * FROM test WHERE nachname="Widenius"
                          OR vorname="Michael";

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:

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:

Uptime: 426 Running Threads: 1 Questions: 11082 Reloads: 1 Open Tables: 12

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:

6.5.2 Serverparameter tunen

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

shell> mysqld --help

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

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
bdb_cache_size        current value: 1048540
binlog_cache_size     current_value: 32768
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 0
interactive_timeout   current value: 28800
join_buffer_size      current value: 131072
key_buffer_size       current value: 1048540
lower_case_tabelles  current value: 0
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_binlog_cache_size current_value: 4294967295
max_connections       current value: 100
max_connect_errors    current value: 10
max_delayed_threads   current value: 20
max_heap_table_size   current value: 16777216
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
max_tmp_tables        current value: 32
max_write_lock_count  current value: 4294967295
myisam_sort_buffer_size  current value: 8388608
net_buffer_length     current value: 16384
net_retry_count       current value: 10
net_read_timeout      current value: 30
net_write_timeout     current value: 60
query_buffer_size     current value: 0
record_buffer         current value: 131072
record_rnd_buffer     current value: 131072
slow_launch_time      current value: 2
sort_buffer           current value: 2097116
table_cache           current value: 64
thread_concurrency    current value: 10
tmp_table_size        current value: 1048576
thread_stack          current value: 131072
wait_timeout          current value: 28800

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:

shell> mysqladmin variables

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:

shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
           -O sort_buffer=4M -O record_buffer=1M &

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

shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M

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

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O record_buffer=100k &

Oder sogar:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

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:

shell> mysqld -O key_buffer=32m --help

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:

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.

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] SQL_VALUE_OPTION= wert, ...

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

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:

shell> mkdir /dr1/datenbanken/test
shell> ln -s /dr1/datenbanken/test mysqld-datadir

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:

shell> cd /pfad/zu/datadir
shell> ln -s datenbank1 datenbank2

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:

if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

zu:

if (1)

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):

Dinge, die noch nicht unterstützt werden:


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