Efektywna optymalizacja baz danych i redukcja zapytań bazodanowych

W ostatnich miesiącach stosunkowo dużo zajmowaliśmy się efektywną optymalizacją baz danych i redukcją zapytań do bazy danych. Powodem było to, że wraz z wprowadzeniem wielojęzyczności na TutKit.com, obecnie w 23 językach, bazy danych przybrały zupełnie nowe wymiary, a wymagania dotyczące wydajności i skalowalności wzrosły. Przeprowadziliśmy więc sprint poświęcony optymalizacji baz danych i redukcji zapytań do bazy danych z naprawdę zdumiewającymi wynikami.

Przeszliśmy przy tym przez różne typy stron (strony kategorii, strony produktów, strony bloga, …) i systematycznie optymalizowaliśmy oraz redukowaliśmy zapytania do bazy danych. Aby podać tylko dwa przykłady:

• W przypadku stron kategorii udało nam się zredukować liczbę zapytań z 916 do 16 queries (zapytań), a Server Running Time z 177ms do 15ms.
• W przypadku stron produktów udało nam się zredukować 2064 zapytania do bazy danych do 157, a Server Running Time z 412ms do 68ms.

Dzięki temu udało nam się zredukować zapytania do bazy danych o 92-98% przy jednocześnie ogromnie poprawionej szybkości, która wymaga już tylko 8 do 17 % wcześniejszego czasu. Cóż za wynik!

Oto porównanie przed i po, przy włączonym Debugbar, naszego projektu Laravel dla tego samego typu strony. Była to strona produktu, która zawierała bardzo dużo treści:

Ponad 2000 zapytań to, trzeba przyznać, brutalnie dużo. Dlaczego mieliśmy tak wysokie wartości, omówię w innym wpisie na blogu, gdy będzie mowa o współpracy z innymi agencjami, przeciętnym know-how i technical debt. Nasz backend developer wziął tę stronę na warsztat i już po 1,5 dnia optymalizacji osiągnął następujący wynik:

Ponieważ chodziło o typ strony produktów, miało to bardzo duży wpływ na bardzo wiele stron w naszym serwisie. Właśnie te strony mają dzięki wysokiej wydajności zarówno przekonywać w obszarze user experience, jak i być atrakcyjne dla Google, aby rankowały wyżej. Jesteśmy z tego wyniku wyjątkowo zadowoleni.

Dobre powody, by optymalizować bazę danych i redukować zapytania

Optymalizacja baz danych jest istotnym elementem tworzenia oprogramowania i refaktoryzacji, oferującym wiele korzyści. Deweloperzy i firmy, które opiekują się dużymi projektami, takimi jak sklepy internetowe czy portale, powinni traktować tę praktykę poważnie z następujących powodów:

• Lepsza wydajność i szybkość: zoptymalizowane bazy danych umożliwiają szybsze zapytania i krótsze czasy ładowania. Jest to szczególnie ważne dla aplikacji o dużym wolumenie danych lub wysokiej aktywności użytkowników, ponieważ szybkie czasy reakcji znacząco poprawiają doświadczenie użytkownika.
• Skalowalność: dobrze zoptymalizowana baza danych lepiej radzi sobie z rosnącą ilością danych i rosnącą liczbą jednoczesnych użytkowników. Zapewnia to, że aplikacja pozostaje wydajna również przy rosnącym wolumenie danych.
• Efektywne wykorzystanie zasobów: dzięki redukcji zbędnych zapytań i poprawie struktury bazy danych potrzeba mniej zasobów obliczeniowych. Prowadzi to do niższych kosztów operacyjnych i efektywniejszego wykorzystania istniejącego sprzętu.
• Niezawodność i stabilność: optymalizacje pomagają zapobiegać wąskim gardłom i awariom baz danych. Jest to szczególnie ważne dla aplikacji krytycznych dla biznesu, w których przestoje mogą prowadzić do znacznych strat finansowych.
• Lepsze doświadczenie użytkownika: użytkownicy oczekują szybkich i płynnych interakcji z aplikacjami. Zoptymalizowane bazy danych sprawiają, że użytkownicy nie są frustrowani długim ładowaniem ani opóźnionymi odpowiedziami, co zwiększa zadowolenie i lojalność klientów.
• Przewaga konkurencyjna: na konkurencyjnym rynku wydajność aplikacji może być czynnikiem decydującym. Firmy, które inwestują w optymalizację baz danych, mogą wyróżnić się na tle konkurencji dzięki lepszej wydajności aplikacji, również dlatego, że Google po prostu kocha wydajne strony.
• Redukcja błędów i problemów: dzięki identyfikacji i usuwaniu nieefektywnych zapytań i struktur można wyeliminować potencjalne źródła błędów. Prowadzi to do stabilniejszej aplikacji i zmniejsza nakład pracy na utrzymanie.
• Oszczędność kosztów: optymalizacje mogą zmniejszyć zapotrzebowanie na dodatkowy sprzęt i zasoby, co w dłuższej perspektywie może prowadzić do znacznych oszczędności. Bardziej efektywne operacje na bazie danych oznaczają także mniejsze wydatki na usługi chmurowe i hosting.

