MySQL Join-Klausel vs. WHERE-Klausel

Question

Was ist der Unterschied in einer Klausel, die auf die beiden folgenden Arten gemacht wird?

SELECT * FROM table1 INNER JOIN table2 ON (
    table2.col1 = table1.col2 AND
    table2.member_id = 4
)

Ich habe sie beide mit einfachen Abfragen verglichen und EXPLAIN EXTENDED und sehen keinen Unterschied. Ich frage mich, ob jemand hier einen Unterschied in einer komplexeren/verarbeitungsintensiven Umgebung entdeckt hat.

SELECT * FROM table1 INNER JOIN table2 ON (
    table2.col1 = table1.col2
)
WHERE table2.member_id = 4

Answer 1

Mit einem INNER join liefern die beiden Ansätze identische Ergebnisse und sollten denselben Abfrageplan ergeben.

Es gibt jedoch einen semantischen Unterschied zwischen einem JOIN (der eine Beziehung zwischen zwei Tabellen beschreibt) und einer WHERE-Klausel (die Zeilen aus der Ergebnismenge entfernt). Dieser semantische Unterschied sollte Ihnen sagen, welche Klausel Sie verwenden sollten. Es macht zwar keinen Unterschied für das Ergebnis oder die Leistung, aber die Wahl der richtigen Syntax hilft anderen Lesern Ihres Codes, ihn schneller zu verstehen.

Beachten Sie, dass es puede einen Unterschied machen, wenn Sie eine äußere Verknüpfung anstelle einer inneren Verknüpfung verwenden. Wenn Sie z. B. INNER in LEFT ändern und die Join-Bedingung fehlschlägt, würden Sie bei der ersten Methode immer noch eine Zeile erhalten, bei der zweiten Methode würde sie jedoch weggefiltert werden (weil NULL nicht gleich 4 ist).

Answer 2

Wenn Sie versuchen, Ihre Daten zu optimieren und zu kennen, können Sie durch Hinzufügen der Klausel "STRAIGHT_JOIN" die Leistung enorm verbessern. Sie haben einen inneren Join ON... Nur zur Bestätigung: Sie wollen nur Datensätze, bei denen Tabelle1 und Tabelle2 verbunden sind, aber nur für Tabelle 2 ist die Mitgliedsnummer ID = ein bestimmter Wert... in diesem Fall 4.

Ich würde die Abfrage dahingehend ändern, dass Tabelle 2 die primäre Tabelle des Selects ist, da sie eine explizite "member_id" hat, die durch einen Index zur Begrenzung der Zeilen optimiert werden könnte, und dann wie folgt mit Tabelle 1 verbunden werden

select STRAIGHT_JOIN
      t1.*
   from
      table2 t2,
      table1 t1
   where 
         t2.member_id = 4
      and t2.col1 = t1.col2

Die Abfrage würde also nur die member_id = 4 Datensätze vorqualifizieren und dann einen Abgleich zwischen Tabelle 1 und 2 vornehmen. Wenn also Tabelle 2 50.000 Datensätze und Tabelle 1 400.000 Datensätze enthält, wird Tabelle 2 zuerst verarbeitet. Begrenzung der ID = 4 noch weniger, und noch weniger, wenn mit Tabelle 1 verbunden.

Ich weiß mit Sicherheit, dass straight_join funktioniert, da ich es viele Male implementiert habe, wenn ich mit Regierungsdaten von mehr als 14 Millionen Datensätzen zu tun hatte, die mit mehr als 15 Nachschlagetabellen verknüpft waren, bei denen die Maschine verwirrt war, weil sie versuchte, für mich an die kritische Tabelle zu denken. Eine solche Abfrage dauerte über 24 Stunden, bevor sie hängen blieb... Durch das Hinzufügen von "STRAIGHT_JOIN" und das Setzen von Prioritäten für die "primäre" Tabelle in der Abfrage wurde eine korrekte Ergebnismenge in weniger als 2 Stunden erzielt.

Answer 3

In der von Ihnen beschriebenen Situation gibt es nicht wirklich einen großen Unterschied; in einer Situation mit mehreren komplexen Verbindungen ist meines Erachtens die erste etwas vorzuziehen, da sie die Komplexität etwas reduziert; abgesehen davon wird es ein kleiner Unterschied sein. Insgesamt sollten Sie in den meisten, wenn nicht in allen Situationen keinen großen Unterschied bemerken.

Answer 4

Bei einer inneren Verknüpfung macht es fast* keinen Unterschied; wenn Sie zu Außen den Unterschied in der Welt ausmachen.

*Ich sage "fast", weil Optimierer eigenwillige Biester sind und es nicht ausgeschlossen ist, dass sie unter bestimmten Umständen das eine oder das andere besser optimieren können. Machen Sie no versuchen, dieses Verhalten auszunutzen.