Podstawowa znajomość języka SQL jest umiejętnością wystarczającą, aby tworzyć aplikacje z wykorzystaniem baz danych. Aby jednak aplikacje te były naprawdę solidne, konieczne jest pogłębienie swojej wiedzy z zakresu używanej technologii bazodanowej. W tym wpisie przedstawię kilka porad dotyczących pracy z bazą MySQL (niektóre z nich są bardziej ogólne, ale w przykładach będę używał właśnie tego systemu zarządzania bazą danych), których stosowanie pozwala zwiększyć jakość wytwarzanego oprogramowania.

Na czym miałoby polegać owo zwiększenie jakości? Mówiąc krótko, na lepszym wykorzystaniu zasobów komputera – jego pamięci oraz procesora. Z perspektywy użytkownika część udoskonaleń będzie się zatem sprowadzać do szybszego wykonywania wysyłanych do bazy zapytań. Jako że świetnym sposobem na naukę jest praktyka, polecam przynajmniej niektóre z poniższych porad samodzielnie przetestować na rzeczywistej bazie danych. Jeśli nie macie pod ręką żadnej o dużym rozmiarze (bo na takiej najlepiej obserwować efekty usprawnień), to można zaopatrzyć się w już gotową, pobierając ją np. ze strony MySQL. Ja wybrałem bazę z pracownikami, posiadającą prawie 3 miliony wierszy z pensjami.

1. Indeksy

Indeksy to jeden z najlepszych sposobów na poprawę wydajności zapytań typu SELECT[1]. Sprawiają one, że aby znaleźć konkretne wiersze nie jest konieczne przeskanowanie całej tabeli, co znacząco redukuje czas potrzebny na otrzymanie wyników. Gdy tworzymy indeks na danej kolumnie, baza danych zapisuje na dysku strukturę – w MySQL zazywyczaj w postaci B-drzewa – w której poszczególnym kluczom odpowiadają „wskaźniki” na konkretne wiersze w tabeli.

Zobaczmy to na przykładzie, w którym zapytanie zawierającę klauzulę WHERE będzie wykonywane szybciej gdy użyjemy indeksów:

Jak widać, operacja jest teraz natychmiastowa, choć oczywiście kosztem zużywanego miejsca na dysku. Dodanie indeksów warto też rozważyć dla kolumn, po których następuje złączenie tabel (JOIN), a nie są one kluczami (w MySQL wszystkie klucze są również indeksami) [2].

Niestety indeksy to nie tylko większa szybkość wyszukiwania, ale też wzrost zużycia pamięci dyskowej, w końcu klucze i ich wartości muszą być gdzieś przechowywane. Dlatego prawidłowe wykorzystanie indeksów polega na takim ich dobraniu, aby nie przesadzić w żadną ze stron. Optymalizując istniejącą już bazę danych dobrze jest zacząć od analizy najczęściej wykonywanych zapytań oraz tych, które są najwolniejsze. Liczbę bajtów zajmowanych przez indeksy poszczególnych tabel można zaś sprawdzić, wykonując polecenie:

Należy też być świadomym, że indeksowanie wydłuża czas zapytań modyfikujących tabele – INSERT, DELETE oraz UPDATE ponieważ indeksy muszą być wtedy przeliczane.

2. Limity

Jeżeli potrzebujemy dobrać się tylko do jednego wiersza z tabeli, najlepiej jest od razu zakomunikować to bazie danych przy pomocy klauzuli LIMIT 1. Przykładowo, chcemy dowiedzieć się, czy przed listopadem 1989 był zatrudniony jakiś pracownik, który urodził się 1. maja 1958. Możemy użyć takiego zapytania:

Na moim komputerze czas jego wykonania oscylował w okolicy 0.35 s. Jeśli jednak ograniczymy wyniki do tylko jednego wiersza, istnieje szansa, że uzyskamy znaczące przyspieszenie działania:

Oczywiście w pesymistycznym przypadku, kiedy poszukiwany element znajduje się na samym końcu bazy, nie doświadczymy tak spektakularych rezultatów, niemniej warto stosować tego typu ograniczenia. Jeszcze lepszym pomysłem (choć już bez szans na tak ogromną poprawę wydajności) będzie użycie poniższego zapytania, które sprawi, że z bazy danych zostanie odczytane tylko to, co niezbędne:

3. Cache

Serwer MySQL posiada zaimplementowaną bardzo przydatną funkcjonalność o nazwie query cache. Polega ona na tym, że pewna liczba ostatnich zapytań SELECT wraz z wynikami jest zapamiętywana. Kiedy serwer otrzymuje dowolne zapytanie tego typu, to najpierw porównuje je z tym, co znajduje się w cache – jeśli identyczne zapytanie już się pojawiło, to wynik zwracany jest natychmiastowo, z pominięciem parsowania i wykonywania zapytania [3].

Aby korzystać z dobrodziejstw query cache należy włączyć odpowiednią opcję w pliku konfiguracyjnym MySQL (do znalezienia np. w /etc/mysql/mysql.conf.d/mysqld.cnf):

Szczegóły dotyczące powyższych parametrów można znaleźć pod tym linkiem. Zobaczmy zatem jak w praktyce działa ta funkcjonalność:

