Ich habe eine Abfrage, die für sehr lange Zeit in den SENDING DATA Status geht. Kann mir bitte jemand dabei helfen: unten sind Details
Mysql-Abfrage:
%Vor% Gesamtzahl Datensätze in delivered_history : 223870168
Gesamtzahl Datensätze in inserted_history : 264817239
Gesamtzahl Datensätze in submitted_history : 226637058
Erkläre Abfrage zurück:
%Vor%CREATE TABLE für delivered_history
%Vor%CREATE TABLE für insert_history
%Vor%CREATE TABLE für submitted_history
%Vor%ALLE TABELLEN SIND DATUM-PARTIONIERT in den Zeitstempelfeldern
Liste der globalen Variablen in Mysql Server
Ihr erklärter Plan, den Sie gegeben haben:
%Vor%zeigt, dass Sie schlagen: 735310 * 2270405 * 2238701 = 3T Reihen !!!!!! Effektiv verwenden Sie Ihre Indizes nicht zu ihrem vollsten Potenzial.
Wie interpretiere ich deinen 'Plan erklären': Für jede Zeile in der Tabelle 'a' (735310) triffst du die Tabelle 'b' 2270405 mal. Für jede Zeile, die Sie in der Tabelle 'b' treffen, treffen Sie die Tabelle 'c' 2238701 mal. Wie Sie sehen können, ist dies ein exponentielles Problem.
Ja, die 8 MB des InnoDb-Pufferspeichers sind klein, aber wenn Sie Ihren EXPLAIN-Plan auf xxxx * 1 * 1 heruntersetzen, werden unglaubliche Geschwindigkeiten erreicht, sogar für 8 MB Pufferspeicher.
Gegeben Ihre Anfrage:
%Vor% Hier sind die Probleme, die ich sehe:
A) Ihre _history Tabellen sind in den Spalten mit 'Timestamp' Datentyp partitioniert, aber Sie sind NICHT diese Spalten in Ihrem JOIN / WHERE Kriterium. Die Engine muss jede Partition ohne diese Informationen treffen.
B) Der Zugriff auf submitted_history und delivered_history verwendet nur 1 Spalte einer 2-spaltigen PK. Sie profitieren nur teilweise von der PK. Kannst du mehr Spalten bekommen, um Teil des JOIN zu sein? Sie müssen die Anzahl der gefundenen Zeilen für diese Tabelle so nah wie möglich an '1' bringen.
C) msgID = varchar (64) und dies ist die 1. Spalte der PK für jede Tabelle. Deine Schlüssel auf jedem Tisch sind ** RIESIG ** !!
- Versuchen Sie, die Größe der Spalten für das PK zu reduzieren oder verwenden Sie andere Spalten.
Ihre Datenmuster der anderen Schlüssel zeigen an, dass Sie in Nicht-PK-Schlüsseln eine Menge an Festplatten- / RAM-Platz gebunden haben.
Frage 1) Was bedeutet "Zeige Indizes von" ( Link ) für jede der Tabellen Bericht ?? Die Spalte "Cardinality" zeigt Ihnen, wie effektiv jeder Ihrer Schlüssel wirklich ist. Je kleiner die Kardinalität ist, desto weniger effektiv ist dieser Index. Sie möchten, dass die Kardinalität für optimale Leistung möglichst nahe an "Gesamtzeilen" liegt.
Frage 2) Können Sie das SQL so neu einteilen, dass die JOIN-Spalten jeder Tabelle diejenigen mit der höchsten Kardinalität für diese Tabelle sind?
Frage 3) Sind die Spalten des 'timestamp' Datentyps wirklich die beste Spalte für die Partitionierung? Wenn Ihre Zugriffsmuster immer 'msgId' verwenden und msgId die 1. Spalte des PK ist, dann.
Frage 4) Ist msgId eindeutig? Meine Vermutung ist ja, und die zweite Spalte der PK ist nicht wirklich notwendig.
Lesen Sie Optimieren von SQL ( Link ) und haben Sie die Indexkardinalität Berichte über Ihre Tabellen. Dies ist der Pfad, um herauszufinden, wie eine Abfrage optimiert wird. Sie möchten, dass die Zeilen des EXPLAIN-Plans N * 1 * 1 sind.
SEITENHINWEIS: InnoDb & amp; MyISAM-Engines aktualisieren nicht automatisch die Tabellenkardinalität für nicht eindeutige Spalten. Der Datenbankadministrator muss die Tabelle "Analysieren" regelmäßig manuell ausführen, um seine Genauigkeit sicherzustellen.
Viel Glück.
Wäre es möglich, den Index von insert_history zu ändern,
?systemId (systemId)
soll
seinsystemId (systemId, inTime) Oder fügen Sie einen zusätzlichen Index hinzu
Meine Logik ist, dass dies dazu beitragen sollte, die Auswahl der Zeile inserted_history (a) zu beschleunigen, die die Grundlage für den Join bildet.
Die Where-Klausel "where a.inTime zwischen '2010-08-10 00:00:00' und '2010-08-010 23:59:59' und a.systemId = 'ND_arber'" wären alle auswählbar nach Index. Momentan sind Zeilen durch systemId auswählbar, aber dann müssen alle diese Zeilen für die Zeit gescannt werden.
Wie interessant, wie viele Datensätze würden (im Durchschnitt) für jede System-ID vorhanden sein? Da auch msgid nicht eindeutig ist, wie viele Datensätze (im Durchschnitt) in den anderen Tabellen haben die gleiche msgid.
Hauptidee
Benutzt du InnoDB? Es sieht so aus, als ob Ihr Pufferpool nur 8 MB groß ist. Das könnte leicht das Problem sein, Sie haben eine Menge Daten und InnoDB hat nicht viel Speicher. Können Sie die innodb_buffer_pool_size erhöhen? Sie müssen MySQL neu starten, aber ich wette, das würde einen RIESIGEN Unterschied ausmachen, selbst wenn Sie es nur 256 oder 512 MB geben.
Update: Ich sehe, dass Ihre Speicher-Engine und das Tabellenformat standardmäßig auf MyISAM eingestellt sind. Wenn Sie nichts anderes angeben, trifft dies nicht zu. Ich frage mich, ob die myisam_sort_buffer_size helfen würde? Wir verwenden MyISAM nicht, daher bin ich nicht damit vertraut, es zu tunen.
Random Thought
Ich frage mich, ob es etwas damit zu tun hat, dass der Primärschlüssel alphanumerisch ist (insbesondere VARCHAR). Ich erinnere mich, dass wir Probleme mit der Leistung bei nicht-numerischen Primärschlüsseln hatten, aber diese Datenbank von 4.0 oder 4.1 stammte, so dass sie möglicherweise nicht zutrifft (oder jemals richtig war).
Sekundäre Idee
Nach der oben erwähnten Erinnerung würde ich wohl am besten meinen Tipp geben. Wenn ich eine Anfrage habe, die langsam abläuft, finde ich es oft hilfreich, wenn ich ihnen mehr Informationen gebe. Sie haben messageId / Zeitindizes für jede Tabelle. Vielleicht würde etwas mehr wie das besser funktionieren:
%Vor%Ich vermute, die Dinge werden in A, dann B, dann C eingefügt. Wenn Sie bessere Grenzen haben (sagen Sie, wenn etwas in A geht, wird es immer gesendet und innerhalb eines Tages eingereicht), fügen Sie diese Informationen hinzu.
Ich wundere mich darüber, weil ich gesehen habe, dass es meine Abfrageleistung in einigen Situationen unterstützt, aber auch, weil Sie die Daten nach datetime partitioniert haben. Das kann dem Optimierer helfen.
Mein anderer Vorschlag wäre, Ihre Anfrage für eine kurze Zeit auszuführen, sagen Sie 10 Minuten anstatt eines ganzen Tages und stellen Sie sicher, dass die Ergebnisse stimmen. Dann versuchen Sie es mit 30. Erhöhen Sie es und sehen Sie, wann es in das Gebiet "Komm zurück morgen" fällt. Das kann dir etwas sagen.