Die Beziehung zwischen MySQL und Indizes existiert nicht

In einigen Geschäftsszenarien wird die NOT EXISTS-Anweisung verwendet, um sicherzustellen, dass die zurückgegebenen Daten nicht in einer bestimmten Sammlung vorhanden sind. Einige Kollegen werden feststellen, dass die Leistung von NOT EXISTS in einigen Szenarios schlecht ist, und das ist der Fall sogar Gerüchte online, dass „NOT EXISTS keine Indizierung verwendet “, wie optimieren wir die NOT EXISTS-Anweisung?

Nehmen Sie das heutige optimierte SQL als Beispiel:

SELECT count(1) FROM t_monitor m WHERE NOT exists (  SELECT 1   FROM t_alarm_realtime AS a   WHERE a.resource_id=m.resource_id   AND a.resource_type=m.resource_type   AND a.monitor_name=m.monitor_name)

Wir verwenden die LEFT JOIN-Methode zur Optimierung. Der optimierte SQL ist:

SELECT count(1) FROM t_monitor m LEFT JOIN t_alarm_realtime AS a    ON a.resource_id=m.resource_id   AND a.resource_type=m.resource_type   AND a.monitor_name=m.monitor_name WHERE a.resource_id is NULL

Die Ausführungszeit vor der Optimierung beträgt mehr als 29 Sekunden, Optimierung Nach 1,2 Sekunden erhöhte sich die Optimierung um das 25-fache.

NOT EXISTS wirklich nicht indiziert?

Sehen Sie sich die Ausführungspläne von zwei SQLs an!

Ausführungsplan mit der NOT EXIST-Methode:

Ausführungsplan mit der LEFT JOIN-Methode:

Aus dem Ausführungsplan verwenden beide Tabellen Indizes, der Unterschied besteht darin, dass NOT EXISTS die „

DEPENDENT SUBQUERY

“-Methode, während LEFT JOIN die gewöhnliche Tabellenzuordnungsmethode verwendet. Empfehlenswerte Lektüre: Warum können Indizes die Abfragegeschwindigkeit verbessern?

Sehen Sie sich den Ausführungsprozess der beiden Methoden über die von MySQL bereitgestellte Profiling-Methode an.

Ausführungsprozess mit der NOT EXIST-Methode:

Ausführungsprozess mit der LEFT JOIN-Methode:

Aus Sicht des Ausführungsprozesses wird die LEFT JOIN-Methode hauptsächlich im Sendedatenelement (1,2 s) verbraucht, während die NOT EXISTS-Methode hauptsächlich verbraucht wird Beim Ausführen und Senden von Daten ist die Profilerstellung auf die Speicherung von nur 100 Datensatzzeilen beschränkt.

Aus der Profilerstellung können wir nur 47 Kombinationen von „Daten ausführen und senden“ sehen (jede Kombination beträgt etwa 50 us). Aus dem Ausführungsplan können wir ersehen, dass das Datenvolumen des externen t_monitors 578436 Zeilen beträgt, wobei die ungenauen Statistiken ignoriert werden Nachfolgend finden Sie unter Verwendung der NOT EXISTS-Methode 578436 Kombinationen von „Daten ausführen und senden“ mit einer Gesamtverbrauchszeit von =50μs*578436=28921800us=28,92s.

Aus dem obigen Ausführungsprozess lässt sich Folgendes ableiten:

Die Ausführungsleistung mit der NOT EXISTS-Methode hängt stark von der Anzahl der Ausführungen der NOT EXISTS-Unterabfrage ab, also der Datenmenge im äußeren Abfrageergebnis Satz.

1. Wenn das Datenvolumen N der äußeren Abfrageergebnismenge klein ist, ist die Ausführungsleistung besser. Wenn N = 10, beträgt die Ausführungszeit 50 μs * 10 = 500 us = 0,005 s, plus etwas zusätzlichen Verbrauch. Das Ausführungsergebnis kann auch im Bereich von 0,01 Sekunden oder 10 Millisekunden liegen, was für die meisten Anwendungen akzeptabel sein sollte.

2. Wenn das Datenvolumen N der äußeren Chengxun-Ergebnismenge groß ist oder sogar mehrere zehn Millionen Daten beträgt, wird die Abfrageleistung von NOT EXISTS sehr schlecht und verbraucht sogar viele Server-E/A- und CPU-Ressourcen. Auswirkungen auf andere Geschäfte laufen normal.

Zusätzlich zu den oben genannten Problemen wurde während des Optimierungsprozesses festgestellt, dass die Spalte „resource_id“, die dieselben Daten speichern sollte, in den beiden Tabellen unterschiedlich definiert ist, eine Tabelle ist VARCHAR und die andere Tabelle ist BIGINT, Der Feldtyp des externen Ergebnissatzes. Die verschiedenen Feldtypen in der Worttabelle NOT EXIST verhindern die Verwendung von Indizes in der Unterabfrage NOT EXISTS, was zu einer schlechten Unterabfrageleistung führt und letztendlich die Ausführungsleistung der gesamten Abfrage beeinträchtigt.

Jingdong Mall hat auch eine große Anzahl ähnlicher Fälle gesehen, um Bestellnummern zu speichern, während andere Tabellen BIGINT zum Speichern verwenden. Ich hoffe, dass die Leistung der beiden Tabellen sehr schlecht ist Betrachten Sie dies als Warnung. Folgen Sie dem öffentlichen Konto Java Technology Stack und antworten Sie auf m36, um eine Kopie der MySQL-Militärvorschriften für Forschung und Entwicklung zu erhalten.

Verwandte Lernempfehlungen: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonDie Beziehung zwischen MySQL und Indizes existiert nicht. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!