Wenn Sie die MyISAM-Speicher-Engine verwenden, benutzt MySQL eine extrem schnelle Tabellensperrung, die mehrere lesende Benutzer oder einen schreibenden Benutzer gestattet. Das größte Problem bei dieser Speicher-Engine tritt auf, wenn ein konstanter Strom von gemischten Änderungs- und langsamen Auswahlanweisungen für eine einzelne Tabelle vorhanden ist. Wenn dies bei bestimmten Tabellen ein Problem darstellt, können Sie für diese eine andere Speicher-Engine verwenden. Siehe auch Kapitel 14, Speicher-Engines und Tabellentypen.

MySQL kann sowohl mit transaktionssicheren als auch mit nichttransaktionssicheren Tabellen arbeiten. Um die reibungslose Arbeit mit nichttransaktionssicheren Tabellen (also Tabellen, für die im Fehlerfall kein Rollback durchgeführt werden kann) zu erleichtern, unterstützt MySQL die folgenden Regeln. Beachten Sie, dass diese Regeln nur dann gültig sind, wenn MySQL nicht im strikten SQL-Modus ausgeführt wird oder Sie IGNORE für INSERT- oder UPDATE-Anweisungen angegeben haben.

Um obiges Verhalten zu ändern, können Sie eine striktere Datenverarbeitung aktivieren, indem Sie den SQL-Modus auf dem Server entsprechend ändern. Weitere Informationen zum Umgang mit Daten finden Sie in Abschnitt 1.9.6, „Wie MySQL mit Constraints umgeht“, Abschnitt 5.2.5, „Der SQL-Modus des Servers“, und Abschnitt 13.2.4, „INSERT“.

Da alle SQL-Server unterschiedliche Teile des SQL-Standards implementieren, ist die Entwicklung portierbarer Datenbankanwendungen sehr aufwändig. Bei sehr simplen Auswahl- und Einfügeoperationen mag die einfache Portierbarkeit noch gegeben sein, aber je mehr Funktionalitäten Sie benötigen, umso komplexer wird sie. Wenn Sie eine Anwendung brauchen, die auf vielen verschiedenen Datenbanksystemen schnell ist, wird es noch schwieriger.

Alle Datenbanksysteme haben Schwachpunkte: Entwicklungsseitig enthalten sie Kompromisse, die ein unterschiedliches Verhalten verursachen.

Um eine komplexe Anwendung portierbar zu machen, müssen Sie zunächst ermitteln, auf welchen SQL-Servern sie laufen muss, und dann bestimmen, welche Funktionen diese Server unterstützen. Sie können mit dem MySQL-Programm crash-me Funktionen, Typen und Einschränkungen ermitteln, die für eine Auswahl von Datenbankservern gelten. crash-me prüft nicht alle denkbaren Funktionen, ist aber mit etwa 450 Tests vergleichsweise umfassend. Ein Beispiel für die Art von Informationen, die sich mit crash-me ermitteln lassen, ist etwa die Tatsache, dass Sie keine Spaltennamen verwenden sollten, die mehr als 18 Zeichen umfassen, wenn Sie Informix oder DB2 einsetzen wollen.

Das Programm crash-me und die MySQL-Benchmarks hängen stark von der jeweiligen Datenbank ab. Sehen Sie sich einmal an, wie sie geschrieben sind, um ein Gefühl dafür erhalten, was Sie tun müssen, um Ihre eigenen Anweisungen so datenbankunabhängig wie möglich zu machen. Sie finden die Programme im Verzeichnis sql-bench der MySQL-Quelldistributionen. Sie sind in Perl geschrieben und verwenden die DBI-Datenbankschnittstelle. Bereits die Verwendung von DBI löst das Portabilitätsproblem teilweise, denn die Schnittstelle bietet datenbankübergreifende Zugriffsmethoden. Siehe auch Abschnitt 7.1.4, „Die MySQL-Benchmark-Reihe“.

Wenn Sie nach Datenbankunabhängigkeit streben, brauchen Sie ein gutes Gespür für die Engpässe auf den einzelnen SQL-Servern. So ist MySQL beispielsweise sehr schnell, wenn es um das Abrufen und Aktualisieren von Datensätzen in MyISAM-Tabellen geht, hat aber Probleme bei Fällen, in denen gleichzeitig langsame Lese- und Schreibvorgänge verwaltet werden müssen. Oracle andererseits zeigt sich ausgesprochen widerspenstig, wenn Sie auf Datensätze zugreifen wollen, die Sie kürzlich aktualisiert (und noch nicht auf Festplatte synchronisiert) haben. Transaktionssichere Datenbanksysteme sind generell nicht besonders gut zur Erstellung von Zusammenfassungstabellen aus Logtabellen geeignet, weil in diesem Fall die Sperrung von Datensätzen praktisch nutzlos ist.

Um Ihre Anwendung wirklich datenbankunabhängig zu machen, sollten Sie eine leicht zu erweiternde Schnittstelle definieren, über die Sie Ihre Daten manipulieren. C++ beispielsweise ist auf den meisten Systemen verfügbar, weswegen es sinnvoll ist, eine Datenbankschnittstelle auf Basis einer C++-Klasse zu verwenden.

Wenn Sie die eine oder andere Funktion verwenden, die für ein gegebenes Datenbanksystem spezifisch ist (z. B. die MySQL-Anweisung REPLACE), dann sollten Sie dieselbe Funktion für andere SQL-Server implementieren, indem Sie eine alternative Methode einkodieren. Auch wenn diese Alternative langsamer ist, so erlaubt sie es anderen Servern doch, dieselben Aufgaben auszuführen.

Bei MySQL können Sie mit der Syntax /*! */ MySQL-spezifische Schlüsselwörter zu einer Anweisung hinzufügen. Der Code in /* */ wird von den meisten anderen SQL-Servern als Kommentar betrachtet (und insofern ignoriert). Informationen zum Schreiben von Kommentaren finden Sie in Abschnitt 9.4, „Kommentar“.

Wenn die Leistungsfähigkeit wichtiger ist als die Genauigkeit (wie es beispielsweise bei Webanwendungen der Fall ist), dann können Sie eine Anwendungsschicht erstellen, die alle Ergebnisse zwischenspeichert, um die Performance noch besser zu optimieren. Indem Sie ältere Ergebnisse nach einer bestimmten Zeit ungültig werden lassen, können Sie den Cache ausreichend aktuell halten. Auf diese Weise können Sie Belastungsspitzen abfangen, indem Sie die Cache-Größe dynamisch erhöhen und den Ungültigkeitszeitpunkt nach hinten verschieben, bis sich die Belastung wieder normalisiert hat.

In diesem Fall sollten die Angaben zur Tabellenerstellung Informationen zur ursprünglichen Cache-Größe und dazu enthalten, wie oft die Tabelle normalerweise aktualisiert wird.

Eine interessante Alternative zur Implementierung eines Anwendungs-Caches ist die Verwendung des Abfrage-Caches von MySQL. Wenn Sie den Abfrage-Cache aktivieren, bestimmt der Server selbst, ob ein Abfrageergebnis wiederverwendet werden kann. Dies vereinfacht Ihre Anwendung. Siehe auch Abschnitt 5.14, „MySQL-Anfragen-Cache“.

Dieser Abschnitt beschreibt eine sehr frühe Anwendung für MySQL.

Im Verlauf der ursprünglichen Entwicklung von MySQL wurden die Funktionen an die Anforderungen unseres größten Kunden angepasst, der das Data-Warehousing für einige der großen schwedischen Handelshäuser implementierte.

Von allen Läden erhielten wir wöchentliche Übersichten über alle Kundenkartentransaktionen, und es wurde von uns verlangt, brauchbare Informationen für Geschäftsinhaber bereitzustellen, damit diese feststellen konnten, wie sich ihre Werbekampagnen auf das Kaufverhalten ihrer Kunden auswirkten.

Das Datenvolumen war gewaltig (ca. sieben Millionen Transaktionen pro Monat), und wir hatten Daten für vier bis zehn Jahre, die wir den Benutzern präsentieren sollten. Allwöchentlich erhielten wir Anfragen von unseren Kunden, die sofortigen Zugriff auf neue, auf diesen Daten basierende Berichte benötigten.

Dieses Problem lösten wir, indem wir alle Daten eines Monats in komprimierten „Transaktionstabellen“ speicherten. Wir hatten eine Anzahl einfacher Makros, die aus den Tabellen, in denen die Transaktionen gespeichert waren, nach verschiedenen Kriterien (Produktgruppe, Kundennummer, Filiale usw.) gruppierte Übersichtstabellen erstellten. Diese Berichte waren Webseiten, die mithilfe eines kleinen Perl-Skripts dynamisch erzeugt wurden. Das Skript analysierte eine Webseite, führte die enthaltenen SQL-Anweisungen aus und fügte die Ergebnisse ein. Wir hätten stattdessen sicher PHP oder mod_perl verwendet, aber diese waren seinerzeit noch nicht verfügbar.

Für die grafische Ausgabe schrieben wir ein kleines Tool in C, das SQL-Abfrageergebnisse verarbeiten und auf Basis der Ergebnisse GIF-Grafiken erstellen konnte. Auch dieses Tool wurde von dem Perl-Skript, welches die Webseiten analysierte, dynamisch ausgeführt.

In den meisten Fällen konnte ein neuer Bericht einfach erstellt werden, indem man ein vorhandenes Skript kopierte und die von ihm verwendete SQL-Abfrage einfach anpasste. In manchen Fällen mussten wir neue Spalten zu einer vorhandenen Übersichtstabelle hinzufügen oder eine neue Tabelle erstellen. Auch dies war ganz einfach, weil wir alle Tabellen mit den Transaktionen auf Festplatte speicherten. (In der Summe hatten wir dann 50 Gbyte Transaktionstabellen sowie 200 Gbyte weiterer Kundendaten.)

Außerdem gestatteten wir unseren Kunden via ODBC den direkten Zugriff auf die Übersichtstabellen, sodass Kunden bei Bedarf selbst mit den Daten experimentieren konnten.

Dieses System funktionierte gut, und wir hatten keine Probleme mit der Verwaltung der Daten auf einer eher bescheidenen Hardware (Sun Ultra SPARCstation, 2 × 200 MHz). Am Ende wurde das System dann auf Linux migriert.

Anhand dieser Benchmark-Reihe kann jeder Benutzer ermitteln, welche Operationen eine gegebene SQL-Implementierung gut oder weniger gut ausführt. Sie können sich einen guten Eindruck davon verschaffen, wie die Benchmarks funktionieren, indem Sie sich den Code und die Ergebnisse im Verzeichnis sql-bench einer beliebigen MySQL-Quelldistribution ansehen.

Beachten Sie, dass diese Benchmark nur einen Thread verwendet – sie misst also die Mindestdauer der durchgeführten Operationen. Wir planen, die Benchmark-Reihe in Zukunft durch Multithread-Tests zu ergänzen.

Um die Benchmark-Reihe verwenden zu können, müssen folgende Voraussetzungen erfüllt sein:

Nachdem Sie sich eine MySQL-Quelldistribution besorgt haben, finden Sie die Benchmark-Reihe im dortigen Verzeichnis sql-bench. Um die Benchmark-Tests durchzuführen, erstellen Sie MySQL und wechseln dann in das Verzeichnis sql-bench. Dort führen Sie das Skript run-all-tests aus:

 shell> cd sql-bench shell>
perl run-all-tests
--server=server_name 

server_name sollte der Name eines der unterstützten Server sein. Um eine Liste aller Optionen und unterstützten Server zu erhalten, rufen Sie folgenden Befehl auf:

 shell> perl run-all-tests --help

Das Skript crash-me befindet sich ebenfalls im Verzeichnis sql-bench. crash-me versucht durch Ausführung von Abfragen zu ermitteln, welche Funktionen ein Datenbanksystem unterstützt und welche Fähigkeiten und Einschränkungen es aufweist. Beispielsweise wird ermittelt,

Die Ergebnisse von crash-me finden Sie für viele verschiedene Datenbankserver unter http://dev.mysql.com/tech-resources/crash-me.php. Weitere Informationen zu Benchmark-Ergebnissen finden Sie unter http://dev.mysql.com/tech-resources/benchmarks/.

Sie sollten Ihre Anwendung und Ihre Datenbank in jedem Fall mit der Benchmark-Reihe prüfen, um festzustellen, wo ggf. Engpässe vorhanden sind. Wenn Sie einen Engpass behoben (oder ihn durch ein „Dummymodul“ ersetzt) haben, können Sie mit der Suche nach weiteren Engpässen fortfahren. Auch wenn die Gesamtleistung für Ihre Anwendung derzeit akzeptabel sein sollte, sollten Sie zumindest einen Plan für jeden Engpass erstellen und entscheiden, wie Sie ihn bei Bedarf beseitigen, sofern dieser Bedarf eines Tages auftreten sollte.

Beispiele für portable Benchmark-Programme finden Sie in der MySQL-Benchmark-Reihe. Siehe auch Abschnitt 7.1.4, „Die MySQL-Benchmark-Reihe“. Sie können ein beliebiges Programm dieser Reihe nehmen und an Ihre eigenen Bedürfnisse anpassen. Dies erlaubt Ihnen, verschiedene Lösungen für Ihr Problem auszuprobieren und zu ermitteln, welche wirklich die schnellste ist.

Eine weitere kostenlose Benchmark-Reihe ist die Open Source Database Benchmark, die Sie unter http://osdb.sourceforge.net/ erhalten.

Häufig treten Probleme nur dann auf, wenn das System sehr stark ausgelastet ist. Viele unserer Kunden wenden sich an uns, wenn sie ein (getestetes) Produktionssystem haben, welches unter Last Ausfallerscheinungen zeigt. In den meisten Fällen werden leistungsbezogene Probleme durch Faktoren des grundlegenden Datenbankdesigns (etwa im Fall von Schwächen bei Tabellenscans unter Last) oder durch Probleme mit dem Betriebssystem oder mit Bibliotheken verursacht. Meistens würden sich diese Probleme wesentlich einfacher beheben lassen, wenn das jeweilige System nicht bereits in der Produktion verwendet würde.

Um derartige Probleme zu vermeiden, sollten Sie den Aufwand auf sich nehmen und Benchmark-Tests Ihrer Anwendung unter maximaler Last durchführen:

