8.1 Dlaczego konieczna jest denormalizacja?

Najbardziej kosztowną obliczeniowo operacją między dużymi tabelami jest łączenie. W związku z tym, jeśli w jednym zapytaniu konieczne jest „przewietrzenie” kilku tabel składających się z wielu milionów wierszy, wówczas DBMS poświęci dużo czasu na takie przetwarzanie.

Użytkownik w tym czasie może oddalić się, aby napić się kawy. Interaktywność przetwarzania praktycznie zanika i zbliża się do przetwarzania wsadowego. Co gorsza, w trybie wsadowym użytkownik otrzymuje wszystkie dane, o które prosił dzień wcześniej rano i spokojnie z nimi pracuje, przygotowując nowe zapytania na wieczór.

Aby uniknąć sytuacji ciężkich łączeń, tabele są denormalizowane. Ale nie byle jak. Istnieją pewne reguły, które pozwalają uznać tabele zdenormalizowane transakcyjnie za „znormalizowane” zgodnie z regułami konstruowania tabel dla hurtowni danych.

Istnieją dwa główne schematy, które są uważane za „normalne” w przetwarzaniu analitycznym: „płatek śniegu” i „gwiazda”. Nazwy dobrze oddają istotę i wynikają bezpośrednio z obrazu powiązanych tabel.

W obu przypadkach centralnym elementem schematu są tzw. tabele faktów, zawierające zdarzenia, transakcje, dokumenty i inne interesujące dla analityka rzeczy. Ale jeśli w transakcyjnej bazie danych jeden dokument jest „rozsmarowany” na kilka tabel (co najmniej dwie: nagłówki i wiersze treści), to w tabeli faktów jeden dokument, a dokładniej każdy z jej wierszy lub zestaw zgrupowanych wierszy, odpowiada do jednego rekordu.

Można to zrobić przez denormalizację dwóch powyższych tabel.

8.2 Przykład denormalizacji

Teraz możesz ocenić, o ile łatwiej będzie DBMS wykonać zapytanie, na przykład następującego typu: w celu określenia wielkości sprzedaży mąki klientom Pirozhki LLC i Vatrushki CJSC w danym okresie.

W znormalizowanej transakcyjnej bazie danych:


SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

W analitycznej bazie danych:


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

Zamiast ciężkiego łączenia dwóch tabel dokumentów i ich składu z milionami wierszy, DBMS otrzymuje bezpośrednią pracę z tabelą faktów, a lekkie łączenie z małymi tabelami pomocniczymi, bez których można się również obejść, znając identyfikatory.

Java webinar 

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

Wróćmy do schematów „gwiazda” i „płatek śniegu”. Za kulisami pierwszego zdjęcia były tabele klientów, ich grup, sklepów, sprzedawców i właściwie towarów. W przypadku denormalizacji tabele te, zwane wymiarami, są również łączone z tabelą faktów. Jeśli tabela faktów odnosi się do tabel wymiarów, które mają powiązania z innymi wymiarami (wymiarami drugiego poziomu i wyższymi), to taki schemat nazywany jest „płatkiem śniegu”.

Jak widać, dla zapytań zawierających filtrowanie po grupach klientów konieczne jest wykonanie dodatkowego połączenia.


SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

W takim przypadku denormalizacja może być kontynuowana i upuścić wymiar drugiego poziomu do wymiaru pierwszego, ułatwiając zapytania do tabeli faktów.

Schemat, w którym tabela faktów odwołuje się tylko do wymiarów, które nie mają drugiego poziomu, nazywany jest schematem gwiazdy. Liczba tablic pomiarowych odpowiada liczbie „promieni” w gwieździe.

Schemat Star całkowicie eliminuje hierarchię wymiarów i konieczność łączenia odpowiednich tabel w jednym zapytaniu.


SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

Wadą denormalizacji jest zawsze redundancja , która powoduje wzrost rozmiaru bazy danych zarówno w aplikacjach transakcyjnych, jak i analitycznych. Obliczmy przybliżoną deltę na powyższym przykładzie zamiany „płatka śniegu” na „gwiazdę”.

W niektórych DBMS, takich jak Oracle, nie ma specjalnych typów całkowitych na poziomie definicji schematu bazy danych, należy użyć ogólnego typu boolowskiego numeric(N), gdzie N to liczba przechowywanych bitów. Wielkość pamięci takiej liczby jest obliczana według specjalnego wzoru podanego w dokumentacji dotyczącej fizycznego przechowywania danych i z reguły przewyższa ona typy niskopoziomowe, takie jak „16-bitowa liczba całkowita” o 1-3 bajty.

Załóżmy, że tabela sprzedaży nie stosuje kompresji danych i zawiera około 500 milionów wierszy, a liczba grup klientów wynosi około 1000. W takim przypadku jako identyfikator typu możemy użyć krótkiej liczby całkowitej (shortint, smallint) zajmującej 2 bajty id_customer_group.

