Transcript PPT2 - pjwstk.edu.pl

Projektowanie fizycznej bazy
danych
• Ulepszanie schematu tabel i postacie normalne
• Projektowanie indeksów
SZB, Lech Banachowski
1/28
Ulepszanie schematu tabel i postacie
normalne
1. Jeśli tabela nie jest w postaci BCNF (Boyce'a-Codda), staramy się
dokonać jej dekompozycji na zbiór tabel w postaci BCNF:
•
Jeśli przy dekompozycji nie daje się zachować zależności
funkcyjnych – poprzestajemy na 3-ciej postaci normalnej.
•
Jednocześnie z dekompozycjami bierzemy pod uwagę
wymagania dotyczące szybkości działania zapytań na bazie
danych ewentualnie nie doprowadzając dekompozycji do
końca. Aby przyśpieszyć złączanie tabel - alternatywą może
być:
•
•
zbudowanie klastra tabel (fizyczna denormalizacja),
skorzystanie z kolumn typu referencji i kolekcji referencji
(w miejscu odpowiednio kolumn klucza obcego i
2/28
SZB,głównego).
Lech Banachowski
Ulepszanie schematu tabel i postacie
normalne
Gdy aplikacja przetwarza osobno dwa zbiory
wierszy może opłacać się rozdzielić tabelę na
dwie np. tabelę Osoby na tabele: Studenci i
Pracownicy. Tutaj dekompozycja jest pozioma,
przy normalizacji natomiast, dekompozycja
jest pionowa.
 Sprawdzamy czy tabela jest wolna od
zależności wielowartościowych. Jeśli nie,
dokonujemy odpowiedniej dekompozycji.

SZB, Lech Banachowski
3/28
Pole działania aplikacji bazodanowej
(workload)
• Oszacowanie liczby wierszy tabel, liczby stron.
• Oszacowanie liczby różnych wartości w kolumnach tabel.
• Najważniejsze zapytania i jak często będą używane.
• Najważniejsze aktualizacje i jak często będą używane.
• Pożądana szybkość działania tych zapytań i aktualizacji.
SZB, Lech Banachowski
4/28
Analiza zapytań

Dla każdego zapytania w projektowanej aplikacji:
–
Jakich relacji i atrybutów dotyczą?
–
Które atrybuty występują w warunkach ograniczających a
które w warunkach złączenia? Jak bardzo selektywne są te
warunki?

Podobnie dla instrukcji INSERT/DELETE/UPDATE.
Decyzje

Czy powinniśmy zmienić schemat tabel?
–
Inaczej pogrupować atrybuty w ramach relacji?
–
Normalizacja, denormalizacja (pionowy podział)?
–
Poziomy podział (np. tabelę Osoby na osobne tabele Pracownicy i
Studenci?)
–
Perspektywa zmaterializowana z wynikami obliczeń (np. statystyka
operacji na kontach bankowych) – ewentualnie z indeksami?

Czy połączyć zapis kilku tabel w klaster? Złączenie – szybsze;
operacje na pojedynczych tabelach wolniejsze niż bez klastra.

Jakie założyć indeksy? Jakiego rodzaju?
–
Wewnętrzny? Pogrupowany? Hasz/B+ drzewo?
Dynamiczny/statyczny? Bitmapowy?
–
Jakie atrybuty zamieścić w kluczach indeksów?
Indeksy
1. Indeks główny/jednoznaczny.
2. Indeks pogrupowany.
3. Indeks haszowany wewnętrzny.
4. Indeks z selektywnym wyszukiwaniem.
5. Indeks obejmujący wszystkie atrybuty warunku wyszukiwania i
ewentualnie wyrażenia (strategia tylko indeks).
6. Indeks bitmapowy (duża liczba wierszy, mała liczba różnych
wartości w kolumnie, kombinacja boolowska warunków
równościowych).
SZB, Lech Banachowski
7/28
Klastry

Wspomagają złączenia/wyszukiwania tabel
według tej samej kolumny złączenia.
SZB, Lech Banachowski
8/28
Perspektywy zmaterializowane

Obejmujące złączenia i/lub agregacje:
– ORGANIZATION HEAP - scan
– ORGANIZATION INDEX – wyszukiwanie

Indeks można uważać za perspektywę
zmaterializowaną dla atrybutów wyszukiwania.
SZB, Lech Banachowski
9/28
Wybór indeksów

Każdy indeks powinien wspomagać wykonywanie
określonych zapytań.

Zaczynamy od najważniejszych zapytań analizujac możliwe
plany ich wykonania. Jaki indeks mógłby wspomóc ich
wykonanie?

Rozpatrujemy jaki wpływ będzie miał ten indeks na operacje
INSERT/DELETE/UPDATE oraz na ilość potrzebnego
miejsca na dysku?

Indeks jest automatycznie tworzony przez system dla
każdego klucza głównego i jednoznacznego.
Wspomaganie wyszukiwania
• Indeks główny, jednoznaczny, selektywny (np..
Emp.Ename=:name).
• Indeks pogrupowany (szczególnie dla zapytań zakresowych).
• Indeks haszowany wewnętrzny.
• Indeks bitmapowy gdy mała liczba różnych wartości atrybutu,
również do zliczania NULL.
• Indeks zawierający wszystkie atrybuty zapytania.
SZB, Lech Banachowski
11/28
Wspomaganie złączenia
• Dla kolumn(y) złączenia w tabeli wewnętrznej złączenia.
• indeks główny, jednoznaczny, selektywny,
• indeks haszowany wewnętrzny.
•
Klaster.
• Perspektywa zmaterializowana.
SZB, Lech Banachowski
12/28
Wspomaganie agregacji
• Indeks zawierający potrzebne atrybuty do wyliczenia
agregacji.
• Perspektywa zmaterializowana.
SZB, Lech Banachowski
13/28
Uwagi

Ponieważ tylko jeden indeks może być wewnętrzny dla jednej
relacji, jeśli zachodzi potrzeba jego użycia, wybierz go biorąc
pod uwagę najważniejsze zapytania. Ewentualnie, dla innych
krytycznych wyszukiwań zbuduj indeksy (strategia tylko indeks)
bądź perspektywy zmaterializowane.

Gdy klauzula WHERE zawiera kilka warunków wskazany
indeks o wieloatrybutowym kluczu wyszukiwania.
–
Przy warunkach zakresowych istotna jest kolejność
atrybutów w kluczu wyszukiwania.
–
Indeksy takie mogą czasem umożliwić zastosowanie
strategii “tylko-indeks” – wtedy pogrupowanie nie ma
znaczenia.
Przykład 1


SELECT E.Ename, D.Loc
FROM Emp E JOIN Dept D
ON E.Deptno=D.Deptno
WHERE D.Dname='Sales';
Indeks na D.Dname wspomaga selekcję D.Dname='Sales'
gdy D jest tabelą zewnętrzną złączenia. Potrzebny jest
indeks albo selektywny albo pogrupowany albo haszowany
wewnętrzny.
Indeks na E.Deptno wspomaga złączenie (E – tabela
wewnętrzna). Powinien być pogrupowany albo haszowany
wewnętrzny, ponieważ spodziewamy się wybrania wielu
wierszy z E.

Perspektywa zmaterializowana, klaster.
Przykład 2
SELECT E.Ename, D.Loc
FROM Emp E JOIN Dept D ON E.Deptno=D.Deptno
WHERE E.Sal BETWEEN 10000 AND 20000 AND
E.Job='SALESMAN';




Ze względu na warunki ograniczające dla tabeli E, wybieramy
Emp E jako tabelę zewnętrzną złączenia. Wtedy Dept D będzie
tabelą wewnętrzną złączenia.
Kolumna D.Deptno jest kluczem głównym, posiada zawsze
indeks, który można użyć przy złączaniu.
Jaki indeks jest potrzebny na tabeli Emp E? Albo indeks na
E.Sal albo na E.Job – wybór zależy od selektywności
warunków wyszukiwania - przy złej selektywności potrzebny
byłby indeks pogrupowany ewentualnie indeks haszowany
wewnętrzny na E.Job.
Klaster, perspektywa zmaterializowana.
Przykład 3



SELECT E.Deptno, COUNT(*)
FROM Emp E
WHERE E.Sal>2000
GROUP BY E.Deptno;
Potrzebny jest indeks pogrupowany albo haszowany
wewnętrzny na kolumnie E.Deptno. Jeśli nie jest możliwe
założenie takiego indeksu, system posortuje względem
E.Deptno plik rekordów tabeli Emp E - biorąc pod uwagę tylko
rekordy dla których E.Sal>2000. Następnie wykona zliczanie
COUNT(*).
Byłoby jeszcze lepiej, gdybyśmy dysponowali indeksem
drzewowym na kolumnach <Emp.Deptno, Emp.Sal>. Wtedy
moglibyśmy wykonać zapytanie przechodząc tylko indeks bez
przechodzenia do pliku rekordów – tzn. stosując strategię
tylko-indeks.
Perspektywa zmaterializowana.
Przykład 4
Pewne zapytania, jak ostatnie, można wykonać bezpośrednio przez
indeks - bez przechodzenia do pliku rekordów.
SELECT E.Deptno, COUNT(*)
FROM Emp E
GROUP BY E.Deptno;
Wystarczy dowolny indeks na E.Deptno, ponieważ nie trzeba
przechodzić do pliku rekordów tabeli E.
SZB, Lech Banachowski
18/28
Podsumowanie

Wybór indeksów i perspektyw zmaterializowanych ma istotny
wpływ na szybkość wykonywania zapytań.
–
–
–
–
–




Aktualizacja indeksów i perspektyw zmaterializowanych spowalnia
INSERT/DELETE/UPDATE.
Wybieraj indeksy, które wspomagają wykonywanie wielu zapytań.
Buduj indeksy umożliwiające strategie tylko-indeks.
Tylko jeden indeks może być wewnętrzny dla jednej relacji.
Kolejność pól w kluczach wielo-atrybutowych może być istotna.
Od czasu do czasu trzeba przebudowywać indeksy.
Od czasu do czasu trzeba odświeżać statystyki.
Sprawdzaj plan wybrany przez optymalizator - ewentualnie
zmień indeks, zapis zapytania. Ewentualnie użyj wskazówek
do optymalizatora (hint).
Unikaj podzapytań, DISTINCT, wyrażeń (może być trudno
użyć indeks), GROUP BY, OR, tymczasowych tabel.