Systemy zarządzania bazami danych
14. Strojenie schematu
Schemat bazy danych
• Schemat relacji składa się z nazwy relacji i zbioru atrybutów
R(a int, b varchar[20]);
• Egzemplarz relacji o schemacie R to
skończony zbiór rekordów z atrybutami
schematu R
Pewne schematy są lepsze od innych
• Schemat 1:
OnOrder1(supplier_id, part_id, quantity, supplier_address)
• Schemat 2:
OnOrder2(supplier_id, part_id, quantity);
Supplier(supplier_id, supplier_address);
• Przestrzeń
– Schemat 2 zajmuje mniej miejsca
• Zachowywanie informacji
– Schemat 1 może gubić adresy dostawców (anomalie
aktualizacyjne)
• Wydajność
– Jeśli często odczytuje się adres dostawcy na podstawie numeru zamówionej części, to schemat 1 jest dobry
– Jeśli jest wiele dodawanych wiele zamówień, schemat 1 jest słaby.
Zależności funkcyjne
• X to podzbiór atrybutów relacji R, a A pojedynczy atrybut R.
– X determinuje A (w R zachodzi zależność funkcyjna X A) wtw:
• Dla każdego egzemplarza I relacji R, jeśli w dwóch rekordach r i r’ egzemplarza I są te same wartości atrybutów ze zbioru X, to rekordy r i r’ mają też tę samą wartość atrybutu A.
• OnOrder1(supplier_id, part_id, quantity, supplier_address)
– supplier_id supplier_address to istotna zależność funkcyjna
Klucz relacji
• Atrybuty ze zbioru X zawartego w R stanowią klucz R, wtw. X determinuje
każdy atrybut R i żaden podzbiór właściwy X nie determinuje wszystkich atrybutów R
• OnOrder1(supplier_id, part_id, quantity, supplier_address)
– { supplier_id, part_id } jest kluczem
• Supplier(supplier_id, supplier_address);
Normalizacja
• Relacja jest znormalizowana, wtw. w
każdej istotnej zależności funkcyjnej X A na atrybutach R X jest kluczem R.
– OnOrder1 nie jest znormalizowana
OnOrder1(supplier_id, part_id, quantity, supplier_address)
– OnOrder2 i Supplier są znormalizowane
OnOrder2(supplier_id, part_id, quantity) Supplier(supplier_id, supplier_address)
Przykład #1
• Bank przypisuje każdemu klientowi
oddział. Każdy oddział podlega jakiemuś sądowi.
– Czy poniższa relacja jest znormalizowana?
R(customer, branch, jurisdiction)
Przykład #1 – analiza
• Jakie są zależności funkcyjne?
– customer branch – branch jurisdiction – customer jurisdiction
• Kluczem jest zbiór { customer }
• Zależność funkcyjna bez udziału atrybutu customer
• R nie jest znormalizowana
Przykład #2
• Lekarz może pracować w kilku szpitalach i dostaje osobne wynagrodzenie od każdego z nich.
– Czy poniższa relacja jest znormalizowana?
R(doctor, hospital, salary)
Przykład #2 – analiza
• Jakie są zależności funkcyjne?
– doctor, hospital salary
• Kluczem jest zbiór { doctor, hospital }
• Relacja jest więc znormalizowana
Przykład #3
• Do relacji R(doctor, hospital, salary) dodajemy atrybut primary_home_address
• Każdy lekarz ma adres stałego zamieszkania
• Kilku lekarzy może mieć ten sam adres stałego
zamieszkania (przychodzi baba do lekarza a lekarz też baba)
– Czy poniższa relacja jest znormalizowana?
R(doctor, hospital, salary, primary_home_address)
Przykład #3 – analiza
• Jakie są zależności funkcyjne?
– doctor, hospital salary
– doctor primary_home_address
– doctor, hospital primary_home_address
• Klucz jest ten sam { doctor, hospital }
• Tym razem mamy jednak zależność częściową
• Dekompozycja na schematy znormalizowane:
– R1(doctor, hospital, salary)
– R2(doctor, primary_home_address)
Projektowanie schematu w praktyce
• Zidentyfikuj encje aplikacji (np., lekarze, szpitale, dostawcy)
• Każdej encji dodaj atrybuty (szpital ma adres…).
• Dwa ograniczenia na atrybuty:
1. Atrybut nie może mieć atrybutów
2. Encja atrybutu musi ten atrybut funkcyjnie
determinować
Projekt logiczny
• Każda encja staje się relacją
• Do tych relacji dodaje się relacje odzwierciedlające związki, np.
– WorksIn (doctor_ID, hospital_ID)
• Znajdź zależności funkcyjne między atrybutami i sprawdź, czy schemat jest znormalizowany:
– Jeśli zachodzi zależność funkcyjna AB C, to AB powinno być (nad)kluczem
Fragmentacja pionowa
• Trzy atrybuty: account_ID, balance, address
• Zależności funkcyjne
– account_ID balance – account_ID address
• Dwa znormalizowane schematy
– (account_ID, balance, address) ORAZ
– (account_ID, balance) – (account_ID, address)
Fragmentacja pionowa – analiza
• Wybór projektu zależy od wzorca zapytań:
– Aplikacja wysyłająca miesięczny wyciąg z konta jest głównym użytkownikiem adresu właściciela konta
– Saldo jest czytane i modyfikowane kilka razy dziennie
• Projekt 2 może być
lepszy, ponieważ relacja (account_ID, balance) jest mniejsza
– Więcej par (account_ID, balance) mieści się w
pamięci, więc zwiększa się współczynnik trafień
– Pełny przegląd zadziała szybciej, ponieważ
przeczyta mniej stron
Strojenie normalizacji
• Pojedyncza znormalizowana relacja XYZ jest lepsza od dwóch znormalizowanych relacji XY i XZ, o ile częste są zapytania o atrybuty XYZ
(wtedy nie jest wymagane złączenie)
• Dwie znormalizowane relacje są lepsze, o ile:
– Użytkownicy zazwyczaj korzystają z atrybutów ze zbiorów Y i Z oddzielnie
– Rozmiar wartości atrybutów Y i Z jest duży
Antyfragmentacja pozioma
• Dealerzy opierają swoje strategie kupna obligacji na trendach ich cen. Baza danych przechowuje ceny
zamknięcia z ostatnich 3000 sesji, ale 10 ostatnich sesji jest szczególnie ważnych
• Jaki schemat?
– (bond_id, issue_date, maturity, …) (bond_id, date, price)
Czy?
– (bond_id, issue_date, maturity, today_price,…10dayago_price) (bond_id, date, price)
• Inna możliwość: perspektywa zmaterializowana
Fragmentacja pionowa i przegląd
• R (X,Y,Z)
– X to liczba całkowita – YZ to długie napisy
• Pełny przegląd
• Fragmentacja pionowa jest wyraźnie gorsza, gdy oba atrybuty są czytane razem
• Fragmentacja pionowa daje przyspieszenie, o ile tylko jeden z Y albo Z jest
odczytywany
0 0.005 0.01 0.015 0.02
No Partitioning - XYZ
Vertical Partitioning - XYZ
No Partitioning - XY
Vertical Partitioning - XY
Througput (queries/sec)
Fragmentacja i zapytania punktowe
• R (X,Y,Z)
– X to liczba całkowita – YZ to długie napisy
• Zapytania punktowe czytające XYZ lub XY
• Fragmentacja pionowa poprawia wydajność, jeśli odsetek zapytań
czytających tylko XY jest większy niż 20%
• Złączenie nie jest
kosztowne w porównaniu z pojedynczym odczytem
0 200 400 600 800 1000
0 20 40 60 80 100
% of access that only concern XY
Throughput (queries/sec)
no vertical partitioning vertical partitioning
Strojenie denormalizacji
• Denormalizacja oznacza pogwałcenie normalizacji w imię lepszej wydajności
• Denormalizacja poprawia wydajność, jeśli atrybuty różnych znormalizowanych relacji są często odczytywane razem
• Denormalizacja obniża wydajność, jeśli
dane w relacjach są często modyfikowane
Denormalizacja – stan przed
Schemat:
lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE ,
L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE,
L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT );
region( R_REGIONKEY, R_NAME, R_COMMENT );
nation( N_NATIONKEY, N_NAME, N_REGIONKEY, N_COMMENT,);
supplier( S_SUPPKEY, S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE, S_ACCTBAL, S_COMMENT);
• Wiersze lineitem = 600000 , nation = 25, region = 5, supplier = 500
Denormalizacja – stan po
lineitemdenormalized ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY, L_LINENUMBER, L_QUANTITY,
L_EXTENDEDPRICE ,
L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE,
L_RECEIPTDATE, L_SHIPINSTRUCT ,
L_SHIPMODE , L_COMMENT, L_REGIONNAME);L_REGIONNAME
– 600000 wierszy w lineitemdenormalized – Pusty bufor
– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, dyski 3x18Gb (10000RPM), Windows 2000.
Zapytania do obu schematów
select L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER,
L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE,
L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT, R_NAME from LINEITEM, REGION, SUPPLIER, NATION
where L_SUPPKEY = S_SUPPKEY and S_NATIONKEY = N_NATIONKEY and N_REGIONKEY = R_REGIONKEY and R_NAME = 'EUROPE';
select L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER,
L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE,
L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT, L_REGIONNAME from LINEITEMDENORMALIZED
where L_REGIONNAME = 'EUROPE';
Wyniki eksperymentu
• Schemat TPC-H
• Zapytanie: znajdź wszystkie pozycje zamówień dla
dostawców w Europie
• Schemat znormalizowany wymaga poczwórnego złączenia
• Po denormalizacji lineitem i wprowadzeniu nazwy regionu do lineitem otrzymujemy
0 0.0005 0.001 0.0015 0.002
normalized denormalized
Throughput (Queries/sec)