Ogólnie rzecz biorąc, optymalizacja baz danych istotnie przyczynia się do poprawy wydajności, niezawodności i skalowalności aplikacji oraz projektów, co ostatecznie prowadzi do lepszego doświadczenia użytkownika, wyższych pozycji w wyszukiwarkach i niższych kosztów operacyjnych.

Jak dokładnie postępowaliśmy? Oto podsumowanie!

Narzędzia do debugowania i profilowania w językach programowania

Praca odbywa się przede wszystkim z użyciem narzędzi do debugowania i profilowania. Narzędzia do debugowania i profilowania są niezbędne dla deweloperów, aby identyfikować i usuwać problemy oraz optymalizować wydajność aplikacji. Dają one wgląd w różne aspekty wykonywania kodu i pomagają deweloperom upewnić się, że ich aplikacje działają wydajnie i poprawnie. Jednym z narzędzi, których używamy w naszym projekcie Laravel TutKit.com, jest Debugbar.

Najważniejsze funkcje narzędzi do debugowania i profilowania

• Monitorowanie zapytań: narzędzia takie jak Debugbar w Laravel logują wszystkie zapytania do bazy danych wykonane podczas żądania i pokazują szczegóły, powiązania (bindings) oraz czasy wykonania zapytań. Pomaga to identyfikować wolne lub nadmiarowe zapytania, które można zoptymalizować.
• Profilowanie wydajności: narzędzia te oferują rozbicie czasu wykonania żądania, w tym czasu operacji na bazie danych, renderowania widoków i innych procesów. Pomaga to wykrywać wąskie gardła wydajności.
• Logowanie błędów i wyjątków: rejestrują i logują błędy oraz wyjątki występujące podczas wykonywania kodu, dostarczając szczegółowe stack trace’y i komunikaty o błędach, aby pomóc deweloperom szybko identyfikować i usuwać problemy.
• Informacje o trasach: w aplikacjach webowych narzędzia te często pokazują informacje o bieżącej trasie, w tym parametry trasy i middleware, co jest przydatne przy rozwiązywaniu problemów z routingiem.
• Renderowanie widoków: pokazują, które widoki zostały wyrenderowane i jak długo to trwało, co pomaga w optymalizacji wykonywania widoków.

Ogólny przypadek użycia

W każdym języku programowania workflow korzystania z narzędzia do debugowania i profilowania obejmuje zazwyczaj następujące kroki

1. Instalacja i konfiguracja: skonfigurowanie narzędzia w środowisku deweloperskim.
2. Monitorowanie wykonania: uruchomienie aplikacji i użycie narzędzia do monitorowania różnych aspektów jej działania.
3. Analiza danych: przegląd zalogowanych danych w celu zidentyfikowania wolnych zapytań, wąskich gardeł wydajności i błędów.
4. Optymalizacja i debugowanie: wprowadzenie niezbędnych zmian w celu optymalizacji wydajności i usunięcia zidentyfikowanych problemów.
5. Testy funkcjonalne i bugfixing: po optymalizacji należy sprawdzić, czy wszystkie niezbędne treści i funkcje zostały zachowane

Przykład: Laravel Debugbar

W naszym projekcie Laravel używamy Debugbar do monitorowania i optymalizacji naszej aplikacji:

1. Wyświetlanie zapytań: Debugbar pokazuje wszystkie wykonane zapytania, ich czas wykonania i parametry.
2. Identyfikacja wolnych zapytań: szukamy zapytań o długich czasach wykonania i je optymalizujemy.
3. Kontrola liczby zapytań: upewniamy się, że nie występują zbędne powtarzające się zapytania, które mogłyby wskazywać na problem N+1.
4. Profilowanie wydajności: funkcja Timeline pomaga nam zobaczyć, na co zużywany jest czas wykonania, dzięki czemu możemy odpowiednio optymalizować.

Szczegółowa analiza zapytań i schematów baz danych

W kolejnym kroku analizowaliśmy podobne zapytania do bazy danych, identyfikowaliśmy wolne i czasochłonne zapytania, sprawdzaliśmy tabele bez indeksów oraz weryfikowaliśmy zbędne kolumny standardowe. Ta kompleksowa analiza pozwoliła nam znacząco zoptymalizować wydajność bazy danych.

Dogłębne debugowanie i optymalizacja wydajności baz danych

Podczas debugowania nasze narzędzie (takie jak Debugbar) daje wgląd w to, z których plików i wierszy pochodzi każde zapytanie. Przy ponad 40 plikach, w tym plikach vendor, identyfikacja przyczyny może być jednak wyzwaniem. Oto jak postępujemy:

1. Gruntowne debugowanie: wykorzystujemy nasze doświadczenie i szczegółowe debugowanie, aby szybko zlokalizować problem, nawet jeśli nie jest on od razu oczywisty. Analizujemy, dlaczego problem wystąpił, i sprawdzamy zarówno kod, jak i nieefektywności frameworka.
2. Analiza kodu i frameworka: czasami dobrze napisany kod może powodować problemy z wydajnością, jeśli framework nie jest wystarczająco zoptymalizowany. I odwrotnie, dobry framework może cierpieć z powodu źle napisanego kodu lub nieprzestrzegania standardów programowania.

Częste problemy w bazach danych

Typowe problemy w większości baz danych to:

• Powtarzające się żądania: mogą występować z powodu przeoczonych optymalizacji, nieefektywnych praktyk programistycznych lub niezamierzonych pętli w logice aplikacji.
• Wolne zapytania: często są wynikiem brakujących indeksów, nieefektywnych struktur zapytań lub złożonych joinów, które można uprościć.
• Projekt schematu: w fazie planowania projektu istotne elementy, takie jak indeksy, mogły zostać przeoczone, co później prowadzi do wąskich gardeł wydajności.
• Problem zapytań N+1: występuje, gdy aplikacja wykonuje dodatkowe zapytania o powiązane dane, czego można by uniknąć dzięki eager loading.
• Wycieki pamięci: nieefektywne wykorzystanie pamięci, takie jak niezwalnianie zasobów czy nadmierne użycie zmiennych globalnych, może z czasem pogarszać wydajność.
• Problemy współbieżności: niewłaściwa obsługa procesów równoległych może prowadzić do race conditions, deadlocków lub innych problemów z synchronizacją, które obniżają wydajność.
• Opóźnienia sieciowe: nadmierne lub źle zarządzane żądania sieciowe mogą spowalniać aplikację, zwłaszcza w systemach rozproszonych.

Jesteśmy przekonani, że dzięki naszym głębokim kompetencjom i wieloletniemu doświadczeniu potrafimy efektywnie rozwiązywać te problemy. Ta wiedza ekspercka pozwala nam szybko identyfikować i usuwać problemy, zapewniając w ten sposób optymalną wydajność naszych, a chętnie także Twoich projektów.

Redukcja zapytań do bazy danych w celu poprawy wydajności

Wielokrotne odpytywanie bazy danych o te same dane może prowadzić do niepotrzebnego obciążenia i wolniejszego działania. Każde wywołanie bazy danych zużywa zasoby i czas. Oto przykładowe rozwiązanie:

Przykład poprawy wydajności poprzez redukcję nadmiarowych zapytań

