Optymalizacja zapytań (całej bazy) Opcje

[adamp359]

Witam
jestem nowy na forum ale często tu zaglądałem bez konieczności rejestracji niestety do dnia dzisiejszego nie mam pojęcia jak rozwiązać dużą ilość zapytań do bazy danych przejdę wiec do rzeczy.

Skrypt który napisałem monitoruje pewne parametry w internecie i zapisuje je do bazy danych dane te są spradzane co pewnien okres czasu który jest obliczany na postawie ostaniego pobrania danych tabel tych jest 11 skrypt sprawdza wszystko co minutę w cronie czy ma pobrać dane czy jest spełniony warunek.

Poniżej podaje przykładowe zapytanie do bazy danych.

[SQL] pobierz, plaintext 
SELECT * FROM `strony` s
WHERE (SELECT DATA FROM `dane` WHERE id_strony=s.id  ORDER BY id DESC LIMIT 1) <=  $data - (60*60*24*data_dane) AND data_dane >=1 AND temp_dane = 0 
[SQL] pobierz, plaintext 

struktura bazy dane

[SQL] pobierz, plaintext 
id 	id_strony 	wartosc 	DATA
[SQL] pobierz, plaintext 

jeśli zostanie spełniony waruken to uruchamia się skrypt pobierania danych który też zabiera jakiś czas na odczytanie danych z innego serwra wiec dla każdej strony dodane jest pole temp_dane do której jest zapisywana wartość 1 jeśli jest w trakcie pobierania a nastepnie po zakończeiu robie update do bazy z wartoscią = 0 zapewne ten proces też męczy trochę serwer MySQL ale nie znalazłem innej metody na blokowanie danego rekordu aby dane nie były pobrane dwa razy w jedej minucie (miałem takie przypadki wcześniej i dlatego dodałem takie pole w bazie)

moje pytanie jak zrobić bardziej optymalną bazę lub zapytanie do bazy danych przedstawione powyrzej.
Dodam że tabele z danymi często mają parę tysięcy rekordów a nawet około 50-90 tys.(niby nie dużo ale serwery siadają od tego skryptu)


Myślałem żeby zrobić do tabeli strony datę ostatniego pobrania aby nie mielić ciągle tabeli dane w której jest dużo wiecej danych niż w tabeli strony i następnie sprawdzać tylko dane z jednej tabeli czy to dobry pomysł ?

Trochę może nie jasno to opisałem lecz mam nadzieje, żę komuś uda mi się pomóc
Z góry dziękuje
Adam

[trafas]

Myślałem żeby zrobić do tabeli strony datę ostatniego pobrania aby nie mielić ciągle tabeli dane w której jest dużo wiecej danych niż w tabeli strony i następnie sprawdzać tylko dane z jednej tabeli czy to dobry pomysł ?

To może być dobry pomysł, bo wtedy odparnie ci sortowanie:

[PHP] pobierz, plaintext 
SELECT DATA FROM `dane` WHERE id_strony=s.id  ORDER BY id DESC LIMIT 1
[PHP] pobierz, plaintext 

A datę tą dla danej strony mógłbyś odświeżać jakimś np. triggerem.

A pobieranie strony mógłbyś zamykać w transakcji.
Wtedy dane by ci wpadały do tabeli dopiero z commitem i dla kolumny temp_dane od razu z wartośćią 0 lub już wtedy zrezygnować z tej kolumny.

[rzymek01]

Na początek sprawdź czy masz odpowiednie indeksy, a przy select możesz dać explain

[adamp359]

To może być dobry pomysł, bo wtedy odparnie ci sortowanie:

Właśnie myślę, że te sortowanie paru tysięcy rekordów najwiecej obciąża

A datę tą dla danej strony mógłbyś odświeżać jakimś np. triggerem.

Nie używałem nigdy tego ale pobierzenie teraz sobie po czytałem

A pobieranie strony mógłbyś zamykać w transakcji.

Wcześniej próbowałem (przed dodaniem tabeli temp_dane) używać transakcji lecz to nie działało jak chciałem nadal skrypt "potrafił" pobrać dwa razy dane
chyba, że źle robiłem.
Może dodanie funkcji blokującej do crona coś mi pomoże (nie będą się dupblowały procesy) i skrypt nie da rady pobrać danych dla tej samej strony kiedy jeszcze się nie skończyło pobieranie poprzednie. Poniżej komenda cron (może komuś się przyda)

Kod

flock -n /tmp/nazwa_operacji.$$$ wget -q -F -O - http://www.link_do_crona.pl/cron.php >/dev/null 2>&1

W moim przypadku lepiej spradzało się LOCK TABLES lecz skrypt się bardzo spowolnił

Na początek sprawdź czy masz odpowiednie indeksy, a przy select możesz dać explain

Takie coś mi wyszło indeksy mam na kolumnie ID w obu tabelach.

[SQL] pobierz, plaintext 
id 	select_type 	TABLE 	type 	possible_keys 	KEY 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	s 	ALL 	NULL 	NULL 	NULL 	NULL 	10 	USING WHERE
2 	DEPENDENT SUBQUERY 	dane_site_google 	INDEX 	NULL 	PRIMARY 	4 	NULL 	1 	USING WHERE
[SQL] pobierz, plaintext 

[rzymek01]

Nadrzędny select nie ma żadnych kluczy + podzapytanie, to na pewno pogarsza złożoność.

Przebuduj zapytanie na join,
warunek ON to:

[SQL] pobierz, plaintext 
dane.DATA <= $data - (60*60*24*data_dane)
[SQL] pobierz, plaintext 

i na tym polu daj indeks

To powinno dać spory zysk wydajności

[adamp359]

Przebuduj zapytanie na join,
warunek ON to:

hmm ale jak posortować i wybrać ostatnią wartość z tabeli z danymi ?
możesz przysłać jak by wyglądało całe zapytanie ?
Bo ja jakoś nie umiałem zrobić tego na joinie bo wcześniej już próbowałem i dlatego zastosowałem dodatkowego selecta aby wybrac ostatni rekord z bazy z danymi o dacie

[rzymek01]

faktycznie, nie zwróciłem na to uwagi,
w tej chwili nie mam pomysłu na join, w sumie można by zrobić dodatkowe pole, które zawierało by id ostatniego wpisu, uaktualniane triggerem przy insercie do tabeli dane, no ale to narusza niejako obecną strukturę bazy,
w każdym razie załóż indeks na DATA, bo nadrzędny select ma where po polach, które nie są objęte żadnymi indeksami.
w zależności od danych mogą pomóc indeksy na pozostałe pola użyte w where...

[adamp359]

Zrobiłem tak dodałem pole data do każdej strony i przechowuje tam datę (bezpieczniej by było ID ostatniej daty ale tak to by były odwołania do dwóch tabel nadal) a tak mam wszystki w jedej tabeli dodatkowo dałem indeksy we wszystkich tabelach na dane po których wyszukuje dane i o dziwo jak wcześniej dane się wczytywały około 4-5 sekund teraz mam 0.7794 sec nie raz nawet i szybciej.
Dzięki za pomoc