logo hostingu

Fragmentacja i reorganizacja indeksów bazy danych: Kompletny przewodnik

Fragmentacja indeksu spowalnia zapytania w wymierny sposób, ponieważ fizyczna kolejność stron indeksu różni się od kolejności logicznej, co wydłuża czas we/wy, procesora i oczekiwania. W tym przewodniku pokażę, jak Reorganizacja, Odbudowa, współczynnik wypełnienia i monitorowanie współpracują ze sobą, aby niezawodnie rozpoznawać i trwale eliminować fragmentację.

Punkty centralne

  • DefinicjaPofragmentowane drzewa B* generują więcej operacji we/wy i wolniejsze skanowanie.
  • PrzyczynyPodziały stron, usunięcia, przesunięte wartości kluczy.
  • ProgiReorganizacja z ~5-30 %, przebudowa z ~30 %.
  • Koncentracja na MySQLOPTYMALIZUJ TABELĘ i współczynniki wypełnienia.
  • AutomatyzacjaZaplanowane zadania, operacje online, metryki.

Co technicznie oznacza fragmentacja indeksu?

określam jako Fragmentacja rozbieżność między logiczną sekwencją kluczy a fizycznym łańcuchem stron indeksu drzewa B*. Wiele operacji INSERT, UPDATE i DELETE skutkuje lukami, podziałami i nieuporządkowanymi stronami liścia, które wyzwalają więcej operacji odczytu. Rezultat: skanowanie odbywa się częściej, liczba trafień w buforze maleje, a koszty procesora rosną. Nawet idealne plany cierpią, ponieważ pamięć dostarcza rozproszone strony wolniej. Dlatego zawsze zwracam uwagę na kontekst obciążenie pracą, rozmiar danych i układ pamięci.

Rodzaje fragmentacji i ich objawy

Dokonuję pragmatycznego rozróżnienia:

  • Fragmentacja logicznaStrony liści nie są już łączone w sekwencji kluczy. Skanowanie zakresowe wymaga dodatkowych skoków, read-ahead jest mniej efektywne.
  • Fragmentacja wewnętrznaStrony zawierają dużo niewykorzystanego miejsca (niski poziom wypełnienia). Więcej stron musi zostać odczytanych na wiersz wyników; rozmiar indeksu wzrasta bez korzyści.
  • Fragmentacja strukturalnaNiekorzystna wysokość drzewa, niezrównoważone węzły lub sterty z przekazanymi rekordami (np. w SQL Server). Dostęp staje się bardziej pośredni.

Można to zmierzyć jako większą liczbę stron odczytywanych na wiersz, wyższe opóźnienia podczas skanowania według zakresu lub kolejności oraz spadający wskaźnik trafień w pamięci podręcznej. Zawsze koreluję sygnały ze statystykami oczekiwania, aby uniknąć pomyłki z problemami z siecią lub pamięcią masową.

Przyczyny: Wstawienia, aktualizacje, podziały stron

Częste wstawki zapełniają strony aż do krawędzi, a następnie nowy klawisz wymusza Podział strony, co pozostawia dwie w połowie zapełnione strony. Usunięcia usuwają wpisy, ale wolna przestrzeń pozostaje rozproszona i nie zawsze jest używana lokalnie przy następnym wstawieniu. Aktualizacje, które zmieniają kluczowe kolumny, przenoszą rekordy i tworzą więcej luk. Losowe wzorce kluczy, takie jak identyfikatory GUID, dodatkowo zwiększają rozproszenie, a tym samym bałagan. Minimalizuję podziały, używając Współczynnik wypełnienia aby dopasować obciążenie zapisu.

Mierzalność strat wydajności

Nie mierzę fragmentacji w izolacji, ale w połączeniu z czasami zapytań, odczytami dzienników, odczytami stron i klasami oczekiwania. Jeśli średnie opóźnienie skanowania zakresów wzrasta, a procesor na zapytanie wzrasta, najpierw sprawdzam fizyczne kluczowe wartości indeksów. Wysoka fragmentacja zwiększa liczbę odczytanych stron na równą liczbę wierszy i kompresuje czasy oczekiwania na I/O. Dobrze uzasadnione porównanie przed i po reorg lub rebuild pokazuje prawdziwą korzyść. Aby uzyskać podstawowe informacje na temat blokowania, planów i wąskich gardeł, warto zajrzeć na stronę Wydajność bazy danych, prawidłowej kategoryzacji objawów.

Szczegółowe metryki, czas oczekiwania i wydajność stron

Obserwuję to również w praktyce:

  • Stron na skanIle stron listków odczytuje typowe skanowanie obszaru? Jeśli wartość wzrasta przy tej samej ilości wyników, oznacza to fragmentację lub zbyt niski poziom wypełnienia.
  • Uderzenie z wyprzedzeniemPofragmentowane łańcuchy sabotują sekwencyjne pobieranie wstępne; efekt jest mniejszy na dyskach SSD, ale nie zerowy, ponieważ procesor, zatrzaski i pamięć podręczna nadal cierpią.
  • Klasy oczekującePAGEIOLATCH/IO-Waits (SQL Server), sekwencyjny/rozproszony odczyt pliku db (Oracle) lub zwiększone opóźnienia odczytu InnoDB (MySQL) rosną wraz z silniejszymi skokami w indeksie.
  • Jakość pamięci podręcznejJeśli wskaźnik trafień puli buforów spada równolegle z fragmentacją, przebudowa jest prawie zawsze opłacalna - szczególnie w przypadku skanowania dużych zakresów.

Analiza fragmentacji: SQL Server, MySQL, Oracle

Zawsze zaczynam analizę od wiarygodnego Snapshot kondycji indeksu i odfiltrować małe indeksy, których wykorzystanie stron zmienia się statystycznie. W SQL Server, sys.dm_db_index_physical_stats zapewnia stopień fragmentacji wraz z liczbą stron, dzięki czemu mogę ważyć wartości odstające. Wartości powyżej 5-30 % wskazują na reorganizację, silne wartości odstające powyżej 30 % wskazują na przebudowę, szczególnie przy dużej liczbie stron. W MySQL sprawdzam widoki SHOW TABLE STATUS lub INFORMATION_SCHEMA i obserwuję długość danych i indeksów w czasie. W Oracle sprawdzam również, czy dostępna jest przebudowa online w celu Przestój których należy unikać.

Praktyczne zapytania i ważenie

Pracuję z prostymi zapytaniami wielokrotnego użytku i ustalam priorytety w zależności od rozmiaru strony i trafności:

  • SQL ServerOkreślam fragmentację i filtruję małe indeksy. 
    SELECT DB_NAME() AS db, OBJECT_NAME(i.object_id) AS obj, i.name AS idx,
       ips.index_type_desc, ips.page_count, ips.avg_fragmentation_in_percent
FROM sys.indexes i
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'SAMPLED') ips
WHERE ips.page_count >= 100
ORDER BY ips.avg_fragmentation_in_percent DESC, ips.page_count DESC;
  • MySQL (InnoDB)Patrzę na rozmiar indeksu, wolne miejsce i szybkość zmian. 
    SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, INDEX_LENGTH, DATA_FREE
FROM information_schema.TABLES
WHERE ENGINE = 'InnoDB'
  AND INDEX_LENGTH > 0
ORDER BY (DATA_FREE) DESC;

    Jednocześnie porównuję wartości w czasie (np. codziennie), aby oddzielić rzeczywiste trendy od wartości odstających. W przypadku statystyk używam ANALYZE TABLE oszczędnie, jeśli optymalizator zakłada nieprawidłowe kardynalności.

  • OracleSprawdzam statystyki segmentów (wolne miejsca, rozszerzenia) i dostępność REBUILD ONLINE, aby okna konserwacji były przewidywalne.

Ważne jest dla mnie, aby patrzeć tylko na indeksy o wysokim wykorzystaniu. Fragmentaryczny, ale nieużywany indeks jest bardziej prawdopodobnym kandydatem do usunięcia niż do reorganizacji.

Reorganizacja kontra przebudowa: Matryca decyzyjna

Wybieram metodę w zależności od stopnia Fragmentacja i okien operacyjnych, ponieważ nie każde środowisko może poradzić sobie z intensywnymi szczytami I/O. Reorganizacja zmienia układ stron liści, redukuje skoki logiczne, kompresuje do współczynnika wypełnienia i zwykle pozostaje w trybie online. Przebudowa odbudowuje indeks, czyści go całkowicie, zwraca pamięć i aktualizuje statystyki, ale wymaga procesora, I/O i często dłuższych blokad. Małe indeksy liczące mniej niż około 100 stron rzadko przynoszą duże korzyści, podczas gdy duże struktury o fragmentacji 30 % lub większej znacznie zyskują. Decyzję dokumentuję kluczowymi liczbami, aby efekt pozostał zrozumiały i aby Harmonogram konserwacji pasuje.

