Join vs. Unterabfrage

Question

Ich bin ein MySQL-Anwender der alten Schule und habe immer die JOIN über die Unterabfrage. Aber heutzutage verwendet jeder Sub-Query, und ich hasse es; ich weiß nicht, warum.

Mir fehlt das theoretische Wissen, um selbst zu beurteilen, ob es einen Unterschied gibt. Ist eine Sub-Query genauso gut wie eine JOIN und gibt es deshalb keinen Grund zur Sorge?

Answer 1

Unterabfragen sind der logisch korrekte Weg, um Probleme der Form "Hole Fakten von A, bedingt durch Fakten von B" zu lösen. In solchen Fällen ist es logisch sinnvoller, B in eine Unterabfrage aufzunehmen als eine Verknüpfung durchzuführen. Auch in praktischer Hinsicht ist dies sicherer, da Sie nicht darauf achten müssen, dass Sie aufgrund mehrerer Übereinstimmungen mit B doppelte Fakten aus A erhalten.

In der Praxis ist die Antwort jedoch in der Regel eine Frage der Leistung. Einige Optimierer sind schlecht, wenn sie eine Verknüpfung mit einer Unterabfrage durchführen, und einige sind umgekehrt schlecht, und dies ist optimiererspezifisch, DBMS-versionsspezifisch und abfragespezifisch.

Historisch gesehen gewinnen explizite Joins in der Regel, daher die gängige Weisheit, dass Joins besser sind, aber die Optimierer werden immer besser, und daher ziehe ich es vor, Abfragen zunächst in einer logisch kohärenten Weise zu schreiben und dann umzustrukturieren, wenn Leistungseinschränkungen dies rechtfertigen.

Answer 2

In den meisten Fällen JOIN s sind schneller als Unterabfragen und es ist sehr selten, dass eine Unterabfrage schneller ist.

Sur JOIN Das RDBMS kann einen Ausführungsplan erstellen, der für Ihre Abfrage besser geeignet ist und vorhersagen kann, welche Daten zur Verarbeitung geladen werden sollten, um Zeit zu sparen, im Gegensatz zur Unterabfrage, bei der alle Abfragen ausgeführt und alle Daten zur Verarbeitung geladen werden.

Das Gute an Unterabfragen ist, dass sie besser lesbar sind als JOIN s: deshalb bevorzugen die meisten SQL-Neulinge sie; es ist der einfache Weg; aber wenn es um die Leistung geht, sind JOINS in den meisten Fällen besser, obwohl sie auch nicht schwer zu lesen sind.

Answer 3

Entnommen aus dem MySQL-Handbuch ( 13.2.10.11 Umschreiben von Unterabfragen als Joins ):

Ein LEFT [OUTER] JOIN kann schneller sein als eine äquivalente Unterabfrage, weil der Server in der Lage sein könnte, sie besser zu optimieren - eine Tatsache, die nicht nur für MySQL spezifisch ist.

Daher können Unterabfragen langsamer sein als LEFT [OUTER] JOIN Meiner Meinung nach liegt ihre Stärke jedoch in der etwas besseren Lesbarkeit.

Answer 4

Im Jahr 2010 hätte ich mich dem Autor dieser Fragen angeschlossen und hätte mit Nachdruck für JOIN aber mit viel mehr Erfahrung (vor allem in MySQL) kann ich feststellen: Ja, Subqueries können besser sein. Ich habe mehrere Antworten hier gelesen; einige sagten, Unterabfragen seien schneller, aber es fehlte eine gute Erklärung. Ich hoffe, ich kann mit dieser (sehr) späten Antwort eine liefern:

Lassen Sie mich zunächst das Wichtigste sagen: Es gibt verschiedene Formen von Unterabfragen

Und die zweite wichtige Aussage: Auf die Größe kommt es an

