Dołącz vs. pod-zapytanie


836

Jestem oldskulowym użytkownikiem MySQL i zawsze wolałem JOINsub-zapytania. Ale obecnie wszyscy używają zapytań podrzędnych i nie znoszę tego; Nie wiem dlaczego.

Brakuje mi wiedzy teoretycznej, aby samodzielnie ocenić, czy jest jakaś różnica. Czy sub-zapytanie jest tak dobre jak JOINi dlatego nie ma się czym martwić?


23
Podkwerendy są czasem świetne. Ssają pod względem wydajności w MySQL. Nie używaj ich.
runrig

8
Zawsze miałem wrażenie, że podpytania zostały domyślnie wykonane jako połączenia, jeśli były dostępne w niektórych technologiach DB.
Kezzer

18
Zapytania podrzędne nie zawsze są do bani, gdy łączysz się z dość dużymi tabelami, preferowanym sposobem jest dokonanie wyboru podrzędnego z tej dużej tabeli (ograniczenie liczby wierszy), a następnie połączenie.
ovais.tariq

136
„w dzisiejszych czasach wszyscy używają zapytań podrzędnych” [potrzebne źródło]
Piskvor opuścił budynek

3
Potencjalnie powiązane (choć o wiele bardziej szczegółowe): stackoverflow.com/questions/141278/subqueries-vs-joins/…
Leigh Brenecki

Odpowiedzi:


190

Zaczerpnięte z podręcznika MySQL ( 13.2.10.11 Przepisywanie zapytań podrzędnych jako złączeń ):

POŁĄCZENIE W LEWO [ZEWNĘTRZNE] może być szybsze niż równoważne podzapytanie, ponieważ serwer może być w stanie lepiej je zoptymalizować - fakt, który nie jest specyficzny tylko dla MySQL Server.

Zatem podkwerendy mogą być wolniejsze niż LEFT [OUTER] JOIN, ale moim zdaniem ich siłą jest nieco większa czytelność.


45
@ user1735921 IMO to zależy ... Zasadniczo bardzo ważna jest czytelność kodu, ponieważ ma on ogromne znaczenie dla późniejszego zarządzania nim ... Pamiętajmy o słynnym oświadczeniu Donalda Knutha: „Przedwczesna optymalizacja jest podstawą wszystkich zło (lub przynajmniej większość z nich) w programowaniu " . Naturalnie są jednak obszary programowania, w których wydajność jest najważniejsza ... Idealnie, gdy uda się pogodzić ze sobą :)
simhumileco

30
W bardziej złożonych zapytaniach łączenia są znacznie łatwiejsze do odczytania niż zapytania podrzędne. podpytania zamieniają się w miskę makaronu w mojej głowie.
Zahra,

6
@ user1735921 na pewno, szczególnie gdy zapytanie staje się tak skomplikowane, że robi coś złego, a ty spędzasz dzień na naprawianiu ... jak zwykle istnieje równowaga między nimi.
fabio.sussetto

6
@ user1735921 Tylko jeśli wzrost wydajności jest wart wydłużenia czasu konserwacji wymaganego w przyszłości
Joshua Schlichting

3
Moja opinia Joini sub queryma inną składnię, więc czytelność nie może być porównywana, obie mają wyższą czytelność, o ile jesteś dobry w składni SQL. Wydajność jest ważniejsza.
Thavaprakash Swaminathan

840

Zapytania podrzędne to logicznie poprawny sposób rozwiązywania problemów formularza „Uzyskaj fakty z A, zależnie od faktów z B”. W takich przypadkach logiczne jest umieszczanie B w zapytaniu cząstkowym niż łączenie. Jest to również bezpieczniejsze, w sensie praktycznym, ponieważ nie musisz być ostrożny w uzyskiwaniu zduplikowanych faktów z A ze względu na wielokrotne mecze przeciwko B.

W praktyce jednak odpowiedź zazwyczaj sprowadza się do wydajności. Niektórzy optymalizatorzy zasysają cytryny, gdy otrzymają sprzężenie w porównaniu z zapytaniem podrzędnym, a niektóre ssają cytryny w drugą stronę, i jest to specyficzne dla optymalizatora, specyficzne dla wersji DBMS i specyficzne dla zapytania.

