Optymalizacja baz danych MySQL

Wiele osób (i to nie tylko związanych z programowaniem) marzy o stworzeniu aplikacji mobilnej lub internetowej, z której będzie korzystać wiele osób. Dla własnej satysfakcji i rzecz jasna dla zarobku. Może się wydawać, że największym wyzwaniem takiego przedsięwzięcia jest samo stworzenie takiej aplikacji która będzie dostarczała samych pozytywnych doświadczeń użytkownikom a potem promocja, promocja sposób na monetyzację i optymalizacja podatku.

Nic bardziej mylnego…

W obecnych czasach stworzenia aplikacji to żaden problem – w Internecie aż roi się od ofert freelancerów i software-houseów które z pomocą niewidzialnej ręki rynku skorygowały ceny za swoje usługi programistyczne z wysokich na… uczciwe.

Promocja też nie jest problemem. Płatną reklamą z Facebooka jesteśmy w stanie dotrzeć do setek tysięcy osób, których persony sobie fajnie dobierzemy w ustawieniach kampanii, wystarczy po prostu zaoferować coś bardziej sensownego od konkurencji żeby się to fajnie sprzedawało.

Największym wyzwaniem dla twórcy takiej aplikacji jest jej utrzymanie i skalowanie.  Jednym z fundamentów utrzymania aplikacji jest zapewnienie bezpieczeństwa a drugim minimalizacja kosztów i optymalizacja. Oczywiście są jeszcze kwestie integralności i dostępności ale o tym porozmawiamy kiedy indziej…

Gdy hostingu (a ściślej bazy MySQL) zaczyna brakować…

…można hosting skalować pionowo i poziomo, czyli kupić po prostu serwer z większymi zasobami albo dokupować kolejne tworząc replikację baz danych MySQL, partycjonowanie funkcjonalne i tzw. data-sharding baz danych. Można też odłożyć te kosztowne lub skomplikowane działania w czasie i zabrać się za porządną optymalizację kodu i bazy danych. To oczywiście nie rozwiąże problemu skalowania (które w końcu nas kiedyś czeka) ale dzięki temu wykorzystamy w pełni potencjał zasobów, którymi aktualnie dysponujemy.

Zakładając, że aplikacja dostarcza typowych funkcjonalności, pierwsze problemy będą z serwerem bazy danych a ściślej: z jego pamięcią RAM. W przypadku braku pamięci operacyjnej system zaczyna korzystać z pliku wymiany, pojawia się wówczas długi czas wykonywania się zapytań a potem zbyt wysoka ilość jednoczesnych połączeń. Im większe tabele utrzymujemy oraz im więcej osób korzysta z naszej bazy, tym bardziej optymalizacja staje się opłacalna a jej efekty są bardziej spektakularne.

1.1 Optymalizacja struktury tabel MySQL

Pierwszą rzeczą jaką warto się zająć przy optymalizacji baz danych jest ilość tabel, ilość kolumn oraz deklarowany w polach rozmiar danych. Tutaj warto działać w zgodzie z zasadą im mniej – tym lepiej. Jest to banał ale z lenistwa większość osób robi tak, że do przechowywania cen, wzrostu, czy kluczy tabel, o których wiemy, że nie będą rosnąć w nieskończoność wykorzystujemy INT(11). Do małych liczb stosujmy TINYINT lub SMALLINT. Po to to jest, żeby z tego korzystać. Jeżeli mamy bazę jakiegoś sklepiku czy małą aplikację biznesową małej firmy, których liczba rekordów zwykle nie przekracza kilkudziesięciu tysięcy – fakt, różnica nie będzie aż tak zauważalna. Ale jeżeli takich pól jest wiele a rekordy zaczynamy liczyć w milionach – możemy sporo zaoszczędzić, zarówno miejsca jakiego baza potrzebuje na dysku (co jest mniej ważne z punktu widzenia optymalizacji) ale przede wszystkim bufora i CPU serwera.

