Na pierwszy rzut oka trochę dziwny tytuł i z pewnością nie każdy od razu zrozumie, o co tutaj chodzi.
Zatem wyjaśniam: nie jest to bynajmniej ocena wydajności MySQL-a w jakimś benchmarku, ale prosta zasada „o lokalności danych”. Mówi ona, że 10% kodu zabiera aż 90% czasu potrzebnego do wykonania zapytania czy skryptu. Równie dobrze moglibyśmy tutaj rzucić hasłem „zasada Pareto”, jednak nazwa czy drobna różnica w liczbach zupełnie nie mają tutaj znaczenia. Nie będę jednak opowiadał teraz o trzewiach tego DBMS-a, powiem zaś o czymś, co dotyczy każdego użytkownika bazy oraz jego danych.
Wszelkiego rodzaju systemy, które obecnie wytwarzamy, skoncentrowane są zazwyczaj wokół jednego użytkownika, dla którego gromadzone są dane, lub też wokół czasu, w którym te dane mogą być uzyskane. Oznacza to, że zalogowanemu użytkownikowi pokazujemy informacje, które dotyczą tylko jego osoby (wiadomości, koszyki, produkty), lub też dane, które dotyczą określonego czasu (najświeższe wiadomości, najnowsze promocje etc.). Rzadko się zdarza, że podczas generowania strony głównej serwisu pokazujemy wszystko, co tylko zgromadziliśmy w bazie. Zazwyczaj jest to tylko jakieś „ziarno”, wokół którego należy się skupić. Niestety, pomimo tego, że zazwyczaj skupiamy się na tym „ziarnie” podczas generowania zawartości strony, nie skupiamy się na nim podczas opiekowania się wytworzonym systemem.
Dane, które gromadzimy w cyklu życia systemu, znajdują sobie miejsce w różnych miejscach dysku. Aby uniknąć przestojów w dostępie do tych informacji, programiści budują odpowiednie indeksy. Ale to nie wszystko, co można zrobić w tym zakresie. Jeżeli dane gromadzone były tygodniami, to odczytanie naszych 10% czy 20% zasobów może wygenerować dziesiątki odczytów z IO (odczytów z dysków czy macierzy dyskowych). Nieistotne jest w tym momencie to, czy dane są ładnie poukładane czy nie. Rzadko się bowiem zdarza, że do pobierania danych wykorzystany zostaje tylko indeks – koniec końców i tak chcemy się dostać do całego wiersza danych (lub dużej jego części). Z pewnością wielu czytelnikom zaświtają już w głowie pojęcia „defragmentacja” oraz „optymalizacja”. Niestety, nie są one kompletnym rozwiązaniem naszego problemu.
Rozwiązaniem tego problemu jest zaś cykliczne sortowanie danych według specyficznego klucza, który to wcześniej został przeze mnie nazwany ziarnem. Sortowanie to może przebiegać następująco:
Zazwyczaj systemy DBMS poruszają się na granicy możliwości mechanizmów IO (input/output), co w języku angielskim przyjmuje ładną nazwę „io bound”. Dbanie o poprawne uporządkowanie danych znakomicie oddala nas w wielu wypadkach od krytycznej granicy.
Z pewnością wielu dostawców hostingowych stosuje praktykę sortowania danych klientów po PK – jeżeli nie jesteście pewni, czy tak się dzieje, przeczytajcie dokumentacje lub zapytajcie. Jeśli nie ma takiej możliwości, sami dodajcie zapytanie do cyklicznych wywołań (np. crona). W zależności od ilości danych napływających do systemu należy odpowiednio dobrać okresy optymalizacji baz danych. Tym samym odradzam wykonywanie takich zabaw codziennie – zazwyczaj jedna optymalizacja w tygodniu lub w miesiącu będzie wystarczająca.
To była pierwsza część opowieści, z której jasno wypływa stwierdzenie: trzymaj najczęściej używane dane tak blisko siebie, jak tylko się da. Jednak temat ten nie jest wyczerpany. Jeżeli przyjrzycie się dokumentacji MySQL-a, w wielu miejscach da się znaleźć informacje, z których jasno wynika, że w dziedzinie przechowywania danych należy być minimalistą. Przechowujmy więc tylko konieczne dane, i to w postaci najbardziej dopasowanej do samych danych. Oznacza to, że jeżeli pole „type” zawiera tylko wartości 1–10, to nie jest konieczne stosowanie dla niego typu INTEGER. Warto zmienić typ pola na mniejsze. Praktyka jednak często jest inna – dodajemy informacje i kolumny na zapas, gromadzimy więcej danych, niż jest to potrzebne w danej chwili. Nie mogę negować takiego zachowania – nie zawsze wiemy, z czego będziemy generowali statystyki czy też jakie wielkości pojawią się w stworzonych przez nas kolumnach. Nie jest to jednak patowa sytuacja.
W wypadku gromadzenia zawyżonej ilości danych warto w momencie ich wyciągania z bazy zawęzić liczbę pobieranych kolumn do niezbędnego minimum oraz przesunąć wszelkie kolumny, z których aktywnie korzystamy, na początek wiersza. Przykładowo: jeżeli mamy taki oto układ tabeli:
id: integer, count: integer; referer: varchar; cookie: varchar; link: varchar
a podczas tworzenia raportów wykorzystujmy tylko pola: id, count i link, to warto te kolumny trzymać obok siebie.
Jeżeli przechowujemy znaczną ilość danych (szczególnie w polach tekstowych) i cierpimy z powodu niedostatków operacji IO, mamy natomiast spory zapas CPU – doradzałbym wówczas użycie kompresji. Z pewnością mocniej obciąży ona CPU, jednakże może się okazać, że wszystko zacznie pracować o wiele wydajniej.
W wypadku, gdy na początku projektu nie można było określić odpowiednich typów danych, warto co jakiś czas analizować to, co znajduje się w tabelach, i odpowiednio modyfikować ich strukturę. W tym celu należy wykorzystać polecenie:
SELECT … FROM … WHERE … PROCEDURE ANALYSE([max_elements,[max_memory]])
Wygenerowany raport może okazać się bardzo przydatny.