Historycznie, jawne sprzężenia zwykle wygrywają, stąd ustalona mądrość, że sprzężenia są lepsze, ale optymalizatory są coraz lepsze, dlatego wolę pisać zapytania najpierw w logicznie spójny sposób, a następnie przeprowadzać restrukturyzację, jeśli uzasadniają to ograniczenia wydajności.


105
Świetna odpowiedź. Dodałbym również, że programiści (zwłaszcza amatorzy) nie zawsze są biegli w języku SQL.
Álvaro González

4
+1 Długo szukam jakiegoś logicznego wyjaśnienia tego problemu, jest to dla mnie logiczna odpowiedź
Ali Umair

1
@Marcelo Cantos, czy mógłbyś podać przykład swojego oświadczenia „Jest to również w sensie praktycznym bezpieczniejsze, ponieważ nie musisz być ostrożny w uzyskiwaniu zduplikowanych faktów z A z powodu wielu dopasowań przeciwko B.”? Uważam to za bardzo wnikliwe, ale trochę zbyt abstrakcyjne. Dzięki.
Jinghui Niu

6
@JinghuiNiu Klienci, którzy kupili drogie przedmioty: select custid from cust join bought using (custid) where price > 500. Jeśli klient kupił wiele drogich przedmiotów, dostaniesz podwójne. Aby rozwiązać ten problem, select custid from cust where exists (select * from bought where custid = cust.custid and price > 500). Możesz użyć select distinct …zamiast tego, ale często jest to więcej pracy, zarówno dla optymalizatora, jak i oceniającego.
Marcelo Cantos,

1
@MatTheWhale tak, użyłem zbyt uproszczonej odpowiedzi, bo byłem leniwy. W prawdziwym scenariuszu wyciągnąłbyś więcej kolumn niż tylko custid z cust.
Marcelo Cantos,

357

W większości przypadków JOINs są szybsze niż pod-zapytania i bardzo rzadko zdarza się, że pod-zapytanie jest szybsze.

W JOINRDBMS może stworzyć plan wykonania, który jest lepszy dla twojego zapytania i może przewidzieć, jakie dane powinny zostać załadowane do przetworzenia i zaoszczędzić czas, w przeciwieństwie do pod-zapytania, w którym będzie uruchamiać wszystkie zapytania i ładować wszystkie swoje dane do przetwarzania .

Dobrą rzeczą w zapytaniach podrzędnych jest to, że są bardziej czytelne niż JOINs: dlatego większość nowych osób SQL je woli; to prosty sposób; ale jeśli chodzi o wydajność, ŁĄCZENIA są lepsze w większości przypadków, nawet jeśli nie są trudne do odczytania.


14
Tak, większość baz danych uwzględnia go zatem jako etap optymalizacji służący do przekształcania podzapytań w sprzężenia podczas analizy zapytania.
Cine

16
Ta odpowiedź jest nieco zbyt uproszczona dla zadanego pytania. Jak twierdzisz: niektóre podzapytania są w porządku, a niektóre nie. Odpowiedź tak naprawdę nie pomaga rozróżnić tych dwóch. (również „bardzo rzadkie” tak naprawdę zależy od danych / aplikacji).
Nieuzasadniony

21
czy możesz udowodnić którykolwiek ze swoich punktów, odwołując się do dokumentacji lub wyników testów?
Uğur Gümüşhan

62
Zrobiłem bardzo dobre wrażenia z podkwerendami, które zawierają odsyłacz wstecz do górnego zapytania, szczególnie jeśli chodzi o liczbę wierszy powyżej 100 000. Wydaje się, że chodzi o wykorzystanie pamięci i stronicowanie do pliku wymiany. Sprzężenie wygenerowałoby bardzo dużą ilość danych, które mogą nie zmieścić się w pamięci i muszą zostać umieszczone w pliku wymiany. Ilekroć jest to przypadek, czasy zapytania dla małych podselekcji jak select * from a where a.x = (select b.x form b where b.id = a.id)są bardzo małe w porównaniu do łączenia. Jest to bardzo specyficzny problem, ale w niektórych przypadkach prowadzi od godzin do minut.
zuloo

13
Mam doświadczenie z Oracle i mogę powiedzieć, że zapytania podrzędne są znacznie lepsze na dużych tabelach, jeśli nie masz na nich żadnego filtrowania ani sortowania.
Amir Pashazadeh

130