Odpowiedni dobór typów danych do kolumn

Czy wiesz, że jeżeli znasz długość stringu jaki będziesz przechowywać w polu to lepiej z punktu widzenia optymalizacji używać CHAR niż VARCHAR? Zysk pod względem wydajności jest dwukrotny! Tak na marginesie, statyczna alokacja pamięci zawsze była, jest i zawsze będzie szybsza.

Czy wiesz, że TIMESTAMP zawiera dwa razy mniej miejsca w pamięci niż DATETIME? Czytaj więcej w artykule: typy danych w MySQL.

1.2 Kolejność kolumn w tabelach

Na wydajność bazy danych ogromny wpływ ma kolejność kolumn w poszczególnych tabelach. Kolumny powinniśmy dodawać według następującej kolejności:

1. Kolumna z kluczem głównym (Primary Key)
2. Kolumna z kluczem obcym (Foreign Key)
3. Kolumny często wyszukiwane (najczęściej padające po słowie WHERE)
4. Kolumny często uaktualniane (najczęściej padające po słowie SET)
5. Częściej używane kolumny typu NULL
6. Rzadziej używane kolumny typu NULL

2. Mierzymy czasy zapytań MySQL

Mimo, że wprawiony programista z doświadczenia „wie”, które zapytania kosztują bazę sporego wysiłku, a które raczej nie stanowią dla niej problemu, to warto od czasu do czasu przyjrzeć się z pokorą i prześledzić czas wykonania się zapytań. Osobiście, przy tworzeniu oprogramowania testuję zapytania w phpMyAdmin – wiem, mało wygodne rozwiązanie. Zazwyczaj używa się do tego profilera. Odsyłam do dokumentacji, gdzie można prześledzić najpopularniejsze przypadki użycia:

Link: https://dev.mysql.com/doc/refman/5.6/en/performance-schema-query-profiling.html 

3. Optymalizacja kodu aplikacji

MySQL wspiera wielowątkowość procesora i sam silnik bazy danych jest wyspecjalizowany do przetwarzania danych zawartych w bazach danych. Dlatego jeżeli np. chcesz wyciągnąć średnią lub zsumować rekordy zazwyczaj lepiej jest użyć wbudowanych funkcji w MySQL niż tworzyć sobie „własne pętelki” w PHP. Baza danych to nie tylko „głupi worek na dane”. Właśnie po to są tworzone funkcje jak AVG() i SUM() aby przyspieszyć aplikacje i przy okazji ułatwić pracę programistom.

Jeżeli musisz przetwarzać dane w logice aplikacji, najpierw je wyciągnij do zmiennej przechowującej tablicę wyników, następnie zamknij połączenie a potem to już rób z danymi co ci się podoba.

4. Optymalizacja zapytań MySQL

Najczęstszym problemem w zapytaniach jest nieszczęsne SELECT *. Im więcej danych wyciągamy, tym gorzej. Baza zwraca większe pakiety danych co jak wiadomo ujemnie wpływa na wydajność. Definiuj na sztywno, z których kolumn dane są ci potrzebne. Każda kolumna niepotrzebnie zwrócona w zapytaniu to marnotrawstwo.

Fajną sprawą jest też funkcja EXPLAIN. Dzięki niej można sprawdzić jak silnik bazy podchodzi do konkretnego zapytania, czy używa indeksów, jeżeli tak to jakich.

Czytaj więcej o funkcji EXPLAIN w oficjalnej dokumentacji: https://dev.mysql.com/doc/refman/8.0/en/explain.html

Nie stosuj podzapytań, nie rób JOINów kilkunastu tabel na raz a wszystko będzie w porządku.

5. Redundancja danych!

