Transcript 02 Normalizacja a wydajno

Optymalizacja aplikacji
bazodanowych
Część 2: Normalizacja a wydajność
Copyright © 2011 Accenture All Rights Reserved. Accenture, its logo, and High Performance Delivered are trademarks of Accenture.
Plan
• Normalizacja – przypomnienie
• Denormalizacja
• Konsekwencje denormalizacji dla odczytu i modyfikacji danych
• Ćwiczenie
Gdzie jesteśmy?
• Dążenie do optymalizacji powinno następować na każdym
etapie tworzenia systemu informatycznego.
– Analiza
-> konstrukcja procesów biznesowych
– Projektowanie
-> model danych
-> moduły programowe
-> dobór technologii
– Kodowanie
-> jakość kodu
– Testy
-> kontrola efektywności
– Wdrożenie
-> konfiguracja środowiska
Normalizacja danych
• Celem normalizacji jest usunięcie niepożądanych cech struktury bazy
danych.
• Przykłady cech niepożądanych to:
– redundancja (nadmiarowość) danych – wpływa na wielkość zbiorów danych,
– anomalia wstawiania – wstawienie nowego rekordu może wymagać
znajomości danych, które na początku nie są wymagane,
– anomalia modyfikacji – modyfikacja danych w jednym miejscu wymusza
zmianę danych w wielu krotkach, co skutkuje ryzykiem utraty spójności,
– anomalia usunięć – usunięcie jednych danych skutkuje zniknięciem innych
danych.
• W wyniku normalizacji zmienia się struktura bazy danych, natomiast
dane nie są tracone.
• Normalizacja jest dokonywana poprzez przekształcanie struktury bazy
danych do kolejnych postaci normalnych (ang. Normal Form)
Normalizacja danych
• Postacie zdefiniowane przez E.F.Codda
– Pierwsza postać normalna = 1 PN = 1 NF
– Druga postać normalna = 2 PN = 2 NF
– Trzecia postać normalna = 3 PN = 3 NF
• Dodatkowe postacie, zdefiniowane w późniejszym okresie
– Postać normalna Boyce’a - Codd’a = BCNF
– Czwarta postać normalna
– Piąta postać normalna
Normalizacja danych
Imię i Nazwisko
Adresy
1. Jan Kowalski
1. Główny: 99-999 Łódź, Włókniarzy 10 m. 9
2. Korespondencyjny: 98-088 Łódź, Wyszyńskiego 23 m. 14
2. Anna Malinowska
1. Główny: 01-010 Warszawa, Jerozolimskie 102
3. Stefan Kwiatkowski
1. Główny: 34-567 Kraków, Stary Rynek 8 m. 5
2. Korespondencyjny: 33-333 Kraków, Dworcowa 12/13
Id
Imię
osoby
Nazwisko
1
Kowalski
Jan
Id
Rodzaj Kod
adresu adresu
Miejsco- Ulica
1
G
wość
99-999 Łódź
2
K
98-088 Łódź
Nr domu Nr
lokalu
Al. Włókniarzy
10
9
Wyszyńskiego
23
14
2
Anna
Malinowska 1
G
01-010 Warszawa Al. Jerozolimskie 102
3
Stefan Kwiatkowski 1
G
34-567 Kraków
Start Rynek
8
2
K
33-333 Kraków
Dworcowa
12/13
5
Normalizacja danych
Id
Imię
osoby
Nazwisko
1
Jan
Kowalski
Malinowska
Id
Rodzaj Kod
adresu adresu
Miejsco-
Ulica
Nr domu Nr
lokalu
2
Anna
1
2
1
G
K
G
wość
99-999 Łódź
Al. Włókniarzy
10
98-088 Łódź
Wyszyńskiego
23
01-010 Warszawa Al. Jerozolimskie 102
3
Stefan Kwiatkowski 1
G
34-567 Kraków
Start Rynek
8
2
K
33-333 Kraków
Dworcowa
12/13
1PN
Id
Imię
osoby
Nazwisko
1
1
Jan
Jan
Kowalski
Kowalski
1
2
G
K
wość
99-999 Łódź
98-088 Łódź
2
Anna
Malinowska 1
G
01-010 Warszawa Al. Jerozolimskie 102
3
3
Stefan Kwiatkowski 1
Stefan Kwiatkowski 2
G
K
34-567 Kraków
33-333 Kraków
Id
Rodzaj Kod
adresu adresu
Miejsco- Ulica
Al. Włókniarzy
Wyszyńskiego
Start Rynek
Dworcowa
9
14
5
Nr domu Nr
lokalu
10
23
8
12/13
9
14
5
Normalizacja danych
1PN
Id
oso
by
Imię
1
Jan
1
Nazwisko
Id
adr
esu
Rodzaj
adresu
Kod
Miejsco-
Ulica
Nr
domu
Nr
lokal
u
Kowalski
1
G
99-999
Łódź
Al. Włókniarzy
10
9
Jan
Kowalski
2
K
98-088
Łódź
Wyszyńskiego
23
14
2
Anna
Malinowska
1
G
01-010
Warszawa
Al. Jerozolimskie
102
3
Stefan
Kwiatkowski
1
G
34-567
Kraków
Start Rynek
8
3
Stefan
Kwiatkowski
2
K
33-333
Kraków
Dworcowa
12/13
Ulica
Nr
domu
Nr
lokalu
9
14
wość
Id
osoby
Imię
Nazwisko
Id
Id
Rodzaj Kod
osoby adresu adresu
Miejsco-
1
Jan
Kowalski
2
Anna
Malinowska
1
1
1
2
G
K
99-999
98-088
Łódź
Łódź
Al. Włókniarzy
Wyszyńskiego
10
23
3
Stefan
Kwiatkowski
2
1
G
01-010
Warszawa
Al. Jerozolimskie
102
3
3
1
2
G
K
34-567
33-333
Kraków
Kraków
Start Rynek
Dworcowa
8
12/13
2PN
wość
5
5
W relacja (1PN) jest jeden klucz kandydujący (Id osoby, Id adresu).
Imię i Nazwisko są funkcjonalnie zależnie od klucza kandydującego, ale nie są w pełni funkcjonalnie zależne od tego klucza, bo są również funkcjonalnie
zależne od podzbioru atrybutów tego klucza – są zależne od (Id osoby).
Atrybuty Rodzaj adresu, Kod, Miejscowość, Ulica, Nr domy, Nr lokalu są w pełni funkcjonalnie zależne od klucza kandydującego (Id osoby, Id adresu).
Normalizacja danych
Id
osoby
Imię
1
Jan
Kowalski
2
Anna
Malinowska
3
Stefan
Kwiatkowski
Id
Id
Rodzaj Kod
osoby adresu adresu
Miejsco-
Ulica
Nr
domu
Nr
lokalu
1
1
1
2
G
K
99-999
98-088
Łódź
Łódź
Al. Włókniarzy
Wyszyńskiego
10
23
9
14
2
1
G
01-010
Warszawa
Al. Jerozolimskie
102
3
3
1
2
G
K
34-567
33-333
Kraków
Kraków
Start Rynek
Dworcowa
8
12/13
Nazwisko
wość
5
2NF
3NF
Id
osoby
Imię
Nazwisko
1
Jan
Kowalski
2
Anna
Malinowska
3
Stefan
Kwiatkowski
Id
Id
Rodzaj Kod
osoby adresu adresu
Ulica
Nr
Nr
domu lokalu
Kod
Miejscowość
1
1
G
99-999 Al. Włókniarzy
10
9
99-999 Łódź
1
2
K
98-088 Wyszyńskiego
23
14
98-088 Łódź
2
1
G
01-010 Al. Jerozolimskie 102
01-010 Warszawa
3
3
1
2
G
K
34-567 Start Rynek
33-333 Dworcowa
34-567 Kraków
33-333 Kraków
8
5
12/13
Denormalizacja
• Wprowadzenie nadmiarowych danych do struktury bazy
• Celem denormalizacji może być:
– Zwiększenie wydajności systemu
– Uproszczenie zapytań
– Poprawienie przejrzystości struktury bazy danych
• Najpierw normalizacja, potem denormalizacja
• Denormalizować należy świadomie
• Sposób denormalizacji wynika ze specyfiki danego systemu;
nie podlega standaryzacji.
• Czynniki wpływające na zasadność denormalizacji:
– Częstość odczytu danych
– Zmienność danych
– Liczebność danych
Denormalizacja cd
• Najczęstsze sposoby denormalizacji
– Powtórzenie kluczowych danych w kilku tabelach (nr klienta, nr
faktury)
– Złączenia kilku tabel w jedną
– Przechowanie kluczy obcych do tabel pośrednio powiązanych
– Przechowywanie wartości wyliczonych (wartość faktury, średnia ocen,
licznik transakcji)
Przykład 1
• Model danych znormalizowany
–
–
–
–
Klient (nr klienta, nazwa, NIP)
Faktura (nr faktury, nr klienta, data)
Pozycja faktury (nr faktury, nr pozycji, id produktu, ilość, cena netto)
Produkt (id produktu, nazwa produktu, cena netto, stawka VAT)
• Model danych zdenormalizowany
– Klient (nr klienta, nazwa, NIP)
– Faktura (nr faktury, nr klienta, NIP, data, wartość netto, wartość
VAT, wartość brutto, liczba pozycji)
– Pozycja faktury (nr faktury, nr pozycji, nr klienta, id produktu, nazwa
produktu, ilość, cena netto, wartość netto, stawka VAT, kwota VAT,
cena brutto, wartość brutto)
– Produkt (id produktu, nazwa produktu, cena netto, stawka VAT,
cena brutto)
Przykład 2
• Model danych znormalizowany
– Osoba (id osoby, imię, nazwisko)
– Adres osoby (id adresu, id osoby, typ adresu)
– Adres (id adresu, kod pocztowy, id miejscowości, ulica, nr ulicy, nr
lokalu)
– Kod pocztowy (kod pocztowy, poczta, województwo)
– Miejscowość (id miejscowości, miejscowość)
• Model danych zdenormalizowany
– Osoba (id osoby, imię, nazwisko, liczba adresów)
– Adres (id adresu, id osoby, typ adresu, kod pocztowy, poczta,
województwo, miejscowość, ulica, nr ulicy, nr lokalu)
Przykład 3 (do ćwiczeń)
• Model danych znormalizowany
– Stanowisko (Id stanowiska, nazwa, dodatek)
– Pracownik (Id pracownika, imię, nazwisko, płeć, pensja, id
stanowiska, id szefa)
– Kara (Id kary, z tytułu, id pracownika, kwota)
– Nagroda (Id nagrody, z tytułu, id pracownika, kwota)
• Model danych zdenormalizowany
– Stanowisko (Id stanowiska, nazwa, dodatek)
– Pracownik (Id pracownika, imię, nazwisko, płeć, pensja, id
stanowiska, id szefa, liczba nagród, suma nagród, liczba kar, suma
kar)
– Jednorazowa zmian wypłaty (Id zmiany, z tytułu, id pracownika,
kwota, typ, id stanowiska)
Co o tym mówi Sieć
(do poczytania w wolnej chwili)
• http://www.devblogi.pl/2010/03/moze-normalizowanie-niejest-normalne.html
„normalizuj dopóki to nie boli, denormalizuj dopóki to działa”
• http://msdn.microsoft.com/pl-pl/library/ms191178.aspx
• http://webmaster.helion.pl/index.php/kursmysqlprojektowanie-relacyjnych-baz-danych
• http://www.zyxist.com/pokaz.php/kilka_sztuczek_ad_projekt
owania_baz_danych
Ćwiczenie
1. Zalogować się jako SYSTEM
2. Założyć użytkownika i przelogować się na niego (1-1_user.sql)
3. Założyć strukturę tabel (1-2_struktura.sql) i wypełnić danymi (13_dane.sql)
4. Założyć zdenormalizowaną strukturę tabel (21_struktura_denormalizacja.sql) i wypełnić danymi (22_dane_denormalizacja.sql)
5. Dla obu struktur tabel napisać polecenia SQL wykonujące:
a.
b.
c.
d.
Dodanie nowej kary
Zmianę kwoty nagrody o id W z kwoty X na Y dla pracownika Z
Raport zestawiający łączną kwotę wypłaty dla wskazanego pracownika
Raport w postaci: Stanowisko, liczba pracowników, suma kar, suma
nagród.
Questions & Answers