Użyj EXPLAIN, aby zobaczyć, w jaki sposób baza danych wykonuje zapytanie na twoich danych. W tej odpowiedzi jest ogromne „to zależy” ...

PostgreSQL może przepisać podzapytanie na złączenie lub dołączanie do podzapytania, jeśli uważa, że ​​jedno jest szybsze od drugiego. Wszystko zależy od danych, indeksów, korelacji, ilości danych, zapytania itp.


6
właśnie dlatego postgresql jest tak dobry i przydatny, że rozumie, jaki jest cel i naprawi zapytanie w oparciu o to, co według niego jest lepsze, a postgresql jest bardzo dobry w wiedzy, jak patrzeć na swoje dane
WojonsTech

heww. Chyba nie ma potrzeby, aby ponownie pisać dla mnie mnóstwo zapytań! postgresql do wygranej.
Daniel Shin,

77

W 2010 roku dołączyłbym do autora tych pytań i zdecydowanie głosowałbym JOIN, ale mając o wiele więcej doświadczenia (szczególnie w MySQL) mogę stwierdzić: Tak, podzapytania mogą być lepsze. Przeczytałem tutaj wiele odpowiedzi; niektóre podane podzapytania są szybsze, ale brakowało dobrego wyjaśnienia. Mam nadzieję, że mogę udzielić tej (bardzo) późnej odpowiedzi:

Po pierwsze, pozwól mi powiedzieć najważniejsze: Istnieją różne formy podkwerend

I drugie ważne stwierdzenie: Rozmiar ma znaczenie

Jeśli korzystasz z zapytań podrzędnych, powinieneś zdawać sobie sprawę z tego, w jaki sposób serwer DB wykonuje zapytanie podrzędne. Zwłaszcza jeśli zapytanie cząstkowe jest oceniane raz lub dla każdego wiersza! Z drugiej strony nowoczesny serwer DB jest w stanie wiele zoptymalizować. W niektórych przypadkach podzapytanie pomaga zoptymalizować zapytanie, ale nowsza wersja serwera DB może sprawić, że optymalizacja stanie się przestarzała.

Zapytania podrzędne w Select-Fields

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

Należy pamiętać, że zapytanie podrzędne jest wykonywane dla każdego wynikowego wiersza z foo.
Unikaj tego, jeśli to możliwe; może to drastycznie spowolnić zapytanie dotyczące ogromnych zestawów danych. Jeśli jednak zapytanie nie zawiera odniesienia foo, może być zoptymalizowane przez serwer DB jako zawartość statyczna i może być ocenione tylko raz.

Zapytania częściowe w instrukcji Where

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

Jeśli masz szczęście, DB optymalizuje to wewnętrznie do JOIN. Jeśli nie, twoje zapytanie stanie się bardzo, bardzo wolne na ogromnych zestawach danych, ponieważ wykona pod-zapytanie dla każdego wiersza w foo, nie tylko wyników, jak w przypadku typu select.

Zapytania częściowe w instrukcji Join

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

To jest interesujące. Łączymy JOINz pod-zapytaniem. I tutaj otrzymujemy prawdziwą siłę podkwerend. Wyobraź sobie zestaw danych z milionami wierszy, wilcoale tylko kilkoma odrębnymi me. Zamiast łączyć się z ogromnym stołem, mamy teraz mniejszy tymczasowy stół, do którego można dołączyć. Może to spowodować znacznie szybsze zapytania w zależności od wielkości bazy danych. Możesz mieć taki sam efekt za pomocą CREATE TEMPORARY TABLE ...i INSERT INTO ... SELECT ..., co może zapewnić lepszą czytelność bardzo złożonych zapytań (ale może zablokować zestawy danych na powtarzalnym poziomie izolacji odczytu).

Zagnieżdżone zapytania cząstkowe

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

Można zagnieżdżać podzapytania na wielu poziomach. Może to pomóc w przypadku ogromnych zestawów danych, jeśli trzeba pogrupować lub posortować wyniki. Zazwyczaj serwer DB tworzy do tego celu tabelę tymczasową, ale czasami nie trzeba sortować według całej tabeli, a jedynie zestawu wyników. Może to zapewnić znacznie lepszą wydajność w zależności od wielkości tabeli.

Wniosek