Wie die Namen dieser Programme bereits suggerieren, besteht ihr Zweck darin, Ihr System in die Knie zu zwingen. Es sollte also klar sein, dass Sie diese Programme nur auf Entwicklungssystemen einsetzen.

 EXPLAIN tbl_name 

Oder:

 EXPLAIN [EXTENDED | PARTITIONS] SELECT
select_options 

Die EXPLAIN-Anweisung kann entweder als Synonym für DESCRIBE oder als Möglichkeit verwendet werden, um Informationen bezüglich der Frage zu ermitteln, wie MySQL eine SELECT-Anweisung ausführt:

Dieser Abschnitt beschreibt die zweite Verwendungsmöglichkeit von EXPLAIN: die Anforderung von Informationen zum Abfrageausführungsplan. Eine Beschreibung der Anweisungen DESCRIBE und SHOW COLUMNS finden Sie in Abschnitt 13.3.1, „DESCRIBE (Informationen über Spalten abrufen)“, und Abschnitt 13.5.4.3, „SHOW COLUMNS“.

Mithilfe von EXPLAIN können Sie ermitteln, wo Sie Indizes für Tabellen konfigurieren sollten, um SELECT-Anweisungen zu beschleunigen, die Datensätze mithilfe von Indizes finden. Sie können mit EXPLAIN auch überprüfen, ob der Optimierer die Tabellen in optimaler Reihenfolge verknüpft. Um den Optimierer zur Verwendung einer Verknüpfungsreihenfolge zu zwingen, die der Reihenfolge entspricht, in der die Tabellen in der SELECT-Anweisung aufgeführt sind, beginnen Sie die Anweisung mit SELECT STRAIGHT_JOIN statt mit SELECT.

Wenn das Problem auftritt, dass Indizes nicht benutzt werden, obwohl Sie annehmen, dass dies eigentlich der Fall sein sollte, dann führen Sie ANALYZE TABLE aus, um die Tabellenstatistiken wie etwa die Kardinalität der Schlüssel, die sich auf die durch den Optimierer vorgenommene Auswahl auswirken kann, zu aktualisieren. Siehe auch Abschnitt 13.5.2.1, „ANALYZE TABLE“.

EXPLAIN gibt je einen Datensatz für jede in der SELECT-Anweisung verwendete Tabelle zurück. Die Tabellen sind in der Ausgabe in der Reihenfolge aufgeführt, in der MySQL sie bei der Verarbeitung der Abfrage lesen würde. MySQL löst alle Joins mithilfe einer Methode auf, die Single-Sweep-Multi-Join heißt. Hierbei liest MySQL einen Datensatz aus der ersten Tabelle und sucht dann einen passenden Datensatz in der zweiten Tabelle, der dritten Tabelle usw. Sind alle Tabellen verarbeitet, dann gibt MySQL die gewählten Spalten aus und durchsucht die Tabellenliste in umgekehrter Reihenfolge, bis eine Tabelle gefunden wird, bei der mehr passende Datensätze vorhanden sind. Der nächste Datensatz wird aus genau dieser Tabelle gelesen, und dann wird der Prozess mit der nächsten Tabelle fortgesetzt.

Wenn das Schlüsselwort EXTENDED verwendet wird, erzeugt EXPLAIN zusätzliche Informationen, die durch Absetzen einer SHOW WARNINGS-Anweisung nach der EXPLAIN-Anweisung angezeigt werden können. Diese Informationen geben Hinweise zum Optimierungsprozess, z. B. wie der Optimierer Tabellen- und Spaltennamen in der SELECT-Anweisung qualifiziert oder wie die SELECT-Anweisung nach der Anwendung der Umformulierungs- und Optimierungsregeln aussieht.

Hinweis: Sie können die Schlüsselwörter EXTENDED und PARTITIONS nicht gemeinsam in derselben EXPLAIN-Anweisung verwenden.

Jeder von EXPLAIN ausgegebene Datensatz enthält Angaben zu genau einer Tabelle. Dabei enthält jeder Datensatz die folgenden Spalten:

Sie erhalten einen geeigneten Anhaltspunkt zu der Frage, wie gut ein Join ist, indem Sie das Produkt der Werte in der Spalte rows der Ausgabe von EXPLAIN erstellen. Hieraus ergibt sich grob, wie viele Datensätze MySQL untersuchen muss, um die Abfrage auszuführen. Wenn Sie die Abfragen mit der Systemvariablen max_join_size beschränken, wird dieses Datensatzprodukt auch benutzt, um zu bestimmen, welche SELECT-Anweisungen für mehrere Tabellen ausgeführt und welche abgebrochen werden. Siehe auch Abschnitt 7.5.2, „Serverparameter feineinstellen“.

Das folgende Beispiel zeigt, wie ein Join mehrerer Tabellen auf der Basis der von EXPLAIN übermittelten Angaben progressiv optimiert werden kann.

Angenommen, Sie hätten die folgende SELECT-Anweisung, die Sie mit EXPLAIN untersuchen wollen:

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;

Für dieses Beispiel gelten die folgenden Annahmen:

Anfangs – also vor der Durchführung von Optimierungen – erzeugt die EXPLAIN-Anweisung die folgenden Angaben:

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,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      range checked for each record (key map: 35)

Da type für jede Tabelle ALL ist, gibt diese Ausgabe an, dass MySQL ein kartesisches Produkt aller Tabellen erzeugt, also jede mögliche Kombination von Datensätzen. Dies dauert recht lang, weil das Produkt der Anzahl der Datensätze in jeder Tabelle untersucht werden muss. Im vorliegenden Fall beträgt dieses Produkt 74 × 2135 × 74 × 3872 = 45.268.558.720 Datensätze. Wenn die Tabellen noch größer wären, kann man sich nur vage vorstellen, wie lange dies dauern würde.

Ein Problem besteht hier darin, dass MySQL Indizes für Spalten effizienter verwenden kann, wenn sie mit demselben Typ und derselben Größe deklariert wurden. In diesem Kontext werden VARCHAR und CHAR als identisch betrachtet, wenn sie mit derselben Größe deklariert werden. tt.ActualPC wird aber als CHAR(10) und et.EMPLOYID als CHAR(15) deklariert – die Längen stimmen also nicht überein.

Um diese Fehlanpassung zwischen den Spaltenlängen aufzuheben, verlängern Sie ActualPC mit ALTER TABLE von 10 auf 15 Zeichen Länge:

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

Nun sind tt.ActualPC und et.EMPLOYID beide VARCHAR(15). Die erneute Ausführung der EXPLAIN-Anweisung erzeugt folgende Ausgabe:

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
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

Dies ist noch nicht perfekt, aber es ist schon wesentlich besser: Das Produkt der rows-Werte ist bereits um den Faktor 74 geringer – die Ausführung dieser Version dauert nur ein paar Sekunden.

Eine weitere Änderung kann vorgenommen werden, um die Nichtübereinstimmung der Spaltenlängen für die Vergleiche tt.AssignedPC = et_1.EMPLOYID und tt.ClientID = do.CUSTNMBR zu beseitigen:

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

Nach dieser Änderung sieht die Ausgabe von EXPLAIN so aus:

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

An dieser Stelle ist die Abfrage schon fast perfekt optimiert. Als letztes Problem verbleibt die Tatsache, dass MySQL standardmäßig voraussetzt, dass Werte in der Spalte tt.ActualPC gleichmäßig verteilt sind; dies ist aber bei der Tabelle tt nicht der Fall. Glücklicherweise ist es einfach, MySQL zur Analyse der Schlüsselverteilung zu bewegen:

mysql> ANALYZE TABLE tt;

Mit den zusätzlichen Indexangaben ist der Join perfekt, und EXPLAIN erzeugt folgendes Ergebnis:

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
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 Spalte rows in der Ausgabe von EXPLAIN eine begründete Annahme des MySQL-Join-Optimierers ist. Sie sollten überprüfen, ob die Zahlen annähernd realistisch sind, indem Sie das rows-Produkt mit der tatsächlichen Anzahl der von der Abfrage zurückgegebenen Datensätze vergleichen. Unterscheiden sich diese Werte erheblich, dann erhalten Sie unter Umständen eine bessere Leistung, wenn Sie STRAIGHT_JOIN in Ihrer SELECT-Abfrage verwenden und versuchen, die Tabellen in der FROM-Klausel in einer anderen Reihenfolge aufzulisten.

In den meisten Fällen können Sie die Leistungsfähigkeit von Abfragen einschätzen, indem Sie die Anzahl der Suchvorgänge auf der Festplatte ermitteln. Bei kleinen Tabellen finden Sie gewöhnlich pro derartigen Suchvorgang einen Datensatz (weil der Index im Zweifelsfall im Cache liegt). Bei größeren Tabellen können Sie bei der Verwendung von B-Tree-Indizes schätzungsweise die folgende Anzahl von Suchvorgängen annehmen, um einen Datensatz zu finden: log(row_count) / log(index_block_length / 3 × 2 / (index_length + data_pointer_length)) + 1.

