Select w select czy join-y Opcje

[marcio]

Czesc nie znam sie bardzo na bazach danych i chcialem sie zapytac powiedzmy ze musze pobrac dane z 3 tabel users,ban i active_account i nawet gdy nie znajdzie rekordu w ktorejs z tabel to ma zwrocic null czyli w tym przypadku uzywajac relacje musze uzyc LEFT JOIN

I problem polega na tym lepiej jest wykonywac takie zapytania w ten sposob:

[SQL] pobierz, plaintext 
SELECT * 
FROM users
LEFT JOIN ban ON ban_user = users.login
LEFT JOIN active_account ON active_user_id = users.id
WHERE login =  'vodka'
LIMIT 1
[SQL] pobierz, plaintext 

Czy:

[SQL] pobierz, plaintext 
SELECT * , (
SELECT id
FROM ban
WHERE ban_user =  'vodka'
) AS banned , (
SELECT id
FROM active_account
WHERE active_user_id = users.id
) AS active
FROM users
WHERE login =  'vodka'
LIMIT 1
[SQL] pobierz, plaintext 

Ktore zapytanie jest bardziej wydajne?

[Pilsener]

Zdecydowanie zapytanie ze złączeniami jest bardziej wydajne. Z podzapytaniami trzeba uważać, bo zazwyczaj to podzapytania wpływają na spadek wydajności.

- będę ostro polemizował.

Joiny strasznie spowalniają, zwłaszcza do dużych tabel. Join jest po to, żeby np. pobrać nazwę województwa na podstawie jego ID wtedy, jeśli jest to potrzebne. Używanie joina żeby np. dodać do całego zapytania where to moim zdaniem nieporozumienie i niezrozumienie istoty działania tego, to tak jakby zamiast where używać having. Jak chcecie to sami sprawdźcie na takim typowym przykładzie:

Źle:

[SQL] pobierz, plaintext 
SELECT * FROM products
JOIN category_has_product ON category_has_product.id_product = products.id
WHERE category_has_product.type=3
[SQL] pobierz, plaintext 

Lepiej:

[SQL] pobierz, plaintext 
SELECT * FROM products
JOIN category_has_product ON category_has_product.id_product = products.id AND category_has_product.type=3
[SQL] pobierz, plaintext 

Najlepiej:

[SQL] pobierz, plaintext 
SELECT * FROM products
WHERE id IN(SELECT id_product FROM category_has_product WHERE type=3)
[SQL] pobierz, plaintext 

Dwa proste i szybkie selecty, jak mamy indeksy to śmiga jak burza, join w tym wypadku to takie babranie się, chcemy przecież tylko pobrać produkty z kategorii określonego typu. Kto mi nie wierzy niech sam zrobi testy. Zresztą czemu wielu programistów zamiast jednego zapytania pełnego joinów robi kilka prostych? Bo tak jest szybciej a kiedyś nie było takich możliwości używania podzapytań.

[mortus]

- będę ostro polemizował.

Joiny strasznie spowalniają, zwłaszcza do dużych tabel. Join jest po to, żeby np. pobrać nazwę województwa na podstawie jego ID wtedy, jeśli jest to potrzebne. Używanie joina żeby np. dodać do całego zapytania where to moim zdaniem nieporozumienie i niezrozumienie istoty działania tego, to tak jakby zamiast where używać having. Jak chcecie to sami sprawdźcie na takim typowym przykładzie:

Źle:

[SQL] pobierz, plaintext 
SELECT * FROM products
JOIN category_has_product ON category_has_product.id_product = products.id
WHERE category_has_product.type=3
[SQL] pobierz, plaintext 

Lepiej:

[SQL] pobierz, plaintext 
SELECT * FROM products
JOIN category_has_product ON category_has_product.id_product = products.id AND category_has_product.type=3
[SQL] pobierz, plaintext 

Najlepiej:

[SQL] pobierz, plaintext 
SELECT * FROM products
WHERE id IN(SELECT id_product FROM category_has_product WHERE type=3)
[SQL] pobierz, plaintext 

Dwa proste i szybkie selecty, jak mamy indeksy to śmiga jak burza, join w tym wypadku to takie babranie się, chcemy przecież tylko pobrać produkty z kategorii określonego typu. Kto mi nie wierzy niech sam zrobi testy. Zresztą czemu wielu programistów zamiast jednego zapytania pełnego joinów robi kilka prostych? Bo tak jest szybciej a kiedyś nie było takich możliwości używania podzapytań.

Nie mam za wiele czasu w tej chwili, ale przetestowałem wstępnie te trzy zapytania na bazie danych zawierającej przeszło 300000 rekordów na silniku InnoDB (złączenia i warunki na kolumnach indeksowanych i kluczach głównych) i jest zupełnie odwrotnie, niż piszesz. Sam jakoś nie mogę w to uwierzyć, bo Twoje przykłady wydają się być konkretne i oczywiste, dlatego użyłem słowa "wstępnie". Zmobilizuje się, znajdę czas i przetestuję to dokładnie na tej samej bazie danych, jak również na identycznej bazie z liczbą rekordów przekraczającą 1000000.

