Optymalizacja zapytania Opcje

[Petre]

Witajcie.
Mam to "szczęście", że muszę po poprzednikach poprawić zapytania w sklepie internetowym. W znacznym stopniu baza danych zostaje obciążona czego efektem jest wydłużony czas ładowania się strony.

Zapytania wyświetlające listę produktów w sklepie:

[SQL] pobierz, plaintext 
SELECT z.zdj, z.id_zest, z.symbol, zj.nazwa, zj.*,MAX(p.data_add) AS data_produkt, zc.wartosc,zc.id_cech 
FROM (zestawy z, zestawy_jezyki zj, produkty p, kategorie_zestawy kz, kategorie k ) LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod 
AND pj.id_lang=1 LEFT JOIN zestawy_cechy zc ON zc.id_zest=z.id_zest AND zc.id_cech=2 
WHERE p.id_zest=z.id_zest AND p.stan=1 AND z.id_zest=zj.id_zest AND z.stan=1 AND zj.id_lang=1 AND kz.id_kat=k.id_kat AND z.id_zest=kz.id_zest 
AND p.detal=1 AND (kz.id_kat=3009 OR k.id_gr=3009) 
GROUP BY z.id_zest 
ORDER BY data_produkt DESC , zj.nazwa ASC LIMIT 0, 20 
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
SELECT count(DISTINCT z.id_zest) AS il 
FROM (zestawy z, zestawy_jezyki zj, produkty p, kategorie_zestawy kz, kategorie k ) LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod 
AND pj.id_lang=1 LEFT JOIN zestawy_cechy zc ON zc.id_zest=z.id_zest AND zc.id_cech=2 
WHERE p.id_zest=z.id_zest AND p.stan=1 AND z.id_zest=zj.id_zest AND z.stan=1 AND zj.id_lang=1 AND kz.id_kat=k.id_kat 
AND z.id_zest=kz.id_zest AND p.detal=1 AND (kz.id_kat=3009 OR k.id_gr=3009) 
[SQL] pobierz, plaintext 

zapytanie zostaje wywołane dwa razy:
1 dla listy produktów, 2 (jak wywnioskowałem) dla paginacji (ilość stron produktów).

Indexy założone na tabele (nazwa tabeli - pola indexow):
zestawy - id_zest(pk), id_kat, zdj, symbol;
zestawy_jezyki - id_zest_l(pk), id_zest, id_lang, nazwa, opis, ivona(nie uzywane juz w sklepie pole w bazie), ivona_url(jak w przypadku ivony)
zestawy_cechy - id_cech_l(pk) & id_zest, id_cech_l(pk), id_zest, wartosc
produkty - id_prod(pk), id_user, id_vat, id_produc, id_zest, id_rodz, data_add, idx_stan, detal
produkty_jezyki - id_prod_l(pk), id_prod, id_lang
kategorie_zestawy - id_kat_zest(pk), id_kat & id_zest
kategorie - id_kat(pk), id_user, id_gr

Wszystkie tabele to MyISAM.

Jakie są wasze sugestie co do optymalizacji tego zapytania ?

Ten post edytował Petre 3.01.2013, 10:16:50

[Sephirus]

Witam. To jest masakra - ile lat ma ten kod? Przede wszystkim przerobił bym to na InnoDB bo jest dużo odczytów a mało zapisów (o ile nie wpłynie to na inne rzeczy w systemie - trzeba by sprawdzić ale wątpie by wpłynęło). Jak już będzie na tym mechanizmie to przerobił bym zapytanka tak by widoczne były wszystkie JOINY - czyli zamiast FROM i listy tabel dałbym to w joinach i odpowiednio owarunkował. Jak już by to działało to zerknąłbym na indeksy i ustawił (jeśli nie ma) dla wszystkich pól jakie są wykorzystywane w WHERE, ON i ORDER.

Jak będziesz miał takie zapytanka (czyli dojdziesz do tego jak to powinno wyglądać) to wtedy można je przeanalizować i ewentualnie jeszcze zoptymalizować. Sama przeróbka powinna podnieść nieco wydajność (IMG:style_emoticons/default/wink.gif)

[Petre]

Nie aż tak wiele ; )

Ok przygotowałem takie coś :

[SQL] pobierz, plaintext 
SELECT z.zdj, z.id_zest, z.symbol, zj.nazwa, zj.*,MAX(p.data_add) AS data_produkt, zc.wartosc,zc.id_cech 
FROM zestawy z
JOIN zestawy_jezyki zj ON z.id_zest=zj.id_zest 
JOIN produkty p ON p.id_zest=z.id_zest
JOIN kategorie_zestawy kz ON kz.id_zest=z.id_zest
JOIN kategorie k ON kz.id_kat=k.id_kat
LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod 
LEFT JOIN zestawy_cechy zc ON zc.id_zest=z.id_zest 
WHERE p.stan=1 AND z.stan=1 AND zj.id_lang=1 AND p.detal=1 AND (kz.id_kat=3009 OR k.id_gr=3009) AND zc.id_cech=2  AND pj.id_lang=1
GROUP BY z.id_zest 
ORDER BY data_produkt DESC , zj.nazwa ASC 
[SQL] pobierz, plaintext 