Metoda Wymagania dotyczące zasobów Typowe zastosowanie Efekt główny
Reorganizacja Niski do średniego ~5-30 % Fragmentacja Reorganizacja, kompresja do współczynnika wypełnienia
Przebudowa Wysoki > 30 % Fragmentacja Całkowita przebudowa, zwolnienie pamięci

Opcje online, blokady i efekty uboczne

Do pracy z niskimi zakłóceniami używam - tam, gdzie jest to możliwe - Przebudowy online w. Zwracam na to uwagę:

  • Wydanie/wersjaFunkcje online różnią się w zależności od bazy danych i edycji. Sprawdzam każde środowisko osobno.
  • Tymczasowe blokady metadanychNawet “online” zazwyczaj wymaga bloków na początku/końcu. Celowo planuję je w spokojnych fazach.
  • Temperatura/zakresy pracyOpcje takie jak SORT_IN_TEMPDB (SQL Server) zmniejszają obciążenie głównego pliku danych, ale wymagają dodatkowej przestrzeni dyskowej.
  • ReplikacjaPrzebudowy zwiększają objętość dziennika. Monitoruję opóźnienia replik i w razie potrzeby dławię je, aby uniknąć opóźnień.

Dla SQL server heaps biorę pod uwagę Przekazane rekordy; Tutaj przebudowa tabeli pomaga usunąć przekierowania. W Oracle używam REBUILD ONLINE lub MOVE PARTITION (z UPDATE INDEXES), aby skrócić czas przestoju.

Współczynnik wypełnienia, podziały stron i pamięć

Odpowiedni Współczynnik wypełnienia Ustawiam między 70-90 % dla tabel, które dużo piszą, aby przyszłe wstawki mogły lokalnie wykorzystywać wolne miejsce. Jeśli zbytnio obniżę współczynnik wypełnienia, indeks rośnie szybciej i zajmuje więcej pamięci; jeśli ustawię go zbyt wysoko, zwiększy się podział i fragmentacja. Dlatego obserwuję związek między wykorzystaniem strony, obciążeniem zapisu i wzorcem wstawiania w kilku cyklach. W przypadku przebudowy celowo definiuję współczynnik wypełnienia dla indeksu, a nie dla całej bazy danych. Regularne monitorowanie zapobiega początkowo dobremu kompromis miesięcy później.

Zrozumienie współczynników wypełnienia dla każdej platformy

  • SQL ServerFILLFACTOR to właściwość indeksu, która działa podczas przebudowy/tworzenia. Ustawiam niższą wartość dla bardzo niestabilnych indeksów drugorzędnych i wyższą wartość dla struktur o dużym obciążeniu odczytem. Dokumentuję wybraną wartość dla każdego indeksu i ponownie kalibruję po zmianie profilu obciążenia.
  • MySQL (InnoDB)Z innodb_fill_factor Mam wpływ na wolną przestrzeń, którą InnoDB pozostawia do (re)kompilacji. Nie ma to zastosowania do codziennego DML, ale dzięki OPTIMIZE/ALTER pomaga tłumić podziały w przyszłości. Planuję również hotspoty (klucze monotoniczne) w taki sposób, aby zmniejszyć konkurencję zatrzasków i podziały.
  • Oracle i PostgreSQLParametr STORAGE lub. FILLFACTOR (Postgres) dają miejsce na wolne powietrze na stronach. W przypadku tabel o dużym natężeniu zapisu używam konserwatywnych poziomów wypełnienia i równoważę dodatkową pamięć z mierzalnie lepszymi czasami skanowania.

Specyficzne dla MySQL i WordPress

W MySQL pomaga mi OPTYMALIZACJA TABLE w InnoDB, aby zreorganizować tabele i powiązane indeksy oraz zwrócić wolne miejsce. Wysoce pofragmentowane obciążenia z wieloma usunięciami również korzystają z okresowego tworzenia krytycznych indeksów pomocniczych. W instalacjach WordPress redukuję bałagan, taki jak rewizje i komentarze spamowe przed optymalizacją, aby mniej stron wymagało zmiany kolejności. Łączę te kroki ze strategią czystego indeksu dla wp_postmeta i podobnych tabel, które często wywołują skanowanie. Praktyczne wprowadzenie można znaleźć w przewodniku po Optymalizacja indeksów WordPress, który odnosi się do typowych przeszkód.

Praktyka MySQL: OPTIMIZE, partycje i efekty uboczne

