MySQL - optymalizacja widoku

[krzyszp]

Mam problem z pewnym widokiem:

Kod [Zaznacz] Rozwiń

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`roads`@`%` SQL SECURITY DEFINER VIEW `Widok1` AS
SELECT `tblstockkitsconts`.`KitID` AS `KitID`,truncate(min(((`tblstock`.`StockQty` - `tblstock`.`DueOut`) / `tblstockkitsconts`.`Qty`)),0)
AS `Available`
FROM ((`tblstockkitsconts` join `tblstock` on((`tblstockkitsconts`.`StockID` = `tblstock`.`StockID`)))
JOIN `tblstockkits` ON((`tblstockkits`.`KitID` = `tblstockkitsconts`.`KitID`))) GROUP BY `tblstockkitsconts`.`KitID`

Niestety, po rozrośniéciu sie tabel tblstockkits i tblstockkitscount widok wykonuje sie kilkanascie sekund.
Po malym sledztwie dotarlo do mnie, ze ten widok nie uzywa indeksow, wiec musze uzyc FORCE INDEX... Tylko w ktorym miejscu?

[cp]

Edit: tak, wiem, odpowiedź nie do końca zgadza się z pytaniem

Insufficient data  :wacko:

Napisz coś więcej o zapytaniach, które korzystają z tego widoku, liczbach rekordów w poszczególnych tabelach oraz o pozakładanych indeksach.

Nie wiem, jak biegły jesteś w bazach danych, więc możliwe, że już znasz i próbowałeś, ale zaryzykuję i podrzucę parę pomysłów.

Jak możesz użyć, to spróbuj zmodyfikować tabele źródłowe z wykorzystaniem tego:

Kod [Zaznacz] Rozwiń

http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

A, i sprawdź, czy w momencie odpytywania tego widoku na tabelach są aktualne statystyki (wiem, oczywista oczywistość; nie obraź się, że o tym wspominam)

P.S.
Eewentualnie rozważ denormalizację i zapisywanie tych wyliczeń bezpośrednio w jednej z tabel, w dodatkowej kolumnie.

P.P.S.
Na kolejną odpowiedź ode mnie będziesz musiał niestety czekać do niedzieli

A sam FORCE INDEX powinien pojawić się zaraz po nazwie tabeli, której dotyczy

[krzyszp]

Tak myślałem, że za mało danych podaję :)

Generalnie, ten widok jest użyty do wykonania dwóch update'ów:

Kod [Zaznacz] Rozwiń

sSQL = "UPDATE tblstockkits JOIN Widok1 ON tblstockkits.KitID = Widok1.KitID SET FreeStock = Widok1.Available WHERE Widok1.KitID = tblstockkits.KitID;"
cnM.Execute sSQL
sSQL = "UPDATE tblebayitems JOIN Widok1 ON tblebayitems.KitID = Widok1.KitID SET FreeStock = Widok1.Available WHERE Widok1.KitID = tblebayitems.KitID;"
cnM.Execute sSQL

i służy do wyliczenia dostępności tzw 'kitów', czyli produktów skompletowanych z kilku innych.
Ze względu na to, że system nie miał nigdy takiego czegoś zawierać (co niestety się zmieniło już w produkcyjnej aplikacji), trzeba było utworzyć "sztuczne", dodatkowe tabele, które to przechowują. Problemu nie było, dopóki kitów była rozsądna ilość (~100), niestety, w przeciągu kilku ostatnich dni właściciel wygenerował ich ponad tysiąc (bo się dobrze sprzedają) i serwer teraz na tych dwóch zapytaniach zużywa 15s, a jest to operacja, która musi się wykonywać przy każdej operacji na towarze (sprzedaż, zakup)...

Partycjonowanie raczej nie ma sensu - cała baza to raptem 66MB w tej chwili.

Całość mnie w ogóle dziwi, bo na serwerze opartym na turionie wykonuje się dokładnie tyle samo czasu, co na E5-2620...

[Dario666]

A nie jest wykonywanych zbyt dużo operacji I/O, które blokują cały system...?
Może by trzeba było zmienić konfigurację/parametry dostępu do MySQLa.

[krzyszp]

A nie jest wykonywanych zbyt dużo operacji I/O, które blokują cały system...?
Może by trzeba było zmienić konfigurację/parametry dostępu do MySQLa.

Zdecydowanie to IO ogranicza, sprawdziłem na mocnijeszym serwerze. Dlatego też zdecydowałem się przenieść te operacje do innej części softu i odpalać je w osobnym wątku na timerze co 1 minutę.
Niemniej, dalej mnie interesuje jak skutecznie zastosować FORCE INDEX w tym konkretnym przypadku.

Ps. (Całość siedzi na InnoDB i ma przydzielone dość RAM'u, aby całkiem na nim działać).

[Dario666]

Z tego co pamiętam z czasów, kiedy zajmowalem się bazami danych to to, że powinno się unikać aktualizacji widoków, bo "strasznie" obciążają system baz danych.
Trzeba by przemyśleć strukturę takiej DB.

[krzyszp]

Widok się aktualizuje za każdym razem jak go odczytujesz, taka jego cecha i dlatego jest tutaj zastosowany.
Zresztą, rozwiązanie takie powstało dlatego, że już w systemie produkcyjnym trzeba było dołożyć funkcjonalności, które nie były przewidziane na początku.

[Dario666]

Tak, ale samoaktualizacja widoku to zupełnie inną sprawa od aktualizacji zawartości DB poprzez widok.

[cp]

Zresztą, rozwiązanie takie powstało dlatego, że już w systemie produkcyjnym trzeba było dołożyć funkcjonalności, które nie były przewidziane na początku.

Hmm, MySQL to w zasadzie nie moja bajka. Ostatni raz miałem z tym styczność jakieś cztery lata temu, a i to tylko pobieżnie. Od dawna siedzę po uszy w Oracle'u. Ale bazując na tym doświadczeniu wygrzebałem coś, co jest odpowiedzią na początkowe pytanie. Otóż RDBMS Oracle ma funkcję PUSH PREDICATE, czyli przesłanie parametrów z zewnętrznego SQL-a do zapytania definiującego widok. Najwyraźniej w MySQL-u nikt nigdy tego nie zaimplementował, nawet gdy MySQL A.G. MySQL AB trafił pod skrzydła Oracle Corp.

Kod [Zaznacz] Rozwiń

http://stackoverflow.com/questions/13944946/how-do-i-get-mysql-to-use-an-index-for-view-query

Wychodzi na to, że w twoim przypadku najlepiej byłoby zmaterializować ten widok do tabeli. Wtedy pewnie nawet nie będzie potrzebny FORCE INDEX.

P.S.
Edit
a jeśli już koniecznie, absolutnie i definitywnie musisz użyć tego widoku, wywal go z UPDATE-ów. Wyciągnij z niego potrzebne dane do części aplikacyjnej i na podstawie tego, co zwróci widok wyślij do bazy polecenia UPDATE. Ewentualnie zawrzyj wszystko w odpalanej raz procedurze przechowywanej w bazie. Tyle, że w obydwu przypadkach możesz zrezygnować z widoku, a zapytanie wysyłać bezpośrednio do bazy, wraz z klauzulą FORCE INDEX... :whistle:

[krzyszp]

Przerzucanie do tabeli sensu nie ma, gdyż w takim przypadku musiałbym ściągnąć dane do aplikacji, przeliczyć i puścić kilka setek update'ów na dwie tabele... Taka operacja zajmuje znacznie więcej czasu (opóźnienie przez sieć).

Niemniej, mam pewną koncepcję. Otóż zarzucę triggera na tabelę tblstock na kolumnę Qty, który mi zaktualizuje resztę (tblstockkits i tblebayitems). Może to przyniesie efekt i nie zabije silnika...