MYSQL Left Join-Optimierung (10 Sekunden bis 20 Millisekunden)

Indem ich die Inhalte aus meiner Arbeit kombiniere, möchte ich den Prozess der Left Jon-Optimierung mit Ihnen teilen, in der Hoffnung, Ihnen neue Ideen zu geben.

【Funktionshintergrund】

Wir müssen die Anzahl der gekauften Waren und die Anzahl der After-Sales-Waren anhand der Benutzerbestellnummer und der Händlernummer zählen. Die beteiligten Tabellen und Beziehungen sind in der folgenden Abbildung dargestellt:

Leider haben die Ingenieure bei der ursprünglichen Gestaltung der Tabellenstruktur weder die Gesamtzahl der gekauften Artikel in der Händlerbestelltabelle aufgezeichnet, noch haben sie die Anzahl der After-Sales-Artikel im After-Sales-Formular der Händlerbestellung aufgezeichnet.

[Rohes SQL]

wählen 
  o.no,s_order.no,sum(s_item.count),sum(nachverkaufsartikel.count)
  aus 
  Käuferbestellung 
  links beitreten seller_order s_order auf o.id = s_order.buyer_order_id 
  links beitreten seller_order_item s_item auf s_order.id = s_item.seller_order_id
  links beitreten seller_order_after_sale after_sale auf s_order.id = after_sale.seller_order_id
  links beitreten seller_order_after_sale_item after_sale_item auf after_sale.id = after_sale_item.after_sale_id
wobei o.add_time >='2019-05-01'
Gruppieren nach
  o.id,s_order.id
Bestellen bis 
  o.id
Grenze 0,10

Die obigen SQL-Anweisungen verwenden Indizes für mehrere Schlüsselfelder.

【Ursprüngliche SQL-Analyse】

Dies ist eine sehr häufige SQL-Anweisung und an der Logik ist nichts auszusetzen.

Diese SQL-Anweisung enthält viele Verbindungsabfragen. Wenn die Anzahl der After-Sales-Bestellungen steigt, steigt auch die Anzahl der verbundenen Daten.

Nachdem Sie alle qualifizierten Daten in den Speicher geladen haben, gruppieren und zählen Sie sie gemäß order.id und s_order.id. Was passiert, wenn 1 Million Daten vorhanden sind? Was würden Sie tun, wenn Sie Code zur Implementierung solcher Statistiken verwenden würden?

Sortieren Sie die gezählten Daten nach der Bestell-ID und nehmen Sie die ersten 10 Daten heraus.

Aus dem obigen SQL können wir erkennen, dass alle Daten, die die Bedingungen erfüllen, in den Speicher geladen, gruppiert, gezählt, sortiert und schließlich paginiert werden müssen. Können wir die Menge der geladenen Daten reduzieren? Ist es möglich, die CPU-Auslastung der Datenbank zu reduzieren? Ist es möglich, zuerst eine kleine Datenmenge abzurufen und dann Statistiken zu erstellen?

Basierend auf den oben genannten Problemen haben wir optimiert

【Analyseschritte】

Als Beobachter verstehen wir zunächst nicht, welche Art von Daten unsere Funktion ausgeben muss. Daher müssen wir verstehen, welche Art von Daten in den einzelnen Tabellen gespeichert sind und in welcher Beziehung sie zueinander stehen.

Wir vergessen, wie das ursprüngliche SQL war, und denken entsprechend der benötigten Daten noch einmal darüber nach, um nicht erneut in den Strudel des ursprünglichen SQL zu geraten.

Als Antwort auf die oben genannten Fragen: Wie kann das Laden der Daten reduziert werden? Ist es möglich, die Daten zuerst zu paginieren und dann separate Statistiken für die paginierten Daten durchzuführen?

Müssen wir also die Gruppierung optimieren? Wir müssen zuerst eine Möglichkeit zur Paginierung finden.

Haben Sie über einige Methoden nachgedacht?

[Optimiertes SQL]

