1 / 51

INDEKSY I SORTOWANIE ZEWNĘTRZNE

INDEKSY I SORTOWANIE ZEWNĘTRZNE. Przygotowa ł Lech Banachowski na podstawie: Raghu Ramakrishnan, Johannes Gehrke, Database Management Systems, McGrawHill, 2000 (ksi ążka i slide’y). Lech Banachowski, Krzysztof Stencel, Systemy zarzadzania bazami danych, Wydawnictwo PJWSTK, 2007.

neila
Download Presentation

INDEKSY I SORTOWANIE ZEWNĘTRZNE

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. INDEKSYI SORTOWANIE ZEWNĘTRZNE • Przygotował Lech Banachowski na podstawie: • Raghu Ramakrishnan, Johannes Gehrke, Database Management Systems, McGrawHill, 2000 (książka i slide’y). • Lech Banachowski, Krzysztof Stencel, Systemy zarzadzania bazami danych, Wydawnictwo PJWSTK, 2007. • Dokumentacja Oracle.

  2. Organizacje pliku z danymi • Plik nieuporządkowany • Plik uporządkowany (posortowany) • Plik haszowany

  3. Wyszukiwanie Najczęściej wykonywaną operacją w bazie danych jest wyszukiwanie danych. W najprostszej postaci: mając konkretną wartość poszukujemy rekordów, w których ta wartość występuje w danym polu.

  4. Indeksy • Plik nieuporządkowanyumożliwia wyszukanie rekordu: • mając ridrekordu, lub • przeglądając sekwencyjnie wszystkie rekordy w pliku. • Często wyszukiwanie na podstawie wartości jednego lub więcej pól, np. • wyznacz wszystkich studentów specjalizacji “BD”, • wyznacz wszystkich studentów mających < 20 lat. • Indeksy są strukturami danych, których celem jest wspomaganie szybkiego znajdowania odpowiedzi na takiego rodzaju zapytania. • Takie samo znaczenie jak skorowidz (indeks) w książce!

  5. Indeksy • Klucz wyszukiwania dla indeksu • wybrane pola rekordu względem których ma odbywać się wyszukiwanie. • Indeks składa się z : • pozycji danychk* określanych względem wartości klucza wyszukiwania k, oraz z • pozycji indeksu, kierujących wyznaczeniem właściwej pozycji danychk* w oparciu o wartość klucza wyszukiwania.

  6. Indeksy • Plik danych – rekordy danych. • Plik indeksu – pozycje danych, pozycje indeksu. • Indeks wewnętrzny - plik indeksu zawiera w sobie plik danych. Pozycjami danych k*w indeksie są rekordy danych. • Indeks zewnętrzny – plik indeksu jest rozłączny z plikiem danych. Pozycje danych k*zawierają wskaźniki do rekordów danych. Postacie: • <k, rid rekordu z tą wartością klucza k> albo • <k, lista rid rekordów z tą wartością klucza k > • Ad 2. Postać bardziej zwarta; zmienny format rekordu.

  7. Klasyfikacja indeksów • Gdy klucz wyszukiwania zawiera klucz główny – indeks głównywpp. indeks niegłówny. • Indeks jednoznaczny - klucz wyszukiwania zawiera klucz jednoznaczny. • Indeks pogrupowany - indeks wewnętrzny i plik danych posortowany według wartości klucza indeksu.

  8. Indeks pogrupowany • Pozycje danych (czyli rekordy danych) są zapisane w kolejności uporządkowanej względem wartości klucza indeksu. W rezultacie, rekordy o tej samej wartości klucza lub zbliżonej znajdują się na tej samej stronie lub na kilku powiązanych.

  9. Indeksy wewnętrzne i zewnętrzne • Indeks wewnętrzny haszowany = indeks wewnętrzny i plik danych haszowany. • Indeks wewnętrzny -> • Plik danych posortowany i indeks pogrupowany, albo • Plik danych haszowany i indeks haszowany. • Indeks zewnętrzny -> • Plik danych nieuporządkowany , albo • Plik danych posortowany , albo • Plik danych haszowany.

  10. Złożone klucze wyszukiwania • Złożone klucze wyszukiwania: kombinacja pól np. <sal,age>. • Zapytanie równościowe: • age=20 and sal =75 • Zapytanie zakresowe: • age < 20; age=20 and sal > 10 • Porządek leksykograficzny. 11,80 11 12 12,10 name age sal 12,20 12 13,75 bob 12 10 13 <age, sal> cal 11 80 <age> joe 12 20 10,12 sue 13 75 10 20 20,12 Rekordy danych posortowane <name> 75,13 75 80,11 80 <sal, age> <sal> Pozycje danych posortowane<sal,age> Pozycje danych posortowane<sal>

  11. Pseudo-wartość NULL • Indeksowane są wszystkie wiersze, w których co najmniej jedna składowa klucza wyszukiwania indeksu nie jest NULL. • Na przykład, za pomocą indeksu na kolumnie Comm nie można zrealizować wyszukiwania wierszy z nieokreśloną wartością w polu Comm: • SELECT EnameFROM EmpWHERE Comm IS NULL; • Można … WHERE Comm IS NOLL NULL; • Ograniczenie to nie dotyczy indeksów bitmapowych i klastrowych.

  12. Wyszukiwanie zakresowe – plik posortowany • "Wyznacz studentów ze średnią > 4.0" • Gdy dane w pliku posortowanym: • wyszukiwanie binarne aby znaleźć pierwszego takiego studenta; • przejdź plik wypisując pozostałych takich studentów. • Plik indeksu k1< k2< … <KN. Plik indeksu kN k2 k1 Plik danych posortowany Page N Page 2 Page 0 Page1 • Wyszukiwanie binarne na mniejszym pliku indeksu!

  13. Pozycja indeksu K < K …. < K 1 2 m Strona indeksu P K P K P P K m 0 1 2 1 m 2 Statyczne drzewo ISAM (indexed sequential access method) Węzły wewnętrzne Liście Strony nadmiarowe Strony główne • W liściach są pozycje danych.

  14. Korzeń 40 20 33 51 63 46* 55* 10* 15* 20* 27* 33* 37* 40* 51* 97* 63* Drzewo ISAM

  15. Wstawiamy 23*, 48*, 41*, 42* ... Korzeń Strony indeksu 40 20 33 51 63 Strony główne 46* 55* 10* 15* 20* 27* 33* 37* 40* 51* 97* 63* 41* 48* 23* Strony nadmiarowe 42*

  16. Pozycje indeksu Pozycje danych B+ drzewo • Wstawianie/usuwanie w czasie rzędulog N; drzewo wyważone względem wysokości. (N = # liści) • Każdy węzeł (z wyjątkiem ewentualnie korzenia) zawierad <= m <= 2d pozycji indeksu. Parametr d - stopień drzewa. • Zapytania równościowe i zakresowe.

  17. B+ drzewo • Wyszukiwanie zaczyna się w korzeniu a porównania klucza wyszukiwania prowadzą do liścia tak jak dla ISAM. • Wyszukiwanie 5*, 15*, >= 24* ... Korzeń 30 13 17 24 39* 3* 5* 19* 20* 22* 24* 27* 38* 2* 7* 14* 16* 29* 33* 34* • Na podstawie wyniku wyszukiwania 15*, wiemy że 15* nie ma w drzewie

  18. Drzewa B+ w praktyce • Średni stopień d=100. Średnie zapełnienie: 67%. • Typowa pojemność: • Wysokość 4: 1334 = 312,900,700 rekordów • Wysokość 3: 1333 = 2,352,637 rekordów • Zwykle górne poziomy drzewa w cache: • Poziom 1 = 1 strona = 8 KB • Poziom 2 = 133 strony = 1 MB • Poziom 3 = 17,689 strony = 133 MB

  19. Wstawienie pozycji danych do B+ drzewa • Wyznacz odpowiedni liśćL. • Wstaw pozycję danych do L. • Jeśli mieści się – to koniec! • Wpp, podziel L (na L i nowy węzeł L2) • Rozdziel równo pozycje, skopiuj na wyższy poziom środkowy klucz (najmniejszy w prawym węźle L2). • Do “ojca” L wstaw pozycję indeksu z tym kluczem i wskaźnikiem wskazującym na L2. • W razie potrzeby powtórz krok podziału rekurencyjnie. • Kroki podziału mogą dojść do korzenia i w rezultacie drzewo może zwiększyć swoją wysokość o jeden.

  20. 17 5 13 24 30 Wstawianie 8* Pozycja wstawiana do “ojca” jest kopiowana. 5 Podział liścia 3* 5* 2* 7* 8* Pozycja wstawiana do “ojca” jest przesuwana w górę . Podział węzła wewnętrznego

  21. Drzewo po wstawieniu 8* Korzeń 17 24 5 13 30 39* 2* 3* 5* 7* 8* 19* 20* 22* 24* 27* 38* 29* 33* 34* 14* 16* • Poprzedni korzeń uległ podziałowi. Wysokość drzewa zwiększyła się o 1. • Możliwa redystrybucja pozycji danych w poziomie nie jest w praktyce realizowana.

  22. B+ drzewo jako indeks wewnętrzny i zewnętrzny • Indeks może być wewnętrznyi wtedy pozycje danych pokrywają się z rekordami danych, tzn. rekordy danych są zapisywane w strukturze B+ drzewa zgodnie z porządkiem klucza wyszukiwania. Z tego powodu jest to wtedy indeks pogrupowany. • Albo indeks jest zewnętrzny i wtedy rekordy z danymi są przechowywane poza indeksem w dowolnym porządku (dla plików nieuporządkowanych, posortowanych i haszowanych).

  23. Strategia zastępowania ramek dla stron B+ drzewa • LRU nie jest tu dobrą metodą! • Korzeń drzewa i najwyższe poziomy dobrze jest trzymać cały czas w pamięci RAM.

  24. Dodatkowe operacje na B+ drzewach • Gdy ilość zajętego miejsca w drzewie spada poniżej pewnego progu, możliwe: • REBUILDutworzenie indeksu od nowa • COALESCEpołączenie ze sobą sąsiednich stron o zajętości poniżej 50%. • Gdy na samym początku mamy dany duży zbiór rekordów, to wtedy zamiast powtarzania kolejnych operacji INSERT opłaca się zastosować algorytm Bulk Loading, którego działanie polega na posortowaniu pliku rekordów z danymi, a następnie dobudowaniu nad posortowanym ciągiem kolejnych poziomów indeksowych drzewa B+.

  25. Dlaczego nie używamy B-drzew? Dla pełności tematu pokazujemy schemat węzła zwykłego B-drzewa (bez plusa).

  26. Podsumowanie drzew • Zastosowanie indeksów o strukturze drzewa: • Wyszukiwanie zakresowe. • Wyszukiwanie równościowe. • Sortowanie (np. ORDER BY). • ISAM – struktura statyczna. • Modyfikowane są tylko liście. • Wymagane są strony nadmiarowe – mogące istotnie pogorszyć działanie algorytmów. • B+ drzewo – struktura dynamiczna. • W praktyce wysokość zwykle <=4.

  27. Indeks haszowany • Ustalona alokacja stron głównych; alokowane dodatkowe strony nadmiarowe w razie potrzeby. • h(k)np.= k mod M= “segment” do którego należy pozycja danych k* o kluczu k (M = # segmentów). 0 h(klucz) 1 klucz h M-1 Strony główne Strony nadmiarowe Plik: nieuporządkowany, posortowany i haszowany.

  28. Indeks haszowany z uporządkowanymi segmentami Wariant w którym pozycje danych są uporządkowane względem wartości klucza wyszukiwania.

  29. Podsumowanie haszowania • Mogą powstać długie łańcuchy nadmiarowych stron co prowadzi do pogorszenia działania: • konieczność okresowego wykonywan ia operacji REBUILD, • istnieją dynamiczne wersje haszowania – polegające na zwiększaniu liczby M segmentów przez ich podział. • Indeksy haszowane dobre przy wyszukiwaniu równościowym. Nie wspomagają wyszukiwania zakresowego ani sortowania.

  30. Podsumowanie indeksu wewnętrznego • Dla jednej tabeli może być zbudowany tylko jeden indeks wewnętrzny i wiele indeksów zewnętrznych względem różnych kluczy wyszukiwania. • W indeksie wewnętrznym zbudowanym na B+ drzewie rekordy z danymi są zapisywane na stronach reprezentujących liście drzewa. W indeksie haszowanym wewnętrznym rekordy z danymi są zapisywane na stronach segmentów. W obu przypadkach z definicji algorytmów wstawiania i usuwania wynika, że rekordy mogą być przesuwane między stronami. To znaczy, rekordy mogą zmieniać stronę a zatem nie może być do nich z zewnątrz bezpośrednich wskaźników używających identyfikatora strony. Za identyfikator rekordu używa się wtedy wartość jego klucza głównego a dostęp do rekordu jest zawsze realizowany poprzez indeks główny.

  31. Indeks wewnętrzny – 2 przypadki • Indeks główny jest indeksem wewnętrznym. Wtedy wyszukiwanie rekordu według wartości klucza wyszukiwania indeksu zewnętrznego wymaga przejścia dwóch indeksów: najpierw zewnętrznego, w którym znajdujemy wartość klucza głównego szukanego rekordu, a następnie indeksu wewnętrznego głównego, w którym w oparciu o wartość klucza głównego znajdujemy szukany rekord. • Indeks główny jest indeksem zewnętrznym, w którego pozycjach danych są zapisywane (zmienne) wskaźniki do rekordów. Przy wyszukiwaniu rekordu  przechodzimy tak jak poprzednio dwa indeksy: zewnętrzny i główny. Natomiast przy zmianach położenia rekordu w indeksie wewnętrznym wymagane jest znalezienie pozycji danych tego rekordu w indeksie głównym i zapisanie w niej nowego adresu tego rekordu.

  32. Struktury danych wielowymiarowe • Siatka (ang. grid file) – podział obszaru wyszukiwań, powiedzmy leżącego na płaszczyźnie, poziomymi i pionowymi liniami na prostokąty. Punkty trafiające do jednego prostokąta są zapisywane w jednym segmencie. Gdy segmenty się przepełnią, można albo stosować strony nadmiarowe albo dzielić prostokąty na mniejsze zwiększając liczbę segmentów.

  33. Struktury danych wielowymiarowe • Dzielona funkcja haszowana (ang. partitioned hash function): • H(v1,...,vn)=h1(v1)&h2(v2)&....&hn(vn) • Wartością funkcji haszującej dla układu wartości jest konkatenacja tekstowa wartości funkcji haszujących dla poszczególnych składowych.

  34. Drzewa wielowymiarowe • R drzewasą uogólnieniem B drzew do n wymiarów. Pozycją danych w takim drzewie jest para złożona z n-wymiarowej kostki:{(v1,...,vn): ai<=vi<=bi dla 1<=i<=n} oraz adresu pewnego obiektu geometrycznego, który jest jednoznacznie ograniczony tą kostką. • Podobnie pozycją indeksu jest taka kostka oraz wskaźnik do węzła niższego poziomu, w którego poddrzewie znajdują się wyłącznie obiekty zawarte w tej kostce i w żadnym innym poddrzewie takie obiekty nie występują.

  35. Powtórzenie Plik danych – składowanie danych • nieuporządkowany • uporządkowany (posortowany) • haszowany Indeks – wyszukiwanie danych i jednoznaczność kluczy • wewnętrzny - pozycja danych = rekord danych • zewnętrzny – pozycja danych = wskaźnik do rekordu danych • pogrupowany – wewnętrzny & plik danych uporządkowany według wartości klucza indeksu • B+ drzewo • tablica haszowana (jako indeks wewnętrzny lub zewnętrzny)

  36. Zastosowania sortowania w bazach danych • ORDER BY - dane są wymagane w pewnym porządku. • Budowa indeksu - początkowego B+ drzewa dla wczytywanego zbioru rekordów. • Złączanie tabel metodąSort-merge. • Realizacja DISTINCT, GROUP BY, UNION, EXCEPT - alternatywą haszowanie. Problem: posortować 1GB danych za pomocą 10MB RAM.

  37. Sortowanie zewnętrzne (wielofazowe przez scalanie) • Faza 0 – sortowanie rekordów w ramach stron: Wczytaj stronę, posortuj ją, zapisz na dysku. • Faza 1,2,3 …, itd: scalaj uporządkowane podpliki w większe uporządkowane podpliki aż cały plik zostanie uporządkowany. Wejście 1 Wyjście Wejście 2 Bufory w RAM Dysk Dysk

  38. Sortowanie wielofazowe przez scalanie 6,2 2 3,4 9,4 8,7 5,6 3,1 Plik wejściowy Faza 0 1,3 2 3,4 2,6 4,9 7,8 5,6 • W każdej fazie odczytujemy i zapisujemy każdą stronę w pliku. • N = # stron => # faz = • Całkowity koszt = 2 N log N • Idea:Dziel i rządź: sortuj podpliki i je scalaj. • Zamiast dwóch buforów można użyć więcej. • Praktycznie liczba faz <=3. Faza 1 4,7 1,3 2,3 8,9 5,6 2 4,6 Faza 2 2,3 4,4 1,2 6,7 3,5 6 8,9 Faza 3 1,2 2,3 3,4 4,5 6,6 7,8 9

  39. Sortowanie za pomocą B+drzewa • Warunek: Tabela ma indeks na B+ drzewie względem kolumn sortowania. • Idea: Przejść po liściach indeksu. • Czy jest to dobra metoda? • Przypadki: • Indeks pogrupowanyBardzo dobra! • Indeks niepogrupowany Może być bardzo zła!

  40. Wewnętrznyindeks pogrupowany • Od korzenia przejdź do skrajnie lewego liścia a następnie sekwencyjnie w prawo po liściach. Indeks Pozycje danych Rekordy danych w pozycjach danych! • Zawsze lepsze od sortowania zewnętrznego!

  41. Indeks niepogrupowany • Ogólnie, jedna operacja We/Wy na rekord danych! Indeks Pozycje danych Rekordy danych

  42. Indeksy w Oracle • Indeks oparty na B+ drzewie • Tabela połączona z indeksem opartym na B+ drzewie • Indeks oparty na B+ drzewie z odwróconymi wartościami kluczy • Indeks oparty na klastrze jednej lub więcej tabel (B+ drzewo lub hasz), indeks haszowany • Indeks z pozycjami określonymi za pomocą wyrażeń • Indeks bitmapowy – implementacja dwa B+ drzewa

  43. Plik nieuporządkowany - bez indeksu wewnętrznego - indeks zewnętrzny oparty na B+ drzewie • Pozycja danych składa się z wartości indeksowanych kolumn oraz z identyfikatora ROWID wiersza w tabeli - określającego fizyczne położenie danego wiersza na dysku. Umożliwia: • znalezienie wiersza w oparciu o wartość klucza wyszukiwania, • realizację zapytań zakresowych. • Przy wykonywaniu zapytania system używa indeksu opartego na B+ drzewie tylko wtedy gdy jest zapewniona wystarczająca selektywność wyszukiwania, powiedzmy zwracane zostaje co najwyżej 5 do 10% wszystkich rekordów w pliku. • Indeks zewnętrzny oparty na B+ drzewie jest automatycznie tworzony dla każdego klucza głównego i jednoznacznego.

  44. Tabela połączona z indeksem głównym opartym na B+ drzewie • Pozycjami danych indeksu głównego są rekordy pliku tzn. wiersze tabeli są trzymane w indeksie. Jest zapewniony bardzo szybki dostęp do wierszy przez wartości klucza głównego. • Wiersze nie posiadają swoich identyfikatorów ROWID – identyfikacja wierszy przebiega wyłącznie przez wartości klucza głównego;  w pozostałych indeksach wynikiem wyszukania jest wartość klucza głównego – a nie ROWID. Odpowiada to koncepcji indeksu pogrupowanego - ale budowanego tylko dla klucza głównego. • Budując osobno perspektywę zmaterializowaną można jej nadać inną organizację.

  45. Tabela połączona z indeksem opartym na B+ drzewie • Załóżmy, że chcemy dokonywać analizy klientów wyszukując klientów mieszkających w określonym mieście. • Miasta(Id_miasta, Nazwa_miasta) • Klienci(Id_miasta, Id_klien_w_miescie, Nazwisko, Hobby, Wiek) • Jeśli tabela Klienci jest połączona z indeksem na swoim kluczu głównym, istotnie można przyśpieszyć wykonywanie zapytań w rodzaju: • SELECT K.Nazwisko, .HobbyFROM Klienci K INNER JOIN Miasta M ON K.Id_miasta = M.Id_miastaWHEREM.Nazwa_miasta = 'WARSZAWA'     AND K.Wiek BETWEEN 18 and 25;

  46. Tworzenie tabeli połączonej z indeksem opartym na B+ drzewie CREATE TABLE Klienci(   Id_miasta INTEGER,   Id_klien_w_miescie INTEGER,   Nazwisko VARCHAR2(80),   Hobby VARCHAR(20),   Wiek INTEGER,   CONSTRAINT Klienci_pk PRIMARY KEY(Id_miasta, Id_klien_w_miescie),   CONSTRAINT Klienci_fk FOREIGN KEY(Id_miasta) REFERENCES Miasta   )ORGANIZATION INDEX;

  47. Indeks haszowany • Przykład zastosowania indeksu haszowanego dla klucza głównego Id_konta tabeli: • Konta(Id_konta, Saldo, Imie, Nazwisko, Adres) • Zakładamy, że tabela Konta zawiera bardzo dużo wierszy oraz że często wielu kasjerów w banku równocześnie wykonuje zapytanie: • SELECT *FROM Konta kWHERE k.Id_konta = :numer;

  48. Tworzenie indeksu haszowanego CREATE CLUSTER Klast_konta(Id_konta INTEGER)  SIZE 512SINGLE TABLE  HASHKEYS 100003 HASH IS mod(Id_konta, 100003); - lub HASH IS Id_konta Następnie definiujemy tabelę Konta: CREATE TABLE Konta(Id_konta INTEGER PRIMARY KEY,     Saldo NUMBER,     Imie VARCHAR2(20),     Nazwisko VARCHAR2(50),     Adres VARCHAR2(70))CLUSTER Klast_konta(Id_konta);

  49. Tworzenie indeksu haszowanego • Indeks klastra jest tworzony automatycznie. • Parametr SIZE w definicji klastra określa ilość miejsca w bajtach przeznaczoną do zapisania rekordów z tą samą wartością klucza klastra. Domyślną wartością (gdy brak klauzuli SIZE) jest rozmiar strony dyskowej. Jeśli wszystkie wiersze dla danej wartości klucza klastra nie mieszczą się w jednym bloku, są zapisywane na liście nadmiarowych bloków. Poprzez dobór odpowiedniej wartości SIZE można spowodować, że wszystkie rekordy z daną wartością klucza znajdą się na tej samej lub tylko na kilku stronach dyskowych.

  50. Sortowanie segmentów haszowanych CREATE CLUSTER call_detail_cluster ( telno NUMBER, call_timestamp NUMBER SORT, call_duration NUMBER SORT ) HASHKEYS 10000 HASH IS telnoSIZE 256; CREATE TABLE call_detail ( telno NUMBER, call_timestamp NUMBER SORT, call_duration NUMBER SORT, other_info VARCHAR2(30) ) CLUSTER call_detail_cluster ( telno, call_timestamp, call_duration ); Wyświetlić rozmowy wykonane z danego numeru w kolejności od najwcześniejszego. SELECT * WHERE telno = 6505551212 ORDER BY call_timestamp;

More Related