1 / 40

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. klucz wyszukiwania. Zbiór rekordów. Pasujące rekordy. Warunek na wartości atrybutu. indeks. Zagadnienia wydajnościowe. Rodzaj zapytania

hasad
Download Presentation

Systemy zarządzania bazami danych

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Systemy zarządzania bazami danych 15. Strojenie indeksów

  2. Indeks • Indeks to struktura danych zapewniająca szybki dostęp do danych klucz wyszukiwania Zbiór rekordów Pasującerekordy Waruneknawartościatrybutu indeks 15. Strojenie indeksów

  3. Zagadnienia wydajnościowe • Rodzaj zapytania • Struktura danych indeksu • Organizacja danych na dysku • Narzuty powodowane przez indeks • Rozproszenie danych • Pokrycie (strategia tylko-indeks) 15. Strojenie indeksów

  4. Zapytanie punktoweSELECT balanceFROM accountsWHERE number = 1023; Zapytanie wielopunktoweSELECT balanceFROM accountsWHERE branchnum = 100; Zapytanie zakresoweSELECT numberFROM accountsWHERE balance > 10000; Zapytanie przedrostkoweSELECT *FROM employeesWHERE name = ‘Jensen’ and firstname = ‘Carl’ and age < 30; Klasyfikacja zapytań 15. Strojenie indeksów

  5. Zapytanie ekstremalneSELECT *FROM accountsWHERE balance = (select max(balance) from accounts) Zapytanie sortująceSELECT *FROM accountsORDER BY balance; Zapytanie grupująceSELECT branchnum, avg(balance)FROM accountsGROUP BY branchnum; ZłączenieSELECT distinct branch.adresseFROM accounts, branchWHERE accounts.branchnum = branch.numberand accounts.balance > 10000; Klasyfikacja zapytań c.d. 15. Strojenie indeksów

  6. 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) 15. Strojenie indeksów

  7. 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 15. Strojenie indeksów

  8. B+ drzewo • B+ drzewo to drzewo zrównoważone, którego liście zawierają ciągi par klucz-wskaźnik 96 75 83 107 33 48 69 75 80 81 83 92 95 96 98 103 107 110 120 15. Strojenie indeksów

  9. 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 15. Strojenie indeksów

  10. 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, Smyzamiast 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 15. Strojenie indeksów

  11. 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 4 2 4 15. Strojenie indeksów

  12. zamek T1 zamek T1 zamek T1 Zamki na B+ drzewach A B C D E F 15. Strojenie indeksów

  13. Zhaszowany klucz Wartości klucz R1 R5 Funkcjahaszująca 0 1 R3 R6 R9 R14 R17 R21 R25 2341 n Indeks haszowany • Indeks haszowany przechowuje pary klucz-wartość korzystąc z pseudo-ranomizującej funkcji zwanej funkcją haszującą Długość tych łańcuchów wpływa na wydajność 15. Strojenie indeksów

  14. 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 Pogrupowany i niepogrupowany Rekordy Rekordy 15. Strojenie indeksów

  15. Indeks rzadki Wskaźniki wskazują strony Indeksy pogrupowane mogą być rzadkie Indeks gęsty Wskaźniki wskazują rekordy Indeksy niepogrupowane muszą być gęste Gęsty i rzadki S1 S2 Si rekord rekord rekord 15. Strojenie indeksów

  16. 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 15. Strojenie indeksów

  17. 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 Implementacja indeksów w SZBD 15. Strojenie indeksów

  18. Regulatory indeksu • Struktura danych • Klucz wyszukiwania • Rozmiar klucza • Pogrupowany/Niepogrupowany/Bez indeksu • Pokrycie (strategia „tylko-indeks”) 15. Strojenie indeksów

  19. 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 15. Strojenie indeksów

  20. Indeks pogrupowany – korzyści, c.d. • Indeks pogrupowany (naatrybucie 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 15. Strojenie indeksów

  21. Indeks pogrupowany – koszty • Strony nadmiarowe • Wynik wstawień • Wynik modyfikacji powiększających rekordy (np., NULL zastąpiony długim napisem) 15. Strojenie indeksów

  22. 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 15. Strojenie indeksów

  23. 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 – środowisko 15. Strojenie indeksów

  24. Modyfikacjaupdate 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 zakresoweselect * from employees where long between ? and ?; Zapytanie punktowe select * from employees where ssnum = ? Strojenie indeksów – operacje 15. Strojenie indeksów

  25. 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 Indeks pogrupowany – pomiary 15. Strojenie indeksów

  26. 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 Indeks niepogrupowany – korzyści 15. Strojenie indeksów

  27. 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) Odczyt pełny może być lepszy 15. Strojenie indeksów

  28. 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 15. Strojenie indeksów

  29. 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 Indeks pokrywający – pomiary 15. Strojenie indeksów

  30. 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 Pielęgnacja indeksu – SQL Server 15. Strojenie indeksów

  31. 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 Pielęgnacja indeksu – DB2 15. Strojenie indeksów

  32. 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 Pielęgnacja indeksu – Oracle 15. Strojenie indeksów

  33. 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ść 15. Strojenie indeksów

  34. 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 Indeks na małej tabeli – pomiary 15. Strojenie indeksów

  35. 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. 15. Strojenie indeksów

  36. 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” Zapytania wielopunktowe: B+drzewo, hasz, bitmapa 15. Strojenie indeksów

  37. Indeks haszowany nie pomaga w zapytaniach zakresowych Indeks haszowany pokonuje B+drzewo przy zapytaniach punktowych B+drzewo, hasz, bitmapa 15. Strojenie indeksów

  38. 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 15. Strojenie indeksów

  39. Podsumowanie • Indeks haszowany nadaje się tylko do zapytań punktowych. Dla zapytań wielopunktowych i zakresowych lepsze są B+drzewa. • 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 • Pokryj kluczowe zapytania indeksem gęstym • Nie zakładaj indeksu, gdy dodatkowy czas potrzebny na obsługę wstawień i modyfikacji jest większy niż oszczędności przy zapytaniach 15. Strojenie indeksów

  40. 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 Kreator indeksów MS SQL 15. Strojenie indeksów

More Related