Transcript 14. Strojenie schematu

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
Oryginał: Shasha & Bonnet
14. Strojenie schematu
2
Pewne schematy są lepsze od innych
• Przestrzeń
• Schemat 1:
OnOrder1(supplier_id, part_id,
quantity, supplier_address)
• Schemat 2:
OnOrder2(supplier_id, part_id,
quantity);
Supplier(supplier_id,
supplier_address);
Oryginał: Shasha & Bonnet
– 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.
14. Strojenie schematu
3
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
Oryginał: Shasha & Bonnet
14. Strojenie schematu
4
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
Oryginał: Shasha & Bonnet
14. Strojenie schematu
5
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)
Oryginał: Shasha & Bonnet
14. Strojenie schematu
6
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)
Oryginał: Shasha & Bonnet
14. Strojenie schematu
7
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
Oryginał: Shasha & Bonnet
14. Strojenie schematu
8
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)
Oryginał: Shasha & Bonnet
14. Strojenie schematu
9
Przykład #2 – analiza
• Jakie są zależności funkcyjne?
– doctor, hospital  salary
• Kluczem jest zbiór { doctor, hospital }
• Relacja jest więc znormalizowana
Oryginał: Shasha & Bonnet
14. Strojenie schematu
10
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)
Oryginał: Shasha & Bonnet
14. Strojenie schematu
11
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)
Oryginał: Shasha & Bonnet
14. Strojenie schematu
12
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ć
Oryginał: Shasha & Bonnet
14. Strojenie schematu
13
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
Oryginał: Shasha & Bonnet
14. Strojenie schematu
14
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?
Oryginał: Shasha & Bonnet
14. Strojenie schematu
15
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
Oryginał: Shasha & Bonnet
• 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
14. Strojenie schematu
16
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
Oryginał: Shasha & Bonnet
14. Strojenie schematu
17
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
Oryginał: Shasha & Bonnet
14. Strojenie schematu
18
Fragmentacja pionowa i przegląd
• R (X,Y,Z)
– X to liczba całkowita
– YZ to długie napisy
Througput (queries/sec)
0.02
0.015
0.01
0.005
0
No Partitioning Vertical
No Partitioning Vertical
XYZ
Partitioning - XYZ
XY
Partitioning - XY
Oryginał: Shasha & Bonnet
• 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
14. Strojenie schematu
19
Fragmentacja i zapytania punktowe
• R (X,Y,Z)
– X to liczba całkowita
– YZ to długie napisy
Throughput (queries/sec)
1000
800
600
400
no vertical partitioning
vertical partitioning
200
0
0
20
40
60
% of access that only concern XY
Oryginał: Shasha & Bonnet
80
100
• 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
14. Strojenie schematu
20
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
Oryginał: Shasha & Bonnet
14. Strojenie schematu
21
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
Oryginał: Shasha & Bonnet
14. Strojenie schematu
22
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);
– 600000 wierszy w lineitemdenormalized
– Pusty bufor
– Dual Pentium II (450MHz, 512Kb), 512 Mb RAM,
dyski 3x18Gb (10000RPM), Windows 2000.
Oryginał: Shasha & Bonnet
14. Strojenie schematu
23
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';
Oryginał: Shasha & Bonnet
14. Strojenie schematu
24
Throughput (Queries/sec)
Wyniki eksperymentu
0.002
0.0015
0.001
0.0005
0
normalized
Oryginał: Shasha & Bonnet
denormalized
• 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
14. Strojenie schematu
25