Systemy zarządzania bazami danych

Systemy zarządzania bazami danych

15. Strojenie indeksów

Oryginał: Shasha & Bonnet 215. Strojenie indeksów

Indeks• Indeks to struktura danych zapewniająca

szybki dostęp do danych

Zbiórrekordów

indeksWarunek

nawartościatrybutu

Pasującerekordy

klucz wyszukiwania


Zagadnienia wydajnościowe

• Rodzaj zapytania• Struktura danych indeksu• Organizacja danych na dysku• Narzuty powodowane przez indeks• Rozproszenie danych• Pokrycie (strategia tylko-indeks)


Klasyfikacja zapytań1. Zapytanie punktowe

SELECT balanceFROM accountsWHERE number = 1023;

2. Zapytanie wielopunktowe

SELECT balanceFROM accountsWHERE branchnum = 100;

3. Zapytanie zakresowe

SELECT numberFROM accountsWHERE balance > 10000;

4. Zapytanie przedrostkowe

SELECT *FROM employeesWHERE name = ‘Jensen’

and firstname = ‘Carl’ and age < 30;


Klasyfikacja zapytań c.d.

5. Zapytanie ekstremalne

SELECT *FROM accountsWHERE balance = (select max(balance) from accounts)

6. Zapytanie sortujące

SELECT *FROM accountsORDER BY balance;

7. Zapytanie grupujące

SELECT branchnum, avg(balance)FROM accountsGROUP BY branchnum;

8. Złączenie

SELECT distinct branch.adresseFROM accounts, branchWHERE accounts.branchnum =

branch.numberand accounts.balance > 10000;


Klucze wyszukiwania

• Klucz (wyszukiwania) to ciąg atrybutówcreate 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)


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


B+ drzewo

• B+ drzewo to drzewo zrównoważone, którego liście zawierają ciągi par klucz-wskaźnik

96

75 83 107

96 98 103 107 110 12083 92 9575 80 8133 48 69


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


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


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

42 4


A

B C

D

E F

zamek T1

zamek T1zamek T1

Zamki na B+ drzewach


Indeks haszowany

• Indeks haszowany przechowuje pary klucz-wartość korzystąc z pseudo-ranomizującej funkcji zwanej funkcją haszującą

Zhaszowanyklucz Wartości

01

n

R1 R5R3 R6 R9 R14 R17 R21 R25

Funkcjahaszująca

klucz

2341

Długość tych łańcuchów wpływa na wydajność


Pogrupowany i niepogrupowany• Indeks pogrupowany

(indeks główny)– Atrybuty o bliskich wartości

klucza są sobie bliskie fizycznie na dysku

– Tabela może mieć tylko jeden taki indeks

• Indeks niepogrupowany (indeks drugorzędny)– Indeks niepogrupowany nie

ogranicza fizycznej organizacji tabeli

– Tabela może mieć wiele takich indeksów

Rekordy Rekordy


Gęsty i rzadki

• Indeks rzadki– Wskaźniki wskazują strony– Indeksy pogrupowane

mogą być rzadkie

• Indeks gęsty– Wskaźniki wskazują

rekordy– Indeksy niepogrupowane

muszą być gęste

S1 SiS2 rekord

rekord rekord


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


Implementacja indeksów w SZBD• SQL Server

– B+drzewo– Indeksy pogrupowane są rzadkie– Pielęgnacja indeksu przy

modyfikacjach, wstawieniach, usnięciach

• DB2– B+drzewo, rozszerzenie

przestrzenne: R-drzewo– Indeksy pogrupowane są gęste– Jawne polecenie reorganizacji

indeksu

• Oracle– B+drzewo, haszowe, bitmapowe,

R-drzewa– Brak indeksu pogrupowanego

• Tabela organizowana indeksem (unikatowym/pogrupowanym)

• Klastry (grona) tworzone razem z tabelami

• MySQL– B+drzewo, R-drzewo– Pielęgnacja indeksu przy

modyfikacjach, wstawieniach, usnięciach


Regulatory indeksu

• Struktura danych• Klucz wyszukiwania • Rozmiar klucza• Pogrupowany/Niepogrupowany/Bez indeksu• Pokrycie (strategia „tylko-indeks”)


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


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


Indeks pogrupowany – koszty

• Strony nadmiarowe• Wynik wstawień• Wynik modyfikacji powiększających rekordy

(np., NULL zastąpiony długim napisem)


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


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.


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 pokryteselect ssnum, name, lat from employees;

• Zapytanie zakresowe select * from employees where long between ? and ?;

• Zapytanie punktoweselect * from employees where ssnum = ?


Indeks pogrupowany – pomiary

• 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

0

0.2

0.4

0.6

0.8

1

SQLServer Oracle DB2

Thro

ughp

ut r

atio

clustered nonclustered no index


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


Odczyt pełny może być lepszy• 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)

0 5 10 15 20 25% of se le cte d re cords

Thr

ough

pu

t (q

uer

ies/

sec)

scannon clustering


Indeks pokrywającySELECT name FROM employeeWHERE department = “marketing”

• Dobry indeks pokrywający to (department, name)

• Indeks (name, department) mniej użyteczny• Indeks na samym (department)

umiarkowanie użyteczny


Indeks pokrywający – pomiary • 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

0

10

20

30

40

50

60

70

SQLSe rv e r

Thro

ugh

put (

que

ries

/sec

) cov e ring

cov e ring - notorde re dnon cluste ring

cluste ring


Pielęgnacja indeksu – SQL Server

• 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

SQLServer

0 20 40 60 80 100

% Increase in Table Size

Thro

ughp

ut

(que

ries/

sec)

No maintenanceMaintenance


Pielęgnacja indeksu – DB2

• 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

DB2

01020304050

0 20 40 60 80 100

% Increase in Table Size

Thro

ughp

ut

(que

ries

/sec

)

No maintenanceMaintenance


Pielęgnacja indeksu – Oracle

• 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

Oracle

0 20 40 60 80 100% Increase in Table Size

Thro

ughp

ut

(que

ries/

sec)

Nomaintenance


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ść


Indeks na małej tabeli – pomiary• 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

02468

1012141618

no index index

Thro

ughp

ut (u

pdat

es/s

ec)


B+drzewo, hasz, bitmapa – daneemployees(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.


Zapytania wielopunktowe: B+drzewo, hasz, bitmapa

• 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”

Multipoint Queries

0

5

10

15

20

25

B-Tree Hash index Bitmap index

Thro

ughp

ut (q

uerie

s/se

c)


• Indeks haszowany nie pomaga w zapytaniach zakresowych

• Indeks haszowany pokonuje B+drzewo przy zapytaniach punktowych

Range Queries

0

0.1

0.2

0.3

0.4

0.5

B-Tree Hash index Bitmap index

Thro

ughp

ut (q

ueri

es/s

ec)

B+drzewo, hasz, bitmapa

Point Queries

0

10

20

30

40

50

60

B-Tree hash index

Thro

ughp

ut(q

ueri

es/s

ec)


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


Podsumowanie1. 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ęstym4. Nie zakładaj indeksu, gdy dodatkowy czas

potrzebny na obsługę wstawień i modyfikacji jest większy niż oszczędności przy zapytaniach


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

• 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

Systemy zarządzania bazami danych

Documents

Transcript of Systemy zarządzania bazami danych