MySQL nutzt geschickt Summe, Groß- und Kleinschreibung und Wann, um statistische Abfragen zu optimieren

Ich habe kürzlich in der Firma an einem Projekt gearbeitet, bei dem es um die Entwicklung statistischer Berichte ging. Aufgrund der relativ großen Datenmenge benötigte die Abfrageanweisung, die ich zuvor geschrieben hatte, etwa zehn Sekunden, um 500.000 Daten abzufragen. Später habe ich unter Anleitung meines Chefs die SQL-Anweisungen „sum, case...when...“ verwendet, um sie neu zu schreiben, und die Leistung wurde sofort auf eine Sekunde verbessert. Um das Problem und die Lösung klar und prägnant zu erklären, werde ich hier das Nachfragemodell vereinfachen.

In der Datenbank liegt nun eine Auftragstabelle (eine vereinfachte Zwischentabelle) mit folgendem Aufbau vor:

CREATE TABLE `statistic_order` (
 `oid` bigint(20) NICHT NULL,
 `o_source` varchar(25) DEFAULT NULL COMMENT 'Quellennummer',
 `o_actno` varchar(30) DEFAULT NULL COMMENT 'Aktivitätsnummer',
 `o_actname` varchar(100) DEFAULT NULL COMMENT 'Name der Teilnahmeaktivität',
 `o_n_channel` int(2) DEFAULT NULL COMMENT 'Einkaufszentrum',
 `o_clue` varchar(25) DEFAULT NULL COMMENT 'Hinweiskategorie',
 `o_star_level` varchar(25) DEFAULT NULL COMMENT 'Sternebewertung der Bestellung',
 `o_saledep` varchar(30) DEFAULT NULL COMMENT 'Marketingabteilung',
 `o_style` varchar(30) DEFAULT NULL COMMENT 'Autotyp',
 `o_status` int(2) DEFAULT NULL COMMENT 'Bestellstatus',
 `syctime_day` varchar(15) DEFAULT NULL COMMENT 'Datum nach Tag formatieren',
 PRIMÄRSCHLÜSSEL (`oid`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8

Die Projektanforderungen lauten wie folgt:

Zählen Sie die Anzahl der Quellnummern für jeden Tag innerhalb eines bestimmten Zeitraums. Die Quellnummer entspricht dem Feld o_source in der Datentabelle und der Feldwert kann CDE, SDE, PDE, CSE, SSE sein.

Quellenklassifizierungsflüsse im Laufe der Zeit

Zu Beginn habe ich dieses SQL geschrieben:

wählen Sie S.syctime_day,
 (wählen Sie count(*) aus statistic_order SS, wobei SS.syctime_day = S.syctime_day und SS.o_source = 'CDE') als 'CDE',
 (wählen Sie count(*) aus statistic_order SS, wobei SS.syctime_day = S.syctime_day und SS.o_source = 'CDE') als 'SDE',
 (wählen Sie count(*) aus statistic_order SS, wobei SS.syctime_day = S.syctime_day und SS.o_source = 'CDE') als 'PDE',
 (wählen Sie count(*) aus statistic_order SS, wobei SS.syctime_day = S.syctime_day und SS.o_source = 'CDE') als 'CSE',
 (Wählen Sie count(*) aus statistic_order SS, wobei SS.syctime_day = S.syctime_day und SS.o_source = 'CDE') als 'SSE'
 aus Statistikreihenfolge S, wobei S.syctime_day > '2016-05-01' und S.syctime_day < '2016-08-01' 
 GROUP BY S.syctime_day, sortiert nach S.syctime_day aufsteigend;

Diese Schreibmethode verwendet eine Unterabfrage. Ohne einen Index hinzuzufügen, wurde diese SQL-Anweisung auf 550.000 Daten ausgeführt. Es dauerte fast zehn Minuten, in der Workbench zu warten, und schließlich wurde eine Verbindungsunterbrechung gemeldet. Durch den Erläuterungsinterpreter können Sie sehen, dass der SQL-Ausführungsplan wie folgt aussieht:

Jede Abfrage führt einen vollständigen Tabellenscan durch. Die fünf Unterabfragen DEPENDENT SUBQUERY zeigen an, dass sie von externen Abfragen abhängen. Dieser Abfragemechanismus führt zuerst eine externe Abfrage aus, um die Datumsergebnisse nach Gruppieren zu erhalten, und dann fragen die Unterabfragen die Anzahl der CDEs, SDEs usw. in den entsprechenden Daten ab. Seine Effizienz ist vorstellbar.

Nach dem Hinzufügen von Indizes zu o_source und syctime_day wird die Effizienz erheblich verbessert und die Abfrageergebnisse werden in etwa fünf Sekunden erhalten:

Wenn wir uns den Ausführungsplan ansehen, stellen wir fest, dass die Anzahl der gescannten Zeilen stark reduziert wurde und der vollständige Tabellenscan nicht mehr durchgeführt wird:

Das ist sicherlich nicht schnell genug. Wenn die Datenmenge in die Millionen geht, ist die Abfragegeschwindigkeit definitiv nicht mehr zu tolerieren. Ich habe mich gefragt, ob es eine Möglichkeit gibt, alle Ergebnisse durch einmaliges Durchlaufen direkt abzufragen, ähnlich dem Durchlaufen der Listensammlung in Java, bei dem beim Auftreten einer bestimmten Bedingung einmal gezählt wird, sodass ein vollständiger Tabellenscan durchgeführt werden kann, um den Ergebnissatz und den Ergebnisindex abzufragen. Die Effizienz sollte sehr hoch sein. Unter Anleitung des Chefs haben wir die Summenaggregationsfunktion und die „seltsame“ Verwendung von Fall… wenn… dann… verwendet, um dieses Problem effektiv zu lösen.
Das spezifische SQL lautet wie folgt:

 wählen Sie S.syctime_day,
 Summe (Fall wenn S.o_source = 'CDE', dann 1, sonst 0, Ende) als 'CDE',
 Summe (Fall wenn S.o_source = 'SDE', dann 1, sonst 0, Ende) als 'SDE',
 Summe (Fall wenn S.o_source = 'PDE', dann 1, sonst 0, Ende) als 'PDE',
 Summe (Fall wenn S.o_source = 'CSE', dann 1, sonst 0, Ende) als 'CSE',
 Summe (Fall wenn S.o_source = 'SSE', dann 1, sonst 0, Ende) als 'SSE'
 aus Statistikreihenfolge S, wobei S.syctime_day > '2015-05-01' und S.syctime_day < '2016-08-01' 
 GROUP BY S.syctime_day, sortiert nach S.syctime_day aufsteigend;

Ich werde nicht zu viel über die Verwendung von case...when...then in MySQL erklären. Dieses SQL ist leicht zu verstehen. Zuerst durchlaufen wir die Datensätze einzeln, group by klassifiziert die Daten und die Aggregatfunktion sum summiert die Werte eines bestimmten Datums. Der entscheidende Punkt ist, dass case...when...then der Summe geschickt Bedingungen hinzufügt. Wenn o_source = 'CDE', ist die Anzahl 1, andernfalls 0; wenn o_source = 'SDE'...

Die Ausführung dieser Anweisung dauerte nur etwas mehr als eine Sekunde, was ideal ist, um Statistiken dieser Dimension mit mehr als 500.000 Datenpunkten durchzuführen.

Der Ausführungsplan zeigt, dass zwar die Anzahl der gescannten Zeilen zugenommen hat, aber nur ein vollständiger Tabellenscan durchgeführt wird und es sich um eine EINFACHE Abfrage handelt, sodass die Ausführungseffizienz natürlich hoch ist:

Wenn Sie eine bessere Lösung oder Idee für dieses Problem haben, hinterlassen Sie bitte eine Nachricht.

Zusammenfassen

Dies ist das Ende dieses Artikels über die Verwendung von „sum“, „case“ und „when“ in MySQL zur Optimierung statistischer Abfragen. Weitere Informationen zur Optimierung statistischer Abfragen in MySQL finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!