Kapitel 1. Abfrage-Antwortzeit

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: translation-feedback@oreilly.com

Leistung ist die Antwortzeit auf Abfragen.

Dieses Buch untersucht diese Idee aus verschiedenen Blickwinkeln mit einem einzigen Ziel: dir zu helfen, eine bemerkenswerte MySQL-Leistung zu erreichen.Effiziente MySQL-Leistung bedeutet, dass du dich auf die bewährten Methoden und Techniken konzentrierst, die sich direkt auf die MySQL-Leistung auswirken - keine überflüssigen Details oder tiefgreifenden Interna, die DBAs und Experten benötigen. Ich gehe davon aus, dass du ein vielbeschäftigter Profi bist, der MySQL benutzt, nicht verwaltet, und dass du die meisten Ergebnisse für den geringsten Aufwand brauchst. Das ist keine Faulheit, sondern Effizienz. Deshalb ist dieses Buch direkt und auf den Punkt gebracht. Und am Ende wirst du in der Lage sein, eine bemerkenswerte MySQL-Leistung zu erreichen.

Die Leistung von MySQL ist ein komplexes und vielschichtiges Thema, aber du musst kein Experte werden, um eine bemerkenswerte Leistung zu erzielen. Ich grenze die Komplexität von MySQL ein, indem ich mich auf das Wesentliche konzentriere. Die Leistung von MySQL beginnt mit der Antwortzeit von Abfragen.

Die Abfrage-Antwortzeit gibt an, wie lange MySQL braucht, um eine Abfrage auszuführen. Synonyme Begriffe sind: Antwortzeit, Abfragezeit, Ausführungszeit und (ungenau) Abfragelatenz.1 Die Zeit beginnt, wenn MySQL die Abfrage erhält, und endet, wenn es die Ergebnismenge an den Kunden gesendet hat. Die Antwortzeit einer Abfrage umfasst viele Phasen (Schritte während der Ausführung der Abfrage) und Wartezeiten (Lock Waits, I/O Waits usw.), aber eine vollständige und detaillierte Aufschlüsselung ist weder möglich noch notwendig. Wie bei vielen Systemen lassen sich die meisten Probleme durch eine grundlegende Fehlersuche und -analyse aufdecken.

Hinweis

Die Leistung steigt, wenn die Antwortzeit der Abfrage sinkt.Die Verbesserung der Antwortzeit der Abfrage ist gleichbedeutend mit der Verringerung der Antwortzeit der Abfrage.

Dieses Kapitel ist die Grundlage. Es erklärt die Antwortzeit von Abfragen, damit du in den folgenden Kapiteln lernst, wie du sie verbessern kannst. Es besteht aus sieben Hauptabschnitten. Der erste ist eine wahre Geschichte, die motivieren und unterhalten soll. Der zweite erläutert, warum die Antwortzeit von Abfragen der Nordstern der MySQL-Leistung ist. Der dritte beschreibt, wie Abfragemetriken in aussagekräftige Berichte umgewandelt werden: Abfrageberichte. Der vierte befasst sich mit der Analyse von Abfragen: Die Verwendung von Abfragemetriken und anderen Informationen, um die Ausführung von Abfragen zu verstehen. Der fünfte beschreibt den Weg zur Verbesserung der Antwortzeit von Abfragen: Die Optimierung von Abfragen. Der sechste gibt einen ehrlichen und bescheidenen Zeitplan für die Optimierung von Abfragen. Der siebte erläutert, warum MySQL nicht einfach schneller werden kann - warum die Optimierung von Abfragen notwendig ist.

Eine wahre Geschichte von falscher Leistung