Poprawa wydajności w zaktualizowanym kodzie wynika z redukcji nadmiarowych zapytań do bazy danych. Dzięki zapisaniu wyniku zapytania relacji translationQuality w zmiennej i jego ponownemu wykorzystaniu kod unika wielu wywołań bazy danych o te same dane. Ta optymalizacja zmniejsza liczbę interakcji z bazą danych, co poprawia czas odpowiedzi i efektywność metody edit.

Czasami bezpośrednie odpytywanie bazy danych za pomocą zapytań surowych (raw queries) może prowadzić do mniejszej liczby bardziej efektywnych zapytań niż użycie metod ORM. To podejście umożliwia precyzyjniejszą kontrolę nad zapytaniem oraz optymalizacje takie jak selektywne joiny i filtrowanie. Tworząc zapytanie ręcznie, możemy wyeliminować zbędny narzut i zapewnić, że tylko potrzebne dane są pobierane w jednym, zoptymalizowanym zapytaniu, co przekłada się na lepszą wydajność i krótsze czasy wykonania.

Aktualizacja pod kątem wydajności znacząco poprawia efektywność, przekształcając wiele zapytań do bazy danych w jedno, zoptymalizowane zapytanie. Zamiast pobierać wszystkie pakiety i następnie filtrować je w pamięci, nowe podejście wykorzystuje pojedynczy, dobrze zaprojektowany join bazodanowy i zapytanie warunkowe, aby pobrać tylko wymagane rekordy. Minimalizuje to nie tylko ilość przesyłanych danych, ale zmniejsza także nakład na przetwarzanie, co prowadzi do szybszego wykonania i zoptymalizowanego pobierania danych.

Implementacja indeksu bazy danych w celu zwiększenia wydajności

Implementacja indeksów bazodanowych to potężna technika poprawy szybkości strony. Indeks znacząco skraca czas potrzebny na pobranie danych, umożliwiając bazie danych efektywniejsze lokalizowanie rekordów i dostęp do nich. Ta optymalizacja jest szczególnie ważna dla tabel o dużym wolumenie danych i częstych operacjach odczytu. Indeksując ważne kolumny, takie jak te używane w klauzulach WHERE lub operacjach JOIN, możemy drastycznie skrócić czas wykonywania zapytań, co prowadzi do szybszego ładowania stron i bardziej responsywnego doświadczenia użytkownika. Dobrze zaprojektowane indeksy zapewniają, że nasza aplikacja może efektywnie się skalować, zachowując przy tym optymalną wydajność, co ostatecznie poprawia ogólną szybkość strony.

Efektywna i szybka realizacja zadań

Przykładamy wagę do efektywności i szybkości w naszym workflow, co pozwala nam realizować zadania na czas. Dzięki zastosowaniu zoptymalizowanych zapytań, minimalizowaniu nadmiarowego przetwarzania danych i wykorzystaniu naszego bogatego doświadczenia usprawniamy nasz proces deweloperski. Ten nacisk na wydajność zapewnia, że dostarczamy wysokiej jakości rezultaty w krótkim czasie, dotrzymujemy terminów projektów i przewyższamy oczekiwania naszych klientów.

Nasza ekspertyza nie ogranicza się do jednego konkretnego języka programowania. Stosujemy te zasady w różnych technologiach i frameworkach, dostosowując nasze podejście do wymagań każdego projektu. Niezależnie od tego, czy chodzi o PHP, MySQL, Python, JavaScript czy inny język, nasze zaangażowanie w efektywność i szybkość pozostaje niezmienne, pozwalając nam dostarczać wyjątkową wydajność i szybką realizację zadań w każdym środowisku deweloperskim.

Obliczanie efektów redukcji zapytań do bazy danych w sprintach

Redukcja liczby zapytań do bazy danych podczas sprintu może znacząco poprawić ogólną wydajność i efektywność aplikacji. Aby obliczyć efekty, najpierw identyfikujemy wszystkie interakcje z bazą danych w kodzie, koncentrując się szczególnie na częstych zapytaniach oraz tych w ścieżkach krytycznych.

Najpierw mierzymy czas wykonania i zużycie zasobów istniejących zapytań przed optymalizacją. Używamy do tego narzędzi profilujących, aby rejestrować opóźnienia i obciążenie każdej interakcji z bazą danych. Agregując te metryki, możemy ustalić bazowy poziom wydajności.

