Rewolucja w procesie identyfikowania zbędnych indeksów w bazach danych programu SQL Server

zbędny
indeks indeksu, który staje się bezużyteczny i niepotrzebny z powodu obecności innego indeksu lub zestawu indeksów.

Czy słyszałeś kiedyś o powiedzeniu „co za dużo dobrego, może być złe”? Cóż, dotyczy to również indeksów SQL Server. To znaczy, oczywiście, możesz utworzyć dowolną liczbę indeksów nieklastrowanych, ale po co? To tak, jakby próbować zaimponować ukochanej przez nałożenie dziesięciu warstw wody kolońskiej — to nie zadziała, a tylko pogorszy sytuację.

Podwójne kłopoty

Niebezpieczeństwa duplikatów indeksów w SQL Server

W SQL Server możliwe jest tworzenie zduplikowanych indeksów na tym samym obiekcie. Chociaż wszystko, od kluczy indeksu po właściwości, może być identyczne, ta praktyka ma kilka wad.

1. Zduplikowane indeksy zajmują dodatkową przestrzeń dyskową. A kto lubi marnować cenną przestrzeń do przechowywania? Nie my! Przeprowadzając analizę redundancji indeksu (wymyślne określenie na analizę nadmiarowych indeksów), byliśmy w stanie zidentyfikować nadmiarowe indeksy i zaoszczędzić prawie 10% miejsca na dysku. To tak, jakby znaleźć ukrytą skrzynię skarbów o pojemności 400 GB w bazie danych o pojemności 4 TB!
2. Nadmiarowe indeksy mogą spowalniać wykonywanie instrukcji DML (wstawianie, aktualizowanie, usuwanie danych). Wyobraź sobie, że musisz aktualizować wiele kopii tego samego indeksu za każdym razem, gdy wprowadzasz zmianę. Mów o marnotrawcy czasu! Ale nie martw się, usunięcie zbędnych indeksów może poprawić wydajność i sprawić, że Twoja baza danych będzie wyglądać jak Incredible Hulk. Ponadto zaoszczędzisz pieniądze na rdzeniach procesora, które możesz zmniejszyć.
3. Przebudowa lub reorganizacja indeksów może być prawdziwym problemem w przypadku zduplikowanych indeksów. Pozbywając się zbędnych indeksów, możesz przyspieszyć ten proces i wrócić do ważniejszych rzeczy (takich jak oglądanie ulubionego programu).
4. Posiadanie wielu indeksów na tym samym obiekcie może utrudnić działanie optymalizatora zapytań. I nikt nie chce zepsutego optymalizatora! Upraszczając indeksy, możesz ułatwić życie optymalizatorowi i poprawić wydajność zapytań.

Dylemat indeksu w SQL Server

Indeksy można uznać za zbędne, nawet jeśli nie są identyczne. W indeksie kolumny są podzielone na dwie kategorie: Kolumny indeksu i Kolumny dołączane.

Nie graj w Jengę ze swoimi indeksami: dlaczego kolejność ma znaczenie!

W SQL Server kolejność kolumn indeksu jest krytyczna. SQL Server może użyć indeksu do skanowania zakresu tylko wtedy, gdy określona jest skrajna lewa kolumna, a następnie tylko wtedy, gdy określona jest następna skrajna lewa kolumna i tak dalej. To jak przestrzeganie przepisu — nie można pominąć kroków ani dodać składników w niewłaściwej kolejności.

Z drugiej strony kolejność kolumn innych niż kluczowe (w tym kolumny) w indeksie nie ma żadnego znaczenia. To jak budowanie kanapki — możesz umieścić sałatę przed pomidorem lub odwrotnie, i nie wpłynie to na smak ani konsystencję kanapki. Uwzględnienie w indeksie kolumn innych niż kluczowe może znacznie poprawić wydajność zapytań, ponieważ optymalizator zapytań może zlokalizować wszystkie wartości kolumn w indeksie, co skutkuje mniejszą liczbą dyskowych operacji we/wy.

Klasyfikacja indeksów redundantnych

Istnieją trzy rodzaje nadmiarowych indeksów:

• Zduplikowane indeksy: Dwa indeksy mają dokładnie te same kolumny klucza w tej samej kolejności (tj. identyczne indeksy) z tymi samymi kolumnami dołączania. Dzieje się tak dlatego, że kolejność dołączania kolumn nie ma znaczenia.
  Np.: Oba indeksy mają te same kolumny kluczowe „KolumnaA” i „KolumnaB” w tej samej kolejności i zawierają te same kolumny niekluczowe „KolumnaC” i „KolumnaD”, co czyni je duplikatami indeksów.
  CREATE INDEX idx1 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnC, ColumnD);
