Relacyjne bazy danych. Dlaczego mogą działać wolno? I co z tym zrobić?

06.05.2025 | Martyna Dolińska

Z artykułu dowiesz się

Jak zorganizowane są relacyjne bazy danych i jakie elementy je tworzą
Jakie techniki pomagają poprawić wydajność bazy danych
Jak analiza planów zapytań pomaga w optymalizacji operacji na dużych zbiorach danych
Dlaczego statystyki bazodanowe i przebudowa indeksów są kluczowe dla efektywnego działania bazy

Wstęp

W ostatnich latach ilość gromadzonych i wytwarzanych danych znacząco rośnie. W dzisiejszym świecie wszystkie systemy informatyczne mają za zadanie przetwarzanie dostarczonych danych i wykonywanie na nich obliczeń. Dane takie mogą być dostarczane z zewnętrznych systemów, wprowadzane przez użytkownika lub przechowywane w przygotowanych bazach danych. W zależności od rodzaju przechowywanych danych i celów biznesowych stawianych przed aplikacją mogą być używane różne typy baz danych. W ramach artykułu skupimy się na relacyjnych bazach danych.

Struktura relacyjnej bazy danych

Na rynku jest wiele silników do relacyjnych baz danych. Niezależnie od wybranego dostawcy, bazy te składają się z takich samych elementów. Głównym elementem bazy są tabele. Każda tabela ma ściśle określoną strukturę, która umożliwia przechowywanie zapisanych w niej danych w ustandaryzowany sposób. Strukturę tą można wyobrazić sobie w sposób graficzny jako tabelę, gdzie każda kolumna to tytuł, a dane są zapisywane w poszczególnych wierszach. Każdy z takich wierszy w terminologii bazodanowej nazywamy rekordem.

Ważnym elementem relacyjnej bazy danych są relacje występujące pomiędzy tabelami. Relacje to powiązanie ze sobą danych pomiędzy różnymi tabelami. Pozwala to na lepszą organizację logiczną przechowywanych informacji, zmniejszenie ilości miejsca zajmowanego przez zapisane dane oraz upraszcza proces zarządzania nimi. Relacje pozwalają również wyeliminować nadmiarowe gromadzenie danych.

W czasie działania systemu ilość danych często bardzo szybko przyrasta. Dane zajmują coraz więcej miejsca, a znalezienie pożądanych informacji zajmuje coraz więcej czasu. Przeszukiwanie tabel bywa czasochłonne i mało efektywne. Dlatego, aby temu przeciwdziałać, możemy zastosować kilka koncepcji, które pomagają w radzeniu sobie z takimi wyzwaniami.

Dla łatwego zobrazowania sytuacji wyobraźmy sobie, że obsługujemy system bankowy. Dla zwiększenia poziomu trudności, wszystkie informacji zapisujemy na kartce papieru w formie tabelek. Poniżej przedstawię kilka scenariuszy z możliwymi występującymi w realnym świecie problemami oraz sposobami, jak sobie radzić z ich rozwiązaniem.

Scenariusz pierwszy – normalizacja

W zeszycie prowadzimy tabelkę z informacjami o prowadzonych rachunkach klientów. Tabelka taka zawiera imię i nazwisko klienta, jego adres i numer prowadzonego rachunku.

Po kilku latach działania systemu i zapisaniu kilku zeszytów z informacjami przychodzi do nas Jan Kowalski i informuje, że przeprowadził się i zmienił adres. W obecnej sytuacji nie pozostaje nam nic innego jak przejrzenie wszystkich zeszytów, skreślenie starego adresu przy każdym rachunku i dopisanie nowego. Jest to długotrwały proces, który wymaga zmian w kilku miejscach. Aby uniknąć takich sytuacji, z pomocą przychodzi nam normalizacja bazy danych. W takim przypadku możemy założyć jeden zeszyt z informacjami o klientach, a każdy klient otrzymuje swój unikalny identyfikator. W zeszycie z danymi o rachunkach bankowych, zamiast szczegółowych danych klienta zapisujemy tylko jego identyfikator i dane rachunku. W przypadku zmiany danych adresowych musimy dokonać tylko jednej modyfikacji.

Scenariusz drugi – indeksy

