INNER JOIN vs LEFT JOIN wydajność w SQL Server
Stworzyłem polecenie SQL, które używa INNER JOIN dla 9 tabel, w każdym razie to polecenie zajmuje bardzo dużo czasu (ponad pięć minut). Więc moi ludzie sugerują mi zmienić wewnętrzne JOIN na LEFT JOIN, ponieważ wydajność LEFT JOIN jest lepsza, po raz pierwszy jej pomimo tego, co wiem. Po zmianie szybkość zapytań znacznie się poprawia.
Chciałbym wiedzieć, dlaczego LEFT JOIN jest szybszy niż INNER JOIN?
Moje polecenie SQL wygląda jak poniżej:
SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D i tak on
Aktualizacja: To jest krótki mój schemat.
FROM sidisaleshdrmly a -- NOT HAVE PK AND FK
INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK
ON a.CompanyCd = b.CompanyCd
AND a.SPRNo = b.SPRNo
AND a.SuffixNo = b.SuffixNo
AND a.dnno = b.dnno
INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine
ON a.CompanyCd = h.CompanyCd
AND a.sprno = h.AcctSPRNo
INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix
ON c.CompanyCd = h.CompanyCd
AND c.FSlipNo = h.FSlipNo
AND c.FSlipSuffix = h.FSlipSuffix
INNER JOIN coMappingExpParty d -- NO PK AND FK
ON c.CompanyCd = d.CompanyCd
AND c.CountryCd = d.CountryCd
INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd
ON b.CompanyCd = e.CompanyCd
AND b.ProductSalesCd = e.ProductSalesCd
LEFT JOIN coUOM i -- PK = UOMId
ON h.UOMId = i.UOMId
INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd
ON a.CompanyCd = j.CompanyCd
AND b.BFStatus = j.BFStatus
AND b.ProductSalesCd = j.ProductSalesCd
INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd
ON e.ProductGroup1Cd = g1.ProductGroup1Cd
INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd
ON e.ProductGroup1Cd = g2.ProductGroup1Cd
8 answers
A LEFT JOIN nie jest absolutnie szybszy od INNER JOIN. W rzeczywistości jest wolniejszy; z definicji zewnętrzne połączenie (LEFT JOIN lub RIGHT JOIN) musi wykonać całą pracę INNER JOIN plus dodatkową pracę null-Rozszerzanie wyników. Oczekuje się również, że zwróci więcej wierszy, co dodatkowo zwiększy całkowity czas wykonania ze względu na większy rozmiar zestawu wyników.
(i nawet jeśli LEFT JOIN były szybsze w specyficznych sytuacjach z powodu jakiegoś trudnego do wyobrażenia zbiegu czynniki, nie jest to funkcjonalnie równoważne INNER JOIN, więc nie można po prostu zastąpić wszystkich instancji jednego z drugim!)
Najprawdopodobniej twoje problemy z wydajnością leżą gdzie indziej, takie jak nie posiadanie klucza kandydata lub klucza obcego odpowiednio zindeksowanego. 9 tabele jest dość dużo do łączenia tak spowolnienie może być dosłownie wszędzie. Jeśli opublikujesz swój schemat, możemy być w stanie podać więcej szczegółów.
Edit:
[[19]}rozważając dalej to, mogłem wymyślić jedną okoliczność, w której aLEFT JOIN może być szybsze niż INNER JOIN, i wtedy:
- niektóre tabele są bardzo małe (powiedzmy, poniżej 10 wierszy);
- tabele nie mają wystarczających indeksów do pokrycia zapytania.
Rozważ ten przykład:
CREATE TABLE #Test1
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Test1 (ID, Name) VALUES (1, 'One')
INSERT #Test1 (ID, Name) VALUES (2, 'Two')
INSERT #Test1 (ID, Name) VALUES (3, 'Three')
INSERT #Test1 (ID, Name) VALUES (4, 'Four')
INSERT #Test1 (ID, Name) VALUES (5, 'Five')
CREATE TABLE #Test2
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Test2 (ID, Name) VALUES (1, 'One')
INSERT #Test2 (ID, Name) VALUES (2, 'Two')
INSERT #Test2 (ID, Name) VALUES (3, 'Three')
INSERT #Test2 (ID, Name) VALUES (4, 'Four')
INSERT #Test2 (ID, Name) VALUES (5, 'Five')
SELECT *
FROM #Test1 t1
INNER JOIN #Test2 t2
ON t2.Name = t1.Name
SELECT *
FROM #Test1 t1
LEFT JOIN #Test2 t2
ON t2.Name = t1.Name
DROP TABLE #Test1
DROP TABLE #Test2
Jeśli uruchomisz to i przejrzysz plan wykonania, zobaczysz, że zapytanie INNER JOIN rzeczywiście kosztuje więcej niż LEFT JOIN, ponieważ spełnia dwa powyższe kryteria. On ponieważ SQL Server chce dopasować hash dla INNER JOIN, ale zagnieżdżone pętle dla LEFT JOIN; pierwsza z nich to normalnie znacznie szybciej, ale ponieważ liczba wierszy jest tak mała i , nie ma indeksów do użycia, operacja hashowania okazuje się być najdroższą częścią zapytania.
Możesz zobaczyć ten sam efekt, pisząc program w swoim ulubionym języku programowania, aby wykonać dużą liczbę wyszukiwań na liście z 5 elementami, a tabelę hashową z 5 żywioły. Ze względu na rozmiar, Wersja tabeli hash jest w rzeczywistości wolniejsza. Ale zwiększ ją do 50 elementów, lub 5000 elementów, a wersja listy spowalnia do crawla, ponieważ jest O (N) vs. O (1) dla hashtable.
Ale zmień to zapytanie na ID zamiast Name i zobaczysz zupełnie inną historię. W takim przypadku wykonuje zagnieżdżone pętle dla obu zapytań, ale wersja INNER JOIN jest w stanie zastąpić jeden ze skanów indeksu klastrem seek-co oznacza, że spowoduje to dosłownie być rząd wielkości szybciej z dużą liczbą wierszy.
Więc wniosek jest mniej więcej tym, o czym wspomniałem kilka akapitów powyżej; jest to prawie na pewno problem indeksowania lub pokrycia indeksów, prawdopodobnie w połączeniu z jedną lub więcej bardzo małych tabel. Są to jedyne okoliczności, w których SQL Server Może czasami wybrać gorszy plan wykonania dla INNER JOIN niż dla LEFT JOIN.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-04-28 04:30:24
Istnieje jeden ważny scenariusz, który może doprowadzić do szybszego połączenia zewnętrznego niż połączenia wewnętrznego, które nie zostało jeszcze omówione.
Podczas korzystania z zewnętrznego łącznika optymalizator zawsze może usunąć zewnętrzną tabelę łączoną z planu wykonania, jeśli kolumny łączone są PK zewnętrznej tabeli, a żadna z kolumn nie jest wybrana z zewnętrznej tabeli. Na przykład SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY I B. klucz jest PK dla B. zarówno Oracle (chyba używałem wersji 10), jak i SQL Server (używałem 2008 R2) tabela B z planu wykonania.
To samo niekoniecznie jest prawdą dla połączenia wewnętrznego: SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY może wymagać B w planie wykonania, w zależności od istniejących ograniczeń.
Jeśli A. KEY jest nullable foreign key odwołującym się do B. KEY, to optymalizator nie może usunąć B z planu, ponieważ musi potwierdzić, że wiersz B istnieje dla każdego wiersza.
Jeśli klucz A. jest obowiązkowym kluczem obcym odwołującym się do klucza B., To optymalizator może swobodnie zrzucić B z planu, ponieważ ograniczenia gwarantują istnienie wiersza. Ale tylko dlatego, że optymalizator może upuścić tabelę z planu, nie oznacza, że tak będzie. SQL Server 2008 R2 nie spada B z planu. Oracle 10 zrzuca B z planu. Łatwo jest zobaczyć, jak w tym przypadku zewnętrzne połączenie wykona wewnętrzne połączenie na serwerze SQL.
Jest to trywialny przykład, a nie praktyczny dla samodzielnego zapytania. Po co dołączać do stołu, jeśli nie musisz?
Ale to może być bardzo ważny projekt rozważenie przy projektowaniu widoków. Często budowany jest widok "zrób wszystko", który łączy wszystko, czego użytkownik może potrzebować w związku z centralną tabelą. (Zwłaszcza jeśli są naiwni użytkownicy wykonujący zapytania ad-hoc, którzy nie rozumieją modelu relacyjnego) Widok może zawierać wszystkie odpowiednie kolumny z wielu tabel. Ale użytkownicy końcowi mogą uzyskać dostęp tylko do kolumn z podzbioru tabel w widoku. Jeśli tabele są połączone z połączeniami zewnętrznymi, to optymalizator może (i robi) upuścić niepotrzebne stoły z planu.
Ważne jest upewnienie się, że Widok za pomocą złączy zewnętrznych daje prawidłowe wyniki. Jak powiedział Aaronaught - nie można ślepo zastąpić zewnętrznego połączenia wewnętrznym połączeniem i oczekiwać takich samych rezultatów. Ale są chwile, kiedy może być przydatny ze względu na wydajność podczas korzystania z widoków.
Ostatnia uwaga - nie testowałem wpływu na wydajność w świetle powyższego, ale teoretycznie wydaje się, że powinieneś być w stanie bezpiecznie wymienić połączenie wewnętrzne na zewnętrzne Dołącz, jeśli dodasz warunek
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-12-04 20:42:26
Jeśli wszystko działa tak, jak powinno, nie powinno, ale wszyscy wiemy, że wszystko nie działa tak, jak powinno, zwłaszcza jeśli chodzi o optymalizator zapytań, buforowanie planu zapytań i statystyki.
Najpierw sugerowałbym przebudowanie indeksu i statystyk, a następnie wyczyszczenie bufora planu zapytań, aby upewnić się, że to nie wszystko psuje. Jednak doświadczałem problemów nawet wtedy, gdy to się stało.
Doświadczyłem pewnych przypadków, w których lewe połączenie było szybsze niż wewnętrzne Dołącz.
Powodem jest to: Jeśli masz dwie tabele i łączysz się na kolumnie z indeksem (na obu tabelach). Połączenie wewnętrzne da ten sam wynik bez względu na to, czy zapętlisz wpisy w indeksie w tabeli pierwszej i dopasujesz je do indeksu w tabeli drugiej, tak jak byś zrobił odwrotnie: zapętl wpisy w indeksie w tabeli drugiej i dopasuj do indeksu w tabeli pierwszej. Problem polega na tym, że gdy masz mylące statystyki, optymalizator zapytań użyje statystyk indeksu, aby znaleźć tabela z najmniej pasującymi wpisami(na podstawie innych kryteriów). Jeśli masz dwie tabele z 1 milionem w każdej, w tabeli pierwszej masz 10 pasujących wierszy, a w tabeli drugiej masz 100000 pasujących wierszy. Najlepszym sposobem byłoby wykonanie skanowania indeksu w tabeli pierwszej i dopasowanie 10 razy w tabeli drugiej. Odwrotnym byłoby skanowanie indeksowe, które zapętla ponad 100000 wierszy i próbuje dopasować 100000 razy, a tylko 10 udaje. Więc jeśli statystyki nie są poprawne, optymalizator może wybrać niewłaściwą tabelę i indeks do pętli.
Jeśli optymalizator zdecyduje się zoptymalizować lewe połączenie w kolejności, w jakiej jest napisane, będzie działać lepiej niż wewnętrzne połączenie.
Ale optymalizator może również zoptymalizować lewe połączenie podoptymalnie jako lewe połączenie półzłącze. Aby wybrać ten, który chcesz, możesz użyć podpowiedzi kolejność siły.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-01-03 10:03:24
Spróbuj obu zapytań (ten z wewnętrznym i lewym połączeniem) z OPTION (FORCE ORDER) Na końcu i opublikuj wyniki. OPTION (FORCE ORDER) to podpowiedź kwerendy, która zmusza optymalizatora do zbudowania planu realizacji z zamówieniem połączenia podanym w zapytaniu.
Jeśli INNER JOIN zaczyna działać tak szybko jak LEFT JOIN, to dlatego, że:
- W zapytaniu złożonym w całości przez
INNER JOINs, kolejność łączenia nie ma znaczenia. Daje to swobodę optymalizatorowi zapytań do zamawiania połączeń według własnego uznania, więc problem może polegać na optymalizator. - z
LEFT JOIN, tak nie jest, ponieważ zmiana kolejności łączenia spowoduje zmianę wyników zapytania. Oznacza to, że silnik musi postępować zgodnie z zamówieniem połączenia podanym w zapytaniu, które może być lepsze niż zoptymalizowane.
Nie wiem, czy to odpowiada na twoje pytanie, ale byłem kiedyś w projekcie, który zawierał bardzo złożone zapytania wykonujące obliczenia, które całkowicie namieszały w optymalizatorze. Mieliśmy przypadki, w których A FORCE ORDER skróciłoby czas wykonania zapytanie od 5 minut do 10 sekund.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-06-15 19:32:11
Przeprowadzili szereg porównań pomiędzy lewym zewnętrznym i wewnętrznym połączeniem i nie byli w stanie znaleźć consisten difference. Istnieje wiele zmiennych. Pracuję nad raportującą bazą danych z tysiącami tabel z dużą liczbą pól, wieloma zmianami w czasie (wersje dostawców i lokalny przepływ pracy) . Nie jest możliwe tworzenie wszystkich kombinacji indeksów pokrycia w celu zaspokojenia potrzeb tak szerokiej gamy zapytań i obsługi danych historycznych. Widziałem wewnętrzne zapytania Zabij wydajność serwera, ponieważ dwie duże (od milionów do kilkudziesięciu milionów wierszy) tabele są połączone wewnętrznie, obie ciągną dużą liczbę pól i nie istnieje indeks pokrycia.
Największy problem nie pojawia się jednak w powyższych dyskusjach. Być może twoja baza danych jest dobrze zaprojektowana z wyzwalaczami i dobrze zaprojektowanym przetwarzaniem transakcji, aby zapewnić dobre dane. Mine często ma wartości NULL tam, gdzie nie są oczekiwane. Tak, definicje tabel mogą wymusić no-null, ale nie jest to opcja w moim środowisku.
Więc pytanie brzmi... czy projektujesz zapytanie tylko dla szybkości, wyższego priorytetu przetwarzania transakcji, który uruchamia ten sam kod tysiące razy na minutę. A może stawiasz na dokładność, którą zapewni lewe zewnętrzne połączenie. Pamiętaj, że połączenia wewnętrzne muszą znajdować dopasowania po obu stronach, więc nieoczekiwane NULL nie tylko usunie dane z dwóch tabel, ale być może całe wiersze informacji. I dzieje się tak ładnie, bez komunikatów o błędach.
Możesz bądź bardzo szybki, ponieważ uzyskanie 90% potrzebnych danych i nie odkrycie wewnętrznych połączeń po cichu usuwa informacje. Czasami połączenia wewnętrzne mogą być szybsze, ale nie wierzę, że ktoś robi to założenie, chyba że przejrzał plan wykonania. Szybkość jest ważna, ale dokładność jest ważniejsza.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-06-19 16:20:32
Twoje problemy z wydajnością są bardziej prawdopodobne ze względu na liczbę połączeń, które wykonujesz i czy kolumny, do których dołączasz, mają indeksy, czy nie.
Najgorszy przypadek można łatwo zrobić 9 całych skanów tabeli dla każdego połączenia.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-04-28 03:49:35
Zewnętrzne połączenia mogą oferować doskonałą wydajność w widokach.
Powiedz, że masz zapytanie, które obejmuje widok, a ten widok składa się z 10 tabel połączonych ze sobą. Powiedz, że Twoje zapytanie zdarza się tylko używać kolumn z 3 z tych tabel 10.
Jeśli te tabele 10 zostały połączone wewnętrznie razem, to optymalizator zapytań musiałby połączyć je wszystkie, nawet jeśli samo zapytanie nie wymaga 7 z 10 tabel. To dlatego, że wewnętrzna łączy się z sobą może odfiltrować dane, czyniąc je niezbędnymi do obliczenia.
Jeśli te tabele 10 były połączone zewnętrznie razem, to optymalizator zapytań połączyłby tylko te, które były konieczne: 3 z 10 z nich w tym przypadku. Dzieje się tak, ponieważ same połączenia nie filtrują już danych, a tym samym nieużywane połączenia mogą być pomijane.
Źródło: http://www.sqlservercentral.com/blogs/sql_coach/2010/07/29/poor-little-misunderstood-views/
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-01 21:29:52
Znalazłem coś ciekawego w SQL serverze podczas sprawdzania, czy połączenia wewnętrzne są szybsze niż połączenia lewe.
Jeśli nie włączysz elementów tabeli left JOIN, w instrukcji select, lewe join będzie szybsze niż to samo zapytanie z INNER join.
Jeśli do instrukcji select dołączysz tabelę left joined, łącznik wewnętrzny z tym samym zapytaniem będzie równy lub szybszy niż łącznik left.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-14 11:47:50