Join vs. Unterabfrage
Ich bin ein MySQL-Benutzer der alten Schule und habe JOIN immer der Unterabfrage vorgezogen. Heutzutage verwendet jedoch jeder eine Unterabfrage, und ich hasse es. Ich weiß nicht warum.
Mir fehlt das theoretische Wissen, um zu beurteilen, ob es Unterschiede gibt. Ist eine Unterabfrage so gut wie eine JOIN und gibt es daher nichts zu befürchten?
Aus dem MySQL-Handbuch entnommen ( 13.2.10.11 Unterabfragen als Joins umschreiben ):
Ein LEFT [OUTER] JOIN kann schneller sein als eine gleichwertige Unterabfrage, da der Server sie möglicherweise besser optimieren kann - eine Tatsache, die nicht nur für MySQL Server allein gilt.
Unterabfragen können also langsamer sein als LEFT [OUTER] JOINS, aber meiner Meinung nach ist ihre Lesbarkeit etwas besser.
Unterabfragen sind der logisch korrekte Weg, um Probleme des Formulars "Fakten von A, bedingt durch Fakten von B" zu lösen. In solchen Fällen ist es logischer, B in eine Unterabfrage zu setzen, als einen Join durchzuführen. Im praktischen Sinne ist es auch sicherer, da Sie aufgrund mehrfacher Kämpfe gegen B keine doppelten Fakten von A erhalten müssen.
In der Praxis kommt es jedoch meistens auf die Leistung an. Einige Optimierer saugen Zitronen, wenn ein Join gegeben wird, gegenüber einer Unterabfrage, andere saugen Zitronen auf die andere Art und Weise, und dies ist optimiererspezifisch, DBMS-spezifisch und abfragespezifisch.
In der Regel gewinnen explizite Joins normalerweise, daher ist die etablierte Weisheit, dass Joins besser sind, besser. Optimierer werden jedoch immer besser. Daher ziehe ich es vor, Abfragen zuerst logisch kohärent zu schreiben und dann umzustrukturieren, wenn dies aufgrund von Leistungsbeschränkungen erforderlich ist.
In den meisten Fällen sind JOINs schneller als Unterabfragen und es ist sehr selten, dass eine Unterabfrage schneller ist.
In JOINs kann RDBMS einen Ausführungsplan erstellen, der für Ihre Abfrage besser geeignet ist. Sie kann vorhersagen, welche Daten zur Verarbeitung geladen werden sollen, und Zeit sparen. Im Gegensatz zur Unterabfrage werden alle Abfragen ausgeführt und alle Daten geladen, um die Verarbeitung auszuführen .
Das Gute an Unterabfragen ist, dass sie besser lesbar sind als JOINs. Aus diesem Grund bevorzugen die meisten neuen SQL-Benutzer diese. es ist der einfache Weg; Aber wenn es um Leistung geht, sind JOINS in den meisten Fällen besser, auch wenn sie nicht schwer zu lesen sind.
Verwenden Sie EXPLAIN, um zu sehen, wie Ihre Datenbank die Abfrage Ihrer Daten ausführt. Es gibt ein riesiges "es kommt darauf an" in dieser Antwort ...
PostgreSQL kann eine Unterabfrage in eine Verknüpfung oder eine Unterabfrage umschreiben, wenn sie der Meinung ist, dass eine schneller ist als die andere. Es hängt alles von den Daten, Indizes, Korrelation, Datenmenge, Abfrage usw. ab.
Um zuerst die beiden zu vergleichen, sollten Sie Abfragen mit Unterabfragen unterscheiden:
- eine Klasse von Unterabfragen, für die immer eine entsprechende entsprechende Abfrage mit Joins geschrieben wurde
- eine Klasse von Unterabfragen, die nicht mit Joins neu geschrieben werden können
Für die erste Klasse von Abfragen werden in einem guten RDBMS Joins und Unterabfragen als gleichwertig betrachtet und es werden dieselben Abfragepläne erstellt.
Heute macht sogar mysql das.
Manchmal auch nicht, aber dies bedeutet nicht, dass Joins immer gewinnen werden. Ich hatte Fälle, in denen Unterabfragen in mysql die Leistung verbesserten. (Wenn der MySQL-Planer beispielsweise die Kosten nicht richtig einschätzen kann und der Planer die Join-Variante und die Unterabfrage-Variante nicht als gleich sieht, können Unterabfragen die Joins durch Erzwingen eines bestimmten Pfads übertreffen).
Die Schlussfolgerung ist, dass Sie Ihre Abfragen sowohl für Join- als auch für Unterabfragevarianten testen sollten, wenn Sie sicher sein möchten, welche der beiden eine bessere Leistung erbringen soll.
Für die zweite Klasse macht der Vergleich keinen Sinn, da diese Abfragen nicht mit Joins neu geschrieben werden können. In diesen Fällen sind Unterabfragen ein natürlicher Weg, um die erforderlichen Aufgaben auszuführen, und Sie sollten sie nicht diskriminieren.
Im Jahr 2010 wäre ich dem Autor dieser Fragen beigetreten und hätte stark für JOIN gestimmt. Aber mit viel mehr Erfahrung (besonders in MySQL) kann ich feststellen: Ja, Unterabfragen können besser sein. Ich habe hier mehrere Antworten gelesen. In einigen Fällen sind Unterabfragen schneller, es fehlte jedoch eine gute Erklärung. Ich hoffe, ich kann eine (sehr) späte Antwort geben:
Lassen Sie mich zunächst das Wichtigste sagen: Es gibt verschiedene Formen von Unterabfragen
Und die zweite wichtige Aussage: Größe zählt
Wenn Sie Unterabfragen verwenden, sollten Sie wissen, wie der DB-Server die Unterabfrage ausführt. Vor allem, wenn die Unterabfrage einmal oder für jede Zeile ausgewertet wird! Auf der anderen Seite kann ein moderner DB-Server viel optimieren. In einigen Fällen hilft eine Unterabfrage beim Optimieren einer Abfrage, aber eine neuere Version des DB-Servers macht die Optimierung möglicherweise überflüssig.
Unterabfragen in Auswahlfeldern
SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo
Beachten Sie, dass für jede resultierende Zeile von foo eine Unterabfrage ausgeführt wird. Vermeiden Sie dies, wenn möglich, da dies die Abfrage großer Datensätze drastisch verlangsamen kann. Wenn die Unterabfrage jedoch keinen Verweis auf foo hat, kann sie 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. Wenn dies nicht der Fall ist, wird Ihre Abfrage bei großen Datensätzen sehr, sehr langsam, da sie die Unterabfrage für jede Zeile in foo ausführt, nicht nur für die Ergebnisse wie beim Auswahltyp.
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 bekommen wir die wahre Stärke von Unterabfragen. Stellen Sie sich einen Datensatz mit Millionen von Zeilen in wilco vor, aber nur einige wenige me. Anstatt sich gegen einen großen Tisch anzuschließen, haben wir jetzt einen kleineren temporären Tisch, gegen den wir uns anschließen möchten. Dies kann je nach Datenbankgröße zu viel schnelleren Abfragen führen. Mit CREATE TEMPORARY TABLE ... und INSERT INTO ... SELECT ... können Sie dasselbe bewirken, was bei sehr komplexen Abfragen möglicherweise die Lesbarkeit verbessert (aber Datasets in einer wiederholbaren Lese-Isolationsstufe sperren kann).
Verschachtelte Unterabfragen
SELECT moo, bar
FROM (
SELECT moo, CONCAT(roger, wilco) AS bar
FROM foo
GROUP BY moo
HAVING bar LIKE 'SpaceQ%'
) AS temp_foo
GROUP BY bar
ORDER BY bar
Sie können Unterabfragen in mehreren Ebenen verschachteln. Dies kann bei großen Datensätzen hilfreich sein, wenn Sie die Ergebnisse gruppieren oder sortieren müssen. Normalerweise erstellt der DB-Server hierfür eine temporäre Tabelle, aber manchmal müssen Sie nicht nach der gesamten Tabelle, sondern nur nach dem Resultset sortieren. Dies kann abhängig von der Größe der Tabelle eine wesentlich bessere Leistung bieten.
Fazit
Unterabfragen sind kein Ersatz für eine JOIN und sollten nicht auf diese Weise verwendet werden (obwohl dies möglich ist). Nach meiner bescheidenen Meinung ist die korrekte Verwendung einer Unterabfrage die Verwendung als schneller Ersatz von CREATE TEMPORARY TABLE .... Eine gute Unterabfrage reduziert ein Dataset auf eine Weise, die Sie mit einer ON-Anweisung einer JOIN nicht erreichen können. Wenn eine Unterabfrage eines der Schlüsselwörter GROUP BY oder DISTINCT enthält und sich vorzugsweise nicht in den Auswahlfeldern oder der where-Anweisung befindet, kann dies die Leistung erheblich verbessern.
MSDN-Dokumentation für SQL Server sagt
Viele Transact-SQL-Anweisungen, die Unterabfragen enthalten, können alternativ als Joins formuliert werden. Andere Fragen können nur mit Unterabfragen gestellt werden. In Transact-SQL gibt es normalerweise keinen Leistungsunterschied zwischen einer Anweisung, die eine Unterabfrage enthält, und einer semantisch äquivalenten Version, die dies nicht tut. In einigen Fällen, in denen die Existenz geprüft werden muss, führt ein Join jedoch zu einer besseren Leistung. Andernfalls muss die verschachtelte Abfrage für jedes Ergebnis der äußeren Abfrage verarbeitet werden, um die Beseitigung von Duplikaten sicherzustellen. In solchen Fällen würde ein Join-Ansatz zu besseren Ergebnissen führen.
also wenn du sowas brauchst
select * from t1 where exists select * from t2 where t2.parent=t1.id
versuchen Sie stattdessen, join zu verwenden. In anderen Fällen macht es keinen Unterschied.
Ich sage: Durch das Erstellen von funktionen für Unterabfragen wird das Cluttter-Problem behoben, und Sie können zusätzliche Logik für Unterabfragen implementieren. Ich empfehle daher, möglichst Funktionen für Unterabfragen zu erstellen.
Unordnung im Code ist ein großes Problem, und die Industrie arbeitet seit Jahrzehnten daran, es zu vermeiden.
Ich denke, das, was in den zitierten Antworten unterstrichen wurde, ist das Thema Duplikate und problematische Ergebnisse, die sich aus bestimmten (Verwendungs-) Fällen ergeben können.
(obwohl Marcelo Cantos es erwähnt)
Ich werde das Beispiel von Stanfords Lagunita-Kursen zu SQL zitieren.
Studententisch
+------+--------+------+--------+
| sID | sName | GPA | sizeHS |
+------+--------+------+--------+
| 123 | Amy | 3.9 | 1000 |
| 234 | Bob | 3.6 | 1500 |
| 345 | Craig | 3.5 | 500 |
| 456 | Doris | 3.9 | 1000 |
| 567 | Edward | 2.9 | 2000 |
| 678 | Fay | 3.8 | 200 |
| 789 | Gary | 3.4 | 800 |
| 987 | Helen | 3.7 | 800 |
| 876 | Irene | 3.9 | 400 |
| 765 | Jay | 2.9 | 1500 |
| 654 | Amy | 3.9 | 1000 |
| 543 | Craig | 3.4 | 2000 |
+------+--------+------+--------+
Tabelle anwenden
(Bewerbungen an bestimmte Universitäten und Majors)
+------+----------+----------------+----------+
| sID | cName | major | decision |
+------+----------+----------------+----------+
| 123 | Stanford | CS | Y |
| 123 | Stanford | EE | N |
| 123 | Berkeley | CS | Y |
| 123 | Cornell | EE | Y |
| 234 | Berkeley | biology | N |
| 345 | MIT | bioengineering | Y |
| 345 | Cornell | bioengineering | N |
| 345 | Cornell | CS | Y |
| 345 | Cornell | EE | N |
| 678 | Stanford | history | Y |
| 987 | Stanford | CS | Y |
| 987 | Berkeley | CS | Y |
| 876 | Stanford | CS | N |
| 876 | MIT | biology | Y |
| 876 | MIT | marine biology | N |
| 765 | Stanford | history | Y |
| 765 | Cornell | history | N |
| 765 | Cornell | psychology | Y |
| 543 | MIT | CS | N |
+------+----------+----------------+----------+
Versuchen wir, die GPA-Werte für Studenten zu finden, die sich für CS major (unabhängig von der Universität) beworben haben.
Verwenden einer Unterabfrage:
select GPA from Student where sID in (select sID from Apply where major = 'CS');
+------+
| GPA |
+------+
| 3.9 |
| 3.5 |
| 3.7 |
| 3.9 |
| 3.4 |
+------+
Der Durchschnittswert für dieses Resultset ist:
select avg(GPA) from Student where sID in (select sID from Apply where major = 'CS');
+--------------------+
| avg(GPA) |
+--------------------+
| 3.6800000000000006 |
+--------------------+
Verwenden eines Joins:
select GPA from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';
+------+
| GPA |
+------+
| 3.9 |
| 3.9 |
| 3.5 |
| 3.7 |
| 3.7 |
| 3.9 |
| 3.4 |
+------+
durchschnittswert für diese Ergebnismenge:
select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';
+-------------------+
| avg(GPA) |
+-------------------+
| 3.714285714285714 |
+-------------------+
Es ist offensichtlich, dass der zweite Versuch in unserem Anwendungsfall irreführende Ergebnisse liefert, da er für die Berechnung des Durchschnittswerts Duplikate zählt. Es ist auch offensichtlich, dass die Verwendung von distinct mit der join - basierten Anweisung nicht Beseitigen Sie das Problem, da es fälschlicherweise eines von drei Vorkommen der 3.9-Bewertung enthält. Der richtige Fall ist die Berücksichtigung von TWO (2) Vorkommen des 3.9-Scores, vorausgesetzt, wir haben tatsächlich TWO (2) Schüler mit diesem Score, die unseren Abfragekriterien entsprechen.
Es scheint, dass in manchen Fällen eine Unterabfrage neben Leistungsproblemen der sicherste Weg ist.
Führen Sie eine sehr große Datenbank aus einem alten Mambo-CMS aus:
SELECT id, alias
FROM
mos_categories
WHERE
id IN (
SELECT
DISTINCT catid
FROM mos_content
);
0 Sekunden
SELECT
DISTINCT mos_content.catid,
mos_categories.alias
FROM
mos_content, mos_categories
WHERE
mos_content.catid = mos_categories.id;
~ 3 Sekunden
Ein EXPLAIN zeigt, dass sie genau die gleiche Anzahl von Reihen untersuchen, aber eine dauert 3 Sekunden und eine ist fast augenblicklich. Moral der Geschichte? Wenn Leistung wichtig ist (wann nicht?), Probieren Sie es auf verschiedene Weise aus und sehen Sie, welche am schnellsten ist.
Und...
SELECT
DISTINCT mos_categories.id,
mos_categories.alias
FROM
mos_content, mos_categories
WHERE
mos_content.catid = mos_categories.id;
0 Sekunden
Wiederum gleiche Ergebnisse, gleiche Anzahl von Reihen untersucht. Meine Vermutung ist, dass DISTINCT mos_content.catid viel länger dauert als DISTINCT mos_categories.id.
Unterabfragen werden im Allgemeinen verwendet, um eine einzelne Zeile als atomaren Wert zurückzugeben. Sie können jedoch auch verwendet werden, um Werte mit dem IN-Schlüsselwort mit mehreren Zeilen zu vergleichen. Sie sind an fast jeder sinnvollen Stelle in einer SQL-Anweisung zulässig, einschließlich der Zielliste, der WHERE-Klausel usw. Eine einfache Unterabfrage könnte als Suchbedingung verwendet werden. Zum Beispiel zwischen zwei Tabellen:
SELECT title FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Bar' AND first_name = 'Foo');
Beachten Sie, dass bei Verwendung eines Normalwertoperators für die Ergebnisse einer Unterabfrage nur ein Feld zurückgegeben werden muss. Wenn Sie daran interessiert sind, zu überprüfen, ob ein einzelner Wert in einer Reihe anderer Werte vorhanden ist, verwenden Sie IN:
SELECT title FROM books WHERE author_id IN (SELECT id FROM authors WHERE last_name ~ '^[A-E]');
Dies unterscheidet sich offensichtlich von einem LEFT-JOIN, bei dem Sie einfach Sachen aus Tabelle A und B verbinden möchten, selbst wenn die Join-Bedingung in Tabelle B keinen übereinstimmenden Datensatz findet.
Wenn Sie sich nur um die Geschwindigkeit sorgen, müssen Sie Ihre Datenbank überprüfen und eine gute Abfrage schreiben und feststellen, ob es einen erheblichen Leistungsunterschied gibt.
Nach meiner Beobachtung, wie in zwei Fällen, funktioniert der Join schnell, wenn eine Tabelle weniger als 100.000 Datensätze enthält.
Wenn jedoch eine Tabelle mehr als 100.000 Tabellen enthält, ist eine Unterabfrage das beste Ergebnis.
Ich habe eine Tabelle mit 500.000 Datensätzen, die ich unter Abfrage erstellt habe, und die Ergebniszeit ist wie
SELECT *
FROM crv.workorder_details wd
inner join crv.workorder wr on wr.workorder_id = wd.workorder_id;
Ergebnis: 13,3 Sekunden
select *
from crv.workorder_details
where workorder_id in (select workorder_id from crv.workorder)
Ergebnis: 1.65 Sekunden
Unterabfragen haben die Fähigkeit, Aggregationsfunktionen für eine Fliege zu berechnen. Finden Sie den Mindestpreis des Buches und erhalten Sie alle Bücher, die mit diesem Preis verkauft werden. 1) Unterabfragen verwenden:
SELECT titles, price
FROM Books, Orders
WHERE price =
(SELECT MIN(price)
FROM Orders) AND (Books.ID=Orders.ID);
2) Verwenden von JOINs
SELECT MIN(price)
FROM Orders;
-----------------
2.99
SELECT titles, price
FROM Books b
INNER JOIN Orders o
ON b.ID = o.ID
WHERE o.price = 2.99;
MySQL-Version: 5.5.28-0ubuntu0.12.04.2-log
Ich hatte auch den Eindruck, dass JOIN in MySQL immer besser ist als eine Unterabfrage, aber EXPLAIN ist eine bessere Möglichkeit, ein Urteil zu fällen. Hier ist ein Beispiel, bei dem Unterabfragen besser funktionieren als JOINs.
Hier ist meine Abfrage mit 3 Unterabfragen:
EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date
FROM `vote-ranked-listory` vrl
INNER JOIN lists l ON l.list_id = vrl.list_id
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION'
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000
AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=43) IS NULL
AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=55) IS NULL
AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL
ORDER BY vrl.moved_date DESC LIMIT 200;
EXPLAIN zeigt:
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| 1 | PRIMARY | vrl | index | PRIMARY | moved_date | 8 | NULL | 200 | Using where |
| 1 | PRIMARY | l | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where |
| 1 | PRIMARY | vrlih | eq_ref | PRIMARY | PRIMARY | 9 | ranker.vrl.list_id,ranker.vrl.ontology_id,const | 1 | Using where |
| 1 | PRIMARY | lbs | eq_ref | PRIMARY,idx_list_burial_state,burial_score | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | list_tag | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.l.list_id,const | 1 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | list_tag | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.l.list_id,const | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | list_tag | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.l.list_id,const | 1 | Using where; Using index |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
Die gleiche Abfrage mit JOINs lautet:
EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date
FROM `vote-ranked-listory` vrl
INNER JOIN lists l ON l.list_id = vrl.list_id
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION'
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5
LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43
LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55
INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000
AND lt1.list_id IS NULL AND lt2.tag_id IS NULL
ORDER BY vrl.moved_date DESC LIMIT 200;
und die Ausgabe ist:
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | lt3 | ref | list_tag_key,list_id,tag_id | tag_id | 5 | const | 2386 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | l | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY | 4 | ranker.lt3.list_id | 1 | Using where |
| 1 | SIMPLE | vrlih | ref | PRIMARY | PRIMARY | 4 | ranker.lt3.list_id | 103 | Using where |
| 1 | SIMPLE | vrl | ref | PRIMARY | PRIMARY | 8 | ranker.lt3.list_id,ranker.vrlih.ontology_id | 65 | Using where |
| 1 | SIMPLE | lt1 | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.lt3.list_id,const | 1 | Using where; Using index; Not exists |
| 1 | SIMPLE | lbs | eq_ref | PRIMARY,idx_list_burial_state,burial_score | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where |
| 1 | SIMPLE | lt2 | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.lt3.list_id,const | 1 | Using where; Using index |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
Ein Vergleich der Spalte rows zeigt den Unterschied und die Abfrage mit JOINs verwendet Using temporary; Using filesort.
Wenn ich beide Abfragen ausführe, ist die erste natürlich in 0,02 Sekunden erledigt, die zweite ist auch nach 1 Minute noch nicht vollständig. Daher erklärte EXPLAIN diese Abfragen richtig.
Wenn ich den INNER JOIN nicht in der list_tag-Tabelle habe, d. H. Wenn ich entferne
AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL
aus der ersten Abfrage und entsprechend:
INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403
von der zweiten Abfrage gibt EXPLAIN dann für beide Abfragen dieselbe Anzahl von Zeilen zurück, und beide Abfragen werden gleich schnell ausgeführt.
- Eine allgemeine Regel lautet, dass Joins in den meisten Fällen schneller sind (99%).
- Je mehr Datentabellen haben, desto langsamer sind die Unterabfragen .
- Je weniger Datentabellen haben, die Unterabfragen haben eine gleichwertige Geschwindigkeit wie Joins .
- Die Unterabfragen sind einfacher, verständlicher und lesbarer.
- Die meisten Web- und App-Frameworks und ihre "ORM" - und "Active-Records" erzeugen Abfragen mit Unterabfragen , da mit Unterabfragen die Aufteilung der Verantwortung, das Verwalten von Code usw. einfacher sind.
- Für kleinere Websites oder Apps sind Unterabfragen in Ordnung, aber für größere Websites und Apps müssen Sie die generierten Abfragen häufig in join - Abfragen neu schreiben, insbesondere wenn eine Abfrage viele Unterabfragen in der verwendet Abfrage.
Einige Leute sagen "einige RDBMS können eine Unterabfrage in eine Verknüpfung oder eine Verknüpfung in eine Unterabfrage umschreiben, wenn sie der Meinung ist, dass eine schneller ist als die andere." trifft auf einfache Fälle zu, sicherlich nicht für komplizierte Abfragen mit Unterabfragen , die tatsächlich Performanceprobleme verursachen.
Der Unterschied ist nur sichtbar, wenn die zweite Verbindungstabelle wesentlich mehr Daten enthält als die Primärtabelle. Ich hatte eine Erfahrung wie unten ...
Wir hatten eine Benutzertabelle mit einhunderttausend Einträgen und ihre Mitgliedschaftsdaten (Freundschaft) etwa dreihunderttausend Einträge. Es war eine Join-Anweisung, um Freunde und ihre Daten mitzunehmen, jedoch mit großer Verzögerung. Aber es hat gut funktioniert, da nur wenige Daten in der Mitgliedschaftstabelle waren. Nachdem wir es geändert hatten, um eine Unterabfrage zu verwenden, hat es gut funktioniert.
In der Zwischenzeit arbeiten die Join-Abfragen jedoch mit anderen Tabellen, die weniger Einträge haben als die Primärtabelle.
Ich denke also, die Anweisungen für Join und Unterabfragen funktionieren gut und hängen von den Daten und der Situation ab.
Heutzutage können viele DBs Unterabfragen und Verknüpfungen optimieren. So können Sie Ihre Abfrage einfach mit erklären und sehen, welche schneller ist. Wenn es keine großen Leistungsunterschiede gibt, ziehe ich die Unterabfrage vor, da diese einfach und verständlicher sind.
Ich denke gerade über dasselbe Problem nach, aber ich verwende Unterabfrage im FROM-Teil .. __ Ich brauche Verbindung und Abfrage von großen Tabellen, die "Slave" -Tabelle hat 28 Millionen Datensätze, aber das Ergebnis ist nur 128 so kleine Ergebnisdaten ! Ich verwende die MAX () - Funktion.
Zuerst verwende ich LEFT JOIN, weil ich denke, dass dies der richtige Weg ist, das mysql sich optimalisieren kann usw. Das zweite Mal, nur zum Testen, schreibe ich in die Unterauswahl gegen den JOIN um.
LEFT JOIN Laufzeit: 1.12s SUB-SELECT Laufzeit: 0.06s
18-mal schneller als die Teilauswahl! Nur im Chokito Adv. Die Unterauswahl sieht schrecklich aus, aber das Ergebnis ...