Gdzie mogę znaleźć wskazówki dotyczące strategii indeksu?

Większość z nas prawdopodobnie zgodzi się, że korzystanie z indeksów baz danych jest dobre. Zbyt wiele indeksów i wydajności można faktycznie obniżyć.

Co do zasady, które pola należy indeksować?
Których pól nie należy indeksować?
Jakie są zasady korzystania z indeksów przy jednoczesnym zachowaniu równowagi między zbyt dużą a niewystarczającą liczbą indeksów, aby osiągnąć poprawę wydajności, a nie degradację?

Krótki

Myślę, że zasada „zbyt wielu indeksów” jest nieco myląca.

Długie

Biorąc pod uwagę, że średnia baza danych wynosi około 98% odczytów (lub więcej) odczytów należy zoptymalizować. INSERT to na przykład odczyt, jeśli istnieje unikalny indeks. Lub GDZIE w aktualizacji. Kiedyś przeczytałem, że nawet baza danych intensywnie zapisująca wciąż czyta 85%.

To, co masz, to indeksowanie niskiej jakości. Przykłady:

• szerokie indeksy klastrowe (szczególnie SQL Server)
• niemonotoniczny indeksowany w klastrze
• nakładające się indeksy (np. cold, coleicold, cole, colf)
• wiele indeksów jednokolumnowych (również pokrywających się z bardziej przydatnymi indeksami), które są bezużyteczne dla twoich zapytań
• brak ZAWIERA, nie obejmuje (np. indeksy wszystkich pojedynczych kolumn)
• ...

Zauważ, że indeksy są kilka razy większe niż rzeczywiste dane, nawet w systemach OLTP.

Ogólnie rzecz biorąc, zacznę od

• indeks klastrowy (zwykle PK)
• unikalne indeksy (nie ograniczenia, nie mogą one obejmować)
• kolumny klucza obcego

Potem spojrzałbym na:

• typowe zapytania i zobacz, czego potrzebuję. Zapytanie uruchamiane co sekundę wymaga dostrajania. Raport w niedzielę 4 rano może poczekać.
• w przypadku SQL Server ważone brakujące indeksy DMV

Mówiąc to, złamałem te zasady dla niektórych systemów po tym, jak zobaczyłem, jak wszystko się potoczyło (10 miliardów rzędów później), aby dostroić system. Ale nigdy nie rozważałbym nie indeksowania, chyba że będę w stanie wykazać, dlaczego to robię.

Należy profilować użycie i ładowanie bazy danych oraz identyfikować wąskie gardła z powodu brakujących indeksów - lub z powodu zbyt wielu indeksów. Następnie musisz wybrać odpowiedni indeks - który wymaga dobrej znajomości określonych technik indeksowania baz danych.

Po prostu jeden z najlepszych artykułów napisanych na temat których indeksów wybrać i dlaczego miałby to być Gail Shaw. Artykuły można znaleźć, klikając tutaj

Na zadane pytanie można odpowiedzieć na 50 różnych sposobów. Tak naprawdę wszystko sprowadza się do posiadanych danych i tego, w jaki sposób będą one wyszukiwane. Ogólna zasada mówi, że zawsze powinieneś mieć indeks klastrowy w każdej tabeli, aby uniknąć stosów. Indeksy klastrowe powinny zazwyczaj być tak małe, jak to możliwe. Jeśli tabela ma indeks klastrowany, wówczas wszystkie rekordy indeksu na stronach liści indeksu nieklastrowanego będą przechowywać wartość rekordu odpowiedniego indeksu klastrowego dla wyszukiwania zakładek. Jeśli tabela jest stertą, wówczas SQL utworzy unikalny identyfikator dla wyszukiwania zakładek. Nie pamiętam, aby był to 8 lub 16 bajtów. Może to być znacznie większy typ danych niż powiedzieć INT. Wyobraź sobie, że masz 8 nieklastrowanych indeksów w tabeli stosu.

