Wann und warum sind Datenbank-Joins teuer?
Ich mache einige der Forschung in Datenbanken und ich freue mich auf einige Einschränkungen des relationalen DBs.
Ich bin es, der Verknüpfungen von großen Tabellen ist sehr teuer, aber ich bin mir nicht ganz sicher, warum. Was macht das DBMS tun müssen, um die Ausführung einer join-operation, wo ist der Engpass?
Wie kann Denormalisierung Hilfe zur überwindung dieser Aufwand? Wie gehen andere Techniken der Optimierung (indexing, zum Beispiel) helfen?
Persönlichen Erfahrungen sind willkommen! Wenn du gehst, um links zu Ressourcen, vermeiden Sie bitte Wikipedia. Ich weiß, wo zu finden, die bereits.
In diesem Zusammenhang Frage ich mich, über die denormalisierten Ansätze von cloud-service-Datenbanken wie BigTable und SimpleDB. Sehen diese Frage.
InformationsquelleAutor der Frage Rik | 2008-10-06
Du musst angemeldet sein, um einen Kommentar abzugeben.
Denormalising um die Leistung zu verbessern? Es klingt überzeugend, aber es muss nicht Wasser zu halten.
Chris Date, die in Unternehmen mit Dr. Ted Codd war der ursprüngliche Verfechter des relationalen Datenmodells, rannte aus der Geduld mit falsch Argumente gegen die Normalisierung und systematisch abgerissen, Sie mit wissenschaftlichen Methode: er bekam große Datenbanken und getestet diese Behauptungen.
Ich glaube, er schrieb es in Relationalen Datenbank Schriften 1988-1991 aber dieses Buch wurde später rollte in Ausgabe sechs von Einführung in Datenbank-Systemedie die endgültigen text auf der Datenbank-Theorie und design, in seiner achten Auflage als ich Schreibe und wahrscheinlich auch in gedruckter Form zu bleiben für die kommenden Jahrzehnte. Chris Datum war ein Experte in diesem Bereich, wenn die meisten von uns waren noch liefen barfuß.
Er festgestellt, dass:
Es kommt alles zurück zur Begrenzung der Größe des working set. Verknüpfungen mit richtig ausgewählten Schlüssel mit richtig eingerichtet Indizes sind Billig, nicht teuer, denn Sie ermöglichen eine deutliche Beschneidung des Ergebnisses vor die Zeilen werden in die Tat umgesetzt.
Materialisieren das Ergebnis beinhaltet bulk Festplatte liest, die sind das teuerste an der übung um ein Vielfaches. Ausführen einer join dagegen, logisch erfordert Abruf nur die Schlüssel. In der Praxis, auch nicht die wichtigsten Werte abgerufen werden: der Schlüssel der hash-Werte verwendet werden, für join-Vergleiche, die Eindämmung der Kosten für mehrspaltige joins und radikal die Reduzierung der Kosten für joins mit string-Vergleiche. Nicht nur erheblich mehr passen in den cache, es gibt viel weniger Datenträger Lesen zu tun.
Darüber hinaus ein gutes Optimierungstool wählen die meisten einschränkende Bedingung und wenden Sie es, bevor es führt einen join sehr effektiv Dank der hohen Selektivität des joins auf Indizes mit hoher Kardinalität.
Zugegeben, diese Art der Optimierung kann auch angewendet werden, um denormalised Datenbanken, sondern die Art von Menschen, die wollen zu denormalise ein schema in der Regel denke nicht über Kardinalität, wenn (wenn) Sie einrichten Indizes.
Ist es wichtig zu verstehen, dass die Tabelle durchsucht (Prüfung jeder Zeile in einer Tabelle im Zuge der Herstellung einer Verknüpfung) sind in der Praxis selten. Ein query Optimizer wählen, wird ein table scan " nur wenn eine oder mehrere der folgenden hält.
Durchführung einer operation ist teurer als nicht durchführen. Allerdings, die Durchführung der falsch Betrieb, gezwungen in sinnlose disk I/O und dann abziehen der Schlacke, die vor dem ausführen der Verknüpfung, die Sie wirklich brauchen, ist viel teurer. Auch wenn der "falsch" - Betrieb ist vorausberechnete und-Indizes wurden vernünftig angewendet, bestehen weiterhin erhebliche Strafe. Denormalising precompute eine join - ungeachtet der update-Anomalien mit sich brachte - ist ein Bekenntnis zu einer bestimmten verbinden. Wenn Sie eine verschiedenen beitreten, dass sich Engagement Kosten würde, um Sie großen.
Wenn jemand will, um mich daran zu erinnern, dass es die Welt verändert sich, ich glaube, du wirst feststellen, dass größere Datensätze auf gruntier hardware nur übertreibt die Ausbreitung von Datum Erkenntnisse.
Für alle von Euch, die Arbeit auf billing-Systeme oder junk-mail-Generatoren (Schande über Sie) und sind empört Einstellung, hand auf der Tastatur, um mir zu sagen, dass Sie wissen, für eine Tatsache, dass der entnormierung ist schneller, sorry, aber du lebst in einer von den besonderen Fällen, speziell in den Fällen, in denen Sie Prozess alle von den Daten in Ordnung. Ist es nicht ein allgemeiner Fall, und Sie sind gerechtfertigt in Ihrer Strategie.
Sind Sie nicht gerechtfertigt fälschlicherweise generalisieren es. Finden Sie am Ende der notes-Abschnitt für weitere Informationen über die geeignete Nutzung der entnormierung im data warehousing-Szenarien.
Ich würde auch gerne geantwortet
Was für eine Last von Mist. Einschränkungen sind so früh wie möglich, die meisten restriktiven erste. Sie haben gelesen, die Theorie, aber haben Sie nicht verstanden. Joins sind behandelt als "kartesische Produkte, für die die Prädikate anwenden" nur von der Abfrage-Optimierer. Dies ist eine symbolische Darstellung (eine Normalisierung, in der Tat) zu erleichtern symbolischen Zersetzung, so dass der Optimierer kann produzieren alle gleichwertig-Transformationen und ordnen Sie Sie, indem Sie Kosten und Selektivität, so dass Sie können wählen Sie den besten Abfrageplan.
Der einzige Weg Sie jemals bekommen die Optimierungstools erzeugen ein Kartesisches Produkt wird scheitern, um die Versorgung ein Prädikat:
SELECT * FROM A,BHinweise
David Aldridge bietet einige wichtige zusätzliche Informationen.
Es ist in der Tat eine Vielzahl von anderen Strategien neben Indizes und Tabelle durchsucht, und einem modernen Compiler, die Kosten werden Ihnen alle vor der Herstellung einen Ausführungsplan.
Einen praktischen Ratschlag: wenn es verwendet werden kann als Fremdschlüssel dann indiziert ist, so dass eine index-Strategie ist verfügbar zu den optimiser.
Ich schlauer als der MSSQL-Optimierungstools. Das änderte sich zwei Versionen vor. Jetzt ist es in der Regel lehrt mir. Es ist in einem sehr realen Sinn, ein Experte system, Kodifizierung aller Weisheit der vielen sehr klugen Menschen in einer Domäne ausreichend geschlossen, dass ein Regel-basiertes system ist wirksam.
"Eier" sich möglicherweise taktlos. Ich bin gefragt zu werden, weniger hochmütig und erinnert daran, dass die Mathematik lügt nicht. Das ist wahr, aber nicht alle Auswirkungen von mathematischen Modellen sollte unbedingt wörtlich genommen werden. Quadrat-Wurzeln von negativ-zahlen sind sehr praktisch, wenn Sie sorgfältig vermeiden, die Untersuchung Ihrer Absurdität (Wortspiel) und machen verdammt sicher, dass Sie Abbrechen, Sie alle, bevor Sie versuchen, Sie zu interpretieren Ihre Gleichung.
Dem Grund, dass ich reagierte so aggressiv war, dass die Aussage so formuliert, sagt, dass
Dies kann nicht sein, was gemeint war, aber es istwas geschrieben wurde, und es ist kategorisch falsch. Ein Kartesisches Produkt ist eine relation. Ein join ist eine Funktion. Genauer gesagt, ein join ist eine relation-valued function. Mit leerem Prädikat erzeugt ein Kartesisches Produkt, und überprüfen, dass es funktioniert, so ist eine Verträglichkeitsprüfung für eine Datenbank-Abfrage-engine, aber niemand schreibt zwanglos schließt sich in der Praxis, denn Sie haben keinen praktischen Wert außerhalb eines Klassenzimmers.
Rief ich, weil ich nicht wollen, dass der Leser fallen in die alte Falle, zu verwirrend das Modell mit dem Ding modelliert. Ein Modell ist eine Annäherung, absichtlich vereinfacht, für eine bequeme Handhabung.
Den cut-off für die Auswahl einer Tabelle-scan join-Strategie kann variieren zwischen Datenbank-engines. Es wird beeinflußt von einer Reihe von Durchführungsmaßnahmen, wie Baum-Knoten fill-Faktor, Schlüssel-Wert-Größe und Feinheiten des Algorithmus, sondern ganz allgemein gesprochen, high-performance-Indizierung hat eine Ausführungszeit von k log n + c. Der C-term ist eine Feste overhead-meist aus der setup-Zeit und die Form der Kurve bedeutet, dass Sie nicht bekommen eine Belohnung (im Vergleich zu einer linearen Suche), bis n in die Hunderte.
Manchmal entnormierung ist eine gute Idee
Entnormierung ist ein Bekenntnis zu einem bestimmten join-Strategie. Wie bereits erwähnt, dies stört anderen join-Strategien. Aber wenn man die Eimer von Speicherplatz, vorhersagbare Muster von access, und eine Tendenz zu verarbeiten, viel oder alle, dann precomputing ein join kann sehr lohnend.
Können Sie auch herausfinden, die Zugriffspfade Ihrem Betrieb in der Regel verwendet, und vorausberechnen alle Verknüpfungen für diese Zugriffspfade. Dies ist die Prämisse hinter data warehouses, oder zumindest ist es, wenn Sie gebaut von Menschen, die wissen, warum Sie tun, was Sie tun, und nicht nur im Interesse der buzzword compliance.
Einer richtig entworfenen data-warehouse-produziert wird in regelmäßigen Abständen von einem bulk-transformation aus einer normalisierten transaction processing system. Diese Trennung von operations-und reporting-Datenbanken hat sich der sehr wünschenswerten Effekt zu beseitigen, der Konflikt zwischen OLTP-und OLAP - (online transaction processing, ie Dateneingabe und online-analytical processing ie-reporting).
Ein wichtiger Punkt ist hier, dass abgesehen von der regelmäßigen updates, das data warehouse ist nur Lesen. Das macht die Volksversammlung die Frage zu update-Anomalien.
Nicht den Fehler machen, denormalising Ihrem OLTP-Datenbank (die Datenbank, auf die Dateneingabe geschieht). Es könnte schneller sein, für die Abrechnung läuft aber wenn Sie das tun, erhalten Sie update-Anomalien. Jemals versucht zu Holen, Reader ' s Digest zu stoppen senden Sie Sachen?
Speicherplatz ist heutzutage Billig, also tobt Euch aus. Aber denormalising ist nur ein Teil der Geschichte für data warehouses. Viel größere performance-Gewinne ergeben sich aus vorausberechnete rolled-up-Werte: monatliche Summen, diese Art der Sache. Es ist immer über die Verringerung des working set.
ADO.NET problem mit Typ-Abweichungen
Angenommen, Sie haben eine SQL Server-Tabelle mit einer indizierten Spalte vom Typ varchar, und verwenden Sie AddWithValue ein parameter übergeben werden Zwangsbedingungen eine Abfrage auf diese Spalte. C# - strings sind Unicode, so dass der abgeleitete parameter Typ NVARCHAR, die nicht mit VARCHAR.
VARCHAR, NVARCHAR ist eine erweiternde Konvertierung, so kommt es implizit - aber sagen Sie auf Wiedersehen zu indizieren, und auf gut Glück arbeiten Sie heraus, warum.
"Zählen die Scheibe trifft" (Rick James)
Wenn alles im Arbeitsspeicher zwischengespeichert,
JOINssind eher Billig. Das ist die Normalisierung nicht viel Leistungseinbußen.Wenn ein "normalisiert" - schema Ursachen
JOINszu schlagen, die Festplatte eine Menge, aber die entsprechende "denormalisierten" - schema würde nicht getroffen habe, um die Festplatte, dann Denormalisierung gewinnt einen performance-Wettbewerb.InformationsquelleAutor der Antwort
Was die meisten Kommentatoren versäumen, zu beachten ist das Breite Spektrum der Verknüpfung von Methoden in einem komplexen RDBMS, und der denormalisers immer beschönigen die höheren Kosten für die Aufrechterhaltung denormalised Daten. Nicht jeder join basiert auf Indizes und Datenbanken haben eine Menge von optimierten algotithms und Methoden für den Beitritt, die dazu bestimmt sind, zu verringern beitreten Kosten.
In jedem Fall die Kosten für eine Verknüpfung ist, hängt von seiner Art und ein paar andere Faktoren. Es muss nicht teuer sein an alle - einige Beispiele.
Datenbanken sind entworfen, um zu verbinden, und Sie sind sehr flexibel, wie Sie es tun und in der Regel sehr performant, es sei denn, Sie erhalten die join-Mechanismus falsch.
InformationsquelleAutor der Antwort David Aldridge
Ich denke, die ganze Frage beruht auf einer falschen Prämisse. Verknüpfungen auf großen Tischen sind nicht unbedingt teuer. In der Tat, tun verbindet effizient ist einer der wichtigsten Gründe für relationale Datenbanken existieren überhaupt. Verknüpfungen auf große setzt sind oft teuer, aber sehr selten haben Sie beitreten wollen, werden die gesamten Inhalte von großen Tisch Ein mit dem gesamten Inhalt der großen Tabelle B. Anstatt, schreiben Sie die Abfrage so, dass nur die wichtigen Zeilen jeder Tabelle benutzt werden und der eigentliche Satz gehalten, die durch die join-bleibt kleiner.
Darüber hinaus haben Sie die Wirkungsgrade erwähnt von Peter Wone, so dass nur die wichtigen Teile von jeder Datensatz benötigen, werden diese im Speicher, bis die endgültige Ergebnismenge materialisiert ist. Auch bei großen Abfragen mit vielen " joins möchten Sie in der Regel beginnen Sie mit den kleineren Tisch-sets und arbeiten Sie Ihren Weg bis zu den großen, so daß der Satz im Speicher gehalten, möglichst klein bleibt, so lange wie möglich.
Wenn es richtig getan wird, schließt im Allgemeinen die beste Weg zu vergleichen, zu verbinden oder filter auf große Datenmengen.
InformationsquelleAutor der Antwort Joel Coehoorn
Den Engpass ist ziemlich viel immer disk-I/O-und mehr noch speziell - zufällige Datenträger-E/A (im Vergleich sequenzielle Lesevorgänge sind ziemlich schnell und können zwischengespeichert werden, mit read-ahead-Strategien).
Verbindet kann erhöhen random sucht - wenn Sie herumspringen, Lesen, kleine Teile einer großen Tabelle. Aber, Abfrage-Optimierer suchen, und verwandelt es in einen sequenziellen table scan (verwerfen Sie die nicht benötigten Zeilen), wenn es denkt, das wäre besser.
Einem einzigen denormalisierten Tabelle hat ein ähnliches problem - die Zeilen sind groß, und so weniger passen auf eine einzelne Daten-Seite. Wenn Sie brauchen, Zeilen, die sich fernab von anderen (und die große Reihe Größe macht Sie weiter auseinander), dann haben Sie mehr random-I/O. Wieder, ein table scan kann gezwungen werden, dies zu vermeiden. Aber, dieses mal, Ihre table scan zu Lesen hat mehr Daten, da eine große Reihe Größe. Hinzu kommt die Tatsache, dass Sie kopieren von Daten von einem einzigen Standort aus an mehreren Standorten, und das RDBMS hat, viel mehr zu Lesen (und cache).
Mit 2 Tabellen, erhalten Sie auch 2 gruppierte Indizes - und kann in der Regel index mehr (weniger wegen der insert - /update-Aufwand), kann man Sie drastisch erhöhte Leistung (vor allem, mal wieder, denn Indizes sind (relativ) kleine, schnell zu Lesen vom Datenträger (oder Billig-cache), und verringern Sie die Menge der Zeilen, die Sie brauchen, um zu Lesen von der Festplatte).
Nur über das overhead mit einem join kommt von herauszufinden, die passenden Zeilen. Sql Server verwendet 3 verschiedene Arten von Verknüpfungen, die hauptsächlich auf dataset-Größen, hier finden Sie die passenden Zeilen. Wenn der Optimierer wählt die falsche join-Typ (aufgrund der ungenauen Statistiken, Unzureichende Indizes, oder einfach nur ein optimizer-bug oder edge-Fall) es kann drastisch beeinflussen Abfrage mal.
Im optimalen Fall werden diese verursachen keine disk-I/O - und so sind vernachlässigbar im Hinblick auf die Leistung.
Alles in allem, im schlimmsten Fall - es sollte eigentlich schneller sein, zum Lesen der gleichen Menge an logische Daten von x-verknüpften Tabellen, wie es ist von einer einzigen denormalisierten Tabelle wegen der kleineren Festplatte liest. Zum Lesen der gleichen Menge an körperliche Daten, könnte es einige geringfügige overhead.
Da die Abfrage ist in der Regel dominiert von I/O-Kosten und die Größe der Daten nicht verändern (minus einige sehr winzige Zeile overhead) mit Denormalisierung, es gibt nicht eine riesige Menge von nutzen zu sein, hatte bloß das Zusammenführen von Tabellen zusammen. Die Art der Denormalisierung, die dazu neigt, um die Leistung zu erhöhen, IME, ist die Zwischenspeicherung von berechneten Werten zu Lesen, anstatt die 10.000 Zeilen benötigt, um zu berechnen.
InformationsquelleAutor der Antwort Mark Brackett
Die Reihenfolge, in der Sie die Tabellen verknüpfen, äußerst wichtig ist. Wenn Sie zwei Sätze von Daten versuchen Sie die Abfrage erstellen in einer Weise, so dass die kleinsten zuerst verwendet wird, zu reduzieren die Menge der Daten, die die Abfrage hat zu arbeiten.
Für einige Datenbanken ist es egal, z.B. MS SQL kennt die korrekte join-Reihenfolge die meisten der Zeit.
Für einige (wie IBM, Informix) die Reihenfolge macht den Unterschied.
InformationsquelleAutor der Antwort Ilya Kochetov
Entscheiden, ob denormalize oder normalisieren ist eine ziemlich einfacher Prozess, wenn Sie überlegen, die Komplexität der Klasse der Verknüpfung. Zum Beispiel, Neige ich dazu, das design für meine Datenbanken, Normalisierung, wenn die Abfragen werden in O(k log n), wo k ist in Bezug auf das gewünschte Ausgabe-Größe.
Einen einfachen Weg, um denormalize und die Leistung zu optimieren ist zu überlegen, wie änderungen an Ihrem normalisieren Struktur auf Ihre denormalisierten Struktur. Es kann problematisch sein, aber wie kann er verlangen, Transaktions-Logik arbeiten auf einer denormalisierten strukturiert.
Die Debatte um Normalisierung und Denormalisierung ist nicht zu Ende, da die Probleme sind riesig. Es gibt viele Probleme, bei denen die Natürliche Lösung erfordert beide Ansätze.
Als eine Allgemeine Regel, ich habe immer gespeichert, um eine normalisierte Struktur und denormalisierte caches rekonstruiert werden können. Schließlich werden diese caches speichern meinen Arsch zu lösen, die Zukunft Normalisierung Probleme.
InformationsquelleAutor der Antwort MathGladiator
Erarbeiten, was andere gesagt haben,
Joins werden nur kartesische Produkte mit etwas lipgloss. {1,2,3,4}X{1,2,3} wäre, geben Sie uns 12 Kombinationen (nXn=n^2). Der berechnete Satz dient als Referenz auf die Bedingungen angewendet werden. Das DBMS wendet die Bedingungen (wie, wo Links und rechts sind 2 oder 3), um uns die passende Bedingung(en). Eigentlich ist es mehr optimiert, aber das problem ist das gleiche. Die änderungen an der Größe der sets erhöhen würde das Ergebnis der Größe exponentiell. Die Höhe der Speicher-und cpu-Zyklen verbraucht, alle sind erfolgt in der exponentiellen Bedingungen.
Wenn wir denormalise, vermeiden wir diese Berechnung insgesamt, daran denken, eine farbige, klebrige, an jeder Seite in Ihrem Buch. Sie können ableiten, die information mit einer Referenz. Die Strafe, die wir zahlen ist, sind wir dabei die Essenz des DBMS (optimale organisation von Daten)
InformationsquelleAutor der Antwort questzen