Wykład 5
Fizyczne projektowanie bazy danych
(Paul Beynon-Davies, Systemy baz danych )
1.Logiczne projektowanie bazy danych - niezależnie od implementacji
Konstruowanie modelu reguł działalności stosowanych w pewnej organizacji w postaci relacji czyli tabel
2. Fizyczne projektowanie bazy danych - zależnie od implementacji
Analiza wyników logicznego projektu
Uwzględnienie wymagań wydajnościowych
Uwzględnienie wymagań pamięciowych
Implementacja przy użyciu mechanizmów wybranego Systemu Zarządzania Bazą Danych
Produkty wejściowe projektowania fizycznego
1. Model logiczny typu relacyjnego (diagramy związków encji, tabele jako odwzorowanie diagramu związków encji)
2. Przybliżone oszacowanie liczby wierszy w tabelach – analiza ilości 3. Szacunkowa analiza sposobów użycia tabel za pomocą rodzaju i
częstości występowania operacji, które będą prawdopodobnie oddziaływać na tabele w naszej bazie danych – analiza użycia 4. Lista więzów integralności
5. Lista najczęściej wykonywanych raportów
Produkty wyjściowe projektowania fizycznego
1. Struktury plików zadeklarowane w wybranym języku definiowania danych (Data Definition Language - DDL)
2. Indeksy na strukturach plików 3. Klastry plików
4. Zbiór powiązań wewnętrznych wyrażony w DDL i dodatkowych więzów integralności wyrażony w wybranym języku integralności danych (Data Integrity Language - DIL)
5. Zbiór zapytań zoptymalizowany do działania w konkretnej bazie danych (zapytanie zoptymalizowane – taka forma zapytania, która podaje odpowiedź w możliwie najkrótszym czasie)
Przykład projektowania wydajnej relacyjnej bazy danych 1) Produkty wejściowe
1.1. Modele relacyjny (pominięto etap analizy czyli tworzenia diagramu związków encji i odwzorowania w tabele)
Katalog książek:
Tytul (Id_tytulu, tytul, autor, ISBN, typ_ksiazki) Ksiazka (Id_ksiazki, numer_ksiazki, Id_tytulu) Rezerwacje(Id_rezerwacji, Id_ksiazki)
Uwaga: atrybut typ_ksiazki umożliwia klasyfikację książek na książki techniczne i beletrystyczne
1.2. Analiza ilości
Oszacowanie maksymalnej liczby krotek (danych) pozwala oszacować rozmiar pamięci dyskowej
Oszacowanie średniej liczby krotek, które mogą być zapisane w bazie danych daje możliwość oceny modelu dostępu do danych Tabela Maksymalna
liczba wierszy
a
Średnia liczba wierszy
b
Rozmiar kolumn (krotki)
c
Maksymalny rozmiar
tabeli a*c
Średni rozmiar
tabeli b*c Tytul 5 000 3 000 49 245 000 147 000 Ksiazka 100 000 30 000 9 900 000 270 000 Rezerwacj
e 100 000 50 000 8 800 000 400 000
Suma 1 945 000 817 000
1.3. Analiza użycia
Identyfikacja podstawowych zapytań, wymaganych w bazie danych jako ciągi operacji: wstawiania, modyfikowania, wyszukiwania i usuwania,
Zmienność pliku w ciągu roku
w tym samym czasie usuwa się 50 tytułów i dodaje 50 nowych, czyli 50/5000=1%
w tym samym czasie 1000 książek usuwa się i 1000 dodaje się, czyli 1000/100000=1%
w tym samym czasie usuwa się 10000 rezerwacji i zakłada nowych 10000 rezerwacji, czyli 10000/10000=100%.
Rozmiar i zmienność pliku mają wpływ na dostęp do bazy danych:
Im większy plik, tym większa konieczność zastosowania specjalnych struktur dostępu do baz danych
Im bardziej zmienny plik, tym bardziej konieczne są jak najmniej czasochłonne struktury dostępu do baz danych
Zapytanie Częstotliwość
wykonywania Konieczność
zapewnienia najkrótszego czasu wykonania zapytania
Podaj tytuły książek Raz na rok -
Podaj autorów książek Raz na rok - Podaj tytuły książek technicznych Raz na miesiąc - Podaj numery książek
technicznych Raz dziennie -
Podaj liczbę zarezerwowanych
tytułów technicznych Wiele razy
dziennie +
1.4. Analiza integralności
Trzy typy więzów integralności wewnętrznej:
klucza głównego (nie może być równy null)
klucza obcego (zazwyczaj nie może być równy null)
dziedziny wartości np. typ_tytulu {Techniczna, Beletrystyczna}
Typy więzów integralności dodatkowej:
więzy przejścia np. jeśli żadna książka o danym tytule nie była
wypożyczona przez rok, należy te książki wraz tytułem usunąć z bazy danych
więzy statyczne: liczba rezerwacji danego tytułu nie może przekroczyć podwójnej liczby książek o tym tytule
Obsługa więzów integralności jest czasochłonna.
2) Zdefiniowanie wydajności
Wydajność jest wyrażana za pomocą:
czasów reakcji systemu (czas oczekiwania na wykonanie operacji oraz czas wykonania operacji)
przepustowości (średnia liczba żądań obsłużonych przez system w określonym czasie) itp
Wydajność zależy od:
liczby operacji oczekujących na wykonanie
złożoności obliczeniowej i algorytmicznej
parametrów czasowych sprzętu
zapotrzebowania na zasoby (pamięć operacyjną, dyski)
algorytmów szeregowania operacji (np. zapytań)
Aby zdefiniować wydajność systemu, trzeba zdefiniować misję aplikacji:
analiza ilości – maksymalna i średnia liczba instancji, zmienność instancji
analiza użycia- priorytetowa lista najważniejszych transakcji, bo najczęściej wykonywanych – należy określić oczekiwaną minimalną częstotliwość tych transakcji
analiza integralności – sposób obsługi więzów integralności Decyzje dotyczące dostrajania bazy danych pod względem wydajności
1. tworzenie mechanizmów dostępu związanych z przechowywaniem 2. dodawanie indeksów
3. denormalizacja 4. więzy integralności 5. wykorzystanie SZBD
Ad. 1)
sekwencyjność (małe tabele, średnie i duże tabele, gdy należy udostępnić ponad 20% wierszy, dla dowolnych tabel w przypadku zapytań o niskim priorytecie)
haszowanie (jako główna ścieżka dostępu do pliku, zbudowana na kluczach głównych)
Ad. 2)
Kompromis między czasem wykonywania zapytań oraz czasem modyfikowania, wstawiania i usuwania danych
indeksy na kluczach głównych (obowiązkowe)
indeksy na kluczach obcych
indeksy wtórne Ad 3)
Stosowanie denormalizacji jest stosowane, gdy inne metody zawiodą Np. Wprowadzenie tablicy łączącej tablicę tytul i tablice ksiazka
Ksiazka_i_tytul (Id_ksiazka_tytul, tytul, autor, ISBN, cena, numer) Ad 4)
Implementowanie więzów integralności:
a) wewnętrznie: określa się więzy integralność encji (ze względu na klucz główny), więzy integralność referencyjnej (klucze obce w zasadzie nie powinny być równe null), więzy integralności dziedziny (np.
wartości typ_tytulu muszą pochodzić z dziedziny {Techniczna, Beletrystyczna}
proceduralnie: np. procedury wyzwalane
nieproceduralnie: zastosowanie centralnych słowników danych, co umożliwia utrzymywanie więzów integralności niezależnie od programów użytkowych
Ad5)
Wybór Systemu Zarządzania Bazą Danych może mieć różny wpływ na wydajność bazy danych
typ optymalizacji
praca wielowątkowa
zapytania interpretowane podczas wykonywania zapytania są wolniejsze- jednak poprawia się ich wydajność, gdy baza jest zmienna lub dostęp do danych ma charakter losowy
zapytania skompilowane są szybsze od interpretowanych
Przykład 1
Zastosowania różnych metod dostępu do danych poprawiające wydajności bazy danych – projektowanie tabel i użycie indeksów
1.1. Pytanie zawierające selekcję na tabeli Tytul a) liczba krotek w tabeli Tytul jest równa n,
b) 0.9 n to krotki zawierające wartość atrybutu typ_tytulu=’Techniczna’
c) 0.1 n to krotki zawierające wartość atrybutu typ_tytulu=’Beletrystyczna’
Pytanie
Select * From Tytul
Where typ_tytulu=’Techniczna’
1.1.1. Czasochłonność w technice sekwencyjnej Do i:= 1 to n
if Tytul[i].typ_ksiazki=’Techniczna’
Dodaj krotkę Tytul[i] do wynikowej tabeli T1=t1+t2
t1= n - czasochłonność czytania krotek z tabeli Tytul t2=0.9n - czasochłonność zapisu krotek z tabeli Tytul o
typ_Tytulu=’Techniczna’
T1=1.9n
1.1.2. Czasochłonność po dodaniu indeksu na atrybucie typ_tytulu w tabeli Tytul
T1’’=t1’’+t2’’
t1’’= 0.9n - czasochłonność czytania krotek z tabeli Tytul
t3’’= k - czasochłonność zapisu krotek z tabeli Tytul jako wyniku selekcji T1’’=0.9n+k=0.9n+k=0.9n+k
T1’’=0.9n
1.2.3. Czasochłonność po podziale tabeli Tytul – poziomy (podział na tytuly techniczne i beletrystyczne) i pionowy (odrzucenie atrybutu typ_tytulu)
Tytul_Techniczny(Id_tytulu, tytul, autor, ISBN) Tytul_Beletrystyczny(Id_tytulu, tytul, autor, ISBN) T1’’’=t1’’’+t2’’’
t1’’’= 0.9n - czasochłonność czytania krotek z tabeli Tytul_Techniczny
Ksiazka (Id_ksiazki, numer, id_tytulu_) Np. Pytanie:
Select * From tabela, ksiazka Where typ_ksiazka=’Techniczna’
And Id_tytulu=id_tytulu_;
d) liczba krotek w tabeli Tytul jest równa n, gdzie 0.9 n to krotki zawierające wartość atrybutu typ_tytulu=’Techniczna’
e) liczba krotek w tabeli Ksiazka odpowiadająca tytułom o wartości atrybutu typ_tytulu równym „Techniczna’ jest równa k
f) liczba krotek w tabeli Ksiazka jest równa m
g) na stronie pliku przechowuje się p krotek, stąd mamy m/p oraz n/p. Zakłada się najgorszy wariant, w którym każda krotka jest na innej stronie
1.2.1. Czasochłonność w technice sekwencyjnej
Do i:= 1 to n
if Tytul[i].typ_ksiazki=’Techniczna’
Do j|:= 1 to m do
if Ksiazka[j].id_tytulu_=Tytul[i].Id_tytulu
Dodaj krotkę Ksiazka[j] * Tytul[i] do wynikowej tabeli T1=t1+t2+t3
t1= n - czasochłonność czytania krotek z tabeli Tytul
t2=0.9nm - czasochłonność czytania krotek z tabeli Ksiazka i testowań
warunków (drugi argument to testowanie zaindeksowanych kluczy głównych dla k krotek z tabeli Ksiazka)
t3= k - czasochłonność zapisu złączonych krotek z tabel Ksiazka i Tytul T1=n+0.9nm+k
1.2.2. Czasochłonność po dodaniu indeksu na atrybucie typ_tytulu w tabeli Tytul
Do i:= 1 to n
if Tytul[i].typ_ksiazki=’Techniczna’
//mamy 0.9n indeksow w tabeli Tytul dla atrybutu typ_tytulu Do j|:= 1 to 0.9n do
Ksiazka[j].id_tytulu_=Tytul[i].Id_tytulu
Dodaj krotkę Ksiazka[j] * Tytul[i] do wynikowej tabeli T1’’=t1’’+t2’’+t3’’
t1’’= 0.9n - czasochłonność czytania krotek z tabeli Tytul
t2’’=0.9n*0.9n - czasochłonność czytania krotek z tabeli Ksiazka i testowań warunków (drugi argument to testowanie zaindeksowanych kluczy głównych dla k krotek z tabeli Ksiazka)
t3’’= k - czasochłonność zapisu złączonych krotek z tabel Ksiazka i Tytul T1’’=0.9n+0.9n*0.9n+k+k=0.9n+0.81n+2k=1.71n+k
T1’’=1.71n+k
1.2.3. Czasochłonność po podziale tabeli Tytul – poziomy (podział na tytuly techniczne i beletrystyczne) i pionowy (odrzucenie atrybutu typ_tytulu)
Tytul_Techniczny(Id_tytulu, tytul, autor, ISBN) Tytul_Beletrystyczny(Id_tytulu, tytul, autor, ISBN) Ksiazka(Id_ksiazki, numer, id_tytulu_)
Do i:= 1 to 0.9n //mamy 0.9n krotek w tabeli Tytul_Techniczny Do j|:= 1 to 0.9n do
Ksiazka[j].id_tytulu_=Tytul[i].Id_tytulu
Dodaj krotkę Ksiazka[j] * Tytul[i] do wynikowej tabeli T1’’’=t1’’’+t2’’’+t3’’’
t1’’’= 0.9n - czasochłonność czytania krotek z tabeli Tytul
t2’’’=0.9n*0.9n +k - czasochłonność czytania krotek z tabeli Ksiazka i testowań warunków (drugi argument to testowanie
zaindeksowanych kluczy głównych dla k krotek z tabeli Ksiazka)
t3’’’= k - czasochłonność zapisu złączonych krotek z tabel Ksiazka i Tytul T1’’’=0.9n+0.9n*0.9n+k=0.9n+0.81n+2k=1.71n+k