co ciekawe, kiedy umieszczam AND zc.id_cech=2 AND pj.id_lang=1 w WHERE czas zapytania jest dłuższy niż, gdy owe pola wstawię w JOINACH

[SQL] pobierz, plaintext 
LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod AND pj.id_lang=1
LEFT JOIN zestawy_cechy zc ON zc.id_zest=z.id_zest AND zc.id_cech=2 
[SQL] pobierz, plaintext 

Czy zmiana silnika na InnoDB na żywo (w trakcie działania sklepu, a więc tym samym robionych selektach przez użytkowników itp.) nie odbije się czkawką ?
AutoInc zostanie zachowane z MyIsam czy poleci od 0 ?

[mmmmmmm]

co ciekawe, kiedy umieszczam AND zc.id_cech=2 AND pj.id_lang=1 w WHERE czas zapytania jest dłuższy niż, gdy owe pola wstawię w JOINACH

Bo wtedy są wykonywane ZUPEŁNIE INNE zapytania... Chyba nie masz do końca pojęcia jaka to różnica.

[Sephirus]

Tak jak Ci pisałem to istotne by owarunkowanie tabel wiązanych było właśnie w joinach (IMG:style_emoticons/default/smile.gif) W WHERE trzymaj tylko to co dotyczy tabeli tej "pierwszej" do której joinujesz.

Sprawdziłeś indeksy czy są tak jak Ci pisałem? (dla wszytskich pól z WHERE, ON, ORDER - w sumie GROUP BY też)


Co do InnoDB... hmm na żywym organiźmie - tymbardziej sklepie bym tego nie robił raczej. Ogólnie najlepiej by było zatrzymać sklep, zrobić backup całej bazy, zrobić konwersje i przetestować. Ogólnie InnoDb jeśli nie poustawiasz kluczy obcych nie powinno nic popsuć na bazie która działała w MyISAM chyba że są tam używane zapytania typowe dla MyISAM jak wyciąganie statysyk tabel z tabeli systemowych - ale wątpie by ktoś na to wpadł patrząc na te zapytanka.

O ile wzrosła wydajność po samej zmianie na JOINy i poprawieniu INDEKSÓW podaj jakieś liczby cokolwiek.

[mmmmmmm]

Tak jak Ci pisałem to istotne by owarunkowanie tabel wiązanych było właśnie w joinach (IMG:style_emoticons/default/smile.gif) W WHERE trzymaj tylko to co dotyczy tabeli tej "pierwszej" do której joinujesz.

To też nie jest prawdą...
Jak znajdę czas, to dam przykład z pokazaniem różnic, bo to jest bez przykładu ciężko zrozumieć...

[Sephirus]

@UP MySQL lubi robić duże tabelki tymczasowe jak go źle instruujesz - w momencie gdy w warunku złączenia określisz więcej danych (bardziej okroisz liczbę pasujących rekordów) tabela tymczasowa jest mniejsza a co za tym idzie sortowanie szybsze itd itp.

Chętnie jednak zobaczę przykład taki (oczywiście praktyczny) w którym będzie odwrotnie.

Oczywiście są przypadki gdy nie ma to różnicy (jeśli i tak i tak pobierane są wszystkie rekordy z tabeli złączanej) lub gdy jest to mniej wygodne - tak czy inaczej IMHO lepiej jest to pisać w ten sposób bo może się czasem nic nie zyska ale przynajmniej nie straci.

[Petre]

Tak jak Ci pisałem to istotne by owarunkowanie tabel wiązanych było właśnie w joinach (IMG:style_emoticons/default/smile.gif) W WHERE trzymaj tylko to co dotyczy tabeli tej "pierwszej" do której joinujesz.

Sprawdziłeś indeksy czy są tak jak Ci pisałem? (dla wszytskich pól z WHERE, ON, ORDER - w sumie GROUP BY też)

O ile wzrosła wydajność po samej zmianie na JOINy i poprawieniu INDEKSÓW podaj jakieś liczby cokolwiek.

przerobiłem zapytanie do takiej postaci :