Zwracam również uwagę na InnoDB:

  • OPTYMALIZUJ TABELĘ rekonstruuje tabelę (i indeksy) i może działać w dużej mierze “inplace” w zależności od wersji, ale zawsze wymaga meta blokad i wolnego miejsca w dzienniku. Planuję na to dedykowane okna czasowe.
  • Podział na partycje pozwala na ukierunkowaną konserwację: OPTIMIZE PARTITION tylko dla gorących lub mocno wymazanych obszarów zmniejsza szczyty I / O i czas pracy.
  • ReplikacjaDuże przebudowy generują wolumen binlog i mogą opóźniać repliki. Rozkładam konserwację na kilka nocy lub pracuję w partycjach.
  • ANALYZE TABLE odnawia statystyki, których optymalizator potrzebuje do lepszych planów - zwłaszcza po masowych zmianach strukturalnych.

W środowiskach WordPress redukuję z wyprzedzeniem stany nieustalone, rewizje i usunięte posty, aby OPTIMIZE przenosiło mniej danych. W przypadku wp_postmeta sprawdzam, czy zapytania są uruchamiane specjalnie za pomocą odpowiednich indeksów złożonych, aby uniknąć szerokiego skanowania.

PostgreSQL w skrócie

Chociaż skupiamy się tutaj na MySQL, biorę pod uwagę środowiska heterogeniczne:

  • VACUUM/Autovacuum zapobiega rozrostowi, ale nie zastępuje REINDEX, jeśli struktury B-drzewa są bardzo pofragmentowane.
  • REINDEKSOWAĆ JEDNOCZEŚNIE umożliwia tworzenie nowych indeksów w dużej mierze online przy ograniczonym blokowaniu.
  • współczynnik wypełnienia na tabelę/indeks kontroluje wolne miejsce na przyszłe WSTAWIENIA/ AKTUALIZACJE. Niższe wartości są korzystne dla tabel z dużą liczbą zapisów.
  • Przegrody na okres odciążenia okien konserwacji; REINDEX może być używany specjalnie dla każdej partycji.

Zautomatyzowana konserwacja i wartości progowe

Automatyzuję reorg i rebuild przy użyciu solidnych rozwiązań Progi i aktywuję tylko indeksy z wystarczającą liczbą page_count, aby uniknąć szumu. Zadania są uruchamiane w oknach konserwacyjnych, podczas gdy ja wykonuję długie operacje za pośrednictwem opcji online z jak najmniejszym czasem przestoju. Podejście rozłożone w czasie odkłada duże przebudowy na spokojne okresy i częściej uruchamia małe przebudowy. Aktualizuję statystyki po większych zmianach, aby optymalizator szybko wybierał lepsze plany. Alerty są uruchamiane, gdy tylko fragmentacja lub opóźnienia przekroczą wcześniej zdefiniowane limity, dzięki czemu mogę działać, zanim użytkownicy zaczną się skarżyć.

Runbook: Sekwencja kroków w celu osiągnięcia trwałych wyników

  1. IdentyfikacjaMigawka najlepszych N indeksów według rozmiaru i fragmentacji, filtrowanie małych indeksów.
  2. Ustalanie priorytetówSortuj według krytyczności obciążenia, liczby stron i obciążenia skanowania.
  3. PlanowanieZaplanuj reorg/rebuild zgodnie z wartościami progowymi, oblicz opcje online i wymagania temp/log.
  4. WykonanieStaggering dużych obiektów, I/O throttling, monitorowanie opóźnień replikacji.
  5. StatystykiZaktualizuj statystyki po przebudowie/OPTIMIZE (lub upewnij się, że jest to wykonywane automatycznie).
  6. WalidacjaPomiar przed/po: Opóźnienie, odczytane strony, czas oczekiwania, współczynnik trafień pamięci podręcznej.
  7. KalibracjaSprawdź współczynniki wypełnienia i progi, udokumentuj wyciągnięte wnioski.

Dostrajanie hostingu: praktyczne zasady

W środowiskach hostingowych planuję analizy tygodnik, regulują okno I/O konserwacji i łączą się z buforowaniem, aby utrzymać hotsety w pamięci. Parametry TempDB/redo/binlog i nośniki pamięci znacząco wpływają na postrzegane efekty defragmentacji. Oceniam również, czy zbędne indeksy generują tylko koszty, ponieważ każdy dodatkowy indeks zwiększa pracę zapisu i szanse na fragmentację. Przed każdym nowym indeksem sprawdzam wzorce obciążenia, kardynalność i istniejące pokrycie. Przedstawiam typowe przeszkody w tym przeglądzie Pułapki indeksów w MySQL, co pozwala uniknąć błędnych ocen.

