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?

[mortus]

Zdecydowanie zapytanie ze złączeniami jest bardziej wydajne. Z podzapytaniami trzeba uważać, bo zazwyczaj to podzapytania wpływają na spadek wydajności. Poza tym zapytanie z podzapytaniami jest nieprawidłowe i zadziała prawidłowo tylko w wyjątkowych warunkach, których nie chcę mi się określać. W każdym bądź razie wyniki w obu przykładach będą/mogą być różne.

Ten post edytował mortus 21.04.2012, 14:25:51

[marcio]

Poza tym zapytanie z podzapytaniami jest nieprawidłowe i zadziała prawidłowo tylko w wyjątkowych warunkach, których nie chcę mi się określać. W każdym bądź razie wyniki w obu przykładach będą/mogą być różne

A dlaczego tak jest?

[mortus]

A dlaczego tak jest?

Warunek WHERE ban_user = 'vodka' nie jest tutaj prawidłowym warunkiem połączenia tabel ban i users, i może generować błędne wyniki. Poza tym wszystko wygląda dobrze, z tym że podzapytania bezsensownie wybierają tylko id z tabeli ban i id z tabeli active_account.

Chętnie przyjrzałbym się strukturze tych trzech tabel, bo na pierwszy rzut oka wydaje mi się, że coś jest "przesadzone".

[marcio]

Poza tym wszystko wygląda dobrze, z tym że podzapytania bezsensownie wybierają tylko id z tabeli ban i id z tabeli active_account.

Wybieraja tylko id zeby bylo szybciej niz pobierac wszystkie kolumny, a pobieram dlatego ze jesli zwroci NULL to znaczy ze uzytkownik nie jest zbanowany i ma aktywne konto.

Strukture tabel podam jutro

[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ń.

[marcio]

Joiny strasznie spowalniają, zwłaszcza do dużych tabel

No wlasnie dlatego spytalem bo ostatnio czytajac to i owo spotkalem sie tez z benchmark-ami...

Co do where w join myslalem ze nie robi to roznicy czy dam where czy and..chyba az tak duzej roznicy nie bedzie?

Co do in(), powiedzmy ze usuwam wiecej rekordow z bazy danych wydajniej jest zrobic to za pomoca IN lub tyle zapytan DELETE ile rekordow chcemy usunac?
Stawiam na IN() ale pewnosci nie mam wiec pytam

[Pilsener]

Pewnie, że IN, delete garści identyfikatorów potrwa chwilę. Dziś i tak jest lepiej z MySQL ale to prosty system i nie zoptymalizuje wszystkiego za nas, dlatego znaczenie ma nawet kolejność wherów i joinów, często trzeba się mocno zastanowić z której tabeli robić from W większość przypadków bazy są śmiesznie małe więc nikt nie myśli o optymalizacji.

[uupah5]

dla dociekliwych może jeszcze źródełko: http://dev.mysql.com/doc/refman/5.5/en/opt...zing-subqueries
a dla początkujących IMHO: lepiej robić joiny (po indexach) niż podzapytania. a optymalizować "hakami" jak jest potrzeba, nie na zapas.

[skowron-line]

Należy też przypomnieć że używając podzapytań w select jesteśmy w stanie wydobyć tylko jedną kolumnę.

Ten post edytował skowron-line 23.04.2012, 12:08:13

[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

[Pilsener]

Faktycznie sprawdzałem i jest jak mówisz - dziwne bo jeszcze trzy lata temu miałem identyczny przypadek i zamiast joinować dużą tabelę dużo szybciej wychodziło pobranie z niej paru ID podzapytaniem i wrzucenie tego do where. Może optimizer nie radzi sobie z bardziej skomplikowanymi zapytaniami albo go ulepszyli albo znaczenie ma cały szereg innych czynników, włącznie z silnikiem bazy. Pewnie podzapytanie będzie szybsze, jeśli nie mamy indeksów na joinowane pola.

To tak jak pobieranie liczbe rekordów przy pomocy found calc rows - do dziś nie wiem czy używać tego czy nie. Wygląda na to, że każdy przypadek należy rozpatrywać indywidualnie, trzeba robić explain i rzeźbić.

[alegorn]

jesli chodzi o podzapytania vs joiny, to w typowej edycji mysql joiny beda zawsze wygrywac (no, prawie zawsze)
gdybym mialbym byc zmuszony do dzialania na podzapytaniach - to tylko na MariaDB, w ktorym to zostalo gruntownie przebudowane, i tam rzeczywiscie dziala lepiej.

jesli nie mamy wyjscia i nie mozemy (lub nie umiemy) przepisac zapytania na joiny - warto rozdzielic to na kika etapow (czyli pola id wybrac w osobnym zapytaniu, i np w phpie wstawic do kolejnego zapytania)
oczywiscie nie zadziala to dobrze, jesli ilosc id bedzie znacznie przesadzona (zyskujemy tutaj dodatkowy bonus w postaci wielowatkowosci)

ide o zaklad, ze na mysql w 9 na 10 przypadkach, na nietrywialnych tabelach (i optymalnych) join bedzie mial lepsze statystyki. (przynajmniej do wersji 5.5.x)

co do argumentu ze wielu programistow wykorzystuje subquery. moge w tym temacie powiedziec jedynie, ze wielu programistow nie rozumie/nie zna sql'a i nie ma pojecia jak pisac optymalne zapytania.
nie mowiac juz o projektowaniu bazy danych. pisza wiec byle jak.

j.

[marcio]

Czyli ogolnie rzecz biorac dobrze ze opieram wszystko o join'y, i nie widze nic zlego w wykorzystaniu warunku where razem z join'e, jesli musze polaczyc kilka tabel w jakims skrajnym przypadku...

[skowron-line]

Jak wyżej było powiedziane jak masz wątpliwości to explain i zobaczysz jak to wygląda.