mysql - tables - outer join sql




In welcher Reihenfolge werden MySQL JOINs ausgewertet? (5)

Ich habe folgende Abfrage:

SELECT c.*
FROM companies AS c
JOIN users AS u USING(companyid)
JOIN jobs AS j USING(userid)
JOIN useraccounts AS us USING(userid)
WHERE j.jobid = 123;

Ich habe folgende Fragen:

  1. Ist die Syntax USING synonym mit der Syntax ON?
  2. Werden diese Joins von links nach rechts ausgewertet? Mit anderen Worten, sagt diese Abfrage: x = Firmen JOIN Benutzer; y = x JOIN-Jobs; z = y JOIN Benutzeraccounts;
  3. Wenn die Antwort auf Frage 2 Ja lautet, kann davon ausgegangen werden, dass die Unternehmenstabelle die Spalten companyid, userid und jobid enthält?
  4. Ich verstehe nicht, wie die WHERE-Klausel verwendet werden kann, um Zeilen in der companies-Tabelle auszuwählen, wenn sie sich auf den Alias ​​"j" bezieht.

Jede Hilfe wäre willkommen!


  1. USING (Feldname) ist eine Abkürzung für ON table1.fieldname = table2.fieldname.

  2. SQL definiert nicht die 'Reihenfolge', in der JOINS ausgeführt werden, da es nicht die Art der Sprache ist. Natürlich muss eine Anweisung in der Anweisung angegeben werden, aber ein INNER JOIN kann als kommutativ betrachtet werden: Sie können sie in beliebiger Reihenfolge auflisten und Sie erhalten die gleichen Ergebnisse.

    Beim Erstellen eines SELECT ... JOIN, insbesondere eines, der LEFT JOINs enthält, habe ich jedoch festgestellt, dass es sinnvoll ist, den dritten JOIN als Beitritt der neuen Tabelle zu den Ergebnissen des ersten JOIN, des vierten JOIN als Beitritt zu betrachten Ergebnisse des zweiten JOIN und so weiter.

    In seltenen Fällen kann die angegebene Reihenfolge das Verhalten des Abfrageoptimierers beeinflussen, da er die Heuristik beeinflusst.

  3. Nein. Die Art und Weise, wie die Abfrage zusammengestellt wird, erfordert, dass Unternehmen und Benutzer beide über eine Firmen-ID verfügen, Jobs über eine Benutzer-ID und eine Job-ID verfügen und useraccounts über eine Benutzer-ID verfügt. Jedoch benötigt nur eine der Firmen oder Benutzer eine Benutzer-ID, damit JOIN funktioniert.

  4. Die WHERE-Klausel filtert das gesamte Ergebnis - dh alle JOINed-Spalten - unter Verwendung einer Spalte, die von der Job-Tabelle zur Verfügung gestellt wird.


1) Verwenden ist nicht genau das Gleiche wie auf, aber es ist kurze Hand, wo beide Tabellen eine Spalte mit dem gleichen Namen haben, an dem Sie sich anschließen ... siehe: website

Es ist meiner Meinung nach schwieriger zu lesen, also schreibe ich die Joins aus.

3) Es ist nicht klar aus dieser Abfrage, aber ich würde vermuten, dass es nicht.

2) Angenommen, Sie gehen durch die anderen Tabellen (nicht alle direkt auf Unternehmen) die Reihenfolge in dieser Abfrage ist von Bedeutung ... siehe Vergleich unten:

Ursprung:

SELECT c.* 
    FROM companies AS c 
    JOIN users AS u USING(companyid) 
    JOIN jobs AS j USING(userid) 
    JOIN useraccounts AS us USING(userid) 
WHERE j.jobid = 123

Was ich denke, ist es wahrscheinlich:

SELECT c.* 
    FROM companies AS c 
    JOIN users AS u on u.companyid = c.companyid
    JOIN jobs AS j on j.userid = u.userid
    JOIN useraccounts AS us on us.userid = u.userid 
WHERE j.jobid = 123

Sie können hier die Zeilen wechseln, die den Jobs & usersaccounts beitreten.

Wie es aussehen würde, wenn sich alles mit der Firma verbindet:

SELECT c.* 
    FROM companies AS c 
    JOIN users AS u on u.companyid = c.companyid
    JOIN jobs AS j on j.userid = c.userid
    JOIN useraccounts AS us on us.userid = c.userid
WHERE j.jobid = 123

Dies ergibt keinen logischen Sinn ... es sei denn, jeder Benutzer hat seine eigene Firma.

4.) Die Magie von SQL ist, dass Sie nur bestimmte Spalten anzeigen können, aber alle sind sie zum Sortieren und Filtern ...

wenn du zurückkehrst

SELECT c.*, j.jobid....  

Sie können deutlich sehen, auf was gefiltert wurde, aber dem Datenbankserver ist es egal, ob Sie eine Zeile zum Filtern ausgeben oder nicht.


Ich bin mir nicht sicher über den ON vs USING Teil (obwohl diese website sagt, dass sie gleich sind)