CREATE INDEX idx2 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnD, ColumnC);


• Nakładające się indeksy: jeden indeks ma kolumny kluczowe, które tworzą uporządkowany w lewo podzbiór kolumn kluczowych innego indeksu i ma podzbiór kolumn niekluczowych innego indeksu, które nakładają się na siebie. Kluczowe kolumny w nakładającym się indeksie muszą być uporządkowane od lewej strony, co oznacza, że ​​są wymienione w kolejności malejącej ważności, przy czym najważniejsza kolumna pojawia się jako pierwsza. Wynika to z faktu, że SQL Server może użyć indeksu do skanowania zakresu tylko wtedy, gdy określona jest kolumna najbardziej po lewej stronie, a następnie tylko wtedy, gdy określona jest następna kolumna po lewej stronie i tak dalej.
  Np.: W tym przykładzie indeks „idx1” ma kluczowe kolumny „KolumnaA” i „KolumnaB”, które są lewym podzbiorem kolumn kluczowych „KolumnaA”, „KolumnaB” w indeksie „idx2”. Dlatego „idx2” pokrywa się z „idx1”, a te dwa indeksy nakładają się na siebie.
  CREATE INDEX idx1 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnX, ColumnY);
CREATE INDEX idx2 ON MyTable (ColumnA, ColumnB, ColumnD) INCLUDE (ColumnX, ColumnY, ColumnZ);


• Podobne indeksy: Indeksy, które mają identyczne kolumny kluczowe w tej samej kolejności, ale różne uwzględnione kolumny. Aby rozwiązać podobne indeksy, należy zaktualizować jeden indeks, aby zawierał kolumny obu nadmiarowych indeksów.
  Np. Indeks rozwiązania: W tym przykładzie indeks „idx2” ma kluczowe kolumny „KolumnaA” i „KolumnaB”, które są identyczne z „idx1”. Kolumny dołączania są jednak różne, a wynikowy indeks musi zawierać kolumny dołączania jako odrębny związek obu kolumn dołączania indeksu.
  CREATE INDEX idx1 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnX, ColumnY);
CREATE INDEX idx2 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnP, ColumnQ);

CREATE INDEX idx1 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnX, ColumnY, ColumnP, ColumnQ);


Polowanie na wydajność w SQL Server

Zagłębiając się w tajemniczy świat baz danych SQL Server, odkryliśmy dziwne zjawisko — nieużywane indeksy! Indeksy te były jak duchy baz danych z przeszłości, nawiedzały naszą pamięć masową i pochłaniały naszą cenną moc obliczeniową niepotrzebnymi aktualizacjami indeksów.

Zauważyliśmy poważne problemy z naszymi dużymi bazami danych — nasze procesory i dane we/wy często gwałtownie wzrastały, co nigdy nie jest dobrym znakiem dla zdrowej bazy danych. Winowajcami były głównie nieznośne zapytania, ale w kilku przypadkach zapytania wydawały się zupełnie niewinne, a indeksy wyglądały dobrze. Po głębszej analizie odkryliśmy, że prawdziwym problemem były liczne nieużywane indeksy w kolumnach podstawowych. Te nadmiarowe indeksy powodowały nadmierną liczbę aktualizacji i tworzenie wielu planów zapytań, siejąc tym samym spustoszenie w wydajności naszej bazy danych.

Rozpoczęliśmy więc naszą misję usuwania tych zbędnych indeksów, jeden po drugim. Niestety, proces usuwania zbędnych indeksów okazał się prawdziwą drzemką. Dla administratora bazy danych wyszukiwanie zbędnych indeksów jest jak granie w prawdziwą grę „Gdzie jest Waldo”. Musisz przesiać morze skryptów SQL, wykonać skrypt, który wyświetla listę wszystkich indeksów oraz ich indeksowanych i uwzględnionych kolumn, a następnie przeczesać każdy indywidualny indeks, aby zidentyfikować nadmiarowe indeksy. Nie umknie nam ironia — próbujemy zwiększyć wydajność bazy danych, robiąc coś, co jest niewiarygodnie nieefektywne. I właśnie wtedy, gdy myślisz, że zidentyfikowałeś zbędne indeksy, nadal musisz je usuwać jeden po drugim, co może skłonić cię do zastanowienia się nad wyborem kariery i marzenia o wymianie laptopa na leżak i piña coladę.