Powyższy przykład ujawnia nam kilka ciekawych faktów na temat działania query cache:

• Na sześć wykonanych zapytań wynik pochodzący z cache został zwrócony w dwukrotnie (w zaznaczonych liniach).
• Mimo, że zapytania w liniach 15. oraz 18. są identyczne, to cache nie zostało wykorzystane – wynika to z użycia niedeterministycznej funkcji CURDATE() – chcąc zatem optymalizować nasze zapytania pod kątem użycia cache, należy wystrzegać się użycia takich funkcji (zamiast tego datę można wcześniej wyliczyć w programie i tylko podstawić do zapytania jako łańcuch znaków).
• Zapytanie z linii 21. nie korzysta z optymalizacji query cache – dzieje się tak dlatego, że porównywane zapytania muszą być identyczne, a zatem Select nie jest w tym przypadku tożsame z SELECT – trzeba więc zwracać uwagę na spójność w stosowanych przez nas zapytaniach, skoro nawet drobna literówka może zniweczyć optymalizację.

4. Adresy IP

Adresy IP to dane, z którymi spotykamy się dosyć często. Często również musimy przechowywać je w bazie. Tu jednak pojawia się pytanie: jakiego typu należy w tym celu użyć? Czy potraktować adresy jak dane tekstowe i wykorzystać VARCHAR(15)?

Chcąc oszczędzić pamięć, lepiej będzie zadeklarować kolumnę z adresami IP jako unsigned int, co pozwoli na zapisywanie ich na jedynie 4 bajtach. Do konwersji ze stringa na int używać możemy funkcji wbudowanej INET_ATON , zaś w odwrotną stronę – INET_NTOA:

Powyższy sposób odnosi się do adresów IPv4. Jeśli będziemy operować na IPv6 należałoby skrzystać z kolumny typu BINARY(16), a do konwersji używać funkcji bliźniaczych do powyżej przedstawionych – INET6_ATON oraz INET6_NTOA.

5. Fixed length

Jeśli jako silnika bazy danych używamy MyISAM, warto tworzyć swoje tabele tak, aby były one traktowane jako statyczne (static-format, fixed-length). W tym celu wszystkie kolumny muszą być zadeklarowane jako przechowujące dane o stałej długości. Tabela nie może więc zawierać kolumn typu VARCHAR (zamiast tego użyjmy CHAR), VARBINARY (alternatywnie zastosujmy BINARY), BLOB ani TEXT. Oczywiście nie zawsze taka optymalizacja ma sens, jednak warto o niej pamiętać jeśli rozważamy wybór np. między CHAR a VARCHAR.

Operacje na tabelach w formacie statycznym są wykonywane szybciej ponieważ każdy wiersz ma ustaloną, taką samą liczbę bajtów. Łatwiej zatem można obliczyć jaki jest jego adres w pamięci. Do zalet tego typu tabel zaliczyć też można łatwiejsze cache’owanie oraz prostszą rekonstrukcję w przypadku awarii, wadą jest natomiast fakt, że zazwyczaj zajmują one więcej przestrzeni dyskowej niż tabele o dynamicznym formacie [4].

6. NOT NULL

Wydajność zapytań oraz zużycie pamięci dyskowej można też zoptymalizować dzięki deklarowaniu kolumn jako NOT NULL. Nie zawsze jest to możliwe, ale jeśli i tak nie zamierzamy przechowywać NULL-i albo jesteśmy w stanie je zastąpić przez puste stringi lub wartość numeryczną (np. 0), to warto tak właśnie uczynić. Jak podaje dokumentacja MySQL, operacje są wtedy wykonywane szybciej – możliwe jest lepsze użycie indeksów oraz unikamy wielokrotnego sprawdzania czy przechowywana wartość jest NULL-em. Zyskujemy też jeden bit (zaokrąglany w górę do najbliższego bajta) pamięci na każdym wierszu [4][5].

7. Wyjaśnienia

Na sam koniec zostawiłem poradę najbardziej ogólną, choć wcale nie najmniej przydatną. Chodzi o tak zwany plan wykonania zapytania. Wbudowany w MySQL optymalizator to dość zaawansowana konstrukcja, która przed wykonaniem zapytania bierze pod uwagę różne sposoby na zwiększenie wydajności.  Poprzedzając nasze zapytanie słowem EXPLAIN otrzymujemy informacje o planie jego wykonania – dowiemy się ile wierszy będzie przeszukiwanych, jakie indeksy zostaną użyte itd. Optymalizacja w oparciu o te wiadomości nie należy do najłatwiejszych, bowiem wymaga pewnej wiedzy, jednak pracując często z MySQL z pewnością warto ją zdobyć. W tym celu polecam zapoznać się z m.in. dokumentacją dostępną na oficjalnej stronie, która obszernie wyjaśnia ten dość skomplikowany temat [6].

Źródła

1. MySQL Documentation: Optimization and Indexes.
2. MySQL Documentation: How MySQL Uses Indexes.
3. MySQL Documentation: How the Query Cache Operates.
4. MySQL Documentation: Static (Fixed-Length) Table Characteristics.
5. MySQL Documentation: Optimizing Data Size.
6. MySQL Documentation: EXPLAIN Output Format.