Ile indeksów bazy danych to za dużo?

Pracuję nad projektem z dość dużą bazą danych Oracle (chociaż moje pytanie równie dobrze odnosi się do innych baz danych). Mamy interfejs sieciowy, który umożliwia użytkownikom wyszukiwanie w prawie każdej możliwej kombinacji pól.

Aby przyspieszyć wyszukiwanie, dodajemy indeksy do pól i ich kombinacji, w których naszym zdaniem użytkownicy będą często wyszukiwać. Ponieważ jednak tak naprawdę nie wiemy, jak nasi klienci będą korzystać z tego oprogramowania, trudno jest określić, które indeksy utworzyć.

Przestrzeń nie jest problemem; mamy 4 terabajtowy dysk RAID, z którego używamy tylko niewielkiej części. Martwię się jednak możliwymi spadkami wydajności wynikającymi z posiadania zbyt wielu indeksów. Ponieważ te indeksy muszą być aktualizowane za każdym razem, gdy dodaje się, usuwa lub modyfikuje wiersz, myślę, że byłoby złym pomysłem mieć dziesiątki indeksów w jednej tabeli.

Więc ile indeksów uważa się za zbyt wiele? 10? 25? 50? A może powinienem po prostu omówić naprawdę, naprawdę powszechne i oczywiste przypadki i zignorować wszystko inne?

To zależy od operacji, które mają miejsce na stole.

Jeśli jest dużo instrukcji SELECT i bardzo mało zmian, zindeksuj wszystko, co chcesz ... to (potencjalnie) przyspieszy instrukcje SELECT.

Jeśli tabela jest mocno obciążona przez UPDATE, INSERT + DELETE ... będą one bardzo powolne z dużą ilością indeksów, ponieważ wszystkie muszą być modyfikowane za każdym razem, gdy ma miejsce jedna z tych operacji

Powiedziawszy to, możesz wyraźnie dodać wiele bezcelowych indeksów do tabeli, która nic nie da. Dodawanie indeksów B-Tree do kolumny z 2 odrębnymi wartościami będzie bezcelowe, ponieważ nie dodaje niczego pod względem wyszukiwania danych. Im bardziej unikalne wartości w kolumnie, tym większe korzyści z indeksu.

Zwykle postępuję w ten sposób.

1. Uzyskaj dziennik prawdziwych zapytań wykonywanych na danych w typowy dzień.
2. Dodaj indeksy, aby najważniejsze zapytania trafiały do ​​indeksów w ich planie wykonania.
3. Staraj się unikać indeksowania pól, które mają dużo aktualizacji lub wstawek
4. Po kilku indeksach pobierz nowy dziennik i powtórz.

Podobnie jak w przypadku każdej optymalizacji, zatrzymuję się, gdy żądana wydajność zostanie osiągnięta (oznacza to oczywiście, że punkt 0. otrzyma określone wymagania dotyczące wydajności).

Wszyscy inni dawali Ci świetne rady. Mam dla Ciebie dodatkową sugestię, gdy będziesz postępować naprzód. W pewnym momencie musisz podjąć decyzję dotyczącą najlepszej strategii indeksowania. Ostatecznie jednak najlepsza PLANOWANA strategia indeksowania może nadal prowadzić do tworzenia indeksów, które nie zostaną wykorzystane. Jedną ze strategii, która pozwala znaleźć indeksy, które nie są używane, jest monitorowanie użycia indeksu. Robisz to w następujący sposób: -

alter index my_index_name monitoring usage;

Następnie możesz monitorować, czy indeks jest używany, czy nie od tego momentu, wysyłając zapytanie do v $ object_usage. Informacje na ten temat można znaleźć w Przewodniku administratora bazy danych Oracle® .

Pamiętaj tylko, że jeśli masz strategię magazynowania polegającą na usuwaniu indeksów przed aktualizacją tabeli, a następnie ich odtwarzaniu, będziesz musiał ponownie ustawić indeks do monitorowania, co spowoduje utratę historii monitorowania dla tego indeksu.

W hurtowni danych bardzo często występuje duża liczba indeksów. Pracowałem z tabelami faktów mającymi dwieście kolumn i 190 z nich zaindeksowanych.

Chociaż wiąże się to z dodatkowymi kosztami, należy rozumieć w kontekście, że w hurtowni danych zazwyczaj wstawiamy wiersz tylko raz, nigdy go nie aktualizujemy, ale może on uczestniczyć w tysiącach zapytań SELECT, które mogą skorzystać na indeksowaniu dowolnego z kolumny.

W celu zapewnienia maksymalnej elastyczności hurtownia danych zazwyczaj używa indeksów bitmap jednokolumnowych, z wyjątkiem kolumn o wysokiej liczności, w których można używać (skompresowanych) indeksów btree.

Narzut związany z utrzymaniem indeksu jest głównie związany z kosztem zapisywania do bardzo wielu bloków, a blok jest dzielony, gdy nowe wiersze są dodawane z wartościami, które znajdują się „w środku” istniejących zakresów wartości dla tej kolumny. Można to złagodzić, partycjonując i dostosowując nowe ładunki danych do schematu partycjonowania oraz stosując bezpośrednie wstawianie ścieżek.

