Czy bazy danych OLAP powinny być denormalizowane pod kątem wydajności odczytu?

Denormalizacja i agregacja to dwie główne strategie stosowane do osiągnięcia wydajności w hurtowni danych. To po prostu głupie sugerować, że nie poprawia wydajności czytania! Chyba muszę coś przegapić?

Agregacja: Rozważmy tabelę zawierającą 1 miliard zakupów. Kontrast z tabelą zawierającą jeden wiersz z sumą zakupów. Co jest szybsze? Select sum (amount) from the one-billion-row table or a select amount from the stół jednorzędowy? To oczywiście głupi przykład, ale dość wyraźnie ilustruje zasadę agregacji. Dlaczego jest szybciej? Ponieważ niezależnie od tego, jakiego magicznego modelu / sprzętu/oprogramowania / religii używamy, odczyt 100 bajtów jest szybszy niż odczyt 100 gigabajtów. To proste.

Denormalizacja: Typowy wymiar produktu w hurtowni danych detalicznych ma mnóstwo kolumn. Niektóre kolumny to łatwe rzeczy, takie jak "nazwa" lub "kolor", ale ma również pewne skomplikowane rzeczy, takie jak hierarchie. Wiele hierarchii (asortyment produktów (5 poziomów), zamierzony nabywca( 3 poziomy), surowce (8 poziomów), sposób produkcji (8 poziomów) wraz z kilkoma liczbami obliczeniowymi, takimi jak średni czas realizacji (Od początku roku), Waga/opakowanie środki etcetera etcetera. Utrzymałem tabelę wymiarów produktu z 200 + kolumnami, która została zbudowana z ~70 tabel z 5 różnych systemów źródłowych. Po prostu głupotą jest dyskutować, czy Zapytanie o znormalizowany model (poniżej)

select product_id
  from table1
  join table2 on(keys)
  join (select average(..)
          from one_billion_row_table 
         where lastyear = ...) on(keys)
  join ...table70
 where function_with_fuzzy_matching(table1.cola, table37.colb) > 0.7
   and exists(select ... from )
   and not exists(select ...)
   and table20.version_id = (select max(v_id from product_ver where ...)
   and average_price between 10 and 20
   and product_range = 'High-Profile'

... jest szybsze niż równoważne zapytanie na modelu denormalizowanym:

select product_id
  from product_denormalized
 where average_price between 10 and 20
   and product_range = 'High-Profile';

Oczywiście, użycie denormalizacji i agregacji utrudnia dostosowanie zmian schematu, co jest złe. Z drugiej strony zapewniają wydajność odczytu, co jest dobrą rzeczą.

Więc, czy powinieneś denormalizować swoją bazę danych w celu osiągnięcia wydajności odczytu? Nie ma mowy! Dodaje to tak wiele złożoności do systemu, że nie ma końca, na ile sposobów będzie Cię wkręcić, zanim dostarczysz. Czy warto? Tak, Czasami trzeba to zrobić, aby spotkać się z szczególne wymagania dotyczące wydajności.

Update 1

PerformanceDBA: 1 wiersz będzie aktualizowany miliard razy dziennie

Oznaczałoby to wymaganie (bliskie) czasu rzeczywistego (które z kolei generowałoby zupełnie inny zestaw wymagań technicznych). Wiele (jeśli nie Większość) hurtowni danych nie ma tego wymogu. Wybrałem nierealistyczny przykład agregacji, aby wyjaśnić, dlaczego agregacja działa. Nie chciałem też tłumaczyć strategii rollup :)

Należy również kontrastować z potrzebami typowego użytkownika hurtowni danych i typowego użytkownika podkładowego systemu OLTP. Użytkownik, który chce zrozumieć, jakie czynniki wpływają na koszty transportu, nie może się martwić, czy brakuje 50% dzisiejszych danych lub czy 10 ciężarówek eksplodowało i zabiło kierowców. Przeprowadzając analizę danych na przestrzeni 2 lat, doszedłby do tego samego wniosku, nawet gdyby miał do dyspozycji drugą aktualną informację.

Potrzeb kierowców tej ciężarówki (tych, którzy przeżyli). Nie mogą czekać 5 godzin w jakimś punkcie tranzytowym tylko dlatego, że jakiś głupi proces agregacji musi Posiadanie dwóch oddzielnych kopii danych rozwiązuje obie potrzeby.

Kolejną poważną przeszkodą w udostępnianiu tego samego zestawu danych dla Systemów Operacyjnych i systemów raportowania jest to, że cykle wydania, Pytania i odpowiedzi, wdrażanie, SLA i to, co masz, są bardzo różne. Ponownie, posiadanie dwóch oddzielnych kopii ułatwia to obsługę.

Przez "OLAP" rozumiem, że masz na myśli zorientowaną tematycznie relacyjną / SQL bazę danych używaną do wspomagania decyzji-AKA hurtownię danych.

Forma normalna (typowo 5. / 6. forma normalna) jest ogólnie najlepszym modelem dla hurtowni danych. Przyczyny normalizacji hurtowni danych są dokładnie takie same jak w każdej innej bazie danych: redukuje redundancję i unika potencjalnych anomalii aktualizacji; unika wbudowanych błędów i dlatego jest najprostszym sposobem obsługi zmian schematu i nowych wymagań. Korzystanie z normalnej formy w hurtowni danych pomaga również utrzymać proces ładowania danych prosty i spójny.

Nie ma "tradycyjnego" podejścia do denormalizacji. Dobre hurtownie danych zawsze były znormalizowane.

Czy baza danych nie powinna być denormalizowana dla wydajności odczytu?

Dobra, oto całkowita "Twój przebieg może się różnić", "To zależy"," używaj odpowiedniego narzędzia do każdego zadania"," Jeden rozmiar nie pasuje do wszystkich "odpowiedź, z odrobiną" nie naprawiaj, jeśli nie jest zepsuty": {]}