In MySQL ist ein Indexblock gewöhnlich 1024 Byte groß, der Datenzeiger umfasst weitere vier Byte. Bei einer Tabelle mit 500.000 Datensätzen und einer Indexlänge von drei Byte (der Größe von MEDIUMINT) gibt die Formel log(500.000/log(1024/3*2/(3+4))+1 = 4 Suchvorgänge aus.

Dieser Index würde eine Speicherkapazität von 500.000 × 7 × 3/2 = 5,2 Mbyte benötigen (ein typisches Indexpuffer-Füllungsverhältnis von 2 : 3 vorausgesetzt), d. h., Sie hätten wahrscheinlich einen Großteil des Indexes im Speicher und benötigen nur ein oder zwei Aufrufe zum Datenlesen, um den Datensatz zu finden.

Bei Schreiboperationen hingegen benötigen Sie vier Suchanforderungen, um zu ermitteln, wo ein neuer Indexwert abgelegt werden muss, und normalerweise zwei Suchvorgänge, um den Index zu aktualisieren und den Datensatz zu schreiben.

Beachten Sie, dass die vorangegangene Beschreibung nicht bedeutet, dass Ihre Anwendungsleistung sich nach und nach um den Logarithmus N verringert. Solange alles vom Betriebssystem oder vom MySQL Server zwischengespeichert wird, verringert sich die Geschwindigkeit nur marginal, wenn die Tabellen größer werden. Sobald die Daten zu umfangreich geworden sind, um zwischengespeichert zu werden, geht alles viel langsamer, bis Ihre Anwendungen nur noch mit Suchvorgängen auf der Festplatte beschäftigt sind (deren Anzahl sich um log N erhöht). Um dies zu vermeiden, müssen Sie die Größe des Schlüssel-Caches erhöhen, wenn der Umfang Ihrer Daten zunimmt. Bei MyISAM-Tabellen wird die Größe des Schlüssel-Caches von der Systemvariablen key_buffer_size gesteuert. Siehe auch Abschnitt 7.5.2, „Serverparameter feineinstellen“.

Wenn Sie eine langsame SELECT … WHERE-Anweisung beschleunigen wollen, besteht der erste Schritt in aller Regel darin, zu überprüfen, ob Sie einen Index hinzufügen können. Alle Referenzierungen zwischen verschiedenen Tabellen sollten normalerweise über Indizes erfolgen. Sie können mit der EXPLAIN-Anweisung ermitteln, welche Indizes für eine SELECT-Anweisung verwendet werden. Siehe auch Abschnitt 7.2.1, „EXPLAIN-Syntax (Informationen über ein SELECT erhalten)“, und Abschnitt 7.4.5, „Wie MySQL Indizes benutzt“.

Hier ein paar allgemeine Tipps zur Beschleunigung von Abfragen für MyISAM-Tabellen:

Dieser Abschnitt behandelt Optimierungen, die für die Verarbeitung von WHERE-Klauseln durchgeführt werden können. Die Beispiele verwenden SELECT-Anweisungen, dieselben Optimierungen gelten aber auch für WHERE-Klauseln in DELETE- und UPDATE-Anweisungen.

Wir arbeiten fortlaufend am MySQL-Optimierer, weswegen dieser Abschnitt nicht vollständig ist. MySQL führt eine Vielzahl von Optimierungen durch, die nicht alle an dieser Stelle dokumentiert sind.

Es folgen einige von MySQL durchgeführte Optimierungen:

Hier einige Beispiele für Abfragen, die sehr schnell sind:

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

MySQL löst die folgenden Abfragen nur mithilfe des Indexbaums auf und setzt dabei voraus, dass die indizierten Spalten numerisch sind:

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

Die folgenden Abfragen verwendet die Indizierung zur Abfrage von Datensätzen in sortierter Reihenfolge ohne separaten Sortierdurchlauf:

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;

Die Zugriffsmethode range ruft mithilfe eines einzelnen Indexes eine Teilmenge der Datensätze ab, die in der Tabelle innerhalb eines oder mehrerer Indexwertintervalle liegen. Sie kann für einen ein- oder mehrteiligen Index verwendet werden. Die folgenden Abschnitte erläutern detailliert, wie Intervalle aus der WHERE-Klausel extrahiert werden.

SELECT * FROM t1
  WHERE key_col > 1 
  AND key_col < 10;

SELECT * FROM t1 
  WHERE key_col = 1 
  OR key_col IN (15,18,20);

SELECT * FROM t1 
  WHERE key_col LIKE 'ab%' 
  OR key_col BETWEEN 'bar' AND 'foo';

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

Die Indexverschmelzungsmethode wird verwendet, um Datensätze mit mehreren range-Scans abzurufen und deren Ergebnisse zu einem Ergebnis zu verschmelzen. Die Verschmelzung kann Unions, Schnittmengen oder Schnittmengen-Unions der zugrunde liegenden Scans erzeugen.

In der Ausgabe von EXPLAIN erscheint die Indexverschmelzungsmethode als index_merge in der type-Spalte. In diesem Fall enthält die Spalte key eine Liste der verwendeten Indizes, und key_len enthält eine Liste der längsten Schlüsselteile für diese Indizes.

Ein paar Beispiele:

SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20;

SELECT * FROM tbl_name
  WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1=t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1=1
  AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

Die Indexverschmelzungsmethode umfasst mehrere Zugriffsalgorithmen (diese können dem Feld Extra in der Ausgabe von EXPLAIN entnommen werden):

Die folgenden Abschnitte beschreiben diese Methoden im Detail.

Hinweis: Der Optimierungsalgorithmus für die Indexverschmelzung weist die folgenden bekannten Defizite auf:

Die Auswahl zwischen verschiedenen möglichen Varianten der Indexverschmelzungsmethode und anderen Zugriffsmethoden basiert auf Kostenschätzungen der verschiedenen verfügbaren Optionen.

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name
  WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
  (key3='foo' AND key4='bar') AND key5=5;

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
     WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

MySQL kann dieselbe Optimierung an col_name IS NULL vornehmen, die es auch für col_name = constant_value durchführen kann. So kann MySQL etwa Indizes und Bereiche zur Suche nach NULL mit IS NULL verwenden.

Ein paar Beispiele:

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name
  WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

Wenn eine WHERE-Klausel eine Bedingung col_name IS NULL für eine Spalte enthält, die als NOT NULL deklariert ist, dann wird dieser Ausdruck wegoptimiert. Diese Optimierung findet allerdings nicht in Fällen statt, in denen die Spalte ohnehin NULL erzeugen könnte – z. B. wenn sie aus einer Tabelle auf der rechten Seite eines LEFT JOIN stammt.

MySQL kann auch die Kombination col_name = expr AND col_name IS NULL optimieren; diese Form tritt bei aufgelösten Unterabfragen häufig auf. EXPLAIN zeigt ref_or_null an, wenn diese Optimierung verwendet wird.

Diese Optimierung kann je Schlüsselteil eine IS NULL-Bedingung verarbeiten.

Es folgen einige Beispiele für optimierte Abfragen (hierbei wird davon ausgegangen, dass ein Index für die Spalten a und b der Tabelle t2 vorhanden ist):

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

Bei ref_or_null wird zunächst der Referenzschlüssel ausgelesen. Danach erfolgt eine separate Suche nach Datensätzen mit einem NULL-Schlüsselwert.

Beachten Sie, dass die Optimierung nur eine IS NULL-Ebene verarbeiten kann. In der folgenden Abfrage verwendet MySQL Schlüsselsuchvorgänge nur für den Ausdruck (t1.a=t2.a AND t2.a IS NULL). Der Schlüsselteil zu b kann von MySQL nicht verwendet werden:

SELECT * FROM t1, t2
     WHERE (t1.a=t2.a AND t2.a IS NULL)
     OR (t1.b=t2.b AND t2.b IS NULL);

DISTINCT kombiniert mit ORDER BY benötigt in vielen Fällen eine Temporärtabelle.

Weil DISTINCT unter Umständen GROUP BY verwendet, sollten Sie wissen, wie MySQL mit Spalten in ORDER BY- oder HAVING-Klauseln verfährt, die nicht Teil der gewählten Spalten sind. Siehe auch Abschnitt 12.11.3, „GROUP BY mit versteckten Feldern“.

In vielen Fällen kann eine DISTINCT-Klausel als Sonderfall von GROUP BY betrachtet werden. So sind beispielsweise die beiden folgenden Abfragen gleichwertig:

SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;

SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

Aufgrund dieser Äquivalenz können die Optimierungen, die für GROUP BY-Abfragen verwendbar sind, auch auf Abfragen mit einer DISTINCT-Klausel angewendet werden. Insofern sollten Sie, um weitere Informationen zu Optimierungsmöglichkeiten bei DISTINCT-Abfragen zu erhalten, unter Abschnitt 7.2.13, „GROUP BY-Optimierung“, nachschlagen.

Wenn Sie LIMIT row_count mit DISTINCT kombinieren, beendet MySQL die Abfrage, sobald row_count eindeutige Datensätze gefunden wurden.

Wenn Sie nicht Spalten aus allen in einer Abfrage aufgeführten Tabellen verwenden, beendet MySQL das Scannen unbenutzter Tabellen, sobald die erste Übereinstimmung gefunden wurde. Im folgenden Fall (in dem vorausgesetzt wird, dass t1 vor t2 benutzt wird, was mit EXPLAIN verifiziert werden kann) beendet MySQL das Auslesen von t2 für einen beliebigen Datensatz in t1, sobald der erste Datensatz in t2 gefunden wird:

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

MySQL implementiert A LEFT JOIN B join_condition wie folgt:

Die Implementierung von RIGHT JOIN erfolgt analog zu der von LEFT JOIN, wobei lediglich die Rollen der Tabellen umgekehrt werden.

Der Join-Optimierer berechnet die Reihenfolge, in der die Tabellen verknüpft werden sollten. Die durch LEFT JOIN oder STRAIGHT_JOIN erzwungene Lesereihenfolge für die Tabellen unterstützt den Join-Optimierer bei seiner Arbeit wesentlich effizienter, weil erheblich weniger Tabellenumstellungen zu überprüfen sind. Beachten Sie, dass dies bedeutet, dass, wenn Sie eine Abfrage folgenden Typs ausführen, MySQL einen vollständigen Scan in b durchführt, weil diese Tabelle aufgrund von LEFT JOIN vor d gelesen wird:

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

Die Lösung besteht in diesem Fall darin, die Reihenfolge umzukehren, in der a und b in der FROM-Klausel aufgelistet werden:

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

Wenn bei einem LEFT JOIN die WHERE-Bedingung für den erzeugten NULL-Datensatz immer falsch ist, dann wird der LEFT JOIN in einen normalen Join umgesetzt. So wäre beispielsweise die WHERE-Klausel in der folgenden Abfrage falsch, wenn t2.column1 NULL wäre:

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

Aus diesem Grund kann die Abfrage problemlos in einen normalen Join konvertiert werden:

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

Dies kann schneller erfolgen, weil MySQL die Tabelle t2 vor t1 verwenden kann, sofern dies einen besseren Abfrageplan zum Ergebnis hätte. Mit STRAIGHT_JOIN können Sie eine bestimmte Tabellenreihenfolge erzwingen.

Die Syntax zum Ausdrücken von Joins gestattet auch verschachtelte Joins. Die nachfolgende Beschreibung bezieht sich auf die in Abschnitt 13.2.7.1, „JOIN“, beschriebene Join-Syntax.

Die Syntax von table_factor ist im Vergleich zum SQL-Standard erweitert. SQL akzeptiert nur table_reference, nicht aber eine in Klammern gesetzte Liste mit Referenzierungen. Dies ist eine konservative Erweiterung, sofern wir jedes Komma in einer Liste mit table_reference-Elementen als äquivalent zu einem inneren Join betrachten. Zum Beispiel:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

ist äquivalent mit

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

In MySQL ist CROSS JOIN syntaktisch ein Äquivalent zu INNER JOIN (diese lassen sich gegeneinander austauschen). Nach SQL-Standard hingegen sind beide nicht äquivalent. INNER JOIN wird bei einer ON-Klausel und CROSS JOIN andernfalls verwendet.

Klammern können in Join-Ausdrücken, die nur innere Join-Operationen enthalten, ignoriert werden. Betrachten Sie folgenden Ausdruck:

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
   ON t1.a=t2.a

Nach Entfernen der Klammern und Gruppieren der Operationen auf der linken Seite entsteht folgender Ausdruck:

(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
    ON t2.b=t3.b OR t2.b IS NULL

Trotzdem sind die beiden Ausdrücke nicht äquivalent. Um dies nachzuweisen, nehmen wir an, dass die Tabellen t1, t2 und t3 die folgenden Zustände haben:

In diesem Fall gibt der erste Ausdruck eine Ergebnismenge mit den Datensätzen {1,1,101,101}, {2,NULL,NULL,NULL} zurück; der zweite Ausdruck hingegen gibt die Datensätze {1,1,101,101}, {2,NULL,NULL,101} zurück:

mysql> SELECT *
    -> FROM t1
    ->      LEFT JOIN
    ->      (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
    ->      ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
    -> FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
    ->      LEFT JOIN t3
    ->      ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

Im folgenden Beispiel wird ein äußerer Join gemeinsam mit einem inneren Join verwendet:

t1 LEFT JOIN (t2, t3) ON t1.a=t2.a

Dieser Ausdruck kann nicht in den folgenden Ausdruck transformiert werden:

t1 LEFT JOIN t2 ON t1.a=t2.a, t3.

Für die gegebenen Tabellenzustände geben die beiden Ausdrücke verschiedene Datensatzmengen zurück:

mysql> SELECT *
    -> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
    -> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

Aus diesem Grund ändern wir, wenn wir in einem Join-Ausdruck mit äußeren Join-Operatoren die Klammern weglassen, unter Umständen die Ergebnismenge für den ursprünglichen Ausdruck.

Genauer formuliert: Wir dürfen die Klammern im rechten Operanden der linken äußeren Join-Operation und im linken Operanden einer rechten Join-Operation nicht ignorieren. Oder: Die Klammern der inneren Tabellenausdrücke äußerer Join-Operationen dürfen nicht unbeachtet bleiben. Klammern für den anderen Operanden (d. h. den Operanden der äußeren Tabelle) können hingegen ignoriert werden.

Der Ausdruck

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)

ist äquivalent zu folgendem Ausdruck:

t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)

Dies gilt für alle Tabellen t1,t2,t3 und alle Bedingungen P für Attribute t2.b und t3.b.

Immer dann, wenn die Join-Operationen in einem Join-Ausdruck (join_table) nicht von links nach rechts ausgeführt werden, redet man von verschachtelten Joins. Betrachten Sie einmal die folgenden Abfragen:

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
  WHERE t1.a > 1

SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
  WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1

Diese Abfragen enthalten die folgenden verschachtelten Joins:

t2 LEFT JOIN t3 ON t2.b=t3.b
t2, t3

Der verschachtelte Join wird in der ersten Abfrage mit einem Left-Join gebildet, während er in der zweiten Abfrage aufgrund einer inneren Join-Operation entsteht.

In der ersten Abfrage können die Klammern weggelassen werden: Die grammatische Struktur des Join-Ausdrucks schreibt dieselbe Ausführungsreihenfolge für Join-Operationen vor. Bei der zweiten Abfrage dürfen die Klammern nicht weggelassen werden, obwohl der Join-Ausdruck hier auch ohne sie eindeutig interpretiert werden könnte. (In unserer erweiterten Syntax sind die Klammern im Ausdruck (t2, t3) der zweiten Abfrage erforderlich, obwohl die Abfrage theoretisch auch ohne sie analysiert werden könnte: Wir hätten immer noch eine eindeutige syntaktische Struktur für die Abfrage, da LEFT JOIN und ON die Rolle der linken und rechten Begrenzungen für den Ausdruck (t2,t3) übernähmen.)

Die obigen Beispiele veranschaulichen die folgenden Aspekte:

Abfragen mit verschachtelten äußeren Joins werden in derselben Weise ausgeführt wie Abfragen mit inneren Joins. Genauer gesagt, wird eine Abwandlung des Join-Algorithmus mit verschachtelten Schleifen benutzt. Vergegenwärtigen Sie sich, mit welchem Algorithmusschema ein Join mit verschachtelten Schleifen eine Abfrage ausführt. Angenommen, wir haben eine Join-Abfrage über drei Tabellen T1,T2,T3 der folgenden Form:

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
                 INNER JOIN T3 ON P2(T2,T3)
  WHERE P(T1,T2,T3).

Hierbei seien P1(T1,T2) und P2(T3,T3) Join-Bedingungen (für Ausdrücke), während P(t1,t2,t3) eine Bedingung über Spalten der Tabellen T1,T2,T3 ist.

Der Algorithmus für Joins mit verschachtelten Schleifen würde die Abfrage wie folgt ausführen:

FOR each row t1 in T1 {
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

Die Notation t1||t2||t3 bedeutet einen „Datensatz, der durch Verkettung der Spalten der Datensätze t1, t2 und t3 entsteht“. In einigen der folgenden Beispiele bezeichnet, wenn ein Datensatzname erscheint, NULL die Tatsache, dass NULL für jede Spalte dieses Datensatzes benutzt wird. Beispielsweise bedeutet t1||t2||NULL „einen Datensatz, der aus den verketteten Spalten der Datensätze t1 und t2 sowie NULL für jede Spalte von t3 besteht“.

Betrachten wir nun eine Abfrage mit verschachtelten äußeren Joins:

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON P2(T2,T3))
              ON P1(T1,T2)
  WHERE P(T1,T2,T3).

Für diese Abfrage ändern wir das Muster für verschachtelte Schleifen ab und erhalten Folgendes:

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF P(t1,t2,NULL) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

Generell wird für jede verschachtelte Schleife für die erste innere Tabelle einer äußeren Join-Operation ein Flag eingeführt, welches vor der Schleife gelöscht und danach wieder gesetzt wird. Das Flag wird gesetzt, wenn für den aktuellen Datensatz aus der äußeren Tabelle eine Entsprechung in der Tabelle gefunden wird, die den inneren Operanden darstellt. Wenn das Flag am Ende des Schleifenzyklus immer noch nicht gelöscht ist, dann wurde für den aktuellen Datensatz in der äußeren Tabelle keine Entsprechung gefunden. In diesem Fall wird der Datensatz mit NULL-Werten für die Spalten der inneren Tabellen ergänzt. Der Ergebnisdatensatz wird zur letzten Überprüfung an die Ausgabe oder in die nächste verschachtelte Schleife übergeben – aber nur dann, wenn der Datensatz die Join-Bedingung für alle eingebetteten äußeren Joins erfüllt.

In unserem Beispiel ist die mit dem folgenden Ausdruck beschriebene äußere Join-Tabelle eingebettet:

(T2 LEFT JOIN T3 ON P2(T2,T3))

Beachten Sie, dass der Optimierer für die Abfrage mit inneren Joins eine andere Reihenfolge für verschachtelte Schleifen wählen könnte – etwa die folgende:

FOR each row t3 in T3 {
  FOR each row t2 in T2 such that P2(t2,t3) {
    FOR each row t1 in T1 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

Bei Abfragen mit äußeren Joins kann der Optimierer nur eine Reihenfolge wählen, bei der Schleifen für äußere Tabellen den Schleifen für die inneren Tabellen vorangehen. Insofern ist für unsere Abfrage mit äußeren Joins nur eine Verschachtelungsreihenfolge möglich. Bei der folgenden Abfrage wird der Optimierer zwei verschiedene Verschachtelungen auswerten:

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
  WHERE P(T1,T2,T3)

Die Verschachtelungen sind

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t1,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

und

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t3 in T3 such that P2(t1,t3) {
    FOR each row t2 in T2 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

In beiden Verschachtelungen muss T1 in der äußeren Schleife verarbeitet werden, weil sie in einem äußeren Join verwendet wird. T2 und T3 werden hingegen in einem inneren Join benutzt, d. h., der Join muss in der inneren Schleife verarbeitet werden. Allerdings können, weil der Join ein innerer Join ist, T2 und T3 in beliebiger Ordnung verarbeitet werden.

Bei der Beschreibung des Algorithmus mit verschachtelten Schleifen für innere Joins haben wir ein paar Details übergangen, deren Wirkung auf die Leistungsfähigkeit der Abfrageausführung beträchtlich sein kann. Wir haben beispielsweise so genannte „Pushdown-Bedingungen“ nicht erwähnt. Angenommen, unsere WHERE-Bedingung P(T1,T2,T3) könnte mit einer konjunktiven Formel dargestellt werden:

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).

In diesem Fall verwendet MySQL in der Tat das folgende Schema mit verschachtelten Schleifen zur Ausführung der Abfrage mit inneren Joins:

FOR each row t1 in T1 such that C1(t1) {
  FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)  {
    FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

Sie erkennen, dass die Konjunkte C1(T1), C2(T2) und C3(T3) aus der innersten in die äußerste Schleife verschoben werden, wo sie dann ausgewertet werden können. Wenn C1(T1) eine sehr restriktive Bedingung ist, dann kann dieser Bedingungs-Pushdown die Anzahl der Datensätze aus Tabelle T1, die an die inneren Schleifen weitergegeben werden, erheblich verringern. Hieraus ergibt sich eine beachtliche Verbesserung bei der Ausführungszeit für die Abfrage.

Bei einer Abfrage mit äußeren Joins muss die WHERE-Bedingung erst geprüft werden, wenn festgestellt wurde, dass für den aktuellen Datensatz in der äußeren Tabelle eine Entsprechung in den inneren Tabellen vorhanden ist. Aufgrund dessen kann die Optimierung mit einer Pushdown-Bedingung aus den inneren verschachtelten Schleifen nicht direkt auf Abfragen mit äußeren Joins angewendet werden. Deswegen müssen an dieser Stelle konditionale Pushdown-Prädikate in Verbindung mit Überwachungs-Flags eingeführt werden, die gesetzt werden, sobald eine Übereinstimmung gefunden wurde.

Betrachten Sie noch einmal unser Beispiel mit äußeren Joins:

P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)

Hier sieht das Schema der verschachtelten Schleifen unter Verwendung überwachter Pushdown-Bedingungen wie folgt aus:

FOR each row t1 in T1 such that C1(t1) {
  BOOL f1:=FALSE;
  FOR each row t2 in T2
      such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3
        such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
      IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1 && P(t1,NULL,NULL)) {
      t:=t1||NULL||NULL; OUTPUT t;
  }
}

Generell können Pushdown-Prädikate aus Join-Bedingungen wie P1(T1,T2) und P(T2,T3) extrahiert werden. In diesem Fall wird ein Pushdown-Prädikat auch von einem Flag überwacht, das eine Überprüfung des Prädikats auf den mit NULL ergänzten Datensatz verhindert, der im Zuge der entsprechenden Join-Operation erzeugt wurde.

Beachten Sie, dass ein Zugriff nach Schlüssel aus einer inneren Tabelle auf eine andere Tabelle im selben verschachtelten Join unzulässig ist, wenn er durch ein Prädikat aus der WHERE-Bedingung herbeigeführt wird. (Wir könnten in diesem Fall einen bedingten Schlüsselzugriff verwenden, aber diese Methode ist in MySQL 5.1 noch nicht implementiert.)

Tabellenausdrücke in der FROM-Klausel einer Abfrage werden in vielen Fällen vereinfacht.

Auf der Parserebene werden Abfragen mit rechten äußeren Join-Operationen in gleichwertige Abfragen konvertiert, die nur Left-Join-Operationen enthalten. Im Allgemeinen erfolgt die Konvertierung auf der Basis der folgenden Regel:

(T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...).

Alle inneren Join-Ausdrücke der Form T1 INNER JOIN T2 ON P(T1,T2) werden durch die Liste T1,T2 ersetzt, und P(T1,T2) wird als Konjunkt mit der WHERE-Bedingung (oder mit der Join-Bedingung des einbettenden Joins, sofern vorhanden) verknüpft.

Wenn der Optimierer Pläne für Join-Abfragen mit einer äußeren Join-Operation auswertet, berücksichtigt er nur diejenigen Pläne, bei denen bei einer solchen Operation zunächst auf die äußeren und erst dann auf die inneren Tabellen zugegriffen wird. Die Optimiereroptionen sind eingeschränkt, weil nur solche Pläne uns die Ausführung von Abfragen mit äußeren Join-Operationen über das Schema mit verschachtelten Schleifen gestatten.

Angenommen, wir haben eine Abfrage folgenden Aussehens:

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)

Hierbei schränkt R(T2) die Anzahl passender Datensätze aus der Tabelle T2 erheblich ein. Wenn wir die Abfrage in ihrer ursprünglichen Form ausgeführt hätten, hätte der Optimierer keine andere Chance gehabt, als zuerst auf die Tabelle T1 und erst dann auf T2 zuzugreifen. Dies hätte einen sehr ineffizienten Ausführungsplan zur Folge gehabt.

Glücklicherweise wandelt MySQL eine solche Abfrage in eine Abfrage ohne äußere Join-Operation um, wenn die WHERE-Bedingung nullabweisend wird. Eine Bedingung heißt nullabweisend für eine äußere Join-Operation, wenn sie für jeden NULL-ergänzten Datensatz, der für die Operation erstellt wurde, stets FALSE oder UNKNOWN ist.

Betrachten Sie folgenden äußeren Join:

T1 LEFT JOIN T2 ON T1.A=T2.A

Für ihn sind Bedingungen wie die folgenden nullabweisend:

T2.B IS NOT NULL,
T2.B > 3,
T2.C <= T1.C,
T2.B < 2 OR T2.C > 1

Bedingungen wie die folgenden sind hingegen nicht nullabweisend:

T2.B IS NULL,
T1.B < 3 OR T2.B IS NOT NULL,
T1.B < 3 OR T2.B > 3

Die allgemeinen Regeln zur Überprüfung, ob eine Bedingung für einen äußeren Join nullabweisend ist oder nicht, sind recht einfach. Eine Bedingung ist nullabweisend, wenn sie

Eine Bedingung kann für eine äußere Join-Operation nullabweisend sein und gleichzeitig für eine andere nicht. Betrachten Sie folgende Abfrage:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

Hier ist die WHERE-Bedingung für die zweite äußere Join-Operation nullabweisend, nicht jedoch für die erste.

Wenn die WHERE-Bedingung für eine äußere Join-Operation in einer Abfrage nullabweisend ist, wird die äußere Join-Operation durch eine innere Join-Operation ersetzt.

So wird etwa obige Abfrage durch die folgende ersetzt:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

Für die ursprüngliche Abfrage würde der Optimierer Pläne auswerten, die mit der Zugriffsreihenfolge T1,T2,T3 kompatibel wären. Für die ersetzende Abfrage wird außerdem die Zugriffssequenz T3,T1,T2 in Betracht gezogen.

Eine Konvertierung einer äußeren Join-Operation kann eine Konvertierung einer anderen derartigen Operation auslösen. Betrachten Sie folgende Abfrage:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

Diese wird in die folgende Abfrage konvertiert:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

Diese wiederum ist äquivalent mit dieser Abfrage:

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

Nun kann die verbleibende äußere Join-Operation ebenfalls durch einen inneren Join ersetzt werden, weil die Bedingung T3.B=T2.B nullabweisend ist und wir eine Abfrage erhalten, die überhaupt keine äußeren Joins enthält:

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

Manchmal gelingt es, eine eingebettete äußere Join-Operation zu ersetzen, aber der einbettende äußere Join kann nicht konvertiert werden. Betrachten Sie folgende Abfrage:

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

Sie wird wie folgt konvertiert:

SELECT * FROM T1 LEFT JOIN
              (T2 INNER JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0,

Dies lässt sich nur in einer Form umschreiben, die nach wie vor die einbettende äußere Join-Operation enthält:

SELECT * FROM T1 LEFT JOIN
              (T2,T3)
              ON (T2.A=T1.A AND T3.B=T2.B)
  WHERE T3.C > 0.

Wenn Sie versuchen, eine eingebettete äußere Join-Operation zu konvertieren, dann müssen Sie die Join-Bedingung für den einbettenden äußeren Join gemeinsam mit der WHERE-Bedingung berücksichtigen. Betrachten Sie folgende Abfrage:

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A AND T3.C=T1.C
  WHERE T3.D > 0 OR T1.D > 0

Hier ist die WHERE-Bedingung für den eingebetteten äußeren Join nicht nullabweisend, wohl aber die Join-Bedingung des einbettenden äußeren Joins T2.A=T1.A AND T3.C=T1.C. Insofern kann die Abfrage wie folgt konvertiert werden:

SELECT * FROM T1 LEFT JOIN
              (T2, T3)
              ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B
  WHERE T3.D > 0 OR T1.D > 0

In manchen Fällen kann MySQL mit einem Index eine ORDER BY-Klausel erfüllen, ohne dass eine zusätzliche Sortierung erforderlich wäre.

Der Index kann auch verwendet werden, wenn die ORDER BY-Klausel dem Index nicht exakt entspricht, solange alle nicht verwendeten Bestandteile des Indexes und alle zusätzlichen ORDER BY-Spalten Konstanten in der WHERE-Klausel sind. Die folgenden Abfragen lösen den ORDER BY-Teil mithilfe des Indexes auf:

SELECT * FROM t1 
  ORDER BY key_part1,key_part2,... ;
    
SELECT * FROM t1 
  WHERE key_part1=constant 
  ORDER BY key_part2;
    
SELECT * FROM t1 
  ORDER BY key_part1 DESC, key_part2 DESC;
    
SELECT * FROM t1
  WHERE key_part1=1 
  ORDER BY key_part1 DESC, key_part2 DESC;

In manchen Fällen kann MySQL Indizes nicht zur Auflösung von ORDER BY verwenden, obwohl es mithilfe der Indizes Datensätze findet, die der WHERE-Klausel entsprechen. Dies betrifft u. a. die folgenden Fälle:

Mit EXPLAIN SELECT … ORDER BY können Sie überprüfen, ob MySQL Indizes zur Auflösung der Abfrage verwenden kann. Wenn Sie Using filesort in der Spalte Extra sehen, ist dies nicht möglich. Siehe auch Abschnitt 7.2.1, „EXPLAIN-Syntax (Informationen über ein SELECT erhalten)“.

Die verwendete filesort-Optimierung vermerkt nicht nur den Sortierschlüsselwert und die Datensatzposition, sondern auch die Spalten, die für die Abfrage erforderlich sind. Hiermit wird verhindert, dass Datensätze zweimal ausgelesen werden. Der filesort-Algorithmus funktioniert wie folgt:

Dieser Algorithmus stellt gegenüber dem in älteren MySQL-Versionen verwendeten eine erhebliche Verbesserung dar.

Um eine Verlangsamung zu vermeiden, wird diese Optimierung nur verwendet, wenn die Gesamtgröße der zusätzlichen Spalten im Sortiertupel den Wert der Systemvariablen max_length_for_sort_data nicht überschreitet. (Wenn Sie dieser Variablen einen zu hohen Wert zuweisen, treten typische Symptome wie eine hohe Festplatten- und eine niedrige Prozessoraktivität auf.)

Wenn Sie die Geschwindigkeit von ORDER BY erhöhen wollen, überprüfen Sie, ob Sie MySQL dazu bewegen können, Indizes statt einer zusätzlichen Sortierphase zu verwenden. Ist dies nicht möglich, dann können Sie die folgenden Strategien ausprobieren:

Standardmäßig sortiert MySQL alle GROUP BY col1, col2, …-Abfragen so, als ob Sie in der Abfrage auch ORDER BY col1, col2, … angegeben hätten. Wenn Sie ausdrücklich eine ORDER BY-Klausel angeben, die dieselbe Spaltenliste enthält, optimiert MySQL diese ohne Geschwindigkeitseinbuße weg, auch wenn die Sortierung tatsächlich stattfindet. Enthält eine Abfrage eine GROUP BY-Klausel, während Sie die Mehrbelastung durch die Sortierung des Ergebnisses vermeiden wollen, dann können Sie diese Sortierung durch Angabe von ORDER BY NULL unterdrücken. Zum Beispiel:

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

Die allgemeinste Art und Weise, eine GROUP BY-Klausel zu erfüllen, besteht darin, die gesamte Tabelle zu scannen und eine neue Temporärtabelle zu erstellen, in der alle Datensätze aus allen Gruppen aufeinander folgend aufgeführt sind, und dann mithilfe dieser Temporärtabelle Gruppen zu erkennen und – sofern möglich – Zusammenfassungsfunktionen anzuwenden. In manchen Fällen kann MySQL jedoch auf andere Weise wesentlich effizienter vorgehen und die Erstellung von Temporärtabellen umgehen, indem ein Indexzugriff verwendet wird.

Die wichtigsten Voraussetzungen zur Verwendung von Indizes für GROUP BY bestehen darin, dass alle GROUP BY-Spalten Attribute aus demselben Index referenzieren und dass der Index seine Schlüssel in der Reihenfolge speichert (beispielsweise ist dies ein BTREE- und kein HASH-Index). Ob die Verwendung von Temporärtabellen durch einen Indexzugriff ersetzt werden kann, hängt auch davon ab, welche Teile eines Indexes in einer Abfrage verwendet werden, welche Bedingungen für diese Teile spezifiziert sind und welche Zusammenfassungsfunktionen ausgewählt wurden.

Es gibt zwei Möglichkeiten, eine GROUP BY-Abfrage über den Indexzugriff auszuführen; sie beide werden in den folgenden Abschnitten beschrieben. Bei der ersten Methode wird die Gruppierungsoperation ggf. gemeinsam mit allen Bereichsprädikaten angewandt. Die zweite Methode führt zunächst einen Bereichsscan durch und gruppiert dann die Ergebnistupel.

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

In manchen Fällen verarbeitet MySQL eine Abfrage anders, wenn Sie LIMIT row_count verwenden und HAVING nicht einsetzen:

Die Ausgabe von EXPLAIN zeigt ALL in der type-Spalte, wenn MySQL die Abfrage unter Verwendung eines Tabellenscans auflöst. Dies geschieht in der Regel unter den folgenden Bedingungen:

Bei kleinen Tabellen ist ein Tabellenscan oft die passende Lösung. Die Auswirkungen auf die Leistung sind vernachlässigbar. Bei großen Tabellen sollten Sie die folgenden Methoden ausprobieren, damit der Optimierer sich nicht fälschlicherweise für einen Tabellenscan entscheidet:

Die Zeit, die für das Einfügen eines Datensatzes erforderlich ist, wird von den folgenden Faktoren bestimmt (hierbei geben die Zahlen in den Klammern näherungsweise den Anteil an):

Hierbei wird der Mehraufwand für das Öffnen von Tabellen nicht berücksichtigt (dies ist einmal je nebenläufiger Abfrageausführung erforderlich).

Die Größe der Tabelle verlangsamt das Einfügen von Indizes um den Faktor LogN (für B-Tree-Indizes).

Sie können Einfügeoperationen mit den folgenden Methoden beschleunigen:

Eine Änderungsanweisung wird wie eine SELECT-Abfrage optimiert, es kommt jedoch der zusätzliche Mehraufwand einer Schreiboperation hinzu. Die Geschwindigkeit der Schreiboperation hängt von der Menge der zu ändernden Daten und der Anzahl der zu aktualisierenden Indizes ab. Indizes, die nicht geändert werden, werden nicht aktualisiert.

Eine andere Möglichkeit, Updates zu beschleunigen, besteht darin, sie aufzuschieben und dann viele Updates direkt hintereinander durchzuführen. Wenn Sie die Tabelle sperren, ist die gemeinsame Durchführung vieler Änderungen wesentlich schneller, als wenn Sie immer nur ein Update zur selben Zeit durchführen.

Bei einer MyISAM-Tabelle, die das dynamische Datensatzformat verwendet, kann die Änderung eines Datensatzes auf eine höhere Gesamtlänge dazu führen, dass der Datensatz geteilt wird. Wenn Sie dies oft tun, dürfen Sie keinesfalls vergessen, gelegentlich OPTIMIZE TABLE abzusetzen. Siehe auch Abschnitt 13.5.2.5, „OPTIMIZE TABLE“.

Die zum Löschen einzelner Datensätze erforderliche Zeit ist direkt proportional zur Anzahl der Indizes. Um Datensätze schneller zu löschen, können Sie den Wert des Schlüssel-Caches erhöhen, indem Sie die Systemvariable key_buffer_size entsprechend einstellen. Siehe auch Abschnitt 7.5.2, „Serverparameter feineinstellen“.

Wenn Sie alle Datensätze aus einer Tabelle löschen wollen, ist TRUNCATE TABLE tbl_name schneller als DELETE FROM tbl_name. Siehe auch Abschnitt 13.2.9, „TRUNCATE“.

Dieser Abschnitt listet eine Anzahl verschiedener Tipps zur Verbesserung der Abfrageverarbeitungsgeschwindigkeit auf:

MySQL verwendet die Sperrung auf Tabellenebene für MyISAM- und MEMORY-Tabellen, die Sperrung auf Seitenebene für BDB-Tabellen und die Sperrung auf Datensatzebene für InnoDB-Tabellen.

In vielen Fällen können Sie eine begründete Annahme dazu treffen, welcher Sperrungstyp der beste für eine Anwendung ist; generell ist es aber schwierig zu sagen, dass ein bestimmter Sperrungstyp besser ist als ein anderer. Alles hängt von der Anwendung ab, und verschiedene Teile einer Anwendung können unterschiedliche Sperrungstypen erfordern.

Um zu entscheiden, ob Sie eine Speicher-Engine mit Sperrung auf Datensatzebene verwenden sollten, müssen Sie einschätzen, was Ihre Anwendung tut und welche Mischung aus Auswahl- und Änderungsanweisungen sie verwendet. Die meisten Webanwendungen führen beispielsweise sehr viele Auswahloperationen, relativ wenig Löschvorgänge, vorzugsweise auf Schlüsselwerten basierende Änderungen und Einfügungen in nur ein paar bestimmte Tabellen durch. Die MyISAM-Basiskonfiguration von MySQL ist für solche Zwecke hervorragend geeignet.

Die Tabellensperrung in MySQL schließt ein Deadlock bei Speicher-Engines aus, die Sperren auf Tabellenebene verwenden. Die Deadlock-Vermeidung wird verwaltet, indem zu Anfang einer Abfrage immer alle erforderlichen Sperren auf einmal angefordert und die Tabellen immer in derselben Reihenfolge gesperrt werden.

Die Tabellensperrmethode, die MySQL für WRITE-Sperren verwendet, funktioniert wie folgt:

Die Tabellensperrmethode, die MySQL für READ-Sperren verwendet, funktioniert wie folgt:

Wenn eine Sperre aufgehoben wird, wird die Sperrmöglichkeit zunächst den Threads in der Warteschleife für Schreibsperranforderungen und dann den Threads in der Warteschleife für Lesesperranforderungen verfügbar gemacht: Wenn Sie also viele Änderungen an einer Tabelle vorgenommen haben, warten SELECT-Anweisungen, bis keine Änderungen mehr anhängig sind.

Sie können die Zugriffssituation bezüglich der Tabellensperren auf Ihrem System analysieren, indem Sie die Statusvariablen Table_locks_waited und Table_locks_immediate überprüfen:

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

Wenn eine MyISAM-Tabelle keine freien Blöcke in der Mitte enthält, werden Datensätze immer am Ende der Datendatei eingefügt. In diesem Fall können Sie INSERT- und SELECT-Anweisungen bei einer MyISAM-Tabelle ohne Sperre gleichzeitig verwenden. Sie können also Datensätze in eine MyISAM-Tabelle einfügen, während andere Clients zur selben Zeit daraus lesen. (Löcher können entstehen, wenn Datensätze in der Mitte der Tabelle gelöscht oder aktualisiert wurden. Wenn Löcher vorhanden sind, sind gleichzeitige Einfügeoperationen nicht möglich; sie werden allerdings sofort wieder verfügbar, sobald alle Löcher mit neuen Daten gefüllt wurden.)

Wenn Sie viele INSERT- und SELECT-Operationen an einer Tabelle durchführen wollen, während gleichzeitige Einfügeoperationen gerade nicht möglich sind, dann können Sie Datensätze in eine Temporärtabelle einfügen und die echte Tabelle dann sporadisch mit den Datensätzen aus dieser Temporärtabelle aktualisieren. Dies ist etwa mit dem folgenden Code möglich:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

InnoDB verwendet Datensatzsperren, BDB hingegen Seitensperren. Bei diesen beiden Speicher-Engines sind Deadlocks möglich, weil sie während der Verarbeitung von SQL-Anweisungen automatisch Sperren erwirken – und nicht gleich beim Start der Transaktion.

Vorteile der Sperrung auf Datensatzebene:

Nachteile der Sperrung auf Datensatzebene:

Tabellensperren sind Sperren auf Seiten- oder Datensatzebene in den folgenden Fällen überlegen:

Bei Sperren höherer Ebene können Sie Anwendungen einfacher optimieren, indem Sie Sperren verschiedener Typen unterstützen, weil der Overhead wesentlich geringer ist als bei Sperren auf Datensatzebene.

Andere Optionen als Sperrungen auf Datensatz- und Seitenebene:

Um eine sehr hohe Sperrgeschwindigkeit zu erzielen, verwendet MySQL die Tabellensperrung (statt der Sperrung auf Seiten-, Datensatz- oder Spaltenebene) für alle Speicher-Engines mit Ausnahme von InnoDB und BDB.

Bei InnoDB- und BDB-Tabellen setzt MySQL die Tabellensperrung nur dann ein, wenn Sie die Tabelle mit LOCK TABLES explizit sperren. Bei diesen Speicher-Engines raten wir von der Verwendung von LOCK TABLES vollständig ab, weil InnoDB eine automatische Sperrung auf Datensatzebene und BDB die Sperrung auf Seitenebene verwendet, um die Transaktionsisolierung sicherzustellen.

Bei großen Tabellen ist die Tabellensperrung für die meisten Anwendungen wesentlich besser als die Datensatzsperrung. Es gibt allerdings auch ein paar Fallstricke:

Die Tabellensperre ist auch in folgendem Szenario nachteilig:

Die folgenden Punkte beschreiben einige Möglichkeiten, um den konkurrierenden Zugriff, der durch Tabellensperren verursacht werden kann, zu vermeiden oder zumindest zu verringern:

Hier noch ein paar Tipps zu Tabellensperren in MySQL:

Bei einer MyISAM-Tabelle können Sie nebenläufige Einfügeoperationen verwenden, um Datensätze einzufügen, während gleichzeitig SELECT-Anweisungen ausgeführt werden, sofern sich in der Mitte der Tabelle keine gelöschten Datensätze befinden.

Unter Umständen, bei denen gleichzeitige Einfügeoperationen verwendet werden können, ist es nur sehr selten notwendig, den Modifizierer DELAYED für INSERT-Anweisungen zu verwenden. Siehe Abschnitt 13.2.4.2, „INSERT DELAYED“.

Wenn Sie das Binärlog verwenden, werden nebenläufige in normale Einfügeoperationen für die Anweisungen CREATE ... SELECT oder INSERT ... SELECT umgewandelt. Hierdurch wird gewährleistet, dass Sie eine exakte Kopie Ihrer Tabellen neu erstellen können, indem Sie das Log während eines Sicherungsvorgangs anwenden.

Bei LOAD DATA INFILE können, wenn Sie CONCURRENT bei einer MyISAM-Tabelle angeben, die die Bedingungen für gleichzeitige Einfügeoperationen erfüllt (d. h. keine freien Blöcke in der Mitte enthält), andere Threads Daten aus der Tabelle abrufen, während LOAD DATA ausgeführt wird. Die Verwendung dieser Option beeinflusst die Leistungsfähigkeit von LOAD DATA auch dann geringfügig, wenn gleichzeitig kein anderer Thread die Tabelle verwendet.

MySQL speichert Datensätze und Indexdaten in separaten Dateien. Viele andere Datenbanksysteme (sogar fast alle) speichern Datensätze und Indexdaten in derselben Datei. Wir sind allerdings der Ansicht, dass die Vorgehensweise von MySQL für eine Vielzahl moderner Systeme die bessere Wahl ist.

Eine andere Möglichkeit zur Speicherung der Datensätze besteht darin, die Daten für jede Spalte in einem separaten Bereich zu halten (Beispiele für Datenbanksysteme, die diesen Ansatz verwenden, sind SDBM und Focus). Allerdings führt dies zu einem erheblichen Leistungseinbruch, sobald auf mehr als eine Spalte zugegriffen wird. Aufgrund dieses Verhaltens beim Zugriff auf mehrere Spalten glauben wir nicht, dass dieses Modell für universelle Datenbanken gut geeignet ist.

Der häufigere Fall ist jedoch der, dass Indizes und Daten gemeinsam gespeichert werden (wie beispielsweise bei Oracle/Sybase usw.). In diesem Fall finden Sie die Datensatzinformationen auf der Blattseite des Indexes. Der Vorteil dieses Designs besteht darin, dass hierbei in vielen Fällen – abhängig davon, wie gut der Index zwischengespeichert wird – ein Lesevorgang auf der Festplatte eingespart wird. Es gibt aber auch Nachteile:

Eine der einfacheren Optimierungen besteht darin, Ihre Tabellen so zu implementieren, dass sie möglichst wenig Platz auf der Festplatte einnehmen. Dies kann erhebliche Verbesserungen zur Folge haben, weil die Lesevorgänge von der Festplatte schneller erfolgen und kleinere Tabellen normalerweise weniger Hauptspeicher benötigen, während ihre Inhalte bei der Abfrageausführung aktiv verarbeitet werden. Auch die Indizierung ist weniger ressourcenbeanspruchend, wenn sie an schmaleren Spalten vorgenommen wird.

MySQL unterstützt viele verschiedene Speicher-Engines (Tabellentypen) und Datensatzformate. Für jede Tabelle können Sie neu entscheiden, welche Speicher- und Indizierungsmethode verwendet werden soll. Die Auswahl des für Ihre Anwendung geeigneten Tabellenformats kann Ihnen einen erheblichen Performancezuwachs bringen. Siehe auch Kapitel 14, Speicher-Engines und Tabellentypen.

Sie können die Leistungsfähigkeit Ihrer Tabellen optimieren und den Speicherbedarf minimieren, indem Sie die folgenden Methoden benutzen:

Alle MySQL-Datentypen lassen sich indizieren. Die Verwendung von Indizes der relevanten Spalten ist die beste Möglichkeit, die Leistung von SELECT-Operationen zu optimieren.

Die maximale Anzahl von Indizes pro Tabelle und die maximale Indexlänge werden durch die verwendete Speicher-Engine bestimmt. Siehe auch Kapitel 14, Speicher-Engines und Tabellentypen. Alle Speicher-Engines unterstützen mindestens 16 Indizes pro Tabelle und eine Indexgesamtlänge von mindestens 256 Byte. Bei den meisten Speicher-Engines liegen die Grenzen höher.

Mit der Syntax col_name(N) in einer Indexdefinition können Sie einen Index erstellen, der nur die ersten N Zeichen einer String-Spalte verwendet. Wenn Sie auf diese Weise nur ein Präfix der Spaltenwerte indizieren, kann dies die Indexdatei erheblich kleiner machen. Bei der Indizierung einer BLOB- oder TEXT-Spalte müssen Sie eine Präfixlänge für den Index angeben. Zum Beispiel:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Präfixe können bis zu 1.000 Byte lang sein (767 Byte bei InnoDB-Tabellen). Beachten Sie, dass Präfixbeschränkungen in Byte angegeben werden, wohingegen die Präfixlänge in CREATE TABLE-Anweisungen als Anzahl der Zeichen interpretiert wird. Dies muss bei der Angabe einer Präfixlänge für eine Spalte, die einen Multibytezeichensatz verwendet, in jedem Fall berücksichtigt werden.

Sie können außerdem FULLTEXT-Indizes erstellen. Diese werden für die Volltextsuche verwendet. Nur die MyISAM-Speicher-Engine unterstützt FULLTEXT-Indizes, und auch nur für CHAR-, VARCHAR- und TEXT-Spalten. Die Indizierung erfolgt stets über die gesamte Spalte – eine Teilindizierung (Spaltenpräfix) wird nicht unterstützt. Detaillierte Informationen finden Sie in Abschnitt 12.7, „MySQL-Volltextsuche“.

Sie können auch Indizes für raumbezogene Typen erstellen. Derzeit unterstützt nur MyISAM R-Tree-Indizes für raumbezogene Typen. Andere Speicher-Engines verwenden B-Trees zur Indizierung raumbezogener Typen. (Ausnahmen sind lediglich ARCHIVE und NDBCLUSTER, die überhaupt keine raumbezogene Indizierung unterstützen.)

Die MEMORY-Engine verwendet standardmäßig HASH-Indizes, unterstützt aber auch BTREE-Indizes.

MySQL kann zusammengesetzte Indizes erstellen (d. h. Indizes über mehrere Spalten). Ein Index kann bis zu 15 Spalten umfassen. Bei bestimmten Datentypen können Sie ein Präfix der Spalte indizieren (siehe Abschnitt 7.4.3, „Spaltenindizes“).

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

MySQL verwendet mehrspaltige Indizes derart, dass Abfragen schnell sind, wenn Sie einen bekannten Teil der ersten Spalte des Indexes in einer WHERE-Klausel angeben – und zwar auch dann, wenn Sie keine Werte für andere Spalten angeben.

Angenommen, eine Tabelle habe die folgende Spezifikation:

CREATE TABLE test (
    id INT NOT NULL,
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

Der Index name ist ein Index über die Spalten last_name und first_name. Der Index kann für Abfragen, die Werte in einem bekannten Bereich für last_name angeben, oder sowohl für last_name als auch für first_name verwendet werden. Aus diesem Grund wird der Index name in den folgenden Abfragen benutzt:

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

Im Gegensatz dazu wird der Index name in den folgenden Abfragen nicht benutzt:

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

Die Art und Weise, wie MySQL Indizes zur Verbesserung der Abfrageleistung verwendet, wird in Abschnitt 7.4.5, „Wie MySQL Indizes benutzt“, näher beschrieben.

Indizes werden verwendet, um schnell Datensätze mit bestimmten Spaltenwerten zu finden. Ohne Index müsste MySQL mit der Suche beim ersten Datensatz beginnen und dann die gesamte Tabelle nach passenden Datensätzen durchforsten. Je größer die Tabelle, desto höher sind die Kosten dafür. Wenn die Tabelle einen Index für die fraglichen Spalten aufweist, kann MySQL die Position in der Mitte der Datendatei, an der die Suche beginnen soll, schnell bestimmen, ohne alle Daten in der Datei überprüfen zu müssen. Wenn eine Tabelle 1.000 Datensätze hat, ist dies mindestens hundertmal schneller als ein sequenzielles Lesen. Wenn Sie hingegen auf die meisten Datensätze zugreifen müssen, ist das sequenzielle Lesen schneller, weil die Anzahl der Festplattenzugriffe geringer ist.

Die meisten MySQL-Indizes (PRIMARY KEY, UNIQUE, INDEX und FULLTEXT) sind in B-Trees gespeichert. Zu erwähnende Ausnahmen sind, dass Indizes für raumbezogene Datentypen R-Typen verwenden und MEMORY-Tabellen auch Hash-Indizes unterstützen.

Strings werden automatisch präfixkomprimiert; Gleiches gilt für Leerzeichen am Ende des Strings. Siehe auch Abschnitt 13.1.4, „CREATE INDEX“.

Generell werden Indizes wie nachfolgend beschrieben verwendet. Die spezifischen Eigenschaften von Hash-Indizes, die in Verbindung mit MEMORY-Tabellen verwendet werden, werden am Ende dieses Abschnitts erläutert.

MySQL verwendet Indizes für die folgenden Operationen:

Nehmen wir an, dass Sie folgende SELECT-Anweisung absetzen:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

Wenn für col1 und col2 ein mehrspaltiger Index vorhanden ist, können die entsprechenden Datensätze direkt abgerufen werden. Sind separate einspaltige Indizes für col1 und col2 vorhanden, dann versucht der Optimierer, den restriktivsten Index zu ermitteln, indem er überprüft, welcher Index weniger Spalten findet; dieser Index wird dann zum Abrufen von Datensätzen benutzt.

Wenn die Tabelle einen mehrspaltigen Index aufweist, kann ein beliebiges linkes Präfix des Indexes vom Optimierer zum Suchen von Datensätzen verwendet werden. Haben Sie beispielsweise einen dreispaltigen Index für (col1, col2, col3), dann bietet dieser Suchfunktionalität für (col1), (col1, col2) und (col1, col2, col3).

MySQL kann keinen Teilindex verwenden, wenn die Spalten kein linkes Präfix für den Index bilden. Betrachten Sie einmal die folgenden SELECT-Anweisungen:

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Wenn ein Index für (col1, col2, col3) vorhanden ist, verwenden diese nur die ersten beiden Abfragen. Die dritte und die vierte Abfrage beziehen sich zwar auf indizierte Spalten, aber (col2) und (col2, col3) sind keine linken Präfixe von (col1, col2, col3).

Ein B-Tree-Index kann für Spaltenvergleiche in Ausdrücken benutzt werden, die die Operatoren =, >, >=, <, <= oder BETWEEN verwenden. Der Index kann auch für LIKE-Vergleiche benutzt werden, wenn das Argument zu LIKE ein Konstanten-String ist, der nicht mit einem Jokerzeichen beginnt. Die folgenden SELECT-Anweisungen verwenden beispielsweise Indizes:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

In der ersten Anweisung werden nur Datensätze mit 'Patrick' <= key_col< 'Patricl' berücksichtigt. In der zweiten Anweisung werden nur Datensätze mit 'Pat' <= key_col< 'Pau' berücksichtigt.

Die folgenden SELECT-Anweisungen verwenden keine Indizes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In der ersten Anweisung beginnt der Wert LIKE mit einem Jokerzeichen. In der zweiten Anweisung ist der LIKE-Wert keine Konstante.

Wenn Sie … LIKE '%string%' verwenden und string mehr als drei Zeichen umfasst, verwendet MySQL den Turbo-Boyer-Moore-Algorithmus zur Initialisierung des Musters für den String; mit diesem Muster wird die Suche dann schneller durchgeführt.

Eine Suche unter Verwendung von col_name IS NULL setzt Indizes ein, wenn col_name indiziert ist.

Ein Index, der nicht alle AND-Ebenen in der WHERE-Klausel einbezieht, wird nicht zur Optimierung der Abfrage benutzt. Um also einen Index verwenden zu können, muss in jeder AND-Gruppe ein Präfix des Indexes benutzt werden.

Die folgenden WHERE-Klauseln verwenden Indizes:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

Die folgenden WHERE-Klauseln verwenden Indizes nicht:

    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10

Manchmal verwendet MySQL auch dann keinen Index, wenn ein solcher vorhanden ist. Ein Umstand, unter dem dies geschieht, liegt vor, wenn der Optimierer der Ansicht ist, dass MySQL bei Verwendung eines Indexes auf einen sehr großen Anteil der Datensätze in der Tabelle zugreifen muss. (In diesem Fall ist ein Tabellenscan mit hoher Wahrscheinlichkeit schneller, weil weniger Suchvorgänge erforderlich sind.) Wenn eine solche Abfrage jedoch dank einer LIMIT-Klausel nur ein paar Datensätze abruft, verwendet MySQL trotzdem einen Index, da sich die Datensätze, die zum Ergebnis gehören, auf diese Weise schneller finden lassen.

Hash-Indizes weisen etwas andere Eigenschaften auf als die soeben beschriebenen:

Um die Festplattenzugriffe zu minimieren, verwendet die MyISAM-Speicher-Engine eine Strategie, auf der zahlreiche Datenbanksysteme basieren. Sie benutzt einen Cache-Mechanismus, um die am häufigsten abgerufenen Tabellenblöcke im Speicher zu halten:

Dieser Abschnitt beschreibt zunächst den grundlegenden Betrieb des MyISAM-Schlüssel-Caches. Nachfolgend werden Funktionen erläutert, die die Leistungsfähigkeit des Schlüssel-Caches optimieren und mit denen Sie den Cache-Betrieb besser steuern können:

Die Größe des Schlüssel-Caches stellen Sie mit der Systemvariablen key_buffer_size ein. Wenn diese Variable auf null gesetzt ist, wird kein Cache verwendet. Der Schlüssel-Cache wird ferner nicht benutzt, wenn der Wert key_buffer_size zu klein ist, um die Mindestanzahl von Blockpuffern (8) zu reservieren.

Wenn der Schlüssel-Cache nicht betriebsbereit ist, erfolgt der Zugriff auf Indexdateien nur über die Puffer des nativen Dateisystems, die vom Betriebssystem bereitgestellt werden. (Anders gesagt, wird auf Tabellenindexblöcke unter Verwendung derselben Methode zugegriffen wie auf Tabellendatenblöcke.)

Ein Indexblock ist eine zusammenhängende Zugriffseinheit für MyISAM-Indexdateien. Normalerweise entspricht die Größe eines Indexblocks der Größe der Knoten des B-Trees. (Indizes werden auf der Festplatte mit einer B-Tree-Datenstruktur dargestellt. Knoten am unteren Ende des Trees sind Blattknoten, Knoten oberhalb der Blätter Nichtblattknoten.)

Alle Blockpuffer in einer Schlüssel-Cache-Struktur haben dieselbe Größe. Diese Größe kann der Größe eines Tabellenindexblocks entsprechen, sie kann aber auch höher oder niedriger sein. Normalerweise ist einer dieser beiden Werte ein Vielfaches des anderen.

Wenn ein Zugriff auf Daten aus einem Tabellenindexblock erfolgen muss, dann überprüft der Server zunächst, ob er in einem Blockpuffer des Schlüssel-Caches vorhanden ist. Ist dies der Fall, dann greift der Server auf die Daten im Schlüssel-Cache statt auf der Festplatte zu: Er liest aus und/oder schreibt in den Cache statt auf die Festplatte. Andernfalls wählt der Server einen Cache-Blockpuffer, der einen oder mehrere andere Tabellenindexblöcke enthält, und ersetzt die dortigen Daten durch eine Kopie des erforderlichen Tabellenindexblocks. Sobald der neue Indexblock sich im Cache befindet, kann auf die Indexdaten zugegriffen werden.

Wenn ein für die Ersetzung gewählter Block modifiziert wurde, wird dieser als „schmutzig“ bezeichnet. In diesem Fall wird der Inhalt vor der Ersetzung in den Tabellenindex geschrieben, von dem er kam.

Normalerweise verwendet der Server eine LRU-Strategie (Least Recently Used): Wenn ein Block zur Ersetzung gewählt werden muss, entscheidet er sich für den Indexblock, der am längsten nicht mehr verwendet wurde. Um die Auswahl zu erleichtern, unterhält das Schlüssel-Cache-Modul eine spezielle Warteschlange (die LRU-Kette) mit allen verwendeten Blöcken. Wenn auf einen Block zugegriffen wird, wird er ans Ende der Warteschlange gesetzt. Müssen Blöcke ersetzt werden, dann sind die Blöcke am Anfang der Warteschlange die am längsten nicht verwendeten und werden infolgedessen als Erste geräumt.

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

mysql> SET GLOBAL keycache1.key_buffer_size=0;

mysql> SET GLOBAL key_buffer_size = 0;

mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8384512 |
+-----------------+---------+

key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache
CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;

Speicher-Engines sammeln tabellenspezifische Statistiken, die der Optimierer verwenden kann. Tabellenstatistiken basieren auf Wertegruppen. Eine Wertegruppe ist eine Menge von Datensätzen mit demselben Schlüsselpräfixwert. Für die Zwecke des Optimierers ist die durchschnittliche Wertegruppengröße eine wichtige Angabe.

MySQL verwendet diese Information,

Wenn die Durchschnittsgröße der Wertegruppen für einen Index ansteigt, wird der Index für diese beiden Aufgaben zunehmend weniger nützlich, weil auch die durchschnittliche Anzahl von Datensätzen pro Suchvorgang steigt: Damit der Index für Optimierungszwecke gut geeignet ist, wäre es optimal, wenn jeder Indexwert auf eine kleine Anzahl von Datensätzen in der Tabelle abzielt. Hat ein gegebener Indexwert eine hohe Zahl von Datensätzen zum Ergebnis, dann ist sein Nutzen eingeschränkt, und die Wahrscheinlichkeit, dass MySQL ihn verwendet, ist geringer.

Die durchschnittliche Wertegruppengröße bezieht sich auf die Kardinalität der Tabelle, d. h. die Anzahl der Wertegruppen. Die SHOW INDEX-Anweisung zeigt einen Kardinalitätswert basierend auf N ÷ S an, wobei N die Anzahl der Datensätze in der Tabelle und S die durchschnittliche Wertegruppengröße darstellt. Dieses Verhältnis gibt einen Näherungswert für die Anzahl der Wertegruppen in der Tabelle an.

Bei einem Join, der auf dem Vergleichsoperator <=> basiert, wird NULL nicht anders behandelt als andere Werte: NULL <=> NULL (wie N<=> N für jeden anderen Wert N).

Basiert ein Join jedoch auf dem Operator =, dann unterscheidet sich NULL von Nicht-NULL-Werten: expr1 = expr2 ist nicht wahr, wenn expr1 oder expr2 (oder beide) NULL sind. Dies wirkt sich auf ref-Zugriffe für Vergleiche der Form tbl_name.key = expr aus: MySQL greift nicht auf die Tabelle zu, wenn der aktuelle Wert von expr NULL ist, weil der Vergleich nicht wahr sein kann.

Bei =-Vergleichen spielt es keine Rolle, wie viele NULL-Werte in der Tabelle vorhanden sind. Der für Optimierungszwecke relevante Wert ist die Durchschnittsgröße der Gruppe von Nicht-NULL-Werten. Allerdings gestattet MySQL derzeit keine Ermittlung oder Verwendung dieser Durchschnittsgröße.

Bei MyISAM-Tabellen haben Sie mit der Systemvariablen myisam_stats_method in eingeschränktem Maße Kontrolle über die Ermittlung der Tabellenstatistiken. Diese Variable hat zwei mögliche Werte, die sich wie folgt voneinander unterscheiden:

Wenn Sie häufig viele Joins benutzen, die <=> statt = verwenden, dann werden NULL-Werte in Vergleichen nicht gesondert betrachtet, sondern ein NULL-Wert entspricht dem anderen. In diesem Fall ist nulls_equal die passende Statistikmethode.

Die Systemvariable myisam_stats_method hat globale und sitzungsbezogene Werte. Das Einstellen des globalen Werts wirkt sich auf die MyISAM-Statistikermittlung bei allen MyISAM-Tabellen aus. Im Gegensatz dazu wirkt der Sitzungswert nur auf Statistiken zur aktuellen Clientverbindung. Sie können also, indem Sie den Sitzungswert von myisam_stats_method einstellen, die Neuerstellung der Statistiken zu einer Tabelle mit einer gegebenen Methode erzwingen, ohne dass andere Clients hiervon beeinträchtigt würden.

Zur Neuerstellung der Tabellenstatistiken können Sie eine der folgenden Methoden benutzen:

Die Verwendung von myisam_stats_method hat auch ein paar Nachteile:

Sie können die Ermittlung von Tabellenstatistiken wie gerade beschrieben erzwingen. Allerdings kann MySQL die Statistiken auch automatisch sammeln. Wenn beispielsweise während der Ausführung von Anweisungen für eine Tabelle einige dieser Anweisungen die Tabelle ändern, ermittelt MySQL unter Umständen Statistiken. (Dies kann etwa bei Masseneinfüge- oder -löschoperationen oder bei bestimmten ALTER TABLE-Anweisungen der Fall sein.) Wenn dies geschieht, werden die Statistiken ohne Berücksichtigung des aktuellen Werts von myisam_stats_method gesammelt. Wenn Sie also Statistiken mit einer bestimmten Methode ermitteln, aber myisam_stats_method bei einer späteren automatischen Statistiksammlung auf eine andere Methode gesetzt wird, dann wird diese andere Methode verwendet.

Es gibt keine Möglichkeit, festzustellen, welche Methode zur Erzeugung von Statistiken für eine gegebene MyISAM-Tabelle verwendet wurde.

myisam_stats_method gilt nur für MyISAM-Tabellen. Die übrigen Speicher-Engines haben jeweils eigene Methoden zur Ermittlung von Tabellenstatistiken. Diese ähneln der Methode nulls_equal.

Wenn Sie einen mysqladmin status-Befehl ausführen, sollten Sie etwa folgendes Ergebnis erhalten:

Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12

Der Open tables-Wert von 12 kann etwas verwirrend sein, wenn Sie nur sechs Tabellen haben.

MySQL arbeitet mit mehreren Threads, d. h., mehrere Clients setzen unter Umständen gleichzeitig Abfragen für eine gegebene Tabelle ab. Um das Problem mit mehreren Client-Threads, die unterschiedliche Zustände bezüglich derselben Tabelle haben, zu minimieren, wird die Tabelle von allen nebenläufigen Threads unabhängig geöffnet. Zwar wird hierdurch mehr Speicher verbraucht, aber in der Regel wird die Leistung verbessert. Bei MyISAM-Tabellen ist pro Client, der eine Tabelle geöffnet hält, ein zusätzlicher Dateideskriptor für die Datendatei erforderlich. (Dies steht im Gegensatz zum Indexdateideskriptor, der von allen Threads gemeinsam verwendet wird.)

Die Systemvariablen table_open_cache, max_connections und max_tmp_tables bestimmen die maximale Anzahl der Dateien, die der Server geöffnet hält. Wenn Sie einen oder mehrere dieser Werte erhöhen, stoßen Sie unter Umständen auf ein Limit, das Ihr Betriebssystem bezüglich der prozessbezogenen Anzahl offener Dateideskriptoren setzt. Viele Betriebssysteme erlauben Ihnen eine Anhebung der Beschränkung für offene Dateien, auch wenn sich die jeweilige Methode von Betriebssystem zu Betriebssystem unterscheidet. Informationen dazu, ob und wie es möglich ist, diesen Wert anzuheben, entnehmen Sie der Dokumentation zu Ihrem Betriebssystem.

table_open_cache bezieht sich auf max_connections. So sollten Sie etwa für 200 gleichzeitig laufende Verbindungen einen Tabellen-Cache mit einer Größe von mindestens 200 × N haben, wobei N die maximale Anzahl von Tabellen pro Join in einer beliebigen von Ihnen ausgeführten Abfrage ist. Sie müssen ferner einige zusätzliche Dateideskriptoren für Temporärtabellen und -dateien reservieren.

Stellen Sie sicher, dass Ihr Betriebssystem die Anzahl der mit der Einstellung von table_open_cache verbundenen Deskriptoren für offene Dateien verarbeiten kann. Wenn für table_open_cache ein zu hoher Wert eingestellt ist, stehen für MySQL irgendwann unter Umständen keine Dateideskriptoren mehr zur Verfügung: MySQL verweigert dann Verbindungen, kann Abfragen nicht ausführen und wird im Ganzen sehr instabil. Sie müssen ferner berücksichtigen, dass die MyISAM-Speicher-Engine zwei Dateideskriptoren für jede offene Tabelle benötigt. Sie können die Anzahl der MySQL zur Verfügung stehenden Dateideskriptoren mit der Startoption --open-files-limit für mysqld_safe erhöhen. Siehe auch Abschnitt A.2.17, „Datei nicht gefunden“.

Der Cache für die offenen Tabellen wird auf einem Niveau von table_open_cache Einträgen gehalten. Der Standardwert beträgt 64, kann aber mit der Option --table_open_cache für mysqld geändert werden. Beachten Sie, dass MySQL zur Ausführung von Abfragen vorübergehend auch mehr Tabellen öffnen kann.

MySQL schließt nicht verwendete Tabellen und entfernt sie aus dem Tabellen-Cache, wenn

Wenn der Tabellen-Cache Einträge enthält, ermittelt der Server einen zu verwendenden Cache-Eintrag wie folgt:

Ist der Cache vorübergehend erweitert und eine Tabelle erhält den Status „nicht verwendet“, dann wird diese Tabelle geschlossen und aus dem Cache entfernt.

Eine Tabelle wird für jeden nebenläufigen Zugriff geöffnet. Das bedeutet, dass die Tabelle zweimal geöffnet werden muss, wenn zwei Threads auf dieselbe Tabelle zugreifen oder ein Thread die Tabelle zweimal in derselben Abfrage referenziert (beispielsweise beim Join einer Tabelle mit sich selbst). Jeder gleichzeitige Öffnungsvorgang erfordert einen Eintrag im Tabellen-Cache. Der erste Öffnungsvorgang einer MyISAM-Tabelle nimmt zwei Dateideskriptoren entgegen, nämlich je einen für die Daten- und die Indexdatei. Jede zusätzliche Verwendung der Tabelle benötigt nur einen Deskriptor für die Datendatei. Der Deskriptor für die Indexdateien wird von allen Threads gemeinsam verwendet.

Wenn Sie eine Tabelle mit der Anweisung HANDLER tbl_name OPEN öffnen, wird für den Thread ein dediziertes Tabellenobjekt reserviert. Dieses Tabellenobjekt wird nicht mit anderen Threads gemeinsam genutzt und wird erst geschlossen, wenn der Thread HANDLER tbl_name CLOSE aufruft oder beendet wird. In diesem Fall wird die Tabelle in den Tabellen-Cache zurückgeführt (sofern dieser nicht voll ist). Siehe auch Abschnitt 13.2.3, „HANDLER“.

Sie können ermitteln, ob Ihr Tabellen-Cache zu klein ist, indem Sie die Statusvariable Opened_tables für mysqld überprüfen:

mysql> SHOW STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

Wenn der Wert sehr groß ist (und zwar auch dann, wenn Sie nicht viele FLUSH TABLES-Anweisungen abgesetzt haben), dann sollten Sie die Größe des Tabellen-Caches erhöhen. Siehe auch Abschnitt 5.2.2, „Server-Systemvariablen“, und Abschnitt 5.2.4, „Server-Statusvariablen“.

Wenn sich viele MyISAM-Tabellen im selben Datenbankverzeichnis befinden, verlaufen Operationen wie das Öffnen, Schließen und Erstellen von Tabellen sehr langsam. Führen Sie SELECT-Anweisungen für viele verschiedene Tabellen aus, dann tritt eine geringe Mehrbelastung auf, wenn der Tabellen-Cache voll ist, weil für jede Tabelle, die geöffnet wird, eine andere geschlossen werden muss. Sie können diese Mehrbelastung verringern, indem Sie den Tabellen-Cache vergrößern.

Wir beginnen mit den Faktoren auf der Systemebene, weil einige dieser Entscheidungen sehr früh getroffen werden müssen, um einen beträchtlichen Leistungszugewinn zu erzielen. In anderen Fällen kann ein kurzer Blick in diesen Abschnitt ausreichen. Allerdings kann es immer praktisch sein, ein Gespür dafür zu entwickeln, welcher Zugewinn sich durch Änderung von Faktoren auf dieser Ebene erzielen lässt.

Das Betriebssystem, welches verwendet werden soll, ist ein wesentlicher Faktor. Um aus Mehrprozessorrechnern die optimale Leistung zu erzielen, sollten Sie Solaris (aufgrund seiner gut funktionierenden Thread-Implementierung) oder Linux verwenden (weil der Kernel der Versionen 2.4 und höher eine gute SMP-Unterstützung bietet). Beachten Sie, dass ältere Linux-Kernels standardmäßig eine Dateigrößenbeschränkung auf 2 Gbyte aufweisen. Wenn Sie einen solchen Kernel verwenden, Ihre Dateien aber größer werden könnten als 2 Gbyte, dann sollten Sie sich den LFS-Patch (Large File Support) für das ext2-Dateisystem besorgen. Andere Dateisysteme wie ReiserFS und XFS weisen diese Beschränkung auf 2 Gbyte nicht auf.

Bevor Sie MySQL in die Produktion nehmen, sollten Sie es auf der vorgesehenen Plattform testen.

Weitere Tipps:

Mit dem folgenden Befehl können Sie die standardmäßigen Puffergrößen festlegen, die der Server mysqld verwendet:

shell> mysqld --verbose --help

Dieser Befehl erzeugt eine Liste aller mysqld-Optionen und konfigurierbarer Systemvariablen. Die Ausgabe enthält die Standardwerte der Variablen und sieht etwa so aus:

help                              TRUE
abort-slave-event-count           0
allow-suspicious-udfs             FALSE
auto-increment-increment          1
auto-increment-offset             1
automatic-sp-privileges           TRUE
basedir                           /home/jon/bin/mysql/
bdb                               FALSE
bind-address                      (No default value)
character-set-client-handshake    TRUE
character-set-server              latin1
character-sets-dir                /home/jon/bin/mysql/share/mysql/charsets/
chroot                            (No default value)
collation-server                  latin1_swedish_ci
completion-type                   0
concurrent-insert                 1
console                           FALSE
datadir                           /home/jon/bin/mysql/var/
default-character-set             latin1
default-collation                 latin1_swedish_ci
default-time-zone                 (No default value)
disconnect-slave-event-count      0
enable-locking                    FALSE
enable-pstack                     FALSE
engine-condition-pushdown         FALSE
external-locking                  FALSE
gdb                               FALSE
large-pages                       FALSE
init-connect                      (No default value)
init-file                         (No default value)
init-slave                        (No default value)
innodb                            TRUE
innodb_checksums                  TRUE
innodb_data_home_dir              (No default value)
innodb_doublewrite                TRUE
innodb_fast_shutdown              1
innodb_file_per_table             FALSE
innodb_flush_log_at_trx_commit    1
innodb_flush_method               (No default value)
innodb_locks_unsafe_for_binlog    FALSE
innodb_log_arch_dir               (No default value)
innodb_log_group_home_dir         (No default value)
innodb_max_dirty_pages_pct        90
innodb_max_purge_lag              0
innodb_status_file                FALSE
innodb_table_locks                TRUE
innodb_support_xa                 TRUE
isam                              FALSE
language                          /home/jon/bin/mysql/share/mysql/english
local-infile                      TRUE
log                               /home/jon/bin/mysql/var/master1.log
log-bin                           /home/jon/bin/mysql/var/master1
log-bin-index                     (No default value)
log-bin-trust-routine-creators    FALSE
log-error                         /home/jon/bin/mysql/var/master1.err
log-isam                          myisam.log
log-queries-not-using-indexes     FALSE
log-short-format                  FALSE
log-slave-updates                 FALSE
log-slow-admin-statements         FALSE
log-slow-queries                  (No default value)
log-tc                            tc.log
log-tc-size                       24576
log-update                        (No default value)
log-warnings                      1
low-priority-updates              FALSE
master-connect-retry              60
master-host                       (No default value)
master-info-file                  master.info
master-password                   (No default value)
master-port                       3306
master-retry-count                86400
master-ssl                        FALSE
master-ssl-ca                     (No default value)
master-ssl-capath                 (No default value)
master-ssl-cert                   (No default value)
master-ssl-cipher                 (No default value)
master-ssl-key                    (No default value)
master-user                       test
max-binlog-dump-events            0
memlock                           FALSE
myisam-recover                    OFF
ndbcluster                        FALSE
ndb-connectstring                 (No default value)
ndb-mgmd-host                     (No default value)
ndb-nodeid                        0
ndb-autoincrement-prefetch-sz     32
ndb-distibution                   KEYHASH
ndb-force-send                    TRUE
ndb_force_send                    TRUE
ndb-use-exact-count               TRUE
ndb_use_exact_count               TRUE
ndb-shm                           FALSE
ndb-optimized-node-selection      TRUE
ndb-cache-check-time              0
ndb-index-stat-enable             TRUE
ndb-index-stat-cache-entries      32
ndb-index-stat-update-freq        20
new                               FALSE
old-alter-table                   FALSE
old-passwords                     FALSE
old-style-user-limits             FALSE
pid-file                          /home/jon/bin/mysql/var/hostname.pid1
port                              3306
relay-log                         (No default value)
relay-log-index                   (No default value)
relay-log-info-file               relay-log.info
replicate-same-server-id          FALSE
report-host                       (No default value)
report-password                   (No default value)
report-port                       3306
report-user                       (No default value)
rpl-recovery-rank                 0
safe-user-create                  FALSE
secure-auth                       FALSE
server-id                         1
show-slave-auth-info              FALSE
skip-grant-tables                 FALSE
skip-slave-start                  FALSE
slave-load-tmpdir                 /tmp/
socket                            /tmp/mysql.sock
sporadic-binlog-dump-fail         FALSE
sql-mode                          OFF
symbolic-links                    TRUE
tc-heuristic-recover              (No default value)
temp-pool                         TRUE
timed_mutexes                     FALSE
tmpdir                            (No default value)
use-symbolic-links                TRUE
verbose                           TRUE
warnings                          1
back_log                          50
binlog_cache_size                 32768
bulk_insert_buffer_size           8388608
connect_timeout                   5
date_format                       (No default value)
datetime_format                   (No default value)
default_week_format               0
delayed_insert_limit              100
delayed_insert_timeout            300
delayed_queue_size                1000
expire_logs_days                  0
flush_time                        0
ft_max_word_len                   84
ft_min_word_len                   4
ft_query_expansion_limit          20
ft_stopword_file                  (No default value)
group_concat_max_len              1024
innodb_additional_mem_pool_size   1048576
innodb_autoextend_increment       8
innodb_buffer_pool_awe_mem_mb     0
innodb_buffer_pool_size           8388608
innodb_concurrency_tickets        500
innodb_file_io_threads            4
innodb_force_recovery             0
innodb_lock_wait_timeout          50
innodb_log_buffer_size            1048576
innodb_log_file_size              5242880
innodb_log_files_in_group         2
innodb_mirrored_log_groups        1
innodb_open_files                 300
innodb_sync_spin_loops            20
innodb_thread_concurrency         20
innodb_commit_concurrency         0
innodb_thread_sleep_delay         10000
interactive_timeout               28800
join_buffer_size                  131072
key_buffer_size                   8388600
key_cache_age_threshold           300
key_cache_block_size              1024
key_cache_division_limit          100
long_query_time                   10
lower_case_table_names            0
max_allowed_packet                1048576
max_binlog_cache_size             4294967295
max_binlog_size                   1073741824
max_connect_errors                10
max_connections                   100
max_delayed_threads               20
max_error_count                   64
max_heap_table_size               16777216
max_join_size                     4294967295
max_length_for_sort_data          1024
max_relay_log_size                0
max_seeks_for_key                 4294967295
max_sort_length                   1024
max_tmp_tables                    32
max_user_connections              0
max_write_lock_count              4294967295
multi_range_count                 256
myisam_block_size                 1024
myisam_data_pointer_size          6
myisam_max_extra_sort_file_size   2147483648
myisam_max_sort_file_size         2147483647
myisam_repair_threads             1
myisam_sort_buffer_size           8388608
myisam_stats_method               nulls_unequal
net_buffer_length                 16384
net_read_timeout                  30
net_retry_count                   10
net_write_timeout                 60
open_files_limit                  0
optimizer_prune_level             1
optimizer_search_depth            62
preload_buffer_size               32768
query_alloc_block_size            8192
query_cache_limit                 1048576
query_cache_min_res_unit          4096
query_cache_size                  0
query_cache_type                  1
query_cache_wlock_invalidate      FALSE
query_prealloc_size               8192
range_alloc_block_size            2048
read_buffer_size                  131072
read_only                         FALSE
read_rnd_buffer_size              262144
div_precision_increment           4
record_buffer                     131072
relay_log_purge                   TRUE
relay_log_space_limit             0
slave_compressed_protocol         FALSE
slave_net_timeout                 3600
slave_transaction_retries         10
slow_launch_time                  2
sort_buffer_size                  2097144
sync-binlog                       0
sync-frm                          TRUE
sync-replication                  0
sync-replication-slave-id         0
sync-replication-timeout          10
table_open_cache                  64
table_lock_wait_timeout           50
thread_cache_size                 0
thread_concurrency                10
thread_stack                      196608
time_format                       (No default value)
tmp_table_size                    33554432
transaction_alloc_block_size      8192
transaction_prealloc_size         4096
updatable_views_with_limit        1
wait_timeout                      28800

Wird gerade ein mysqld-Server ausgeführt, dann können Sie die aktuellen Werte seiner Systemvariablen anzeigen, indem Sie eine Verbindung mit ihm herstellen und die folgende Anweisung absetzen:

mysql> SHOW VARIABLES;

Mit der folgenden Anweisung können Sie statistische Informationen und Statusangaben für einen laufenden Server abrufen:

mysql> SHOW STATUS;

Angaben zu Systemvariablen und Status erhalten Sie auch mit mysqladmin:

shell> mysqladmin variables
shell> mysqladmin extended-status

Eine vollständige Beschreibung aller System- und Statusvariablen finden Sie in Abschnitt 5.2.2, „Server-Systemvariablen“, und Abschnitt 5.2.4, „Server-Statusvariablen“.

MySQL verwendet Algorithmen, die sehr gut skalierbar sind, d. h., Sie können MySQL gewöhnlich mit sehr wenig Speicher ausführen. Andererseits erhalten Sie eine umso bessere Performance, je mehr Speicher Sie MySQL zugestehen.

Wenn Sie einen MySQL Server optimieren, dann sind die beiden wichtigsten zu konfigurierenden Variablen key_buffer_size und table_open_cache. Sie sollten erst ganz sicher sein, dass Sie für diese Variablen geeignete Werte eingestellt haben, bevor Sie andere Variablen zu konfigurieren versuchen.

Die folgenden Beispiele zeigen einige typische Variablenwerte für verschiedene Laufzeitkonfigurationen.

Wenn Sie GROUP BY- oder ORDER BY-Operationen an Tabellen durchführen, die wesentlich größer sind als der verfügbare Speicher, dann sollten Sie den Wert von read_rnd_buffer_size erhöhen, um nach Durchführung von Sortieroperationen das Auslesen von Datensätzen zu beschleunigen.

Wenn Sie MySQL installiert haben, enthält das Verzeichnis support-files eine Anzahl verschiedener my.cnf-Beispieldateien, nämlich my-huge.cnf, my-large.cnf, my-medium.cnf und my-small.cnf. Diese können Sie als Basis zur Optimierung Ihres Systems verwenden. (Unter Windows schauen Sie im MySQL-Installationsverzeichnis nach.)

Wenn Sie für mysqld oder mysqld_safe eine Option auf der Befehlszeile angeben, ist diese nur für den betreffenden Aufruf des Servers gültig. Um diese Option jedes Mal bei Ausführung des Servers zu verwenden, müssen Sie sie in einer Optionsdatei ablegen.

Um die Wirkung einer Parameteränderung zu prüfen, können Sie Folgendes tun:

shell> mysqld --key_buffer_size=32M --verbose --help

Die Variablenwerte sind kurz vor Ende der Ausgabe aufgeführt. Vergewissern Sie sich, dass die Optionen --verbose und --help am Ende stehen. Andernfalls werden die Auswirkungen von Optionen, die nach diesen beiden Optionen auf der Befehlszeile angegeben wurden, in der Ausgabe nicht berücksichtigt.

Informationen zur Optimierung der InnoDB-Speicher-Engine finden Sie in Abschnitt 14.2.11, „Tipps zur Leistungssteigerung“.

Die Aufgabe des Abfrageoptimierers besteht darin, einen optimalen Plan für die Ausführung einer SQL-Abfrage zu entwickeln. Da der Unterschied zwischen „gut“ und „schlecht“ aus leistungstechnischer Sicht mehrere Größenordnungen betragen kann (d. h. Sekunden im Vergleich zu Stunden oder sogar Tagen), führten die meisten Abfrageoptimierer (einschließlich des MySQL-Optimierers) unter allen möglichen Plänen zur Abfragebewertung eine mehr oder minder umfangreiche Suche nach dem optimalen Plan durch. Bei Join-Abfragen wächst die Anzahl möglicher Pläne, die vom MySQL-Optimierer untersucht werden, exponentiell mit der Anzahl der Tabellen, die in einer Abfrage referenziert werden. Bei einer kleinen Anzahl von Tabellen (etwa 7 bis 10) ist dies unproblematisch. Werden hingegen größere Abfragen abgesetzt, dann kann die für die Abfrageoptimierung erforderliche Zeit schnell zum Engpass für die Leistung des Servers werden.

Eine flexiblere Methode zur Abfrageoptimierung gewährt dem Benutzer Kontrolle darüber, wie erschöpfend der Optimierer bei der Suche nach einem optimalen Abfragebewertungsplan vorgeht. Hintergedanke ist, dass umso weniger Zeit für die Kompilierung einer Abfrage aufgewendet wird, je weniger Pläne vom Optimierer untersucht werden. Andererseits kann es sein, dass der Optimierer, weil er einige Pläne überspringt, den optimalen Plan übersieht.

Das Verhalten des Optimierers in Bezug auf die Anzahl der Pläne, die er bewertet, kann über zwei Systemvariablen gesteuert werden:

Die meisten der folgenden Tests wurden unter Linux mit den MySQL-Benchmarks durchgeführt, sie sollten aber trotzdem ausreichend aussagekräftig auch für andere Betriebssysteme und Belastungsbereiche sein.

Sie erhalten die schnellsten ausführbaren Dateien, indem Sie mit der Option -static verknüpfen.

Unter Linux kompilieren Sie den Server am besten mit pgcc und -O3. Sie benötigen etwa 200 Mbyte Speicher zur Kompilierung von sql_yacc.cc mit diesen Optionen, weil gcc oder pgcc eine Menge Speicher benötigt, um alle Funktionen zu integrieren. Ferner sollten Sie bei der Konfiguration von MySQL CXX=gcc einstellen, um die Miteinbeziehung der Bibliothek libstdc++ zu vermeiden, denn diese wird nicht benötigt. Beachten Sie, dass bei einigen Versionen von pgcc die resultierende Binärdatei nur auf echten Pentium-Prozessoren läuft, auch wenn Sie eine Compileroption verwendet haben, mit der Sie angeben wollten, dass der Ergebniscode auf allen x586-Prozessoren (z. B. AMD) laufen soll.

Sie können eine Geschwindigkeitszunahme von 10 bis 30 Prozent erzielen, indem Sie einen besseren Compiler und geeignetere Kompilierungsoptionen verwenden. Dies ist besonders wichtig, wenn Sie den MySQL Server selbst kompilieren.

Als wir die Cygnus CodeFusion- und Fujitsu-Compiler testeten, stellte sich heraus, dass keiner von diesen ausreichend wenig Bugs aufwies, um MySQL mit aktivierten Optimierungen zu kompilieren.

MySQL-Standarddistributionen werden mit der Unterstützung für alle Zeichensätze kompiliert. Wenn Sie MySQL selbst kompilieren, sollten Sie die Unterstützung nur für diejenigen Zeichensätze integrieren, die Sie tatsächlich verwenden werden. Dies wird mit der Option --with-charset für configure gesteuert.

Es folgt eine Liste einiger Maßnahmen, die wir vorgenommen haben:

Von MySQL AB angebotene MySQL-Binärdistributionen für Linux wurden früher generell mit pgcc kompiliert. Aufgrund eines Bugs in pgcc, der dazu führte, dass die erzeugten Binärdateien nicht auf AMD-Prozessoren liefen, mussten wir jedoch zu gcc zurückkehren. Wir werden gcc weiterhin verwenden, bis dieser Bug behoben ist. In der Zwischenzeit können Sie, wenn Sie kein AMD-System haben, durch Kompilierung mit pgcc eine schnellere Binärdatei erstellen. Die MySQL-Standardbinärdatei für Linux ist statisch verknüpft, um Geschwindigkeit und Portabilität zu optimieren.

Die folgende Liste gibt einige Möglichkeiten an, wie der mysqld-Server Speicher verwendet. Sofern anwendbar, wird der Name der Systemvariablen angegeben, die für die Speichernutzung relevant ist:

ps und andere Systemstatusprogramme melden unter Umständen, dass mysqld viel Speicher benötigt. Dies kann von Thread-Stapeln an verschiedenen Speicheradressen verursacht werden. Die Solaris-Version von ps beispielsweise zählt die Menge des nicht verwendeten Speichers zwischen den Stapeln als verwendeten Speicher. Sie können dies überprüfen, indem Sie den verfügbaren Auslagerungsspeicher mit swap -s verifizieren. Wir testen mysqld mit verschiedenen Speicherleckdetektoren (sowohl kommerzieller als auch freier Herkunft), d. h., es sollten keine Speicherlecks vorhanden sein.

Wenn ein neuer Client eine Verbindung mit mysqld herstellt, dann erzeugt mysqld einen neuen Thread, um die Anforderung zu bearbeiten. Dieser Thread überprüft zunächt, ob der Hostname sich im Hostnamens-Cache befindet. Ist dies nicht der Fall, dann versucht der Thread, den Hostnamen aufzulösen:

Sie können DNS-Lookups für Hostnamen deaktivieren, indem Sie mysqld mit der Option --skip-name-resolve starten. Allerdings können Sie in diesem Fall nur IP-Adressen in den MySQL-Grant-Tabellen verwenden.

Wenn Ihr DNS sehr langsam ist und Sie viele Hosts haben, können Sie die Leistung steigern, indem Sie entweder DNS-Lookups mit --skip-name-resolve deaktivieren oder den HOST_CACHE_SIZE-Wert (Standard: 128) erhöhen und mysqld neu kompilieren.

Sie können den Hostnamens-Cache deaktivieren, indem Sie den Server mit der Option --skip-host-cache starten. Um den Cache zu leeren, setzen Sie eine FLUSH HOSTS-Anweisung ab oder führen den Befehl mysqladmin flush-hosts aus.

Wenn Sie TCP/IP-Verbindungen vollständig deaktivieren wollen, starten Sie mysqld mit der Option --skip-networking.

Sie können Tabellen und Datenbanken aus dem Datenbankverzeichnis an andere Positionen verschieben und sie durch symbolische Verknüpfungen auf diese neuen Positionen ersetzen. Sie könnten dies beispielsweise tun, um eine Datenbank auf ein Dateisystem mit mehr freiem Speicher zu verschieben oder die Geschwindigkeit Ihres Systems durch Verteilen Ihrer Tabellen auf verschiedene Festplatten zu erhöhen.

Die empfohlene Vorgehensweise hierfür besteht schlicht darin, für Datenbanken symbolische Verknüpfungen auf eine andere Festplatte zu erstellen. Symbolische Verknüpfungen für Tabellen sollten hingegen nur als letzter Ausweg verwendet werden.

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/datadir

shell> cd /path/to/datadir
shell> ln -s db1 db2

if (!(MyFlags & MY_RESOLVE_LINK) ||
    (!lstat(filename,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

if (1)