Inspiracja , wiedza , realizacja
Jsystems

W przebudowie

Login



Java

Oracle

Linux

Android

PostgreSQL

Microsoft SQL Server

Indeksy

Data dodania: Jun 16, 2016
Data aktualizacji: Jun 16, 2016

Proste indeksy B-Tree


Aby mieć na czym pracować stworzymy sobie prostą tabelę i wypełnimy ją danymi:


create table duza (

id serial primary key,

wartosc integer not null

);


insert into duza (wartosc) values (generate_series(1,2000000));


Odświeżymy też statystyki dla niej:


vacuum analyze duza;


Jak widać poniżej, tabela ma dwa miliony wierszy.




Jej zawartość również nie jest specjalnie złożona, pokazuję ją byś widział mniej więcej co możemy w niej znaleźć i jaki jest układ danych.




Sprawdźmy więc szacunkowy koszt odczytania 100 wierszy filtrując po drugiej kolumnie (nie ma na niej indeksu).


explain select * from duza where wartosc between 1000000 and 1000100;




Na potrzeby porównania sprawdźmy też czas pobrania samej kolumny wartość, ale posortowanej:




Przyjrzyj się zaznaczonym fragmentom. Pierwszy zaznaczony fragment to koszt sortowania. Zauważ że koszt samego sortowania jest niemal dziesięciokrotnie wyższy od kosztu odczytu całej tabeli (linia 4)! Wynika to z działania które jest zakreślone w linii 3. Planer oszacował, że dane w tej tabeli nie zmieszczą się w pamięci podczas sortowania, zadecydował więc o sortowaniu z użyciem dysku. Jak powszechnie wiadomo, wszelkie operacje I/O na dysku są wrogiem wydajności, ponieważ odczyt z dysku jest znacznie wolniejszy od odczytu z pamięci.Czas wykonania to 1,2s.

Założymy teraz zwykły indeks na kolumnie wartość. Taki indeks będzie posortowany wg wartości pierwszej kolumny:


create index przykladowy on duza(wartosc);


I porównujemy koszt odnalezienia danych:




Sprawdźmy więc różnicę w odczycie danych posortowanych:




Zatrzymajmy się na chwilę w linii 1. Zauważ że w miejsce sortowania mamy zwykły odczyt indeksu. Nie ma potrzeby sortowania, ponieważ indeks już jest posortowany wg kolumny na którą został założony. Odeszły nam więc również kosztowne operacje I/O. Czas wykonania – 0,24 s – czyli prawie 5cio krotnie szybciej. Weź pod uwagę że wraz ze wzrostem wielkości tabeli, będzie ten dysonans wzrastał.


Jeszcze ciekawostka. Z zapytania pozbyłem się sortowania, a planer zadecydował o użyciu skanu sekwencyjnego po tabeli zamiast indeksu. Wynika to z większego kosztu odczytu samych obiektów – nie uwzględniając sortowania.




Aspekt na który powinni zwrócić uwagę szczególnie użytkownicy baz danych Oracle – w PostgreSQL trzeba zapomnieć o wielu przyzwyczajeniach.... Zadajemy zapytanie kształtu:


select count(*) from duza;


Wiemy że mamy jeden indeks na kolumnie wartość a dodatkowo jest ona oznaczona jako NOT NULL, a ponadto mamy jeszcze indeks stworzony automagicznie w związku z założeniem klucza głównego. Czego można by się spodziewać w Oracle? Zapewne zliczenia wartości na podstawie któregoś z tych indeksów. PostgreSQL natomiast zrobi pełny skan po tabeli:




Dopiero gdy dodamy jakiś warunek odnoszący się do którejś z zaindeksowanych kolumn użyje skanu po indeksie:




W tym przypadku jeszcze jest decyzja o odczycie z użyciem indeksu, ale nie zawsze tak będzie nawet jeśli indeks będzie istniał. Zależy to przede wszystkim od tak zwanej selektywności, czyli stosunku liczby wybieranych wierszy do całości. Tutaj pobieramy 0,00005 część całości danych, selektywność jest jednak bardzo niska. Gdybyśmy wybrali więcej danych, może się okazać że wydajniejszy będzie odczyt sekwencyjny całej tabeli:




Dokumentacja niestety milczy (stan na początek 2016 roku) na temat tego jaki jest próg przejścia ze skanu po indeksie na skan po tabeli, z moich obserwacji wynika jednak że duży wpływ na ten próg ma stosunek parametrów random_page_cost do seq_page_cost.


Indeksy wielokolumnowe


Oczywiście możemy też założyć indeksy wielokolumnowe, np. w przypadku filtrowania po kilku kolumnach:


explain analyze select * from duza where wartosc>1000000 and id<1000100;


Nic nie stoi na przeszkodzie by połączyć dwa indeksy, jednak będzie to na pewno droższe niż odczyt jednego podwójnego, ale za to tańsze niż odczyt sekwencyjny. Tutaj przykładowo zakładam indeks na obu kolumnach tabeli:


create index omg on duza(wartosc,id);

A plan wykonania wygląda teraz tak:




Indeksy B-tree mogą mieć maksymalnie 32 kolumny.


Indeksy unikalne


Indeksy mogą również wymuszać unikalność w kolumnie lub kombinacji wartości z kilku kolumn:


create unique index unikalny on duza(wartosc);

insert into duza values (2000001,1);



