Jeśli baza danych ma tylko jedną wstawkę, czy źle jest indeksować każdą możliwą kombinację kolumn?

Pracuję nad systemem raportowania, który będzie wymagał dużych wybranych zapytań, ale jest oparty na bazie danych, która jest wypełniana tylko raz. System zarządzania bazą danych to Microsoft SQL Server 2017. Prawdopodobnie istnieje lepszy sposób zaprojektowania takiego systemu, ale podejdźmy do niego teoretycznie.

Teoretycznie rzecz biorąc:

1. Jeśli mamy bardzo dużą bazę danych (150 mln + wiersze w kilku tabelach)
2. Możemy założyć, że baza danych zostanie wypełniona tylko raz.

Czy indeksowanie każdej możliwej kombinacji kolumn może mieć negatywny wpływ na wydajność wybranego zapytania?

Tak, wpłynie to na czas kompilacji wstępnego planu, ponieważ optymalizator będzie miał wiele dodatkowych ścieżek dostępu do danych do rozważenia.

Ponieważ korzystasz z programu SQL Server 2017, raz ładujesz i uruchamiasz raporty, dlaczego zamiast tego nie użyć po prostu indeksu klastrowanego magazynu kolumn?

To wydaje się być idealnym rozwiązaniem dla potrzeby indeksowania każdej możliwej kombinacji kolumn.

Indeksy magazynu kolumn - przegląd

Jeśli masz N kolumn w tabeli, każda możliwa kombinacja kolumn to 2 ^ N-1 (usunięcie pustego zestawu). Dla 10 kolumn, co oznaczałoby 1023 indeksów, dla 20 kolumn otrzymujemy imponującą liczbę 1048575 indeksów. Większość indeksów nigdy nie będzie wykorzystywana, ale optymalizator musi wziąć to pod uwagę. Możliwe, że optymalizator wybierze indeks nieoptymalny zamiast lepszego. Nie wybrałbym ścieżki generowania różnego rodzaju indeksów, zamiast próbować dowiedzieć się, jakie indeksy byłyby w rzeczywistości korzystne.

EDYCJA poprawiła liczbę możliwych indeksów

Jak zauważa Jeff , jest nawet gorszy niż 2 ^ N (zestaw mocy), ponieważ (3,2,1) jest wyraźnie różny od (1,2,3). Dla N kolumn możemy wybrać pierwszą pozycję w indeksie, który zawiera wszystkie kolumny na N sposobów. Za drugą pozycję pod względem N-1 itd. Dlatego też otrzymujemy N! różne indeksy pełnego rozmiaru. Żaden z tych indeksów nie jest objęty innym indeksem w tym zestawie. Ponadto nie możemy dodać kolejnego krótszego indeksu, aby nie był objęty żadnym pełnym indeksem. Liczba indeksów wynosi zatem N !. Przykład dla 10 kolumn staje się zatem 10! = 3628800 indeksów i dla 20 (bębnów) 2432902008176640000 indeksów. To jest absurdalnie duża liczba, jeśli umieścimy kropkę dla każdego indeksu jeden mm na część, minie 94 dni, zanim wszystkie kropki przejdą. Wszyscy, nie ;-)

Nie.

Indeksowanie „wszystkiego” nie jest praktyczne, ale można indeksować „większość” tego.

To jest ta rzecz. Jeśli tabela ma Nkolumny, liczba możliwych indeksów wynosi N!. Załóżmy, że tabela ma 10 kolumn, a więc nie tylko 10możliwe indeksy, ale 10!. To jest ... 3 628 800 ... na jednym stole. To dużo miejsca na dysku, dyskowych operacji we / wy, pamięci podręcznej i czasów wyszukiwania.

Czemu? Kilka powodów:

• Indeksy Lightwwight są zwykle buforowane, co sprawia, że ​​są szybkie. Jeśli masz 3 miliony, NIE będą one buforowane.

• Optymalizator SQL może zająć dużo czasu przy podejmowaniu decyzji, który z nich jest lepszy, szczególnie w przypadku połączeń.

• Optymalizator SQL może zrezygnować z używania kompleksowego algorytmu i zamiast tego wypróbować algorytm heurystyczny. Może to być „mniej niż optymalne”. Na przykład PostgreSQL ma różne opcje dla „zapytań tabel mniejszych niż 8” i „zapytań ponad 8 tabel”.

• Indeksy powinny być lżejsze niż kupa. Jeśli indeksujesz wszystko, indeks staje się tak ciężki jak kupa ... coś, co nie spełnia celu indeksu.

Nie, prawdopodobnie nie będzie to miało negatywnego wpływu na SELECTzapytania, ale

• Spowoduje to duże zużycie dysku.
• To ogromnie zwiększy INSERTkoszty.
• Większość twoich indeksów nigdy nie będzie używana.
• Wiele WHEREwyrażeń warunkowych nadal nie używa indeksów, głównie bardziej złożonych.
• Liczba wymaganych indeksów wzrośnie wykładniczo wraz z liczbą kolumn. To znaczy, jeśli masz na przykład 8 kolumn, potrzebujesz 256 indeksów dla wszystkich możliwych kombinacji.