Redundancja danych czyli celowe zdenormalizowanie danych w celu przyspieszenia ich przetwarzania. Optymalizacja ta polega na specjalnym złamaniem reguły normalizacji baz danych i przechowywania tych samych informacji w dwóch miejscach po to by te dane szybciej wyciągnąć. Niby nie powinno się tego robić bo łamie sens relacyjnych baz danych ale wykonany w przemyślany sposób, (czyli z zabezpieczeniami przed utratą integralności) trik z danymi nadmiarowymi może przynieść niesamowite rezultaty – tym bardziej jeżeli łączone tabele zaczynają się rozrastać.

Rozwiązania tego typu uważam za kwintesencję informatyki, inżynierii baz danych oraz architektury. Tak samo jak w matematycznych zadaniach optymalizacji czasem używa się metod Monte Carlo. Oczywiście o tej metodzie na studiach magisterskich się tylko wspomina – a szkoda. Bo jak można uzyskać nieco mniej dokładny wynik dużo mniejszym nakładem pracy to właśnie tak (w większości przypadków) się powinno zrobić.

Prosty przykład z życia: wartość końcowa faktury może być przechowywana w polu faktury.wartosc_faktury mimo, że można ją otrzymać poprzez zsumowanie pozycje_faktury.wartosc_pozycji. Wykonując kwerendę odpytującą o np. roczny przychód firmy na podstawie wystawionych faktur wystarczy wtedy zsumować wartość z faktur a nie sumę sum z poszczególnych pozycji.

Dzięki temu zabiegowi, który polega na (mogłoby się wydawać) stworzeniu niepotrzebnego nadmiarowego pola, kwerendy z tego typu raportami mogą się wykonywać szybciej o tyle razy ile wynosi średnia liczba pozycji na fakturze! Jeżeli na fakturach jest zazwyczaj jedna pozycja cały trik jeszcze pogorszy sprawę ale jeżeli na fakturach jest 10 pozycji, raport roczny otrzymamy ok. 10 razy szybciej. To dlatego nie da się stworzyć uniwersalnego poradnika jak optymalizować bazy danych, bo wszystko zależy od konkretnego przypadku.

6. Optymalizacja serwera MySQL

Zmiana miejsca przechowywania plików tymczasowych jest jednym z najprostszych trików. Zamiast zapisywać pliki tymczasowe do RAM zapisz je na dysku. Pamiętaj, że dysk jest wolniejszym medium do przechowywania danych dlatego jeżeli serwer będzie intensywnie z nich korzystał zmiana wpłynie niekorzystnie na czas wykonywania się zapytań MySQL.

Druga sprawa: Im wyższa wersja MySQL tym lepiej.

Jak wiadomo, modyfikacja wielkości buffer_pool_size, key_buffer_size, query_cache_limit, query_cache_size może korzystnie wpłynąć na Cache-Hit-Ratio, czyli efektywność wewnętrznego systemu Cache. Nie kieruj się poradnikami znalezionymi w sieci. Po prostu wprowadzaj małe zmiany i sprawdź za pomocą zapytania:

SELECT FORMAT(((QCACHE_HITS / (Qcache_hits + Qcache_inserts + Qcache_not_cached))*100),2) AS query_cache_hit_rate_percentage
-- , QCACHE_FREE_BLOCKS, QCACHE_FREE_MEMORY, QCACHE_HITS, QCACHE_INSERTS, QCACHE_LOWMEM_PRUNES, QCACHE_NOT_CACHED, QCACHE_QUERIES_IN_CACHE, QCACHE_TOTAL_BLOCKS
FROM (
SELECT 
MAX(IF(VARIABLE_NAME = 'QCACHE_FREE_BLOCKS', VARIABLE_VALUE, NULL)) QCACHE_FREE_BLOCKS,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_FREE_MEMORY', VARIABLE_VALUE, NULL)) QCACHE_FREE_MEMORY,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_HITS', VARIABLE_VALUE, NULL)) QCACHE_HITS,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_INSERTS', VARIABLE_VALUE, NULL)) QCACHE_INSERTS,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_LOWMEM_PRUNES', VARIABLE_VALUE, NULL)) QCACHE_LOWMEM_PRUNES,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_NOT_CACHED', VARIABLE_VALUE, NULL)) QCACHE_NOT_CACHED,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_QUERIES_IN_CACHE', VARIABLE_VALUE, NULL)) QCACHE_QUERIES_IN_CACHE,
MAX(IF(`VARIABLE_NAME` = 'QCACHE_TOTAL_BLOCKS', VARIABLE_VALUE, NULL)) QCACHE_TOTAL_BLOCKS
FROM information_schema.global_status
WHERE VARIABLE_NAME LIKE "QCACHE%"
) AS stats;

