Systemy zarządzania bazami danych

Systemy zarządzania bazami danych

14. Strojenie schematu

Oryginał: Shasha & Bonnet 214. Strojenie schematu

Schemat bazy danych

• Schemat relacji składa się z nazwy relacji i zbioru atrybutówR(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);– { supplier_id } jest kluczem


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 znormalizowanaOnOrder1(supplier_id, part_id, quantity, supplier_address)

– OnOrder2 i Supplier są znormalizowaneOnOrder2(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ów2. 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)

• Który z nich jest lepszy?


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

VerticalPartitioning - XYZ

No Partitioning -XY

VerticalPartitioning - XY

Th

rou

gp

ut

(qu

erie

s/se

c)


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

Th

rou

gh

pu

t (q

uer

ies/

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_REGIONNAMEL_REGIONNAME);

– 600000 wierszy w lineitemdenormalized

– Pusty bufor

– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, dyski 3x18Gb (10000RPM), Windows 2000.


Zapytania do obu schematówselect 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 30% poprawę wydajności

0

0.0005

0.001

0.0015

0.002

normalized denormalized

Th

rou

gh

pu

t (Q

uer

ies/

sec)

Systemy zarządzania bazami danych

Documents

Transcript of Systemy zarządzania bazami danych