Optymalizacja bazy danych Opcje

[wachcio]

Witam

Mam bazę danych którą opisałem wcześniej Temat: Wyszukiwanie danych w kilku tabelach
Na razie składa się ona z 11 tabel gdzie każda przechowuje w kolumnach numer porządkowy, datę odczytu i wartość odczytu z różnych czujników np temperatury. Dane gromadzone są od maja tego roku a odczyty co minutę. Daje to na tę chwilę ok 250 000 rekordów na każdą tabelę czyli niecałe 3 000 000 rekordów w bazie. Zrobiłem sobie prostą stronę aby wyświetlać jak na razie:
- ostatni odczyt np.

[PHP] pobierz, plaintext 
$temp_raspberry_pi = mysql_query ("SELECT `value`,`time` FROM `Raspberry_Pi` ORDER BY  `Raspberry_Pi`.`time` DESC LIMIT 2");
$temp_raspberry_pi = mysql_fetch_assoc($temp_raspberry_pi);
[PHP] pobierz, plaintext 

- najwyższy odczyt z dziś

[PHP] pobierz, plaintext 
$hi_temp_raspberry_pi = mysql_query ("SELECT `value`,`time` FROM  `Raspberry_Pi` WHERE DATE(  `time` ) = CURDATE( ) ORDER BY  `Raspberry_Pi`.`value` DESC  LIMIT 2");
$hi_temp_raspberry_pi = mysql_fetch_assoc($hi_temp_raspberry_pi);
[PHP] pobierz, plaintext 

- najniższy odczyt dziś

[PHP] pobierz, plaintext 
$low_temp_raspberry_pi = mysql_query ("SELECT `value`,`time` FROM  `Raspberry_Pi` WHERE DATE(  `time` ) = CURDATE( ) ORDER BY  `Raspberry_Pi`.`value` ASC LIMIT 2 ");
$low_temp_raspberry_pi = mysql_fetch_assoc($low_temp_raspberry_pi);
[PHP] pobierz, plaintext 

Udało mi się uporać z problemami przedstawionymi w poprzednim poście ale wyświetlanie tych wyników dla wszystkich 11 tabel trwało ok 14 sek (IMG:style_emoticons/default/sad.gif) Poczytałem jak mogę zoptymalizować bazę. Dodałem indeksy na kolumny z datą i wartością odczytu. W zapytaniu SELECT uściśliłem potrzebne mi kolumny oraz dodałem klauzulę LIMIT. Strona wczytuje się teraz ok 6,5 sek czyli sporo mniej ale i tak dość długo. Zauważyłem, że najwięcej czasu zajmują zapytania wybierające rekordy z dziś. Wiadomo, że z czasem problem będzie narastał bo i danych będzie przybywało. Poza tym planuję dodawać nowe czujniki...
Co jeszcze mogę zrobić aby poprawić wydajność?

[wachcio]

Kolega pmir13 bardzo dobrze zrozumiał co miałem na myśli i dobrze to opisał. Na razie 11 tabel z 3 kolumnami każda. Kiedy w skrypcie odczytuję dane robi się już 33 zapytania do bazy przez co czas jest dość długi. Mam dostęp do bazy danych gdyż jest to tylko kopia do której system odbioru danych z czujników (nettemp) zapisuje co minuta zduplikowane wartości ze swojej własnej bazy. W przyszłości będzie dodatkowe urządzenie (urządzenia) które będą zbierały dane z kolejnych czujników i zapisywały je do tej właśnie bazy. Dlatego też pomysł był taki aby mieć wszystkie odczyty w jednym miejscu z możliwością dość łatwej rozbudowej o dalsze funkcje czyli np. raport ze średniej temperatury z danego okresy z danego czujnika itd.
Widzę, że koledzy proponują różne rozwiązania i bardzo dziękuję Wam za to ale muszę mocno podszkolić się w MySQL aby niektóre sprawdzić w praktyce...
Do tej pory sprawdziłem najłatwiejszy do zrobienia sposób czyli zamiast CURDATE() na sztywno wpisać datę i godzinę z BETWEEN niestety bez jakiejś zauważalnej różnicy w czasie wykonania.
Zainteresował mnie szczególnie pomysł kolegi pmir13 ale nie bardzo wiem jak go zrealizować niestety... Muszę przeczytać post kilka razy i może wtedy uda mi się lub poprosić o jakieś dalsze wskazówki (IMG:style_emoticons/default/wink.gif)


EDIT:

Udało mi się zmodyfikować pliki systemu Nettemp i teraz kopię danych w mojej bazie danych zapisuje w postaci:

Kolumna Typ Null Ustawienia domyślne
id int(6) Nie
time timestamp Nie CURRENT_TIMESTAMP
value float Tak NULL
data date Nie IndeksyDokumentacja


Nazwa klucza Typ Jednoznaczny Spakowany Kolumna Moc Metoda porównywania napisów Null Komentarz
PRIMARY BTREE Tak Nie id 261521 A Nie
time BTREE Nie Nie time 261521 A Nie
value BTREE Nie Nie value 318 A Tak


Czyli dodałem kolejną kolumnę o nazwie "data" typu "date" w której on zapisuje na bieżąco aktualną datę. Reasumując teraz w każdej tabeli mam dwa razy datę zapisu. Raz jako "CURRENT_TIMESTAMP" i raz jako samą datę.
Teraz gdzie powinienem założyć a gdzie (i jak) ewentualnie usunąć indeksy w tabelach? Jak dodać samą datę w brakujących rekordach? Czy wystarczy w zapytaniu o ekstremalne wartości z danego dnia zmienić wyszukiwanie z kolumny "time" na "data"?

Ten post edytował wachcio 16.12.2016, 21:53:37