Założymy, że nasz DBMS obsługuje dwubajtowy typ całkowity (na przykład PostgreSQL, SQL Server, Sybase i inne). Wtedy dodanie odpowiedniej kolumny id_customer_groupdo tabeli sprzedaży zwiększy jej rozmiar o co najmniej 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Kiedy potrzebna jest denormalizacja?

Przyjrzyjmy się kilku typowym sytuacjom, w których denormalizacja może być przydatna.

Duża liczba połączeń tabel

W zapytaniach do w pełni znormalizowanej bazy danych często trzeba łączyć do kilkunastu, a nawet więcej tabel. A każde połączenie jest operacją bardzo zasobochłonną. W rezultacie takie żądania zużywają zasoby serwera i są wykonywane powoli.

W takiej sytuacji może pomóc:

  • denormalizację poprzez zmniejszenie liczby tabel. Lepiej jest połączyć w jedną kilka tabel o niewielkich rozmiarach, zawierających rzadko zmieniane (jak często mówią warunkowo stałe lub referencyjne) informacje oraz informacje ściśle powiązane znaczeniowo.
  • Ogólnie rzecz biorąc, jeśli musisz połączyć więcej niż pięć lub sześć tabel w dużej liczbie zapytań, powinieneś rozważyć denormalizację bazy danych.
  • Denormalizacja poprzez dodanie dodatkowego pola do jednej z tabel. W takim przypadku pojawia się nadmiarowość danych, wymagane są dodatkowe działania w celu zachowania integralności bazy danych.

Szacunkowe wartości

Często zapytania są powolne i zużywają dużo zasobów, w których wykonywane są złożone obliczenia, zwłaszcza przy użyciu grupowania i funkcji agregujących (Sum, Max itp.). Czasami warto dodać 1-2 dodatkowe kolumny do tabeli zawierającej często używane (i trudne do obliczenia) dane obliczeniowe.

Załóżmy, że chcesz określić całkowity koszt każdego zamówienia. W tym celu należy najpierw ustalić koszt każdego produktu (zgodnie ze wzorem „liczba sztuk produktu” * „cena jednostkowa produktu” - rabat). Następnie musisz pogrupować koszty według zamówień.

Wykonanie tego zapytania jest dość skomplikowane i jeśli w bazie danych przechowywane są informacje o dużej liczbie zamówień, może zająć dużo czasu. Zamiast wykonywać takie zapytanie, możesz określić jego koszt na etapie składania zamówienia i zapisać go w osobnej kolumnie tabeli zamówień. W takim przypadku, aby uzyskać pożądany wynik, wystarczy wyodrębnić wstępnie obliczone wartości z tej kolumny.

Utworzenie kolumny zawierającej wstępnie obliczone wartości oszczędza dużo czasu podczas uruchamiania zapytania, ale wymaga terminowej aktualizacji danych w tej kolumnie.

długi brzeg

Jeśli mamy w bazie duże tabele, które zawierają długie pola (Blob, Long itp.), to możemy poważnie przyspieszyć wykonywanie zapytań do takiej tabeli, jeśli przeniesiemy długie pola do osobnej tabeli. Chcemy, powiedzmy, stworzyć katalog zdjęć w bazie danych, w tym przechowywać same zdjęcia w polach blob (profesjonalna jakość, wysoka rozdzielczość i odpowiedni rozmiar). Z punktu widzenia normalizacji absolutnie poprawna byłaby następująca struktura tabeli:

  • Numer identyfikacyjny fotografii
  • Identyfikator autora
  • Identyfikator modelu aparatu
  • samo zdjęcie (pole blob)

A teraz wyobraźmy sobie, jak długo będzie działać zapytanie, licząc liczbę zdjęć zrobionych przez dowolnego autora…

Właściwym rozwiązaniem (aczkolwiek łamiącym zasady normalizacji) w takiej sytuacji byłoby utworzenie kolejnej tabeli składającej się tylko z dwóch pól - identyfikatora ze zdjęciem oraz pola typu blob z samym zdjęciem. Wtedy selekcje z głównej tabeli (w której nie ma już ogromnego pola blob) pójdą od razu, ale gdy już będziemy chcieli zobaczyć samo zdjęcie, to cóż, poczekajmy…

Jak określić, kiedy denormalizacja jest uzasadniona?

8.4 Plusy i minusy denormalizacji

Jednym ze sposobów ustalenia, czy określone kroki są zasadne, jest przeprowadzenie analizy pod kątem kosztów i możliwych korzyści. Ile będzie kosztować zdenormalizowany model danych?

Określ wymagania (co chcemy osiągnąć) → określ wymagania dotyczące danych (co musimy spełnić) → znajdź minimalny krok spełniający te wymagania → oblicz koszty wdrożenia → wdroż.

Koszty obejmują aspekty fizyczne, takie jak miejsce na dysku, zasoby wymagane do zarządzania tą strukturą oraz utracone możliwości z powodu opóźnień czasowych związanych z utrzymaniem tego procesu. Za denormalizację trzeba zapłacić. Zdenormalizowana baza danych zwiększa nadmiarowość danych, co może poprawić wydajność, ale wymaga więcej wysiłku w celu kontrolowania powiązanych danych. Proces tworzenia aplikacji stanie się trudniejszy, ponieważ dane będą się powtarzać i trudniej będzie je śledzić. Ponadto implementacja integralności referencyjnej nie jest łatwa – powiązane dane są podzielone na różne tabele.