[SQL] pobierz, plaintext 
SELECT z.zdj, z.id_zest, z.symbol, zj.nazwa, zj.*,MAX(p.data_add) AS data_produkt, zc.wartosc,zc.id_cech 
FROM zestawy z
JOIN zestawy_jezyki zj ON z.id_zest=zj.id_zest AND zj.id_lang=1
JOIN produkty p ON p.id_zest=z.id_zest AND p.detal=1 AND p.stan=1
JOIN kategorie_zestawy kz ON kz.id_zest=z.id_zest
JOIN kategorie k ON kz.id_kat=k.id_kat
LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod  AND pj.id_lang=1
LEFT JOIN zestawy_cechy zc ON zc.id_zest=z.id_zest AND zc.id_cech=2
WHERE z.stan=1 AND (kz.id_kat=3009 OR k.id_gr=3009)  
GROUP BY z.id_zest 
ORDER BY data_produkt DESC , zj.nazwa ASC 
[SQL] pobierz, plaintext 

pozakładałem indexy na wszystkie z pól ON, WHERE, ORDER, GROUP BY

Testuję na "żywym" sklepie. Wyniki :
duration: od 5 - 11 s

Kiedy warunki zostawiam w WHERE wówczas czas jest nieznacznie krótszy.
Nie jest to jednoznaczne ponieważ ilość użytkowników w sklepie ciągle się zmienia więc i obciążenie serwera się waha.

Jest jeszcze jakaś opcja przetestowania bardziej miarodajna ?

[phpion]

Daj może wynik EXPLAINa. Na pierwszy rzut oka spojrzałbym czy są pozakładane indeksy na kolumnach, których używasz w złączeniach. Nie chodzi o indeks na każdej kolumnie z osobna, ale o indeks na N kolumnach, czyli np.:

[SQL] pobierz, plaintext 
JOIN zestawy_jezyki zj ON z.id_zest=zj.id_zest AND zj.id_lang=1
[SQL] pobierz, plaintext 

tutaj wypadałoby dodać 1 indeks na obu kolumnach.

Zastanów się również czy nie ma możliwości pozbycia się tego grupowania. Ot tak pewnie nie, ale może warto dodać gdzieś dodatkową kolumnę przechowującą wartość MAX(p.data_add). Wartość kolumny można by aktualizować triggerem by działo się to automatycznie. No ale tutaj wkraczamy już w modyfikację struktury bazy więc teren robi się grząski (IMG:style_emoticons/default/wink.gif)

[Sephirus]

Jeśli sklep ma duże obciążenie to możliwe, że zapytanie czeka na odblokowywanie tabel - zajrzyj w procesy - zobacz ile trwają - na czym wiszą (SHOW PROCESSLIST na przykład - lub poprzez PHPMyAdmin itp)

Jeśli wiszą na tym to InnoDB powinno znacznie pomóc.

[Petre]

Jeśli sklep ma duże obciążenie to możliwe, że zapytanie czeka na odblokowywanie tabel - zajrzyj w procesy - zobacz ile trwają - na czym wiszą (SHOW PROCESSLIST na przykład - lub poprzez PHPMyAdmin itp)

Jeśli wiszą na tym to InnoDB powinno znacznie pomóc.

Co widzę to często pojawia się COPYING TO TMP TABLE.

Jeśli sklep ma duże obciążenie to możliwe, że zapytanie czeka na odblokowywanie tabel - zajrzyj w procesy - zobacz ile trwają - na czym wiszą (SHOW PROCESSLIST na przykład - lub poprzez PHPMyAdmin itp)

Jeśli wiszą na tym to InnoDB powinno znacznie pomóc.

Dostalem odpowiedz od adminow, ze wisi na tych COPYING TO TMP TABLE. Mowia, ze zwiekszyli takze buffor bazy pod to.

Jak widzicie rozbic to na mniejsze zapytania (tak jak pisalem wczesniej) ? Mniej JOINow, ale wiecej zabawy z odpowiednim selekcjonowaniem wynikow do zmiennych.

[netmare]

Ciężki masz ten przykład. Nie znam za bardzo MySQL-a i nie wiem czy może go dociążać to dziwaczne grupowanie po mniejszej liczbie kolumn niż wyświetlana.
Poza tym LEFT JOIN dość spowalnia.

Do tego przenoszenie warunku pomiędzy WHERE a LEFT JOIN ma zgoła odmienne rezultaty a Ty przerzucasz to ot tak (IMG:style_emoticons/default/haha.gif)

Któryś z kolegów napisał żeby MySQL lubi być ograniczony na poziomie tworzenia złączeń więc przerzuć z.stan=1 do złączenia zj, a (kz.id_kat=3009 OR k.id_gr=3009) do złączenia k

Zastanów się czy ma być to tak: LEFT JOIN produkty_jezyki pj ON p.id_prod=pj.id_prod AND pj.id_lang=1 bo to jeśli to jest poprawne to zdaje mi się że wynik będzie identyczny jak to odrzucisz.

Jaką informację ma przedstawiać MAX(p.data_add) AS data_produkt ? Może da się podejść do rozwiązania problemu od innej strony? Bo na localu jak na warunki sklepu internetowego wysoki wydaje się czas 0.1 s.

Wklej EXPLAIN tego zapytania może ktoś zauważy coś ciekawego.