Wie optimiert MySQL die Unterabfrage?

In der tatsächlichen Entwicklung verwenden wir häufig die In-Unterabfrage, die einer Reihe von Werten entspricht, die mit bestimmten Feldern übereinstimmen, sodass wir bequemer filtern und abfragen können. Diese Unterabfragemethode führt jedoch zu großen Leistungsproblemen, wenn die Datenmenge groß ist. In diesem Artikel wird vorgestellt, wie MySQL die In-Unterabfrage optimiert.

1. Vermeiden Sie die Verwendung in Unterabfragen

In tatsächlichen Projekten sehen wir häufig diese Schreibweise:

SELECT *
FROM table
WHERE col1 IN (SELECT col1 FROM table2 WHERE condition);

Diese Anweisung ist die einfachste in Unterabfrage, die Werte mehrerer Zeilen von col1 herausgenommen , stimmt mit dem Wert in der Tabelle überein und gibt die passende Zeile zurück. Das Schreiben auf diese Weise führt jedoch zu einem Leistungsengpass, da die Art und Weise, wie MySQL die In-Unterabfrage intern ausführt, die Ergebnismenge der Unterabfrage im Speicher (oder auf der Festplatte) zwischenspeichert Festplatte) zwischengespeichert wird, führt dies zu einer großen Anzahl von E/A-Vorgängen, und wenn die Ergebnismenge der Unterabfrage groß ist, wird auch viel Speicher belegt.

Vermeiden Sie daher die Verwendung in Unterabfragen in der tatsächlichen Entwicklung und können Sie stattdessen Join verwenden.

2. Verwenden Sie Join anstelle einer In-Unterabfrage. Es gibt keinen Unterschied zwischen dem Schreiben einer Unterabfrage und der Art und Weise, wie eine Unterabfrage geschrieben wird, um die SQL-Syntax zu optimieren. und die Ausführungseffizienz ist viel höher als bei Unterabfragen. Suchen Sie Spalte1 und verknüpfen Sie sie dann mit Spalte1 in Tabelle1, wie unten gezeigt:

SELECT table.*
FROM table
JOIN table2 ON table.col1 = table2.col1
WHERE table2.condition;

Im Vergleich zur In-Unterabfrage kann die Verwendung von Join die Ergebnismenge der Unterabfrage mit der Tabelle verbinden, wodurch viel Speicher (Festplatte) beim Lesen reduziert wird. Get the Betrieb.

3. Die Verwendung von „exists“ anstelle von „in subquery“ bedeutet tatsächlich die Verwendung von „join“. Im Gegensatz zur Unterabfrage „in“ muss die Unterabfrage „exists“ nur eine einfache Beurteilung durchführen, unabhängig von der Größe der Ergebnismenge. Das Folgende ist ein Syntaxbeispiel für die Unterabfrage „exists“:

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition);

Die Verwendung von „exists“ anstelle der Unterabfrage „in“ hat die Effizienz erheblich verbessert und kann eine Menge E/A- und Speicherverbrauch einsparen.

4. Verwenden Sie den Index, um die In-Anweisung zu optimieren.

Wenn Sie den Index verwenden können, um die In-Unterabfrage während der Abfrage zu beschleunigen, wird auch die Abfrageeffizienz erheblich verbessert. MySQL-Indizes werden in drei Typen unterteilt: Primärschlüsselindex, eindeutiger Index und gewöhnlicher Index. Wenn Sie einen geeigneten Index erstellen können, können Sie verhindern, dass MySQL einen vollständigen Tabellenscan durchführt, und die Abfrageeffizienz verbessern.

CREATE INDEX idx_col1 ON table (col1);

Wenn der Wert von col1 groß ist, wird die Verwendung des Index die Abfrageeffizienz erheblich optimieren und Effizienzprobleme reduzieren, die durch die Verwendung in Unterabfragen verursacht werden.

5. Verwenden Sie „Limit“ und „Existent“, um die Unterabfrage zu optimieren .

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition LIMIT 1000, 20);

Die Funktion dieser SQL-Anweisung besteht darin, die Ergebnismenge von Tabelle2 zu finden und dann mithilfe von Spalte1 und Tabelle „existiert“ auszuführen, die Abfrageergebnismenge auf 20 zu begrenzen und ab der 1000. Zeile abzufragen.

6. Angemessene Verwendung der Speicheroptimierung in der Anweisung

Wenn das in der Abfrage verwendete Unterabfrageergebnis nicht viele Zeilen enthält, können wir set anstelle von in verwenden. set speichert die Ergebnismenge der In-Unterabfrage im Speicher für den nachfolgenden Abfrageabgleich. Durch die Verwendung von Speicher zur Optimierung der In-Anweisung kann die Leistung ebenfalls erheblich verbessert werden.

SET @col1 = (SELECT GROUP_CONCAT(DISTINCT col1) FROM table2 WHERE condition);
SELECT *
FROM table
WHERE FIND_IN_SET(table.col1, @col1);

Diese Anweisung verwendet zunächst select für den Datenabgleich und verwendet dann GROUP_CONCAT, um die Werteliste von col1 zu einer Zeichenfolge zu verbinden, die in @col1 gespeichert wird. In nachfolgenden Abfragen wird FIND_IN_SET für den Abgleich verwendet und Speicher-Caching wird verwendet, um die Abfrageeffizienz zu optimieren.

7. Zusammenfassung: Vermeiden Sie bei der Verwendung von Unterabfragen unbedingt vollständige Tabellenscans, insbesondere wenn die Datenmenge groß ist, da dies sonst zu ernsthaften Leistungsproblemen führt. Durch das Verknüpfen, Existieren, Optimieren von Indizes, die entsprechende Verwendung von Limits, die Verwendung von Speicher und anderen Methoden können Sie die Abfrageeffizienz verbessern und die Leistung von Unterabfragen optimieren. In tatsächlichen Projekten sollten wir die beste Lösung entsprechend der jeweiligen Situation auswählen, um den besten Leistungsoptimierungseffekt zu erzielen.

Das obige ist der detaillierte Inhalt vonWie optimiert MySQL die Unterabfrage?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!