Po wprowadzeniu powyższych modyfikacji przychodzi do nas Adam Wiśniewski. Adam wie, że ma u nas otwarte wiele rachunków bieżących i oszczędnościowych, ale nie do końca wie, jakie i ile ma na nich pieniędzy. Prosi o przygotowanie zestawienia z informacjami o wszystkich rachunkach i ich saldach. Ze względu, że prowadzimy najlepszy bank na rynku, ilość klientów liczymy w setkach tysięcy, a ilość rachunkach w milionach. No cóż, nie pozostaje nam nic innego jak żmudne przeglądanie wszystkich naszych zeszytów i sprawdzanie wiersz po wierszu, który rachunek należy do Pana Adama i zapisywanie tych informacji w raporcie. Brzmi jak zadanie na długie dni albo tygodnie? Na szczęście bazy danych oferują funkcjonalność indeksów.

Indeks jest rodzajem spisu treści, który umożliwia łatwe znalezienie rekordów spełniających zadane kryterium. W naszym przypadku najlepszym rozwiązaniem byłoby stworzenie indeksu na tabeli z danymi klientów. W pierwszym kroku pomógłby łatwo znaleźć identyfikator klienta po jego nazwisku i imieniu (dla uproszczenia załóżmy, że dane te nie powtarzają się), a następnie indeks na tabeli z danymi rachunków założony na identyfikatorze klienta. Dzięki takiemu podejściu wybierzemy najpierw klienta posiadającego konkretne rachunki, następnie przyjrzymy się szczegółom interesujących nas rachunków. Pozwoli to znacząco przyspieszyć proces przeszukiwania niezbędnych danych.

Scenariusz trzeci – partycjonowanie

Klient przychodzi do naszego banku z prośbą o sporządzenie wyciągu z rachunków za cały zeszły rok. Wiemy już, że przeszukiwanie takich danych znacząco przyspieszy odpowiednie indeksowanie danych. Istnieje jednak jeszcze jeden sposób przyspieszenia wyszukiwania – partycjonowanie. Jest to mechanizm, który wymusza dzielenie danych fizycznie na mniejsze części, np. transakcje z danego roku. Dzięki wykorzystaniu tego mechanizmu przeszukiwane są tylko dane w określonym czasie z pominięciem tych nieistotnych, bo historycznych.

Partycjonowanie możemy porównać do encyklopedii lub roczników statystycznych. Każda książka z serii odpowiada za fragment opisu rzeczywistości, w określonym porządku alfabetycznym lub chronologicznym. W każdej partycji znajdziemy dane gromadzone w zadanym okresie. Zatem jeśli wiemy, z jakiego okresu są to dane, to w łatwy sposób możemy je odszukać na odpowiedniej partycji.

Archiwizacja danych

Co w przypadku gdy normalizacja, indeksy i partycjonowanie, czyli to, co jest najbardziej znane nie wystarcza? Jakie inne mechanizmy istnieją w zarządzaniu relacyjnymi bazami danych, aby działały szybko, sprawnie i były łatwo zarządzalne? Indeksy stanowią lekarstwo na wiele problemów. Niestety nie zawsze możliwe jest stworzenie indeksu, który będzie optymalny. Nie każdy przypadek możemy obsłużyć w ten sposób. Jednym z takich przykładów jest przeszukiwanie historii przelewów po tytule przelewu.

Załóżmy, że pewnego dnia przychodzi do nas Pan Kazimierz i prosi o znalezienie wszystkich przelewów, które zawierały w tytule słowo „prezent”. Słowo takie może wystąpić na początku tytułu, na jego końcu, ale również w dowolnym miejscu wewnątrz tekstu. Działanie indeksu możemy porównać do spisu w książce telefonicznej. Jeżeli chcemy znaleźć wszystkie nazwiska zaczynające się od „Ska”, to jest to zadanie proste i pozwala na użycie indeksu. Jednakże znalezienie wszystkich nazwisk kończących się na „Ska” jest już niemożliwe z wykorzystaniem indeksu, gdyż musimy przejrzeć wszystkie nazwiska. Problem taki można rozwiązać przez archiwizację starych danych. W większości przypadków przeglądanie tylko informacji bieżących (na przykład z ostatniego roku). Starsze dane mogą być dostępne na specjalne żądanie z dodatkową informacją, że na wynik trzeba będzie poczekać dłużej.

Statystyki bazodanowe

Oprócz zmian, które wpływają na to, jak dane są przechowywane, ważne jest też to, jak dane są znajdowane. Każde oprogramowanie serwera bazodanowego jest aplikacja bardzo złożona i przygotowana do przetwarzania dużych wolumenów danych w jak najkrótszym czasie. Jednak oprogramowanie jest tylko oprogramowaniem i czasem trzeba mu pomóc lepiej zrozumieć, jakie dane są przechowywane. Aby serwer mógł znaleźć optymalny sposób dostępu do danych, ważne jest, aby wiedział, czego może spodziewać się w poszczególnych tabelach. Do tego celu, każdy serwer baz danych zawiera statystyki odnośnie tego, jakie wartości są przechowywane w kolumnach tabel. Na przykład na podstawie statystyk, baza danych wie, ilu użytkowników o nazwisku Kowalski może spodziewać się w tabeli z klientami i jak dobrać sposób dostępu do tych danych, aby zrobić to szybko i z użyciem jak najmniejszej ilości pamięci i czasu procesora.

