Transcript Acc Presentation Template

Optymalizacja aplikacji
bazodanowych
Część 3: Indeksy w bazie danych Oracle
Copyright © 2011 Accenture All Rights Reserved. Accenture, its logo, and High Performance Delivered are trademarks of Accenture.
Plan
• Indeksy – czym są?
• Podział indeksów ze względu na wewnętrzną strukturę
• W jakich sytuacjach (nie-)stosować indeksy?
• Indeksy proste i złożone (na przykładzie drzew „B-trees”)
• Zalety oraz wady indeksów
• Statystyki oraz histogramy
Copyright © 2011 Accenture All Rights Reserved.
2
Indeksy – czym są?
Czym są indeksy?
• Indeksy można porównać do spisu treści w książce – szukamy w spisie
treści, interesującego nas zagadnienia – wraz z nim podana jest strona,,
na której znajdziemy informacje o tym zagadnieniu – podobnie działają
indeksy
• Indeksy są opcjonalnymi strukturami związanymi z konkretną tabelą –
zakładamy indeksy na określonych polach tabeli
• Nie trzeba przepisywać zapytań SQL, żeby móc użyć indeksów –
wystarczy je dodać, natomiast procesy Oracle’a skorzystają z nich
automatycznie (oczywiście pod warunkiem, że indeksowane pole jest
wykorzystane w zapytaniu – szczegóły na następnych slajdach)
Copyright © 2011 Accenture All Rights Reserved.
3
Indeksy – czym są?
Analogia ich budowy do struktur drzewiastych
• Dzięki indeksom, „ścieżka dostępu” do szukanych danych jest krótsza
Dlaczego? Wystarczy przypomnieć sobie strukturę drzew binarnych
Korzeń (root)
12
Gałąź (branch)
4
1
25
7
18
Liść (leaf)
32
Żeby wyszukać wartość „18”:
- rozpoczynamy „odwiedziny” korzenia – wartość 18 jest większa od 12
- w takiej sytuacji „odwiedzamy” prawą gałąź – wartość 18 jest mniejsza od 25
- „odwiedzamy” lewą gałąź - trafiamy na 18 – znaleźliśmy interesującą nas wartość
... ciąg dalszy na nastepnej stronie
Copyright © 2011 Accenture All Rights Reserved.
4
Indeksy – czym są?
Analogia ich budowy do struktur drzewiastych
• Podobnie do idei drzew binarnych, działają indeksy. W przypadku
drzewa binarnego wystarczyły 3 porównania (12, 25 i 18), żeby dotrzeć
do interesującej nas informacji.
• Indeksy w Oracle’u nie są drzewami binarnymi, jednak wykorzystują
właściwość struktur drzewiastych
• A co w przypadku przeszukiwania tabeli?
W takiej sytuacji musimy przeszukiwać
tabelę wiersz po wierszu – jak widać
na przykładzie obok, należy wykonać
6 porównań, żeby znaleźć interesującą
nas wartość
• A co jeżeli takich rekordów będą setki tysięcy?
.. miliony ?
.. miliardy ?
Copyright © 2011 Accenture All Rights Reserved.
row#1
25
row#2
7
row#3
1
row#4
32
row#5
12
row#6
18
row#7
4
5
Indeksy – czym są?
Złożoność obliczeniowa
• Jeżeli drzewo binarne jest wyważone, czyli wszystkie jego gałęzie różnią się
wysokością co najwyżej o 1, wówczas złożoność obliczeniowa operacji
wyszukiwania wynosi O(logn) – logarytmiczna złożoność obliczeniowa
• W najgorszym przypadku
(zdegenerowane drzewo),
czyli takie drzewo, gdzie
wszystkie jego gałęzie
mają po jednej wychodzącej
z niej kolejnej gałęzi,
złożoność obliczeniowa
wyszukiwania wynosi O(n)
– liniowa złożoność obliczeniowa
25
20
15
liniowa
logarytmiczna
10
5
0
1 2 3 4 5 6 7 8 9 1011121314151617181920
Copyright © 2011 Accenture All Rights Reserved.
6
Podział indeksów ze względu na wewnętrzną
strukturę
• Indeksy można podzielić na kilka rodzajów – w tym przypadku podział ze
względu na wewnętrzną strukturę:
–
–
B-trees, czyli „B-drzewa” – struktura drzewiasta, cechująca się podobną co drzewa binarne
funkcjonalnością
Widać na rysunku obok,
że „Branch Blocks”, czyli
„bloki gałęzi” posiadają
gałąź (korzeń) – blok na
samej górze, w którym
widać, że istnieje podział
wszystkich zindeksowanych
wartości, pogrupowanych
w mniejsze części (gałęzie).
Po przejściu do „mniejszej
części” (gałęzi),
znowu widzimy podział
na mniejsze części,
tym razem dowiązanie
istnieje do liści, nie gałęzi.
W liściach znajdujemy już
interesujące nas wartości
oraz rowid, czyli adres
fizycznej lokalizacji na dysku.
Dostęp za pomocą rowid jest
stały O(1)
Copyright © 2011 Accenture All Rights Reserved.
7
Podział indeksów ze względu na wewnętrzną
strukturę
–
Bitmap indexes, czyli indeksy bitmapowe przechowują „mapę bitów” dla klucza indeksu. I tak na
przykład, dla pola, mówiącego nam o płci danej osoby, możemy stworzyć taką mapę bitów:
Row #1
Row #2
Row #3
Row #4
Row #5
Row #6
Row #7
Row #8
–
–
–
Mężczyzna Kobieta
0
1
1
0
1
0
1
0
0
1
1
0
0
1
0
1
• Jak widać, dla wiersza, które zawiera wartość „Mężczyzna”, „Kobieta”,
wartość tego pola wynosi 1. W przeciwnym przypadku to pole
przyjmuje wartość 0.
• Operacje bitowe są bardzo pożądane w aplikacjach assemblerowych,
ponieważ ich największą zaletą jest wydajność. To samo dotyczy baz
danych Oracle’a – w końcu przy dużych wolumenach danych,
wydajność ma znaczenie.
Nie bez powodu powyższy przykład posiada podział na wartości Mężczyzna / Kobieta, ponieważ
indeksy bitmapowe powinno się stosować dla pól, gdzie liczność możliwych do wystąpienia wartości
jest mała – to jest najważniejsza właściwość tych indeksów
W przypadku dużej liczności możliwych do wystąpienia wartości dla danego pola (np. data
aktualizacji rekordu), należy używać indeksów typu „B-drzewa”,
Dla indeksów typu „B-drzewa”, gdy znajdziemy interesującą nas wartość, mamy wskazanie na rowid,
czyli fizyczną lokalizację całego wiersza.
W przypadku „bitmapowych indeksów”, każdy klucz indeksu, czyli interesująca nas wartość,
przechowuje wskaźniki na wiele wierszy.
Copyright © 2011 Accenture All Rights Reserved.
8
Podział indeksów ze względu na wewnętrzną
strukturę
–
Problem z życia wzięty: Tabela przechowująca informacje o doładowaniach kart prepaid’owych (czyli
doładowujesz „telefon” i dopiero możesz dzwonić) oraz typu „mix”, jednej sieci komórkowej w Polsce.
Wolumen danych tabeli, to aktualnie ok. 30 miliardów rekordów, podzielonych na części, o
częstotliwości 1 dnia.
Wolumen danych dla 1 części (1 dnia) to średnio ok. 18 milonów rekordów.
Wolumen osób to ok. 40 milonów rekordów.
W tabeli doładowań mamy informację o osobie (jej unikalny numer w postaci numeru
idnetyfikacyjnego), która doładowała kartę. Mamy identyfikator takiej osoby i chcemy zobaczyć jej
wszystkie informacje odnośnie doładowań za okres ostatnich 5 miesięcy.
Przeszukując tabelę, wiersz po wierszu, mielibyśmy do przeszukania 2 miliardy 700 milionów
rekordów.
Nasuwa się stwierdzenie – użyjmy indeksu... Zgadza się. Ale jakiego ???
Okres za 5 miesięcy wybieramy za pomocą funkcjonalności partycjonowania, omówionego
szczegółowo na kolejnych wykładach. W skrócie, taki podział na części, gdzie dane za 1 dzień
należą do 1 części (partycji), nazywa się partycjonowaniem.
Copyright © 2011 Accenture All Rights Reserved.
9
Podział indeksów ze względu na wewnętrzną
strukturę
–
Stosunek liczby osób doładowujących (40 milionów) do ogólnej liczby doładowań (30 miliardów)
wynosi 0,0013
Nie tylko indeks na osobie doładowującej kartę, ale również na pozostałych kilku polach
zastosowano indeks bitmapowy.
Oczywiście nie jest on tak wydajny, jak przykład płci (Mężczyzna / Kobieta) w tabeli pracowników
danej firmy, gdzie stosunek liczności (2) do liczby pracowników (niech będzie to nawet 50 000
pracowników), to 0,00004, ale i tak jest to korzystny wybór.
W dokumentacji Oracle’a można wyczytać, że „jeżeli liczba unikalnych wartości jest mniejsza niż 1%
względem liczby rekordów lub jeśli wartości w danej kolumnie są powtórzone przynajmniej 100 razy,
wówczas takie pole jest kandydatem do zindeksowania za pomocą bitmapowych indeksów”.
Brak takiego „progu”, gdzie wylicza się stosunek liczności do liczby rekordów, przy którym należy
rozważyć użycie jednak indeksu typu „B-drzewa” – pozostają tylko testy + doświadczenie.
–
Można tylko dodać, że w/w indeks bitmapowy na tak dużym wolumenie danych sprawdza się
doskonale.
Copyright © 2011 Accenture All Rights Reserved.
10
Podział indeksów ze względu na wewnętrzną
strukturę
Główne różnice pomiędzy indeksami typu „B-trees” oraz „Bitmap”
B-Trees
Bitmap
Użyteczne przede wszystkim w aplikacjach OLTP
oraz DWH
Użyteczne głównie w aplikacjach typu DWH, gdzie
tworzone są raporty, analizy oraz jest mała
modyfikacja zindeksowanych pól
Zajmują więcej miejsca niż bitmapy
Zajmują mniej miejsca niż „B-trees”
Użyteczne dla pól o duzej liczności
Użyteczne dla pól o małej liczności
Niższa wydajność niż bitmapy, dla zapytań gdzie
używa się operacji AND, OR lub XOR
Wysoka wydajność w zapytaniach, gdzie używa się
operacji AND, OR lub XOR
Aktualizacja wartości zindeksowanej blokuje dostęp
tylko do tej wartości lub jeżeli aktualizowanych jest
wiele wartości, wówczas tylko one zostają
zablokowane, aby przeprowadzić proces aktualizacji
Aktualizacja wartości zindeksowanej blokuje dostęp
do całej grupy rekordów (segment), w której znajduje
się aktualizowana wartość. Jeżeli są to rekordy
należące do wielu segmentów, może zdarzyć się, że
cała tabela zostanie zablokowana, przez co
niemożliwa jest aktualizacja przez inny proces
Copyright © 2011 Accenture All Rights Reserved.
11
W jakich sytuacjach (nie-)stosować indeksy?
Warto pomyśleć o utworzeniu indeksu na tabeli, jeżeli:
– W klauzuli WHERE istnieją pola, po których następuje często wyszukiwanie wartości,
jednak szacowana ilość zwróconych wierszy oscyluje w co najwyżej kilkunastu
procentach, chociaż idealnymi kandydatami są pola, gdzie ilość zwróconych wierszy to
co najwyżej 5 – 10 %
– Łączenie tabel odbywa się po danym polu – wówczas indeks na kluczu głównym
nadrzędnej tabeli zostanie stworzony automatycznie, natomiast na kluczu obcym tabeli
podrzędnej warto założyć indeks – przyspieszy to łączenie tabel i uniknie blokowania
całych tabel, gdy trzeba będzie zaktualizować wartość pola klucza obcego
– w obrębie tabeli hurtowni danych (DWH) istnieją takie pola, które mają małą liczność,
będą używane w zapytaniach oraz nie będą aktualizowane lub będzie to się odbywać
bardzo rzadko
– Indeksowane pole ma „dużą unikalność”, tzn. indeksowana wartość jest powtórzona
tylko kilka razy – mało duplikatów
Warto pomyśleć o nietworzeniu indeksu na tabeli, jeżeli:
-
Zindeksowane pole będzie często aktualizowane
Na zindeksowanym polu będą użyte funkcje inne niż MIN() oraz MAX()
Copyright © 2011 Accenture All Rights Reserved.
12
Indeksy proste i złożone (na przykładzie drzew
„B-trees”)
Indeksy proste i złożone
–Zanim omówione zostaną indeksy proste i złożone, należy wspomnieć o ważnej cesze
indeksów:
• możliwość zwrócenia wartości w kolejności rosnącej / malejącej, ponieważ każdy z
liści przechowujących dane posiada referencję do sąsiadującego z nim liścia
–Jak wspomniano – dla indeksów, po odnalezieniu przez warunek odpowiednich wartości,
za pomocą rowid następuje bezpośredni odczyt wierszy.
–Indeksy proste, to takie indeksy, które zawierają tylko jedno indeksowane pole
• Indeksy można czytać na wiele sposobów:
– metoda „Range Scan” – zakres wartości
• Mając poniższe wartości w kolejności posortowanej:
41,rowid
49,rowid
55,rowid
65,rowid
71,rowid
92,rowid
• Można zwrócić część danych, za pomocą „Range Scan” w następujący sposób:
SELECT * FROM tabela WHERE id BETWEEN 50 AND 90
• Wówczas tylko wytłuszczone wartości zostaną zwrócone
Copyright © 2011 Accenture All Rights Reserved.
13
Indeksy proste i złożone (na przykładzie drzew
„B-trees”)
– metoda „Unique Scan”
• Występuje dla indeksów z unikalnymi wartościami i zwraca tylko jedną wartość (unikalną):
SELECT * FROM tabela WHERE id = 55
– metoda „Full index Scan”
SELECT * FROM tabela WHERE id > 42
– metoda „Fast Full Index Scan”
• W takiej sytuacji nie występuje odczyt wartości w kolejności rosnącej / malejącej, tylko
czytane są całe bloki z danymi
• Taka metoda odczytu następuje w momencie, kiedy wszystkie interesujące w zapytaniu,
wyszukiwane wartości znajdują się w indeksie:
SELECT nazwisko FROM tabela
• Ważne tylko jest, żeby takie pole miało atrybut NOT NULL
Copyright © 2011 Accenture All Rights Reserved.
14
Indeksy proste i złożone (na przykładzie drzew
„B-trees”)
– Indeksy złożone to takie, które posiadają więcej niż jedno indeksowane pole
• Wszystkie powyższe metody odczytu tak samo dotyczą indeksów złożonych
• Struktura indeksu złożonego wygląda podobnie, jak w prostych, z tą różnicą, że przed
rowid znajdują się pozostałe klucze:
’Kowalski’,’Manager’,rowid
’Kowalski’,’Analityk’,rowid
‚Kowalski’,’Programista’, rowid
• I tak zapytanie:
SELECT * FROM tabela WHERE nazwisko = ‚Kowalski’ AND stanowisko = ‚Analityk’
– zwróci wiersz dla rowid wytłuszczonego wpisu.
• W przeciwnym przypadku, czyli gdyby nie było w indeksie stanowiska:
’Kowalski’,rowid
’Kowalski’,rowid
‚Kowalski’,rowid
• trzeba byłoby przejrzeć 3 rekordy i dopiero zwrócić ten, który spełnia warunki wyszukiwania
Copyright © 2011 Accenture All Rights Reserved.
15
Indeksy proste i złożone (na przykładzie drzew
„B-trees”)
Kolejność pól w indeksie złożonym ma znaczenie:
– Jeżeli mamy indeks na 2 polach, np. [ płeć , data_urodzenia ], wówczas indeks zadziała
poprawnie, gdy w zapytaniu użyjemy jednego z dwóch poniższych warunków:
płeć = ’Mężczyzna’ oraz data_urodzenia = 12/04/1974
lub
płeć = ’Mężczyzna’
– W przypadku, gdy będziemy wyszukiwać tylko po dacie urodzenia, wówczas Oracle
użyje tego samego indeksu, jednak trochę w inny sposób - zostanie użyta wcześniej
niewymieniona metoda „Skip Scan Index” (od Oracle’a wersji 9i), która to służy do
pominięcia pierwszego pola indeksu i będzie korzystać tylko z drugiego pola
Copyright © 2011 Accenture All Rights Reserved.
16
Indeksy proste i złożone (na przykładzie drzew
„B-trees”)
– Takie rozwiązanie jest akceptowalne w przypadku, gdy pierwszym polem jest pole z
mała licznością (tak jak w przykładzie), gdzie mamy 2 wartości, ponieważ Oracle
najpierw „odwiedzi lewą gałąź”, gdzie indeksowane są wartości dla płeć = ’Mężczyzna’
a następnie „odwiedzi prawą gałąź” i połączy wyniki. Konceptualnie wykona mniej
więcej taką operację:
SELECT * FROM tabela WHERE płeć = ‚Mężczyzna’ AND data_urodzenia =
12/04/1974
UNION ALL
SELECT * FROM tabela WHERE płeć = ‚Kobieta’ AND data_urodzenia = 12/04/1974
– W tym przypadku można jeszcze pokusić się o takie zastosowanie, jednak w przypadku
gdyby pierwsze pole było np. typu „nazwisko”, wówczas wystarczy wyobrazić sobie
konceptualny przypadek, gdzie dla wszystkich „gałęzi” (nazwisk) wykonywane jest
pojedyncze zapytanie i na końcu złączenie – wydajność drastycznie spada
Copyright © 2011 Accenture All Rights Reserved.
17
Zalety oraz wady indeksów
– Operacje INSERT / UPDATE / DELETE wymagają ingerencji w dane, powodując
„reorganizację” indeksu i jego przebudowę
– Pomimo zajętości obszaru przez indeksy, można je kompresować, w podobny sposób,
tak jak robią to aplikacje typu ZIP (algorytm jest oczywiście inny)
– Można je przebudowywać, żeby ponownie „wyważyć” drzewo, dla jak najlepszej jego
„rozłożystości” i tym samym dostępu do jego wartości
– Nie zawsze należy używać indeksów. Czasami lepiej jest przeskanować całą tabelę,
gdyż będzie to miało większą wydajność, niż najpierw użycie indeksu, a następnie na
jego podstawie, mając wszystkie rowid, odwiedzać poszczególne rekordy tabeli.
– Z drugiej strony, jeżeli tak przeskanowana tabela, ma być złączona z inną tabelą, gdzie
potrzebne jest jej posortowanie – lepiej rozważyć przeskanowanie całego indeksu, który
przecież można odczytać w kolejności rosnącej / malejącej
Copyright © 2011 Accenture All Rights Reserved.
18
Statystyki oraz histogramy
–
–
–
Statystyki optymalizatora są bardzo ważnym wyznacznikiem dla osób piszących zapytania lub
utrzymujących całe rozwiązania biznesowe, ponieważ pomagają zobrazować strukturę fizyczną
tabel, indeksów, wartości kolumn, rozkład danych dla pola itp.
Za pomocą statystyk możliwe jest oszacowanie kosztu zapytania, czyli ile zasobów będzie
potrzebowała maszyna, aby zwrócić kompletne wyniki. Takie estymaty dostępne są dzięki tzw.
planom zapytania, które zostaną omówione na kolejnym wykładzie
Statystyki optymalizatora zawierają takie informacje jak:
• Statystyki tabel:
– Liczba rekordów w danej tabeli
– Liczba bloków wykorzystana przez tabelę
– Średnia długość rekordu wyrażona w bajtach
• Statystyki kolumn:
– Ilość unikalnych wartości pola (NDV – Number of Distinct Values)
– Ilość wartości NULL w polach
– Rozkład danych (histogramy) – dostarczają dokładne estymaty (szacunki) dystrybucji wartości danych w polach
• Statystyki indeksów:
– Ilość bloków liści (leaf blocks)
– Wysokość drzewa (poziomy – levels)
– Clustering factor – liczba reprezentująca, w jakim stopniu dane sa losowo rozmieszczone w tabeli
• Statystyki systemu:
– Wydajność wejść / wyjść (I/O)
– Wydajność CPU
Copyright © 2011 Accenture All Rights Reserved.
19
Statystyki oraz histogramy
–
–
–
Statystyki można aktualizować automatycznie lub manualnie za pomocą pakietu dostarczonego
przez firmę Oracle DBMS_STATS:
• Procedura GATHER_INDEX_STATS – zbiera informacje o indeksie
• Procedura GATHER_TABLE_STATS – zbiera informacje dla tabeli, jej kolumn oraz indeksów
• Procedura GATHER_SCHEMA_STATS – zbiera informacje dla całego schematu
• Procedura GATHER_DICTIONARY_STATS – zbiera informacje dla wszystkich obiektów słownika
• Procedura GATHER_DATABASE_STATS – zbiera informacje dla wszystkich obiektów bazy danych
Na podstawie wygenerowanych statystyk, można zadecydować np. czy warto przebudować indeks, ponieważ jego
aktualna struktura została trochę zdegenerowana (zaburzona), przez co wydajność procesów spada
Histogramy można podzielić na 2 typy:
• Height-Balanced
–
w tym przypadku rozkład danych polega na podzieleniu histogramu na tzw. „wiadra” (buckets), gdzie dla poniższego przykładu istnieją
wartości od 1 do 100, idealnie rozłożone:
–
Natomiast na poniższym przykładzie ten sam typ histogramu, jednak z losowymi wartościami z zakresu od 1 do 100
• Frequency
–
W tym przypadku istnieje tyle „wiader” ile jest wartości – dla każdej z wartości (wiadra) przypisana jest ilość wystąpień wartości w polu
Copyright © 2011 Accenture All Rights Reserved.
20
Ćwiczenie
1. Wskazać, dla których pól (ze struktury bazy z wykładu 1) należy
założyć indeksy oraz jakiego rodzaju.
2. Sprawdzić aktualność statystyk dla tabel (pole
user_tables.last_analyzed) i indeksów (pole
user_indexes.last_analyzed) oraz histogramów (tabela
user_tab_histograms).
3. Wykonać SELECT na tabeli pracownik wg pola pr_nazwisko.
4. Sprawdzić plan zapytań (w aplikacji SQL Developer).
5. Odszukać informacje o zapytaniu (v$sql) i plan jego wykonania
(v$sql_plan)
6. Dodać indeks na polu pracownik.pr_nazwisko.
7. Odśwież statystyki dla dodanego indeksu.
8. Powtórzyć czynności z punktów 3,4,5.
Questions & Answers