Im Jahr 2004 arbeitete ich in der Nachtschicht in einem Rechenzentrum - von 2 Uhr nachts bis Mitternacht. Das war aus zwei Gründen ein toller Job. Erstens waren die einzigen Angestellten im Rechenzentrum nach 17 Uhr eine Handvoll Ingenieure, die Tausende von physischen Servern für eine ungenannte Anzahl von Kunden und Websites überwachten und verwalteten - wahrscheinlich Zehntausende von Websites. Es war der Traum eines jeden Ingenieurs. Zweitens gab es zahllose MySQL-Server, bei denen es immer wieder Probleme zu beheben galt. Das war eine Goldmine des Lernens und der Möglichkeiten. Aber zu dieser Zeit gab es nur wenige Bücher, Blogs oder Werkzeuge über MySQL. (Obwohl O'Reilly noch im selben Jahr die erste Ausgabe von High Performance MySQL veröffentlichte). Folglich war der Stand der Technik, um MySQL-Leistungsprobleme zu "beheben", "dem Kunden mehr Arbeitsspeicher zu verkaufen". Für den Vertrieb und das Management hat das immer funktioniert, aber für MySQL waren die Ergebnisse uneinheitlich.

Eines Abends beschloss ich, dem Kunden nicht mehr Arbeitsspeicher zu verkaufen, sondern stattdessen einen technischen Deep Dive zu machen, um die wahre Ursache für sein MySQL-Leistungsproblem zu finden und zu beheben. Die Datenbank speiste ein Schwarzes Brett, das unter der Last seines Erfolges immer langsamer wurde - auch heute noch, fast 20 Jahre später, ein häufiges Problem. Um es kurz zu machen: Ich fand eine einzelne Abfrage, der ein wichtiger Index fehlte. Nachdem ich die Abfrage richtig indiziert hatte, verbesserte sich die Leistung dramatisch und die Website war gerettet. Das kostete den Kunden null Dollar.

Nicht alle Leistungsprobleme und Lösungen sind so einfach und glamourös. Aber knapp 20 Jahre Erfahrung mit MySQL haben mich (und viele andere) gelehrt, dass MySQL-Leistungsprobleme sehr oft mit den bewährten Methoden und Techniken in diesem Buch gelöst werden können.

Nordstern

Ich bin MySQL DBA und Softwareentwickler, also weiß ich, wie es ist, mit MySQL als Letzterem zu arbeiten. Vor allem wenn es um die Leistung geht, wollen wir (Softwareentwickler) einfach nur, dass es (MySQL) funktioniert. Wer hat schon Zeit für die Leistung von MySQL, wenn es darum geht, neue Funktionen zu entwickeln und Brände zu löschen? Und wenn die Leistung von MySQL schlecht ist - oder noch schlimmer: wenn sie plötzlich schlecht wird -, kann es schwierig sein, den Weg nach vorne zu erkennen, weil es viele Überlegungen gibt: Wo sollen wir anfangen? Brauchen wir mehr Arbeitsspeicher? Schnellere CPUs? Mehr IOPS für die Speicherung? Liegt das Problem an einer kürzlich vorgenommenen Codeänderung? (Tatsache: Codeänderungen, die in der Vergangenheit vorgenommen wurden, können in der Zukunft zu Leistungsproblemen führen, manchmal sogar noch Tage in der Zukunft.) Ist das Problem ein lauter Nachbar? Haben die DBAs etwas mit der Datenbank gemacht? Hat sich die App viral verbreitet und ist es ein Problem der guten Art?

Als Ingenieur, dessen Fachgebiet die Anwendung und nicht MySQL ist, kann diese Situation überwältigend sein. Um zuversichtlich voranzukommen, solltest du dir zunächst die Antwortzeit von Abfragen ansehen , denn sie ist aussagekräftig und umsetzbar. Das sind starke Eigenschaften, die zu echten Lösungen führen:

Bedeutungsvoll

Die Abfragezeit ist die einzige Kennzahl, die irgendjemand wirklich interessiert, denn seien wir mal ehrlich: Wenn die Datenbank schnell ist, schaut sich das niemand an oder stellt Fragen. Warum? Weil die Antwortzeit der Abfrage die einzige Kennzahl ist, die wir wahrnehmen. Wenn eine Abfrage 7,5 Sekunden für die Ausführung braucht, sind wir 7,5 Sekunden ungeduldig. Dieselbe Abfrage könnte eine Million Zeilen untersuchen, aber wir erleben nicht, dass eine Million Zeilen untersucht werden. Unsere Zeit ist kostbar.

Aktionsfähig

Es gibt so viel, was du tun kannst, um die Abfrage-Antwortzeit zu verbessern und alle wieder glücklich zu machen, dass du ein Buch darüber hältst. (Gibt es in der Zukunft noch Bücher? Ich hoffe es.) Die Antwortzeit von Abfragen kann direkt beeinflusst werden, weil du den Code besitzt und die Abfragen ändern kannst. Auch wenn du den Code nicht besitzt (oder keinen Zugriff darauf hast), kannst du die Antwortzeit von Abfragen indirekt optimieren. In "Die Antwortzeit von Abfragen verbessern" geht es um die direkte und indirekte Optimierung von Abfragen.

Konzentrieren Sie sich auf die Verbesserung der Antwortzeit von Abfragen - dem Nordstern der MySQL-Leistung. Beginnen Sie nicht damit, das Problem mit Hardware zu lösen, sondern nutzen Sie Abfragemetriken, um herauszufinden, was MySQL macht. Analysieren und optimieren Sie dann langsame Abfragen, um die Antwortzeit zu verringern. Die Leistung wird sich verbessern.

Abfrage-Berichterstattung

Abfragemetriken bieten unschätzbare Einblicke in die Ausführung von Abfragen: Antwortzeit, Sperrzeit, untersuchte Zeilen usw. Aber Abfragemetriken sind wie alle Metriken Rohwerte, die gesammelt, aggregiert und in einer Weise berichtet werden müssen, die für Ingenieure aussagekräftig (und lesbar) ist. Darum geht es in diesem Abschnitt: wie Abfragemetrik-Tools Abfragemetriken in Abfrageberichte umwandeln. Aber Abfrageberichte sind nur ein Mittel zum Zweck, wie in "Abfrageanalyse" beschrieben .

Die Abfrageanalyse ist die eigentliche Arbeit: die Analyse von Abfragemetriken (wie berichtet) und anderen Informationen mit dem Ziel, die Ausführung von Abfragen zu verstehen. Um die Leistung von MySQL zu verbessern, musst du Abfragen optimieren. Um Abfragen zu optimieren, musst du verstehen, wie sie ausgeführt werden. Und um das zu verstehen, musst du sie mit relevanten Informationen analysieren, einschließlich Abfrageberichten und Metadaten.

Aber zuerst musst du das Abfrage-Reporting verstehen, denn es ist die Fundgrube für Abfragemetriken, die unschätzbare Einblicke in die Abfrageausführung liefern. In den nächsten drei Abschnitten lernst du Folgendes:

  • Quellen: Die Abfragemetriken stammen aus zwei Quellen und variieren je nach MySQL-Distribution und -Version

  • Aggregation: Abfragemetrikwerte werden gruppiert und durch normalisierte SQL-Anweisungen aggregiert

  • Berichtswesen: Abfrageberichte werden durch ein übergeordnetes Profil und einen abfragespezifischen Bericht organisiert

Dann bist du bereit für die "Abfrageanalyse".

Hinweis

Da dies kein Buch über Datenbankadministration ist, wird in diesem Abschnitt nicht auf die Einrichtung und Konfiguration von Abfragemetriken in MySQL eingegangen. Ich gehe davon aus, dass dies bereits geschehen ist oder geschehen wird. Wenn nicht, mach dir keine Sorgen: Frag deinen DBA, engagiere einen Berater oder lerne es im MySQL-Handbuch.

Quellen

Abfragen Metriken stammen aus dem Slow Query Log oder dem Performance Schema.Wie der Name schon sagt, handelt es sich bei Ersterem um eine Protokolldatei auf der Festplatte und bei Letzterem um eine Datenbank mit demselben Namen: performance_schema. Obwohl beide von ihrer Art her völlig unterschiedlich sind (Protokolldatei auf der Festplatte im Gegensatz zu Tabellen in einer Datenbank), liefern sie Abfragemetriken. Der wichtigste Unterschied ist die Anzahl der Metriken, die sie liefern: Abgesehen von der Abfrage-Antwortzeit, die beide liefern, reicht die Anzahl der Metriken von 3 bis zu mehr als 20.

Hinweis

Der Name Slow Query Log ist historisch bedingt. Vor langer Zeit hat MySQL nur Abfragen protokolliert, deren Ausführung länger als N Sekunden dauerte, und der Mindestwert für N war 1. Alte MySQL-Versionen haben keine Abfragen protokolliert, die 900 Millisekunden zur Ausführung brauchten, weil das "schnell" war. Das Protokoll für langsame Abfragen hat seinen Namen wirklich verdient. Heute ist der Mindestwert Null mit einer Auflösung von Mikrosekunden. Wenn er auf Null gesetzt wird, protokolliert MySQL jede ausgeführte Abfrage. Daher ist der Name ein wenig irreführend, aber jetzt weißt du, warum.

Alles in allem ist das Leistungsschema die beste Quelle für Abfragemetriken, weil es in jeder aktuellen Version und Distribution von MySQL vorhanden ist, lokal und in der Cloud funktioniert, alle neun in "Abfragemetriken" beschriebenen Metriken liefert und am konsistentesten ist. Außerdem enthält das Leistungsschema eine Fülle anderer Daten für eine tiefgehende MySQL-Analyse, so dass sein Nutzen weit über die Abfragemetriken hinausgeht. Das langsame Abfrageprotokoll ist auch eine gute Quelle, aber es variiert stark:

MySQL

Seit MySQL 8.0.14 können Sie die Systemvariable log_slow_extra und das Slow Query Log liefert sechs der neun Metriken in "Query Metrics", es fehlen nur noch Rows_affected, Select_scan und Select_full_join. Es ist immer noch eine gute Quelle, aber verwende wenn möglich das Performance Schema.

Vor MySQL 8.0.14, in dem MySQL 5.7 enthalten ist, ist das Slow Query Log sehr dürftig und liefert nur Query_time, Lock_time, Rows_sent und Rows_examined. Du kannst Abfragen immer noch mit diesen vier Metriken analysieren, aber die Analyse ist viel weniger aufschlussreich. Daher solltest du das Slow Query Log vor MySQL 8.0.14 vermeiden und stattdessen das Leistungsschema verwenden.

Percona Server

Percona Server bietet eine deutlich größere Anzahl von Metriken im Slow Query Log, wenn die Systemvariable log_slow_verbosity konfiguriert ist: alle neun Metriken, die in "Query Metrics" behandelt werden, und mehr. Außerdem unterstützt es Query Sampling (Protokollierung eines Prozentsatzes der Abfragen), wenn die Systemvariable log_slow_rate_limit konfiguriert ist, was für ausgelastete Server hilfreich ist. Diese Funktionen machen das Slow Query Log von Percona Server zu einer großartigen Quelle. Weitere Informationen findest du unter "Slow Query Log" im Percona Server Handbuch.

MariaDB Server

MariaDB Server 10.x nutzt die Erweiterungen des Slow Query Logs von Percona Server, aber es gibt zwei bemerkenswerte Unterschiede: Die Systemvariable log_slow_verbosity ist in MariaDB anders konfiguriert, und es gibt keine Metrik Rows_affected. Ansonsten ist es im Wesentlichen dasselbe und eine großartige Quelle. Siehe "Slow Query Log Extended Statistics" in der MariaDB Wissensdatenbank für Details.

Das Slow Query Log ist standardmäßig deaktiviert, aber du kannst es dynamisch aktivieren (ohne MySQL neu zu starten). Das Performance Schema sollte standardmäßig aktiviert sein, obwohl einige Cloud-Provider es standardmäßig deaktivieren. Anders als das Slow Query Log kann das Performance Schema nicht dynamisch aktiviert werden - du musst MySQL neu starten, um es zu aktivieren.

Stelle sicher, dass die beste Quelle für Abfragemetriken verwendet und richtig konfiguriert wird. Frag deinen DBA, engagiere einen Berater oder lese das MySQL-Handbuch.

Warnung

Das langsame Abfrageprotokoll kann alle Abfragen protokollieren, wenn long_query_time auf Null gesetzt ist, aber sei vorsichtig: Auf einem stark ausgelasteten Server kann dies die Festplattenein- und -ausgabe erhöhen und eine beträchtliche Menge an Speicherplatz belegen.

Aggregation

Abfragemetriken werden nach Abfrage gruppiert und aggregiert.Das klingt offensichtlich, da sie Abfragemetriken genannt werden, aber einige Tools für Abfragemetriken können nach Benutzername, Hostname, Datenbank usw. gruppieren. Diese alternativen Gruppierungen sind äußerst selten und führen zu einer anderen Art von Abfrageanalyse, weshalb ich sie in diesem Buch nicht behandle. Da die Antwortzeit von Abfragen der Nordstern der MySQL-Leistung ist, ist die Gruppierung von Abfragemetriken nach Abfrage der beste Weg, um zu sehen, welche Abfragen die langsamste Antwortzeit haben, was die Grundlage für Abfrageberichte und -analysen bildet.

Es gibt nur ein kleines Problem: Wie identifizierst du Abfragen eindeutig, um die Gruppen zu bestimmen, zu denen sie gehören? Systemmetriken (CPU, Arbeitsspeicher, Speicherung usw.) werden zum Beispiel nach Hostnamen gruppiert, weil Hostnamen eindeutig und aussagekräftig sind. Aber Abfragen haben keine eindeutig identifizierenden Eigenschaften wie Hostnamen. Die Lösung: ein SHA-256-Hash der normalisierten SQL-Anweisung. Beispiel 1-1 zeigt, wie eine SQL-Anweisung normalisiert wird.

Beispiel 1-1. Normalisierung von SQL-Anweisungen
SELECT col FROM tbl WHERE id=1 1

SELECT `col` FROM `tbl` WHERE `id` = ? 2

f49d50dfab1c364e622d1e1ff54bb12df436be5d44c464a4e25a1ebb80fc2f13 3
1

SQL-Anweisung (Beispiel)

2

Auszugstext (normalisierte SQL-Anweisung)

3

Digest-Hash (SHA-256 des Digest-Textes)

MySQL normalisiert SQL-Anweisungen zu Hash-Texten und berechnet dann den SHA-256-Hash des Hash-Textes, um den Hash-Wert zu erhalten. (Es ist nicht notwendig, den gesamten Prozess der Normalisierung zu verstehen; es reicht zu wissen, dass bei der Normalisierung alle Werte durch ? ersetzt werden und mehrere Leerzeichen zu einem einzigen Leerzeichen zusammengefasst werden). Da der Digest-Text eindeutig ist, ist auch der Digest-Hash eindeutig (ungeachtet von Hash-Kollisionen).

Hinweis

Im MySQL-Handbuch wird der Begriff Digest zweideutig verwendet und bedeutet entweder Digest-Text oder Digest-Hash. Da der Digest-Hash aus dem Digest-Text berechnet wird, handelt es sich bei der Zweideutigkeit nur um eine sprachliche Unklarheit, nicht um einen technischen Fehler. Erlaube mir bitte, ebenfalls zweideutig zu sein und den Begriff "digest" entweder für "digest text" oder "digest hash" zu verwenden, wenn der technische Unterschied keine Rolle spielt.

Im Zusammenhang mit den Abfragemetriken gibt es eine wichtige terminologische Veränderung: Der Begriff Abfrage wird zum Synonym für Digest-Text. Die Änderung der Terminologie steht im Einklang mit der Änderung des Schwerpunkts: die Gruppierung von Metriken nach Abfrage. Um nach Abfrage zu gruppieren, muss die Abfrage eindeutig sein, was nur auf Digests zutrifft.

SQL-Anweisungen werden auch als Query Samples (oder kurz Samples ) bezeichnet, und sie können gemeldet werden oder auch nicht. Aus Sicherheitsgründen verwerfen die meisten Query Metric Tools standardmäßig Samples (weil sie echte Werte enthalten) und melden nur Digest-Texte und Hashes. Samples sind für die Query-Analyse erforderlich, weil man EXPLAIN Einige Abfragemetrik-Tools EXPLAIN ein Sample, verwerfen es dann und melden den EXPLAIN-Plan (die Ausgabe von EXPLAIN). Andere melden nur das Sample, was immer noch sehr bequem ist: Copy-Paste auf EXPLAIN. Wenn du keines von beiden hast, dann extrahiere Samples manuell aus der Quelle oder schreibe sie bei Bedarf manuell.

Noch zwei Klarstellungen zur Terminologie, dann kommen wir zu spannenderen Themen. Erstens variiert die Terminologie je nach Abfragemetrik-Tool stark, wie in Tabelle 1-1 dargestellt.

Tabelle 1-1. Terminologie der Abfragemetriken
Offiziell (MySQL) Alternativen

SQL-Anweisung

Abfrage

Beispiel

Abfrage

Auszugstext

Klasse, Familie, Fingerabdruck, Abfrage

Digest-Hash

Klassen-ID, Abfrage-ID, Signatur

Zweitens, ein weiterer Begriff, der von Percona stammt, ist Query Abstract: eine SQL-Anweisung, die stark auf ihren SQL-Befehl und ihre Tabellenliste abstrahiert ist.Beispiel 1-2 ist der Query Abstract für SELECT col FROM tbl WHERE id=1.

Beispiel 1-2. Zusammenfassung der Abfrage
SELECT tbl

Abfragezusammenfassungen sind nicht einzigartig, aber sie sind nützlich, weil sie kurz und bündig sind. Normalerweise müssen Entwickler nur eine Abfragezusammenfassung sehen, um die vollständige Abfrage zu kennen, die sie darstellt.

Kürze ist die Seele des Witzes.

William Shakespeare

Es ist wichtig zu verstehen, dass SQL-Anweisungen normalisiert werden, weil die Abfragen, die du schreibst, nicht die Abfragen sind, die du siehst. In den meisten Fällen ist das kein Problem, weil die Digest-Texte den SQL-Anweisungen sehr ähnlich sind. Aber der Prozess der Normalisierung wirft einen weiteren wichtigen Punkt auf: Erstelle nicht dynamisch dieselbe logische Abfrage mit unterschiedlicher Syntax, sonst wird sie in verschiedenen Digests normalisiert und als unterschiedliche Abfragen gemeldet. Zum Beispiel im Fall einer programmatisch erstellten Abfrage, die die WHERE Klausel aufgrund von Benutzereingaben ändert:

SELECT name FROM captains WHERE last_name = 'Picard'
SELECT name FROM captains WHERE last_name = 'Picard' AND first_name = 'Jean-Luc'

Für dich und die Anwendung mögen diese beiden Abfragen logisch gleich sein, aber in Bezug auf das Reporting sind sie unterschiedliche Abfragen, weil sie zu unterschiedlichen Digests normalisiert werden. Meines Wissens erlaubt es kein Abfragemetrik-Tool, Abfragen zu kombinieren. Und es ist technisch korrekt, diese Abfragen getrennt zu melden, weil jede Bedingung - insbesondere in der WHERE Klausel - die Abfrageausführung und -optimierung beeinflusst.

Ein Punkt zur Abfragenormalisierung: Werte werden entfernt, so dass die folgenden zwei Abfragen auf denselben Digest normalisiert werden:

-- SQL statements
SELECT `name` FROM star_ships WHERE class IN ('galaxy')
SELECT `name` FROM star_ships WHERE class IN ('galaxy', 'intrepid')

-- Digest text
SELECT `name` FROM `star_ships` WHERE `class` IN (...)

Da der Digest für beide Abfragen gleich ist, werden die Metriken für beide Abfragen gruppiert, aggregiert und als eine Abfrage gemeldet.

Genug über Terminologie und Normalisierung. Lass uns über die Berichterstattung sprechen.

Melden

Berichterstattung ist eine Herausforderung und eine Kunstform, denn eine einzige Anwendung kann Hunderte von Abfragen haben. Jede Abfrage hat viele Metriken und jede Metrik hat mehrere Statistiken: Minimum, Maximum, Durchschnitt, Perzentil und so weiter. Außerdem gibt es zu jeder Abfrage Metadaten: Beispiele, EXPLAIN-Pläne, Tabellenstrukturen und so weiter. Es ist eine Herausforderung, all diese Daten zu speichern, zu verarbeiten und zu präsentieren. Fast jedes Abfragemetrik-Tool stellt die Daten in einer zweistufigen Hierarchie dar: Abfrageprofil und Abfragebericht. Die Begriffe variieren je nach Abfragemetrik-Tool, aber du wirst sie leicht erkennen, wenn du sie siehst.

Profil abfragen

Ein Abfrageprofil zeigt langsame Abfragen an.Es ist die oberste Ebene der Abfrageberichterstattung und normalerweise das erste, was du in einem Abfragemetrik-Tool siehst. Es zeigt Abfrageauszüge und eine begrenzte Teilmenge von Abfragemetriken, weshalb es auch Profil genannt wird.

Langsam bezieht sich auf die Sortiermetrik: den Gesamtwert einer Abfragemetrik, nach der die Abfragen geordnet werden. Die zuerst geordnete Abfrage wird als die langsamste bezeichnet, auch wenn die Sortiermetrik nicht die Abfragezeit (oder irgendeine Zeit) ist. Wenn die Sortiermetrik zum Beispiel die durchschnittlich gesendeten Zeilen sind, wird die zuerst geordnete Abfrage immer noch als die langsamste bezeichnet.

Obwohl jede Abfragemetrik die Sortiermetrik sein kann, ist die Abfragezeit die universelle Standardsortiermetrik. Wenn du die Ausführungszeit von Abfragen reduzierst, setzt du Zeit frei, die es MySQL ermöglicht, mehr Arbeit zu erledigen oder andere Arbeiten schneller zu erledigen. Die Sortierung von Abfragen nach der Abfragezeit zeigt dir, wo du anfangen solltest: bei den langsamsten, zeitaufwendigsten Abfragen.

Was nicht universell ist, ist, wie die Abfragezeit aggregiert wird. Die gebräuchlichsten Aggregatwerte sind:

Gesamte Abfragezeit

Gesamtzeit der Abfrage ist die Summe der Ausführungszeit (pro Abfrage). Dies ist der gebräuchlichste aggregierte Wert, weil er eine wichtige Frage beantwortet: Welche Abfrage verbringt die meiste Zeit mit der Ausführung?Um dies zu beantworten, addiert ein Abfragemetrik-Tool die gesamte Zeit, die MySQL mit der Ausführung jeder Abfrage verbringt. Die Abfrage mit der größten Gesamtzeit ist die langsamste, zeitaufwändigste Abfrage. Hier ist ein Beispiel, warum dies wichtig ist. Angenommen, Abfrage A hat eine Antwortzeit von 1 Sekunde und wird 10-mal ausgeführt, während Abfrage B eine Antwortzeit von 0,1 Sekunden hat und 1.000-mal ausgeführt wird. Abfrage A hat eine viel langsamere Antwortzeit, aber Abfrage B ist 10-mal zeitaufwändiger: 10 Sekunden insgesamt gegenüber 100 Sekunden insgesamt. In einem Abfrageprofil, das nach der Gesamtzeit der Abfrage sortiert ist, ist Abfrage B die langsamste Abfrage. Das ist wichtig, weil du die meiste Zeit für MySQL freisetzt, wenn du Abfrage B optimierst.

Prozentuale Ausführungszeit

Dieprozentuale Ausführungszeit ist die Gesamtabfragezeit (pro Abfrage) geteilt durch die Gesamtausführungszeit (alle Abfragen). Wenn zum Beispiel Abfrage C eine Gesamtabfragezeit von 321 ms hat und Abfrage D eine Gesamtabfragezeit von 100 ms, dann beträgt die Gesamtausführungszeit 421 ms. Einzeln macht Abfrage C (321 ms / 421 ms) × 100 = 76,2 % der Gesamtausführungszeit aus, und Abfrage D (100 ms / 421 ms) × 100 = 23,8 % der Gesamtausführungszeit. Mit anderen Worten: MySQL hat 421 ms mit der Ausführung von Abfragen verbracht, davon 76,2 % mit der Ausführung von Abfrage C. In einem nach prozentualer Ausführungszeit sortierten Abfrageprofil ist Abfrage C die langsamste Abfrage. Die prozentuale Ausführungszeit wird von einigen Abfragemetrik-Tools verwendet, aber nicht von allen.

Abfrage laden

Die Abfragelast ist die gesamte Abfragezeit (pro Abfrage) geteilt durch die Uhrzeit, wobei die Uhrzeit die Anzahl der Sekunden im Zeitbereich ist. Wenn die Zeitspanne 5 Minuten beträgt, dann ist die Uhrzeit 300 Sekunden. Wenn z.B. Abfrage E eine Gesamtabfragezeit von 250,2 Sekunden hat, dann ist die Last 250,2 s / 300 s = 0,83; und wenn Abfrage F eine Gesamtabfragezeit von 500,1 Sekunden hat, dann ist die Last 500,1 s / 300 s = 1,67. In einem nach Abfragelast sortierten Abfrageprofil ist Abfrage F die langsamste Abfrage, weil ihre Last am größten ist.

Die Last ist relativ zur Zeit, aber auch ein subtiler Hinweis auf die Gleichzeitigkeit: mehrere Instanzen einer Abfrage, die gleichzeitig ausgeführt werden. Eine Abfragelast von weniger als 1,0 bedeutet, dass die Abfrage im Durchschnitt nicht gleichzeitig ausgeführt wird. Eine Abfragelast von mehr als 1,0 deutet auf eine Gleichzeitigkeit der Abfrage hin. Eine Abfragelast von 3,5 bedeutet zum Beispiel, dass jedes Mal, wenn du nachschaust, wahrscheinlich 3,5 Instanzen der Abfrage ausgeführt werden. (In Wirklichkeit sind es 3 oder 4 Instanzen der Abfrage, da es keine 0,5 Instanzen einer Abfrage geben kann.) Je höher die Abfragelast ist, desto größer ist die Wahrscheinlichkeit, dass es zu Konflikten kommt, wenn die Abfrage auf dieselben oder nahegelegene Zeilen zugreift. Eine Abfragelast von mehr als 10 ist hoch und wahrscheinlich eine langsame Abfrage, aber es gibt Ausnahmen. Während ich dies schreibe, sehe ich eine Abfrage mit einer Last von 5.962. Wie ist das möglich? Die Antwort verrate ich in "Datenzugriff".

Wenn die Sortiermetrik eine nicht-zeitliche Abfragemetrik verwendet, wie z. B. gesendete Zeilen, kann ein anderer Aggregatwert (Durchschnitt, Maximum usw.) sinnvoll sein, je nachdem, was du zu diagnostizieren versuchst. Dies ist weitaus seltener der Fall als die Gesamtabfragezeit, aber gelegentlich werden dadurch interessante Abfragen aufgedeckt, die es wert sind, optimiert zu werden.

Bericht abfragen

Ein Abfragebericht zeigt dir alles, was es über eine Abfrage zu wissen gibt. Er ist die zweite Organisationsebene für Abfrageberichte und wird normalerweise durch die Auswahl einer langsamen Abfrage im Abfrageprofil aufgerufen. Er zeigt alle Abfragemetriken und Metadaten. Während das Abfrageprofil nur etwas aussagt (welche Abfragen am langsamsten sind), ist ein Abfragebericht ein organisierter Informationsdump, der für die Abfrageanalyse verwendet wird. Je mehr Informationen, desto besser, denn sie helfen dir, die Abfrageausführung zu verstehen.

Abfrageberichte unterscheiden sich je nach Abfragemetrik-Tool erheblich. Ein Minimalbericht enthält alle Abfragemetriken aus der Quelle und die grundlegenden Statistiken für diese Metriken: Minimum, Maximum, Durchschnitt, Perzentil usw. Ein ausführlicher Bericht enthält Metadaten: Abfragebeispiele, EXPLAIN-Pläne, Tabellenstrukturen und mehr. (Beispiele können aus Sicherheitsgründen deaktiviert werden, da sie echte Werte enthalten). Einige Metrik-Tools für Abfragen gehen noch weiter und fügen zusätzliche Informationen hinzu: Metrik-Diagramme, Histogramme (Verteilungen), Erkennung von Anomalien, Vergleich von Zeitverschiebungen (jetzt im Vergleich zur letzten Woche), Entwicklernotizen, Extraktion von SQL-Kommentar-Schlüsselwerten und so weiter.

Für die Abfrageanalyse sind nur die Abfragemetriken im Bericht erforderlich. Metadaten können manuell erfasst werden. Wenn das von dir verwendete Abfragemetrik-Tool nur Abfragemetriken meldet, mach dir keine Sorgen: Das ist schon mal ein Anfang, aber du musst zumindest EXPLAIN-Pläne und Tabellenstrukturen manuell erfassen.

Wenn du einen Abfragebericht in der Hand hältst, bist du für die Abfrageanalyse gerüstet.

Abfrageanalyse

Das Ziel der Abfrageanalyse ist es, die Abfrageausführung zu verstehen, nicht die langsame Antwortzeit zu lösen. Das mag dich überraschen, aber das Lösen von langsamen Antwortzeiten geschieht nach der Abfrageanalyse, während der Abfrageoptimierung. Zuerst musst du verstehen, was du zu ändern versuchst: die Abfrageausführung.

Die Ausführung einer Abfrage ist wie eine Geschichte mit einem Anfang, einem Mittelteil und einem Ende: Du musst alle drei Teile lesen, um die Geschichte zu verstehen. Wenn du erst einmal verstanden hast, wie MySQL eine Abfrage ausführt, wirst du auch verstehen, wie du sie optimieren kannst. Verstehen durch Analyse, dann Handeln durch Optimierung.

Tipp

Ich habe schon vielen Ingenieuren bei der Analyse von Abfragen geholfen, und die Hauptschwierigkeit besteht nicht darin, die Metriken zu verstehen, sondern in der Analyse stecken zu bleiben: Du starrst tief in die Zahlen und wartest auf eine Offenbarung. Bleib nicht stecken. Überprüfe sorgfältig alle Metriken und Metadaten - lies die ganze Geschichte - und konzentriere dich dann auf die Abfrageoptimierung mit dem Ziel, die Antwortzeit zu verbessern.

Die folgenden Abschnitte befassen sich mit den wichtigsten Aspekten einer effizienten und aufschlussreichen Abfrageanalyse. Manchmal ist die Ursache für eine langsame Antwortzeit so offensichtlich, dass sich die Analyse eher wie ein Tweet als wie eine Geschichte liest. Aber wenn das nicht der Fall ist - wenn sich die Analyse wie eine Doktorarbeit über den französischen Existenzialismus liest - hilft dir das Verständnis dieser Aspekte, die Ursache zu finden und eine Lösung zu finden.

Metriken abfragen

Aus "Quellen", weißt du, dass Abfragemetriken je nach Quelle, MySQL-Distribution und MySQL-Version unterschiedlich sind. Alle Abfragemetriken sind wichtig, weil sie dir helfen, die Ausführung von Abfragen zu verstehen, aber die neun Metriken, die in den folgenden Abschnitten beschrieben werden, sind für jede Abfrageanalyse unerlässlich.

Das Leistungsschema liefert alle neun wichtigen Abfragemetriken.

Hinweis

Auch die Namen der Abfragemetriken variieren je nach Quelle. Im Slow Query Log lautet die Abfragezeit Query_time, im Performance Schema TIMER_WAIT. Ich verwende keine der beiden Konventionen, sondern benutze stattdessen menschenfreundliche Namen wie Query Time und Rows Sent. Auch die Abfrageberichte verwenden fast immer menschenfreundliche Namen.

Abfragezeit

Die Abfragezeit ist die wichtigste Metrik - das wusstest du bereits. Was du vielleicht nicht weißt, ist, dass die Abfragezeit eine weitere Kennzahl beinhaltet: die Sperrzeit.

Die Sperrzeit ist ein fester Bestandteil der Abfragezeit, daher ist es nicht überraschend, dass die Sperrzeit die Abfragezeit mit einschließt. Überraschend ist, dass die Abfragezeit und die Sperrzeit die einzigen beiden zeitbasierten Abfragemetriken sind, mit einer Ausnahme: Das langsame Abfrageprotokoll des Percona Servers enthält Metriken für die InnoDB-Lesezeit, die Wartezeit auf die Zeilensperre und die Wartezeit in der Warteschlange. Die Sperrzeit ist wichtig, aber es gibt einen bedauerlichen technischen Fehler: Sie ist nur im langsamen Abfrageprotokoll genau. Dazu später mehr.

Mit Hilfe des Leistungsschemas kannst du viele (aber nicht alle) Teile der Abfrageausführung sehen. Das ist kein Thema und geht über den Rahmen dieses Buches hinaus, aber es ist gut zu wissen, wo du nachschauen musst, wenn du tiefer gehen willst. MySQL instrumentiert eine verwirrende Anzahl von Ereignissen, die das Handbuch als "alles, was der Server tut, das Zeit benötigt und instrumentiert wurde, damit Zeitinformationen gesammelt werden können" definiert. Ereignisse sind in einer Hierarchie organisiert:

transactions
└── statements
    └── stages
        └── waits
Transaktionen

Transaktionen sind das Top-Ereignis, weil jede Abfrage in einer Transaktion ausgeführt wird(Kapitel 8 behandelt Transaktionen).

Aussagen

Die Anweisungen sind Abfragen, für die Abfragemetriken gelten.

Etappen

Stages sind "Schritte während des Ausführungsprozesses einer Anweisung, wie z.B. das Parsen einer Anweisung, das Öffnen einer Tabelle oder das Ausführen einer Dateisortieroperation".

Wartet

Warten sind "Ereignisse, die Zeit brauchen". (Diese Definition amüsiert mich. Sie ist tautologisch und in ihrer Einfachheit seltsam befriedigend.)

Beispiel 1-3 zeigt die Schritte für ein einzelnes UPDATE Statement (Stand: MySQL 8.0.22).

Beispiel 1-3. Etappen für eine einzelne UPDATE Anweisung
+----------------------------------+----------------------------------+-----------+
| stage                            | source:line                      | time (ms) |
+----------------------------------+----------------------------------+-----------+
| stage/sql/starting               | init_net_server_extension.cc:101 |     0.109 |
| stage/sql/Executing hook on trx  | rpl_handler.cc:1120              |     0.001 |
| stage/sql/starting               | rpl_handler.cc:1122              |     0.008 |
| stage/sql/checking permissions   | sql_authorization.cc:2200        |     0.004 |
| stage/sql/Opening tables         | sql_base.cc:5745                 |     0.102 |
| stage/sql/init                   | sql_select.cc:703                |     0.007 |
| stage/sql/System lock            | lock.cc:332                      |     0.072 |
| stage/sql/updating               | sql_update.cc:781                | 10722.618 |
| stage/sql/end                    | sql_select.cc:736                |     0.003 |
| stage/sql/query end              | sql_parse.cc:4474                |     0.002 |
| stage/sql/waiting handler commit | handler.cc:1591                  |     0.034 |
| stage/sql/closing tables         | sql_parse.cc:4525                |     0.015 |
| stage/sql/freeing items          | sql_parse.cc:5007                |     0.061 |
| stage/sql/logging slow query     | log.cc:1640                      |     0.094 |
| stage/sql/cleaning up            | sql_parse.cc:2192                |     0.002 |
+----------------------------------+----------------------------------+-----------+

Die tatsächliche Ausgabe ist komplexer; ich habe sie vereinfacht, damit sie leichter zu lesen ist. Die Anweisung UPDATE wurde in 15 Schritten ausgeführt. Die eigentliche Ausführung von UPDATE war der achte Schritt: stage/sql/updating. Es gab 42 Wartezeiten, , aber ich habe sie aus der Ausgabe entfernt, weil sie zu sehr vom Thema abweichen.

Leistungsschema-Ereignisse (Transaktionen, Anweisungen, Phasen und Wartezeiten) sind die Feinheiten der Abfrageausführung. Abfragemetriken beziehen sich auf Anweisungen. Wenn du tiefer in eine Abfrage einsteigen willst, schau im Leistungsschema nach.

Effizienz ist unser Modus Operandi, also verliere dich nicht im Leistungsschema, bis du es brauchst, was vielleicht nie der Fall ist. Die Abfragezeit ist ausreichend.

Sperrzeit

Die Sperrzeit ist die Zeit, die für die Beschaffung von Sperren während der Abfrageausführung aufgewendet wird. Im Idealfall ist die Sperrzeit ein winziger Prozentsatz der Abfragezeit, aber die Werte sind relativ (siehe "Relative Werte"). Bei einer extrem optimierten Datenbank, die ich verwalte, beträgt die Sperrzeit beispielsweise 40 bis 50 % der Abfragezeit für die langsamste Abfrage. Klingt schrecklich, oder? Ist es aber nicht: Die langsamste Abfrage hat eine maximale Abfragezeit von 160 Mikrosekunden und eine maximale Sperrzeit von 80 Mikrosekunden - und die Datenbank führt über 20.000 Abfragen pro Sekunde (QPS) aus.

Obwohl die Werte relativ sind, kann ich mit Sicherheit sagen, dass eine Sperrzeit von mehr als 50 % der Abfragezeit ein Problem darstellt, weil MySQL den Großteil seiner Zeit mit Arbeit verbringen sollte, nicht mit Warten. Eine theoretisch perfekte Abfrageausführung hätte keine Wartezeit, aber das ist aufgrund der gemeinsam genutzten Ressourcen, der Gleichzeitigkeit und der systemimmanenten Latenz unmöglich. Aber wir können ja träumen.

Erinnerst du dich an den unglücklichen technischen Fehler, der bereits erwähnt wurde? Hier ist er: Die Sperrzeit aus dem Leistungsschema enthält keine Wartesituationen auf Zeilensperren, nur Wartesituationen auf Tabellen- und Metadatensperren. Wartezeiten auf Zeilensperren sind der wichtigste Teil der Sperrzeit, weshalb die Sperrzeit aus dem Leistungsschema fast nutzlos ist. Im Gegensatz dazu enthält die Sperrzeit aus dem Slow Query Log alle Sperrsperren: Metadaten, Tabellen und Zeilen. Die Sperrzeit aus beiden Quellen gibt keinen Aufschluss darüber, welche Art von Sperre gewartet wurde. Aus dem Leistungsschema geht mit Sicherheit hervor, dass es sich um eine Metadatensperre handelt, und aus dem Slow Query Log geht hervor, dass es sich wahrscheinlich um eine Zeilensperre handelt, aber auch eine Metadatensperre ist möglich.

Warnung

Die Sperrzeit aus dem Leistungsschema beinhaltet nicht die Wartezeiten auf Zeilensperren.

Sperren werden in erster Linie für Schreibvorgänge verwendet (INSERT, UPDATE, DELETE, REPLACE), weil Zeilen gesperrt werden müssen, bevor sie geschrieben werden können. Die Antwortzeit für Schreibvorgänge hängt zum Teil von der Sperrzeit ab. Die Zeit, die benötigt wird, um Zeilensperren zu erhalten, hängt von der Gleichzeitigkeit ab: wie viele Abfragen gleichzeitig auf dieselbe (oder nahe gelegene) Zeile zugreifen. Wenn auf eine Zeile keine Gleichzeitigkeit besteht (also nur eine Abfrage gleichzeitig darauf zugreift), ist die Sperrzeit verschwindend gering. Wenn eine Zeile jedoch "heiß"ist - im Fachjargonfür sehr häufige Zugriffe -,kann die Sperrzeit einen erheblichen Anteil der Antwortzeit ausmachen. Gleichzeitigkeit ist eines von mehreren Datenzugriffsmustern (siehe "Datenzugriffsmuster" in Kapitel 4).

Bei Lesungen (SELECT) gibt es nicht-sperrende und sperrende Lesungen. Die Unterscheidung ist einfach, denn es gibt nur zwei Locking Reads: SELECT...FOR UPDATE und SELECT...FOR SHARE. Wenn es sich nicht um einen dieser beiden handelt, dann ist SELECT ein Non-Locking-Read, was der Normalfall ist.

Obwohl SELECT...FOR UPDATE und SELECT...FOR SHARE die einzigen Lesesperren sind, vergiss nicht die Schreibsperren mit einem optionalen SELECT. In den folgenden SQL-Anweisungen erwirbt SELECT gemeinsame Zeilensperren für die Tabelle s:

  • INSERT...SELECT FROM s

  • REPLACE...SELECT FROM s

  • UPDATE...WHERE...(SELECT FROM s)

  • CREATE TABLE...SELECT FROM s

Streng genommen handelt es sich bei diesen SQL-Anweisungen um Schreibvorgänge, nicht um Lesevorgänge, aber die optionale SELECT erwirbt gemeinsame Zeilensperren für die Tabelle s. Weitere Informationen findest du im MySQL-Handbuch unter "Locks Set by Different SQL Statements in InnoDB".

Locking Reads sollten vermieden werden, vor allem SELECT...FOR UPDATE, da sie nicht skalieren, zu Problemen führen und es in der Regel eine Non-Locking-Lösung gibt, um das gleiche Ergebnis zu erzielen. In Bezug auf die Locking-Zeit ist ein Locking-Read wie ein Write: Sie hängt von der Gleichzeitigkeit ab. Sei vorsichtig mit SELECT...FOR SHARE: Shared Locks sind mit anderen Shared Locks kompatibel, aber sie sind inkompatibel mit exklusiven Sperren, d.h. Shared Locks blockieren Schreibvorgänge auf denselben (oder benachbarten) Zeilen.

Bei nicht sperrenden Lesevorgängen ist die Sperrzeit nicht null, auch wenn keine Zeilensperren erworben werden, da Metadaten- und Tabellensperren erworben werden. Aber die Beschaffung dieser beiden sollte sehr schnell sein: weniger als 1 Millisekunde. Eine andere Datenbank, die ich verwalte, führt zum Beispiel über 34.000 QPS aus, aber die langsamste Abfrage ist eine nicht sperrende SELECT, die einen vollständigen Tabellenscan durchführt und bei jeder Ausführung sechs Millionen Zeilen liest, und das bei sehr hoher Gleichzeitigkeit: 168 Abfragen. Trotz dieser hohen Werte liegt die maximale Sperrzeit bei 220 Mikrosekunden und die durchschnittliche Sperrzeit bei 80 Mikrosekunden.

Nicht-sperrendes Lesen bedeutet nicht nicht-sperrend. SELECT Abfragen müssen für alle Tabellen, auf die zugegriffen wird, gemeinsame Metadatensperren (MDL) erwerben. Wie bei Sperren üblich, sind gemeinsam genutzte MDL mit anderen gemeinsam genutzten MDL kompatibel, aber eine exklusive MDL blockiert alle anderen MDL. ALTER TABLE ist die übliche Operation, die eine exklusive MDL erwirbt. Auch bei der Verwendung von ALTER TABLE...ALGORITHM=INPLACE, LOCK=NONE oder Online-Schemaänderungswerkzeugen von Drittanbietern wie pt-online-schema-change und gh-ostmuss am Ende eine exklusive MDL erworben werden, um die alte Tabellenstruktur gegen die neue auszutauschen. Obwohl der Tabellentausch sehr schnell geht, kann er zu einer spürbaren Unterbrechung führen, wenn MySQL stark ausgelastet ist, weil alle Tabellenzugriffe blockiert werden, während die exklusive MDL gehalten wird. Dieses Problem zeigt sich in der Sperrzeit, insbesondere bei SELECT Anweisungen.

Warnung

SELECT kann das Warten auf Metadaten-Sperren blockieren.

Locking ist vielleicht der komplexeste und differenzierteste Aspekt von MySQL. Um nicht in den sprichwörtlichen Kaninchenbau zu verfallen, möchte ich fünf Punkte nennen, die ich aber vorerst nicht weiter erläutern möchte. Wenn du diese Punkte kennst, kannst du deine MySQL-Fähigkeiten erheblich verbessern:

  • Die Sperrzeit kann deutlich größer sein als innodb_lock_wait_timeout weil diese Systemvariable für jede Zeilensperre gilt.
  • Sperren und Transaktionsisolationsebenen sind miteinander verbunden.
  • InnoDB sperrt jede Zeile, auf die es zugreift , auch Zeilen, die es nicht schreibt.
  • Sperren werden beim Commit oder Rollback der Transaktion und manchmal auch während der Ausführung der Abfrage freigegeben.
  • InnoDB hat verschiedene Arten von Sperren: Record, Gap, Next-Key und mehr.

Im Abschnitt "Row Locking" wird das im Detail erläutert. Fassen wir zunächst zusammen, wie die Abfragezeit die Sperrzeit einschließt.Abbildung 1-1 zeigt die Sperren, die während der Abfrageausführung erworben und freigegeben werden.

emsp 0101
Abbildung 1-1. Sperrzeit während der Abfrageausführung

Die Etiketten 1 bis 10 markieren Ereignisse und Details in Bezug auf die Schließung:

  1. Gemeinsame Metadatensperre für die Tabelle erlangen

  2. Erlangung einer absichtsexklusiven (IX) Tabellensperre

  3. Zeilensperre erwerben 1

  4. Zeile 1 aktualisieren (schreiben)

  5. Reihensperre erwerben 2

  6. Reihensperre 2 lösen

  7. Reihensperre erwerben 3

  8. Zeile 3 aktualisieren (schreiben)

  9. Transaktion festschreiben

  10. Löse alle Sperren

Zwei Punkte von Interesse:

  • Die Sperrzeit aus dem Leistungsschema enthält nur die Labels 1 und 2. Im Protokoll für langsame Abfragen sind die Labels 1, 2, 3, 5 und 7 enthalten.

  • Obwohl Zeile 2 gesperrt ist (Label 5), wird sie nicht geschrieben und ihre Sperre wird freigegeben (Label 6), bevor die Transaktion festgeschrieben wird (Label 9). Das kann passieren, muss aber nicht. Es hängt von der Abfrage und dem Isolationslevel der Transaktion ab.

Das war eine Menge Information über Sperrzeit und Sperren, aber jetzt bist du gut gerüstet, um die Sperrzeit in deiner Abfrageanalyse zu verstehen.

Untersuchte Reihen

Geprüfte Zeilen ist die Anzahl der Zeilen, auf die MySQL zugegriffen hat, um übereinstimmende Zeilen zu finden. Sie gibt die Selektivität der Abfrage und der Indizes an. Je selektiver beide sind, desto weniger Zeit verschwendet MySQL mit der Prüfung nicht übereinstimmender Zeilen. Dies gilt für Lese- und Schreibanweisungen, außer INSERT, es sei denn, es handelt sich um eine INSERT...SELECT Anweisung.

Um die untersuchten Zeilen zu verstehen, schauen wir uns zwei Beispiele an. Zuerst nehmen wir die folgende Tabelle, t1, und drei Zeilen:

CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

+----+---+
| id | c |
+----+---+
|  1 | a |
|  2 | b |
|  3 | c |
+----+---+

Die Spalte id ist der Primärschlüssel, und die Spalte c ist nicht indiziert.

Die Abfrage SELECT c FROM t1 WHERE c = 'b' stimmt mit einer Zeile überein, untersucht aber drei Zeilen, weil es keinen eindeutigen Index auf der Spalte c gibt. Daher weiß MySQL nicht, wie viele Zeilen mit der Klausel WHERE übereinstimmen. Wir können sehen, dass nur eine Zeile übereinstimmt, aber MySQL hat keine Augen, sondern Indizes. Im Gegensatz dazu stimmt die Abfrage SELECT c FROM t1 WHERE id = 2 überein und untersucht nur eine Zeile, weil es einen eindeutigen Index auf der Spalte id (dem Primärschlüssel) gibt und die Tabellenbedingung den gesamten Index verwendet. Jetzt kann MySQL bildlich gesehen sehen, dass nur eine Zeile übereinstimmt, also ist das alles, was er untersucht.Kapitel 2 lehrt Indizes und Indexierung, die Tabellenbedingungen und vieles mehr erklären.

Für das zweite Beispiel verwenden wir die folgende Tabelle, t2, und sieben Zeilen:

CREATE TABLE `t2` (
  `id` int NOT NULL,
  `c` char(1) NOT NULL,
  `d` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
+----+------+--------+
| id | c    | d      |
+----+------+--------+
|  1 | a    | apple  |
|  2 | a    | ant    |
|  3 | a    | acorn  |
|  4 | a    | apron  |
|  5 | b    | banana |
|  6 | b    | bike   |
|  7 | c    | car    |
+----+------+--------+

Die Spalte id ist die gleiche wie zuvor (Primärschlüssel). Die Spalte c hat einen nicht eindeutigen Index. Die Spalte d ist nicht indiziert.

Wie viele Zeilen untersucht die Abfrage SELECT d FROM t2 WHERE c = 'a' AND d = 'acorn'? Die Antwort lautet: vier. MySQL verwendet den nicht eindeutigen Index auf der Spalte c, um Zeilen zu finden, die der Bedingung c = 'a' entsprechen. Um die andere Bedingung, d = 'acorn', zu erfüllen, untersucht MySQL jede dieser vier Zeilen. Daher untersucht die Abfrage vier Zeilen, trifft aber nur auf eine Zeile zu (und gibt sie zurück).

Es ist nicht ungewöhnlich, dass man feststellt, dass eine Abfrage mehr Zeilen prüft als erwartet. Der Grund dafür ist in der Regel die Selektivität der Abfrage oder die Indizes (oder beides), aber manchmal liegt es auch daran, dass die Tabelle viel größer geworden ist als erwartet, so dass es viel mehr Zeilen zu untersuchen gibt. In Kapitel 3 wird das genauer untersucht (Wortspiel beabsichtigt).

Geprüfte Zeilen sagen nur die Hälfte der Geschichte aus. Die andere Hälfte sind die gesendeten Zeilen.

Gesendete Zeilen

Gesendete Zeilen ist die Anzahl der Zeilen, die an den Kunden zurückgegeben werden - die Größe der Ergebnismenge. Die gesendeten Zeilen sind am aussagekräftigsten im Verhältnis zu den untersuchten Zeilen.

Gesendete Zeilen = Untersuchte Zeilen

Der Idealfall ist, wenn gesendete und geprüfte Zeilen gleich sind und der Wert relativ klein ist, insbesondere als Prozentsatz der Gesamtzeilen, und die Abfrageantwortzeit akzeptabel ist. Zum Beispiel sind 1.000 Zeilen aus einer Tabelle mit einer Million Zeilen ein angemessener Wert von 0,1 %. Das ist ideal, wenn die Antwortzeit akzeptabel ist. Aber 1.000 Zeilen aus einer Tabelle mit nur 10.000 Zeilen ist ein fragwürdiger Wert von 10 %, selbst wenn die Antwortzeit akzeptabel ist. Unabhängig vom Prozentsatz gilt: Wenn gesendete und untersuchte Zeilen gleich sind und der Wert verdächtig hoch ist, deutet das stark darauf hin, dass die Abfrage einen Tabellenscan auslöst, der in der Regel schlecht für die Leistung ist -"Tabellenscan" erklärt warum.

Gesendete Zeilen < Geprüfte Zeilen

Weniger gesendete als geprüfte Zeilen sind ein zuverlässiges Zeichen für eine schlechte Abfrage- oder Indexselektivität. Wenn der Unterschied extrem ist, erklärt das wahrscheinlich die langsame Antwortzeit. Zum Beispiel sind 1.000 gesendete und 100.000 geprüfte Zeilen keine großen Werte, aber sie bedeuten, dass 99% der Zeilen nicht übereinstimmten - die Abfrage hat MySQL viel Zeit gekostet. Selbst wenn die Antwortzeit akzeptabel ist, könnte ein Index die verschwendete Zeit drastisch reduzieren.

Gesendete Zeilen > Geprüfte Zeilen

Es ist möglich, aber selten, dass mehr Zeilen gesendet werden, als untersucht wurden. Das passiert unter besonderen Bedingungen, zum Beispiel wenn MySQL die Abfrage "wegoptimieren" kann. Zum Beispiel sendet SELECT COUNT(id) FROM t2 für die Tabelle im vorherigen Abschnitt eine Zeile für den Wert von COUNT(id), untersucht aber null Zeilen.

Die Anzahl der gesendeten Zeilen ist an sich selten ein Problem. Moderne Netzwerke sind schnell und das MySQL-Protokoll ist effizient. Wenn deine Distribution und deine Version von MySQL die Metrik der gesendeten Bytes im Slow Query Log haben (das Performance Schema stellt diese Abfragemetrik nicht zur Verfügung), kannst du sie auf zwei Arten nutzen. Erstens verraten die Minimal-, Maximal- und Durchschnittswerte die Größe der Ergebnismenge in Bytes. Diese ist normalerweise klein, kann aber groß sein, wenn die Abfrage BLOB oder JSON Spalten zurückgibt. Zweitens kann die Gesamtzahl der gesendeten Bytes in einen Netzwerkdurchsatz (Mbps oder Gbps) umgewandelt werden, um die Netzwerkauslastung der Abfrage zu ermitteln, die in der Regel ebenfalls sehr gering ist.

Betroffene Zeilen

Betroffene Zeilen ist die Anzahl der eingefügten, aktualisierten oder gelöschten Zeilen. Ingenieure achten sehr darauf, dass nur die richtigen Zeilen betroffen sind. Es ist ein schwerwiegender Fehler, wenn die falschen Zeilen geändert werden. So gesehen ist der Wert der betroffenen Zeilen immer korrekt. Aber ein überraschend großer Wert könnte auf eine neue oder geänderte Abfrage hinweisen, die mehr Zeilen betrifft als beabsichtigt.

Eine andere Möglichkeit, die betroffenen Zeilen zu betrachten, ist die Stapelgröße von Bulk-Operationen. Bulk INSERT, UPDATE und DELETE sind eine häufige Ursache für verschiedene Probleme: Replikationsverzögerung, Länge der History-Liste, Sperrzeit und allgemeine Leistungseinbußen. Ebenso häufig wird die Frage gestellt: "Wie groß sollte die Batchgröße sein?" Es gibt keine allgemeingültige Antwort. Stattdessen musst du die Batch-Größe und -Rate bestimmen, die MySQL und die Anwendung verkraften können, ohne dass die Antwortzeit der Abfrage beeinträchtigt wird. Das erkläre ich im Abschnitt "Batch-Größe", der sich auf DELETE konzentriert, aber auch auf INSERT und UPDATE anwendbar ist.

Scan auswählen

Select scan ist die Anzahl der vollständigen Tabellenscans auf die erste Tabelle, auf die zugegriffen wird. (Wenn die Abfrage auf zwei oder mehr Tabellen zugreift, gilt die nächste Metrik: select full join.) Dies ist normalerweise schlecht für die Leistung, weil es bedeutet, dass die Abfrage keinen Index verwendet. Nach Kapitel 2, in dem Indizes und Indexierung gelehrt werden, sollte es einfach sein, einen Index hinzuzufügen, um einen Tabellenscan zu beheben. Wenn select scan nicht null ist, wird dringend empfohlen, die Abfrage zu optimieren.

Es ist möglich, aber sehr selten, dass eine Abfrage manchmal eine Tabellendurchsuchung verursacht, aber nicht immer. Um herauszufinden, warum das so ist, brauchst du ein Abfragebeispiel und einen EXPLAIN-Plan für beides: ein Abfragebeispiel, das eine Tabellendurchsuchung verursacht, und ein Abfragebeispiel, bei dem das nicht der Fall ist. Ein wahrscheinlicher Grund ist, wie viele Zeilen MySQL schätzt, dass die Abfrage im Verhältnis zur Indexkardinalität (die Anzahl der eindeutigen Werte im Index), der Gesamtzahl der Zeilen in der Tabelle und anderen Kosten untersucht wird. (Der MySQL-Abfrageoptimierer verwendet ein Kostenmodell.) Die Schätzungen sind nicht perfekt und manchmal liegt MySQL falsch, was zu einem Tabellenscan oder einem suboptimalen Ausführungsplan führt, aber wie gesagt: Das ist sehr selten.

Höchstwahrscheinlich ist Select Scan entweder ganz Null oder ganz Eins (es ist ein binärer Wert). Sei froh, wenn er Null ist. Optimiere die Abfrage, wenn er nicht Null ist.

Vollständige Verbindung auswählen

Select full join ist die Anzahl der vollständigen Tabellenscans auf den verbundenen Tabellen. Dies ist ähnlich wie select scan, aber schlechter - ich erkläre gleich, warum. Select full join sollte immer Null sein; wenn nicht, ist eine Abfrageoptimierung praktisch erforderlich.

Wenn du EXPLAIN eine Abfrage mit mehreren Tabellen stellst, gibt MySQL die Join-Reihenfolge der Tabellen von oben (erste Tabelle) nach unten (letzte Tabelle) aus. Select scan gilt nur für die erste Tabelle. Select full join gilt nur für die zweite und die folgenden Tabellen.

Die Reihenfolge der Tabellenverknüpfungen wird von MySQL bestimmt, nicht von der Abfrage.2 Beispiel 1-4 zeigt den EXPLAIN-Plan für SELECT...FROM t1, t2, t3: MySQL bestimmt eine andere Join-Reihenfolge als den impliziten Drei-Tabellen-Join in der Abfrage.

Beispiel 1-4. EXPLAIN-Plan für drei verbundene Tabellen
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: NULL

MySQL liest zuerst die Tabelle t3, dann die Tabelle t1 und dann die Tabelle t2. Diese Join-Reihenfolge unterscheidet sich von der Abfrage (FROM t1, t2, t3), deshalb musst du EXPLAIN eine Abfrage lesen, um die Join-Reihenfolge zu sehen.

Tipp

Immer EXPLAIN eine Abfrage, um ihre Join-Reihenfolge zu sehen.

Select scan gilt für die Tabelle t3, weil sie die erste Tabelle in der Join-Reihenfolge ist und einen Table Scan verursacht (angezeigt durch type: ALL). Select full join würde für die Tabelle t1 gelten, wenn sie einen Table Scan verursachen würde, aber das tut sie nicht: MySQL fügt die Tabelle mit einem Range Scan des Primärschlüssels zusammen (angegeben durch type: range bzw. key: PRIMARY). Select full join gilt für die Tabelle t2, weil MySQL sie mit einem Full Table Scan zusammenführt (angegeben durch type: ALL).

Der Tabellenscan auf t2 wird Full Join genannt, weil MySQL beim Join die gesamte Tabelle scannt. Select Full Join ist schlechter als Select Scan, weil die Anzahl der Full Joins, die während der Ausführung der Abfrage auf einer Tabelle auftreten, gleich dem Produkt der Zeilen aus den vorangehenden Tabellen ist. MySQL schätzt drei Zeilen aus der Tabelle t3 (angegeben durch rows: 3) und zwei Zeilen aus der Tabelle t1 (angegeben durch rows: 2). Daher ergeben sich 3 × 2 = 6 Full Joins in der Tabelle t2 während der Ausführung der Abfrage. Der Wert der Select Full Join-Metrik ist jedoch 1, weil er Full Joins im Ausführungsplan und nicht während der Ausführung der Abfrage zählt, was ausreichend ist, da selbst ein Full Join zu viel ist.

Hinweis

Seit MySQL 8.0.18 verbessert die Hash-Join-Optimierung die Leistung für bestimmte Joins, aber die Vermeidung von Full-Joins bleibt die bewährte Methode. Einen kurzen Überblick über den Hash-Join findest du unter "Tabellen-Join-Algorithmen".

Erstellte tmp-Tabellen

Created tmp disk tables ist die Anzahl der temporären Tabellen, die auf der Festplatte erstellt wurden. Es ist normal, dass Abfragen temporäre Tabellen im Speicher erstellen; wenn eine temporäre Tabelle im Speicher jedoch zu groß wird, schreibt MySQL sie auf die Festplatte. Das kann die Antwortzeit beeinträchtigen, da der Zugriff auf die Festplatte um Größenordnungen langsamer ist als der Zugriff auf den Speicher.

Temporäre Tabellen auf der Festplatte sind jedoch kein häufiges Problem, da MySQL versucht, sie zu vermeiden. Zu viele "tmp disk tables" deuten darauf hin, dass eine Abfrage optimiert werden kann, oder (vielleicht) die Systemvariable zu klein ist. tmp_table_size Optimiere immer zuerst die Abfragen und ändere erst in letzter Instanz die Systemvariablen, vor allem die, die die Speicherzuweisung beeinflussen.

Weitere Informationen findest du unter "Internal Temporary Table Use in MySQL" im MySQL-Handbuch.

Anzahl der Abfragen

Abfrageanzahl ist die Anzahl der Abfrageausführungen. Der Wert ist beliebig, es sei denn, er ist extrem niedrig und die Abfrage ist langsam. "Niedrig und langsam" ist eine seltsame Kombination, die es zu untersuchen gilt.

Während ich dies schreibe, schaue ich mir ein Abfrageprofil an, das ein perfektes Beispiel ist: Die langsamste Abfrage wurde einmal ausgeführt, benötigte aber 44 % der Ausführungszeit. Andere Metriken sind:

  • Reaktionszeit: 16 s

  • Sperrzeit: 110 μs

  • Untersuchte Reihen: 132,000

  • Reihen gesendet: 13

Eine nicht alltägliche Abfrage. Es sieht so aus, als hätte ein Techniker die Abfrage manuell ausgeführt, aber aus dem Text der Zusammenfassung kann ich erkennen, dass sie programmgesteuert erstellt wurde. Was verbirgt sich hinter dieser Abfrage? Um das herauszufinden, muss ich die Anwendungsentwickler fragen.

Metadaten und die Anwendung

Zu einer Abfrageanalyse gehört mehr als Abfragemetriken: Metadaten. Ohne mindestens zwei Metadaten kannst du keine Abfrageanalyse durchführen: den EXPLAIN-Plan (auch Abfrageausführungsplan genannt) und die Tabellenstruktur für jede Tabelle. Einige Abfragemetrik-Tools sammeln automatisch Metadaten und zeigen sie im Abfragebericht an. Wenn dein Abfragemetrik-Tool das nicht tut, mach dir keine Sorgen: Es ist einfach, Metadaten zu sammeln.EXPLAIN und SHOW CREATE TABLE zeigen den EXPLAIN-Plan bzw. die Tabellenstrukturen an.

Metadaten sind unverzichtbar für die Analyse von Abfragen, die Optimierung von Abfragen und die Leistung von MySQL im Allgemeinen.EXPLAIN ist ein unverzichtbares Werkzeug in deinem MySQL-Repertoire. Ich erkläre es in "EXPLAIN: Query Execution Plan" und verwende es ausführlich in diesem Buch.

Zur Abfrageanalyse gehört noch mehr als Abfragemetriken und Metadaten: die Anwendung. Metriken und Metadaten sind ein Muss für jede Abfrageanalyse, aber die Geschichte ist erst vollständig, wenn du weißt, welchem Zweck die Abfrage dient: Warum führt die Anwendung die Abfrage aus? Wenn du das weißt, kannst du Änderungen an der Anwendung bewerten, was im Mittelpunkt von Kapitel 4 steht. Mehr als einmal habe ich erlebt, dass Ingenieure erkannt haben, dass eine Abfrage viel einfacher sein kann - oder komplett entfernt werden kann.

Abfragemetriken, Metadaten und die Anwendung sollten die Geschichte vervollständigen. Aber ich wäre nachlässig, nicht zu erwähnen, dass manchmal auch Probleme außerhalb von MySQL und der Anwendung die Geschichte beeinflussen - und das meist nicht zum Besseren."Noisy Neighbors" ist ein klassischer Fall. Wenn die Antwortzeit langsam ist, aber eine gründliche Abfrageanalyse nicht den Grund dafür aufzeigt, dann sollten Sie externe Probleme in Betracht ziehen. Aber ziehen Sie nicht zu schnell diese Schlussfolgerung; externe Probleme sollten die Ausnahme sein, niemals die Norm.

Relative Werte

Für ist der einzige objektiv gute Wert Null, denn wie das Sprichwort sagt, ist der schnellste Weg, etwas zu tun, es nicht zu tun. Werte, die nicht Null sind, sind immer relativ zur Abfrage und Anwendung. Ein Beispiel: Tausend gesendete Zeilen sind im Allgemeinen in Ordnung, aber es könnte schrecklich sein, wenn die Abfrage nur eine Zeile zurückgeben soll. Relative Werte sind sinnvoll, wenn man die ganze Geschichte betrachtet: Metriken, Metadaten und die Anwendung.

Hier ist eine weitere wahre Geschichte, um zu verdeutlichen, dass Werte relativ und aussagekräftig sind. Ich habe eine Anwendung geerbt, die im Laufe der Jahre immer langsamer wurde. Es handelte sich um eine interne Anwendung, die nicht von Kunden genutzt wurde, so dass es keine Priorität hatte, sie zu reparieren, bis sie unerträglich langsam wurde. Im Abfrageprofil untersuchte die langsamste Abfrage mehr als zehntausend Zeilen und gab sie zurück - kein vollständiger Tabellenscan, sondern einfach nur eine Menge Zeilen. Anstatt mich auf die Werte zu fixieren, stöberte ich im Quellcode und entdeckte, dass die Funktion, die die Abfrage ausführte, nur die Anzahl der Zeilen zählte, aber die Zeilen nicht verwendete. Sie war langsam, weil sie unnötigerweise auf Tausende von Zeilen zugriff und diese zurückgab, und sie wurde mit der Zeit immer langsamer, weil die Anzahl der Zeilen mit dem Wachstum der Datenbank zunahm. Mit der vollständigen Geschichte war die Optimierung offensichtlich und einfach: SELECT COUNT(*).

Durchschnitt, Perzentil und Maximum

Es ist üblich, über die Antwortzeit von Abfragen zu sprechen, als ob es sich um einen einzelnen Wert handeln würde, aber das ist nicht der Fall. Aus dem Kapitel "Aggregation" weißt du, dass Abfragemetriken gruppiert und nach Abfrage aggregiert werden. Daher werden Abfragemetriken als einzelne, statistische Werte angegeben: Minimum, Maximum, Durchschnitt und Perzentil. Du bist zweifellos mit diesen allgegenwärtigen "Statistiken" vertraut, aber in Bezug auf die Antwortzeit von Abfragen werden dich die folgenden Punkte vielleicht überraschen:

  • Durchschnitt ist übermäßig optimistisch

  • Der Prozentsatz ist eine Annahme

  • Das Maximum ist die beste Darstellung

Lass mich erklären:

Durchschnitt

Lass dich nicht vom Durchschnitt täuschen: Wenn die Anzahl der Abfragen gering ist, können ein paar sehr große oder kleine Werte die durchschnittliche Antwortzeit (oder jede andere Kennzahl) verzerren. Ohne die Verteilung der Werte zu kennen, wissen wir außerdem nicht, welchen Prozentsatz der Werte der Durchschnitt repräsentiert. Wenn zum Beispiel der Durchschnitt dem Median entspricht, repräsentiert der Durchschnitt die unteren 50 % der Werte, also die besseren (schnelleren) Antwortzeiten. In diesem Fall ist der Durchschnitt zu optimistisch. (Die meisten Werte sind zu optimistisch, wenn du die schlechteste Hälfte ignorierst.) Der Durchschnitt sagt dir nur auf einen Blick, ob die Abfrage normalerweise in Mikrosekunden, Millisekunden oder Sekunden ausgeführt wird. Mehr darfst du nicht hineininterpretieren.

Perzentil

Perzentile lösen das Problem der Durchschnittswerte. Ohne eine ausführliche Erklärung der Perzentile zu geben, ist P95 der Wert, der bei 95% der Stichproben kleiner oder gleich ist.3 Wenn P95 zum Beispiel 100 ms beträgt, sind 95% der Werte kleiner oder gleich 100 ms und 5% der Werte sind größer als 100 ms. P95 repräsentiert also 95% der Werte, was objektiv repräsentativer - und weniger optimistisch - ist als der Durchschnitt. Es gibt noch einen weiteren Grund, warum Perzentile verwendet werden: Der kleine Prozentsatz der Werte, der ignoriert wird, gilt als Ausreißer. Zum Beispiel können Netzwerkstörungen und Ausreißer dazu führen, dass ein kleiner Prozentsatz der Abfragen länger als normal dauert. Da dies nicht die Schuld von MySQL ist, ignorieren wir diese Ausreißer als Ausführungszeiten.

Perzentile sind gängige Praxis, aber sie sind auch eine Annahme. Ja, es kann Ausreißer geben, aber die sollten nachgewiesen werden, nicht angenommen. Solange nicht bewiesen ist, dass die obersten N% keine Ausreißer sind, sind sie die interessantesten Werte, eben weil sie nicht normal sind. Was ist die Ursache dafür? Das ist schwer zu beantworten. Deshalb sind Perzentile die gängige Praxis: Es ist einfacher, die obersten N % der Werte zu ignorieren, als sich auf die Suche nach der Antwort zu machen.

Das beste Perzentil ist P999 (99,9 %), denn 0,1 % der Werte zu verwerfen ist ein akzeptabler Kompromiss zwischen der Annahme, dass es sich um Ausreißer handelt, und der Tatsache, dass es Ausreißer gibt.4

Maximum

Die maximale Abfragezeit löst das Problem der Perzentile: Es werden keine Werte verworfen. Der Maximalwert ist kein Mythos oder eine statistische Erscheinung wie der Durchschnitt. Irgendwo auf der Welt hat ein Anwendungsnutzer die maximale Abfragezeit erlebt - oder nach ein paar Sekunden aufgegeben und ist gegangen. Wenn du wissen willst, warum, kannst du die Antwort finden. Während es schwierig ist, die obersten N% der Werte zu erklären, weil es viele Werte und damit viele potenziell unterschiedliche Antworten gibt, ist die Erklärung des Maximums ein einziger Wert und eine einzige Antwort. Abfragemetrik-Tools verwenden oft die Abfrage mit der höchsten Antwortzeit als Beispiel, was die Erklärung fast trivial macht, weil du die sprichwörtliche rauchende Pistole hast. Mit dieser Stichprobe gibt es zwei Möglichkeiten: Entweder reproduziert sie das Problem, dann kannst du mit der Analyse fortfahren, oder sie reproduziert das Problem nicht, dann hast du bewiesen, dass es sich um einen Ausreißer handelt, der ignoriert werden kann.

Hier ist eine weitere wahre Geschichte aus dem ersten Fall. Eine ansonsten gute Anwendung antwortete zufällig sehr langsam. Mindest-, Durchschnitts- und P99-Abfragezeit lagen alle bei Millisekunden, aber die maximale Abfragezeit betrug Sekunden. Anstatt das Maximum zu ignorieren, sammelte ich Abfrageproben mit normaler und maximaler Ausführungszeit. Der Unterschied lag in der Größe der IN Liste in der WHERE Klausel: Hunderte von Werten für die normale Abfragezeit und mehrere tausend Werte für die maximale Abfragezeit. Das Abrufen von mehr Werten dauert länger, aber Millisekunden bis Sekunden sind selbst bei Tausenden von Werten nicht normal. EXPLAIN lieferte die Antwort: Die normale Abfragezeit verwendete einen Index, aber die maximale Abfragezeit verursachte einen Full Table Scan. MySQL kann Abfrageausführungspläne umschalten (siehe "It's a Trap! (Wenn MySQL einen anderen Index wählt)"), was MySQL erklärt, aber was erklärt die Anwendung? Um es kurz zu machen: Die Abfrage wurde verwendet, um Daten für die Betrugserkennung abzufragen, und gelegentlich wurden in einem großen Fall mehrere tausend Zeilen auf einmal abgefragt, was MySQL dazu veranlasste, die Abfrageausführungspläne zu wechseln. Normalerweise funktionierte die Abfrage einwandfrei, aber die Untersuchung der maximalen Antwortzeit offenbarte nicht nur ein MySQL-Problem, sondern auch eine Möglichkeit, die Anwendung und das Benutzererlebnis zu verbessern, indem große Abfragen effizienter verarbeitet werden.

Durchschnitt, Perzentil und Maximum sind nützlich, aber sei dir bewusst, was sie darstellen und was nicht.

Achte auch auf die Verteilung der Werte zwischen dem Minimum und dem Maximum. Wenn du Glück hast, enthält der Abfragebericht Histogramme, aber verlass dich nicht darauf: Die Berechnung von Histogrammen für einen beliebigen Zeitbereich ist schwierig und wird daher von fast keinem Abfragemetrik-Tool durchgeführt. Die grundlegenden Statistiken (Minimum, Maximum, Durchschnitt und Perzentil) geben genug Aufschluss über die Verteilung, um festzustellen, ob die Abfrage stabil ist: Die Metriken sind bei jeder Ausführung ungefähr gleich. (In Kapitel 6 komme ich auf die Idee der Stabilität zurück. Siehe "Normal und stabil: Die beste Datenbank ist eine langweilige Datenbank") Instabile Abfragen erschweren die Analyse: Was ist die Ursache dafür, dass die Abfrage anders ausgeführt wird? Die Ursache liegt wahrscheinlich außerhalb von MySQL, was es schwieriger macht, sie zu finden, aber es ist notwendig, sie zu finden, denn stabile Abfragen lassen sich leichter analysieren, verstehen und optimieren.

Verbesserung der Abfrage-Antwortzeit

Die Verbesserung der Antwortzeit von Abfragen ist eine Reise, die man Abfrageoptimierung nennt. Ich nenne es eine Reise, um die richtigen Erwartungen zu wecken. Die Optimierung von Abfragen kostet Zeit und Mühe, aber es gibt ein Ziel: eine schnellere Antwortzeit. Um die Reise effizient zu gestalten - und nicht Zeit und Mühe zu verschwenden - gibt es zwei Teile: die direkte Abfrageoptimierung und die indirekte Abfrageoptimierung.

Direkte Abfrage-Optimierung

Direkte Abfrageoptimierung sind Änderungen an Abfragen und Indizes. Diese Änderungen lösen eine Menge Leistungsprobleme, weshalb die Reise mit der direkten Abfrageoptimierung beginnt. Und weil diese Änderungen so leistungsstark sind, endet die Reise oft auch hier.

Lassen Sie mich eine Analogie verwenden, die jetzt etwas vereinfacht ist, aber später aufschlussreicher sein wird. Stellen Sie sich eine Abfrage wie ein Auto vor. Mechaniker haben Werkzeuge, um ein Auto zu reparieren, wenn es nicht gut läuft. Einige Werkzeuge sind alltäglich (wie ein Schraubenschlüssel), andere sind spezialisiert (wie eine doppelte Nockenwellenverriegelung). Sobald ein Mechaniker die Motorhaube öffnet und das Problem findet, weiß er, welche Werkzeuge er braucht, um es zu beheben. Genauso haben Ingenieure Werkzeuge, um eine Abfrage zu reparieren, wenn sie langsam läuft. Die alltäglichen Werkzeuge sind Abfrageanalysen und Indizes, EXPLAINDie speziellen Werkzeuge sind abfragespezifische Optimierungen. Um nur einige aus "Optimizing SELECT Statements" im MySQL-Handbuch zu nennen:

  • Optimierung der Reichweite

  • Optimierung der Indexzusammenführung

  • Optimierung des Hash Join

  • Indexbedingung Pushdown-Optimierung

  • Optimierung des Mehrbereichslesens

  • Optimierung der konstanten Faltung

  • IS NULL Optimierung

  • ORDER BY Optimierung

  • GROUP BY Optimierung

  • DISTINCT Optimierung

  • LIMIT Abfrage-Optimierung

In diesem Buch erkläre ich keine abfragespezifischen Optimierungen, weil sie bereits in Kapitel 8, "Optimierung" im MySQL-Handbuch ausführlich erklärt werden, das maßgebend ist und regelmäßig aktualisiert wird. Außerdem variieren die abfragespezifischen Optimierungen je nach MySQL-Version und -Distribution. Stattdessen lehre ich in Kapitel 2 Indizes und Indizierung: die Grundlage für das Wissen, welche abfragespezifischen Optimierungen du verwenden solltest - und wie - wenn du eine langsame Abfrage behebst. Nach Kapitel 2 wirst du mit speziellen Werkzeugen wie der "Index Condition Pushdown Optimization" umgehen können wie ein Mechanikermeister mit einer doppelten Überkopfkurbel.

Immer wieder spreche ich mit Ingenieuren, die überrascht und ein wenig unglücklich sind, wenn die Abfrageoptimierungen, die sie so eifrig angewendet haben, das Problem nicht lösen. Eine direkte Abfrageoptimierung ist notwendig, aber nicht immer ausreichend. Eine optimierte Abfrage kann unter verschiedenen Umständen ein Problem sein oder werden. Wenn du eine Abfrage nicht weiter optimieren kannst (oder du kannst sie überhaupt nicht optimieren, weil du keinen Zugriff auf den Quellcode hast), kannst du um die Abfrage herum optimieren, was zu Teil zwei der Reise führt: indirekte Abfrageoptimierung.

Indirekte Abfrage-Optimierung

Indirekte Abfrageoptimierung sind Änderungen an Daten und Zugriffsmustern. Anstatt eine Abfrage zu ändern, änderst du, worauf die Abfrage zugreift und wie: die Daten bzw. die Zugriffsmuster. Diese Änderungen optimieren die Abfrage indirekt, weil Abfrage, Daten und Zugriffsmuster in Bezug auf die Leistung untrennbar miteinander verbunden sind. Änderungen an einem beeinflussen die anderen. Das ist leicht zu beweisen.

Angenommen, du hast eine langsame Abfrage. Datengröße und Zugriffsmuster spielen für diesen Beweis keine Rolle, also stell dir vor, was immer du willst. Ich kann die Antwortzeit der Abfrage auf fast Null reduzieren. (Sagen wir mal, fast Null ist 1 Mikrosekunde. Für einen Computer ist das eine lange Zeit, aber für einen Menschen ist es nicht wahrnehmbar.) Die indirekte "Optimierung" lautet: TRUNCATE TABLE. Wenn keine Daten vorhanden sind, kann MySQL jede Abfrage in nahezu Nullzeit ausführen. Das ist zwar geschummelt, aber es beweist, dass die Verringerung der Datengröße die Antwortzeit der Abfrage verbessert.

Kommen wir noch einmal auf den Vergleich mit dem Auto zurück. Indirekte Abfrageoptimierung ist vergleichbar mit der Änderung wichtiger Designelemente eines Autos. Zum Beispiel ist das Gewicht ein Faktor für die Kraftstoffeffizienz: Ein geringeres Gewicht erhöht die Kraftstoffeffizienz. (Daten sind mit dem Gewicht vergleichbar, weshalb TRUNCATE TABLE die Leistung drastisch erhöht - aber benutze diese "Optimierung" nicht.) Eine Gewichtsreduzierung ist keine einfache (direkte) Veränderung, denn Ingenieure können nicht auf magische Weise dafür sorgen, dass Teile weniger wiegen. Stattdessen müssen sie erhebliche Änderungen vornehmen, wie z. B. den Wechsel von Stahl zu Aluminium, was sich auf viele andere Konstruktionselemente auswirken kann. Daher sind diese Änderungen mit einem größeren Aufwand verbunden.

Ein höherer Aufwand ist der Grund, warum die indirekte Abfrageoptimierung Teil zwei der Reise ist. Wenn die direkte Abfrageoptimierung das Problem löst, dann hör auf - sei effizient. (Und herzlichen Glückwunsch.) Wenn das nicht der Fall ist und du dir sicher bist, dass die Abfrage nicht weiter optimiert werden kann, dann ist es an der Zeit, die Daten und Zugriffsmuster zu ändern, was in den Kapiteln 3 und 4 behandelt wird.

Wann Abfragen optimiert werden sollten

Wenn du eine langsame Abfrage behebst, wird eine andere an ihre Stelle treten. Es wird immer langsame Abfragen geben, aber du solltest sie nicht immer optimieren, denn das ist keine effiziente Nutzung deiner Zeit. Erinnere dich stattdessen an den "Nordstern" und frage: Ist die Antwortzeit der Abfrage akzeptabel? Wenn nicht, dann optimiere die Abfragen bitte weiter. Wenn ja, dann bist du für den Moment fertig, denn wenn die Datenbank schnell ist, schaut niemand hin oder stellt Fragen.

Als DBA würde ich mir wünschen, dass du jede Woche die Abfragemetriken (beginnend mit dem "Abfrageprofil") überprüfst und bei Bedarf die langsamsten Abfragen optimierst, aber als Softwareentwickler weiß ich, dass das nicht praktisch ist und fast nie passiert. Hier sind stattdessen drei Anlässe, bei denen du Abfragen optimieren solltest.

Leistung wirkt sich auf Kunden aus

Wenn die Leistung die Kunden beeinträchtigt, ist es die Pflicht der Ingenieure, die Abfragen zu optimieren. Ich glaube nicht, dass irgendein Ingenieur dem widersprechen würde; vielmehr sind die Ingenieure bestrebt, die Leistung zu verbessern. Manche mögen sagen, das sei ein schlechter Ratschlag, weil er reaktiv und nicht proaktiv ist, aber meine überwältigende Erfahrung ist, dass Ingenieure (und sogar DBAs) erst dann auf die Abfragemetriken schauen, wenn die Kunden melden, dass die Anwendung zu langsam ist oder die Zeit abläuft. Solange die Abfragemetriken immer einsatzbereit sind, ist dies ein objektiv guter Zeitpunkt, um Abfragen zu optimieren, denn der Bedarf an besserer Leistung ist genauso real wie deine Kunden.

Vor und nach den Codeänderungen

Die meisten Ingenieure sprechen sich nicht dagegen aus, die Optimierung von Abfragen vor und nach Codeänderungen zu priorisieren, aber meiner Erfahrung nach tun sie es auch nicht. Ich bitte dich inständig, dieses häufige Muster zu vermeiden: Scheinbar harmlose Änderungen werden am Code vorgenommen, im Staging getestet, in der Produktion eingesetzt und dann fängt die Leistung an zu "sprudeln" (eine bunte Metapher, die mit Toiletten zu tun hat und "schlechter werden" bedeutet). Was ist passiert? Die Ursache sind meist Änderungen an Abfragen und Zugriffsmustern, die eng miteinander verbunden sind. In Kapitel 2 wird erklärt, warum das so ist; Kapitel 3 und 4 vervollständigen die Erklärung. Fürs Erste gilt: Du wirst ein Held sein, wenn du die Abfragemetriken vor und nach Codeänderungen überprüfst.

Einmal im Monat

Selbst wenn sich dein Code und deine Abfragen nicht ändern, ändern sich mindestens zwei Dinge um sie herum: Daten und Zugriffsmuster. Ich hoffe, dass deine Anwendung sehr erfolgreich ist und immer mehr Daten speichert, während die Zahl der Nutzer/innen "nach oben und nach rechts" klettert. Die Antwortzeit von Abfragen ändert sich im Laufe der Zeit, wenn sich Daten und Zugriffsmuster ändern. Zum Glück sind diese Veränderungen relativ langsam, meist in der Größenordnung von Wochen oder Monaten. Selbst für eine Anwendung, die ein starkes Wachstum erfährt (z. B. wenn jeden Tag Tausende von neuen Nutzern zu den Millionen von bestehenden Nutzern hinzukommen), ist MySQL wirklich gut darin, so zu skalieren, dass die Abfrage-Antwortzeit stabil bleibt - aber nichts hält ewig (selbst die Sterne sterben). Es gibt immer einen Punkt, an dem gute Abfragen schlecht werden. Diese Tatsache wird in den Kapiteln 3 und 4 deutlich. Für den Moment gilt: Du wirst vom Helden zur Legende aufsteigen - vielleicht werden sogar Lieder und Geschichten über dich geschrieben - wenn du einmal im Monat die Abfragemetriken überprüfst.

MySQL: Schneller gehen

Es gibt keine Magie oder ein Geheimnis, um MySQL deutlich schneller zu machen, ohne die Abfragen oder die Anwendung zu ändern. Hier ist eine weitere wahre Geschichte, um zu verdeutlichen, was ich meine.

Ein Team von Entwicklern erfuhr, dass ihre Anwendung von einem Prominenten erwähnt werden würde. Sie erwarteten eine Flut von Datenverkehr, also planten sie vorausschauend, um sicherzustellen, dass MySQL und die Anwendung überleben würden. Eine Ingenieurin aus dem Team bat mich, dabei zu helfen, den MySQL-Durchsatz (QPS) zu erhöhen. Ich fragte: "Um wie viel?" Sie sagte: "Um das 100-fache". Ich sagte: "Klar. Habt ihr ein Jahr Zeit und Lust, die Anwendung neu zu entwickeln?" Sie sagte: "Nein, wir haben nur einen Tag."

Ich verstehe, was der Ingenieur dachte: Wie viel Durchsatz könnte MySQL bewältigen, wenn wir die Hardware erheblich aufrüsten würden - mehr CPU-Kerne, mehr Speicher, mehr IOPS? Es gibt keine einfache oder einzige Antwort, denn das hängt von vielen Faktoren ab, die dieses Buch in den kommenden Kapiteln untersucht. Aber eines ist sicher: Die Zeit ist eine harte Grenze.

Eine Sekunde hat 1.000 Millisekunden - nicht mehr und nicht weniger. Wenn eine Abfrage 100 Millisekunden für die Ausführung benötigt, beträgt ihr Durchsatz im schlimmsten Fall 10 QPS pro CPU-Kern: 1.000 ms / 100 ms/Query = 10 QPS. (Der tatsächliche Durchsatz ist wahrscheinlich höher - mehr dazu gleich.) Wenn sich nichts ändert, bleibt einfach keine Zeit mehr, um die Abfrage mit einem höheren Durchsatz auszuführen.

Um MySQL dazu zu bringen, mehr Arbeit in der gleichen Zeit zu erledigen, hast du drei Möglichkeiten:

  • Verändere die Natur der Zeit

  • Reaktionszeit verkürzen

  • Last erhöhen

Option eins würde den Rahmen dieses Buches sprengen, also konzentrieren wir uns auf die Optionen zwei und drei.

Die Verringerung der Antwortzeit setzt Zeit frei, die MySQL nutzen kann, um mehr Arbeit zu erledigen. Es ist eine einfache Rechnung: Wenn MySQL 999 Millisekunden pro Sekunde beschäftigt ist, hat es eine Millisekunde frei, um mehr Arbeit zu erledigen. Wenn das nicht genug freie Zeit ist, musst du die Zeit verringern, die die aktuelle Arbeit verbraucht. Der beste Weg, um das zu erreichen: direkte Abfrageoptimierung. Wenn das nicht funktioniert: indirekte Abfrageoptimierung. Und schließlich: bessere, schnellere Hardware. In den folgenden Kapiteln lernst du, wie das geht.

Eine Erhöhung der Last - also der Anzahl der gleichzeitig ausgeführten Abfragen - geschieht in der Regel zuerst, weil dafür keine Änderungen an der Abfrage oder der Anwendung erforderlich sind: Es werden einfach mehr Abfragen auf einmal (gleichzeitig) ausgeführt, und MySQL reagiert darauf, indem es mehr CPU-Kerne einsetzt. Dies geschieht, weil ein CPU-Kern einen Thread ausführt, der wiederum eine Abfrage ausführt. Im schlimmsten Fall verwendet MySQL N CPU-Kerne, um N Abfragen gleichzeitig auszuführen. Der schlimmste Fall ist jedoch praktisch nicht existent, weil die Antwortzeit keine CPU-Zeit ist. Ein Anteil der Antwortzeit, der nicht Null ist, ist CPU-Zeit, und der Rest ist nicht CPU-Zeit. Zum Beispiel könnte die Antwortzeit 10 ms CPU-Zeit und 90 ms Disk-I/O-Wartezeit betragen. Daher beträgt der Worst-Case-Durchsatz für eine Abfrage, die 100 Millisekunden für die Ausführung benötigt, 10 QPS pro CPU-Kern, aber der tatsächliche Durchsatz sollte höher sein, da der Worst-Case praktisch nicht vorhanden ist. Klingt toll, oder? Du musst MySQL nur mehr Leistung abverlangen und voilà: mehr Leistung. Aber du weißt, wie die Geschichte ausgeht: Wenn du MySQL zu sehr strapazierst, funktioniert es nicht mehr, denn jedes System hat eine begrenzte Kapazität. MySQL kann die meisten modernen Geräte problemlos an ihre Grenzen bringen, aber versuche es nicht, bevor du nicht "Leistung wird am Limit destabilisiert" gelesen hast .

Unterm Strich: MySQL kann nicht einfach schneller werden. Um MySQL schneller zu machen, musst du dich auf den Weg der direkten und indirekten Abfrageoptimierung machen.

Zusammenfassung

In diesem Kapitel wird die Abfragezeit erläutert, damit du in den folgenden Kapiteln lernst, wie du sie verbessern kannst. Die wichtigsten Punkte, die du mitnehmen kannst, sind:

  • Leistung ist die Abfrage-Antwortzeit: wie lange MySQL braucht, um eine Abfrage auszuführen.

  • Die Abfrage-Antwortzeit ist der Nordstern der MySQL-Leistung, weil sie aussagekräftig und umsetzbar ist.

  • Abfragemetriken stammen aus dem Slow Query Log oder dem Performance Schema.

  • Das Leistungsschema ist die beste Quelle für Abfragemetriken.

  • Abfragemetriken werden gruppiert und nach Digest aggregiert: normalisierte SQL-Anweisungen.

  • Ein Abfrageprofil zeigt langsame Abfragen an; langsam ist relativ zur Sortiermetrik.

  • Ein Abfragebericht zeigt alle verfügbaren Informationen für eine Abfrage; er wird für die Abfrageanalyse verwendet.

  • Das Ziel der Abfrageanalyse ist es, die Ausführung der Abfrage zu verstehen und nicht, langsame Antwortzeiten zu lösen.

  • Die Abfrageanalyse nutzt Abfragemetriken (wie berichtet), Metadaten (EXPLAIN-Pläne, Tabellenstrukturen usw.) und Kenntnisse über die Anwendung.

  • Neun Abfragemetriken sind für jede Abfrageanalyse unerlässlich: Abfragezeit, Sperrzeit, untersuchte Zeilen, gesendete Zeilen, betroffene Zeilen, Select Scan, Select Full Join, erstellte Tmp-Tabellen und Abfrageanzahl.

  • Die Verbesserung der Antwortzeit von Abfragen (Abfrageoptimierung) besteht aus zwei Teilen: der direkten Abfrageoptimierung und der indirekten Abfrageoptimierung.

    • Direkte Abfrageoptimierung sind Änderungen an Abfragen und Indizes.

    • Indirekte Abfrageoptimierung sind Änderungen an Daten und Zugriffsmustern.

  • Überprüfe zumindest das Abfrageprofil und optimiere langsame Abfragen, wenn sich die Leistung auf die Kunden auswirkt, vor und nach Codeänderungen und einmal im Monat.

  • Um MySQL schneller zu machen, musst du die Antwortzeit verkürzen (freie Zeit für mehr Arbeit) oder die Last erhöhen (MySQL muss härter arbeiten).

Das nächste Kapitel befasst sich mit MySQL-Indizes und der direkten Optimierung von Abfragen durch Indizes.

Praxis: Langsame Abfragen identifizieren

Das Ziel dieser Übung ist es, langsame Abfragen mit pt-query-digest zu identifizieren: ein Kommandozeilen-Tool, das ein Abfrageprofil und Abfrageberichte aus einem Protokoll langsamer Abfragen erstellt.

Warnung

Verwenden Sie eine Entwicklungs- oder Staging-MySQL-Instanz - verwenden Sie die Produktionsinstanz nur dann, wenn Sie sicher sind, dass sie keine Probleme verursacht. Das langsame Abfrageprotokoll ist an sich sicher, aber wenn es auf einem stark ausgelasteten Server aktiviert wird, kann es die Festplatten-E/A erhöhen.

Wenn du einen DBA hast, der MySQL verwaltet, kannst du ihn bitten, das Slow Query Log zu aktivieren und zu konfigurieren. Du kannst es auch im MySQL-Handbuch unter "Das Slow Query Log" nachlesen. (Du brauchst ein MySQL-Benutzerkonto mit den Rechten SUPER, um MySQL zu konfigurieren.) Wenn du MySQL in der Cloud verwendest, lies die Dokumentation des Cloud-Providers, um zu erfahren, wie du das Slow Query Log aktivieren und darauf zugreifen kannst.

Die MySQL-Konfigurationen variieren, aber der einfachste Weg, das Slow Query Log zu konfigurieren und zu aktivieren, ist:

SET GLOBAL long_query_time=0;

SET GLOBAL slow_query_log=ON;

SELECT @@GLOBAL.slow_query_log_file;
+-------------------------------+
| @@GLOBAL.slow_query_log_file  |
+-------------------------------+
| /usr/local/var/mysql/slow.log |
+-------------------------------+

Die Null in der ersten Anweisung, SET GLOBAL long_query_time=0;, führt dazu, dass MySQL jede Abfrage protokolliert. Sei vorsichtig: Auf einem stark ausgelasteten Server kann dies die Festplattenein- und -ausgabe erhöhen und Gigabytes an Speicherplatz verbrauchen. Verwende bei Bedarf einen etwas größeren Wert wie 0.0001 (100 Mikrosekunden) oder 0.001 (1 Millisekunde).

Hinweis

Percona Server und MariaDB Server unterstützen langsames Query Log Sampling: Setze die Systemvariable log_slow_rate_limit so, dass sie jede N-te Abfrage protokolliert. log_slow_rate_limit = 100 protokolliert zum Beispiel jede 100. Abfrage, was 1% aller Abfragen entspricht. In Kombination mit long_query_time = 0 entsteht so im Laufe der Zeit eine repräsentative Stichprobe. Wenn du diese Funktion verwendest, achte darauf, dass das Query Metric Tool das Sampling berücksichtigt, da es sonst zu niedrige Werte meldet.pt-query-digest berücksichtigt das Sampling.

Die letzte Anweisung, SELECT @@GLOBAL.slow_query_log_file;, gibt den Dateinamen für das Protokoll der langsamen Abfrage aus, den du als erstes Befehlszeilenargument für pt-query-digest benötigst. Du kannst diese Variable dynamisch ändern, wenn du in eine andere Datei protokollieren möchtest.

Zweitens: Führe pt-query-digest mit dem Dateinamen des langsamen Abfrageprotokolls als erstem Befehlszeilenargument aus. Das Tool wird eine Menge Ausgaben ausgeben; schau dir aber erst einmal die Profile am Anfang der Ausgabe an:

# Profile
# Rank Query ID                            Response time   Calls
# ==== =================================== =============== =====
#    1 0x95FD3A847023D37C95AADD230F4EB56A  1000.0000 53.8%   452  SELECT tbl
#    2 0xBB15BFCE4C9727175081E1858C60FD0B   500.0000 26.9%    10  SELECT foo bar
#    3 0x66112E536C54CE7170E215C4BFED008C    50.0000  2.7%     5  INSERT tbl
# MISC 0xMISC                               310.0000 16.7%   220  <2 ITEMS>

Die vorangehende Ausgabe ist eine textbasierte Tabelle, in der die langsamsten Abfragen aus dem Protokoll für langsame Abfragen aufgelistet sind. In diesem Beispiel ist SELECT tbl (eine Abfragezusammenfassung) mit 53,8 % der gesamten Ausführungszeit die langsamste Abfrage. (Standardmäßig sortiert pt-query-digest Abfragen nach der prozentualen Ausführungszeit.) Unterhalb des Abfrageprofils wird für jede Abfrage ein Abfragebericht gedruckt.

Erforsche die Ausgabe von pt-query-digest. Das Handbuch dokumentiert die Ausgabe, und im Internet gibt es eine Fülle von Informationen, da das Tool weit verbreitet ist. Schau dir auch Percona Monitoring and Management an: eine umfassende Lösung zur Datenbanküberwachung, die Grafana nutzt, um Abfragemetriken zu melden. Beide Tools sind kostenlos, Open Source und werden von Percona unterstützt.

Durch die Überprüfung langsamer Abfragen weißt du genau, welche Abfragen du optimieren musst, um die Leistung möglichst effizient zu steigern. Noch wichtiger ist, dass du begonnen hast, die Leistung von MySQL wie ein Experte zu trainieren: mit dem Fokus auf Abfragen, denn Leistung ist die Antwortzeit der Abfragen.

1 Latenz ist eine systemimmanente Verzögerung. Die Antwortzeit auf eine Abfrage ist keine MySQL-eigene Verzögerung, sondern setzt sich aus verschiedenen Latenzen zusammen: Netzwerk, Speicherung und so weiter.

2 Es sei denn, STRAIGHT_JOIN wird verwendet - aber das solltest du nicht tun. Lass den MySQL-Abfrageoptimierer die Join-Reihenfolge für den besten Abfrageausführungsplan wählen. Er hat fast immer recht, also vertraue ihm, es sei denn, du kannst ihm das Gegenteil beweisen.

3 Eine vollständige Erklärung der Perzentile findest du unter HackMySQL.

4 P95, P99 und P999 sind üblich. Ich habe noch nie gesehen, dass andere Perzentile in MySQL verwendet werden - abgesehen von Mittelwert (P50) und Maximum (P100).

Get Effiziente MySQL-Leistung now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial