Czy źle jest mieć przestrzeń indeksu większą niż przestrzeń danych?

Często muszę uruchamiać zapytania względem dużych tabel, które nie mają właściwego indeksu. Dlatego proszę DBA o utworzenie takiego indeksu. Pierwszą rzeczą, którą robi, jest przeglądanie statystyk tabeli i sprawdzanie wielkości przestrzeni indeksu.

Często kazał mi znaleźć alternatywne rozwiązanie, ponieważ „indeks jest już większy niż tabela”. Uważa, że ​​indeks musi być mniejszy niż dane, ponieważ powiedział mi „czy widziałeś kiedyś indeks w książce? Jest znacznie mniejszy niż sama książka i tak powinien wyglądać indeks tabeli”.

Nie sądzę, aby jego filozofia była poprawna, ale nie mogę się z nim zmierzyć, ponieważ jest wiodącym DBA, a ja jestem programistą. Wydaje mi się, że jeśli zapytanie wymaga indeksu, indeks należy po prostu utworzyć, zamiast znajdować „obejścia”, które powodują, że SP są nieczytelne i niemożliwe do utrzymania.

Wybieram tylko wymagane kolumny. Problem polega na tym, że filtruję według daty, więc silnik koniecznie wykona skanowanie tabeli, aby dopasować kolumny. Kwerenda jest uruchamiana raz dziennie, w nocy, w celu zebrania statystyk, ale jej uruchomienie zajmuje 15 minut (mamy inną twardą i szybką zasadę: żadna procedura nie powinna zająć więcej niż 3 minuty).

DBA pokazało mi statystyki indeksu. W tej tabeli znajdowało się około 10 indeksów, z których użyto tylko 6 (statystyki pokazały zero trafień dla 4 z nich). To duży system z udziałem ponad 20 programistów. Indeksy zostały utworzone z dowolnego powodu i prawdopodobnie już nie są używane.

Jesteśmy zobowiązani do obsługi SQL Server 2008, ponieważ na tym działają testowe bazy danych. Ale wszyscy klienci są w 2014 i 2016 roku.

Pomyśl o projekcie indeksu jak o przesuwanym przełączniku. Możesz przesunąć gałkę przełącznika z czerwonym trójkątem w dowolne miejsce wzdłuż wybranej linii:

Zwykle nie mierzę go pod względem wielkości - zwykle myślę o tym pod względem ilości indeksu, ale rozmiar również byłby odpowiedni.

Wygląda na to, że Twój DBA uważa, że ​​przełącznik jest zbyt daleko w prawo - że dodałeś zbyt wiele indeksów, a usuwanie / aktualizacje / wstawki działają zbyt wolno.

Zamiast kłócić się o to, gdzie jest przełącznik, spróbuj zapytać go o problemy z wydajnością, które masz z powodu dużej liczby indeksów. Być może Twoi użytkownicy narzekają na szybkość usuwania / aktualizacji / wstawiania, albo widzi, że blokada czeka, albo ma trudności z utworzeniem kopii zapasowej bazy danych z powodu jej rozmiaru.

Mój punkt początkowy to zwykle 5 i 5: około 5 indeksów na tabelę, z około 5 lub mniej polami na indeks. W tej liczbie nie ma nic magicznego - po prostu wynika to z faktu, że mam 5 palców na każdej ręce, więc łatwo podnieść ręce i wyjaśnić zasadę.

Może być konieczne posiadanie wielu indeksów LESS niż 5, gdy obciążenie jest silnie tendencyjne do operacji usuwania / aktualizacji / wstawiania i nie masz wystarczającej mocy sprzętowej, aby nadążyć.

Możesz mieć wiele WIĘCEJ indeksów, gdy obciążenie jest w większości tylko do odczytu lub gdy inwestujesz w sprzęt (np. Buforuj całą bazę danych w pamięci, a pod nią znajduje się cała pamięć półprzewodnikowa).

Również chęć posiadania na stole więcej niż „Ozar 5” indeksów prawdopodobnie wskazuje , że masz wiele różnych rodzajów zapytań wymagających dużej ilości odczytu.

Co prawdopodobnie oznacza, że możesz skorzystać z klastrowego lub nieklastrowanego indeksu magazynu kolumn w tabeli.

Zamiast optymalnego indeksu dla każdej z N różnych ścieżek dostępu, magazyn kolumn zapewnia superszybkie skanowanie oraz możliwość pominięcia niepotrzebnych kolumn i segmentów wierszy. Możesz więc mieć niewielką liczbę indeksów BTree dla transakcji nadkrytycznych i wrócić do magazynu kolumn po wszystko inne.

