Tradycyjne indeksy w sql server

Tradycyjne indeksy w sql server

• 1.
  TRADYCYJNE INDEKSY W SQL SERWER TomaszWaloszek
• 2.
  Agenda  Sterta  Indeksyw bazie danych  Typy indeksów  Współczynnik wypełnienia  Statystyki  Wpływ indeksów na zapytania
• 3.
  Sterta  Stertę tworzą nieuporządkowanedane tabeli  Dane te zapisane są na stronach, które zawierają odnośniki do stron poprzedniej i następnej  Wyszukiwanie danych w tego typu strukturze jest bardzo nieefektywne Z uwagi na to, że stertę tworzą fizyczne dane tabeli, to terminów „tabela bez indeksu grupującego” i „sterta” możemy używać zamiennie.
• 4.
  Sterta
• 5.
  Po co tworzyćindeksy? Jedynym powodem tworzenia indeksów jest poprawa wydajności bazy danych. Indeksy, tak jak statystyki, nie wpływają na wynik instrukcji języka Transact-SQL, a jedynie na plan i koszt ich wykonania. Indeksacja jest jednym z elementów strojenia bazy danych polegającym na skróceniu czasu wykonania zapytania przesłanego do SQL Serwer.
• 6.
  Indeks klastrowany (grupujący) Książka telefoniczna wg. Nazwisk  Posortowana sterta z dodatkową strukturą ułatwiającą posortowanie i wyszukiwanie  Przykład szukamy po nazwisku jest szybko, jak szukamy numer telefonu wówczas pozostaje nam mozolne szukanie strona po stronie
• 7.
  Indeks klastrowany Indeks grupującyjest to struktura drzewiasta, a dokładniej B-drzewo Na tabeli można założyć tylko jeden indeks typu CLUSTERED, gdy tak się stanie mówimy, że tabela jest indeksem grupującym.
• 8.
  Indeks klastrowany Restrykcje:  maksymalnie16 kolumn  maksymalnie 900 bitów Zalety:  Szybkie wyszukiwanie po kolumnach z klucza  Wymusza unikatowość wierszy Wady:  Zajmują miejsce na dysku  Zwiększają obciążenie systemu
• 9.
  Wyszukiwanie binarne
• 10.
  Indeksie grupującym -wyszukiwanie  Szukamy rekordów dla Customer Number = 4:
• 11.
  Indeks nieklastrowany (niezgrupowany) Odpowiednik indeksu w książce  Jest zakładany na pole, które ma unikatowe wartości w każdym rekordzie lub które nie jest polem klucza i posiada powtarzające się wartości
• 12.
  Indeks nieklastrowany  Indeksniegrupujący jest to także B-drzewo, jednakże strony tego indeksu nie są danymi tabeli, lecz ich kopią  Liście indeksu niegrupującego nie zawierają danych, znajdują się na nich jedynie wskaźniki do tabeli źródłowej
• 13.
  Indeks nieklastrowany –dla sterty
• 14.
  Indeks nieklastrowany –dla tabeli grupującej
• 15.
  Indeks nieklastrowany -wyszukiwanie
• 16.
  Selektywność indeksu  Parametrokreślający, czy indeks na określonych kolumnach może być przydatny.  Wartość tego parametru wyliczamy ze wzoru: S= U/W gdzie: S – selektywność U – liczba unikalnych wartości dla kolumny W – liczba wszystkich wierszy w kolumnie
• 17.
  Do których kolumnnależy tworzyć indeksy? Dla indeksów niezgrupowanych :  przechowują wartości częściej odczytywane, niż modyfikowane,  wykorzystywane są do łączenia lub wyszukiwania danych,  przechowują różnorodne wartości. Nie ma natomiast możliwości, aby założyć indeks dla kolumn przechowujących dane typu txt, ntext, image
• 18.
  Indeksy niegrupujące zkolumnami zawieranymi  Indeks niegrupujący z kolumnami zawieranymi budową przypomina zwykły indeks niegrupujący  Indeks z kolumnami zawieranymi posiadają one nie tylko wskaźnik do tabeli źródłowej lecz także kopię źródłowych danych  Dzięki tej różnicy używając indeksu z kolumnami zawieranymi jesteśmy w stanie pozbyć się kosztownych operacji typu „KEY LOOKUP”
• 19.
  Indeks filtrowany  Indeksyfiltrowane zostały wprowadzone wraz z SQL Server 2008  Jak wskazuje nazwa możemy w nich użyć filtru w celu wybrania dokładnie tych rekordów, które chcemy zindeksować  Dzięki temu, że indeks zawiera tylko wybrane rekordy to zajmuje mniej miejsca na dysku i działa szybciej
• 20.
  Współczynnik wypełnienia (fillfactor)  Opcja określa stopień zapełnienia liści w drzewie indeksu (stron indeksów) w momencie  tworzenia indeksu  przebudowania indeksu  Wartości są od 0 do 100 (0 i 100 oznaczają 100% zapełnienie)  Domyślna wartość to 0, można ją zmienić dla całego serwera poleceniem sp_configure
• 21.
  Fragmentacja indeksów  Fragmentacjapojawia się przy modyfikacji tabeli  Rodzaje fragmentacji:  Wewnętrzna  Zewnętrzna
