Ich habe eine Abfrage, die in sehr unterschiedlichen Zeiträumen zwischen 2 Datensätzen zurückkehrt. Für einen Satz (Datenbank A) kommt er in ein paar Sekunden zurück, für den anderen (Datenbank B) .... naja ich habe noch nicht lange genug gewartet, sondern über 10 Minuten. Ich habe diese beiden Datenbanken auf meinem lokalen Rechner abgelegt, wo ich das Problem mit MySQL 5.1.37 reproduzieren kann.
Kurioserweise ist Datenbank B kleiner als Datenbank A.
Eine abgespeckte Version der Abfrage, die das Problem reproduziert, ist:
%Vor%Der EXPLAIN-Abfrageplan für die erste Datenbank (A), die in ~ 2 Sekunden zurückkehrt, ist:
%Vor%Der EXPLAIN-Abfrageplan für die zweite Datenbank (B), die in & gt; 600 Sekunden zurückkommt, ist:
%Vor%Wenn Datenbank B läuft, kann ich den MySQL-Administrator anschauen und der Status bleibt unbegrenzt auf "Copy to tmp table". Datenbank A hat diesen Status auch nur für eine Sekunde.
Es gibt keine Unterschiede in der Tabellenstruktur, den Indizes, Schlüsseln usw. zwischen diesen Datenbanken (ich habe Show Create-Tabellen erstellt und unterschieden).
Die Größen der Tabellen sind:
%Vor%Einige Punkte zu beachten:
AKTUALISIEREN nach der Antwort von AJ: - Die Größe der ship_id ist in Datenbank B (max Wert = 800002752) signifikant größer als in Datenbank A (max Wert = 3489). Angesichts der Tatsache, dass diese InnoDB-Tabellen jede Pufferhilfe bei der Handhabung von Schlüsseln dieser Größe ändern würden? Weitere Aktualisierung dazu: Ich habe die Größe der Schlüssel reduziert und erneut ANALYZEd, aber immer noch keine Leistungsänderung.
UPDATE Beschreibung von EVENT_TABLE:
Beachten Sie, dass es in beiden Datenbanken identisch ist %Vor%
Und zu guter Letzt die SHOW CREATE TABLE EVENT_TABLE:
Das einzige, was sich zwischen Datenbanken unterscheidet, ist der automatische Inkrementwert
%Vor%Kann jemand vorschlagen, wie man dieses Problem löst? Was habe ich verpasst?
UPDATE nach einer Frage von Michael Holzmann Hier sind die neuen Abfragepläne basierend auf seiner aktualisierten STRAIGHT_JOIN-Abfrage. Beachten Sie, dass Datenbank B "Temporary verwenden; Verwenden von filesort" hat, während Datenbank A dies nicht tut. Könnte das an den langen Tasten oder ähnlichem liegen?
Datenbank A
%Vor%Datenbank B
%Vor%UPDATE Es ist definitiv datenbezogen. Ich habe die Daten aus der Datenbank A ausgegeben und in die Datenbank B geladen mit:
%Vor%und
%Vor%Dann läuft die Abfrage der Datenbank B schnell - dh. so schnell wie Datenbank A. Irgendwelche Ideen zur Diagnose, was mit den Daten falsch sein könnte?
AKTUALISIEREN @newtover: Aus Datenbank A:
%Vor%Aus Datenbank B (der schlechte)
%Vor%Und die Show für po_shipment erstellen:
%Vor%AKTUALISIEREN @chris_I Wenn ich die Abfrage abnehme, indem ich alle anderen Joins neben EVENT_TABLE lösche, bekomme ich die gleiche Leistung (dh crappy)
%Vor%AKTUALISIEREN @Marcus Adams: Abfrage nach Plänen, die Sie bei entfernten inneren Joins angefordert haben:
%Vor%Abfrageplan von Datenbank A (antwortet in 0.35s)
%Vor%Abfrageplan von Datenbank B (reagiert nicht rechtzeitig, um eine Tasse Tee zu machen)
%Vor%Wenn es sich um ein Datenproblem handelt, kann ich Ihnen nicht sagen, was das genaue Problem ist, aber hier ist meine Lieblingsstrategie, um diese Art von Problem zu lösen:
Versuchen Sie, die Hälfte Ihrer Joins zu entfernen. Wiederholen Sie den Vorgang rekursiv, bis die Abfrage schnell ausgeführt wird. Fügen Sie dann die Hälfte der Joins hinzu, die Sie im letzten Schritt entfernt haben ... (Diese Strategie erfordert wesentlich weniger Schritte als das Entfernen und Hinzufügen von Join durch Join.)
Sobald Sie den "schlechten" Join gefunden haben, können Sie versuchen, seine Werte mit einer zusätzlichen "where" -Klausel zu beschränken, bis die Abfrage wieder schnell ausgeführt wird ... Versuchen Sie in jedem Schritt, das Problem zu halbieren .
Hinweis: Es kann sehr gut der Fall sein, dass Sie viel mehr Datensätze für die Zwischenergebnisse Ihrer Joins erhalten, auch wenn die Gesamtmenge der Daten in Datenbank B kleiner ist.
Fügen Sie der Abfrage STRAIGHT_JOIN hinzu, um festzustellen, ob der Ausführungsplan das Problem ist. Der Optimierer wählt für jede Datenbank einen anderen Ausführungsplan, der das Problem möglicherweise verursacht.
%Vor% UPDATE
1. Der neue Ausführungsplan für Datenbank A benötigt keinen Dateisort oder temporäre Tabelle, da er den Primärschlüssel verwendet. Ich würde beginnen, USE INDEX in die Abfrage zu fügen, um zu sehen, ob Sie die Abfrage für Datenbank B beschleunigen können. Ist ship_id der Primärschlüssel von po_shipment? Wenn ja, müssen Sie herausfinden, was mehr kostet die Gruppe durch oder Filtern der Daten.
Wenn das nicht hilft, versuchen Sie, mehr der im Ausführungsplan von Datenbank A für Datenbank B verwendeten Indizes vorzuschlagen.
Habe MySQL schon seit einiger Zeit nicht mehr angefasst, aber ich vermute, dass das Problem mit beiden zusammenhängt.
Da es ein Problem mit den Daten zu sein scheint, wäre es wahrscheinlich hilfreich, herauszufinden, welche Daten das Problem verursachen. Erstellen Sie eine dritte Datenbank C und fügen Sie zweimal die Hälfte der Daten aus der Datenbank B ein (Sie haben also die gleiche Anzahl an Zeilen). Wenn Datenbank C langsamer ist, als die schlechten Daten vorhanden sind, ist es andernfalls in der anderen Hälfte. Wiederholen Sie den Vorgang mit kleineren und kleineren Chunk-Größen, um die Problemdaten zu finden.
Obwohl Datenbank B kleiner als Datenbank A ist, sind die Tabellen "po_header" und "EVENT_TABLE" nicht proportional kleiner. Das kann etwas mit der Geschwindigkeitsdifferenz zu tun haben.
Haben Sie versucht, "CHECK TABLE" und / oder "OPTIMIZE TABLE" zu tun?
Ich hatte eine ähnliche Situation wo das Problem war, dass die Schlüsselindizes wo nicht richtig aktualisiert und CHECK TABLE repariert sie. Eine lange Einstellung, aber den Versuch wert.
Stellen Sie zunächst sicher, dass Sie Indizes für die entsprechenden Felder erstellt haben. Ich bin sicher, dass du das getan hast.
Versuchen Sie als Nächstes, Indexhinweise ( USE INDEX ) zu verwenden, um das zu erzwingen Datenbank, um die Indizes richtig zu verwenden.
Ich hatte ein ähnliches Problem , in dem ich davon ausgegangen war, dass die Indizes waren Setup und von mysql richtig verwendet, aber sie waren es nicht. Ich konnte es umgehen, indem ich Indexhinweise benutzte.
Was ist die Selektivität von Daten in po_shipment.eta und po_shipment.ship_id? Können Sie die Ergebnisse der folgenden Abfrage aus beiden Datenbanken veröffentlichen:
%Vor%Normalerweise sind die Felddaten (je näher 1) selektiver, der bessere Index funktioniert. Die Ursache könnte auch eine sehr ungleichmäßige Verteilung der Daten in po_shipment.eta sein (wenn Sie '2099-01-01' oder ähnliches als Sonderwert verwenden), wenn dem Optimierer die erforderlichen Statistiken fehlen.
Übrigens, könnten Sie SHOW CREATE TABLE po_shipment bereitstellen? Indizes auf dem Tisch könnten Licht ins Dunkel bringen.
UPD : Wenn die Selektivität für das Feld so niedrig ist wie für eta field, ist der Index grundsätzlich nutzlos. Schlimmer noch, es könnte den Optimierer beim Auswählen und Verlangsamen von Einfügungen und Aktualisierungen der Daten verwirren.
Der erste Vorschlag besteht darin, den Index für das Feld eta zu entfernen und die Ergebnisse zu messen. Es kann sein, dass für den A-Datenbank-Optimierer nicht einmal versucht, den Index wegen der sehr geringen Selektivität zu verwenden, und für die B-Datenbank gibt es den Index einen Versuch.
Die zweite Sache, die mich stört, ist, warum gruppieren Sie sich mit ship_id überhaupt? Wenn es eine Notwendigkeit in einer impliziten temporären Tabelle gibt und unter den Feldern ein TEXT (wie in Ihrem Fall) oder BLOB ein ist, wird MySQL immer auf Disk temporären Tabellen zum Sortieren verwenden (was implizit in GROUP BY benötigt wird). In Ihrem Fall ist ship_id der geclusterte Primärschlüssel und die Ergebnisse werden trotzdem nach ship_id sortiert. Was Sie brauchen, ist eine Basisabfrage zu extrahieren (bereits gruppiert nach ship_id, wenn es mehrere Übereinstimmungen geben kann) und JOIN po_shipment mit der Basisabfrage, die Ihre Bereichsbedingung anwendet und die Gruppe nicht oben verwendet.
Und der dritte. Brauchst du wirklich alle Felder, wenn du * oben verwendest? Nachdem Sie 10 Tische beigetreten sind, erhalten Sie viele Fieds. Ich glaube kaum, dass du alle von ihnen brauchst. Selbst ein Ausschluss des TEXT-Feldes von den Ergebnissen könnte die Abfrageleistung erhöhen.
Ich habe das Gleiche bei der Verwendung der Bereichsauswahl less than & lt; und greater than & gt; auch.
Experiment: Wenn der Bereich nicht zu groß ist, haben Sie versucht versucht, den Bereich zu erweitern und eine IN (..., ..., ...) Anweisung instad?
ZB
%Vor%Bearbeiten: Hinzugefügt USE INDEX () Hinweis als Salman A vorgeschlagen. Sieht so aus, als ob mysql den möglichen Index sieht, aber er wählt ihn nicht aus ... es lohnt sich, ihn zu testen.
Ich denke, es könnte die manuelle Sortierung (Filesort) für die GROUP BY-Klausel sein, die das spürbare Defizit verursacht.
Versuchen Sie, den SQL_BIG_RESULT-Hinweis zu verwenden, um zu sehen, ob MySQL seine Methode zur Behandlung von GROUP BY verbessert.
%Vor%Ich denke, Sie gehen das falsch an. Wenn Sie einen LINKEN JOIN ausführen, erhalten Sie alle Datensätze in EVENT_TABLE unabhängig von einer Übereinstimmung mit po_shipment zurück.
Und du hast sechsmal einen LINKEN JOIN zu EVENT_TABLE laufen lassen. Sie erhalten (6 * (SELECT count (*) FROM EVENT_TABLE)) Datensätze mit jeder Abfrage zurück. Da db A weniger Datensätze als db B hat, läuft die Abfrage natürlich schneller auf A.
Ich denke, so etwas würde Welten besser machen:
%Vor%Es sieht so aus, als ob Sie für jeden Zustandsübergang eine Zustandsmaschine für po_shipment w / event records in event_table modellieren.
Was ist die Geschäftslogik, die Sie mit dieser Abfrage ausdrücken möchten?
Sie sollten in der Lage sein, Ihrem po_shipment ein Zustandsfeld hinzuzufügen, das die Reihe von Ereignissen in der event_table zusammenfasst und denormalisiert.
Erstellen Sie die Logik, um zu überprüfen, ob alle 6 Ereignisse für einen bestimmten Datensatz stattgefunden haben, der nicht in der Datenbank enthalten sein sollte. Er sollte in Ihrem Modell vorhanden sein und in der Datenbank beibehalten werden (klassisches Zustandsautomatenmuster für ein Modell).
Dann wählen Sie einfach aus po_shipment where state = 'MAS5' und eta & gt; Datum;
Tags und Links optimization mysql sql-execution-plan