MySQL-Abfragen verstehen mit Explain

Sie befinden sich in Ihrem neuen Job als Datenbankadministrator oder Dateningenieur und haben sich gerade verirrt, um herauszufinden, was diese wahnsinnig aussehenden Abfragen bedeuten und tun sollen. Warum gibt es 5 Joins und warum wird eine ORDER BY in einer Unterabfrage verwendet, bevor einer der Joins überhaupt stattfindet? Denken Sie daran, dass Sie aus einem bestimmten Grund eingestellt wurden – höchstwahrscheinlich hat dieser Grund auch mit vielen komplizierten Abfragen zu tun, die im letzten Jahrzehnt erstellt und bearbeitet wurden.

article cover

Das Schlüsselwort EXPLAIN wird in verschiedenen SQL-Datenbanken verwendet und gibt Auskunft darüber, wie Ihre SQL-Datenbank eine Abfrage ausführt. In MySQL kann EXPLAIN vor einer Abfrage verwendet werden, die mit SELECTINSERTDELETEREPLACE und UPDATE. Für eine einfache Abfrage würde es wie folgt aussehen:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

Anstelle der üblichen Ergebnisausgabe würde MySQL dann seinen Anweisungsausführungsplan anzeigen, indem erklärt wird, welche Prozesse bei der Ausführung der Anweisung in welcher Reihenfolge ablaufen.

Hinweis: Wenn EXPLAIN für Sie nicht funktioniert, verfügt Ihr Datenbankbenutzer möglicherweise nicht über die Berechtigung SELECT für die Tabellen oder Ansichten, die Sie in Ihrer Anweisung verwenden.

EXPLAIN ist ein großartiges Werkzeug, um langsame Abfragen schnell zu beheben. Während es Ihnen sicherlich helfen kann, wird es nicht die Notwendigkeit für strukturelles Denken und einen guten Überblick über die vorhandenen Datenmodelle beseitigen. Häufig besteht die einfachste Lösung und der schnellste Rat darin, den betreffenden Spalten einer bestimmten Tabelle einen Index hinzuzufügen, wenn sie in vielen Abfragen mit Leistungsproblemen verwendet werden. Beachten Sie jedoch, dass Sie nicht zu viele Indizes verwenden, da dies kontraproduktiv sein kann. Das Lesen des Index und der Tabelle ist nur dann sinnvoll, wenn die Tabelle eine erhebliche Anzahl von Zeilen enthält und Sie nur wenige Datenpunkte benötigen. Wenn Sie eine große Ergebnismenge aus einer Tabelle abrufen und häufig verschiedene Spalten abfragen, ist ein Index für jede Spalte nicht sinnvoll und behindert die Leistung mehr als es hilft. Weitere Informationen zu den tatsächlichen Berechnungen von index vs no Index finden Sie in der offiziellen MySQL-Dokumentation.

Die Dinge, die Sie vermeiden möchten, wo immer dies möglich und anwendbar ist, sind Sortierung und Berechnungen innerhalb von Abfragen. Wenn Sie der Meinung sind, dass Sie Berechnungen in Ihren Abfragen nicht vermeiden können: Ja, das können Sie. Schreiben Sie die Ergebnismenge an eine andere Stelle und berechnen Sie Ihren Datenpunkt außerhalb der Abfrage. Stellen Sie einfach sicher, dass Sie dokumentieren, warum Sie in Ihrer Anwendung berechnen, anstatt sofort ein Ergebnis in SQL zu erhalten. Andernfalls wird der nächste Datenbankadministrator oder Entwickler kommen und die glorreiche Idee haben, eine Berechnung innerhalb der Abfrage in der Art von „Oh schau, mein Vorgänger wusste nicht einmal, dass du das in SQL machen kannst!“ Einige Entwicklerteams, die noch nicht die unvermeidlichen Probleme hatten, Datenbanken zu löschen, verwenden möglicherweise In-Query-Berechnungen für Zahlenunterschiede zwischen Datumsangaben oder ähnlichen Datenpunkten.

Die allgemeine Faustregel für SQL-Abfragen lautet wie folgt:

Seien Sie präzise und generieren Sie nur die Ergebnisse, die Sie benötigen.

Schauen wir uns eine etwas kompliziertere Abfrage an …

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)

…und seine EXPLAIN Ausgabe.

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)

Die Spalten in der EXPLAIN Ausgabe mit denen, die besondere Aufmerksamkeit benötigen, um Probleme in Fettdruck zu identifizieren, sind:

id (Abfrage-ID)
select_type (Typ der Anweisung)
table (Tabelle, auf die verwiesen wird)
type (Join-Typ)
possible_keys (welche Schlüssel hätten verwendet werden können)
key (Schlüssel, der verwendet wurde)
key_len (Länge des verwendeten Schlüssels)
ref (Spalten im Vergleich zum Index)
rows (Anzahl der gesuchten Zeilen)
Extra (zusätzliche Informationen)

Je höher die Anzahl der gesuchten Zeilen ist, desto besser muss der Optimierungsgrad in Bezug auf Indizes und Abfragegenauigkeit sein, um die Leistung zu maximieren. In der zusätzlichen Spalte werden mögliche Aktionen angezeigt, auf die Sie sich konzentrieren können, um Ihre Abfrage gegebenenfalls zu verbessern.

Show Warnings;

Wenn die Abfrage, die Sie mit EXPLAIN verwendet haben, nicht korrekt analysiert wird, können Sie SHOW WARNINGS; in Ihren MySQL-Abfrageeditor eingeben, um Informationen über die letzte Anweisung anzuzeigen, die ausgeführt wurde und nicht diagnostiziert wurde, dh es werden keine Informationen für Anweisungen wie SHOW FULL PROCESSLIST;. Es kann zwar keinen ordnungsgemäßen Abfrageausführungsplan wie EXPLAIN geben, gibt Ihnen jedoch möglicherweise Hinweise zu den Abfragefragmenten, die es verarbeiten könnte. Angenommen, wir verwenden die Abfrage EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; für eine bestimmte Datenbank, die eigentlich keine Tabelle foo . Die MySQL-Ausgabe wäre:

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

Wenn wir SHOW WARNINGS; eingeben, lautet die Ausgabe wie folgt:

+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)

Versuchen wir dies mit einem absichtlichen Syntaxfehler.

EXPLAIN SELECT * FROM foo WHERE name = ///;

Dies erzeugt die folgenden Warnungen:

> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+

Diese Abfrageausgabe ist ziemlich einfach und wird von MySQL sofort als Ergebnisausgabe angezeigt, aber für kompliziertere Abfragen, die nicht analysiert werden, ist es immer noch möglich, einen Blick darauf zu werfen, was in den Abfragefragmenten passiert, die analysiert werden können. SHOW WARNINGS; enthält spezielle Marker, die nützliche Informationen liefern können, wie zum Beispiel:

<index_lookup>(query fragment): Eine Indexsuche würde passieren, wenn die Abfrage richtig analysiert worden wäre
<if>(condition, expr1, expr2): in diesem speziellen Teil der Abfrage tritt eine if-Bedingung auf
<primary_index_lookup>(query fragment): Eine Indexsuche würde über den Primärschlüssel erfolgen
<temporary table>: Hier würde eine interne Tabelle zum Speichern temporärer Ergebnisse erstellt, z. B. in Unterabfragen vor Joins

Um mehr über diese speziellen Marker zu erfahren, lesen Sie Extended Explain Output Format in der offiziellen MySQL-Dokumentation.

Die langfristige Lösung