• 22.
  Widok indeksowany  Widokimożemy tworzyć nie tylko na tabelach możemy je tworzyć również bezpośrednio na widokach.  Ponieważ dane dla takiego widoku są bezpośrednio przechowywane na stronach, to widok taki nazywamy widokiem zmaterializowanym  Widok, który chcemy zindeksować musi spełniać szereg wymagań, najważniejsze z nich to:  używanie w widoku tylko i wyłącznie tabel jako źródeł danych  widok musi być stworzony z opcją SCHEMABINDING
• 23.
  Strategia Indeksowania  Dobrąpraktyką jest zakładanie indeksów grupujących na wszystkich tabelach w naszej bazie  Starajmy się aby klucz indeksu grupującego był jak najkrótszy  Zawsze gdy mamy taką możliwość używajmy indeksów typu UNIQUE  Rozważmy możliwość użycia indeksów niegrupujących do wstępnych agregacji lub pokrycia zapytań  Jeżeli łączymy dwie tabele, to połóżmy indeksy na kolumnach występujących w klauzuli JOIN
• 24.
  Strategia Indeksowania  Przeanalizujkolumny najczęściej występujące w zapytaniach SQL w poleceniach WHERE, ORDER BY i GROUP BY – pozwoli to wytypować odpowiednich kandydatów do stworzenia indeksu  Usuń nieużywane indeksy  Nie ma sensu tworzyć indeksu na kolumnie która ma małe zróżnicowanie wartości (np. pola logiczne, płeć, stan cywilny, itp.)
• 25.
  Pozostałe typy indeksów Indeksy typu XML  Indeksy pełnotekstowe  Indeksy typów geograficznych i geometrycznych  Indeksy typu COLUMNSTORE  Indeksy Hash
• 26.
  Statystyki Statystyki pełnią istotnąrolę w uzyskiwaniu wysokiej wydajności w zapytaniach TSQL. Indeksy mające na celu przyspieszenie wyszukiwania oraz skalowanie systemów bazodanowych będą wykorzystywane poprawnie tylko w momencie, kiedy optymalizator zapytań będzie miał do dyspozycji aktualne statystyki dotyczące danych w tabelach oraz ich rozkładzie.
• 27.
  Zbieranie informacji dostatystyk  Przeglądanie wszystkich lub losowo wybranych wartości w kolumnach  Przeglądanie losowej próbki jest domyślne przy:  tworzeniu i aktualizacji statystyk  Przeglądanie wszystkich wierszy jest domyślne  przy tworzeniu indexów  przy użyciu opcji FULLSCAN podczas tworzeniu lub aktualizacji
• 28.
  Tworzenie statystyk Statystyki możnautworzyć dla  nieindeksowanych kolumn  wszystkich kolumn poza pierwszą indeksu złożonego  dla kolumn wyliczanych, ale takich, dla których można by utworzyć indeks (czyli spełniających pewne warunki)  kolumn nie opartych na typach text, ntext, image
• 29.
  Automatyczne tworzenie statystyk Jeśliopcja bazy danych auto create statistics jest ustawiona na ON, automatycznie są tworzone statystyki dla:  indeksowanych kolumn zawierających dane  nieindeksowanych kolumn używanych w złączeniach klauzuli where Wyłączenie tej opcji może źle wpłynąć na wydajność, gdyż wtedy optymalizator kwerend nie będzie mógł z nich korzystać
• 30.
  Odświeżanie statystyk  Jestto ważne, ponieważ przy nieaktualnych danych optymalizator kwerend może działać nieoptymalnie  Automatyczne  ma miejsce, gdy jest włączona opcja auto update statistics  aktualizacja jest wykonywana przy optymalizacji zapytań przez optymalizator kwerend  Ręczne  należy je wykonać w następujących sytuacjach  kiedy utworzymy indeks przed wstawieniem danych do tabeli  kiedy tabela jest obcięta (truncate)
• 31.
  Podsumowanie  Nieposortowane danetabeli nazywamy stertą  Możemy położyć tylko jeden indeks klastrowany na naszej tabeli, należy go dobrać w pełni świadomie  Dobrą praktyką jest posiadanie indeksu grupującego w każdej tabeli  Istnieje wiele rodzajów indeksów niegrupujących, każdy z nich jest przeznaczony do innych celów  Indeksować możemy nie tylko tabele
• 32.
  Literatura:  https://technet.microsoft.com/pl- pl/library/cc280372%28v=sql.105%29.aspx  http://wss.geekclub.pl/baza-wiedzy/kurs-transact-sql-czesc-10- indeksy,1197#1 https://msdn.microsoft.com/pl-pl/library/baza-danych-sql-server-- indeksy--kiedy-i-jak-je-stosowac.aspx#4  https://msdn.microsoft.com/pl-pl/library/encyklopedia-sql-- indeksowanie-tabel-indeks-klastrowy-i-nieklastrowy.aspx  Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases  „Microsoft SQL Serwer 2008 od środkaL Zapytania w języku T- SQL” - Itzik Ben-Gan  Cezary Ołtuszyk „Strojenie baz danych”  http://sqlblog.com/blogs/paul_white/default.aspx?p=4  http://www.getallanswers.com/core-storage-and-index-structure/
• 33.
  DZIĘKUJĘ ZA UWAGĘ TomaszWaloszek E-mail: waloszektomasz@gmail.com