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.

[pmir13]

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ę).

Na podstawie pierwszego postu i szacowanych ilości rekordów. Mamy 11 tabel, osobno dla każdego czujnika, w każdej co minutę insert od maja (jak inaczej wytłumaczyć 250k rekordów na tabelę?), szukamy dla każdego czujnika najwyższej i najniższej wartości dla danego dnia. O ile znalezienie tych wartości dla jednej tabeli daje autorowi w tej chwili akceptowalny czas wykonania, to gdy trzeba to powtórzyć dla wszystkich tabel spowolnienie jest już wyraźne. Myślę, że problem przedstawiony jest wystarczająco precyzyjnie.

To że czujnik ma najświeższe dane nic nie zmienia, jeśli chcemy najwyższą temperaturę dzisiaj, nie interesuje nas wpis z letnich upałów.

Dodanie dodatkowej kolumny na datę jest tutaj najbardziej oczywistym krokiem, jednorazowy update na dotychczasowe dane, trigger after insert na wyciągnięcie i zapisanie samej daty (być może również after update o ile robimy update oprócz insertów) plus dwukolumnowy indeks na (date,value) i mamy problem z głowy. Rozwiązanie bez grzebania w aplikacji zbierającej odczyty, opierające się wyłącznie na samej bazie danych (skoro autor może dodawać indeksy to ma do niej dostęp) i niezależne od wielkości bazy, bo dodatkowe czynności przy insertach co minutę nie wpłyną w zauważalny sposób na pracę bazy, a potrzebne do wyświetlenia na stronie dane dostajemy natychmiast z indeksu.