Denormalizacja jest jednym ze sposobów poprawy wydajności zapytań w pewnych sytuacjach. W innych sytuacjach może faktycznie zmniejszyć wydajność (ze względu na zwiększone zużycie dysku). To z pewnością sprawia, że aktualizacje trudniej.

Powinno być brane pod uwagę tylko wtedy, gdy napotkasz problem z wydajnością (ponieważ dajesz korzyści z normalizacji i wprowadzasz złożoność).

Wady denormalizacji to mniej problemów z danymi, które nigdy nie są aktualizowane lub aktualizowane tylko w zadaniach wsadowych, tzn. nie dane OLTP.

Jeśli denormalizacja rozwiązuje problem wydajności, który trzeba rozwiązać, a mniej inwazyjne techniki (takie jak indeksy lub pamięci podręczne lub zakup większego serwera) nie Rozwiąż, to tak, powinieneś to zrobić.

Najpierw moje opinie, potem analiza

Opinie
Denormalizacja jest postrzegana jako pomoc w odczytywaniu danych, ponieważ powszechne użycie słowa denormalizacja często obejmuje nie tylko łamanie normalnych form, ale także wprowadzanie wszelkich zależności wstawiania, aktualizacji i usuwania do systemu.

To, ściśle mówiąc, jest false , Patrz to pytanie/odpowiedź , Denormalizacja w ścisłym znaczeniu oznacza złamanie jakiejkolwiek normalnej formy z 1NF-6NF, inne zależności wstawiania, aktualizacji i usuwania są rozwiązywane przy użyciu zasady konstrukcji ortogonalnej .

Więc dzieje się tak, że ludzie biorą zasadę kompromisu czasoprzestrzeni {[18] } i pamiętają termin redundancja (związany z denormalizacją, wciąż nie równy) i dochodzą do wniosku, że powinieneś mieć korzyści. Jest to błędna implikacja, ale fałszywe implikacje nie pozwalają na zawarcie odwrotności.

Przełamywanie normalnych formMoże rzeczywiście przyspieszyć niektóre pobieranie danych (szczegóły w analizie poniżej), ale z reguły będzie to również w tym samym czasie:

• faworyzuje tylko określony typ zapytań i spowalnia wszystkie inne ścieżki dostępu
[33]} zwiększenie złożoności systemu (co wpływa nie tylko na utrzymanie samej bazy danych, ale także zwiększa złożoność aplikacji, które zużywają dane) {34]}
• zaciemniać i osłabiać semantyczną klarowność bazy danych
• głównym punktem systemów bazodanowych, jako Centralne dane reprezentujące przestrzeń problemu mają być bezstronne w rejestrowaniu faktów, tak aby w przypadku zmiany wymagań nie trzeba było przeprojektowywać części systemu (danych i aplikacji), które są niezależne w rzeczywistości. aby móc wykonać te sztuczne zależności należy zminimalizować-dzisiejszy "krytyczny" wymóg przyspieszenia jednego zapytania często staje się tylko marginalnie ważny.

Analiza

Więc stwierdziłem, żeczasami łamanie normalne formy mogą pomóc w odzyskaniu. Czas podać kilka argumentów

1) Breaking 1NF

Załóżmy, że masz dokumentację finansową w 6NF. Z takiej bazy danych na pewno można uzyskać raport o tym, co jest Saldem dla każdego konta za każdy miesiąc.

Zakładając, że zapytanie, które musiałoby obliczyć taki raport, musiałoby przejść przez N rekordy, możesz utworzyć tabelę

account_balances(month, report)

Które przechowywałyby salda strukturyzowane XML dla każdego rachunku. To łamie 1NF (Patrz uwagi później), ale pozwala na wykonanie jednego konkretnego zapytania z minimum I / o .

W tym samym czasie, zakładając, że istnieje możliwość aktualizacji dowolnego miesiąca za pomocą wstawiania, aktualizacji lub usuwania zapisów finansowych, wydajność zapytań o aktualizację w systemie może zostać spowolniona o czas proporcjonalny do pewnej funkcji n dla każdej aktualizacji. (powyższy przypadek ilustruje zasadę, w rzeczywistości miałbyś lepsze opcje i korzyści z uzyskania minimum We / Wy przynoszą takie kary, że w przypadku realistycznego systemu, który faktycznie aktualizuje dane, często uzyskujesz złą wydajność nawet dla docelowego zapytania w zależności od rodzaju rzeczywistego obciążenia; możesz wyjaśnić to bardziej szczegółowo, jeśli chcesz) {]}

Uwaga: Jest to właściwie banalny przykład i jest z tym jeden problem-definicja 1NF. Założenie, że powyższy model łamie 1NF jest zgodne z wymogiem, że wartości atrybutu " zawierają dokładnie jedną wartość z applicable domain ".

To pozwala ci powiedzieć, że domena raportu atrybutu jest zbiorem wszystkich możliwych raportów i że z nich wszystkich jest dokładnie jedna wartość i twierdzić, że 1NF nie jest złamany (podobnie jak argument, że przechowywanie słów nie łamie 1NF, mimo że możesz mieć letters relację gdzieś w twoim modelu).

Z drugiej strony istnieją znacznie lepsze sposoby modelowania tej tabeli, co byłoby bardziej przydatne dla szerszego zakresu zapytań (np. do pobierania salda dla jednego konta na wszystkie miesiące w roku). W takim przypadku uzasadniłbyś tę poprawę mówiąc, że to pole nie znajduje się w 1NF.

W każdym razie wyjaśnia to, dlaczego ludzie twierdzą, że złamanie NFs może poprawić wydajność.

2) Breaking 3NF

Zakładanie tabel w 3NF

CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(10) unsigned NOT NULL,
  `status` tinyint(3) unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `opening` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `m` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

CREATE TABLE `m` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Z przykładowymi danymi (1m wierszy w t, 100K w m)

Załóżmy wspólne zapytanie, które chcesz poprawić

mysql> select sql_no_cache m.name, count(*) 
       from t join m on t.member_id = m.id 
       where t.id between 100000 and 500000 group by m.name;
+-------+----------+
| name  | count(*) |
+-------+----------+
| omega |       11 |
| test  |        8 |
| test3 |   399982 |
+-------+----------+
3 rows in set (1.08 sec)

Możesz znaleźć propozycje przeniesienia atrybutu name do tabeli m, która łamie 3NF (ma FD: member_id -> name I member_id nie jest kluczem t)

Po

alter table t add column varchar(255);
update t inner join m on t.member_id = t.id set t.name = m.name;

Running

mysql> select sql_no_cache name, count(*) 
       from t where id 
       between 100000 and 500000 
       group by name;
+-------+----------+
| name  | count(*) |
+-------+----------+
| omega |       11 |
| test  |        8 |
| test3 |   399982 |
+-------+----------+
3 rows in set (0.41 sec)

