Indeksowanie od początku lub gdy pojawia się problem z wydajnością?

Moje pytanie dotyczy użycia indeksów.

1. Czy powinienem rozpocząć indeksowanie od samego początku, czy gdy pojawi się problem z wydajnością?

2. Możemy również utworzyć indeks tymczasowy podczas wykonywania zapytania. Jakie są zalety i wady takich technik?

Czy powinienem rozpocząć indeksowanie od samego początku, czy gdy pojawi się problem z wydajnością?

Strategia indeksowania ma tendencję do ewolucji w miarę pojawiania się wzorców użytkowania. To powiedziawszy, istnieją również strategie i wytyczne projektowe, które można zastosować z góry.

• Wybierz dobry klucz grupowania . Zwykle można określić odpowiedni indeks klastrowy w czasie projektowania, w oparciu o oczekiwany wzorzec wstawek do tabeli. Jeśli pojawi się przekonujący przypadek zmiany w przyszłości, niech tak będzie.

• Utwórz podstawowe i inne unikalne ograniczenia . Zostaną one wymuszone przez unikalne indeksy.

• Utwórz klucze obce i powiązane indeksy nieklastrowane . Klucze obce to najczęściej wskazywane kolumny łączenia, więc indeksuj je od samego początku.

• Twórz indeksy dla wszelkich oczywiście wysoce selektywnych zapytań . W przypadku wzorców zapytań, które już znasz, będą wysoce selektywne i raczej będą wykorzystywać wyszukiwania niż skanowanie.

Oprócz powyższego należy stosować stopniowe i holistyczne podejście do wdrażania nowych indeksów. Przez holistyczny rozumiem ocenę potencjalnej korzyści i wpływu na wszystkie zapytania i istniejące indeksy podczas oceny dodatku.

Niezbyt częstym problemem w kręgach programu SQL Server jest nadmierne indeksowanie w wyniku wskazówek od brakujących indeksów DMV i wskazówek SSMS. Żadne z tych narzędzi nie ocenia istniejących indeksów i wesoło sugeruje utworzenie nowego indeksu 6 kolumn zamiast dodawania pojedynczej kolumny do istniejącego indeksu 5 kolumn.

-- If you have this
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable] 
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
)

-- But your query would benefit from the addition of a column
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable] 
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

-- SSMS will suggest you create this instead
CREATE NONCLUSTERED INDEX [IX_MyTable_AnotherIndexWithTheSameColumnsAsTheExistingIndexPlusCol6] ON [dbo].[MyTable] 
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

Kimberly Tripp ma doskonały materiał na temat strategii indeksowania, który podczas gdy SQL skupia się na innych platformach. Dla ludu SQL Server istnieje kilka przydatnych narzędzi do identyfikowania duplikatów, takich jak powyższy przykład.

Możemy również utworzyć indeks tymczasowy podczas wykonywania zapytania. Jakie są zalety i wady takich technik?

Zwykle dotyczy to tylko rzadko uruchamianych zapytań, zwykle ETL. Musisz ocenić:

1. Czy czas potrzebny na utworzenie indeksu skraca czas wykonywania zapytania.
2. Czy koszty utrzymania pozostawienia indeksu na miejscu przewyższają czas potrzebny do utworzenia / upuszczenia, gdy jest potrzebny.

Istnieje naprawdę ryzyko związane z obiema metodami:

Opcja a) Indeks od samego początku, ale nie zdajesz sobie sprawy, że utworzyłeś pewną liczbę indeksów, które nigdy nie są używane. Powodują to dodatkowe obciążenie (najbardziej zauważalne w przypadku zapytań, które modyfikują dane, ale także dzięki optymalizacji instrukcji SELECT próbujących zidentyfikować najlepszy indeks).

Musisz zdyscyplinować się, aby zidentyfikować nieużywane indeksy i spróbować je usunąć (PostgreSQL może to zrobić; niestety MySQL w porównaniu z tym jest bardzo słaby po wyjęciu z pudełka).