Chcę tutaj dodać, że różne bazy danych wymagają różnych strategii. Porównajmy na przykład MySQL z InnoDB i PostgreSQL.

InnoDB

Tabele InnoDB są w zasadzie indeksem b-drzewa klucza podstawowego, który jest rozszerzony o informacje o wierszu we wpisie indeksu. Skany w porządku fizycznym nie są obsługiwane, a wszystkie skany odbywają się w kolejności logicznej. Oznacza to dwie rzeczy:

1. Skanowanie sekwencyjne w Innodb generuje wiele losowych operacji we / wy dysku i

2. Indeks klucza podstawowego musi być przeszukiwany niezależnie od tego, czy używany jest indeks wtórny.

3. Wyszukiwanie klucza podstawowego jest szybsze w tym modelu niż w jakimkolwiek innym podejściu.

W takim przypadku bardzo ważne jest indeksowanie wystarczającej liczby pól w tabelach wielostronicowych. Typową regułą jest indeksowanie wszystkiego, co chcesz filtrować.

PostgreSQL

PostgreSQL używa plików sterty, po jednej tabeli na plik (niektóre tabele mogą być wieloma plikami), w których krotki są przydzielane z wolnego miejsca na tej sterty. Obsługiwane są skany zamówień fizycznych. Aby skanowanie kolejności logicznej działało, należy dodać indeks.

Klucze podstawowe w PostgreSQL są w zasadzie podzbiorem unikalnych indeksów, w których żadna wartość nie może mieć wartości NULL. UNIKALNE ograniczenia są wykonywane przy użyciu indeksów niejawnych, a kilka innych typów indeksów jest obsługiwanych przy użyciu różnych operacji możliwych w indeksie.

To znaczy:

1. Wyszukiwanie kluczy głównych, przy założeniu, że odpowiednio duża tabela wymaga trafienia do pliku indeksu i pliku tabeli. Jest to znacznie wolniejsze niż podejście MySQL, w którym indeks musi być tylko przeglądany, a wiersz jest zawarty w indeksie.

2. Skany porządku fizycznego działają znacznie lepiej, zmniejszając liczbę losowych operacji we / wy na dysku, gdzie ma zostać przetworzona znaczna liczba wierszy.

3. Wtórne skany indeksu działają lepiej niż MySQL, ponieważ tylko jeden indeks musi zostać przeszukany, aby dostać się do fizycznej części tabeli.

W tym modelu indeksy są często konieczne, ale planista ma większą swobodę w korzystaniu z indeksu, a konsekwencje nieużywania go są często mniej dotkliwe. Tabele są bardziej ogólnie zoptymalizowane (zamiast specjalizować się w wyszukiwaniu kluczy), dlatego wymagane jest mniej indeksów.

TL; DR

Poznaj swój RDBMS.

Z Przewodnika pojęciach Oracle 11.2:

• Przegląd indeksów

Z Przewodnika dostrajania wydajności 11.2:

• Wybieranie kolumn i wyrażeń do indeksu
• Korzystanie z indeksów i klastrów

Z Przewodnika administratora 11.2:

• Wytyczne dotyczące zarządzania indeksami

Nawet z wszystkimi powyższymi linkami musisz spojrzeć na to, co napisała Kimberly Tripp na temat opieki, karmienia i korzystania z indeksów.

Na początek wykonaj następujące czynności linku do kolekcji jej postów na blogu związanych z indeksem Kimberly. Możesz przeglądać określone tematy za pomocą widżetów „Na tej stronie” i „Kategorie” po lewej stronie okna przeglądarki.

Jest tu wiele informacji, ale nie zniechęcaj się nimi.

Strona Kimberly's About znajduje się tutaj

Oto kilka innych zasobów, które mogą okazać się przydatne podczas pracy z programem SQL Server:

• http://www.toadworld.com/platforms/sql-server/w/wiki/9624.indexing.aspx
• http://www.sql-server-performance.com/category/tips/index/
• http://sqlfool.com/summit2010/
• http://sqlfool.com/2011/06/index-defrag-script-v4-1/