Tak pokrótce. Dlaczego wyniki moich testów przeczą temu, co piszesz? Twoje pierwsze zapytanie najpierw wykonuje złączenie, a później sprawdza warunek. Tutaj złączenie następuje bardzo szybko, ponieważ pod uwagę brany jest tylko warunek złączenia, i tylko założenie odpowiedniego indeksu na typ kategorii decyduje o szybkości wyszukiwania produktów z danego typu kategorii. Drugie zapytanie podczas złączenia musi sprawdzić warunek, czyli teoretycznie rzecz biorąc wykonuje się wolniej, bo nie dość, że trzeba sprawdzić warunek złączenia, to jeszcze trzeba sprawdzić typ kategorii - złączenie następuje tylko wtedy, gdy oba warunki się zgadzają. Trzecie zapytanie to jest dla bazy danych tak naprawdę "masakra", nawet jeśli wybierzemy najpierw zbiór identyfikatorów (ten w klauzuli IN) to "zgodnych" kombinacji każdy z każdym pomiędzy produktami i kategoriami danego typu są setki, a zapytanie musi sprawdzić "zgodność" każdej z kombinacji (również tych niezgodnych, chodzi o to, że np. produkt 1 ma kategorię typu 3 o nazwie 1, ale już niekoniecznie ma/a przeważnie nie ma kategorii typu 3 o nazwie 2). Taka sytuacja w przypadku złączeń nie występuje, bo złączenie już wcześniej nastąpiło i innych możliwości nie ma, poza warunkiem złączenia.

Tak, że ogólnie rzecz biorąc, albo ja to źle rozumiem, albo Ty. Zapytania ze złączeniami operują na zbiorze wszystkich potrzebnych danych. Podzapytania selekcjonują "wiersz po wierszu" dane potrzebne. Co na "chłopski rozum" będzie szybsze?

Niemniej jednak, zaintrygowany tym tematem, postaram się wykonać rzetelne testy na dwóch maszynach z różnej epoki, w jak najbardziej identycznych warunkach, a wyniki tych testów wszem i wobec (czyli tutaj) ogłoszę.

EDIT1:
Tak gwoli uzupełnienia, zapytanie które testowałem było praktycznie rzecz biorąc identyczne jak to, które dla przykładu pokazałeś - jedno proste złączenie i jeden banalny warunek. Najlepiej spisał się w tej sytuacji JOIN z WHERE, na drugim miejscu był JOIN z AND, a na trzecim zapytanie z podzapytaniem. Testowałem trzykrotnie i testy były zgodne. Nie przejmowałem się jednak zbytnio warunkami, w jakich test był przeprowadzony - kilka aplikacji działało w tle i w czasie testów wykonywałem inne czynności. I choć obciążenie procesora (Intel Core i5-2430, 2.4GHz) było niewielkie, bo na poziomie 28%, podobnie jak i zużycie pamięci (około 2GB z 8GB), to być może miało to minimalny wpływ na wyniki testów.

EDIT2:
Kiedyś gdzieś wyczytałem, że złączenie dwóch tabel przy użyciu zapytania bez JOIN wiąże się tak na prawdę ze "sztucznym" złączeniem tych dwóch tabel i późniejszym wyselekcjonowaniem odpowiednich rekordów. Nie pamiętam jednak w jakiej sytuacji takiej informacji szukałem, nie pamiętam źródła i nie jestem w stanie zweryfikować wiarygodności.

Joiny strasznie spowalniają, zwłaszcza do dużych tabel. Join jest po to, żeby np. pobrać nazwę województwa na podstawie jego ID wtedy, jeśli jest to potrzebne.

Tak do tego właśnie służą JOIN-y, podobnie jak i do pobrania informacji odnośnie tego, czy konto danego użytkownika jest aktywne, czy nie i czy przypadkiem nie został on zbanowany.

Dwa proste i szybkie selecty, jak mamy indeksy to śmiga jak burza, join w tym wypadku to takie babranie się, chcemy przecież tylko pobrać produkty z kategorii określonego typu.

Proste to chyba w tym przypadku oznacza "prosto napisane", bo działanie już takie proste i oczywiste nie jest. Główny SELECT wybiera wiersz, a później dla tego wiersza wykonuje kolejny SELECT, żeby sprawdzić czy jego wyniki znajdują się w zbiorze wyników oczekiwanych. I tak wiersz po wierszu. Oczywiście w tak banalnym przypadku odbywa się to w miarę szybko, bo wyniki podzapytania dla każdego wiersza z zapytania głównego są identyczne i zostały już wybrane dla pierwszego wiersza, zatem rezydują sobie gdzieś w pamięci. Tyle, że podzapytanie wcale nie musi być tak banalne i proste.

Zresztą czemu wielu programistów zamiast jednego zapytania pełnego joinów robi kilka prostych? Bo tak jest szybciej a kiedyś nie było takich możliwości używania podzapytań.

To, że wielu programistów wykorzystuje podzapytania zamiast JOIN-ów nie oznacza, że podzapytania działają szybciej. Mogłoby raczej oznaczać, że wielu programistów nie potrafi się złączeniami dobrze posługiwać. No chyba, że chodzi Ci o to, że napisanie takiego jednego zapytania z podzapytaniami zajmuje mniej czasu, niż napisanie zapytania ze złączeniami działającego dokładnie tak samo.

Przeglądałem wiele wątków na ten temat (również na forum MySQL) i rzeczywistość jest taka, że złączenia działają szybciej, a tylko w bardzo rzadkich sytuacjach mogą zostać skutecznie zastąpione podzapytaniami.

Ten post edytował mortus 24.04.2012, 09:38:19