Opcja b) Nie dodawaj indeksów, dopóki ludzie nie zaczną narzekać, lub twoje narzędzia diagnostyczne nie spowodują, że niektóre zapytania są wolne i można je poprawić.

Ryzyko, które wprowadzasz, polega na tym, że nie masz wystarczająco dużego okna czasowego między tym, kiedy zauważysz, że potrzebujesz indeksu, a momentem, gdy musisz go dodać.

PostgreSQL obsługuje budowanie indeksów CONCURRENTLY, co zmniejsza część stresu wynikającego z tego nagłego wymagania dotyczącego dodawania indeksu, ale w podręczniku odnotowano pewne zastrzeżenia .

Opcja (b) wydaje się być moją preferencją, ale myślę, że połączenie obu opcji jest prawdopodobnie najlepszym rozwiązaniem. Ma to związek z twoim poziomem pewności, czy uważasz, że indeks zostanie rzeczywiście użyty.

To, co sprawia, że ​​jest to szczególnie złożona dyskusja, polega na tym, że zwykle łatwo jest zmienić indeksy, ale trudniej jest zmienić schemat. Nie chcę promować opóźnionej reakcji b jako pretekstu do lekkomyślności.

Oprócz odpowiedzi Marka

Możesz poczuć się, mając realistyczne dane testowe w oczekiwanych ilościach. Widziałem wiele, wiele (zbyt wielu) przypadków, w których zapytanie działa poprawnie z 1000 wierszy, ale nie z milionem w produkcji.

Jeśli możesz, popracuj później nad kopią produkcji,

Oczywiście widziałem dziwny problem tylko w produkcji z powodu wzorców użytkowania, gdy wszystko inne jest identyczne

Tymczasowe indeksy? Poza wzorcami ładowania ETL, jeśli będziesz ich potrzebować, będziesz ich potrzebować ponownie. Nie zapomnij: tworzenie / upuszczanie indeksu jest zapisem i jest rejestrowane = większe obciążenie

Wystarczy dodać kilka rzeczy.

• Indeksy tymczasowe to okropny pomysł ... chyba że indeks znajduje się w tabeli tymczasowej.
• Indeksy zajmują znacznie więcej przestrzeni danych (jak również inne koszty ogólne), niż ludzie zdają sobie sprawę. Dlatego stwórz je zachowawczo.

To jest moje podejście.

1. Podobnie jak Mark, rób indeksy tam, gdzie mają sens, ale nie spóźniaj się.
2. Nie musisz czekać, aż wydajność spadnie, aby utworzyć nowe indeksy. Za każdym razem, gdy piszesz nowy SQL, uruchom plan zapytań (najlepiej dla bazy danych prod). Powinieneś być w stanie sprawdzić, czy wymagany jest nowy indeks.

3. Nie bój się umieścić > 0albo > ""w swoim WHERE dla nieużywanych kolumn.

   1. To znaczy, powiedzmy, że masz indeks A, B, C i D. Jednak masz tylko informacje A, B, D. Nie ma powodu, dla którego nie możesz tego zrobić

   select * from blah 
   where A="one" 
   and B="two" 
   and C>=""     --to match index
   and D="four"
   
   --This will use your existing index. No need to create a redundant one.

Spróbuję odpowiedzieć tylko na pierwsze pytanie. Jeśli potrafisz oszacować nawet z grubsza od początku, ile rekordów będziesz mieć w swoich tabelach po pewnym czasie, to powiedziałbym, że lepiej zacząć od początku od zaprojektowania niektórych indeksów. Spróbuj użyć narzędzi testowych lub skryptów testowych, które zautomatyzują jak najwięcej wywołań dla wywołań aplikacji, które Twoim zdaniem będą najczęściej używane, a zobaczysz, jakich skanów tabeli można uniknąć od samego początku.

Na początku będzie to zgadywanka, ale z czasem, gdy będziesz mieć odpowiednie statystyki użytkowania, uzyskasz wyraźniejszy obraz.