Aby odpowiedzieć na twoje pytanie bardziej bezpośrednio, myślę, że prawdopodobnie na początku będzie dobrze indeksować to, co oczywiste, ale nie bój się dodawać więcej indeksów, jeśli zapytania w tabeli przyniosą korzyści.

W parafrazie Einsteina o prostocie dodaj tyle indeksów, ile potrzebujesz i nie więcej.

Poważnie jednak, każdy dodawany indeks wymaga konserwacji za każdym razem, gdy dane są dodawane do tabeli. W przypadku tabel, które są głównie tylko do odczytu, dużo indeksów jest dobrą rzeczą. Na stołach, które są bardzo dynamiczne, mniej znaczy lepiej.

Moja rada jest taka, aby omówić typowe i oczywiste przypadki, a następnie, gdy napotkasz problemy, w których potrzebujesz większej szybkości w pobieraniu danych z określonych tabel, oszacuj i dodaj indeksy w tym momencie.

Dobrym pomysłem jest również ponowna ocena schematów indeksowania co kilka miesięcy, aby sprawdzić, czy jest coś nowego, co wymaga indeksowania, lub jakiekolwiek utworzone przez Ciebie indeksy, które nie są używane do niczego i należy się ich pozbyć .

Oprócz punktów podniesionych przez wszystkich innych, Optymalizator oparty na kosztach ponosi koszt podczas tworzenia planu instrukcji SQL, jeśli istnieje więcej indeksów, ponieważ istnieje więcej kombinacji, które należy wziąć pod uwagę. Można to zmniejszyć, prawidłowo używając zmiennych powiązań, tak aby instrukcje SQL pozostały w pamięci podręcznej SQL. Oracle może następnie przeprowadzić miękką analizę i ponownie wykorzystać plan, który znalazł ostatnim razem.

Jak zawsze, nic nie jest proste. Jeśli w grę wchodzą przekrzywione kolumny i histogramy, może to być zły pomysł.

W naszych aplikacjach internetowych zwykle ograniczamy kombinacje wyszukiwań, na które zezwalamy. W przeciwnym razie musiałbyś przetestować dosłownie każdą kombinację pod kątem wydajności, aby upewnić się, że nie masz czającego się problemu, który ktoś znajdzie pewnego dnia. Wdrożyliśmy również ograniczenia zasobów, aby zapobiec powodowaniu problemów w innych miejscach aplikacji, jeśli coś pójdzie nie tak.

Wykonałem kilka prostych testów na moim prawdziwym projekcie i prawdziwej bazie danych MySql. Odpowiedziałem już w tym temacie: Jaki jest koszt indeksowania wielu kolumn bazy danych?

Ale myślę, że będzie lepiej, jeśli zacytuję to tutaj:

Zrobiłem kilka prostych testów, używając mojego prawdziwego projektu i prawdziwej bazy danych MySql.

Moje wyniki to: dodanie średniego indeksu (1-3 kolumny w indeksie) do tabeli - spowalnia wstawianie o 2,1%. Więc jeśli dodasz 20 indeksów, twoje wstawki będą wolniejsze o 40-50%. Ale twoje wybory będą 10-100 razy szybsze.

Czy więc można dodać wiele indeksów? - To zależy :) Podałem Ci swoje wyniki - Ty decydujesz!

Ostatecznie liczba potrzebnych indeksów zależy od zachowania aplikacji, które działają na serwerze bazy danych.

Ogólnie rzecz biorąc, im więcej wstawiasz, tym bardziej bolesne stają się indeksy. Za każdym razem, gdy robisz wstawianie, wszystkie indeksy, które zawierają tę tabelę, muszą zostać zaktualizowane.

Teraz, jeśli twoja aplikacja ma przyzwoitą ilość odczytów, a nawet więcej, jeśli prawie wszystko odczytuje, wtedy indeksy są drogą do zrobienia, ponieważ nastąpi znaczna poprawa wydajności przy bardzo niewielkich kosztach.

Moim zdaniem nie ma statycznej odpowiedzi, tego rodzaju rzeczy podlegają „dostrojeniu wydajności”.

Może się zdarzyć, że wszystko, co robi Twoja aplikacja, jest wyszukiwane za pomocą klucza podstawowego lub może być odwrotnie, ponieważ zapytania są wykonywane na nieoznaczonych kombinacjach pól, a każde w szczególności może być używane w dowolnym momencie.

Oprócz samego indeksowania, istnieje reorganizacja bazy danych w celu uwzględnienia obliczonych pól wyszukiwania, dzielenia tabel itp. - to naprawdę zależy od kształtów obciążenia i parametrów zapytań, ile / jakie dane „naprawdę” muszą zostać ponownie przesłane przez zapytanie.

