Czy dodawanie indeksów bazy danych jest przedwczesną optymalizacją?

Mój kolega dziś zasugerował, abyśmy przejrzeli wszystkie zapytania w naszej aplikacji i odpowiednio dodali indeksy.

Wydaje mi się, że jest to przedwczesna optymalizacja, ponieważ nasza aplikacja nie została jeszcze wydana. Zasugerowałem monitorowanie wolnych zapytań po uruchomieniu, a następnie odpowiednio dodać indeksy.

Jaki jest ogólny konsensus przy projektowaniu bazy danych, czy należy dodawać pasujący indeks za każdym razem, gdy piszesz nowe zapytanie? A może lepiej po prostu monitorować i zobaczyć, jak idzie?

Przedwczesna optymalizacja to „optymalizacja” czegoś z powodu niejasnego, intuicyjnego poczucia, że, wiesz, prawdopodobnie będzie to powolne, szczególnie ze szkodą dla czytelności kodu i możliwości konserwacji . Nie oznacza to umyślnego nieprzestrzegania ugruntowanych dobrych praktyk dotyczących wydajności.

Czasami jest to trudna linia do narysowania, ale zdecydowanie powiedziałbym, że brak dodawania indeksów przed uruchomieniem jest zbyt późną optymalizacją ; będzie to karać początkujących użytkowników - twoich najbardziej chętnych i najważniejszych użytkowników - i da im negatywny pogląd na twój produkt, który będą następnie rozpowszechniane w recenzjach, dyskusjach itp. Monitorowanie zapytań w celu znalezienia problemów wymagających indeksowania to dobry pomysł, ale zrobię to nie później niż w wersji beta.

monitoruj wolne zapytania, gdy tylko zaczniemy działać

ponieważ nic nie mówi o jakości, takiej jak powodowanie cierpienia użytkowników z powodu braku projektu!

Powinieneś wiedzieć, które zapytania wymagają indeksów podczas projektowania tabel, wiesz, które kolumny są pytane o to, gdzie klauzule i łączą. Powinny one być już zaindeksowane, ponieważ to, co może nie być widoczne w środowisku na żywo, może szybko ujawnić się, gdy wzrasta obciążenie lub przechowywane dane. To, czego nie chcesz robić, gdy tak się dzieje, to uderzanie indeksów w każde „wolne” zapytanie, a skończysz na indeksie wszystkiego.

„Przedwczesna optymalizacja” w sensie uwłaczającym oznacza kosztowną optymalizację, która może nie być potrzebna. Nie oznacza to, że cała optymalizacja została wdrożona przed ostatnim możliwym momentem, aby zapobiec bankructwu!

W szczególności uzasadniona jest optymalizacja na podstawie testów wydajności przed uruchomieniem, aby upewnić się, że możesz spełnić pewne rozsądne (aczkolwiek przybliżone) wymagania, aby aplikacja nie była całkowicie do bani.

Przy absolutnym minimum powinieneś załadować bazę danych z wiarygodną ilością danych testowych i sprawdzić reakcję swojej aplikacji. Nie jest to przedwczesne, ponieważ wiesz, że tak się stanie, i będzie przechwytywać zapytania, które wywołują absurdalnie powolne skanowanie. Jak mówi AE w komentarzu:

Użyj indeksów, aby uniknąć pełnego skanowania tabeli dla każdego zapytania, które użytkownik końcowy zwykle wykonuje w czasie rzeczywistym

Przynajmniej dla tabel, które mają rosnąć w użyciu.

W skrócie, jeśli masz duże doświadczenie z silnikiem bazy danych i już zaplanowałeś testy podczas pisania pierwszego wycinka kodu, to często będziesz wiedział, nawet bez uruchamiania, że ​​kwerenda, którą jesteś pisanie będzie zbyt wolne bez indeksu. Oczywiście możesz udawać, że nie wiesz, i oglądać test zakończony niepowodzeniem przed dodaniem indeksu, aby go przejść, ale nie ma powodu, aby znany wadliwy kod (ponieważ nie reagował) został uruchomiony.

Wydaje mi się, że jest to przedwczesna optymalizacja, ponieważ nasza aplikacja nie została jeszcze wydana. Zasugerowałem monitorowanie wolnych zapytań po uruchomieniu, a następnie odpowiednio dodać indeksy.

Nie możesz traktować użytkowników końcowych i środowiska produkcyjnego jak zapewniania jakości. Innymi słowy, mówisz, że odkryjesz to w produkcji. Nie sądzę, żeby to była właściwa droga, i widzę, że takie podejście każdego dnia jest strasznie złe .

Musisz pamiętać o jednej rzeczy, ponieważ nie można tego pomalować szerokim pędzlem.

