Najpotrzebniejsze informacje na temat indeksów

Indeksy stanowią niezwykle ważny obiekt bazodanowy, którego zadaniem jest przyspieszenie dokonywania operacji na danych. Podstawowe informacje na temat indeksów przedstawiłem w formie wypunktowania poniżej. Wpis ten nie jest wytłumaczeniem od początku do końca jak działają indeksy, ponieważ takich wpisów istnieje wiele i można bez problemu takie odnaleźć. Wpis ten stanowi po prostu luźne notatki na temat klasycznych indeksów wierszowych.

Uwagi/ciekawostki

  • Tabela bez indeksu klastrowego nosi nazwę sterty (ang. heap), w której dane są przechowywane bez ustalonego porządku, o tym czy dany obiekt jest stertą czy tabelą możemy dowiedzieć się w widoku systemowym sys.indexes.

    Źródło: https://gallery.technet.microsoft.com/scriptcenter/List-all-heap-tables-7ffaea35
  • Na poziomie liścia indeksu klastrowego znajdują się dane.
  • Na poziomie liścia indeksu nieklastrowego znajduje się w przypadku:
    – sterty tzw. Row Identifier – RID
    – B-drzewa – uniquifier
  • Zarówno indeks klastrowy jak i indeks nieklastrowy są zorganizowane w strukturę B-drzewa a przeszukiwane są za pomocą algorytmu połowienia binarnego.
  • Współczynnik wypełnienia (ang. fill factor) określa stopień zapełnienia liści danymi w drzewie indeksu. Na jednym biegunie posiada rozsądny rozmiar bazy danych i minimalizacja liczby operacji wejścia/wyjścia, a na drugim biegunie konieczność dokonywania podziału pełnej strony i przesunięcia danych w inne miejsce, co powoduje fragmentację.
  • Selektywność indeksu (ang. index selectivity) to miernik pozwalający na ocenę przydatności indeksu i stanowi stosunek unikalnych wartości kolumny do liczby wszystkich wierszy.
  • Od wersji SQL Server 2008 wprowadzono tzw. indeksy filtrowane (ang. filtered indexes), które pozwalają na zastosowanie filtru względem rekordów, które chcemy zindeksować. Pozwala to na oszczędność miejsca na dysku i pozwala na szybszą modyfikację danych.
  • Fragmentacja indeksów pojawia się wtedy, gdy dane są modyfikowane. Fragmentację możemy podzielić na:
    – wewnętrzną – powstaje przy operacji modyfikacji danych, które mogą pozostawić wolne miejsce na stronie
    – zewnętrzną – powstaje przy operacji wstawiania rekordu na określoną stronę indeksu przy braku miejsca
  • W celu defragmentacji indeksów należy dokonać operacji REBUILD lub REORGANIZE, o czym można byłoby napisać kolejny długi wpis.

Ograniczenia

  • Dana tabela może posiadać tylko jeden indeks klastrowy, ponieważ wyznacza on fizyczną organizację danych.
  • Dla jednej tabeli można utworzyć 249 (do wersji 2005 włącznie), 999 (od wersji 2008) indeksów nieklastrowych.
  • Jeden indeks może obejmować maksymalnie 16 kolumn, a całkowita ilość danych w tych kolumnach nie może przekroczyć 900 bajtów (limit nie dotyczy kolumn dołączanych).
  • Kolumny dołączone można stosować tylko dla indeksu nieklastrowanego, ponieważ w przypadku indeksu klastrowanego dane są na poziomie liścia.
  • Tworząc indeksy ponosimy dwojaki koszt: indeksy zajmują dodatkową przestrzeń na dysku; ponadto każdy dodatkowy indeks nieklastrowany wydłuża operację modyfikacji danych (INSERT, UPDATE, DELETE).
  • Nie można utworzyć indeksu dla kolumn typu bit, txt, ntext, image.

