Methode zur Optimierung von MySQL-Gruppenabfragen

MySQL behandelt GROUP BY- und DISTINCT-Abfragen in den meisten Fällen ähnlich; tatsächlich wechselt es während der Optimierung manchmal zwischen beiden. Beide Abfragetypen können von der Indizierung profitieren und dies ist normalerweise die wichtigste Möglichkeit, sie zu optimieren.

Wenn ein Index nicht verwendet werden kann, bietet MySQL zwei Strategien für GROUP BY-Abfragen: die Verwendung einer temporären Tabelle oder eine Dateisortierung zum Gruppieren. Für eine bestimmte Abfrage kann keiner der Ansätze effizienter sein. Wir können SQL_BIG_RESULT und SQL_SMALL_RESULT konfigurieren, um den Optimierer anzugeben, der eine dieser Methoden auswählen soll.

Normalerweise ist es effizienter, die Abfragetabelle nach ID statt nach Wert zu gruppieren. Die folgende Abfrage ist beispielsweise ineffizient:

SELECT Schauspieler.Vorname, Schauspieler.Nachname, COUNT(*)
VON sakila.file_actor
INNER JOIN sakila.actor USING(Schauspieler-ID)
GRUPPE NACH Schauspieler.Vorname, Schauspieler.Nachname;

Die folgende Abfrage ist effizienter:

SELECT Schauspieler.Vorname, Schauspieler.Nachname, COUNT(*)
VON sakila.file_actor
INNER JOIN sakila.actor USING(Schauspieler-ID)
GRUPPE NACH film_actor.actor_id;

Die Verwendung von actor.actor_id zur Gruppierung ist effizienter als film_actor.actor_id.

Diese Abfrage ist von Vorteil, da der Name des Schauspielers von der Schauspieler-ID abhängig ist und daher dieselben Ergebnisse zurückgibt. Dies wäre jedoch nicht der Fall, wenn die Ergebnisse unterschiedlich wären. Manchmal deaktiviert der Server GROUP BY sogar über die SQL_MODE-Konfiguration. Wenn Sie sich zu diesem Zeitpunkt nicht für den erhaltenen Wert interessieren und der Wert der zur Gruppierung verwendeten Spalte eindeutig ist, können Sie dieses Problem mit MIN und MAX lösen.

SELECT MIN(Schauspieler.Vorname), MAX(Schauspieler.Nachname), ...;

Perfektionisten werden denken, dass Ihre Gruppierung falsch und sie richtig sind. Eine Konsequenz eines Dummy-MIN- oder -MAX-Werts besteht darin, dass die Abfrage nicht korrekt zusammengesetzt wird. Manchmal möchten Sie jedoch einfach, dass MySQL Abfragen schneller ausführt. Ein Perfektionist wäre mit der folgenden Abfrage zufrieden:

SELECT Schauspieler.Vorname, Schauspieler.Nachname, c.cnt
VON sakila.actor
	Innerer Join (
    Wählen Sie Schauspieler-ID, COUNT(*) AS cnt
    VON sakila.film_actor
    GRUPPE NACH Schauspieler-ID
  ) ALS c USING(Schauspieler-ID);

Das Erstellen und Auffüllen einer temporären Tabelle in einer Unterabfrage kann jedoch aufwändiger sein, als es theoretisch scheint. Es ist wichtig zu bedenken, dass die durch die Unterabfrage erstellte temporäre Tabelle keinen Index hat, was zu einer Leistungsverschlechterung führt.

Bei gruppierten Abfragen ist es im Allgemeinen keine gute Idee, Spalten auszuwählen, die nicht gruppiert sind. Dies liegt daran, dass die Abfrageergebnisse unsicher sind. Sobald der Index geändert wird oder der Optimierer eine andere Strategie verwendet, ändern sich die Ergebnisse. Tatsächlich empfehlen wir, den SQL_MODE des Servers auf ONLY_FULL_GROUP_BY zu setzen, sodass das System beim Schreiben einer falsch geschriebenen Group-By-Abfrage einen Fehler generiert, anstatt sie direkt auszuführen. Nachdem ONLY_FULL_GROUP_BY aktiviert wurde, können die Felder von SELECT nur die von GROUP BY angegebenen Felder sein. In diesem Fall können Sie eine schrittweise Abfrage oder eine Unterabfrage erstellen, um zuerst die gruppierten Spalten zu finden und dann eine sekundäre Abfrage auszuführen.

MySQL gruppiert automatisch entsprechend der Reihenfolge der in GROUP BY angegebenen Spalten, sofern nicht ORDER BY zum Angeben einer Sortierregel verwendet wird. Wenn Ihnen die Reihenfolge egal ist und Sie feststellen, dass dies zu einer Dateisortierung führt, können Sie die automatische Sortierung mit ORDER BY NULL überspringen. Sie können auch angeben, dass die Ergebnisse in eine bestimmte Richtung sortiert werden sollen, indem Sie nach GROUP BY DESC oder ASC hinzufügen.

Manchmal können Sie MySQL beim Gruppieren von Abfragen auffordern, eine Superaggregation der Ergebnisse durchzuführen. Dies kann durch Hinzufügen der Klausel WITH ROLLUP nach GROUP BY erreicht werden, dadurch wird jedoch möglicherweise nicht die gewünschte Optimierung erreicht. Sie können die Ausführungsmethode über EXPLAIN überprüfen und darauf achten, ob die Gruppierung über Filesort oder temporäre Tabellen erfolgt. Vergleichen Sie dann dieselben Abfragen, nachdem Sie WITH ROLLUP entfernt haben. Durch Vergleiche können wir möglicherweise Optimierungsmöglichkeiten finden.

Manchmal ist es effizienter, eine aggregierte Abfrage hinzuzufügen, obwohl diese Methode mehr Zeilen zurückgibt. Sie können Unterabfragen auch nach FROM verschachteln, um Zwischenergebnisse der Abfrage beizubehalten, und dann UNION verwenden, um das Endergebnis zu erhalten.

Beachten Sie jedoch, dass Sie WITH ROLLUP am besten aus Ihrer Anwendung entfernen und gruppierte Abfragen durch Optimierung durchführen.

Fazit: Wenn Sie GROUP BY für gruppierte Abfragen verwenden, sollten Sie am besten Indexspalten zur Gruppierung verwenden. Wenn Sie die Reihenfolge nicht angeben müssen, können Sie zur Optimierung ORDER BY NULL verwenden. Wenn Sie nicht nach Indexspalten gruppieren, müssen Sie Alternativen in Betracht ziehen und überlegen, ob Sie Unterabfragen verwenden oder WITH ROLLUP verwenden, um die Leistung vor der Optimierung zu prüfen. Um gleichzeitig unerwartete Fehler bei Gruppenabfragen zu vermeiden, ist es am besten, ONLY_FULL_GROUP_BY zu aktivieren.

Oben sind die Details der Optimierungsmethode für MySQL-Gruppenabfragen aufgeführt. Weitere Informationen zur Optimierung von MySQL-Gruppenabfragen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!