Es gibt mehrere Möglichkeiten, die Ursache für eine schlechte Datenbankleistung zu beheben. Der erste zu betrachtende Punkt ist das Datenmodell, dh. wie sind die Daten strukturiert und verwenden Sie die richtige Datenbank? Für viele Produkte ist eine SQL-Datenbank in Ordnung. Eine wichtige Sache, an die Sie sich erinnern sollten, ist, die Zugriffsprotokolle immer von der regulären Produktionsdatenbank zu trennen, was leider in vielen Unternehmen nicht der Fall ist. Meistens hat ein Unternehmen in diesen Fällen klein angefangen, ist größer geworden und verwendet im Wesentlichen immer noch dieselbe Datenbank, was bedeutet, dass es sowohl für die Protokollierungsfunktion als auch für andere Transaktionen auf dieselbe Datenbank zugreift. Dies reduziert die Gesamtleistung erheblich, insbesondere wenn das Unternehmen größer wird. Daher ist es sehr wichtig, ein Datenmodell zu erstellen, das passt und nachhaltig ist.

Datenmodell

Wenn Sie ein Datenmodell auswählen, wird höchstwahrscheinlich auch die richtige Form der Datenbank (en) angezeigt. Wenn Sie nahezu in Echtzeit Zahlen für Zugriffsprotokolle anzeigen müssen, möchten Sie höchstwahrscheinlich ein hochperformantes Data Warehouse, während reguläre Transaktionen möglicherweise über eine SQL-Datenbank erfolgen, und Sie haben möglicherweise eine Diagrammdatenbank, in der die relevanten Datenpunkte beider Datenbanken ebenfalls in einer Empfehlungs-Engine gesammelt werden.

Die Softwarearchitektur des Gesamtprodukts ist genauso wichtig wie die Datenbank selbst, da schlechtes Design hier zu Engpässen führt, die in Richtung Datenbank gehen und alles sowohl von der Softwareseite als auch von dem, was die Datenbank ausgeben kann, verlangsamen. Sie müssen entscheiden, ob Container für Ihr Produkt geeignet sind, ob ein Monolith der bessere Weg ist, um Dinge zu handhaben, ob Sie einen Core-Monolith mit mehreren Microservices haben möchten, die auf andere Funktionen abzielen, die an anderer Stelle verteilt sind, und wie Sie darauf zugreifen, sammeln, verarbeiten und Speichern von Daten.

Hardware

Ebenso wichtig wie Ihre allgemeine Struktur ist Ihre Hardware eine Schlüsselkomponente für Ihre Datenbankleistung. Exoscale bietet Ihnen verschiedene Instanzoptionen, die Sie je nach Transaktions- und Speichervolumen sowie gewünschter Antwortzeit nutzen können.

Es ist wichtig, die Spitzenzeiten Ihrer Anwendung zu bestimmen und daher zu wissen, wann Sie die langsameren administrativen Abfragen nach Möglichkeit weglassen sollten. Datenträger-E / A- und Netzwerkstatistiken müssen ebenfalls berücksichtigt werden, wenn Sie das Timing Ihrer Datenbanktransaktionen und -analysen entwerfen.

Zusammenfassung

Zusammenfassend sind hier die wichtigsten Punkte für die langfristige Performance zusammengefasst:

Erstellen Sie ein nachhaltiges Datenmodell, das den Anforderungen Ihres Unternehmens entspricht
Wählen Sie die richtige Form der Datenbank
Verwenden Sie eine Softwarearchitektur, die zu Ihrem Produkt passt
Überprüfen Sie regelmäßig die Struktur Ihrer Abfragen und verwenden Sie EXPLAIN Optimieren Sie bei den komplizierteren die Nutzung Ihrer ausgewählten Datenbank (en), auch im Hinblick auf Datenbankaktualisierungen und deren Auswirkungen auf Sie
Wählen Sie die Instanzen aus, die Ihren Anwendungs- und Datenbankanforderungen am besten entsprechen, je nach Leistung und Bandbreite

Maybaygiare.org