Select w select czy join-y |
Select w select czy join-y |
21.04.2012, 14:00:04
Post
#1
|
|
Grupa: Zarejestrowani Postów: 2 291 Pomógł: 156 Dołączył: 23.09.2007 Skąd: ITALY-MILAN Ostrzeżenie: (10%) |
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:
Czy:
Ktore zapytanie jest bardziej wydajne? -------------------- Zainteresowania: XML | PHP | MY(SQL)| C# for .NET | PYTHON
http://code.google.com/p/form-builider/ Moj blog |
|
|
21.04.2012, 14:24:18
Post
#2
|
|
Grupa: Zarejestrowani Postów: 2 178 Pomógł: 596 Dołączył: 25.09.2009 Skąd: Piwniczna-Zdrój Ostrzeżenie: (0%) |
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 |
|
|
21.04.2012, 14:50:06
Post
#3
|
|
Grupa: Zarejestrowani Postów: 2 291 Pomógł: 156 Dołączył: 23.09.2007 Skąd: ITALY-MILAN Ostrzeżenie: (10%) |
Cytat 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? -------------------- Zainteresowania: XML | PHP | MY(SQL)| C# for .NET | PYTHON
http://code.google.com/p/form-builider/ Moj blog |
|
|
21.04.2012, 15:05:58
Post
#4
|
|
Grupa: Zarejestrowani Postów: 2 178 Pomógł: 596 Dołączył: 25.09.2009 Skąd: Piwniczna-Zdrój Ostrzeżenie: (0%) |
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". |
|
|
22.04.2012, 00:31:53
Post
#5
|
|
Grupa: Zarejestrowani Postów: 2 291 Pomógł: 156 Dołączył: 23.09.2007 Skąd: ITALY-MILAN Ostrzeżenie: (10%) |
Cytat 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 -------------------- Zainteresowania: XML | PHP | MY(SQL)| C# for .NET | PYTHON
http://code.google.com/p/form-builider/ Moj blog |
|
|
22.04.2012, 21:35:28
Post
#6
|
|
Grupa: Zarejestrowani Postów: 1 590 Pomógł: 185 Dołączył: 19.04.2006 Skąd: Gdańsk Ostrzeżenie: (0%) |
Cytat 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:
Lepiej:
Najlepiej:
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ń. |
|
|
22.04.2012, 23:56:07
Post
#7
|
|
Grupa: Zarejestrowani Postów: 2 291 Pomógł: 156 Dołączył: 23.09.2007 Skąd: ITALY-MILAN Ostrzeżenie: (10%) |
Cytat 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 -------------------- Zainteresowania: XML | PHP | MY(SQL)| C# for .NET | PYTHON
http://code.google.com/p/form-builider/ Moj blog |
|
|
23.04.2012, 10:34:14
Post
#8
|
|
Grupa: Zarejestrowani Postów: 1 590 Pomógł: 185 Dołączył: 19.04.2006 Skąd: Gdańsk Ostrzeżenie: (0%) |
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.
|
|
|
23.04.2012, 11:57:09
Post
#9
|
|
Grupa: Zarejestrowani Postów: 207 Pomógł: 18 Dołączył: 4.09.2010 Skąd: warszawa Ostrzeżenie: (0%) |
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. |
|
|
23.04.2012, 12:07:56
Post
#10
|
|
Grupa: Zarejestrowani Postów: 4 340 Pomógł: 542 Dołączył: 15.01.2006 Skąd: Olsztyn/Warszawa Ostrzeżenie: (0%) |
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 -------------------- I'm so fast that last night I turned off the light switch in my hotel room and was in bed before the room was dark - Muhammad Ali.
Peg jeżeli chcesz uprawiać sex to dzieci muszą wyjść, a jeżeli chcesz żeby był dobry ty też musisz wyjść - Al Bundy. QueryBuilder, Mootools.net, bbcradio1::MistaJam http://www.phpbench.com/ |
|
|
24.04.2012, 00:05:21
Post
#11
|
|
Grupa: Zarejestrowani Postów: 2 178 Pomógł: 596 Dołączył: 25.09.2009 Skąd: Piwniczna-Zdrój Ostrzeżenie: (0%) |
- będę ostro polemizował. 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.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:
Lepiej:
Najlepiej:
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ń. 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. Cytat 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.Cytat 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.Cytat 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 |
|
|
24.04.2012, 10:23:44
Post
#12
|
|
Grupa: Zarejestrowani Postów: 1 590 Pomógł: 185 Dołączył: 19.04.2006 Skąd: Gdańsk Ostrzeżenie: (0%) |
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ć. |
|
|
24.04.2012, 10:25:28
Post
#13
|
|
Grupa: Zarejestrowani Postów: 341 Pomógł: 40 Dołączył: 23.06.2009 Ostrzeżenie: (0%) |
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. |
|
|
24.04.2012, 23:49:29
Post
#14
|
|
Grupa: Zarejestrowani Postów: 2 291 Pomógł: 156 Dołączył: 23.09.2007 Skąd: ITALY-MILAN Ostrzeżenie: (10%) |
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...
-------------------- Zainteresowania: XML | PHP | MY(SQL)| C# for .NET | PYTHON
http://code.google.com/p/form-builider/ Moj blog |
|
|
25.04.2012, 08:45:28
Post
#15
|
|
Grupa: Zarejestrowani Postów: 4 340 Pomógł: 542 Dołączył: 15.01.2006 Skąd: Olsztyn/Warszawa Ostrzeżenie: (0%) |
Jak wyżej było powiedziane jak masz wątpliwości to explain i zobaczysz jak to wygląda.
-------------------- I'm so fast that last night I turned off the light switch in my hotel room and was in bed before the room was dark - Muhammad Ali.
Peg jeżeli chcesz uprawiać sex to dzieci muszą wyjść, a jeżeli chcesz żeby był dobry ty też musisz wyjść - Al Bundy. QueryBuilder, Mootools.net, bbcradio1::MistaJam http://www.phpbench.com/ |
|
|
Wersja Lo-Fi | Aktualny czas: 27.04.2024 - 01:52 |