Wenn Sie Unterabfragen verwenden, sollten Sie aufmerksam sein wie der DB-Server die Sub-Query ausführt. Insbesondere ob die Sub-Query einmal oder für jede Zeile ausgewertet wird! Auf der anderen Seite ist ein moderner DB-Server in der Lage, eine Menge zu optimieren. In manchen Fällen hilft eine Subquery bei der Optimierung einer Abfrage, aber eine neuere Version des DB-Servers könnte die Optimierung obsolet machen.

Unterabfragen in Select-Feldern

SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo

Beachten Sie, dass eine Unterabfrage für jede Ergebniszeile von foo .
Vermeiden Sie dies nach Möglichkeit; es kann Ihre Abfrage bei großen Datensätzen drastisch verlangsamen. Wenn die Unterabfrage jedoch keinen Verweis auf foo kann er vom DB-Server als statischer Inhalt optimiert und nur einmal ausgewertet werden.

Unterabfragen in der Where-Anweisung

SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)

Wenn Sie Glück haben, optimiert die DB dies intern in eine JOIN . Andernfalls wird Ihre Abfrage bei großen Datensätzen sehr, sehr langsam, da sie die Unterabfrage für jede Zeile in foo und nicht nur die Ergebnisse wie bei der Auswahlart.

Unterabfragen in der Join-Anweisung

SELECT moo, bar 
  FROM foo 
    LEFT JOIN (
      SELECT MIN(bar), me FROM wilco GROUP BY me
    ) ON moo = me

Das ist interessant. Wir kombinieren JOIN mit einer Unterabfrage. Und hier zeigt sich die wahre Stärke von Unterabfragen. Stellen Sie sich einen Datensatz mit Millionen von Zeilen in wilco aber nur wenige eindeutige me . Anstatt eine große Tabelle zu verknüpfen, haben wir jetzt eine kleinere temporäre Tabelle, mit der wir verknüpfen können. Dies kann je nach Größe der Datenbank zu wesentlich schnelleren Abfragen führen. Sie können den gleichen Effekt mit CREATE TEMPORARY TABLE ... y INSERT INTO ... SELECT ... die bei sehr komplexen Abfragen eine bessere Lesbarkeit bieten kann (aber Datensätze in einer wiederholbaren Leseisolationsebene sperren kann).

Verschachtelte Unterabfragen

SELECT VARIANCE(moo)
  FROM (
    SELECT moo, CONCAT(roger, wilco) AS bar
      FROM foo
      HAVING bar LIKE 'SpaceQ%'
  ) AS temp_foo
  GROUP BY moo

Sie können Unterabfragen in mehreren Ebenen verschachteln. Dies kann bei großen Datenmengen hilfreich sein, wenn Sie die Ergebnisse gruppieren oder ändern müssen. Normalerweise erstellt der DB-Server dafür eine temporäre Tabelle, aber manchmal braucht man einige Operationen nicht auf der gesamten Tabelle, sondern nur auf der Ergebnismenge. Abhängig von der Größe der Tabelle kann dies eine viel bessere Leistung bringen.

結論

Unterabfragen sind kein Ersatz für eine JOIN und Sie sollten sie nicht auf diese Weise verwenden (obwohl dies möglich ist). Meiner bescheidenen Meinung nach ist die korrekte Verwendung einer Sub-Query die Verwendung als schneller Ersatz von CREATE TEMPORARY TABLE ... . Eine gute Sub-Query reduziert einen Datensatz auf eine Weise, die Sie mit einer ON Aussage eines JOIN . Wenn eine Unterabfrage eines der Schlüsselwörter GROUP BY o DISTINCT und vorzugsweise nicht in den Select-Feldern oder in der Where-Anweisung liegt, dann könnte dies die Leistung erheblich verbessern.

Answer 5

Verwenden Sie EXPLAIN, um zu sehen, wie Ihre Datenbank die Abfrage an Ihren Daten ausführt. In dieser Antwort steckt ein großes "es kommt darauf an"...

PostgreSQL kann eine Subquery in einen Join oder einen Join in eine Subquery umschreiben, wenn es glaubt, dass das eine schneller ist als das andere. Das hängt von den Daten, den Indizes, der Korrelation, der Menge der Daten, der Abfrage usw. ab.