Możemy również stworzyć indeks unikalny wielokolumnowy:


create unique index mc_double on duza(id,wartosc);


W takim przypadku będzie musiała być zapewniona unikalność kombinacji wartości. Czyli np. może być 1000 Janów i 1000 Kowalskich, ale tylko 1 Jan Kowalski.


Mała uwaga dla Oracle'owców: Czy w Oracle mogliśmy założyć unikalny indeks na kolumnie na której jest już założony indeks nieunikalny? Nie mogliśmy ;) Przyjrzyj się ostatnim operacjom – w PostgreSQL się to udaje.


Indeksy częściowe


Wyobraźmy sobie sytuację w której mamy jakąś ogromną tabelę, ale najczęściej wyciągamy te same wiersze z tego samego zakresu. Moglibyśmy założyć indeks na całą długość kolumny, ale byłoby to marnowanie miejsca. W PostgreSQL istnieją indeksy częściowe, obejmujące tylko wskazaną część kolumny. Są one oparte o warunek WHERE. Poniżej przykład na naszej tabeli „duza”. Tworzę indeks obejmujący tylko wiersze o wartosci w kolumnie „Wartosc” w zakresie 1000000-1000500. Chwilę później uruchamiam zapytanie z którego warunku WHERE wynika, że wiersze o które pytam mają w kolumnie „wartosc” wartosc mieszcząca się w zakresie objętym przez indeks. Zauważ że zakres podany w zapytaniu jest różny od tego w indeksie, jednak się w nim zawiera. Jak widzisz planer zadecydował o wykorzystaniu naszego nowego indeksu.


create index sam_srodek on duza(wartosc) where wartosc between 1000000 and 1000500;

explain analyze select count(*) from duza where wartosc between 1000100 and 1000400;



Indeksy a NULLe


A tutaj może być pewne zaskoczenie dla osób dotychczas korzystających z baz danych Oracle. Od wersji 8.3 indeksy B-Tree mogą przechowywać Nulle i mogą być użyte do ich wyszukania lub ominięcia. Do naszej dużej tabelki dodaję jeszcze jedną kolumnę. Może ona przyjmować nulle. Zakładam na nową kolumnę indeks. Dla części wierszy ustawiam w niej jakąś wartość, a następnie sprawdzam plan wykonania zapytania zwracającego liczbę wierszy z nullem w tej kolumnie. Został użyty nowo utworzony indeks.


alter table duza add mozenull integer;

update duza set mozenull=1 where id>1000000;

create index null_tu_null_tam on duza(mozenull);

explain analyze select count(*) from duza where mozenull is null;



Indeksy funkcyjne


Jeśli na jakiejś kolumnie często w warunku WHERE dodatkowo stosujemy jakąś funkcję lub przeliczenie np.:


explain analyze select * from duza where trunc(wartosc/2)>900000;


możemy założyć indeks który zawierał będzie nie wartość bezpośrednią z kolumny a takie samo przeliczenie np.:


create index polowa on duza(trunc(wartosc/2));


Dzięki takiemu zabiegowi nie będzie potrzeby wykonywania tej operacji arytmetycznej „w locie” podczas wykonywania zapytania, a wartość od razu przeliczona zostanie odczytana bezpośrednio z indeksu:



Problemy wynikające z użycia indeksów


Konieczność aktualizacji


Z indeksami wcale nie jest tak różowo jak mogłoby się wydawać. Z jednej strony mogą nam pomóc w przyspieszeniu odczytu danych, ale weź też pod uwagę że takie indeksy trzeba będzie aktualizować przy wykonywaniu operacji UPDATE, DELETE i INSERT. To powoduje wydłużenie tych operacji. Nie zakładaj więc więcej indeksów niż jest niezbędne i nie stosuj ich tam gdzie korzyść z ich zastosowania jest znikoma.


Zajęte miejsce


Indeksy nie wiszą w powietrzu. Muszą być przechowywane na dysku podobnie jak tabele. Jeśli więc np. utworzysz na jakiejś tabeli indeksy na każdej kolumnie, musisz liczyć się z tym, że ilość zajmowanego miejsca na potrzeby danej tabeli oraz jej indeksów przynajmniej się podwoi.


Blokady podczas tworzenia i odbudowywania


Podczas budowania lub odbudowywania indeksu nakładana jest blokada na wszystkie wiersze których dotyczy. Wydawać by się mogło że to nic poważnego, a tymczasem wyobraź sobie tabelę wielkości kilkuset milionów wierszy. Zakładanie indeksu na takiej tabeli może trwać bardzo długo. Wszystkie transakcje które będą w tym czasie próbowały założyć swoją blokadę będą czekały (nie zostanie zgłoszony błąd) na zakończenie budowania indeksu. Weź też pod uwagę, że takie transakcje mogły wcześniej zablokować inne zasoby. Aby ten problem „obejść” możesz zastosować współbieżne tworzenie indeksu:


create index concurrently magic_trick on duza(wartosc) ;


Tworzenie takiego indeksu trwa jednak nieco dłużej od tworzenia zwykłego. Inny jest też sposób działania. PostgreSQL najpierw skanuje tabelę na której ma zostać założony indeks, odczytuje potrzebne mu wartości, następnie tworzy indeks i ponownie skanuje tabelę w poszukiwaniu wierszy które zostały dodane, skasowane lub zmienione w międzyczasie.