Wir haben eine Tabelle in MySql mit ungefähr 30 Millionen Datensätzen, die folgende ist Tabellenstruktur
%Vor%Das ist meine Abfrage
Ich mache UNION ALL anstatt IN-Operation
%Vor%Im Folgenden finden Sie meine Erklärungsaussage
%Vor%2. Ich habe COUNT (DISTINCT subscriber_id) aus der Abfrage entfernt, dann wurde ich sehr groß Leistungssteigerung, ich bekomme Ergebnisse in ca. 1,5 sec, vorher wars dauerte 50 Sekunden - 1 Minute. Aber ich brauche distinct count von subscriber_id von der Abfrage
Im Folgenden wird erklärt, wenn ich COUNT (DISTINCT subscriber_id) aus der Abfrage lösche
%Vor% Ich könnte mein Leistungsproblem lösen, indem ich COUNT(DISTINCT...) weglasse, aber ich brauche diese Werte. Gibt es eine Möglichkeit, meine Abfrage umzuformen oder einen Index oder etwas hinzuzufügen, um die COUNT(DISTINCT...) -Werte zu erhalten, aber viel schneller?
UPDATE Die folgenden Informationen beziehen sich auf die Datenverteilung der obigen Tabelle
für 1 Domain 1 Kampagne 20 Protokolltypen 1k-200k Abonnenten
Die obige Abfrage, für die ich arbeite, die Domain mit 180.000 Abonnenten.
Wenn die Abfrage ohne count(distinct) viel schneller geht, können Sie vielleicht eine verschachtelte Aggregation durchführen:
Mit ein bisschen Glück dauert das 2-3 Sekunden statt 50. Sie müssen dies jedoch möglicherweise in Unterabfragen aufteilen, um die volle Leistung zu erhalten. Wenn dies keinen signifikanten Leistungszuwachs hat, ändern Sie in zurück in = eines der Typen. Wenn das funktioniert, ist möglicherweise union all erforderlich.
BEARBEITEN:
Ein weiterer Versuch besteht darin, Variablen zu verwenden, um die Werte vor dem group by :
Dies erfordert noch eine andere Art von Daten, aber es könnte helfen.
Um Ihre Frage zu beantworten:
Gibt es eine Möglichkeit, meine Abfrage umzuformen oder einen Index oder etwas hinzuzufügen? bekomme die COUNT (DISTINCT ...) Werte, aber viel schneller?
Ja, gruppiere nicht nach dem berechneten Feld (gruppiere nicht nach dem Ergebnis der Funktion). Berechnen Sie es stattdessen vor, speichern Sie es in der persistenten Spalte und fügen Sie diese persistente Spalte in den Index ein.
Ich würde versuchen, Folgendes zu tun und zu sehen, ob es die Leistung erheblich verändert.
1) Vereinfachen Sie die Abfrage und konzentrieren Sie sich auf einen Teil.
Lassen Sie nur eines der am längsten laufenden SELECT von den drei, lassen Sie UNION für die Tuning-Periode los. Sobald die längste SELECT optimiert ist, fügen Sie weitere hinzu und überprüfen Sie, wie die vollständige Abfrage funktioniert.
2) Die Gruppierung nach dem Ergebnis der Funktion lässt die Engine den Index nicht effizient verwenden.
Fügen Sie der Tabelle (zunächst vorübergehend, um die Idee zu überprüfen) eine weitere Spalte mit dem Ergebnis dieser Funktion hinzu. Soweit ich sehen kann, möchten Sie nach 1 Stunde gruppieren, fügen Sie also die Spalte log_time_hour datetime hinzu und setzen Sie sie auf log_time gerundet / auf die nächste Stunde gekürzt (bewahren Sie die Datumskomponente).
Index über neue Spalte hinzufügen: (domain, campaign_id, log_type, log_time_hour, subscriber_id) . Die Reihenfolge der ersten drei Spalten im Index sollte keine Rolle spielen (weil Sie die Gleichheit mit einer Konstanten in der Abfrage vergleichen, nicht den Bereich), sondern sie in derselben Reihenfolge wie in der Abfrage erstellen. Oder, besser, machen Sie sie in der Indexdefinition und in der Abfrage in der Reihenfolge der Selektivität. Wenn Sie die 100,000 -Kampagnen, 1000 -Domänen und 3 -Logarten haben, legen Sie sie in der folgenden Reihenfolge ab: campaign_id, domain, log_type . Es sollte nicht viel ausmachen, aber es lohnt sich zu überprüfen. log_time_hour muss an vierter Stelle in der Indexdefinition stehen und subscriber_id zuletzt.
Verwenden Sie in der Abfrage eine neue Spalte in WHERE und in GROUP BY . Stellen Sie sicher, dass Sie alle benötigten Spalten in GROUP BY enthalten: sowohl log_type als auch log_time_hour .
Benötigen Sie sowohl COUNT als auch COUNT(DISTINCT) ? Lassen Sie nur COUNT zuerst und messen Sie die Leistung. Lassen Sie nur COUNT(DISTINCT) und messen Sie die Leistung. Lassen Sie beide und messen Sie die Leistung. Sehen Sie, wie sie sich vergleichen.
Ich würde andere Ordnungen des verwendeten Indexes ausprobieren, indem ich die subscriber_id umherbewege und sehe, was der Effekt ist. Es ist möglich, dass Sie bessere Ergebnisse erzielen, wenn Sie Spalten mit einer höheren Kardinalität verschieben.
Zuerst dachte ich, dass es vielleicht nur einen Teil des Indexes benutzt (und nicht zu subscriber_id gelangt). Wenn sie die Subskribenten-ID nicht verwenden kann, wird sie nach dem Verschieben in den Indexbaum langsamer ausgeführt, was zumindest anzeigt, dass sie nicht verwendet werden kann.
Ich kann nicht an viel anderes denken, mit dem Sie spielen können.
subscriber_id ist in Ihrem Schlüssel nicht hilfreich, da Sie nach einem berechneten Feld außerhalb des Schlüssels gruppieren (log_date), bevor Sie bestimmte Abonnenten zählen. Es erklärt, warum dies so langsam ist, weil MySQL doppelte Abonnenten ohne Verwendung des Schlüssels sortieren und filtern muss.
Möglicherweise liegt ein Fehler in Ihrer log_time-Bedingung vor: Sie sollten die gegenteilige Zeitzonenumwandlung Ihrer Auswahl (dh '+05:30','+00:00' ) haben, aber dies wird Ihre Abfragezeit nicht wesentlich beeinflussen.
Sie können die "union all" vermeiden, indem Sie log_type IN (...) und group by log_type, log_date
Die effektivste Lösung wäre, Ihrem Datenbankschema ein Mid-Hour-Feld hinzuzufügen und dort eine der 48 Stunden in der Mitte des Tages einzustellen (und auf die Zeitzone in der Mitte der Stunde zu achten). Sie könnten also einen Index für campaign_id , domain , log_type , log_mid_hour , subscriber_id
Dies wird ziemlich überflüssig sein, aber die Geschwindigkeit verbessern.
Dies sollte zu einigen Initialisierungen in Ihrer Tabelle führen: Vorsicht: Testen Sie das nicht auf Ihrer Produktionstabelle
%Vor%Sie müssen auch die log_mid_hour in Ihrem Skript für zukünftige Datensätze festlegen.
Ihre Suchanfrage wird (für eine 11-stündige Zeitverschiebung) :
%Vor%Dies gibt Ihnen die Zählung für jede Mitte der Stunde, die vollen Nutzen aus Ihrem Index zieht.
Ich hatte ein sehr ähnliches Problem, das hier auf SO gepostet wurde, und bekam eine große Hilfe. Hier ist der Thread: MySQL MyISAM langsame count () Abfrage trotz Deckung Index
Kurz gesagt, ich entdeckte, dass mein Problem NICHTS mit der Abfrage oder den Indizes zu tun hatte und ALLES mit der Art, wie ich die Tabellen und MySQL eingerichtet hatte. Meine genaue Abfrage wurde viel schneller, wenn ich:
Ich habe keine Ahnung, welche dieser Änderungen mein Problem behebt (weil ich unwissenschaftlich war und sie nicht einzeln probiert habe), aber es hat meine Abfragen 50-100x schneller gemacht. YMMV.
Tags und Links sql mysql aggregate-functions query-performance mysql-variables