Korzyści obejmują szybszą wydajność zapytań i możliwość uzyskania szybszej odpowiedzi. Możesz także czerpać inne korzyści, w tym zwiększoną przepustowość, zadowolenie klientów i produktywność, a także bardziej efektywne korzystanie z zewnętrznych narzędzi programistycznych.

Częstotliwość żądań i spójność wydajności

Na przykład 72% z 1000 zapytań generowanych codziennie przez przedsiębiorstwo to zapytania na poziomie podsumowania, a nie zapytania szczegółowe. W przypadku korzystania z tabeli podsumowań zapytania są uruchamiane w ciągu około 6 sekund zamiast 4 minut, co skraca czas przetwarzania o 3000 minut. Nawet po uwzględnieniu 100 minut, które trzeba poświęcić na obsługę tabel przestawnych każdego tygodnia, oszczędza to 2500 minut tygodniowo, co uzasadnia utworzenie tabeli przestawnej. Z biegiem czasu może się zdarzyć, że większość zapytań nie będzie kierowana do danych zbiorczych, ale do danych szczegółowych. Im mniej zapytań korzysta z tabeli podsumowań, tym łatwiej jest ją usunąć bez wpływu na inne procesy.

I…

Wymienione powyżej kryteria nie są jedynymi, które należy wziąć pod uwagę przy podejmowaniu decyzji o wykonaniu kolejnego kroku w optymalizacji. Należy wziąć pod uwagę inne czynniki, w tym priorytety biznesowe i potrzeby użytkowników końcowych. Użytkownicy muszą zrozumieć, w jaki sposób z technicznego punktu widzenia na architekturę systemu wpływają wymagania użytkowników, którzy chcą, aby wszystkie żądania były realizowane w ciągu kilku sekund. Najłatwiejszym sposobem osiągnięcia tego zrozumienia jest przedstawienie kosztów związanych z tworzeniem takich tabel i zarządzaniem nimi.

8.5 Jak kompetentnie wdrożyć denormalizację.

Zapisz szczegółowe tabele

Aby nie ograniczać ważnych dla biznesu możliwości bazy danych, konieczne jest przyjęcie strategii współistnienia, a nie zastępowania, czyli prowadzenia szczegółowych tabel do głębokiej analizy, dodawania do nich zdenormalizowanych struktur. Na przykład licznik trafień. W biznesie musisz znać liczbę odwiedzin strony internetowej. Ale do analizy (według okresu, kraju...) najprawdopodobniej potrzebne będą szczegółowe dane - tabela z informacjami o każdej wizycie.

Korzystanie z wyzwalaczy

Możliwe jest zdenormalizowanie struktury bazy danych i nadal czerpanie korzyści z normalizacji za pomocą wyzwalaczy bazy danych w celu zachowania integralności integrityzduplikowanych danych.

Na przykład podczas dodawania pola wyliczanego każda z kolumn, od których zależy pole wyliczane, jest zawieszana za pomocą wyzwalacza, który wywołuje pojedynczą procedurę składowaną (to ważne!), która zapisuje niezbędne dane do pola wyliczanego. Konieczne jest jedynie, aby nie pominąć żadnej z kolumn, od których zależy pole obliczeniowe.

Wsparcie oprogramowania

Jeśli nie korzystasz z wbudowanych wyzwalaczy i procedur składowanych, to twórcy aplikacji powinni zadbać o zapewnienie spójności danych w zdenormalizowanej bazie danych.

Analogicznie do wyzwalaczy powinna istnieć jedna funkcja aktualizująca wszystkie pola zależne od zmienianego pola.

wnioski

Podczas denormalizacji ważne jest zachowanie równowagi między zwiększeniem szybkości bazy danych a zwiększeniem ryzyka niespójności danych, między ułatwieniem życia programistom piszącym , Select-sa skomplikowaniem zadania tych, którzy zapewniają populację bazy danych i aktualizację danych. Dlatego konieczna jest bardzo ostrożna denormalizacja bazy danych, bardzo selektywna, tylko tam, gdzie jest to niezbędne.

Jeśli nie jest możliwe wcześniejsze obliczenie zalet i wad denormalizacji, to początkowo konieczne jest wdrożenie modelu ze znormalizowanymi tabelami, a dopiero potem, aby zoptymalizować problematyczne zapytania, przeprowadzić denormalizację.

Ważne jest, aby denormalizację wprowadzać stopniowo i tylko w tych przypadkach, w których występują powtarzające się pobieranie powiązanych danych z różnych tabel. Pamiętaj, że podczas powielania danych liczba rekordów wzrośnie, ale liczba odczytów spadnie. Wygodne jest również przechowywanie obliczonych danych w kolumnach, aby uniknąć niepotrzebnych selekcji agregatów.