Korzystanie z indeksów i wykonywanie skanów tabel to dwa podstawowe podejścia do dostępu do danych w bazie danych, każda z odrębnymi kompromisami w zależności od sytuacji.
Indeks w bazie danych to struktura danych, która umożliwia szybsze wyszukiwanie, umożliwiając systemowi szybkie zlokalizowanie wierszy bez skanowania każdego wiersza w tabeli. Większość relacyjnych baz danych wykorzystuje struktury drzew B+ dla indeksów, które organizują klawisze i wskaźniki w formie drzewa. Umożliwia to wyszukiwanie, wstawki i delecje w złożoności czasu logarytmicznej $$ o (\ log n) $$, co zwykle jest znacznie szybsze niż skanowanie całej tabeli z złożonością $$ o (n) $$. Indeksy mogą być klastrowane lub nie-klastrowane, z klastrowymi indeksami przechowującymi dane w kolejności fizycznej, poprawia wydajność skanowania zasięgu kosztem dodatkowych kosztów modyfikacji danych. Indeksy mogą być również kompozytowe, częściowe, filtrowane lub oparte na skrócie, dostrojone do określonych wzorów zapytań.
Natomiast skanowanie tabeli (lub pełne skanowanie tabeli) odczytuje każdy wiersz w tabeli sekwencyjnie, niezależnie od selektywności zapytania. Obejmuje to skanowanie wszystkich bloków danych tabeli i jest często uważana za najdroższą metodę dostępu, ponieważ przetwarza więcej danych niż to konieczne. Jednak skany tabeli mogą dobrze działać w niektórych przypadkach. Na przykład, gdy zapytania pobierają duży odsetek wierszy, narzut używania indeksu (który często wymaga dodatkowych wyszukiwań dla rzeczywistych wierszy) może przekroczyć koszt skanowania całej tabeli raz. Skany tabeli mogą korzystać z odczytów wielopłaskich, które umożliwiają odczyt dużych fragmentów danych o mniejszej liczbie operacji we/wy, zmniejszając w ten sposób opóźnienie w porównaniu z odczytaniem wielu poszczególnych bloków losowo wymaganych przez skany indeksu.
Jeden główny kompromis obejmuje selektywność i wielkość zestawu danych zwróconych przez zapytanie. Jeśli zapytanie odlicza się do niewielkiej liczby wierszy (wysoka selektywność), indeksy ogólnie przewyższają skanowanie tabeli, ponieważ muszą uzyskać dostęp do odpowiednich danych. Jednak wraz ze wzrostem odsetka wierszy, koszt skanów indeksu wzrasta, ponieważ może być wymagane wiele kluczowych wyszukiwania, a silnik bazy danych musi wykonywać dodatkowe losowe operacje we/wy. Przy pewnym progu często około 10-20% wierszy tabeli, ale zależnych od szerokości danych i sprzętu, pełne skanowanie tabeli staje się bardziej wydajne. Wynika to z faktu, że koszty skanowania pozostają stałe, niezależnie od selektywności, po prostu przeczytaj tabelę sekwencyjnie raz.
Skany indeksu zazwyczaj odczytują mniej stron niż skanowanie tabeli, gdy zakryte kolumny są mniej lub bardziej kompaktowe niż pełne wiersze tabeli. Na przykład indeks może obejmować tylko indeksowane kolumny bez pełnych danych wierszy tabeli, dzięki czemu jest cieńszy i umożliwiając dopasowanie większej liczby wierszy na każdej stronie bazy danych. Zmniejsza to obciążenie we/wy podczas skanowania indeksu w porównaniu do skanowania całej tabeli danych. Ponadto niektóre indeksy mogą być filtrowane (indeksy częściowe), aby wykluczyć nieistotne wiersze, co dodatkowo zmniejszając ślad skanowania.
Z drugiej strony, pełne skany tabeli piszą mniej obciążenia po stronie konserwacji bazy danych. Indeksy wprowadzają koszty ogólne podczas operacji modyfikacji danych, takich jak wstawka, aktualizacja i usuwanie. Każda zmiana w tabeli wymaga aktualizacji indeksów, czasami prowadząc do zwiększonego opóźnienia zapisu i narzutów pamięci, szczególnie jeśli wiele indeksów istnieje w tabeli. Ten koszt narzutowy może również wpływać na współbieżność i prowadzić do rywalizacji w ciężkich środowiskach zapisu. Zatem skany tabeli, które po prostu odczytują dane w naturalnej kolejności bez dodatkowej konserwacji struktury, unikaj tych kosztów.
Kolejnym ważnym czynnikiem jest efekt buforowania i właściwości sprzętowych. Skanowanie tabel korzystają z sekwencyjnego we/wy i prefettowania, umożliwiając systemowi efektywne odczytywanie wielu ciągłych bloków, często z pamięci, jeśli są buforowane. I odwrotnie, skanowanie indeksu wymagają losowych we/wy, aby pobrać różne bloki danych, szczególnie jeśli skanowanie indeksu musi sprawdzić wskaźniki wiersza do przechowywania sterty. Może to sprawić, że skany indeksu wolniejsze w systemach z wolniejszą losową wydajnością we/wy, chociaż SSD i duże pule pamięci zawężają tę lukę. Sytuacja może również zależeć od specyfiki, takich jak równoległość i możliwości wielokretetu silnika bazy danych, w których skanowanie tabel równoległych mogą znacznie zwiększyć przepustowość.
Ponadto wewnętrzny układ fragmentacji i fizycznego przechowywania wpływają na kompromisy wydajności. Skanowanie tabel na tabelach zorganizowanych przez szereg może cierpieć z powodu przekierowanych zapisów, w których rzędy przeniosły się na różne strony z powodu aktualizacji, pogarszając wydajność skanowania. Indeks klastrowy, które przechowują dane posortowane według klucza, mogą uniknąć tego problemu, a czasem tworzą „skanowanie tabeli” równoważne skanowanemu skanowaniu indeksu. Jednak korzyści mają koszty drogich kolejności wierszy podczas ciężkich danych.
Z perspektywy optymalizatora zapytania decyzja między skanowaniem indeksu a skanowaniem tabeli jest zwykle podejmowana przez modele szacowania oparte na kosztach, biorąc pod uwagę statystyki dystrybucji danych, liczby wierszy i kosztów sprzętowych. Optymalizator równoważy procesor, we/wy i koszty pamięci, aby wybrać najbardziej wydajną ścieżkę dostępu. Na decyzje te mogą mieć wpływ czynniki takie jak dostępna pamięć, stan buforowania i wzorce zapytań. Nie ma ustalonego progu między kiedy użyć jednego lub drugiego; Punkt crossovera różni się w zależności od systemu i obciążenia.
Podsumowując, kompromisy między użyciem indeksów a skanami tabel obejmują:
- Wydajność vs. wolumin danych: indeksy przewyższają się podczas filtrowania do kilku wierszy; Skanowanie tabeli mogą być lepsze w przypadku dużego wyszukiwania danych.
- Wzory we/wy: skany indeksu powodują losowe odczyty we/wy; Skanowanie tabeli korzystają z sekwencyjnych odczytów we/wy i wielopłasowości.
- Koszty utrzymania: indeksy zwiększają koszty działania zapisu ze względu na aktualizacje struktur indeksu; Skany stołowe tego nie ponoszą.
- Wydajność przechowywania: Indeksy mogą być kompaktowe, pokrywając mniej kolumn; Skanowanie tabeli przetwarzają pełne wiersze i potencjalnie więcej danych.
- Efekty buforowania: Skany tabeli mogą skutecznie wykorzystać buforowanie danych, szczególnie w przypadku dużych odczytów sekwencyjnych; Skany indeksu mogą nie przynieść tak dużej korzyści z powodu dostępu losowego.
- Decyzja optymalizatora: Optymalizatory zapytań oparte na kosztach dynamicznie wybierają między tymi opcjami na podstawie statystyk zapytania i specyfiką obciążenia.
- Wpływ układu danych: tabele sterty mogą ponieść kary, takie jak przekazane rekordy podczas skanów; Indeksy klastrowane organizują dane fizycznie, ale zwiększają koszty aktualizacji.