mysql tabele tymczasowe – optymalizacja zapytań

Niedawno rozpocząłem pisać o bazach danych. Do tej pory omijałem te tematy z pełną świadomością ponieważ uważałem, iż dodanie indeksów spowoduje przyśpieszenie działania. Lecz w moich projektach pensje.net i grolix.pl problem z wydajnością zapytań okazał się poważniejszy, i nie wystarczyło dodać indeksów :) Ze skomplikowanymi zapytaniami poradziłem sobie wykonując tabele tymczasowe…

Opis problemu

Jako przykład weźmy serwis http://grolix.pl. Na witrynie mam wiele rankingów, tzn najczęściej odwiedzane oraz najlepiej oceniane w segmentacjach: dzis/wczoraj/tydzien/miesiac/rok. W bazie mam taką strukturę:

games
	id
	name

views
	game_id
	date
	cnt

votes
	game_id
	date
	vote

Data jest w formacie DATE czyli YYYY-MM-DD. Teraz aby wyciągnąć dla każdej gry ilość odsłon należałoby wykonać zapytanie:

1
2
3
4
5
6
7
SELECT SQL_NO_CACHE g.id, sum(gv.VIEW) AS VIEW
FROM games_views gv, games g
WHERE date_format(gv.date, "%Y") = date_format(NOW(), "%Y")
AND gv.game_id=g.id
GROUP BY game_id
ORDER BY VIEW DESC
czas: ~0.02

Średnią głosów

1
2
3
4
5
6
7
SELECT SQL_NO_CACHE g.id, ROUND(AVG(gv.vote)) AS AVGvote
FROM games_votes gv, games g
WHERE date_format(gv.date, "%Y") = date_format(NOW(), "%Y")
AND gv.game_id=g.id
GROUP BY game_id
ORDER BY AVGvote DESC
czas ~0.33

Lecz mnie interesuje zarówno i ilośc odłosn i średnia głosów:

1
2
3
4
5
6
7
8
9
SELECT SQL_NO_CACHE g.id, sum(gv.VIEW) AS VIEW, ROUND(AVG(gv2.vote)) AS AVGvote
FROM games_views gv, games g, games_votes gv2
WHERE date_format(gv.date, "%Y") = date_format(NOW(), "%Y")
AND date_format(gv2.date, "%Y") = date_format(NOW(), "%Y")  
AND gv.game_id=g.id
AND gv2.game_id=g.id
GROUP BY g.id
ORDER BY VIEW DESC
czas: ~0.60

Jak widać są to proste zapytania, lecz dokładając kolejne połączenia z innym tabelami czas wykonywania szybko rośnie, w przypadku tego serwisu tak być nie może, ponieważ zapytania takie będą mogły być dość często wykonywane, pomimo używania mojego cache :). Aha w bazie jest mało rekordów w przyszłości ma tam ich być o wiele wiele więcej :)

Rozwiązanie problemu…

…jest dość proste. Z tej racji, że są to dane statystyczne nie powinny być obliczane za każdym razem, powinniśmy je składować, np w tabeli:

game_stat
	game_id
	day_view
	week_view
	month_view
	year_view
	day_vote
	week_vote
	month_vote
	year_vote

A później za pomocą prostego zapytania będziemy wyciągać te dane. Będą one musiały się aktualizować co średnio 5 minut – tu juz wystarczy cron.

Ok a poniżej prezentuje zapytaniajakich użyłem do stworzenia tabeli tymczasowej i jak ją później wykorzystałem :)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//utworzenie tabeli z sql
CREATE TEMPORARY TABLE ViewRok AS
    SELECT game_id, sum(VIEW) AS AVGvote
    FROM games_views WHERE date_format(date, "%Y") = date_format(NOW(), "%Y")
    GROUP BY game_id;
czas: ~0.02

//dodanie indexa - aby późniejszy UPDATE BYł szybszy
ALTER TABLE ViewRok ADD UNIQUE (game_id);
czas: ~0.01

//UPDATE
UPDATE games_stats g
    LEFT JOIN ViewRok VD ON VD.game_id=g.id
    SET view_rok=AVGvote
WHERE g.active=1;
czas: ~0.02

Takich zapytań wykonuję tyle ile potrzebuje obliczeń statystycznych, czas wykonywania tych powyższych poleceń w sumie ok 0.05 sek :). Jak widać tutaj zastosowanie tabel tymczasowych do obliczeń miało dość spore uzasadnienie, chociaż i tak używam w tym serwsie mojego cache więc jakos zbytnio, praktycznie nie przyśpieszyłem działania serwisu.

Podsumowanie

Tabele tymczasowe w mysql to potężna broń w walce z obciążeniami :), przydaje się nie tylko podczas obliczania statystyk lecz również w sytuacjach kiedy jesteśmy skazani na podzapytania – wtedy towrząc tabelę tymczasową, oraz jakieś proste updaty możemy wykonać super robotę optymalizacyjną, ponieważ tworzenie takich tabel jest szybkie a po wykonaniu zapytań automatycznie takie tabele znikają :).

Link do wspomnianego cache http://blog.zeromski.com.pl/2008/11/07/cachowanie-funkcji-przyspieszenie-generowania-stron/.

 

Tagi: , ,

Komentarze: 6 do “mysql tabele tymczasowe – optymalizacja zapytań”

  1. 1 eRIZ     2008-12-07 18:17:06

    Coś mi nie pasuje; niby niewiele rekordów i się ślimaczy?
    A czemu do statystyk nie zrobisz tabeli MEMORY?

  2. 2 Mateusz Żeromski     2008-12-07 19:56:45

    Hmm, ilości rekordów
    games – 8 300 (oczywiscie nie wszystkie aktywne)
    views – 10 398 (tu juz prawdziwe dane :) )
    votes – 83 869 (początkowo każdej grze nadałem 10 ocen)
    Więc jest trochę rekordów, najbardziej będzie puchło views, ale moze to jakos zoptymalizuję kiedyś.

    Jeżeli chodzi o memory to nie sądze aby tu miało zastosowanie, przyjmując milionowy wzrost odwiedzin ilość rekorów się zwiększy, będę zajmował coraz to więcej pamięci itp – no tak ale to tylko teoria.
    Praktycznie serwer na którym stawiam moje produkty nie stoi u mnie fizycznie i nie mam kontroli nad tym czy przypadkiem nie został zresetowany. Bo jak wiadomo po resecie memory idzie w zapomnienie. Przy użyciu memory bym musiał wykonywać crona co 1 minute i sprawdzać non stop czy jest ta moja tabela jak nie to bym musiał ją robić – niepraktyczne. Przy zastosowaniu memory zysku by nie było (bo jak napisałem i tak cachuje zapytania) a roboty więcej, i byłby dodatkowy krytyczny element serwisu.

  3. 3 eRIZ     2008-12-07 21:29:19

    sprawdzać non stop czy jest ta moja tabela jak nie to bym musiał ją robić

    I tu jesteś w błędzie. ;) Tabela istnieje zawsze, tylko jej zawartość jest wymazywana.

    Przy zastosowaniu memory zysku by nie było (bo jak napisałem i tak cachuje zapytania)

    Cache’ujesz, ale na HDD.

    Jeżeli chodzi o memory to nie sądze aby tu miało zastosowanie, przyjmując milionowy wzrost odwiedzin ilość rekorów się zwiększy, będę zajmował coraz to więcej pamięci itp – no tak ale to tylko teoria.

    Niekoniecznie. Resety serwera nie zdarzają się tak często. Możesz zrobić do tej tabeli coś w stylu journala – początkowo (po resecie) wczytać dane z głównej tabeli i tylko je modyfikować na podstawie danych użytkowników -> odwiedziny są zapisywane do dziennika @ memory. Select wykona się dużo szybciej niż na temporary. ;)

    No i – oczywiście – raz na jakiś czas commit, aby zapisać wyniki.

  4. 4 eRIZ     2008-12-07 21:31:19

    PS. Poza tym, dlaczego zamiast zapisywać oceny wpisując w pola: ocena, liczba głosów, to trzymasz je w osobnych rekordach? :P

    Nie dziw się, że Ci tak baza jeździ, skoro takie podstawowe dane trzymasz nieoptymalnie. ;) Gdybyś miał komentarze do tych ocen, to jeszcze by to miało sens.

  5. 5 Mateusz Żeromski     2008-12-07 21:42:04

    Brak danych w tabeli a brak tabeli – dla mnie oznacza jedno i to samo – brak danych, co trzeba sprawdzać.

    Czy cachowanie na hdd ma jakieś wady? Oczywiście memory szybsze by było ale wolę rozwiązania ze 100% pewnością – serwer nie mój, więc wolę nie ryzykować.

    Odnośnie przechowywania ocen każdej z osobna i odsłon dla dni w każdym wierszu to mi zależy na tym aby robić statystyki – najpopularniejsze dziś/wczoraj/tydzien/miesiac/rok i podczas gdy tak trzymam dane jest to proste. Owszem jest to nieoptymalne, bo lepiej by było operować na plikach itp ale mogę sobie pozwolić na zajęcie 100 MB więcej, lecz nie mogę pozwolić aby zapytania wykonywały się przez 1 sek. Skala zajętości tych danych jest mała – gdyby dzienny przyrost wynosiłby więcej niż 20 MB no to bym się zastanawiał nad innym rozwiązaniem, ale narazie to mnie nie dotyczy – mam nadzieję że będę miał taki problem w przyszłości w tym przypadku :)

  6. 6 pch     2009-02-09 05:57:08

    A gdzie tu są indeksy? Powinny być na game_id oraz na date. Tylko żeby indeks na date zadziałał, przy zapytaniu nie wolno wykonywać konwersji daty.

Napisz komentarz