Przypisy: Powyższy czas wykonania zapytania to przecięty na pół, Ale

• tabela nie była w 5NF/6NF na początek
• test został wykonany z no_sql_cache, więc większość mechanizmów pamięci podręcznej została uniknięta (i w rzeczywistych sytuacjach odgrywają one rolę w wydajności systemu)
• zużycie przestrzeni zwiększa się o ok. rozmiar nazwy kolumny x 100k wierszy
Aby zachować integralność danych, powinny istnieć wyzwalacze na t, co znacznie spowolniłoby wszystkie aktualizacje nazwy i dodałoby dodatkowe kontrole, przez które wstawki w t musiałyby przejść Prawdopodobnie lepsze wyniki można osiągnąć przez upuszczenie kluczy zastępczych i Przełączenie na klucze naturalne i / lub indeksowanie lub przeprojektowanie na wyższy NFs [34]}

Bottom line

Myślę, że najbardziej trafną odpowiedzią na twoje pytanie jest to, że znajdziesz przemysł i edukację używając terminu "denormalizacja" w

• ścisły sens, dla łamanie NFs
• luźno, za wprowadzanie dowolnych wstawek, aktualizacja i usuwanie zależności (oryginalny cytat Codd komentarze na temat normalizacji mówiąc: " (!) wstawianie, aktualizowanie i usuwanie zależności', zobacz kilka szczegółów tutaj )

Tak więc, zgodnie ze ścisłą definicją, agregacja (tabele podsumowujące) nie są uważane za denormalizację i mogą bardzo pomóc pod względem wydajności (podobnie jak każda pamięć podręczna, która nie jest postrzegana jako denormalizacja).

Luźne użycie obejmuje zarówno łamanie formularze normalne i zasada konstrukcji ortogonalnej , Jak wspomniano wcześniej.

Kolejną rzeczą, która może rzucić trochę światła jest to, że istnieje bardzo ważna różnica między modelem logicznym a modelem fizycznym.

Na przykład indeksy przechowują nadmiarowe dane, ale nikt nie uważa ich za denormalizację, nawet ludzie, którzy używają terminu luźno i istnieją dwa (połączone) powody tego

• nie są częścią model logiczny
Są one przejrzyste i gwarantują, że nie naruszają integralności Twojego modelu.]}

Jeśli nie uda Ci się poprawnie modelować swojego modelu logicznego, skończysz z niespójną bazą danych-niewłaściwe typy relacji między twoimi podmiotami ( niezdolność do reprezentowania przestrzeni problemowej), sprzeczne fakty (zdolność do utraty informacji) i powinieneś zastosować wszelkie możliwe metody, aby uzyskać poprawny Model logiczny, jest to podstawa dla wszystkich aplikacji, które zostaną zbudowane na bazie danych. to.

Normalizacja, ortogonalna i jasna semantyka Twoich predykatów, dobrze zdefiniowane atrybuty, prawidłowo zidentyfikowane zależności funkcjonalne-wszystko to odgrywa rolę w unikaniu pułapek.

Jeśli chodzi o fizyczną implementację, rzeczy stają się bardziej zrelaksowane w tym sensie, że zmaterializowana kolumna obliczeniowa, która jest zależna od klucza non, może łamać 3NF, ale jeśli istnieją mechanizmy gwarantujące spójność, jest dozwolona w modelu fizycznym w taki sam sposób, jak dozwolone są indeksy, ale musisz bardzo ostrożnie uzasadnić to, ponieważ zazwyczaj normalizacja przyniesie takie same lub lepsze ulepszenia we wszystkich aspektach i nie będzie miała negatywnego lub mniej negatywnego wpływu i utrzyma przejrzysty projekt (co zmniejsza koszty rozwoju i utrzymania aplikacji), co skutkuje oszczędnościami, które można łatwo wydać na modernizację sprzętu, aby poprawić szybkość jeszcze bardziej niż to, co osiąga się przy łamaniu NFs.

Dwie najpopularniejsze metody budowy hurtowni danych (DW) wydają się być Bill Inmon i Ralph Kimball.

Metodologia Inmona wykorzystuje znormalizowane podejście, podczas gdy Kimball używa modelowania wymiarowego-znormalizowanego schematu gwiazd.