Następnie implementujemy zoptymalizowane zapytania i refaktoryzujemy kod, aby zminimalizować nadmiarowe żądania. Może to obejmować konsolidację wielu zapytań w jedno, bardziej efektywne zapytanie, użycie joinów zamiast osobnych zapytań lub wykorzystanie strategii cache’owania w celu uniknięcia zbędnych dostępów do bazy danych.

Po optymalizacji ponownie mierzymy czas wykonania i zużycie zasobów zrefaktoryzowanych zapytań. Porównując te metryki po optymalizacji z naszym poziomem bazowym, możemy skwantyfikować poprawę. Kluczowe wskaźniki sukcesu to zmniejszona liczba zapytań, krótszy czas wykonania, mniejsze obciążenie bazy danych i lepsze czasy odpowiedzi.

Ponadto oceniamy szersze efekty dla wydajności aplikacji. Obejmuje to monitorowanie metryk doświadczenia użytkownika, takich jak czasy ładowania stron i responsywność systemu. Dokumentując te usprawnienia, dostarczamy jasne, oparte na danych uzasadnienie działań optymalizacyjnych.

Ogólnie rzecz biorąc, to systematyczne podejście do redukcji zapytań do bazy danych przyczynia się nie tylko do przyspieszenia poszczególnych sprintów, ale także do długoterminowej skalowalności i łatwości utrzymania aplikacji.

Czy Twoja baza danych potrzebuje optymalizacji?

Jeśli masz szczególne wymagania dotyczące skalowalności swojego projektu, bo planujesz na przykład wielojęzyczność i międzynarodowy roll-out albo wiesz, że wkrótce należy się spodziewać szczytów ruchu (np. dzięki reklamie telewizyjnej), przygotuj się na te nadchodzące obciążenia swoich baz danych. Zwłaszcza wtedy, gdy masz wrażenie, że pierwsze ładowanie Twojej strony i tak przebiega już bardzo wolno, Twoje bazy danych i zapytania do nich nie będą optymalne i nie wytrzymają nadchodzącego naporu wielu odwiedzających jednocześnie na stronie. Może dojść do awarii serwera.

Najprościej jest samodzielnie zmierzyć za pomocą narzędzia debugującego, ile zapytań jest potrzebnych do wywołania strony. Kolejną wskazówkę, że czasy reakcji serwera, a tym samym zapytania do bazy danych trwają zbyt długo, znajdziesz też w Search Console w sekcji Ustawienia => Statystyki indeksowania. Wartość powyżej 1100 ms średniego czasu reakcji to już niemal katastrofa.

Dobre wartości leżą moim zdaniem poniżej 400 ms. John Müller z Google zalecał w rozmowie wideo od 100 do 500 ms.

Najlepsza widziana przeze mnie dotąd średnia wartość czasu odpowiedzi serwera wynosiła 180 ms w statystykach indeksowania Search Console dla sklepu internetowego opartego na Shopify, który zawierał teksty, obrazy, produkty itd.

Agencja optymalizacji baz danych: koszty redukcji zapytań do bazy danych

Za tę bardzo wyspecjalizowaną usługę naliczamy stawkę godzinową 240 euro netto. Potrafimy przy tym pracować z najróżniejszymi systemami baz danych, takimi jak MariaDB, Mongo DB, PostgreSQL czy MySQL.

To, jak długo trwa optymalizacja baz danych na typ strony w Twoim projekcie, zależy zawsze także od serwera i hostingu, a także od zastosowanego frameworka. Czy musimy wszystko klonować, czy możemy pracować już w przygotowanym środowisku testowym? Mamy dostęp SSH czy nie? Jeśli wszystko jest już skonfigurowane, dobrą regułą są dwa pełne dni robocze na typ strony. Jeśli setup serwera trzeba dopiero przygotować, czas odpowiednio się wydłuża.

Po briefingu oraz wstępnej analizie możemy podać pierwsze oszacowanie rzeczywistego nakładu czasowego.

Jeżeli także dla Ciebie interesująca jest redukcja zapytań do bazy danych i związany z nią wzrost wydajności po stronie serwera, śmiało odezwij się do nas.