… czy Cache-Hit Ratio wzrósł, bo to tylko o to w tym chodzi.

źródło: https://haydenjames.io/mysql-query-cache-size-performance/

7. Indeksowanie

Indeksy to szczególny przypadek redundantnych danych, są to pary klucz – lokalizacja, które mają za zadanie zwrócić wyniki bez przeszukiwania całych tabel (coś jak spis treści w książce). Najlepsza zasada indeksowania: indeksujemy możliwe jak najmniej pól, a jeżeli już musimy indeksować to te pola, które najczęściej padają po słowie WHERE.

Świetnym rozwiązaniem jest tworzenie osobnego indeksu dla dwóch tabel. Korzysta się z tego wtedy, kiedy często łączymy dwie tabele. Wszystkie zapytania, w których dochodzi do ich łączenia będą się wykonywać radykalnie szybciej.

Jeżeli w aplikacji wykorzystujesz wyszukiwarkę słów kluczowych (np. wyszukiwarka w CMSie lub sklepie internetowych) stwórz indeks typu fulltext i odpytuj nie za pomocą LIKE '%słowo kluczowe%’ ale MATCH AGAINST. Warto znać takie triki jeżeli zabieramy się za tworzenie własnych CMSów i systemów sklepowych.

Czytaj więcej o indeksowaniu w MySQL.

8. Antywzorce projektowe

Artykuł jest o optymalizacji baz danych ale oczywiście to od samej aplikacji zależy jak intensywnie „zawraca głowę” naszym tabelkom. Zachęcam do poczytania o antywzorcach projektowych w tym EAV, który czasem jest niezastąpiony ale na pewno jest nadużywany we wszelkiego rodzaju oprogramowaniu:

Link: https://mikesmithers.wordpress.com/2013/12/22/the-anti-pattern-eavil-database-design/

9. Wybór silnika składowania danych

Silnik InnoDB obsługuje transakcje, zapewnia lepszą współbieżność ale jest wolniejszy zarówno przy odczycie jak i zapisywaniu. Z kolei MyISAM źle znosi jednoczesne odczytywanie i zapisywanie. Mechanizm Memory jest nieporównywanie szybszy jeżeli będziemy z niego korzystać jak z pamięci podręcznej.

Czy wiesz, że dla każdej tabeli można niezależnie wybrać z jakiego systemu składowania danych ma ona korzystać? Czytaj więcej w krótkim porównaniu trybów składowania danych w MySQL.

10. Cache w warstwie aplikacji

Poza standardowym buforowaniem odpowiedzi MySQL możemy wykorzystać własne konstrukcje pamięci podręcznej. Jeżeli dysponujemy dyskiem SSD, sprawdzą się rozwiązania bazujące na systemie plików – szczególnie w przypadkach zwracania odpowiedzi zliczających lub raportujących dane.

Nieco lepszym rozwiązaniem będzie wykorzystanie open-sourceowych technologii Redis lub memcached, które przechowują obiekty w pamięci RAM – co pozwala przetwarzać dane o kilka rzędów szybciej. Rozwiązanie takie radykalnie odciąża bazę danych, jeżeli korzysta z niej wiele osób jednocześnie. Możemy dzięki temu odwlec w czasie zakup mocnego hostingu i sytuacji, w której przepłacamy za skalowanie chmurowe.

Z memchaced korzystają takie serwisy jak Wikipedia, YouTube czy Facebook. Czytaj więcej o tym jak zainstalować i wykorzystać memcached we własnej aplikacji PHP.

11. Wykluczenie niektórych rezultatów z buforowania

Ciężkie zapytania wykonywane raz na jakiś czas (które dodatkowo są buforowane w warstwie aplikacji) i zwracające sporej wielkości odpowiedzi powinny być wykluczane z Query Cache za pomocą komendy SQL_NO_CACHE

Chodzi o to by nie marnować miejsca w pamięci podręcznej, którą można wykorzystać na wiele innych mniejszych i częściej wykonywanych zapytań, które mają szansę użyć rezultatu przechowywanego w pamięci podręcznej. Przykłady zapytań które można wykluczyć z bufrowania: raportowanie, zapytania wykonywane w ramach CRONa, rankingi zmieniające się w czasie rzeczywistym itp.

Czy to naprawdę działa?

Podczas kiedy wszyscy doradzali migrację na dużo droższy hosting, wykonałem optymalizację bazy danych małego serwisu, która zajęła mi chyba z pół godziny. Efektu nie można było nie zauważyć. Po restarcie serwera nastąpiło zbicie utylizacji pamięci RAM z 90% do 70%:

Uważam to za niemały sukces, nie małe oszczędności dla klienta i też lepsze doświadczenia dla użytkowników. Serwis działa szybciej, ponieważ każde zapytanie jest wykonywane w krótszym czasie. Baza może się dalej spokojnie rozrastać na obecnym hostingu… Na kosztowny upgrade pamięci RAM jest trochę więcej czasu.

Najczęstsze mity odnośnie optymalizacji baz danych MySQL

1. Relacje zwiększają wydajność bazy danych
2. Relacje obniżają wydajność bazy danych

Relacje nie mają żadnego związku z wydajnością bazy danych. Relacje są do zabezpieczenia integralności bazy danych. To już kwestia twórcy bazy danych, czy relacje mają być chronione po stronie silnika bazy danych czy nie. Wielokrotnie doświadczyłem plusów i minusów zarówno jednego rozwiązania jak i drugiego.

Praca na bazach danych bez relacji daje większą elastyczność przy rozwoju aplikacji. Można tworzyć „dowolne relacje” przy każdym zapytaniu bezpośrednio w back-endzie aplikacji kiedy tylko będzie ku temu potrzeba. Z drugiej strony stosowanie relacji bardzo upraszcza programowanie w back-endzie i daje nam gwarancję integralności danych. Wszystko zależy od konkretnego przypadku.

Jeszcze raz powtórzę: relacje (ich istnienie lub ich brak) nie wpływa na wydajność bazy danych.

Podsumowanie

Jak widać optymalizacja baz danych MySQL to kwintesencja web developmentu. Jest to bardzo interdyscyplinarne wyzwanie, które wymaga znajomości niuansów bazodanowych, umiejętności programowania, wiedzy z zakresu algorytmów i złożoności, matematyki ale przede wszystkim logicznego myślenia. Pamiętaj zakup „lepszego hostingu” jak jeszcze serwis na siebie nie zarabia nie jest najlepszym rozwiązaniem.

Oczywiście, doświadczeni programiści są w stanie przewidzieć, które działania warto wykonać na starcie i część z tych rozwiązań można zastosować podczas projektowania aplikacji. Z drugiej strony, pewne optymalizacje mają sens dopiero jak aplikacja nieco urośnie. Wtedy można porównać czasy zapytań a zysk np. z denormalizacji osiągniemy wówczas kiedy nasze tabele rozrosną się do znacznych rozmiarów.

Źródła:

https://dev.mysql.com/doc/refman/8.0/en/

Schwartz B., Zaitsev P., Tkachenko V., High Performance MySQL. Optimization, Backups, Replication, and More, O’Reilly 2012