Oba są dobrze udokumentowane do małych szczegółów i oba mają wiele udanych wdrożeń. Obie stanowią "szeroką, dobrze utwardzoną drogę" do celu DW.

Nie mogę komentować podejścia 6NF ani modelowania Anchor, ponieważ Nigdy nie widziałem i nie brałem udziału w projekcie DW wykorzystującym tę metodologię. Jeśli chodzi o implementacje, lubię podróżować po sprawdzonych ścieżkach - ale to tylko ja.

Podsumowując, czy DW powinien być znormalizowany czy zdegenerowany? Zależy od metodologii, którą wybierzesz-po prostu wybierz jedną i trzymaj się jej, przynajmniej do końca projektu.

EDIT-przykład

W miejscu, w którym obecnie pracuję, mieliśmy raport, który działa od zawsze na serwerze produkcyjnym. Nie zwykły raport, ale zbiór 30 raportów wysyłanych codziennie do wszystkich i jego mrówek.

Ostatnio wdrożyliśmy DW. Z dwoma serwerami raportów i mnóstwem raportów, miałem nadzieję, że możemy zapomnieć o dziedzictwie. Ale nie, dziedzictwo to dziedzictwo, zawsze je mieliśmy, więc go chcemy, potrzebujemy, nie możemy bez niego żyć itp.

Rzecz w tym, że bałagan skryptu Pythona i SQL zajęło osiem godzin (tak, e-i-G-H-T godzin), aby uruchomić każdy jeden dzień. Nie trzeba dodawać, że baza danych i aplikacja zostały zbudowane przez lata przez kilka partii programistów - więc nie do końca Twój 5NF.

Nadszedł czas, aby odtworzyć dziedzictwo z DW. Ok, żeby było krótko, to jest zrobione i trwa 3 minuty (T-h-R-E-E minut), aby to wyprodukować, 6 sekund na raport podrzędny. I byłem w pośpiechu, aby dostarczyć, więc nie było nawet optymalizacji wszystkich zapytań. Jest to czynnik 8 * 60 / 3 = 160 razy szybciej - nie wspominając o korzyściach z usuwania 8 godzin pracy z serwera produkcyjnego. Myślę, że mogę się ogolić na minutę, ale teraz nikogo to nie obchodzi.

Jako ciekawostkę, użyłem metody Kimballa (modelowanie wymiarowe) dla DW i wszystko użyte w tej historii jest open-source.

To jest to, co to wszystko (hurtownia danych) ma być, myślę. Czy w ogóle ma znaczenie, która metodologia (znormalizowana lub de-znormalizowana) została użyta?

EDIT 2

Jako punkt zainteresowania, Bill Inmon ma ładnie napisany artykuł na swojej stronie internetowej -- opowieść o dwóch architekturach.

Problem ze słowem "denormalized" polega na tym, że nie określa w jakim kierunku iść. To tak, jakby próbować dostać się do San Francisco z Chicago, odjeżdżając z Nowego Jorku.

Schemat gwiazdy lub schemat płatka śniegu z pewnością nie jest znormalizowany. I na pewno działa lepiej niż znormalizowany schemat w niektórych wzorcach użytkowania. Są jednak przypadki denormalizacji, w których projektant nie przestrzegał żadnej dyscypliny, a jedynie komponował tabele intuicyjnie. Czasami te wysiłki nie wychodzą.

W skrócie, nie tylko denormalizuj. Postępuj zgodnie z inną dyscypliną projektowania, jeśli jesteś przekonany o jej zaletach, a nawet jeśli nie zgadza się ona z znormalizowanym projektem. Ale nie używaj denormalizacji jako pretekstu do przypadkowego projektowania.

Krótka odpowiedź brzmi nie naprawiaj problemu z wydajnością, którego nie masz !

Jeśli chodzi o tabele oparte na czasie, ogólnie przyjęte pardigm ma mieć daty valid_from i valid_to w każdym wierszu. Jest to wciąż w zasadzie 3NF, ponieważ zmienia tylko semantykę z " to jest jedyna i jedyna wersja tego podmiotu "na" to jest jedyna i jedyna wersja tego podmiotu w tym czasie "

Uproszczenie:

Baza danych OLTP powinna zostać znormalizowana (o ile ma to sens).

Hurtownia danych OLAP powinna być denormalizowana na tabele faktów i wymiarów (w celu zminimalizowania złączeń).