Przebudowa istniejących indeksów

Nasze indeksy, które zostaną raz utworzone, po pewnym czasie mogą już nie mieć optymalnej struktury. Załóżmy, że za każdym razem, gdy klient zamyka rachunek, informacja o tym rachunku jest wykreślana z indeksu. Akcje takie mogą mieć miejsce po zakończeniu okresów promocyjnych na rachunkach oszczędnościowych. Po wielu takich operacjach ilość skreśleń będzie tak duża, że znalezienie właściwego rekordu będzie bardzo trudne. Z pomocą w takim przypadku przychodzi przebudowa indeksu. Jest to tak naprawdę stworzenie indeksu na nowo z uwzględnieniem tylko aktualnych danych.

Zwiększanie dostępnych zasobów bazodanowych

Serwer bazodanowy jak każdy inny ma ograniczoną ilość fizycznych zasobów takich jak pamięć obliczeniowa, dostęp do procesora czy szybkość przesyłania danych pomiędzy dyskiem a pamięcią podręczną. Problem taki można rozwiązać przez zwiększanie ilości dostępnych zasobów (skalowanie wertykalne) lub wykonanie kopii bazy danych na innym serwerze (replikacji bazy danych) i wykonywanie trudnych obliczeń wymagających większej ilości zasobów (np. przygotowywanie raportów) na innym fizycznym komputerze (skalowanie horyzontalne).

Kwestia optymalizacji baz danych i przyspieszania dostępu do przechowywanych informacji jest bardzo złożona. Wraz z rozwojem systemu informatycznego i przyrostem wolumenu danych, baza danych powinna być nieustannie monitorowana i wszelkie spowolnienia powinny być na bieżąco analizowane w celu wykrycia ich powodów i przeciwdziałania im.

Plany zapytań

We wcześniejszej części artykułu opisałam, co może mieć wpływ na wolne działanie zapytań SQL oraz przedstawiłam ogólne sposoby poradzenia sobie z każdym z tych wyzwań. Wszystkie poprzednie sytuacje powinny być brane pod uwagę w czasie tworzenia aplikacji i dodawania nowych funkcjonalności. Nasuwa się pytanie, jak znaleźć przyczynę problemu, jeśli aplikacja do tej pory radziła sobie szybko ze swoimi zadaniami, a teraz zaczęła sprawiać problemy?

Plany zapytań służą do zrozumienia tego, jak silnik bazy danych planuje oraz wykonuje dane zapytanie SQL. Plany zapytań wizualizują kolejność dostępu do poszczególnych tabel bazy danych, sposób pobierania z nich danych i łączenia wyników z tych tabel, tak aby uzyskać finalny rezultat. Analizując plan zapytania, możemy znaleźć miejsca, w których brakuje indeksów lub zdefiniowany indeks jest niewydajny.

Za budowę planów zapytań w bazach danych odpowiadają optymalizatory. Dla przykładu, w relacyjnych bazach danych Oracle istnieją dwa typy optymalizatorów. Każdy z nich ma swoje plusy i minusy:

Optymalizator regułowy - buduje plan zapytania na podstawie struktury kodu SQL i opisanych w nim połączeń pomiędzy tabelami. Jest szybki i wymaga mało zasobów procesora dla zbudowania planu zapytania, jednak zdarza mu się znaleźć nieoptymalny plan. Plan zapytania wygenerowany przez taki optymalizator w różnym czasie jest zwykle taki sam.
Optymalizator kosztowy – buduje plan podobnie jak optymalizator regułowy. Dodatkowo bierze pod uwagę również statystyki dystrybucji danych dla danej tabeli, tzw. histogramy. Dla każdego potencjalnego planu zapytań oblicza on koszt pobrania danych i wybiera taki plan, który koszt ma najniższy. W związku z tym potrzebuje dużo czasu i zasobów na zbudowanie planu, ale z reguły plan zapytania jest utworzony optymalnie. Plan zapytania dla dwóch różnych wykonań tego samego zapytania może być różny w zależności, od jakich tego danych poszukujemy.

