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ść?

[pmir13]

Z tego co autor napisał wynika, że tabele są dla różnych czujników, a każda ma dane od początku, więc WHERE jest niezbędny by mieć dane z konkretnego dnia.
Co do optymalizacji pierwszym problemem jest tutaj fakt, że w tym warunku WHERE wykonujemy funkcję na kolumnie, co nie kwalifikuje indeksu na tej kolumnie do wykorzystania. Można próbować przepisać to w ogólnej postaci jako na przykład

[SQL] pobierz, plaintext 
 ... WHERE `time` BETWEEN '2016-08-01 00:00:00' AND '2016-08-01 23:59:59'
[SQL] pobierz, plaintext 

albo prościej dla dzisiejszego dnia o ile nie ma możliwości by były jakieś późniejsze wpisy:

[SQL] pobierz, plaintext 
 ... WHERE `time` > CURDATE()
[SQL] pobierz, plaintext 

jednak w najlepszym przypadku dostaniemy w explain type=range, a do tego nie da się potem podczepić następnej części indeksu do sortowania wartości. W zależności od ilości danych może to być szybsze niż pełen skan tabeli (type=all) czy bardziej prawdopodobny skan indeksu (type=index), jednak wciąż w obrębie dnia będziemy mieć sort by znaleźć maksymalną czy minimalną wartość.
Dopiero denormalizacja i wprowadzenie dodatkowej kolumny zawierającej wyłącznie datę bez czasu oraz wprowadzenie podwójnego indeksu na (data,wartość) umożliwi nam dla zapytania typu

[SQL] pobierz, plaintext 
... WHERE `date` = CURDATE() ORDER BY `value` DESC LIMIT 1
[SQL] pobierz, plaintext 

pełne wykorzystanie tego indeksu (w explain będzie type=ref oraz ref=const) i możemy mieć w tabeli pierdyliard rekordów a wynik dostaniemy bardzo szybko - mysql znajdzie konkretne miejsce z danymi po indeksie btree w logarytmicznym czasie.

[trueblue]

Z tego co autor napisał wynika, że tabele są dla różnych czujników, a każda ma dane od początku, więc WHERE jest niezbędny by mieć dane z konkretnego dnia.

Zgadza się, że są dla różnych czujników, ale nie wiem na jakiej podstawie wnioskujesz, że każdy czujnik ma dane z innych dni.
Z tego co napisał autor, każdy czujnik ma najświeższe dane (zbiera co minutę).
Tak więc WHERE jest niepotrzebny.
Ale może się zdarzyć sytuacja, że dany czujnik nie działa i jeśli chcemy wyświetlić dane tylko z dzisiejszego dnia. Wtedy rozwiązaniem może być sprawdzenie danych już na poziomie skryptu. Podobnie gdyby się jednak okazało, że dane są z różnych dni.