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.
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:
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.
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.
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.
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):
| 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 |
| 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:
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.
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.
SELECTs und andere Anfragen optimierenZunä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.
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
type
possible_keys
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
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
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
ref-Spalte zeigt an, welche Spalten oder Konstanten beim
key benutzt werden, um Zeilen aus der Tabelle auszuwählen.
rows
rows-Spalte gibt die Anzahl von Zeilen an, von denen MySQL
annimmt, dass es sie untersuchen muss, um die Anfrage auszuführen.
Extra
Distinct
Not exists
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: #)Using filesort
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
Using temporary
ORDER
BY auf eine andere Spalte setzen als auf die, die Sie im GROUP BY
angegeben haben.
Where used
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).
Using filesort und Using temporary achten.
Die verschiedenen Join-Typen sind unten aufgeführt, sortiert vom besten zum schlechtesten Typ:
system
const-Join-Typs.
const
const-Tabellen sind sehr schnell, weil sie nur einmal
gelesen werden!
eq_ref
const-Typen. Er wird benutzt, wenn alle Teile eines Indexes vom Join
benutzt werden und der Index UNIQUE oder ein PRIMARY KEY ist.
ref
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
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
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
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:
| Tabelle | Spalte | Spaltentyp |
tt | ActualPC | CHAR(10)
|
tt | AssignedPC | CHAR(10)
|
tt | ClientID | CHAR(10)
|
et | EMPLOYID | CHAR(15)
|
do | CUSTNMBR | CHAR(15)
|
| Tabelle | Index |
tt | ActualPC
|
tt | AssignedPC
|
tt | ClientID
|
et | EMPLOYID (primary key)
|
do | CUSTNMBR (primary key)
|
tt.ActualPC-Werte sind nicht gleichmäßig verteilt.
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.
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.
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:
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.
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!
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:
((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)
(a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
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.
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.
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.
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.
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
ORDER BY und
in GROUP BY aus derselben Tabelle stammen, wird diese Tabelle
vorzugsweise vorn hingestellt, wenn verknüpft wird.
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.
SQL_SMALL_RESULT benutzen, benutzt MySQL eine temporäre
Tabelle im Arbeitsspeicher.
HAVING-Klausel übereinstimmen, übersprungen.
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,... ;
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.
LEFT JOIN optimiert
A LEFT JOIN B ist in MySQL wie folgt implementiert:
B wird als abhängig von Tabelle A und allen
Tabellen, von denen A abhängig ist, gesetzt.
A wird als abhängig von allen Tabellen (ausser B)
gesetzt, die in der LEFT JOIN-Bedingung aufgeführt sind.
LEFT JOIN-Bedingungen werden zu WHERE-Klausel
verschoben.
WHERE-Optimierungen werden durchgeführt.
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.
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:
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
LIMIT optimiert
In einigen Fällen handhabt MySQL die Anfrage unterschiedlich, wenn Sie
LIMIT # statt HAVING benutzen:
LIMIT auswählen, benutzt MySQL
in einigen Fällen Indexe, wenn er ansonsten vorzugsweise einen
vollständigen Tabellenscan durchführen würde.
LIMIT # mit ORDER BY benutzen, beendet MySQL das
Sortieren, sobald er die ersten # Zeilen gefunden hat, anstatt die
gesamte Tabelle zu sortieren.
LIMIT # mit DISTINCT kombinieren, hört MySQL auf,
sobald er # eindeutige Zeilen gefunden hat.
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.
# 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.
LIMIT #, um zu berechnen,
wieviel Platz benötigt wird, um die Anfrage aufzulösen.
INSERT-AnfragenDie 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:
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.
INSERT
DELAYED-Statement benutzen. See section 7.4.3 HANDLER-Syntax.
MyISAM-Tabellen Zeilen zur selben Zeit
einfügen können, zu der SELECTs laufen, wenn es keine gelöschten
Zeilen in den Tabellen gibt.
LOAD DATA INFILE. Das ist üblicherweise 20 mal schneller als
viele INSERT-Statements zu benutzen. See section 7.4.9 LOAD DATA INFILE-Syntax.
LOAD DATA INFILE noch
schneller laufen zu lassen, wenn die Tabelle viele Indexe hat. Gehen Sie
wie folgt vor:
CREATE TABLE, zum Beispiel mit
mysql oder über die Perl-DBI.
FLUSH TABLES-Statement oder den Shell-Befehl
mysqladmin flush-tables aus.
myisamchk --keys-used=0 -rq /pfad/zu/db/tabelle ein.
Dadurch entfernen Sie die Benutzung aller Indexe von der Tabelle.
LOAD DATA INFILE ein. Dadurch
werden keine Indexe aktualisiert, was deswegen sehr schnell läuft.
myisampack, um sie kleiner zu machen. See section 8.1.2.3 Kennzeichen komprimierter Tabellen.
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.
FLUSH TABLES-Statement oder den Shell-Befehl
mysqladmin flush-tables aus.
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.
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.
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.
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.
Ungeordnete Liste von Tipps für schnellere Systeme:
Thread_cache_size-Variablen ändern.
See section 6.5.2 Serverparameter tunen.
EXPLAIN-Befehl tun. See section 6.2.1 EXPLAIN-Syntax (Informationen über ein SELECT erhalten).
SELECT-Anfragen auf Tabellen zu vermeiden,
die viel aktualisiert werden, um Probleme mit Tabellensperren zu vermeiden.
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.
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.
SELECT * FROM tabelle WHERE hash=MD5(concat(spalte1,spalte2))
AND spalte_1='constant' AND spalte_2='constant'
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.
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.
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.
INSERT /*! LOW_PRIORITY */, wenn Sie wollen, dass Ihre
Selects höhere Priorität haben.
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.
INSERT-Statement, um viele Zeilen mit
einem SQL-Befehl zu speichern (viele SQL-Server unterstützen das).
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.
AUTO_INCREMENT-Spalten, um eindeutige Werte zu
erzeugen.
OPTIMIZE TABLE, um Fragmentierungen zu
vermeiden, wenn Sie das dynamische Tabellenformat verwenden.
See section 5.5.1 OPTIMIZE TABLE-Syntax.
HEAP-Tabellen, um mehr Geschwindigkeit
zu erzielen. See section 8 MySQL-Tabellentypen.
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.
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.
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.
Im Anhang finden Sie eine Erörterung zu den unterschiedlichen Sperrmethoden. See section D.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.
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:
SELECT aus, das lange Zeit läuft.
UPDATE auf die benutzte Tabelle
aus. Dieser Client wartet, bis das SELECT fertig ist.
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!
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:
SELECT-Statements schneller ablaufen zu lassen.
Hierfür müssen Sie eventuell Zusammenfassungstabellen erzeugen.
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.
INSERT-, UPDATE- oder
DELETE-Statement mit dem LOW_PRIORITY-Attribut geringere
Priorität geben.
mysqld mit einem niedrigen Wert für
max_write_lock_count, um READ-Sperren nach einer bestimmten
Anzahl von WRITE-Sperren zu ermöglichen.
SET SQL_LOW_PRIORITY_UPDATES=1 benutzen. See section 6.5.6 SET-Syntax.
HIGH_PRIORITY-Attribut festlegen, dass ein
bestimmtes SELECT sehr wichtig ist. See section 7.4.1 SELECT-Syntax.
INSERT in Kombination mit SELECT haben,
stellen Sie auf die neuen MyISAM-Tabellen um, weil diese
gleichzeitige SELECTs und INSERTs unterstützen.
INSERT- und SELECT-Statements mischen,
wird das DELAYED-Attribut für INSERT wahrscheinlich Ihre
Probleme lösen. See section 7.4.3 HANDLER-Syntax.
SELECT und DELETE haben, mag die
LIMIT-Option für DELETE helfen. See section 7.4.6 DELETE-Syntax.
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:
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:
MEDIUMINT zum Beispiel ist oft besser als
INT.
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.
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.
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:
WHERE-Klausel
übereinstimmen.
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
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;
SELECT schluessel_teil3 FROM tabelle WHERE schluessel_teil1=1
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:
LIMIT benutzt, um nur ein paar Zeilen abzufragen, weil er
dann schneller die wenigen Zeilen im Ergebnis finden kann.
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.
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.
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.
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.
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.
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.
--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.
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.
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:
pgcc benutzen und alles mit -O6 kompilieren, ist der
mysqld-Server 1% schneller als mit gcc 2.95.2.
-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.
mysqld-Binärdatei mit strip libexec/mysqld
strippen, ist die resultierende Binärdatei bis zu 4% schneller.
localhost
verbinden, benutzt MySQL vorgabemäßig Sockets.)
--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.
gcc 2.95.2.
gcc 2.95.2 für ultrasparc mit der Option
-mcpu=v8 -Wa,-xarch=v8plusa ergibt 4% mehr Performance.
--log-bin macht MySQL 1% langsamer.
-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.
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.
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.
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.
record_buffer).
record_rnd_buffer).
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.
malloc() und free() gemacht).
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.
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.
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.
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.
SET-SyntaxSET [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
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')
PASSWORD FOR benutzer = PASSWORD('ein_passwort')
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
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
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
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
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
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
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
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
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
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
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
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
zeitstempel_wert sollte ein
UNIX-Epoche-Zeitstempel sein, kein MySQL-Zeitstempel.
LAST_INSERT_ID = #
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 = #
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.
hdparm -m 16 -d 1Beachten 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!
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.
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.
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):
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.
mysqld NICHT als Root laufen zu lassen und
niemandem zu erlauben, Schreibzugriff auf die MySQL-Datenbankverzeichnisse
zu haben.
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.
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.
--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.