Podzapytania a sprzężenia

Refaktoryzowałem powolną sekcję aplikacji, którą odziedziczyliśmy po innej firmie, aby użyć sprzężenia wewnętrznego zamiast podzapytania, takiego jak:

WHERE id IN (SELECT id FROM ...)

Zapytanie refaktoryzowane działa około 100 razy szybciej. (~ 50 sekund do ~ 0,3) Spodziewałem się poprawy, ale czy ktoś może wyjaśnić, dlaczego było to tak drastyczne? Wszystkie kolumny użyte w klauzuli where zostały zindeksowane. Czy SQL wykonuje zapytanie w klauzuli where raz na wiersz czy coś w tym stylu?

Aktualizacja - wyjaśnij wyniki:

Różnica jest w drugiej części zapytania „where id in ()” -

2   DEPENDENT SUBQUERY  submission_tags ref st_tag_id   st_tag_id   4   const   2966    Using where

vs 1 indeksowany wiersz z łączeniem:

    SIMPLE  s   eq_ref  PRIMARY PRIMARY 4   newsladder_production.st.submission_id  1   Using index

„Skorelowane podzapytanie” (tj. Takie, w którym warunek gdzie zależy od wartości uzyskanych z wierszy zawierającego zapytanie) zostanie wykonane raz dla każdego wiersza. Podzapytanie nieskorelowane (takie, w którym warunek gdzie jest niezależny od zapytania zawierającego) zostanie wykonane raz na początku. Silnik SQL dokonuje tego rozróżnienia automatycznie.

Ale tak, plan wyjaśnienia dostarczy ci brudnych szczegółów.

Uruchamiasz podzapytanie raz dla każdego wiersza, podczas gdy łączenie odbywa się na indeksach.

Oto przykład, jak są oceniane podzapytania w MySQL 6.0 .

Nowy optymalizator przekształci tego rodzaju podzapytania w sprzężenia.

Uruchom plan wyjaśniający dla każdej wersji, a dowiesz się dlaczego.

zanim zapytania zostaną uruchomione względem zestawu danych, które zostaną przekazane przez optymalizator zapytań, optymalizator próbuje zorganizować zapytanie w taki sposób, aby mógł usunąć jak najwięcej krotek (wierszy) ze zbioru wyników tak szybko, jak to tylko możliwe. Często, gdy używasz podzapytań (szczególnie złych), krotki nie mogą zostać usunięte z zestawu wyników, dopóki zapytanie zewnętrzne nie zostanie uruchomione.

Nie widząc zapytania, trudno powiedzieć, co było tak złego w oryginale, ale przypuszczam, że było to coś, czego optymalizator po prostu nie mógł zrobić dużo lepiej. Uruchomienie „wyjaśnienia” pokaże metodę optymalizatorów do pobierania danych.

Spójrz na plan zapytań dla każdego zapytania.

Gdzie in i Join można zwykle wdrożyć przy użyciu tego samego planu wykonania, więc zazwyczaj zmiana między nimi nie przyspiesza.

Optimizer nie wykonał zbyt dobrej roboty. Zwykle można je przekształcić bez żadnej różnicy, a optymalizator może to zrobić.

Zwykle jest to wynikiem tego, że optymalizator nie jest w stanie dowiedzieć się, czy podzapytanie może zostać wykonane jako łączenie, w którym to przypadku wykonuje podzapytanie dla każdego rekordu w tabeli, a nie łączy tabelę w podzapytaniu względem tabeli, o którą pytasz. Niektóre z bardziej „korporacyjnych” baz danych są w tym lepsze, ale czasami ich brakuje.

To pytanie jest dość ogólne, więc oto ogólna odpowiedź:

Zasadniczo zapytania trwają dłużej, gdy MySQL ma mnóstwo wierszy do sortowania.

Zrób to:

Uruchom EXPLAIN dla każdego z zapytań (tego DOŁĄCZONEGO, a następnie Podbitego) i opublikuj wyniki tutaj.

Myślę, że zauważenie różnicy w interpretacji tych zapytań przez MySQL byłoby doświadczeniem edukacyjnym dla każdego.

Podzapytanie where musi uruchomić 1 zapytanie dla każdego zwróconego wiersza. Sprzężenie wewnętrzne musi tylko uruchomić 1 zapytanie.

Podzapytanie prawdopodobnie wykonywało „pełne skanowanie tabeli”. Innymi słowy, nieużywanie indeksu i zwracanie zbyt wielu wierszy, które pole Gdzie z głównego zapytania musiało odfiltrować.

Oczywiście zgadywanie bez szczegółów, ale to powszechna sytuacja.

W przypadku podzapytania musisz ponownie wykonać 2. SELECT dla każdego wyniku, a każde wykonanie zwykle zwraca 1 wiersz.

W przypadku sprzężenia 2. SELECT zwraca znacznie więcej wierszy, ale wystarczy wykonać je tylko raz. Zaletą jest to, że teraz możesz dołączyć do wyników, a łączenie relacji jest tym, w czym baza danych powinna być dobra. Na przykład, być może optymalizator może teraz wykryć, jak lepiej wykorzystać indeks.

To nie tyle podzapytanie, ile klauzula IN, chociaż łączenia są podstawą przynajmniej silnika SQL Oracle i działają niezwykle szybko.

Zaczerpnięte z podręcznika referencyjnego ( 14.2.10.11 Przepisywanie podzapytań jako połączenia ):

LEFT [OUTER] JOIN 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 dla samego serwera MySQL.

Zatem podzapytania mogą być wolniejsze niż LEFT [OUTER] JOINS.