wählen 
  o.id,o.nr,s_bestell.nr,
  (Wählen Sie Summe(sot.count) aus Verkäuferbestellung, also 
    links beitreten seller_order_item sot auf so.id = sot.seller_order_id 
        wobei so.id =s_order.id ),
  (Wählen Sie Summe(osat.count) aus Verkäuferbestellung nach Verkauf osa 
    links beitreten seller_order_after_sale_item osat auf osa.id = osat.after_sale_id
        wobei osa.seller_order_id = s_order.id )
  aus 
  Käuferbestellung 
  links beitreten seller_order s_order auf o.id = s_order.buyer_order_id 
wobei o.addTime >='2019-05-01'
Bestellen bis 
  o.id
Grenze 0,10

【Optimierte SQL-Analyse】

1. Es ist sehr intuitiv festzustellen, dass wir die Gruppierung nach entfernt haben, da die Gruppierung nach order.id, s_order.id tatsächlich nur die Tabellen buyer_order und seller_order verbindet und sie logischerweise auf die gleiche Weise gruppiert sind.
2. Wenn „Gruppieren nach“ nicht verwendet wird, können wir die CPU-Verarbeitung der Datengruppierung reduzieren und nur die Haupttabellendaten verbinden, wodurch die in den Speicher geladenen Daten reduziert werden.
3. Die obigen Vorgänge vervollständigen, was wir zuvor über das Paginieren der Daten gesagt haben. Wir haben 10 Daten abgerufen.
4. Als nächstes zählen wir die Anzahl der verkauften Waren und die Anzahl der Nachverkäufe für die 10 Datenelemente.
5. Zu diesem Zeitpunkt stellte jeder fest, dass wir tatsächlich nur die 10 ausgelagerten Datenelemente gezählt hatten, aber es stellte sich heraus, dass wir alle Daten gruppieren und dann 10 Elemente für die Statistik nehmen sollten. Es zeigt sich, dass dieser Vorgang den statistischen Aufwand für die Datenverarbeitung erheblich reduziert. Wir müssen nur die Daten erheben, die wir brauchen.

Die Wirkung der oben genannten Optimierung geht möglicherweise weit über Ihre Vorstellungskraft hinaus.

In der Praxis ist die Anzahl der verknüpften Tabellen größer als in unserem Beispiel. Wenn das nicht optimierte SQL ohne Paginierung ausgeführt wird, stellt sich heraus, dass insgesamt 700.000 Daten vorhanden sind. Wir haben mehr als 10 Sekunden gebraucht, um 10 Daten zu paginieren und abzurufen. Die Datenmenge ist nicht groß, aber die meiste Zeit wird für die Gruppierung und Datenstatistik benötigt. Sie können versuchen, ein Stück Code zu schreiben, um diese Daten zu gruppieren und zu zählen, um die Komplexität zu verstehen.

Unabhängig davon, ob Sie 10 oder alle herausnehmen, ist die erforderliche Zeit im Grunde gleich (IO nicht berücksichtigt), da zuerst die Statistiken erstellt werden.

Nach der Optimierung werden nur etwa 20.000 Daten in den Speicher geladen und es werden keine Statistiken durchgeführt. Zuerst werden 10 Daten entnommen und dann werden Statistiken für diese 10 Daten durchgeführt. Die Logik ist viel einfacher als zuvor. Die optimierte SQL-Ausführungszeit liegt innerhalb von 20 Millisekunden.

Wenn die entsprechenden Mengen sowohl in der Bestelltabelle als auch in der Kundendiensttabelle erfasst werden, ist die Anzahl der Tabellen sogar noch geringer und es ist keine Unterabfrage erforderlich. Manchmal müssen Sie beim Entwerfen einer Tabelle dennoch statistische Anforderungen berücksichtigen.

Dies ist das Ende dieses Artikels über die MYSQL Left Join-Optimierung (Optimierung von 10 Sekunden auf 20 Millisekunden). Weitere relevante Inhalte zur MYSQL Left Join-Optimierung finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!