Systemy zarzdzania bazami danych 15 Strojenie indeksw Orygina
Systemy zarządzania bazami danych 15. Strojenie indeksów Oryginał: Shasha & Bonnet 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. Zapytanie zakresowe SELECT balance FROM accounts WHERE number = 1023; SELECT number FROM accounts WHERE balance > 10000; 2. Zapytanie wielopunktowe 4. Zapytanie przedrostkowe SELECT balance FROM accounts WHERE branchnum = 100; Oryginał: Shasha & Bonnet 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 i 1 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, My. SQL): 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 DB 2) • • 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 T 1 B zamek T 1 D E Oryginał: Shasha & Bonnet C F 15. Strojenie indeksów 12
Indeks haszowany • Indeks haszowany przechowuje pary kluczwartość korzystąc z pseudo-ranomizującej funkcji zwanej funkcją haszującą klucz 2341 Zhaszowany Wartości klucz Funkcja haszująca 0 1 R 5 R 3 R 6 R 9 n Oryginał: Shasha & Bonnet 15. Strojenie indeksów R 14 R 17 R 21 R 25 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 S 1 S 2 Oryginał: Shasha & Bonnet – Wskaźniki wskazują rekordy – Indeksy niepogrupowane muszą być gęste 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 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 • DB 2 – B+drzewo, rozszerzenie przestrzenne: R-drzewo – Indeksy pogrupowane są gęste – Jawne polecenie reorganizacji indeksu Oryginał: Shasha & Bonnet • My. SQL – 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 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, hundreds 1, hundreds 2); clustered index c on employees(hundreds 1) with fillfactor=100; nonclustered index nc on employees (hundreds 2); nonclustered index nc 3 on employees (ssnum, name, lat); nonclustered index nc 4 on employees (lat, ssnum, name); • 1000000 wierszy; Pusty (zimny) bufor • Dual Xeon (550 MHz, 512 Kb), 1 Gb RAM, sterownik RAID Adaptec (80 Mb), dyski 4 x 18 Gb (10000 RPM), 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, 'polo 94064', 97. 48, 84. 03, 4700. 55, 3987. 2); • Zapytanie wielopunktowe: select * from employees where name = ? ; select * from employees where hundreds 1= ? ; select * from employees where hundreds 2= ? ; • 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 • 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 Oryginał: Shasha & Bonnet 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
Odczyt pełny może być lepszy • IBM DB 2 v 7. 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) Oryginał: Shasha & Bonnet 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
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 Oryginał: Shasha & Bonnet 15. Strojenie indeksów 29
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 Oryginał: Shasha & Bonnet 15. Strojenie indeksów 30
Pielęgnacja indeksu – DB 2 • 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 Oryginał: Shasha & Bonnet 15. Strojenie indeksów 31
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 Oryginał: Shasha & Bonnet 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
Indeks na małej tabeli – pomiary • Mała tabela: 100 rekordów • Dwa współbieżne procesy modyfikują dane (każdy działa 10 ms 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 Oryginał: Shasha & Bonnet 15. Strojenie indeksów 34
B+drzewo, hasz, bitmapa – dane employees(ssnum, name, lat, long, hundreds 1, hundreds 2); create cluster c_hundreds (hundreds 2 number(8)) PCTFREE 0; create cluster c_ssnum(ssnum integer) PCTFREE 0 size 60; create cluster c_hundreds(hundreds 2 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 (hundreds 2); create bitmap index b 2 on employees (ssnum); • 1000000 wierszy; Pusty (zimny) bufor • Dual Xeon (550 MHz, 512 Kb), 1 Gb RAM, sterownik RAID Adaptec (80 Mb), dyski 4 x 18 Gb (10000 RPM), Windows 2000. Oryginał: Shasha & Bonnet 15. Strojenie indeksów 35
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” Oryginał: Shasha & Bonnet 15. Strojenie indeksów 36
B+drzewo, hasz, bitmapa • Indeks haszowany nie pomaga w zapytaniach zakresowych • Indeks haszowany pokonuje B+drzewo przy zapytaniach punktowych Oryginał: Shasha & Bonnet 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
- Slides: 40