I o rany, czekała nas nie lada gratka — okazuje się, że nasze problemy z bazą danych dopiero się zaczynały! Doszliśmy do wniosku, że pozbycie się bezużytecznych indeksów było dość nisko na liście priorytetów naszych drogich administratorów baz danych. To było jak próba skłonienia ich do zrezygnowania z ukochanej dawki kofeiny — prawie niemożliwe! Wiedzieliśmy jednak, że jeśli chcemy mieć jakąkolwiek nadzieję na naprawienie problemów z bazą danych, musimy usprawnić ten proces.

Ale nie bój się, bo pamiętaliśmy o naszym zaufanym pomocniku, snorqlu! Z jego pomocą stworzyliśmy doładowaną metrykę optymalizacji — Index Redundancy Metric. Dzięki temu przydatnemu narzędziu byliśmy w stanie usprawnić proces i pozbyć się tych bezużytecznych indeksów w mgnieniu oka. Ten zły chłopiec zrobił DBA tak szybko, że mógłby sprawić, że gepard będzie wyglądał jak leniwiec.

Co to jest snorql?

diagnozować bazy danych jak szef!

Opracowany w Udaan, snorql to open source i całkowicie darmowy framework, którego celem jest diagnozowanie, rozwiązywanie i optymalizacja metryk SQL. snorql można podłączyć i można go łatwo zaimplementować z dowolną bazą danych i jest tutaj, aby ułatwić Ci życie. Zacznij od naszego łatwego przewodnika na temat „ Pierwsze kroki z snorql ”.

Ale to nie wszystko — Snorql identyfikuje również nieużywane tabele i nieużywane indeksy, dając Ci jeszcze lepszy wgląd w potencjalne oszczędności miejsca. Dzięki metrykom optymalizacji Snorql możesz pewnie podejmować świadome decyzje dotyczące tego, które indeksy i tabele usunąć, redukując bałagan i poprawiając organizację bazy danych.

Repozytoria Snorql:
1. snorql (framework): https://github.com/udaan-com/snorql
2. interfejs snorql: https://github.com/udaan-com/snorql-frontend

Jeśli potrzebujesz dalszego przekonania, koniecznie zapoznaj się z fantastycznym artykułem snorql — diagnozowanie baz danych jak szef

Indeksowanie szaleństw

Lekcja wyciągnięta z dążenia do wydajności bazy danych

Aby poradzić sobie ze zbędnymi indeksami, opracowaliśmy algorytm klasyfikowania naszych indeksów. Brzmi dość prosto, prawda? Po prostu porównaj i porównaj, a voila — nieużywane, zduplikowane, nakładające się i podobne indeksy zostaną starannie posortowane. Ale trzymaj konie, przyjacielu — to nie był spacer po parku! Dopracowanie naszych wskaźników wymagało wielu iteracji, a po drodze zdobyliśmy cenne informacje.

1. Zapraszanie baz danych Read-Replica i Geo-Replica na imprezę:
   zdaliśmy sobie sprawę, że fakt, że indeks istnieje w podstawowej bazie danych, nie oznacza, że ​​jest traktowany w ten sam sposób w instancjach Read-Replica i Geo-Replica. Aby jeszcze bardziej skomplikować sprawę, indeks, który jest intensywnie używany w bazie danych Read-Replica lub Geo-Replica, może zostać oznaczony jako nieużywany indeks w podstawowej bazie danych. Tak więc, aby uzyskać dokładne statystyki dla naszego algorytmu klasyfikacji indeksów, musieliśmy zebrać i podsumować zarówno użycie indeksu, jak i aktualizacje indeksu we wszystkich instancjach.
2. Upewnianie się, że unikalne indeksy pozostaną w drużynie:
   oto sprawa — unikalne indeksy i ograniczenia UNIQUE są jak rodzeństwo, które wymusza unikalność w ten sam sposób. Kiedy tworzysz ograniczenie UNIQUE, SQL Server jest jak magik, który tworzy unikalny indeks z powietrza. A z powodu tego czaru nie możesz po prostu upuścić unikalnych indeksów bezpośrednio z bazy danych. Aby uniknąć wpadek, musieliśmy wykluczyć unikalne indeksy z naszego algorytmu klasyfikacji.
3. Jak nie robić UX:
   W pierwszej wersji naszej analizy redundancji indeksu próbowaliśmy grupować nadmiarowe indeksy na poziomie indeksu. Utrudniało to zrozumienie relacji między indeksami nadrzędnymi i podrzędnymi bez patrzenia na całą tabelę. Poza tym algorytm, który opracowaliśmy, był tak skomplikowany, jak gra w szachy 3D, i szybko zdaliśmy sobie sprawę, że nie tędy droga.