6 Comments Add yours

  1. Gość napisał(a):

    W internecie krąży sporo różnych opinii dotyczących defragmentacji indeksów. Jak zwykle są gorący zwolennicy defragmentacji, jak i ci którzy negują ją. To chyba jest częsty temat do dyskusji. Jeżeli chodzi o spowolnienie operacji modyfikacji danych (ich importu), to tutaj http://www.mobilo24.eu/przyspieszanie-importu-danych-poprzez-wylaczenie-indeksow-tabel/ jest artykuł o tym, że można temu zaradzić czasowo wyłączając indeksy, a po imporcie danych trzeba te indeksy odbudować. Domyślam się że nie chodzi tu o import albo modyfikację stu czy tysiąca wierszy, lecz pewnie różnica szybkości zauważalna jest dopiero przy milionach rekordów?

  2. Gość napisał(a):

    Jako uzupełnienie, porównanie zastosowania tabel z indeksem klastrowym, z tabelami bez tego indeksu:
    https://sqlsunday.com/2016/09/01/compelling-case-for-heaps/

    1. piotrziuzianski napisał(a):

      Dzięki za linka, autor wpisu syntetycznie podsumowuje różnice między stertą a tabelą 🙂

  3. Adrian Chodkowski napisał(a):

    @Gość – istnieje fragmentacja wewnętrzna i zewnętrzna. Z zewnętrzną w środowiskach zwirtualizowanych i macierzach RAID ciężko walczyć. Jeżeli chodzi natomiast o integrację wewnętrzną no to ma ona ogromne znaczenie gdyż polega ona właściwie na tym, iż strona danych w części jest pusta, a my ją odpytując ciągniemy „powietrze” i to właśnie powietrze zajmuje nam I/O i RAM.

    W przypadku importu danych do hurtowni danych to ważne żeby operacja była minimalnie logowana i to jest główna przyczyna wydajnego wprowadzania danych. Jakiekolwiek indeksy rowstore będą je spowalniały – warto rozważyć clustered columnstore w takich przypadkach.

    @Piotr fajny artykuł, przy okazji nonclustered index na tabeli clustered ma klucz indeksu klastrowanego, a nie uniquefier przy okazji polecam przejrzeć moją prezentację z niedawnej sesji na ten temat http://pl.seequality.net/seequality-4-indexes-how-it-works/

    1. piotrziuzianski napisał(a):

      Warto tylko wspomnieć, że w przypadku indeksów kolumnowych niestety wymagane jest posiadanie Microsoft SQL Server w wersji Enterprise.
      Dzięki za polecony wpis, bardzo fajna prezentacja – zwięzła i na temat 🙂
      Czy mógłbyś rozwinąć jak wygląda wskazanie do indeksu clustered na poziomie liścia indeksu non clustered? To jest zależne od tego czy indeks jest unikalny czy też nie?
      https://technet.microsoft.com/en-us/library/ms177484(v=sql.105).aspx

  4. Adrian Chodkowski napisał(a):

    Zależy od unikalności bo wtedy pojawia się dodatkowe unikalne pole niejako rozszerzające nasz klucz.

    W przypadku np. gdy mamy nieunikalne wartości klucza indeksu clustered 1,1,2 to „pod spodem” ten klucz składa się tak naprawdę z kombinacji (1,0),(1,1),(1,2),(2,0),(3,0) i po tym się odwołuje się każdy na nim założony indeks nieklastrowany. Taką kombinację klucz+uniquifier w uproszczeniu możemy nazwać kluczem indeksu.

    W tym wszystkim nie będziemy mieli świadomości istnienia dodatkowe pola dającego unikalność ale ono daje narzut 4 bajty i w przypadku ogromnej ilości duplikatów na polu wskazanym przez nas jako klucz możemy nawet wyczerpać limit liczb jakie mieszczą się w 4 bajtach czyli możemy przekroczyć zakres jaki daje nam integer i nie wstawimy kolejnego duplikatu:D

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *