Web Design Blog / Programowanie:

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ć…

…wtedy widać kto programowania uczył się z kursów online i zaleca kupno pięciokrotnie droższego hostingu VPS. A kto naprawdę potrafi programować i zabiera się porządną optymalizację kodu i bazy danych.

Zakładając, że aplikacja nie jest stworzona na fundamencie jakiegoś nic nie wartego Open-Source’a stworzonego na IFach, pierwsze problemy będą z serwerem bazy danych a ściślej: z jego pamięcią RAM. Może też to być długi czas wykonywania zapytań i zbyt wysoka ilość jednoczesnych połączeń.

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.

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?

Odsyłam do ściągawki: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

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 apliakcji

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.

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.

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 mnie 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 tutaj trzeba niestety myśleć.

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.

7. Indeksowanie

Co za oczywistość! 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.

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.

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/

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%:

Optymalizacja MySQL - pamięć RAM

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 mi większą elastyczność przy rozwoju aplikacji. Mogę tworzyć „dowolne relacje” przy każdym zapytaniu bezpośrednio w back-endzie aplikacji kiedy tylko będzie ku temu potrzeba. Pozorną wadą tego rozwiązania jest konieczność „sprawdzania” czy dane na pewno się dodały, usunęły itd… no i to że trzeba myśleć o integralności na własną rękę.

Z drugiej strony stosowanie relacji bardzo upraszcza programowanie w back-endzie i daje nam gwarancję integralności danych. Wszystko zależy od konkretnego przypadku. Oczywiście we wszystkich kursach i na uczelni pojęcie „bazy danych” i „relacyjne bazy danych” są stosowanie zamiennie. O braku relacji w bazach danych nie ma nawet co wspominać w gronie programistów, którzy nigdy nie stworzyli czegoś swojego od podstaw a  tworzenie aplikacji zaczynają od rysowania mądrze wyglądających grafów 😉

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.

Źródła:

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

Optymalizacja baz danych MySQL
4.8 (95%) głosów: 12

Autor: (29 lat)

Służę pomocą w razie wykonania / odnowienia strony lub sklepu internetowego dla twojej firmy niezależnie od twojej lokalizacji czy skali przedsięwziecia.

Tego typu wpisy piszę w wolnych chwilach lub pomiędzy projektami. Tylko pomyśl co mogę dla Ciebie zrobić w ramach usługi :-)

Zadaj pytanie lub skomentuj

Wyrażam zgodę na przetwarzanie moich danych osobowych przez firmę Paweł Mansfeld z siedzibą w Jastrzębiu-Zdroju, ul. Plebiscytowa 10, w celu udzielenia odpowiedzi, w tym przedłożenia oferty jeśli o nią pytam. Moje dane osobowe będą przetwarzane do czasu cofnięcia zgody lub przez okres niezbędny do ustalenia, dochodzenia lub obrony roszczeń. Mam prawo dostępu do danych, sprostowania, usunięcia lub ograniczenia przetwarzania, prawo sprzeciwu, prawo wniesienia skargi do organu nadzorczego i prawo do przeniesienia danych.

Komentarze publiczne

Brak komentarzy.
Otrzymuj powiadomienie o nowych artykułach

Wyrażam zgodę na przetwarzanie moich danych osobowych przez firmę Paweł Mansfeld z siedzibą w Jastrzębiu-Zdroju, ul. Plebiscytowa 10, w celu udzielenia odpowiedzi, w tym przedłożenia oferty jeśli o nią pytam. Moje dane osobowe będą przetwarzane do czasu cofnięcia zgody lub przez okres niezbędny do ustalenia, dochodzenia lub obrony roszczeń. Mam prawo dostępu do danych, sprostowania, usunięcia lub ograniczenia przetwarzania, prawo sprzeciwu, prawo wniesienia skargi do organu nadzorczego i prawo do przeniesienia danych.

*Bez obaw, nie udostępniam nikomu twojego adresu e-mail