Jakie jest twoje wspólne obciążenie pracą ?

Może to zabrzmieć oczywisto lub nudno, ale w praktyce jest znaczące. Jeśli masz 10 zapytań, które stanowią 98% twojego obciążenia (dość powszechne, wierz lub nie), moja rekomendacja byłaby trudną analizą przed produkcją . Przy realistycznych i reprezentatywnych danych upewnij się, że te 10 zapytań jest tak dobrych, jak to tylko możliwe ( idealne to strata cennego czasu i prawie nieosiągalne).

W przypadku pozostałych 200 zapytań, które stanowią 2% obciążenia , są to te, które najprawdopodobniej nie są warte wiele wysiłku i będą stanowić podstawowe zagadnienie rozwiązywania problemów w produkcji. To także rzeczywistość i wcale nie taka straszna zła rzecz. Ale to nie oznacza ignorowania najlepszych praktyk indeksowania lub dokonywania szacunkowych założeń dotyczących odzyskiwania danych.

Dobrą praktyką jest ustalanie wydajności bazy danych przed produkcją. W rzeczywistości istnieje stosunkowo powszechne stanowisko dla tego typu rzeczy zwanych deweloperskim DBA .

Ale...

Niektórzy posuwają się za daleko i wariują dodając indeksy „na wszelki wypadek”. Ktoś poleca to brakujący indeks? Dodaj go i cztery inne odmiany. Również zły pomysł. Musisz nie tylko pomyśleć o odzyskiwaniu danych, ale co z modyfikacją danych? Im więcej indeksów masz na stole, ogólnie mówiąc, tym więcej masz narzutu podczas modyfikowania danych.

Jak większość rzeczy, istnieje zdrowa równowaga.

Jako zabawna mała uwaga dodatkowa ... Liczba mnoga „Index”

„Wskaźniki” są dla ludzi z branży finansowej

„Indeksy” są dla nas

Nie, nie jest to przedwczesna optymalizacja, ale musi być wykonana poprawnie tak jak każda optymalizacja.

Oto co bym zrobił:

1. Załaduj bazę danych wystarczającą ilością danych testowych, aby naśladować obciążenie produkcyjne. Nie możesz uzyskać tej 100% dokładności, ale to jest w porządku: wystarczy wprowadzić wystarczającą ilość danych. Czy jedna tabela ma określoną ilość danych? Załaduj to. Czy masz jedną tabelę, która zawiera wiele danych, np. Dowolna tabela zawiera pytania na tej stronie? Załaduj kilka milionów rekordów, nawet jeśli są to atrapy danych.
2. Włącz profilowanie na serwerze bazy danych.
3. Uderz w aplikację za pomocą kombinacji zautomatyzowanych skryptów (zapewnia objętość) i prawdziwych użytkowników (wiedzą, jak to zrobić).
4. Przejrzyj dane profilowania. Czy określone zapytania są wolne? Sprawdź plany wyjaśniania i sprawdź, czy serwer bazy danych mówi, że chce indeksu, ale nie istnieje.

Serwery baz danych to złożone i inteligentne oprogramowanie. Mogą ci powiedzieć, jak je zoptymalizować, jeśli umiesz słuchać.

Klucze służą do pomiaru wydajności przed i po optymalizacji oraz pozwalają bazie danych powiedzieć, czego potrzebuje .

Postępowanie zgodnie ze sprawdzonymi wzorcami znanych problemów (np. Wyszukiwanie rekordu według jego identyfikatora) nie jest niczym przedwczesnym. To po prostu rozsądne.

To powiedziawszy, indeksy nie zawsze są prostym biznesem. Często trudno jest wiedzieć na etapie projektowania, od których indeksów będzie zależeć ruch, i które będą ograniczały operacje zapisu. Tak więc, argumentowałbym za wykorzystaniem pewnych „oczywistych” najlepszych praktyk w zakresie projektowania schematów (użyj PK odpowiednich dla zaprojektowanych wzorców odczytu / zapisu i indeksów FK); ale nie umieszczaj indeksu na niczym innym, dopóki nie wymagają tego testy warunków skrajnych.

Po opublikowaniu aplikacji jest już za późno.

Ale każdy właściwy proces rozwoju powinien obejmować testowanie wydajności.

Skorzystaj z wyników testów wydajności, aby zdecydować, które indeksy dodać, i zweryfikuj ich skuteczność, powtarzając testy wydajności.

Chociaż nie uważam, że każde zapytanie powinno być zoptymalizowane, indeksy są tak bardzo częścią RDBMS, że należy je rozważyć przed wydaniem. Podczas wykonywania zapytania, w przeciwieństwie do innych form programowania, nie mówisz systemowi, jak go wykonać. Opracowują własne plany i prawie zawsze opierają je na dostępności indeksu. Skład i objętość danych będą również brane pod uwagę w późniejszym czasie.