Każdy utworzony plan zapytania zapisywany jest w pamięci podręcznej do ponownego wykorzystania w razie potrzeby. Pomaga to zaoszczędzić czas przy ponownym wykonaniu zapytania poprzez pominięcie etapu analizy kodu zapytania, jego kompilacji i budowy planu.

Analiza planów zapytań

Podczas analizy konkretnego planu zapytania powinniśmy zwrócić uwagę, czy występują w nim odczyty typu TABLE SCAN. Odczyty te informują nas, że cała tabela bazy danych jest przeglądana pod kątem zadanego warunku, aby zwrócić oczekiwane rekordy. Będzie to oczekiwane i wydajne rozwiązanie, jeśli z całej tabeli potrzebujemy prawie wszystkie rekordy. Jeśli natomiast spodziewamy się małej ilości wyników, lepszym rozwiązaniem będzie użycie indeksu do znalezienia interesujących nas danych.

Odczyt całej tabeli (bez indeksu)

Kolejnym krokiem jest sprawdzenie, czy w planie zapytania występują wypisy typu INDEX SCAN. Tego typu operacje polegają na przejrzeniu całego indeksu, aby znaleźć dane spełniające zadane kryteria.

Odczyt indeksu w tabeli

W powyższym przykładzie należy spróbować stworzyć indeks, który lepiej będzie pasował do danego zapytania. Powinien on pozwolić zastąpić INDEX SCAN przez operację INDEX SEEK, która jest najbardziej wydajnym sposobem dostępu do danych w tabelach ze względu na precyzyjne rozpoczęcie wyszukiwania. Pozwoli to na przygotowanie zapytania, które spowoduje, że baza danych przeczyta i pobierze tylko te rekordy, które są wymagane do zbudowania wyniku końcowego.

Odczyt indeksu do pobrania dokładnego rekordu

W planie zapytania możemy również zauważyć sytuacje, w których w pierwszej kolejności baza danych używa indeksu, a później i tak odwołuje się do całej tabeli. Wynika to z faktu, że nasz indeks jest zbyt mało dokładny, przez co konieczne jest przeszukanie iteracyjne po wyszukanych w pierwszym kroku danych.

Odczyt danych z indeksu i uzupełnienie danych z pełnej tabeli

Rozwiązaniem takiego problemu jest utworzenie indeksu pokrywającego (ang. „covering index”), który będzie zawierał wszystkie wymagane kolumny i zlikwiduje potrzebę odwołania się bezpośrednio do samej tabeli. Innymi słowy, spowoduje to rozszerzenie indeksu.

Wykorzystanie "covering index"

Plan zapytania zawiera też informacje o sposobach łączenia wyników z poszczególnych tabel. Sposób taki jest określany przez bazę danych w zależności od ilości wierszy zwróconych z łączonych tabel oraz typu danych. W większości przypadków sposób wybrany przez bazę danych jest optymalny i nie wymaga ingerencji człowieka. Manualny wpływ na rodzaj operacji łączenia można wywrzeć przez ograniczenie ilości wierszy pobieranych z tabel lub przez zdefiniowanie wskazówek (ang. HINTS) w kodzie zapytania. Co najważniejsze wskazówki takie mogą, ale nie muszą zostać wzięte pod uwagę przez silnik bazy danych podczas budowy planu zapytania.

Analiza planów zapytania pozwala lepiej zrozumieć jak baza danych radzie sobie z naszymi zapytaniami i co można zrobić lepiej, aby wyniki były zwracane w krótszym czasie. Pozwala też nabrać dobrych nawyków podczas projektowania bazy danych oraz pisania nowych zapytań, tak aby uniknąć problemów wydajnościowych w przyszłości.

Podsumowanie

Praca z bazami danych wymaga od nas rozsądnego planowania i przewidywania, jakie dane oraz w jakiej ilości będą przechowywane. Pozwoli to na odpowiednie jej zaprojektowanie, zbudowanie oraz utrzymanie. Dzięki wykorzystaniu rad zawartych w artykule możliwe będzie spowodowanie, że baza danych będzie działa szybko, wydajnie i zawsze będzie możliwość pobrania interesujących nas danych.

Spis treści

Z artykułu dowiesz się
Wstęp
Struktura relacyjnej bazy danych
Scenariusz pierwszy – normalizacja
Scenariusz drugi – indeksy
Scenariusz trzeci – partycjonowanie
Archiwizacja danych
Statystyki bazodanowe
Przebudowa istniejących indeksów
Zwiększanie dostępnych zasobów bazodanowych
Plany zapytań
Analiza planów zapytań
Podsumowanie