Transcript PPT1 - pjwstk.edu.pl

WYKONYWANIE
ZAPYTAŃ
Przygotował Lech Banachowski na podstawie:
1.
Raghu Ramakrishnan, Johannes Gehrke, Database Management Systems, McGrawHill,
2000 (książka i slide’y).
2.
Lech Banachowski, Krzysztof Stencel, Systemy zarzadzania bazami danych, Wyd.
PJWSTK, 2007.
Operatory relacyjne
–
–
–
–
–
Selekcja Selekcja podzbioru wierszy (klauzula WHERE).
Projekcja Pominięcie z wyniku niepotrzebnych kolumn (klauzula
SELECT).
Złączenie Złączenie relacji (tabel), iloczyn kartezjański
Suma (UNION) Suma relacji (tabel), INERSECT, EXCEPT
Agregacja (SUM, MIN, itd.) i grupowanie GROUP BY.

* Relacja = tabela
Proste selekcje
SELECT *
FROM Emp E
WHERE E.Ename=‘KING’
-- lub E.Ename < 'C‘
v
Bez indeksu, nieposortowane: Koszt jest M = #stron
w E.
v
Z indeksem na atrybucie selekcji: Użyj indeksu,
wyznacz pozycje danych, przejdź do rekordów.
– Najlepiej gdy indeks haszowany, selektywny, główny lub
jednoznaczny dla selekcji równościowych oraz indeks
pogrupowany dla selekcji zakresowych.
Użycie indeksu do selekcji
v
v
Gdy indeks niepogrupowany koszt jest
proporcjonalny do liczby znalezionych rekordów
co może być więcej niż M - liczba stron w E.
Ulepszenie dla niepogrupowanych indeksów:
1. Wyznacz odpowiednie pozycje danych.
2. Posortuj je względem rid.
3. Sprowadzaj rekordy w takim porządku. Każda
potrzebna strona zostanie sprowadzona tylko raz.
STRATEGIE: TYLKO-INDEKS,
PERSPEKTYWA ZMATERIALIZOWANA
v
v
v
W szczególnym przypadku gdy wszystkie elementy klauzul
SELECT i WHERE należą do klucza wyszukiwania jednego
indeksu – wystarczy przejść tylko ten indeks.
Czasami warto do klucza wyszukiwania dodać jedno lub
więcej pól aby umożliwić zastosowanie tej metody np. do
indeksu opartego na nazwisku pracownika możemy
rozważyć dodanie zarobków i/albo numeru działu.
Zamiast indeksu można użyć perspektywy
zmaterializowanej, szczególnie wtedy, gdy rozmiar wyniku
jest większy.
– W przeciwieństwie do indeksu stosując perspektywę
zmaterializowaną zachowujemy w niej wszystkie pseudo-wartości
NULL.
PJWSTK, SZB, Lech Banachowski
5
Projekcja
SELECT DISTINCT
FROM
E.Job
Emp E
v
Bez DISTINCT – przepisanie.
v
Z DISTINCT – wymagane jest wyeliminowanie powtórzeń:
– posortowanie;
– haszowanie i eliminacja powtórzeń w ramach segmentów
haszowania;
– gdy atrybuty klauzuli SELECT tworzą indeks – wystarczy przejść
tylko indeks (strategia tylko indeks).
Operatory zbiorowe
Przecięcie i iloczyn kartezjański relacji są
SELECT Deptno FROM
specjalnymi przypadkami złączenia (z
Dept
DISTINCT).
INTERSECT
v
Union Distinct i Except są podobne do
siebie.
v
Posortuj obie relacje (na kombinacji wszystkich
atrybutów).
–
–
Dokonaj odpowiedniego scalenia wyników.
–
Alternatywa: Sortuj od razu razem obie relacje.
–
Zamiast sortowania można użyć haszowania.
SELECT Deptno FROM Dept
EXCEPT
SELECT Deptno FROM Emp;
SELECT Deptno FROM
Emp;
=
SELECT DISTINCT
a.Deptno
FROM Dept a INNER
JOIN Emp b ON
a.Deptno=b.Deptno;
Operacje agregacji (AVG, MIN itd.)
v
Bez grupowania:
–
Na ogół trzeba rozważyć każdy wiersz.
–
Gdy jest indeks, którego klucz wyszukiwania obejmuje wszystkie
atrybuty występujące w klauzulach SELECT i WHERE, wystarczy
przejrzeć indeks (strategia tylko-indeks).
v
Z grupowaniem GROUP BY:
–
Posortuj względem wartości atrybutów GROUP BY, przejdź po
rekordach w każdej grupie licząc wartości funkcji sumarycznych – w
tym celu można użyć pogrupowany indeks na B+ drzewie.
–
Gdy jest indeks, którego klucz wyszukiwania obejmuje wszystkie
atrybuty występujące w klauzulach SELECT, WHERE i GROUP BY,
wystarczy przejrzeć indeks (strategia tylko-indeks).
–
v
Zamiast sortowania można użyć haszowania.
Perspektywa zmaterializowana jak w hurtowni danych.
Strategia tylko-indeks
v
v
v
Jej zastosowanie wymaga aby wszystkie potrzebne
do wyznaczenia wyniku zapytania wiersze tabeli
były indeksowane.
Indeks(Ename, Comm)
SELECT e.Ename, e.Comm SELECT e.Ename
FROM Emp e
FROM Emp e
WHERE e.Comm IS NULL;
ORDER BY e.Ename;
Indeks(Comm)
SELECT e.Comm
SELECT Avg(e.Comm)
FROM Emp e
FROM Emp e;
WHERE e.Comm IS NOT NULL;
PJWSTK, SZB, Lech Banachowski
9
Kolumny INCLUDE (SQL Server)
Ułatwia korzystanie z metody „Tylko indeks”.
Opcja INCLUDE dodaje dodatkowe kolumny do pozycji danych w
indeksie nie połączonym z tabelą.
SELECT Imie, Nazwisko, Data_ur
FROM Osoby
WHERE Nazwisko = 'Kowalski‘
Indeks Osoby(Nazwisko), Kolumny INCLUDE: Imie i Data_ur.
Wyszukiwanie po nazwisku, w pozycji danych odczytanie imienia i
daty urodzenia bez sięgania do stron z rekordami.
CREATE NONCLUSTERED INDEX Osoby_tylko_index
ON Osoby(Nazwisko) INCLUDE (Imie, Data_ur)
PJWSTK, SZB, Lech Banachowski
10
Złączenia równościowe z jedną kolumną
złączenia
SELECT E.Ename, D.Loc
FROM Emp E INNER JOIN Dept D
ON E.Deptno = D.Deptno;
v
v
Bezpośrednie podejście: generuj wszystkie
kombinacje wierszy i stosuj selekcję E.Deptno =
D.Deptno.
M =#stron w E, pE =#wierszy na stronie dla E,
N=#stron w D, pD =#wierszy na stronie dla D.
–
w przykładzie: E – Emp, D – Dept.
Ogólnie kolumny złączenia: Ei w E i Dj w D
Algorytm Nested Loops Join
foreach row e in E do
foreach row d in D do
if ei = dj then add <e, d> to result
v
Dla każdego wiersza zewnętrznej tabeli E, przeglądamy
wszystkie wiersze wewnętrznej tabeli D.
– Koszt (liczba sprowadzanych stron): M + M*N
– Koszt mniejszy gdy M<N.
v
Metoda stosowana:
– gdy M jest niewielkie (np. 2, 3).
– do obliczania iloczynu kartezjańskiego (CROSS JOIN).
Algorytm Index Nested Loops Join
foreach row e in E do
{weź wartość ei kolumny złączenia Ei i poprzez indeks na Dj
wyznacz wszystkie wiersze d w D mające tę samą wartość w
kolumnie złączenia Dj (dj = ei):
• połącz oba takie wiersze <e,d> i
• dodaj <e, d> do obliczanego wyniku}
Algorytm Index Nested Loops Join
Indeks
E
Ei
D
Dj
Koszt: M + (M*pE) * (średni koszt
wyznaczenia pasujących wierszy w D
dla jednego wiersza w E)
–
PJWSTK, SZB, Lech Banachowski
14
Algorytm Index Nested Loops Join
Dla każdego wiersza w E: średni koszt wyszukania pozycji
danych w indeksie dla D jest:
v
v
ok. 1.2 dla indeksu haszowanego;
v
ok. 3 dla B+ drzewa.
Mając znalezioną pozycję danych, średni koszt wyznaczenia
pasujących wierszy w D zależy od rodzaju indeksu:
v
- indeks główny lub jednoznaczny: +1
–
indeks pogrupowany: +1 We/Wy (zwykle)
indeks niepogrupowany: +1 We/Wy dla każdego pasującego wiersza
wD
– może być więcej niż liczba stron w D!
–
PJWSTK, SZB, Lech Banachowski
15
Klaster tabel
Można się lepiej przygotować do często występujących złączeń
tabel przez umieszczenie ich w jednym klastrze z kluczem będącym
kolumną złączenia obu tabel. Wiersze, które są ze sobą złączane,
znajdują się wtedy (zazwyczaj) na tej samej stronie dyskowej.
Połączenie tabel w klaster powoduje, że złączenie odbywa się tak
jakby to była pojedyncza operacja przejścia jednej tabeli.
Realizacja naszego przykładowego zapytania zostanie
przyśpieszona jeśli obie tabele Emp i Dept umieścimy w jednym
klastrze.
Koszt metody jest taki jak koszt przejścia pliku rekordów rozmiaru
N+M.
PJWSTK, SZB, Lech Banachowski
16
Algorytm Sort-Merge Join
v
v
Posortuj D i E na kolumnach złączenia, następnie scal
odpowiadające sobie wiersze w D i E. Przy scalaniu na
ogół każda z posortowanych tabeli D i E jest przeglądana
raz (liniowo).
Koszt algorytmu Sort Merge Join mierzony liczbą operacji
We/Wy jest liniowy = N+M operacji We-Wy – ze
współczynnikiem ok. 5-7.
Algorytm
Hash-Join
Podziel obie tabele E i D na
partycje względem wartości
funkcji haszujacej h1 na
kolumnach złączenia:
wiersze E w partycji i
wystarczy złączyć z
wierszami D w partycji i.
Wczytaj partycję i
tablicy E dokonując
haszowania przy
pomocy f.h. h2 (<> h1!).
Wczytując elementy
partycji i w D, stosuj h2
i uzgadniaj z E.
Oryginalne
tabele
Partycje
1
1
2
2
funkcja
haszująca
...
h1
B-1
B-1
Dysk
B - buforów w RAM
Partycje
E&D
Dysk
Wynik
Tablica haszowana
dla partycji i relacji E
f. h.
h2
h2
Bufor wej. dla
partycji i rel. D
Dysk
Bufor
wyjściowy
B – buforów w RAM
Dysk
Porównanie Sort-Merge Join i Hash Join
–
Obie metody mają porównywalny liniowy koszt.
–
Hash Join lepszy przy większej różnicy rozmiarów; łatwy
do zrównoleglenia; zły w pesymistycznym przypadku –
nie losowym.
–
Sort-Merge mniej wrażliwy na losowość danych; wynik
posortowany.
Złączanie tabel obiektowo-relacyjnych
Przy złączaniu tabel obiektowo-relacyjnych możemy skorzystać z
referencji i kolekcji referencji.
Obie operacje zarówno przejście przez referencję jak i przejście
przez kolekcję referencji są szybsze niż odpowiednie operacje
przejścia przez indeksy rozłączne dla tabel relacyjnych.
Wady referencji i kolekcji referencji:
1. dodatkowy narzut czasowy i miejsca na dysku związany z
reprezentacją i przetwarzaniem referencji i kolekcji,
2. są wolniejsze niż tabele relacyjne z użyciem klastrów i indeksów
połączonych z tabelą.
PJWSTK, SZB, Lech Banachowski
20
Podsumowanie - realizacja
operatorów
v
Zaleta relacyjnych SZBD – zapytania złożone z kilku bazowych
operatorów; implementacje tych operatorów można dokładnie
dostroić.
v
v
Wiele alternatywnych metod implementacyjnych.
Dla konkretnego zapytania dla każdego występującego w nim
operatora trzeba rozważyć dostępne opcje i wybrać najlepszą
korzystając z dostępnych statystyk. Jest to zadanie
optymalizacji zapytania.
Wykonywanie zapytań przez
SZBD
PJWSTK, SZB, Lech Banachowski
22
Optymalizacja zapytań
v
Budowa drzewa zapytania odpowiadającego wyrażeniu
algebry relacji danego zapytania.
v
Plan: Algorytm wykonania zapytania – w postaci
drzewa.
v
–
Dla danego zapytania: jakie plany są rozpatrywane?
–
Jak oszacować koszt planu?
Idealnie: Chcemy znaleźć najlepszy plan.
Praktycznie: Staramy się unikać złych planów!
PJWSTK, SZB, Lech Banachowski
23
Działanie w miejscu
v
v
v
v
Nie korzystanie z tymczasowych tabel. Przy określonym
sposobie dostępu do rekordów każdej tabeli utrzymuje się
tylko kursory przebiegające rekordy w plikach (ewentualnie
pozycje danych w pliku indeksowym) bez zapisywania
pomocniczych tabel.
Metody złączenia Simple Nested Loops Join i Index Nested
Loops Join umożliwiają działanie w miejscu.
Natomiast metody Sort-Merge Join i Hash Join wymagają
użycia pomocniczych plików na dysku, więc nie działają w
miejscu.
Zastosowanie klastra lub kolekcji referencji zamiast
operatora złączenia też umożliwia działanie w miejscu.
PJWSTK, SZB, Lech Banachowski
24
Przetwarzanie potokowe
v
Wynik jednego operatora relacyjnego jest
przekazywany na wejście drugiego
operatora. Oznacza to, że nie jest potrzebna
tymczasowa tabela, więc metoda ta
umożliwia działanie w miejscu.
PJWSTK, SZB, Lech Banachowski
25
Przykład
Drzewo
instrukcji:
SELECT E.Ename
FROM Emp E INNER JOIN Dept D
ON E.Deptno=D.Deptno
WHERE E.Mgr=100 AND D.Loc='Oz';
v
v
v
Plan 1:
Dostęp do tabel: Scan.
Działa w miejscu, przetwarza
potokowo.
Cel optymalizacji: Wyznaczyć inne
bardziej efektywne plany
obliczenia tego samego wyniku.
Scan
PJWSTK, SZB, Lech Banachowski
Scan
26
Plan 2
(bez użycia indeksu
do złączania)
v
v
v
Główna różnica: selekcje wcześniej.
Zamiast SORT-MERGE JOIN mogłoby być HASH JOIN.
Dwie alternatywne metody dostępu do rekordów:
– przez scan całej tabeli;
– przez wyszukiwanie przez indeks odpowiednio na Emp.Mgr i
Dept.Loc.
v
Nie działa w miejscu.
PJWSTK, SZB, Lech Banachowski
27
Plan 3 (z metodą dostępu do
tabel przez indeksy)
Najlepszy byłby indeks pogrupowany lub
selektywny na Emp(Mgr), ewentualnie
indeks haszowany. Dostęp do rekordów w
Emp przez ten indeks.
v
INL bez zapisywania wyniku selekcji jako
tymczasowej relacji - działa w miejscu.
v
Ename
Loc=‘Oz’
(Index Nested Loops)
Deptno=Deptno
(Hash index;
bez temp)
Mgr=100
Dept
Emp
Kolumna złączenia Deptno jest kluczem głównym dla
tabeli Dept. Wystarczy indeks niepogrupowany.
v
PJWSTK, SZB, Lech Banachowski
28
Generowanie przez optymalizator
planów wykonania zapytania
v
Generowanie równoważnych drzew wykonania zapytania.
Analiza złączeń i ograniczenie do: drzew skierowanych w lewo.
– Drzewa skierowane w lewo dają plany umożliwiające
wykonanie zapytania “w miejscu” tj. bez tymczasowych
plików.
– Podstawa: przemienność i łączność operatora złączenia.
D
D
C
A
B
C
D
A
B
C
A
Analiza dostępu do poszczególnych tabel z możliwością
zastowania indeksów.
B
v
PJWSTK, SZB, Lech Banachowski
29
v
v
Faza 1: Generowanie równoważnych drzew wykonania
zapytania.
Faza 2: Analiza metod dostępu do tabel Emp i Dept:
Emp:
Indeks haszowany na Emp.Mgr
Indeks główny B+drzewo na Emp.Empno
Scan
Dept:
Indeks B+ drzewo na Dept.Loc
Scan
Indeks główny haszowany na Dept.Deptno
Faza 3: Rozpatrujemy każde drzewo z Fazy 1 i każdą metodę dostępu z
Fazy 2. Idąc od najniższych poziomów drzewa zapytania wybieramy
metodę realizacji każdego operatora relacyjnego np. dla złączenia: NLJ,
INLJ, SMJ, HJ, liczymy orientacyjny koszt korzystając ze statystyk zebranych
przez system jak liczba wierszy, liczba stron dla plików z danymi i plików
indeksów , selektywność wyszukiwania przez indeks. Powstaje możliwy plan
wykonania zapytania razem z oszacowanym kosztem.
v Faza 4: Spośród wszystkich wygenerowanych planów wykonania
zapytania system wybiera plan o najmniejszym koszcie.
v
PJWSTK, SZB, Lech Banachowski
30
Podzapytania (w tym perspektywy
lokalne inline)
•
Podzapytania są optymalizowane niezależnie.
•
Główne zapytanie jest optymalizowane z branym pod uwagę
kosztem „wywoływanych” podzapytań.
•
Alternatywnie, sprowadzane do złączeń i optymalizowane
łącznie.
PJWSTK, SZB, Lech Banachowski
31
Ogólne strategie optymalizacyjne






•

v
•
Wybierz plan wykonania działający “w miejscu” bez pomocniczych
relacji (drzewa skierowane w lewo).
Wykonuj jednocześnie ciągi operacji jednoargumentowych takich jak
selekcje i rzuty (realizacja potokowa).
Sprowadzaj przetwarzanie do operacji w pamięci RAM np. przez
dokonanie jak najwcześniej selekcji, istotnie ograniczającej liczbę
wierszy.
Gdy złączenia bez operatorów JOIN, staraj się związać selekcje z iloczynem
kartezjańskim, w celu zidentyfikowania rodzaju złączenia relacji.
Wyszukuj wspólne podwyrażenia i wykonuj je tylko raz.
Przetwórz wstępnie plik (posortuj, wykonaj haszowanie).
Cały czas gromadź statystyki ilościowe dotyczące tabel, kolumn i
indeksów, jak liczba stron, liczba różnych wartości w kolumnie.
Dokonaj analizy możliwych opcji z oszacowaniem ich kosztu.
Rozważ możliwość użycia indeksów w tym bitmapowych.
Stosuj:
• Klaster.
• Strategię tylko indeks.
• Perspektywę zmaterializowaną.
PJWSTK, SZB, Lech Banachowski
32