Oto kilka rzeczy, które rozważę:

1. Jest kilka zapytań, które powinieneś zidentyfikować na wczesnym etapie rozwoju, o których wiesz, że będą często używane. Skoncentruj się na nich.
2. Będą wolne zapytania. Indeksując je najpierw, możesz następnie ustalić, czy wydajność nadal nie jest wystarczająco szybka, a następnie rozważyć przeprojektowanie (Denormalizacja może być przedwczesna). Wolę to zrobić przed wydaniem. Nikt nie chce systemu, w którym znalezienie czegoś w ekwipunku zajmuje 10 minut.
3. Indeksy mogą poprawić wydajność zapytań, ale nie utrudniają modyfikacji danych.
4. Wiele systemów ma narzędzia do analizy zapytań, więc nie bój się ich używać.

Po wstępnej analizie powinieneś wziąć pod uwagę kilka uwag na temat tego, kiedy powinieneś to powtórzyć i jak będziesz w stanie zebrać informacje, aby to zrobić (monitoruj użycie, uzyskaj kopie danych klienta itp.).

Zdaję sobie sprawę, że nie chcesz przedwcześnie optymalizować, ale jest prawie pewne, że będziesz mieć niską wydajność bez indeksowania bazy danych. Usuwając to z drogi, możesz ustalić, czy istnieją inne obszary powodujące problemy z wydajnością.

Zależy to również od liczby oczekiwanych użytkowników. Zdecydowanie powinieneś wykonać test obciążenia i upewnić się, że Twoja baza danych jest w stanie nadążyć za jednoczesnym żądaniem od 10 do 100 do 1000. Znowu zależy to od oczekiwanego natężenia ruchu i obszarów, które mają być używane częściej niż inne.

Ogólnie rzecz biorąc, precyzyjnie dostroiłbym obszary, które według mnie będą najbardziej trafiać jako pierwsze. Następnie dostroiłbym wszystko, co jest wolne z punktu widzenia doświadczenia użytkownika. Ilekroć użytkownik musi na coś poczekać, ma złe doświadczenia i może zostać odrzucony. Niedobrze!

Dobrą praktyką jest ustalenie, które kolumny zdecydowanie potrzebują indeksu na podstawie niektórych wstępnych analiz. Istnieje realne ryzyko stopniowego lub nieoczekiwanego obniżenia wydajności produkcji, ponieważ rozmiar bazy danych rośnie, jeśli nie masz absolutnie żadnych indeksów. Sytuacja, której chcesz uniknąć, polega na tym, że często uruchamiane zapytanie wymaga skanowania dużej liczby wierszy tabeli. Optymalizacja nie jest przedwczesnym dodawaniem indeksów do krytycznych kolumn, ponieważ masz wiele niezbędnych informacji, a potencjalne różnice w wydajności są znaczące (rzędy wielkości). Istnieją również sytuacje, w których korzyści wynikające z indeksów są mniej wyraźne lub bardziej zależne od danych - prawdopodobnie możesz odroczyć podjęcie decyzji w niektórych z tych przypadków.

Niektóre pytania, które musisz zadać, to:

• Jakie będą granice projektu dla wielkości każdego stołu?

Jeśli tabele zawsze będą małe (powiedzmy <100 wierszy), nie jest katastrofą, jeśli baza danych musi przeskanować całą tabelę. Dodanie indeksu może być korzystne, ale wymaga to nieco więcej wiedzy lub pomiaru.

• Jak często będzie uruchamiane każde zapytanie i jaki jest wymagany czas odpowiedzi?

Jeśli zapytanie jest uruchamiane rzadko i nie ma ścisłych wymagań dotyczących czasu odpowiedzi (np. Generowanie raportu), a liczba wierszy nie jest duża, prawdopodobnie odroczenie dodawania indeksów jest dość bezpieczne. Znów wiedza specjalistyczna lub pomiar może pomóc stwierdzić, czy będzie to korzystne.

• Czy zapytanie wymaga przeszukania tabeli poza czymś innym niż klucz podstawowy? Np. Filtrowanie według zakresu dat, dołączanie do klucza obcego?

Jeśli te zapytania są uruchamiane często i dotykają tabel z wieloma wierszami, powinieneś poważnie rozważyć zapobiegawcze dodanie indeksu. Jeśli nie masz pewności, czy tak jest w przypadku zapytania, możesz zapełnić bazę danych realistyczną ilością danych, a następnie spójrz na plan zapytań.