Gdzie należy umieścić indeksy w tabeli wymiarów czasowych?

Po przeczytaniu pytań i odpowiedzi z tej witryny na temat indeksów przyszło mi do głowy pytanie.

Co, jeśli używa się tabeli wymiarów czasowych, przy czym niższy poziom szczegółowości to dzień. Gdzie należy umieścić indeksy?

Randy Melder w pytaniu: Co oznacza „indeks” w RDBMS? powiedział:

Traktuj indeks jako „spis treści” ... czyli uporządkowaną listę wskaźników do pozycji w pliku, czyli przesunięć

W przypadku wymiaru czasu większość badań danych może być przeprowadzona dla określonego dnia, określonego tygodnia, określonego miesiąca lub określonego kwartału, jeśli tabela czasu przechowuje cały dzień dla unikalnego roku .

Moje pytanie brzmi: czy należy umieścić indeksy dla wszystkich tych pól?

Dzień ma być wyjątkowy, dlatego doskonale rozumiem użycie indeksów. Ale identyfikator tygodnia będzie miał 7 wystąpień , identyfikator miesiąca będzie miał 30/31 wystąpień , identyfikator kwartalny będzie miał mniej więcej 120 wystąpień .

• Czy należy nadal umieszczać indeksy dla tych pól?
• Czy nadal będzie przydatny?

Proszę o to, ponieważ w tym samym pytaniu David Spillett powiedział:

Dodanie zbyt wielu indeksów może być złą optymalizacją, ponieważ dodatkowa przestrzeń używana do przechowywania indeksów (i obciążenie IO do ich utrzymania, jeśli twoja baza danych widzi wiele operacji zapisu) może być gorszym problemem niż nieco mniej optymalne zapytania odczytu , więc nie przesadzaj.

Więc jakie byłyby najlepsze rozważania w przypadku wymiaru czasu?

Prawdopodobnie nie napotkasz problemów z pisaniem, ponieważ zakładam, że byłoby to coś stworzonego raz (lub raz w roku), a następnie nie dotkniętego.

Ale korzystanie z indeksu będzie prawdopodobnie utrudnieniem, jeśli będziesz wyszukiwać według tygodnia ... Problem polega na tym, że jeśli indeks jest używany, może najpierw go zeskanować, a następnie pobrać każdy rekord z tabeli indywidualnie, a gdy „ pobierając więcej niż około 5-20% rekordów, zwykle jest to pełne skanowanie pełnego stołu, a następnie upuszczanie rekordów, na których ci nie zależy.

Nie znam żadnych głównych RDBMS, które nie optymalizują się pod tym kątem, gdy są to dobrze rozproszone dane. Jeśli nie jest on dobrze rozłożony (np. Jedna z wartości w kolumnie występuje w 95% przypadków, ale są też inne możliwe wartości), może być konieczne obliczenie histogramów w tabeli i niewykorzystanie symbolu zastępczego dla wartości podczas wyszukiwania, aby optymalizator zapytań wyszukiwał wartość podczas generowania planu wykonania.

Prawdopodobnie nie indeksowałbym dnia tygodnia. Sprawdziłbym dokumentację mojej bazy danych, aby zobaczyć, jaki jest ich kompromis w przypadku zindeksowanych odczytów w porównaniu do pełnych skanów tabeli, aby sprawdzić, czy zindeksowałbym dzień miesiąca lub miesiąca roku. Prawdopodobnie zindeksowałbym DOY / dzień roku, jeśli jest obecny (co brzmi, jakby to był twój unikalny indeks, tak czy inaczej)

Indeks nie musi być unikalny, aby był użyteczny, więc odpowiedź zależy od niego . Jeśli Twoje zapytania korzystają z obecności indeksu, mogą być wartościowym dodatkiem. Nie wiem, czy powinny istnieć jakieś specjalne wytyczne dotyczące kolumn czasowych. Traktuj je jak inne kolumny i indeksuj je na podstawie przydatności zapytań.

Ogólna zasada jest taka, że ​​im bardziej selektywny jest indeks (selektywność jest definiowana jako liczba unikalnych wartości w kolumnie podzielona przez liczbę wierszy w tabeli), tym bardziej prawdopodobne jest, że silnik użyje indeksu w przypadku zapytania używa kolumny w klauzuli where.

Jeśli zastanawiasz się nad zaindeksowaniem kolumny, uruchomienie zapytania w kolumnie indeksowanej przed i po oraz sprawdzenie planów wykonania powie ci, czy indeks jest używany, a jeśli tak, to w jakim stopniu indeks pomaga. Najlepiej byłoby, gdyby zapytanie użyte do testu było używane przez aplikację.

Jak dotąd moją podstawową zasadą było nie umieszczanie żadnych indeksów w moich bazach programistycznych podczas pracy nad nimi. Jako baza produkcyjna robi się coraz większy, używam rejestrowania bazy danych i EXPLAINdowiedzieć się, co potrzebuje indeksowanie, a następnie utworzyć tylko niezbędne indeksów. Działa to dobrze, o ile wykorzystanie bazy danych stopniowo wzrasta, i utrzymuje niski poziom indeksów.

Analizując dane w bazie danych, zwykle muszę dodać dodatkowe indeksy, aby przyspieszyć żądania, które nie są powszechne w produkcji. Zawsze robię to na kopiach produkcyjnej bazy danych, więc te indeksy nigdy nie są dodawane do produkcji.