Pod-zapytania nie zastępują a JOINi nie należy ich używać w ten sposób (chociaż jest to możliwe). Moim skromnym zdaniem prawidłowe użycie zapytania częściowego to użycie go jako szybkiej zamiany CREATE TEMPORARY TABLE .... Dobre zapytanie podrzędne ogranicza zestaw danych w sposób, którego nie można osiągnąć w ONinstrukcji a JOIN. Jeśli zapytanie podrzędne zawiera jedno ze słów kluczowych GROUP BYlub DISTINCTi najlepiej nie znajduje się w polach wyboru lub instrukcji where, może to znacznie poprawić wydajność.


3
Dla Sub-queries in the Join-statement: (1) wygenerowanie tabeli pochodnej z samego zapytania podrzędnego może zająć bardzo dużo czasu. (2) wynikowa tabela pochodna nie jest indeksowana. te dwa same mogą znacznie spowolnić SQL.
jxc

@jxc Mogę mówić tylko za MySQL (1) Jest to tymczasowa tabela podobna do złączenia. Czas zależy od ilości danych. Jeśli nie możesz zmniejszyć danych za pomocą podzapytania, użyj sprzężenia. (2) To prawda, zależy to od czynnika, który można zmniejszyć w tabeli tymczasowej. Miałem rzeczywiste przypadki, w których mogłem zmniejszyć rozmiar złączenia z kilku milionów do kilkuset i skrócić czas zapytania z wielu sekund (przy pełnym wykorzystaniu indeksu) do kwadransa z podzapytaniem.
Trendfischer

IMO: (1) taka tabela tymczasowa (tabela pochodna) nie jest zmaterializowana, dlatego za każdym razem, gdy uruchamiasz SQL, tabela tymczasowa musi zostać odtworzona, co może być bardzo kosztowne i może być bardzo kosztowne (np. Prowadzenie grupy przez miliony rekordów) (2), nawet jeśli można zmniejszyć rozmiar tabeli tymczasowej do 10rekordów, ponieważ nie ma indeksu, co nadal oznacza, że ​​potencjalnie można zapytać 9 razy więcej rekordów danych niż bez tabeli tymczasowej podczas ŁĄCZENIA z innymi tabelami. BTW Miałem już ten problem z moim db (MySQL), w moim przypadku użycie pod-zapytania SELECT listmoże być znacznie szybsze.
jxc

@ jxc Nie wątpię, że istnieje wiele przykładów, w których użycie podzapytania jest mniej optymalne. Jako dobrą praktykę powinieneś skorzystać EXPLAINz zapytania przed optymalizacją. Ze starym set profiling=1można łatwo zobaczyć, czy tymczasowy stół jest wąskim gardłem. Nawet indeks wymaga czasu przetwarzania, B-Trees optymalizuje zapytania dotyczące rekordów, ale tablica 10 rekordów może być znacznie szybsza niż indeks dla milionów rekordów. Ale zależy to od wielu czynników, takich jak rozmiary i typy pól.
Trendfischer

1
Naprawdę podobało mi się twoje wyjaśnienie. Dziękuję Ci.
unpairestgood

43

Przede wszystkim, aby porównać dwa pierwsze, należy rozróżnić zapytania z podkwerendami do:

  1. klasa podkwerend, które zawsze mają odpowiadające im równoważne zapytanie zapisane złączeniami
  2. klasa podkwerend, których nie można przepisać przy użyciu sprzężeń

W przypadku pierwszej klasy zapytań dobry RDBMS zobaczy sprzężenia i podzapytania jako równoważne i wygeneruje te same plany zapytań.

W dzisiejszych czasach robi to nawet mysql.

Nadal czasami tak nie jest, ale nie oznacza to, że złączenia zawsze wygrywają - miałem przypadki, gdy korzystałem z podkwerend w mysql, poprawiając wydajność. (Na przykład, jeśli istnieje coś, co uniemożliwia planerowi mysql prawidłowe oszacowanie kosztu, a jeśli planista nie widzi wariantu łączenia i wariantu podzapytania jako tego samego, wówczas podzapytania mogą prześcignąć łączenia poprzez wymuszenie określonej ścieżki).

Wniosek jest taki, że powinieneś przetestować swoje zapytania dla wariantów łączenia i podzapytań, jeśli chcesz mieć pewność, które z nich będzie działać lepiej.

W przypadku drugiej klasy porównanie nie ma sensu, ponieważ tych zapytań nie można przepisać przy użyciu sprzężeń, aw takich przypadkach podkwerendy są naturalnym sposobem wykonywania wymaganych zadań i nie należy ich dyskryminować.


1
czy możesz podać przykład zapytania napisanego przy użyciu zapytań cząstkowych, których nie można przekonwertować na sprzężenia (jak to się nazywa druga klasa)?
Zahra,

24

Myślę, że niedoceniana w cytowanych odpowiedziach jest kwestia duplikatów i problematycznych wyników, które mogą wynikać z konkretnych przypadków (użycia).

(chociaż Marcelo Cantos o tym wspomina)

Przytoczę przykład z kursów Lagunita Stanforda na temat SQL.

Tabela uczniów

+------+--------+------+--------+
| 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 |
+------+--------+------+--------+

Zastosuj tabelę

(wnioski złożone na określone uniwersytety i kierunki)

+------+----------+----------------+----------+
| 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        |
+------+----------+----------------+----------+

Spróbujmy znaleźć wyniki GPA dla studentów, którzy zgłosili się CSna studia wyższe (niezależnie od uczelni)

Korzystanie z podzapytania:

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 |
+------+

Średnia wartość dla tego zestawu wyników wynosi:

select avg(GPA) from Student where sID in (select sID from Apply where major = 'CS');

+--------------------+
| avg(GPA)           |
+--------------------+
| 3.6800000000000006 |
+--------------------+

Korzystanie z połączenia:

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 |
+------+

średnia wartość dla tego zestawu wyników:

select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+-------------------+
| avg(GPA)          |
+-------------------+
| 3.714285714285714 |
+-------------------+

Oczywiste jest, że druga próba daje mylące wyniki w naszym przypadku użycia, biorąc pod uwagę, że liczy się duplikaty do obliczenia wartości średniej. Oczywiste jest również, że użycie distinctinstrukcji opartej na złączeniu nie wyeliminuje problemu, biorąc pod uwagę, że błędnie zatrzyma jedno z trzech wystąpień 3.9wyniku. Prawidłowym przypadkiem jest uwzględnienie DWÓCH (2) wystąpień 3.9wyniku, biorąc pod uwagę fakt, że faktycznie mamy DWÓCH (2) uczniów z wynikiem, który spełnia nasze kryteria zapytania.

Wydaje się, że w niektórych przypadkach podpytanie jest najbezpieczniejszym sposobem, oprócz problemów z wydajnością.


Myślę, że nie możesz tutaj użyć zapytania podrzędnego. Nie jest to przypadek, w którym logicznie można użyć jednego z nich, ale jedna daje złą odpowiedź ze względu na techniczną implementację. Jest to przypadek, w którym NIE MOŻESZ użyć zapytania częściowego, ponieważ uczeń nienależący do CS może uzyskać wynik 3,9, który znajduje się na liście IN wyników. Kontekst CS zostaje utracony po wykonaniu zapytania częściowego, co nie jest logicznie tym, czego chcemy. To nie jest dobry przykład, w którym można zastosować jedno z nich. Użycie pod-zapytania jest koncepcyjnie / logicznie niepoprawne w tym przypadku użycia, nawet jeśli na szczęście daje poprawny wynik dla innego zestawu danych.
Saurabh Patil

22

Dokumentacja MSDN dla SQL Server mówi

Wiele instrukcji Transact-SQL zawierających podkwerendy można alternatywnie sformułować jako sprzężenia. Inne pytania można zadawać tylko z podzapytaniami. W języku Transact-SQL zwykle nie ma różnicy w wydajności między instrukcją zawierającą podzapytanie a wersją semantycznie równoważną, która tego nie robi. Jednak w niektórych przypadkach, w których należy sprawdzić istnienie, połączenie daje lepszą wydajność. W przeciwnym razie zapytanie zagnieżdżone musi zostać przetworzone dla każdego wyniku zapytania zewnętrznego, aby zapewnić eliminację duplikatów. W takich przypadkach metoda łączenia przyniosłaby lepsze wyniki.

więc jeśli potrzebujesz czegoś takiego

select * from t1 where exists select * from t2 where t2.parent=t1.id

zamiast tego spróbuj użyć łączyć. W innych przypadkach nie ma znaczenia.

Mówię: tworzenie funkcji dla podkwerend eliminuje problem z zakłóceniami i pozwala na implementację dodatkowej logiki do podkwerend. Dlatego zalecam tworzenie funkcji dla podkwerend, gdy tylko jest to możliwe.

Zaśmiecenie kodu jest dużym problemem, a przemysł od dziesięcioleci pracuje nad jego unikaniem.


9
Zamiana podkwerend na funkcje jest bardzo złym pomysłem pod względem wydajności w niektórych RDBMS (np. Oracle), więc poleciłbym coś wręcz przeciwnego - w miarę możliwości używaj podkwerend / złączeń zamiast funkcji.
Frank Schmitt,

3
@FrankSchmitt, poprzyj swój argument referencjami.
Uğur Gümüşhan

2
Są też przypadki, w których powinieneś użyć zapytania podrzędnego zamiast łączenia, nawet jeśli sprawdzasz istnienie: jeśli sprawdzasz NOT EXISTS. A NOT EXISTSwygrywa Ponad LEFT OUTER JOIN z różnych powodów: PreFormance, nie-bezpieczeństwa (w przypadku nulable kolumn) i czytelności. sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join
Tim Schmelter

16

Uruchom na bardzo dużej bazie danych ze starego Mambo CMS:

SELECT id, alias
FROM
  mos_categories
WHERE
  id IN (
    SELECT
      DISTINCT catid
    FROM mos_content
  );

0 sekund

SELECT
  DISTINCT mos_content.catid,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

~ 3 sekundy

WYJAŚNIENIE pokazuje, że sprawdzają dokładnie taką samą liczbę wierszy, ale jedna zajmuje 3 sekundy, a jedna jest prawie natychmiastowa. Morał historii? Jeśli wydajność jest ważna (kiedy nie jest?), Wypróbuj ją na wiele sposobów i sprawdź, która z nich jest najszybsza.

I...

SELECT
  DISTINCT mos_categories.id,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

0 sekund

Ponownie te same wyniki, ta sama liczba zbadanych wierszy. Domyślam się, że DISTINCT mos_content.catid wymyśla znacznie dłużej niż DISTINCT mos_categories.id.


1
chciałbym dowiedzieć się więcej o tym, co próbujesz wskazać w ostatnim wierszu „Domyślam się, że DISTINCT mos_content.catid zajmuje znacznie więcej czasu niż DISTINCT mos_categories.id”. . Czy mówisz, że identyfikator powinien mieć tylko nazwę, ida nie coś takiego jak catid? Próbuję zoptymalizować mój dostęp do bazy danych, a twoje wnioski mogą pomóc.
bool.dev

2
użycie SQL IN w tym przypadku jest złą praktyką i niczego nie dowodzi.
Uğur Gümüşhan

15

Zgodnie z moimi obserwacjami, tak jak w dwóch przypadkach, jeśli tabela ma mniej niż 100 000 rekordów, wówczas połączenie będzie działać szybko.

Ale w przypadku, gdy tabela ma ponad 100 000 rekordów, najlepszym wynikiem jest podzapytanie.

Mam jedną tabelę, która zawiera 500 000 rekordów, które utworzyłem poniżej zapytania, a czas jej zakończenia jest podobny

SELECT * 
FROM crv.workorder_details wd 
inner join  crv.workorder wr on wr.workorder_id = wd.workorder_id;

Wynik: 13,3 sekundy

select * 
from crv.workorder_details 
where workorder_id in (select workorder_id from crv.workorder)

Wynik: 1,65 sekundy


Zgadzam się, że czasami przerwanie zapytania działa również, gdy masz milion rekordów, nie chcesz używać sprzężeń, ponieważ trwają one na zawsze. Rób to raczej w kodzie, a mapa w kodzie jest lepsza.
user1735921,

1
Wiązanie połączeń nie działa wystarczająco szybko, być może brakuje indeksu. Query Analyzer może być bardzo pomocny w porównywaniu rzeczywistej wydajności.
digital.aaron

Zgadzam się z Ajayem Gajerą, sam to widziałem.
user1735921,

14
Jak sens ma porównywanie wydajności dwóch zapytań, które zwracają różne wyniki?
Paul Spiegel,

Tak, to są różne zapytania, ale zwracają ten sam wynik
król neo

12

