Przykłady denormalizacji danych i jej konsekwencje

Normalizacja rozwiązuje jedne problemy, ale w niektórych przypadkach tworzy inne, zwłaszcza jeśli chodzi o wydajność. Dzisiaj otworzymy dla Ciebie wrota do ciemnej (a czasem jasnej) sztuki — denormalizacji. Tak, możesz łamać zasady normalizacji... ale z głową!

Denormalizacja — to proces odwrotny do normalizacji. Jeśli normalizacja rozbija tabele na osobne logiczne encje, minimalizując nadmiarowość, to denormalizacja łączy dane z powrotem, żeby poprawić wydajność. Denormalizacja jest często używana, gdy przy dużym obciążeniu i częstym wykonywaniu złożonych zapytań joinowanie wielu tabel zaczyna spowalniać system.

Można powiedzieć, że denormalizacja to kompromis między czystością danych a szybkością wykonywania zapytań.

Kiedy używać denormalizacji?

Jak z każdym narzędziem, ważne jest, żeby wiedzieć, kiedy denormalizacja ma sens. Stosuje się ją w takich sytuacjach:

1. Często używane zapytania robią się wolne. Kiedy system pod dużym obciążeniem często wykonuje te same zapytania (np. raporty zbiorcze i agregaty), joiny wielu tabel mogą zajmować sporo czasu. Denormalizacja pozwala zmniejszyć liczbę takich joinów.

2. Zadania analityczne i statystyka. W systemach analitycznych (np. BI — Business Intelligence) często trzeba robić masową analizę danych. W takich przypadkach denormalizacja przyspiesza przetwarzanie dzięki "wstępnie przygotowanym" danym.

3. Skomplikowane zapytania. Jeśli żeby wykonać zapytanie, musisz joinować pięć, dziesięć albo nawet więcej tabel, to może mocno spowolnić bazę danych. Denormalizacja pozwala uprościć strukturę zapytań.

4. Liczba joinów przekracza zdrowy rozsądek. Jeśli masz zapytania z 25 tabelami w JOIN, to chyba czas przemyśleć podejście.

Przykłady denormalizacji

Przykład 1: Sklep internetowy. W znormalizowanej bazie danych sklepu internetowego możemy mieć takie tabele:

1. customers — dane o klientach.
2. orders — info o zamówieniach.
3. products — dane o produktach.
4. order_items — produkty wchodzące w skład zamówienia.

Zapytanie do pobrania informacji może wyglądać mniej więcej tak:

SELECT
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id
WHERE 
    c.customer_id = 42;

Ale co jeśli nasz sklep internetowy obsługuje setki tysięcy zamówień dziennie? To zapytanie stanie się za wolne przez dużą liczbę joinów.

Rozwiązanie: denormalizacja.

Zróbmy tabelę dla często używanych danych:

CREATE TABLE order_summary AS
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    p.product_id,
    p.product_name,
    oi.quantity
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id;

Teraz, kiedy chcemy pobrać dane, po prostu robimy zapytanie do order_summary:

SELECT * FROM order_summary WHERE customer_id = 42;

Przykład 2: System analityczny. Wyobraź sobie, że pracujesz z bazą danych dla firmy sprzedającej bilety na eventy. Są tabele:

1. events — info o wydarzeniach.
2. sales — dane o sprzedaży biletów.

Jeśli analitycy chcą zrobić raport o średnim przychodzie z jednego biletu dla wszystkich wydarzeń, znormalizowana struktura zmusza Cię do wykonywania zapytania agregującego za każdym razem:

SELECT
    e.event_name,
    AVG(s.price) AS avg_ticket_price
FROM 
    events e
JOIN 
    sales s ON e.event_id = s.event_id
GROUP BY 
    e.event_name;

To zapytanie może być dość wolne, szczególnie jeśli każda sprzedaż to miliony rekordów.

Rozwiązanie: denormalizacja. Tworzymy osobną tabelę z danymi zagregowanymi:

CREATE TABLE event_summary AS
SELECT 
    e.event_id,
    e.event_name,
    COUNT(s.sale_id) AS ticket_count,
    SUM(s.price) AS total_revenue,
    AVG(s.price) AS avg_ticket_price
FROM 
    events e
JOIN 
    sales s ON e.event_id = s.event_id
GROUP BY 
    e.event_id, e.event_name;

Teraz raporty będą działać szybciej na poziomie zagregowanym:

SELECT
    event_name, 
    avg_ticket_price 
FROM 
    event_summary;

Konsekwencje denormalizacji

Denormalizacja oczywiście może przyspieszyć zapytania, ale to nie jest magiczna różdżka, która rozwiąże wszystkie problemy. Oto z czym możesz się spotkać, jeśli zdecydujesz się na ten krok.

Po pierwsze — duplikacja danych. Kiedy te same informacje są przechowywane w kilku miejscach, rozmiar bazy szybko rośnie i trudniej się z tym pracuje.

Po drugie — aktualizowanie danych jest teraz trudniejsze. Wyobraź sobie, że masz dane o kliencie w tabeli customers, a jeszcze kopię tych danych w tabeli order_summary. Jeśli klient zmieni imię albo adres, trzeba pamiętać, żeby zaktualizować od razu w dwóch miejscach. Pomyłka — i masz błąd, bo dane już się nie zgadzają.

Po trzecie — przez taką nadmiarowość łatwo się pogubić i popełnić błędy. To jak z różnymi wersjami tego samego dokumentu — czasem ciężko ogarnąć, która wersja jest właściwa.

I w końcu, utrzymanie i rozwijanie takiej bazy jest trudniejsze. Trzeba pisać specjalne triggery albo skrypty, żeby wszystkie kopie danych były zsynchronizowane. To dodatkowa robota dla devów.

Ogólnie, denormalizacja to narzędzie, którego warto używać z głową, znając wszystkie plusy i minusy.

2

Zadanie

SQL SELF, poziom 25, lekcja 4

Niedostępne

Tworzenie zdenormalizowanej tabeli na podstawie istniejących danych

Tworzenie zdenormalizowanej tabeli na podstawie istniejących danych

Otwórz

1

Ankieta/quiz

Normalizacja danych, poziom 25, lekcja 4

Niedostępny

Normalizacja danych

Normalizacja danych

Otwórz