Indeksy magazynu kolumn są zaprojektowane do pracy w obciążeniach obciążonych OLTP z SQL Server 2016+. Zobacz dokumentację dotyczącą analiz operacyjnych w czasie rzeczywistym .

Podoba mi się odpowiedź Brenta i głosowałem za nią. Chciałbym jednak dodać inną perspektywę. Pracowałem jako użytkownik, programista i DBA i uważam, że opinie nie są istotne. Uważam, że to użytkownik (lub interesariusz) decyduje o tym, jak kwerenda zostanie wykonana i ile czasu zajmuje uzyskanie wyników. Następnie programista i DBA muszą współpracować, aby tak się stało.

Jeśli stanowisko DBA w Twojej firmie jest „odpowiedzialne” za ten temat, mogą one przeanalizować Twoje zapytanie i zasugerować lepszy projekt zapytania lub odpowiedzieć na wyniki.

Jeśli nie można zmodyfikować zapytania i / lub struktury danych, aby osiągnąć cel, to myślę, że sprowadzają się do trzech opcji.

1. Powolne pobieranie danych
2. Powolna aktualizacja danych
3. Więcej zasobów sprzętowych $$$$

Oczywiście każda sytuacja ma wiele zmiennych w zależności od wielu czynników biznesowych i technologicznych, ale uważam, że te trzy opcje dotyczą większości, jeśli nie wszystkich przypadków.

Wydaje się zbyt surowe, aby zabronić indeksów> tabela. Jeśli twoja tabela rzadko się zmienia (lub zmienia się w nocy, gdy nie ma dużej konkurencji o zasoby) i jest bardzo często pytana na wiele różnych sposobów, wiele dużych indeksów może być uzasadnionych. DBA powinni również uważać, aby nie przyklejać nosa tam, gdzie to nie należy. Jeśli da Tobie / Twojemu systemowi limit gigabajtów, nie powinno go zbytnio obchodzić, jak ta przestrzeń jest wykorzystywana. Jeśli jest przepracowany, może dlatego.

Istnieje jednak wiele rzeczy do rozważenia:

• Wiele indeksów spowalnia wstawianie / aktualizację / usuwanie. Więc jeśli twój stół się bardzo zmienia, uważaj, aby nie zrobić ich zbyt wielu.
• Problemem może być także przestrzeń kosmiczna. Nie tylko dlatego, że gigabajty kosztują (obecnie niewiele), ale także czas, ponieważ tworzenie kopii zapasowej będzie wolniejsze (w zależności od sposobu wykonania kopii zapasowej).
• Najpoważniejsze bazy danych można monitorować w celu znalezienia indeksów, które rzadko lub nigdy nie są używane. Rozważ upuszczenie niektórych z nich.
• Czasami wydaje ci się, że potrzebujesz indeksu, ale gdy przyjrzysz się dokładniej swojemu zapytaniu, można je dostroić i przepisać inaczej z tym samym wynikiem i bez potrzeby korzystania z indeksu. Użyj planu wyjaśniania, aby sprawdzić, czy indeks jest używany, czy nie.
• Czasami ostatnie kolumny można usunąć z indeksu wielokolumnowego bez większego spadku wydajności. Czasami może to nawet przyspieszyć zapytania, ponieważ przestrzeń do przechowywania indeksu jest mniejsza, a większa część indeksu będzie przechowywana / buforowana w pamięci w dowolnym momencie.
• Indeksy funkcyjne mogą zastąpić normalne indeksy, aby zaoszczędzić więcej miejsca. Przykład: zamiast pytać o pełne nazwisko, zapytaj również o dwie pierwsze litery ( where substr(surname, 1, 2) = substr(<userinput>, 1, 2) and surname=<userinput>) i create index i on customers(substr(surname,1,2)). Może to być wystarczająco szybkie, a Twój indeks będzie mniejszy.
• Bazy danych obsługują różne typy indeksów. Niektóre typy zajmują mniej miejsca niż inne. Może niektóre z twoich indeksów można przekonwertować na mniej zajmujący miejsce typ? Pamiętaj, aby najpierw zrozumieć różne typy indeksów i sytuacje, w których są dobre i złe.
• Jeśli rzadkie zadanie wsadowe jest jedyną rzeczą, która wymaga określonego indeksu, rozważ utworzenie tego indeksu tylko dla tego zadania wsadowego i upuść go później.