Koszty/korzyści i kiedy świadomie nic nie robię

Nie każda fragmentacja jest warta utrzymania. Celowo się bez nich obywam:

  • Obiekt jest mały (np. mniej niż 100 stron) i ulega znacznym wahaniom - w tym miejscu korzyści spadają.
  • Zapytania są selektywne (głównie wyszukiwania na klucz) i nie są uruchamiane żadne skanowania zakresu.
  • Obciążenie pracą jest przejściowe (okno migracji, archiwizacja wkrótce) - wtedy planuję tylko ostateczną przebudowę.

Zamiast tego inwestuję w lepsze indeksy, mniej nadmiarowości i czystą selekcję kluczy, aby przyszłe podziały zdarzały się rzadziej.

Kiedy reorganizować, a kiedy czekać?

Wydaję Reorganizacja jeśli stopień fragmentacji wzrasta umiarkowanie i dotyczy wystarczającej liczby stron, aby uzyskać rzeczywisty efekt. Po masowym usunięciu lub archiwizacji, uporządkowana redystrybucja często przynosi zauważalne korzyści w zakresie skanowania. W przypadku poważnych wartości odstających lub wymagań dotyczących pamięci masowej planuję przebudowę, najlepiej online, aby zminimalizować zakłócenia w działaniu. Często pozostawiam małe indeksy liczące mniej niż 100 stron bez zmian, ponieważ ich układ ulega znacznym wahaniom, a korzyści są minimalne. Dokumentuję decyzję wraz z danymi przed i po, aby łatwiej było zaplanować przyszłe cykle.

Długoterminowe zapobieganie poprzez projektowanie

Dobry Projekt schematu zmniejsza fragmentację jeszcze przed pierwszym wstawieniem, zapewniając spójność wyboru klucza, typów danych i normalizacji. Unikam bardzo szerokich wierszy, które pozwalają na mniejszą liczbę rekordów danych na stronę i sprzyjają podziałom. Partycjonowanie oddziela zimne dane od gorących i zmniejsza efekty uboczne podczas konserwacji i tworzenia kopii zapasowych. Staranna optymalizacja zapytań zmniejsza zależność od kosztownych skanów i dostosowuje indeksy do rzeczywistych wzorców. W miarę jak zmieniają się obciążenia, dostosowuję definicje indeksów przyrostowo, zamiast odrzucać całe struktury ad hoc.

Wybór klucza i wzór wstawiania

Wybór klucza głównego ma decydujący wpływ na zachowanie podziału:

  • Monotonne klawisze (np. AUTO_INCREMENT, identyfikatory oparte na czasie) łączą wstawki przy prawej krawędzi, zmniejszają rozproszenie i podziały, ale mogą tworzyć hotspoty. Wyrównuję hotspoty za pomocą buforowania / grupowania.
  • Klucze losowe (np. GUID/UUID v4) rozkładają obciążenie, ale zwiększają prawdopodobieństwo podziału. Warianty sekwencyjne (np. identyfikatory UUID oparte na czasie) lepiej równoważą dystrybucję i kolejność.
  • Szeroki klucz zwiększają indeks i liczbę wymaganych stron. Szczupłe, selektywne klucze są bardziej zrównoważone.

Ponadto kompresja wierszy i stron zmniejsza współczynnik podziału, ponieważ jest miejsce na więcej wpisów na stronę. Jednak przed włączeniem kompresji zawsze sprawdzam koszty procesora i dostępność licencji/funkcji.

Krótkie podsumowanie: Kroki z efektem

Zaczynam od skupienia Analiza największych i najbardziej pofragmentowanych indeksów, nadając im priorytety według page_count i krytyczności obciążenia. Następnie wdrażam działania rozłożone w czasie: reorganizuję umiarkowane przypadki, odbudowuję ciężkie przypadki, ponownie dostosowuję współczynniki wypełnienia dla każdego indeksu. Zautomatyzowane zadania utrzymują porządek bez ciągłej ręcznej interwencji, podczas gdy alerty niezawodnie uruchamiają się w przypadku wartości odstających. Środowiska MySQL i WordPress przynoszą zauważalne korzyści, jeśli wcześniej ograniczę marnotrawstwo danych i zachowam tylko przydatne indeksy. Dzięki spójnemu monitorowaniu, jasnym progom i powtarzalnym playbookom Wydajność stabilny - nawet w przypadku szybkiego przyrostu danych.

Artykuły bieżące