wyklad15

Systemy zarządzania bazami
danych
15. Strojenie indeksów
Indeks
• Indeks to struktura danych zapewniająca
szybki dostęp do danych
klucz
wyszukiwania
Warunek
na
wartości
atrybutu
indeks
Oryginał: Shasha & Bonnet
Zbiór
rekordów
15. Strojenie indeksów
Pasujące
rekordy
2
Zagadnienia wydajnościowe
•
•
•
•
•
•
Rodzaj zapytania
Struktura danych indeksu
Organizacja danych na dysku
Narzuty powodowane przez indeks
Rozproszenie danych
Pokrycie (strategia tylko-indeks)
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
3
Klasyfikacja zapytań
1.
Zapytanie punktowe
3.
SELECT balance
FROM accounts
WHERE number = 1023;
2. Zapytanie wielopunktowe
SELECT balance
FROM accounts
WHERE branchnum = 100;
Oryginał: Shasha & Bonnet
Zapytanie zakresowe
SELECT number
FROM accounts
WHERE balance > 10000;
4.
Zapytanie przedrostkowe
SELECT *
FROM employees
WHERE name = ‘Jensen’
and firstname = ‘Carl’
and age < 30;
15. Strojenie indeksów
4
Klasyfikacja zapytań c.d.
5.
Zapytanie ekstremalne
SELECT *
FROM accounts
WHERE balance =
(select max(balance) from accounts)
6.
Zapytanie sortujące
SELECT *
FROM accounts
ORDER BY balance;
Oryginał: Shasha & Bonnet
7.
Zapytanie grupujące
SELECT branchnum, avg(balance)
FROM accounts
GROUP BY branchnum;
8.
Złączenie
SELECT distinct branch.adresse
FROM accounts, branch
WHERE
accounts.branchnum =
branch.number
and accounts.balance > 10000;
15. Strojenie indeksów
5
Klucze wyszukiwania
• Klucz (wyszukiwania) to ciąg atrybutów
create index i1 on accounts(branchnum, balance);
• Rodzaje indeksów
– Sekwencyjny: wartości klucza są monotoniczne
względem kolejności wstawiania (np. licznik
lub stempel czasowy)
– Niesekwencyjne: wartości klucza nie mają
związku z kolejnością wstawiania (np. NIP, bo
PESEL już mniej)
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
6
Struktury danych
• Większość indeksowych struktur danych to
drzewa
• Zwykle, korzeń takiego drzewa jest zawsze w
pamięci RAM, podczas gdy liście znajdują się
na dysku
– Wydajność struktury danych zależy od średniej
długości ścieżki od liścia do korzenia
– Struktury danych o dużym rozgałęzieniu są więc
preferowane
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
7
B+ drzewo
• B+ drzewo to drzewo zrównoważone,
którego liście zawierają ciągi par kluczwskaźnik
96
75 83
33 48 69
75 80 81
Oryginał: Shasha & Bonnet
107
83 92 95
96 98 103
15. Strojenie indeksów
107 110 120
8
Wydajność B+ drzew
• Liczba poziomów drzewa
– Rozgałęzienie drzewa
• Wielkość klucza
• Wykorzystanie stron
• Pielęgnacja drzewa
– Na bieżąco
• Przy wstawieniach
• Przy usunięciach
– Manualna
• Zamki na węzłach drzewa
• Korzeń drzewa w pamięci
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
9
Rozmiar klucza
• Rozmiar klucza wpływa na rozgałęzienie
• Tworząc indeks lepiej wybrać mniejszy klucz
• Kompresja klucza
– Kompresja przedrostkowa (Oracle 8, MySQL):
przechowuj tylko tę część klucza odróżniającą go od
sąsiadów: Smi, Smo, Smy zamiast Smith, Smoot, Smythe
– Kompresja wspólnego przedrostka (Oracle 5): podobny
przedrostek sąsiednich kluczy jest wydzielony, np. Smi,
(2)o, (2)y. Są pewne wady:
• Procesor obciążony pielęgnacją tej struktury
• Zamek na Smoot wymaga też zamka na Smith
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
10
Zamki na B+ drzewie
• Przeszukiwanie drzewa
– Modyfikacja, odczyt
– Wstawieniem usunięcie
• Problem fantomów: potrzebne są zamki zakresowe
• ARIES KVL (zaimplementowane w DB2)
•
•
•
•
Przejście po drzewie
Zamki na krotkach
Zamki na kluczach
Zamki na zakresach
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
2
4
4
11
Zamki na B+ drzewach
A
zamek T1
B
zamek T1
C
zamek T1
D
E
Oryginał: Shasha & Bonnet
F
15. Strojenie indeksów
12
Indeks haszowany
• Indeks haszowany przechowuje pary kluczwartość korzystąc z pseudo-ranomizującej
funkcji zwanej funkcją haszującą
Zhaszowany
Wartości
klucz
klucz
2341
Funkcja
haszująca
0
1
R1 R5
R3 R6 R9
n
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
R14 R17 R21
R25
Długość tych
łańcuchów wpływa
na wydajność
13
Pogrupowany i niepogrupowany
• Indeks pogrupowany
(indeks główny)
• Indeks niepogrupowany
(indeks drugorzędny)
– Atrybuty o bliskich wartości
klucza są sobie bliskie
fizycznie na dysku
– Tabela może mieć tylko
jeden taki indeks
Rekordy
Oryginał: Shasha & Bonnet
– Indeks niepogrupowany nie
ogranicza fizycznej organizacji
tabeli
– Tabela może mieć wiele takich
indeksów
Rekordy
15. Strojenie indeksów
14
Gęsty i rzadki
• Indeks rzadki
• Indeks gęsty
– Wskaźniki wskazują strony
– Indeksy pogrupowane mogą
być rzadkie
S1
S2
Oryginał: Shasha & Bonnet
– Wskaźniki wskazują
rekordy
– Indeksy niepogrupowane
muszą być gęste
rekord
Si
rekord
15. Strojenie indeksów
rekord
15
Więzy a indeksy
• Klucz główny, klucz alternatywny
– Unikatowy indeks niepogrupowany jest
tworzony na atrybutach składających się na
klucz
• Klucz obcy
– Domyślnie nie tworzy się indeksu
wymuszającego więzy klucza obcego
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
16
Implementacja indeksów w SZBD
• Oracle
• SQL Server
– B+drzewo
– Indeksy pogrupowane są
rzadkie
– Pielęgnacja indeksu przy
modyfikacjach, wstawieniach,
usnięciach
– B+drzewo, haszowe, bitmapowe,
R-drzewa
– Brak indeksu pogrupowanego
• Tabela organizowana indeksem
(unikatowym/pogrupowanym)
• Klastry (grona) tworzone razem z
tabelami
• DB2
– B+drzewo, rozszerzenie
przestrzenne: R-drzewo
– Indeksy pogrupowane są gęste
– Jawne polecenie reorganizacji
indeksu
Oryginał: Shasha & Bonnet
• MySQL
– B+drzewo, R-drzewo
– Pielęgnacja indeksu przy
modyfikacjach, wstawieniach,
usnięciach
15. Strojenie indeksów
17
Regulatory indeksu
•
•
•
•
•
Struktura danych
Klucz wyszukiwania
Rozmiar klucza
Pogrupowany/Niepogrupowany/Bez indeksu
Pokrycie (strategia „tylko-indeks”)
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
18
Indeks pogrupowany – korzyści
• Indeks rzadki (więc pogrupowany) przechowuje
mniej wskaźników niż indeks gesty
• Może to oszczędzić nam jednego poziomu B+drzewa
• Indeks pogrupowany jest dobry do zapytań
wielopunktowych (zwłaszcza mało selektywnych)
• Białe strony strony książki telefonicznej
• Indeks pogrupowany na B+drzewie dobrze
wspomaga zapytania zakresowe, przedrostkowe,
ekstremalne i sortujące
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
19
Indeks pogrupowany – korzyści, c.d.
• Indeks pogrupowany (na atrybucie X)
może zmniejszyć rywalizację o zamki
• Pobranie lub modyfikacja z użyciem
warunku równościowego, zakresowego
lub przedrostkowego wymaga dostępu
do i zamków tylko na kilku sąsiednich
fizycznie stronach
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
20
Indeks pogrupowany – koszty
• Strony nadmiarowe
• Wynik wstawień
• Wynik modyfikacji powiększających rekordy
(np., NULL zastąpiony długim napisem)
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
21
Indeks pogrupowany – jedyność
• Na tabeli może być tylko jeden indeks
pogrupowany
• Powielenie tabeli może być dobrym pomysłem,
jeśli jego celem jest użycie dwóch różnych
indeksów pogrupowanych na tej tabeli
• Takim powieleniem są żółte strony książki telefonicznej
• Powielenie będzie miało sens tylko jeśli na danej tabeli
liczba wstawień i modyfikacji jest niska
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
22
Strojenie indeksów – środowisko
employees(ssnum, name, lat, long, hundreds1, hundreds2);
clustered index c on employees(hundreds1) with fillfactor=100;
nonclustered index nc on employees (hundreds2);
nonclustered index nc3 on employees (ssnum, name, lat);
nonclustered index nc4 on employees (lat, ssnum, name);
• 1000000 wierszy; Pusty (zimny) bufor
• Dual Xeon (550MHz,512Kb), 1Gb RAM, sterownik RAID
Adaptec (80Mb), dyski 4x18Gb (10000RPM), Windows 2000.
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
23
Strojenie indeksów – operacje
• Modyfikacja
update employees set name = ‘XXX’ where ssnum=?;
• Wstawienie
insert into employees values
(1003505,'polo94064',97.48,84.03,4700.55,3987.2);
• Zapytanie wielopunktowe:
select * from employees where name = ?;
select * from employees where hundreds1= ?;
select * from employees where hundreds2= ?;
• Zapytanie pokryte
select ssnum, name, lat from employees;
• Zapytanie zakresowe
select * from employees where long between ? and ?;
• Zapytanie punktowe
select * from employees where ssnum = ?
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
24
Indeks pogrupowany – pomiary
clustered
nonclustered
no index
Throughput ratio
1
0.8
0.6
0.4
0.2
0
SQLServer
Oracle
Oryginał: Shasha & Bonnet
DB2
• Zapytanie wielopunktowe
zwracające 100 rekordów
spośród 1000000.
• Pusty bufor
• Odczyt z indeksu
pogrupowanego jest co
najmniej dwa razy szybszy
niż z niepogrupowanego i o
rzędy wielkości szybszy niż
przegląd pełny
15. Strojenie indeksów
25
Indeks niepogrupowany – korzyści
• Indeks gęsty może wyeliminować konieczność
odczytu tabeli dzięki pokryciu („tylko-indeks”)
• Może być warto stworzyć więcej indeksów po to, aby dać
optymalizatorowi więcej możliwości użycia strategii „tylko-indeks”
• Indeks niepogrupowany jest dobry, gdy używające
go zapytania zwracają znacznie mniej rekordów niż
jest stron w tabeli
• Zapytania punktowe
• Zapytania wielopunktowe, pod warunkiem, że
liczba różnych wartości klucza wyszukiwania >
liczba stron prefetch * liczba rekordów na stronie
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
26
Throughput (queries/sec)
Odczyt pełny może być lepszy
scan
non clustering
0
5
10
15
% of selected records
Oryginał: Shasha & Bonnet
20
25
• IBM DB2 v7.1, Windows 2000
• Zapytanie zakresowe
• Gdy zapytanie zwraca co
najmniej 10% rekordów, odczyt
pełny jest często lepszy niż
użycie indeksu
niepogrupowanego
(niepokrywającego)
• Punkt przecięcia > 10%, gdy
rekordy są duże lub tabela jest
pofragmentowana na dysku
(wtedy odczyt pełny drożeje)
15. Strojenie indeksów
27
Indeks pokrywający
SELECT name FROM employee
WHERE department = “marketing”
• Dobry indeks pokrywający to (department,
name)
• Indeks (name, department) mniej użyteczny
• Indeks na samym (department)
umiarkowanie użyteczny
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
28
Throughput (queries/sec)
Indeks pokrywający – pomiary
70
60
covering
50
covering - not
ordered
non clustering
40
30
20
clustering
10
0
• Indeks pokrywający działa
szybciej niż pogrupowany,
gdy jego pierwsze
atrybuty są użyte w
klauzuli WHERE, a
ostatnie w SELECT
• Gdy atrybuty są w
indeksie w złym porządku,
wydajność znacznie spada
SQLServer
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
29
Pielęgnacja indeksu – SQL Server
Throughput
(queries/sec)
SQLServer
No maintenance
Maintenance
0
20
40
60
% Increase in Table Size
Oryginał: Shasha & Bonnet
80
100
• Indeks stworzony z
parametrem fillfactor = 100.
• Wstawienia powodują
podział stron i dodatkowe
operacje I/O przy każdym
zapytaniu
• Pielęgnacja polega na
stworzeniu indeksu na nowo
• Z pielęgnacją wydajność jest
stała, a bez niej spada bardzo
wyraźnie
15. Strojenie indeksów
30
Pielęgnacja indeksu – DB2
DB2
Throughput
(queries/sec)
50
40
30
20
No maintenance
Maintenance
10
0
0
20
40
60
% Increase in Table Size
Oryginał: Shasha & Bonnet
80
100
• Indeks utworzony z
parametrem pctfree = 0
• Wstawienia powodują
dodawanie rekordów na
końcu tabeli
• Każde zapytanie
przechodzi przez indeks i
czyta ogon tabeli
• Wydajność powoli spada,
gdy nie ma pielęgnacji
15. Strojenie indeksów
31
Pielęgnacja indeksu – Oracle
Throughput
(queries/sec)
Oracle
No
maintenance
0
20
40
60
80
% Increase in Table Size
Oryginał: Shasha & Bonnet
100
• W Oracle, indeks
pogrupowany można
symulować poprzez indeks na
tabeli w klastrze
• Brak automatycznej fizycznej
reorganizacji
• Indeks utworzony z
parametrem pctfree = 0
• Strony nadmiarowe powodują
wyraźny spadek wydajności
15. Strojenie indeksów
32
Indeks na małej tabeli
• Podręczniki strojenia zalecają, by na małych
tabelach unikać indeksów
– Jeśli wszystkie dane z relacji mieszczą się na
jednej stronie, wszelkie dodatkowe struktury (np.
indeksy) powodują dodatkowe operacje I/O
– Jeśli każdy rekord mieści się na stronie, indeks
jednak poprawia wydajność
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
33
Throughput (updates/sec)
Indeks na małej tabeli – pomiary
18
16
14
12
10
8
6
4
2
0
no index
Oryginał: Shasha & Bonnet
index
• Mała tabela: 100 rekordów
• Dwa współbieżne procesy
modyfikują dane (każdy
działa 10ms zanim
zatwierdzi)
• Bez indeksu: każda
modyfikacja wymaga
odczytu pełnego. Brak
współbieżnych modyfikacji
• Indeks pogrupowany
pozwala na skorzystanie z
zamków na poziomie
wierszy
15. Strojenie indeksów
34
B+drzewo, hasz, bitmapa – dane
employees(ssnum, name, lat, long, hundreds1, hundreds2);
create cluster c_hundreds (hundreds2 number(8)) PCTFREE 0;
create cluster c_ssnum(ssnum integer) PCTFREE 0 size 60;
create cluster c_hundreds(hundreds2 number(8)) PCTFREE 0
HASHKEYS 1000 size 600;
create cluster c_ssnum(ssnum integer) PCTFREE 0 HASHKEYS
1000000 SIZE 60;
create bitmap index b on employees (hundreds2);
create bitmap index b2 on employees (ssnum);
• 1000000 wierszy; Pusty (zimny) bufor
• Dual Xeon (550MHz,512Kb), 1Gb RAM, sterownik RAID Adaptec
(80Mb), dyski 4x18Gb (10000RPM), Windows 2000.
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
35
Zapytania wielopunktowe:
B+drzewo, hasz, bitmapa
Throughput (queries/sec)
Multipoint Queries
25
20
15
10
5
0
B-Tree
Hash index
Oryginał: Shasha & Bonnet
Bitmap index
• W indeksie haszowanym są
łańcuchy stron nadmiarowych
• W pogrupowanym B+drzewie
rekordy są na kolejnych
stronach (ciągła alokacja)
• Indeks bitmapowy jest
proporcjonalny względem
rozmiaru tabeli i odczytuje
rekordy w sposób
„niepogrupowany”
15. Strojenie indeksów
36
B+drzewo, hasz, bitmapa
Throughput (queries/sec)
Range Queries
• Indeks haszowany nie
pomaga w zapytaniach
zakresowych
0.5
0.4
0.3
0.2
0.1
0
B-Tree
Hash index
Bitmap index
Throughput(queries/sec)
Point Queries
• Indeks haszowany
pokonuje B+drzewo
przy zapytaniach
punktowych
60
50
40
30
20
10
0
B-Tree
Oryginał: Shasha & Bonnet
hash index
15. Strojenie indeksów
37
Kompresja kluczy
• Używaj kompresji kluczy, gdy
– Używasz B+drzewa
– Kompresja kluczy zmniejszy liczbę poziomów
B+drzewa
– System nie ma dociążonego procesora (tzn. nie
procesor jest najbardziej obciążonym zasobem)
– Modyfikacje danych są stosunkowo rzadkie
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
38
Podsumowanie
1. Indeks haszowany nadaje się tylko do zapytań
punktowych. Dla zapytań wielopunktowych i
zakresowych lepsze są B+drzewa.
2. Indeks pogrupowany ma być, gdy:
•
•
Zapytania odwołują się do większość pól każdego
zwróconego rekordu
Dużo jest zapytań zakresowych i wielopunktowych
3. Pokryj kluczowe zapytania indeksem gęstym
4. Nie zakładaj indeksu, gdy dodatkowy czas
potrzebny na obsługę wstawień i modyfikacji
jest większy niż oszczędności przy zapytaniach
Oryginał: Shasha & Bonnet
15. Strojenie indeksów
39
Kreator indeksów MS SQL
• MS SQL Server od 7
• Dane wejściowe
– Baza danych (schemat +
dane + istniejące indeksy)
– Reprezentatywny fragment
śladu obciążenia
• Dane wyjściowe
– Ocena istniejących
indeksów
– Zalecenia dodania lub
usunięcia indeksów
Oryginał: Shasha & Bonnet
• Czynności
– Wyliczenie możliwych
indeksów na jednym
atrybucie i na wielu
atrybutach
– Przejście tej przestrzeni
wyszukiwania wraz z
optymalizatorem, aby
każdemu indeksowi
przypisać koszt
15. Strojenie indeksów
40