In unserer Anwendung sammeln wir Daten über die Motorleistung von Automobilen - im Wesentlichen liefern wir Daten über die Motorleistung basierend auf dem Motortyp, dem Fahrzeug, in dem er läuft, und dem Motordesign. Derzeit ist die Basis für neue Reiheneinsätze eine Motor-Ein-Aus-Periode; Wir überwachen Performance-Variablen basierend auf einer Änderung des Engine-Status von aktiv nach inaktiv und umgekehrt. Die verwandte Tabelle engineState sieht folgendermaßen aus:
Für eine spezifische Analyse möchten wir den Tabelleninhalt basierend auf einer Zeilengranularität von Minuten und nicht auf der aktuellen Basis des aktiven / inaktiven Maschinenstatus analysieren. Dazu überlegen wir, eine einfache productionMinute -Tabelle mit einer Zeile für jede Minute in dem Zeitraum zu erstellen, in dem wir analysieren und die Tabellen productionMinute und engineEvent in den Datums- und Uhrzeitspalten jeder Tabelle einfügen. Wenn unser Analysezeitraum vom 2009-12-01 bis 2010-02-28 ist, würden wir eine neue Tabelle mit 129.600 Zeilen erstellen, eine für jede Minute eines jeden Tages für diesen Dreimonatszeitraum. Die ersten paar Zeilen der Tabelle productionMinute :
Der Join zwischen den Tabellen wäre:
%Vor%Dieser Beitritt bringt jedoch mehrere Umweltprobleme mit sich:
engineState hat 5 Millionen Zeilen und die Tabelle productionMinute hat 130.000 Zeilen engineState -Zeile mehr als eine Minute erstreckt (dh die Differenz zwischen es.state_start_time und es.state_end_time ist länger als eine Minute), wie im obigen Beispiel, gibt es mehrere productionMinute -Tabellenzeilen Diese Join zu einer einzelnen engineState Tabellenzeile engineState -Tabellenzeilen zu einer einzelnen productionMinute -Zeile Beim Testen unserer Logik und der Verwendung nur eines kleinen Tabellenextrakts (ein Tag anstatt drei Monate für die Tabelle productionMinute ) dauert die Generierung der Abfrage über eine Stunde. Bei der Untersuchung dieses Artikels, um die Leistung zu verbessern, so dass es möglich wäre, dreimonatige Daten abzufragen, wollten wir eine temporäre Tabelle aus engineEvent eins erstellen, wobei alle Tabellendaten entfernt wurden, die für die Analyse nicht kritisch sind Verbinden der temporären Tabelle mit der Tabelle productionMinute . Wir planen auch mit verschiedenen Joins zu experimentieren - speziell mit einem inneren Join - um zu sehen, ob das die Performance verbessern würde.
Was ist der beste Abfrageentwurf für Join-Tabellen mit der Viele-Viele-Beziehung zwischen den Join-Prädikaten, wie oben beschrieben? Was ist der beste Verbindungstyp (links / rechts, innen)?
Die Datenwiederherstellungsleistung ist die Funktion von
Für alle diese können Sie optimieren
Indizes haben den größten Einfluss auf die Performance, da sie die Speicherzugriffszeit und -geschwindigkeit im Speicher um Größenordnungen reduzieren können (sie schalten O (n) auf Kosten der Indexstrukturpflege auf O (n)) Sie verlangsamen Updates)
Für eine maximale Suchgeschwindigkeit sollten Indizes alle Joins abdecken und Bedingungen und Abfragen sollten so geschrieben werden, dass der Abfrageoptimierer bestimmen kann, welche davon den höchsten Nutzen bringt, wenn er zuerst ausgeführt wird (höchste Selektivität).
Versuchen Sie für Ihr spezielles Beispiel, verschiedene Kombinationen von Indizes zu vergleichen
Wenn Sie Ihre Daten kennen, können Sie bestimmen, welche optimal ist. Ich wäre nicht überrascht, wenn Sie feststellen würden, dass die letzten zwei Spaltenindizes die besten Ergebnisse bringen würden. Oder mit einer einzelnen Spalte und einem anderen zwei Spaltenindex (aber in umgekehrter Reihenfolge der Spalten).
In beiden Fällen würde der anständige Optimierer in der Lage sein, die Ergebnismenge zu ermitteln, indem er nur die Indizes liest und nicht einmal auf die tatsächlichen Datensätze schaut, was den Festplattenzugriff erheblich einschränkt.
Ich stimme mit vy32 überein. Sie müssen diese Abfrage einmal und nur einmal ausführen, um Ihre Daten in einem für die Analyse geeigneten Format zu erhalten. Sie sollten ein geeignetes ETL-Tool verwenden (oder einfach nur perl oder etwas Einfaches), um die Daten aus der engineState-Tabelle zu ermitteln, die Produktionsminuten zu berechnen und sie dann in eine andere DB zu laden, die für Analyseanfragen geeignet modelliert wurde.
Wenn Sie glauben, dass Ihr Problem durch Sie besteht, denentieren Sie einfach Ihre Daten und weisen Sie winzige Zahlen als Ersatzschlüssel zu. Dies ist ein relativ einfaches (und häufiges) ETL-Problem, das im direkten SQL nicht performant ist, aber mit anderen Sprachen und Tools einfach ist.
Ihr Produktionsvolumen würde von einem echten ETL-Prozess leicht verarbeitet werden.
Die Leistung hängt davon ab, wie Ihre Daten in den Tabellen strukturiert sind.
Ein linker oder rechter äußerer Join ist nur nützlich, wenn Sie alle Werte in der linken oder rechten Tabelle für die ausgewählte Projektion haben möchten und diese Werte möglicherweise nicht in der Tabelle enthalten sind, mit der sie verbunden ist.
Vertraue deinem Abfrageoptimierer, um den effizientesten Joinalgorithmus für deine Daten zu finden ... er wurde erstellt, um zu wissen, wie man seine Aufgabe gut macht. Wenn Sie Leistungsprobleme haben, schauen Sie sich an, wie die Daten strukturiert und gespeichert sind.
Meine Erfahrung ist, dass der MySQL-Abfrageoptimierer ziemlich schlecht ist. Die in PostgreSQL ist viel besser.
Ihr Problem besteht darin, dass Ihre Daten so strukturiert sind, dass sie leichter aufgezeichnet werden können und nicht für eine einfachere Analyse. Mein Vorschlag ist, dass Sie den temporären Tisch erstellen, aber nicht so, wie Sie es sich vorstellen können. Ich denke, dass es am besten ist, am Ende jeden Tages einen Nachbearbeitungsschritt zu haben, der alle Daten des Tages aufnimmt und minutengenaue Einträge in eine neue Tabelle (idealerweise auf einer anderen Spindel) mit einem production_minute-Index erstellt. Diese neue Datenbank ist schneller für analytische Abfragen und die Abfragen verlangsamen die Datenerfassung nicht merklich.
Wenn ich richtig verstanden habe, dann sehen Sie sich ein BI-Problem an. Ein BI-Layout würde die operativen Daten abgesehen von der konsolidierten haben.
Damit dies geschieht (schnell und schmutzig), benötigen Sie drei Elemente.
Auf diese Weise beschleunigen Sie Ihre Abfrage, da es jetzt eine einfache Auswahl wäre.
Wie in jeder BI-Lösung müssen Sie die ETL täglich (abhängig von Ihren Geschäftsanforderungen) ausführen, um Ihre denormalisierten Informationen zu aktualisieren.
Auf der anderen Seite könnten Sie den BI-Weg ablehnen und an Ihrem aktuellen Schema / Ihrer Anfrage arbeiten. Sie könnten Indizes, Statistiken und Altertabellen hinzufügen, aber meiner Meinung nach ist das keine skalierbare Lösung. Sie könnten das Leistungsproblem für eine drei Monate alte Datenbank lösen, aber was ist, wenn Sie eine drei Jahre alte Datenbank haben?
Die Verwendung von LEFT JOIN, INNER JOIN oder RIGHT JOIN ist ein semantischer Unterschied - die Verwendung einer anderen Verknüpfung für Leistung ist nicht nur eine schlechte Idee, sondern bedeutet auch, dass die Beziehung zwischen Tabellen nicht besteht vollständig verstanden - da die verschiedenen JOIN-Typen unterschiedliche Informationen zurückgeben können, weil sie verschiedene Dinge bedeuten.
In der Regel sind INNER JOINs sehr optimiererfreundlich, da dadurch verschiedene Filterkriterien aus Ihrer WHERE-Klausel und der JOIN-Bedingung um einiges mehr verschoben werden können, um die Index-Scans oder Tabellen-Scans zu verbessern. Einschränkungen der referenziellen Integrität können dem Optimierer auch Informationen über Daten geben, die auf beiden Seiten garantiert sind.
Sie sollten Ihre Ausführungspläne überprüfen und sich Ihre Indexierungsstrategien ansehen. Idealerweise möchten Sie enge, abdeckende Indizes und Sie möchten in Ihren Plänen Index-Suchvorgänge, Index-Scans, Tabellen-Scans (in der Reihenfolge ihrer Präferenz) sehen.
Normalerweise möchten Sie, dass Ihr Modell für die Transaktionsverarbeitung normalisiert und für die Berichterstellung denormalisiert wird, aber zwei Modelle sind am Anfang lästig. Daher versuchen Sie zunächst, Berichte und Analysen zu den normalisierten Daten zu erstellen eine Weile mit besseren Indizes und Blick auf die Ausführungspläne.
Wenn Ihre Berichterstattung auf einer gut indizierten Normalform zu schlecht wird, würde ich die Daten vielleicht in ein dimensionales Modell umwandeln (siehe Kimballs Methodik) mit Sternschemata, die sehr einfache Schemata für das Reporting haben ( typischerweise alle INNER JOINs und ein einfacher Stern) und kann sehr gut auf traditionellen Datenbanksystemen optimiert werden.
Tags und Links sql mysql performance join