Optimieren der langsamen Abfrage von MySQL-Aggregatstatistikdaten

Vorne geschrieben

Wenn wir in unserem täglichen Leben mit Datenbanken arbeiten, beispielsweise mit Bestelltabellen, Zugriffsdatensatztabellen und Produkttabellen.

Wir beschäftigen uns häufig mit statistischen Problemen wie der Berechnung der Summe von Datenspalten und der Anzahl von Datenzeilen.

Mit der Entwicklung des Geschäfts werden diese Tabellen immer größer. Wenn dies nicht richtig gehandhabt wird, wird die Geschwindigkeit der Statistikabfrage immer langsamer, bis das Geschäft dies nicht mehr tolerieren kann.

Daher müssen wir zunächst diese Szenario-Wissenspunkte verstehen und darüber nachdenken und zu Beginn des Entwurfs etwas Optimierungsspielraum reservieren, um die Geschäftsentwicklung zu unterstützen.

SQL-Aggregatfunktion

In MySQL und anderen Daten werden Aggregatfunktionen unterstützt, um die Datenberechnung zu erleichtern.

Die folgenden Methoden sind üblich

Nehmen Sie den Durchschnittswert AVG()
SUMME()
Maximalwert MAX()
Minimalwert MIN()
ZÄHLEN()

Demonstrieren Sie mehrere einfache SQL-Anweisungen:

Abfrage der Gesamtzahl der Bestellungen mit u_id 100

Wählen Sie Anzahl(ID) aus Bestellungen, bei denen u_id = 100 ist;

Abfrage des Gesamtverbrauchs von Bestellungen mit u_id 100

Wählen Sie die Summe (Bestellbetrag) aus den Bestellungen, bei denen u_id = 100 ist.

Finden Sie die meistverkauften Produkte

Wählen Sie max(sell_num) aus Waren

Statistik zur Anzahl und Gesamtsumme der Bestellungen im Juli

Wählen Sie Anzahl(ID) als Anzahl, Summe(Bestellbetrag) als Gesamtbetrag 
aus Bestellungen, bei denen das Bestelldatum zwischen 20190701 und 20190731 liegt und is_pay = 1

Wenn zu diesem Zeitpunkt die Gesamtzahl der Bestelltabellen 100 Millionen beträgt. Und diese SQL-Anweisung wird sehr langsam ausgeführt. Wie können wir das Problem beheben und es optimieren?

Einige Studenten sagen vielleicht: Es gibt viele Zeilen, also fügen Sie einen Index für das Datumsfeld hinzu, damit die Überprüfung sehr schnell geht.

Die Gesamtzahl beträgt 100 Millionen. Angenommen, es gibt im Juli 10 Millionen Bestellungen, dann wird sich die Screening-Geschwindigkeit nach dem Hinzufügen des Indexes natürlich deutlich erhöhen. Aber ist unser Problem an diesem Punkt wirklich gelöst?

Bei dieser Art von Aggregatfunktion muss das Ergebnis durch Durchlaufen jedes einzelnen Datenelements berechnet werden. Wenn wir beispielsweise die Gesamtzahl der Bestellungen zählen möchten, müssen wir den Bestellbetrag jeder Zeile lesen und dann addieren.

Das heißt, in diesem statistischen SQL müssen Sie zuerst 10 Millionen Daten aus 100 Millionen Daten filtern und diese Daten dann zur Berechnung durchlaufen. Zu diesem Zeitpunkt wird es sehr langsam sein.

Das Hinzufügen von Indizes löst nicht das Problem langsamer Aggregatfunktionsstatistiken

Optimierung der Aggregationsstatistiklösung

Budget im Voraus

Erstellen Sie eine nach Datum getrennte statistische Datentabelle, z. B.: Wie viele Bestellungen wurden am 01.08.2019 verkauft, die Menge und andere Daten.
Bei der Generierung einer Bestellung (die Daten können nach Zahlungseingang gezählt werden) werden Betrag und Menge zum entsprechenden Datum in die Statistikdatentabelle eingetragen.

Es ist zu beachten, dass bei Szenarien wie Rückerstattungen, die sich auf die Reduzierung der Daten auswirken, diese entsprechend behandelt werden müssen

Wenn wir die Daten für August zählen müssen, müssen wir nur die etwa dreißig Datenzeilen für diesen Monat durchlaufen und berechnen.

Zeitgesteuerte Landung

Wir können Easyswoole, geplante Aufgaben usw. verwenden. Um die Gesamtsumme zu einem festgelegten Zeitpunkt (beispielsweise alle 20 Minuten) zu berechnen und sie dann in die statistische Datentabelle zu aktualisieren.

Vorteile: Es muss weniger verarbeitet werden und es müssen keine Änderungen an der API vorgenommen werden, z. B. bei Rückerstattungsvorgängen. Es müssen lediglich die Daten der ursprünglichen Bestelltabelle verwendet und die statistischen Daten regelmäßig gezählt und aktualisiert werden.

Es ist zu beachten, dass je nach Beliebtheit der einzelnen Bestellungen unterschiedliche Lieferfrequenzen festgelegt werden sollten. Wenn beispielsweise die Wahrscheinlichkeit einer Datenänderung innerhalb einer Woche relativ groß ist, kann die Lieferung innerhalb von 20 Minuten erfolgen. Es ist unwahrscheinlich, dass sich die Daten von vor einem Jahr ändern. Sie können sie daher einmal täglich synchronisieren oder, wenn Sie sicher sind, dass sie sich nicht ändern, auch nicht aktualisieren.

Zusammenfassen

Indizes können das Problem langsamer SQL-Anweisungen zur statistischen Aggregation von Daten nicht lösen

Aggregatfunktionen sollten mit Vorsicht oder gar nicht verwendet werden, da wir nicht abschätzen können, wie viele Datenzeilen wir scannen müssen, um das zukünftige Datenvolumen zu berechnen.

Der Optimierungsplan ist untrennbar mit der Statistiktabelle verbunden, und die in einem bestimmten Zeitraum berechneten statistischen Daten müssen gespeichert werden.

Dies ist das Ende dieses Artikels über die langsame Abfrage aggregierter statistischer MySQL-Daten. Weitere relevante Inhalte zur langsamen Abfrage aggregierter statistischer MySQL-Daten finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den verwandten Artikeln weiter unten. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!