Jeśli cała baza danych jest wyposażona w fasady procedur składowanych, przełączanie staje się nieco łatwiejsze, ponieważ nie musisz martwić się o każde zapytanie ad hoc. Lub możesz mieć głębokie zrozumienie rodzaju zapytań, które będą trafiać w twoją bazę danych, i możesz ograniczyć dostrajanie do nich.

W przypadku SQL Server uważam, że doradca Database Engine Tuning jest przydatny - konfigurujesz „typowe” obciążenia i może on przedstawiać zalecenia dotyczące dodawania / usuwania indeksów i statystyk. Jestem pewien, że inne bazy danych mają podobne narzędzia, „oficjalne” lub strony trzeciej.

To naprawdę jest bardziej teoretyczna niż praktyczna kwestia. Wpływ indeksów na wydajność zależy od posiadanego sprzętu, wersji Oracle, typów indeksów, itp. Wczoraj słyszałem, jak Oracle ogłosiło wprowadzenie dedykowanej pamięci masowej HP, która ma działać 10 razy szybciej z bazą danych 11g. W Twoim przypadku może być kilka rozwiązań: 1. Miej dużą liczbę indeksów (> 20) i odbudowuj je codziennie (co noc). Byłoby to szczególnie przydatne, gdyby tabela otrzymywała tysiące aktualizacji / usunięć dziennie. 2. Podziel tabelę na partycje (jeśli dotyczy to Twojego modelu danych). 3. Użyj osobnej tabeli dla nowych / zaktualizowanych danych i uruchom nocny proces, który łączy dane razem. Wymagałoby to zmiany logiki aplikacji. 4. Przejdź do IOT (tabela zorganizowana według indeksu), jeśli dane to obsługują.

Oczywiście rozwiązań w takim przypadku może być znacznie więcej. Moją pierwszą sugestią byłoby sklonowanie bazy danych do środowiska programistycznego i przeprowadzenie testów obciążeniowych.

Jeśli wykonujesz głównie odczyty (i kilka aktualizacji), to naprawdę nie ma powodu, aby nie indeksować wszystkiego, co będzie potrzebne do zindeksowania. Jeśli często aktualizujesz, być może będziesz musiał uważać na liczbę posiadanych indeksów. Nie ma twardej liczby, ale zauważysz, kiedy sytuacja zacznie zwalniać. Upewnij się, że indeks klastrowy jest tym, który ma największy sens na podstawie danych.

Jedną z rzeczy, które możesz rozważyć, jest budowanie indeksów w celu uwzględnienia standardowej kombinacji wyszukiwań. Jeśli często wyszukiwane jest kolumna 1, często używana jest z nią kolumna 2, a kolumna 3 jest czasami używana z kolumną 2 i kolumną 1, to indeks kolumny 1, kolumna 2 i kolumna 3 w tej kolejności może być użyty w każdej z tych trzech okoliczności, chociaż tak jest tylko jeden indeks, który należy zachować.

Indeks nakłada koszt, gdy tabela bazowa jest aktualizowana. Indeks zapewnia korzyść, gdy jest używany do przyspieszenia zapytania. W przypadku każdego wskaźnika należy zrównoważyć koszt i korzyści. O ile wolniej zapytanie działa bez indeksu? Jaka jest korzyść z szybszego działania? Czy Ty lub Twoi użytkownicy możecie tolerować niską prędkość, gdy brakuje indeksu?

Czy możesz tolerować dodatkowy czas potrzebny na ukończenie aktualizacji?

Musisz porównać koszty i korzyści. To jest szczególne w twojej sytuacji. Nie ma magicznej liczby indeksów, która przekroczyłaby próg „zbyt wielu”.

Istnieje również koszt miejsca potrzebnego do przechowywania indeksu, ale powiedziałeś, że w Twojej sytuacji nie stanowi to problemu. To samo dotyczy większości sytuacji, biorąc pod uwagę, jak tanie stało się miejsce na dysku.

Ile jest kolumn? Zawsze mówiono mi, żebym tworzył indeksy jednokolumnowe, a nie wielokolumnowe. Więc nie więcej indeksów niż liczba kolumn, IMHO.

Sprowadza się to do tego, że nie dodawaj indeksu, chyba że wiesz (a to często oznacza zbieranie statystyk użytkowania), że będzie on używany znacznie częściej niż aktualizowany.

Każdy indeks, który nie spełnia tych kryteriów, będzie kosztował więcej w celu odbudowania niż spadek wydajności wynikający z braku go w dziwnym przypadku, gdy został użyty.

Serwer SQL zapewnia dobre narzędzia, które pozwalają zobaczyć, które indeksy są aktualnie używane. Ten artykuł, http://www.mssqltips.com/tip.asp?tip=1239 , zawiera kilka zapytań, które pozwalają uzyskać lepszy wgląd w to, jak często indeks jest używany, a nie jak bardzo jest aktualizowany.

Jest całkowicie oparty na kolumnach, które są używane w klauzuli Where. Zgodnie z zasadą, musimy mieć indeksy w kolumnach klucza obcego, aby uniknąć DEADLOCKS. Raport AWR powinien okresowo analizować, aby zrozumieć potrzebę indeksów.