Podkwerendy są na ogół używane do zwrócenia pojedynczego wiersza jako wartości atomowej, chociaż można ich użyć do porównania wartości z wieloma wierszami ze słowem kluczowym IN. Są one dozwolone w prawie dowolnym znaczącym punkcie instrukcji SQL, w tym na liście docelowej, klauzuli WHERE i tak dalej. Proste kryterium zapytania może być wykorzystane jako warunek wyszukiwania. Na przykład między parą tabel:

   SELECT title FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Bar' AND first_name = 'Foo');

Należy zauważyć, że użycie operatora wartości normalnej w wynikach zapytania podrzędnego wymaga zwrócenia tylko jednego pola. Jeśli chcesz sprawdzić, czy istnieje jedna wartość w zestawie innych wartości, użyj IN:

   SELECT title FROM books WHERE author_id IN (SELECT id FROM authors WHERE last_name ~ '^[A-E]');

To oczywiście różni się od powiedzenia LEFT-JOIN, w którym po prostu chcesz dołączyć rzeczy z tabeli A i B, nawet jeśli warunek łączenia nie znajdzie żadnego pasującego rekordu w tabeli B itp.

Jeśli martwisz się o szybkość, musisz sprawdzić bazę danych i napisać dobre zapytanie i sprawdzić, czy jest jakaś znacząca różnica w wydajności.


11

Wersja MySQL: 5.5.28-0ubuntu0.12.04.2-log

Miałem też wrażenie, że JOIN jest zawsze lepszy niż pod-zapytanie w MySQL, ale EXPLAIN jest lepszym sposobem na osądzanie. Oto przykład, w którym zapytania cząstkowe działają lepiej niż JOIN.

Oto moje zapytanie z 3 pod-zapytaniami:

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;

WYJAŚNIJ pokazuje:

+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| 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 |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+

To samo zapytanie z JOIN:

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;

a wynikiem jest:

+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| 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                     |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+

Porównanie rowskolumny wskazuje różnicę, a używane jest zapytanie z JOIN Using temporary; Using filesort.

Oczywiście, kiedy uruchamiam oba zapytania, pierwsze odbywa się za 0,02 sekundy, drugie nie kończy się nawet po 1 minucie, więc WYJAŚNIJ poprawnie te zapytania.

Jeśli nie mam INNER JOIN na list_tagstole, tj. Jeśli usunę

AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL  

z pierwszego zapytania i odpowiednio:

INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403

z drugiego zapytania funkcja EXPLAIN zwraca tę samą liczbę wierszy dla obu zapytań i oba te zapytania działają równie szybko.


Mam podobną sytuację, ale z większą liczbą złączeń niż twoja, spróbuję wyjaśnić raz
pahnin

W Oracle lub PostgreSQL spróbowałbym: I NIE ISTNIEJE (WYBIERZ 1 Z list_tag GDZIE list_id = l.list_id AND tag_id w (43, 55, 246403))
David Aldridge

11

Podkwerendy mają zdolność do obliczania funkcji agregacji w locie. Np. Znajdź minimalną cenę książki i uzyskaj wszystkie książki sprzedawane z tą ceną. 1) Korzystanie z podkwerend:

SELECT titles, price
FROM Books, Orders
WHERE price = 
(SELECT MIN(price)
 FROM Orders) AND (Books.ID=Orders.ID);

2) za pomocą JOIN

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;


6
-1 Jest to mylące, ponieważ używasz podzapytania i dołączasz do obu przykładów. Wyciągnięcie podzapytania do drugiego zapytania w celu ustalenia najniższej ceny zamówienia nie ma żadnego efektu, ponieważ baza danych zrobi dokładnie to samo. Ponadto nie przepisujesz sprzężenia za pomocą podzapytania; oba zapytania wykorzystują sprzężenie. Ci poprawne, że podkwerend umożliwiają funkcje agregujące, ale ten przykład nie wykazać ten fakt.
David Harkness,

Zgadzam się z Davidem i możesz użyć grupy by uzyskać minimalną cenę.
user1735921,

9
  • Ogólna zasada jest taka, że sprzężenia są w większości przypadków szybsze (99%).
  • Im więcej tabel danych, tym podkwerendy są wolniejsze.
  • Im mniej tabel danych, tym podzapytania mają równoważną szybkość jak sprzężenia .
  • W podzapytania są prostsze, łatwiejsze do zrozumienia i łatwiejsze do odczytania.
  • Większość platform internetowych i aplikacji oraz ich „ORM” i „Aktywny rekord” generują zapytania z podkwerendami , ponieważ z podkwerendami łatwiej jest podzielić odpowiedzialność, utrzymać kod itp.
  • W przypadku mniejszych witryn lub aplikacji podkwerendy są OK, ale w przypadku większych witryn i aplikacji często trzeba będzie ponownie napisać wygenerowane zapytania, aby dołączyć do zapytań, szczególnie jeśli zapytanie korzysta z wielu podkwerend w zapytaniu.