Was die Bestellfrage angeht, ist ihre vollständige Implementierung (und wahrscheinlich die Abfrage) spezifisch. MYSQL wählt wahrscheinlich eine Reihenfolge aus, wenn die Anforderung kompiliert wird. Wenn Sie eine bestimmte Bestellung erzwingen möchten, müssen Sie Ihre Abfragen "verschachteln":

SELECT c.*
FROM companies AS c 
    JOIN (SELECT * FROM users AS u 
        JOIN (SELECT * FROM  jobs AS j USING(userid) 
              JOIN useraccounts AS us USING(userid) 
              WHERE j.jobid = 123)
    )

Wie für Teil 4: Die where-Klausel begrenzt, welche Zeilen aus der Job-Tabelle für die Teilnahme an JOINed berechtigt sind. Wenn also Zeilen vorhanden sind, die aufgrund der übereinstimmenden Benutzer-IDs hinzugefügt werden, aber nicht über die richtige Job-ID verfügen, werden sie weggelassen.


Ich kann das Bit über die USING-Syntax nicht beantworten. Das ist komisch. Ich habe es vorher nie gesehen, immer eine ON-Klausel verwendet.

Aber was ich Ihnen sagen kann, ist, dass die Reihenfolge der JOIN-Operationen dynamisch vom Abfrageoptimierer bestimmt wird, wenn es seinen Abfrageplan erstellt, basierend auf einem System von Optimierungsheuristiken, von denen einige sind:

  1. Wird der JOIN auf einem Primärschlüsselfeld ausgeführt? Wenn dies der Fall ist, erhält dies eine hohe Priorität im Abfrageplan.

  2. Wird der JOIN auf einem fremden Schlüsselfeld ausgeführt? Dies hat auch hohe Priorität.

  3. Existiert ein Index für das verbundene Feld? Wenn dies der Fall ist, erhöhen Sie die Priorität.

  4. Wird eine JOIN-Operation für ein Feld in der WHERE-Klausel ausgeführt? Kann der WHERE-Klauselausdruck ausgewertet werden, indem der Index untersucht wird (anstatt einen Tabellenscan durchzuführen)? Dies ist eine große Optimierungsmöglichkeit, so dass es eine große Priorität hat.

  5. Was ist die Kardinalität der verbundenen Spalte? Spalten mit hoher Kardinalität geben dem Optimierer mehr Möglichkeiten, falsche Übereinstimmungen zu unterscheiden (solche, die die WHERE-Klausel oder die ON-Klausel nicht erfüllen), so werden Joins mit hoher Kardinalität normalerweise verarbeitet, bevor Kardinalitäten mit geringem Kardinalwert hinzugefügt werden.

  6. Wie viele tatsächliche Zeilen befinden sich in der verbundenen Tabelle? Die Verbindung mit einer Tabelle mit nur 100 Werten führt zu weniger Datenexplosion als die Verbindung mit einer Tabelle mit zehn Millionen Zeilen.

Wie auch immer ... der Punkt ist ... es gibt eine Menge Variablen, die in den Abfrageausführungsplan eingehen. Wenn Sie sehen möchten, wie MySQL seine Abfragen optimiert, verwenden Sie die EXPLAIN-Syntax.

Und hier ist ein guter Artikel zu lesen:

http://www.informit.com/articles/article.aspx?p=377652

EIN BEARBEITEN:

Um Ihre vierte Frage zu beantworten: Sie fragen nicht die Tabelle "Firmen" ab. Sie fragen das verknüpfte Kreuzprodukt ALLER vier Tabellen in Ihren FROM- und USING-Klauseln ab.

Der Alias ​​"j.jobid" ist nur der vollständig qualifizierte Name einer der Spalten in dieser verknüpften Auflistung von Tabellen.


Siehe http://dev.mysql.com/doc/refman/5.0/en/join.html

UND beginnen Sie hier zu lesen:

Bearbeitungsänderungen in MySQL 5.0.12

Ab MySQL 5.0.12 werden natürliche Joins und Joins mit USING, einschließlich Outer Join-Varianten, gemäß dem SQL: 2003-Standard verarbeitet. Ziel war es, die Syntax und Semantik von MySQL in Bezug auf NATURAL JOIN und JOIN ... USING gemäß SQL: 2003 auszurichten. Diese Änderungen in der Join-Verarbeitung können jedoch zu unterschiedlichen Ausgabespalten für einige Joins führen. Außerdem müssen einige Abfragen, die in älteren Versionen korrekt zu funktionieren scheinen, neu geschrieben werden, um dem Standard zu entsprechen.

Diese Änderungen haben fünf Hauptaspekte:

  • Die Art, wie MySQL die Ergebnisspalten von NATURAL- oder USING-Join-Operationen bestimmt (und somit das Ergebnis der gesamten FROM-Klausel).

  • Erweiterung von SELECT * und SELECT tbl_name. * In eine Liste ausgewählter Spalten.

  • Auflösung von Spaltennamen in NATURAL- oder USING-Joins.

  • Die Transformation von NATURAL oder USING verbindet sich in JOIN ... ON.

  • Auflösung von Spaltennamen im ON-Zustand eines JOIN ... ON.







join