5. Jak radzić sobie z podobnymi indeksami
Wyobraź sobie więc, że masz dwóch przyjaciół, którzy wyglądają podobnie, ale jeden z nich ma fajny kapelusz, a drugi odjechane okulary. Są jak podobne indeksy — duplikaty lub nakładanie się dla indeksowanych kolumn, ale z różnymi kolumnami dołączania. Ale w tym tkwi haczyk — nie można ich tak po prostu wymienić jak pary butów bez powodowania poważnych problemów z wydajnością. Usunięcie ich też nie jest rozwiązaniem — to jak zerwanie z jednym z twoich przyjaciół tylko dlatego, że obaj mają to samo imię! Najlepszym podejściem jest zaktualizowanie jednego z indeksów, aby zawierał wszystkie kolumny z obu, a następnie pożegnanie z drugim. W ten sposób możemy zaoszczędzić na podobnych indeksach i zachować spokój między naszymi przyjaciółmi z bazy danych.

Dobra ludzie, czas zakasać rękawy i wziąć się w garść! Zidentyfikowaliśmy te irytujące przypadki brzegowe, a teraz nadszedł czas, aby przejść do fazy implementacji.

Więc kto jest gotowy, aby ubrudzić sobie ręce? Zróbmy to!

Zagłębianie się w metrykę redundancji indeksu

Czas skupić się na Magu, który za tym stoi, Algorytmie!

Uwaga: Analiza redundancji indeksu jest obecnie dostępna dla baz danych programu SQL Server, a implementacja jest specyficzna dla tego systemu baz danych.

Github Issue ✅ #79 New Metric — Index Redundancy Metric Github PR ⛓ #84 [New Metric] Index Redundancy Metric

Algorytm podzieliłem na etapy:

1. Uzyskaj szczegółowe dane wszystkich indeksów w bazie danych, korzystając z poniższego zapytania sql

3. Grupuj indeksy według tabeli i iteruj po każdej tabeli, filtruj indeksy, których name == NULLchcesz filtrować indeksy sterty, i sortuj w kolejności malejącej według liczby indeksowanych kolumn.
Prowadzimy również listę indeksów, które należy pominąć. Należą do nich indeksy, które są już sklasyfikowane lub indeksy unikalne.

4. Zidentyfikuj nieużywane indeksy. Tutaj, jeśli użycie jest poniżej 10, uważamy to za niewykorzystany indeks. Zachowaliśmy ten mały próg, ponieważ może się zdarzyć, że indeks zostanie użyty podczas wykonywania zapytań ad-hoc.

5. Identyfikacja unikalnych indeksów. Unikalne indeksy są tworzone celowo na kolumnach, aby zachować unikalność, dlatego pomijamy ich klasyfikację w analizie. Pokazujemy unikalne indeksy na poziomie tabeli dla lepszej analizy i widoczności.

6. Opublikuj to, iterujemy każdy indeks i analizujemy go, aby znaleźć zbędne indeksy

A. Klasyfikuj zduplikowane indeksy:
Klasyfikacja zduplikowanych indeksów jest prosta. Kolumny indeksowane i kolumny dołączane muszą być takie same, a kolumny indeksów muszą być w tej samej kolejności

B. Klasyfikuj nakładające się indeksy:
Kolumny indeksowane z indeksem potomnym powinny być podzbiorem indeksowanych kolumn nadrzędnych z lewej strony w tej samej kolejności, a kolumny dołączane powinny być takie same.

C. Klasyfikuj podobne indeksy:
indeksowane kolumny powinny być identyczne, podczas gdy kolumny dołączane mogą być różne

Jaki udaan z tego wyszedł?

Więcej oszczędności, mniej stresu i szczęśliwych administratorów baz danych!

Okazuje się, że analiza redundancji indeksu była nie tylko gratką dla administratorów baz danych, ale dla całej organizacji. Mamy kilka soczystych korzyści, którymi możemy się pochwalić:

1. Cha Ching! Zaoszczędziliśmy trochę dolarów, zmniejszając rozmiary naszych baz danych bez żadnych problemów z wydajnością. Nasza szczytowa moc obliczeniowa bazy danych spadła średnio o 8%, a pamięć masowa skurczyła się o 10%. Na przykład odchudziliśmy bazę danych z 32 rdzeniami wirtualnymi do bazy danych z 24 rdzeniami wirtualnymi i bam, zaoszczędziliśmy 22% kosztów! Kto i tak potrzebuje tych wszystkich dodatkowych rdzeni?

3. Administratorzy baz danych są w siódmym niebie. Słyszeliśmy nawet pogłoski, że całują swoje monitory z otwartą metryką indeksu redundancji na ekranie. Hej, nie osądzamy — jeśli właśnie tego potrzeba, aby ich uszczęśliwić, niech tak będzie!