Niektórzy twierdzą, że „niektóre RDBMS mogą przepisać podzapytanie na złączenie lub dołączanie do podzapytania, gdy sądzi, że jedno jest szybsze od drugiego”. Ale to stwierdzenie dotyczy prostych przypadków, na pewno nie w przypadku skomplikowanych zapytań z podzapytaniami, które w rzeczywistości powodują problemy z wydajnością.


> ale to stwierdzenie dotyczy prostych przypadków. Rozumiem, że jest to albo prosty przypadek, który może zostać przepisany na „DOŁĄCZ” przez RDBMS, albo tak złożony przypadek, że odpowiednie są tutaj podzapytania. :-) Fajny punkt na ORM. Myślę, że ma to największy wpływ.
pilat

4

Różnica jest widoczna tylko wtedy, gdy druga tabela łączenia zawiera znacznie więcej danych niż tabela podstawowa. Miałem doświadczenie jak poniżej ...

Mieliśmy tabelę użytkowników zawierającą sto tysięcy wpisów, a ich dane członkostwa (przyjaźń) około 300 tysięcy wpisów. Było to wspólne oświadczenie, aby zabrać przyjaciół i ich dane, ale z wielkim opóźnieniem. Ale działało dobrze, gdy w tabeli członkostwa znajdowała się tylko niewielka ilość danych. Gdy zmieniliśmy go, aby użyć pod-zapytania, działało dobrze.

Ale w międzyczasie zapytania dotyczące łączenia działają z innymi tabelami, które mają mniej wpisów niż tabela podstawowa.

Myślę więc, że instrukcje łączenia i kwerendy działają dobrze i zależy to od danych i sytuacji.


3

Obecnie wiele dbs może zoptymalizować podkwerendy i złączenia. Dlatego właśnie musisz zbadać swoje zapytanie za pomocą wyjaśnienia i zobaczyć, które z nich jest szybsze. Jeśli nie ma dużej różnicy w wydajności, wolę używać podkwerend, ponieważ są one proste i łatwiejsze do zrozumienia.


1

Właśnie myślę o tym samym problemie, ale używam podzapytania w części FROM. Potrzebuję połączenia i zapytania z dużych tabel, tabela „slave” ma 28 milionów rekordów, ale wynik to tylko 128, więc mały wynik big data! Używam na nim funkcji MAX ().

Najpierw używam LEFT JOIN, ponieważ myślę, że jest to właściwy sposób, mysql może zoptymalizować itp. Drugi raz tylko w celu przetestowania, przepisuję, aby podselekcjonować względem JOIN.

Środowisko wykonawcze LEFT JOIN: 1,12 s Środowisko uruchomieniowe SUB-SELECT: 0,06 s

18 razy szybszy wybór podrzędny niż łączenie! Tylko w chokito adv. Podselekcja wygląda okropnie, ale wynik ...


-1

Jeśli chcesz przyspieszyć zapytanie za pomocą Join:

W przypadku „wewnętrznego łączenia / łączenia”, nie używaj warunku where zamiast tego używaj go w stanie „ON”. Na przykład:

     select id,name from table1 a  
   join table2 b on a.name=b.name
   where id='123'

 Try,

    select id,name from table1 a  
   join table2 b on a.name=b.name and a.id='123'

W przypadku „Łączenia w lewo / w prawo”, nie używaj w stanie „WŁ.”, Ponieważ jeśli użyjesz łączenia w lewo / w prawo, otrzyma wszystkie wiersze dla dowolnej tabeli. Więc nie ma potrzeby używania go w „Włącz”. Więc spróbuj użyć warunku „Where”


Zależy to od serwera SQL i złożoności zapytania. Wiele implementacji SQL zoptymalizowałoby takie proste zapytania w celu uzyskania najlepszej wydajności. Być może podaj przykładową nazwę serwera i wersję, w której takie zachowanie zdarza się, aby poprawić odpowiedź?
Trendfischer
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.