Systemy zarządzania bazami danych

Systemy zarządzania bazami danych

15. Strojenie indeksów

Indeks

• Indeks to struktura danych zapewniająca szybki dostęp do danych

Zbiór rekordów indeks

Warunek na wartości atrybutu

Pasujące rekordy 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 balance FROM accounts

WHERE number = 1023;

2. Zapytanie wielopunktowe

SELECT balance FROM accounts

WHERE branchnum = 100;

3. Zapytanie zakresowe

SELECT number FROM accounts

WHERE balance > 10000;

4. Zapytanie przedrostkowe

SELECT *

FROM employees

WHERE name = ‘Jensen’

and firstname = ‘Carl’

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;

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;

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)

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 120 83 92 95

75 80 81 33 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

2 4 4

A

B C

D

E F

zamek T1

zamek T1

zamek T1

Zamki na B+ drzewach

Indeks haszowany

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

Zhaszowany

klucz Wartości

0 1

n

R1 R5

R3 R6 R9 R14 R17 R21 R25 Funkcja

haszują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 S2 Si ^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 pokryte

select ssnum, name, lat from employees;

• Zapytanie zakresowe

select * from employees where long between ? and ?;

• Zapytanie punktowe

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

Throughput ratio

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

Throughput (queries/sec)

scan

non clustering

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

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

Throughput (queries/sec) cov e ring cov e ring - not orde re d

non 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

SQLServ er

0 20 40 60 80 100

% Increase in Table Size Throughput (queries/sec)

No maintenance Maintenance

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

0 10 20 30 40 50

0 20 40 60 80 100

% Increase in T able Size Throughput (queries/sec)

No maintenance Maintenance

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 Throughput (queries/sec)

No

maintenance

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

0 2 4 6 8 10 12 14 16 18

no index index

Throughput (updates/sec)

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.

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

Throughput (queries/sec)

• Indeks haszowany nie pomaga w zapytaniach zakresowych

• Indeks haszowany pokonuje B+drzewo przy zapytaniach

punktowych

Range Q ue ries

0 0.1 0.2 0.3 0.4 0.5

B-T re e Hash index Bitmap inde x

Throughput (queries/sec)

B+drzewo, hasz, bitmapa

Point Q ue rie s

0 10 20 30 40 50 60

B-T re e hash inde x

Throughput(queries/sec)

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

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

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