Databases

Database-indexfragmentatie en -reorganisatie: Ultieme gids

Index versnippering vertraagt queries meetbaar omdat de fysieke volgorde van de indexpagina's afwijkt van de logische volgorde, waardoor I/O, CPU en wachttijden toenemen. In deze handleiding laat ik zien hoe Reorganisatie, heropbouw, vulfactor en monitoring werken samen om fragmentatie op betrouwbare wijze te herkennen en duurzaam te elimineren.

Centrale punten

Definitie vanGefragmenteerde B* bomen genereren meer I/O en langzamere scans.
OorzakenPagina splitst, verwijdert, verschoven sleutelwaarden.
DrempelsReorg van ~5-30 %, herbouw van ~30 %.
MySQL focusOPTIMIZE TABLE en vulfactoren.
AutomatiseringGeplande taken, online bewerkingen, statistieken.

Wat betekent indexfragmentatie technisch gezien?

Ik noem het Versnippering de discrepantie tussen de logische sleutelvolgorde en de fysieke paginaketen van een B* boomindex. Veel INSERTs, UPDATEs en DELETEs resulteren in gaten, splitsingen en ongeordende bladpagina's, die meer leesbewerkingen veroorzaken. Het resultaat: scans springen vaker, buffer cache hits nemen af en CPU kosten nemen toe. Zelfs ideale plannen lijden eronder omdat het geheugen de verspreide pagina's langzamer aflevert. Ik let daarom altijd op de context van werklast, gegevensgrootte en geheugenindeling.

Soorten fragmentatie en hun symptomen

Ik maak een pragmatisch onderscheid:

Logische fragmentatieDe bladzijden worden niet langer in sleutelvolgorde aan elkaar gekoppeld. Bereikscans vereisen extra sprongen, read-ahead is minder effectief.
Interne fragmentatiePagina's bevatten veel ongebruikte ruimte (lage vulniveaus). Er moeten meer pagina's worden gelezen per resultaatregel; de index wordt groter zonder voordeel.
Structurele fragmentatieOngunstige boomhoogte, onevenwichtige knooppunten of hopen met doorgestuurde records (bijv. in SQL Server). Toegang wordt indirecter.

Dit kan gemeten worden als meer gelezen pagina's per regel, hogere latenties tijdens bereik- of order-by scans en een dalende cache hit rate. Ik correleer de signalen altijd met wachtstatistieken om verwarring met netwerk- of opslagproblemen te voorkomen.

Oorzaken: Invoegingen, updates, paginasplitsingen

Frequente invoegingen vullen pagina's tot aan de rand, waarna een nieuwe toets een Pagina splitsen, waardoor er twee halfgevulde pagina's overblijven. Verwijderingen verwijderen entries, maar vrije ruimte blijft verdeeld en wordt niet altijd lokaal gebruikt bij de volgende insert. Updates die sleutelkolommen veranderen verplaatsen records en creëren meer hiaten. Willekeurige sleutelpatronen zoals GUID's vergroten de spreiding en dus de rommel. Ik minimaliseer splitsingen door de Vulfactor om overeen te komen met de schrijfbelasting.

Prestatieverliezen meetbaar maken

Ik meet fragmentatie niet geïsoleerd, maar in combinatie met querytijden, log reads, page reads en wait classes. Als de gemiddelde latentie van range scans toeneemt en de CPU per query toeneemt, controleer ik eerst de fysieke kengetallen van de indices. Hoge fragmentatie verhoogt het aantal gelezen pagina's per gelijk aantal regels en comprimeert de wachttijden voor I/O. Een goed onderbouwde vergelijking voor en na reorg of rebuild laat het echte voordeel zien. Voor achtergrondinformatie over locking, plannen en bottlenecks is het de moeite waard om te kijken naar Prestaties database, om symptomen correct te categoriseren.

Metriek, wachttijden en pagina-efficiëntie in detail

In de praktijk observeer ik ook:

Pagina's per scanHoeveel bladzijden leest een typische oppervlaktescan? Als de waarde toeneemt met dezelfde resultaathoeveelheid, duidt dit op fragmentatie of te lage vulniveaus.
Vooruit lees hitGefragmenteerde ketens saboteren sequentiële prefetches; het effect is kleiner op SSD's, maar niet nul, omdat CPU, latches en cache blijven lijden.
WachtklassenPAGEIOLATCH/IO-Waits (SQL Server), sequentieel/scattered lezen van db-bestanden (Oracle) of verhoogde InnoDB leeslatenties (MySQL) nemen toe met sterker springen in de index.
CachekwaliteitAls de hitrate van de bufferpool parallel met fragmentatie daalt, is een rebuild bijna altijd de moeite waard - vooral voor grote bereikscans.

Fragmentatie analyseren: SQL Server, MySQL, Oracle

Ik begin de analyse altijd met een betrouwbare Snapshot van de gezondheid van de index en kleine indices uitfilteren waarvan het paginagebruik statistisch fluctueert. In SQL Server geeft sys.dm_db_index_physical_stats de mate van fragmentatie samen met page_count, zodat ik uitschieters kan wegen. Waarden boven de 5-30 % wijzen op reorganisatie, sterke uitschieters boven de 30 % wijzen op een rebuild, vooral bij een grote page_count. In MySQL controleer ik SHOW TABLE STATUS of INFORMATION_SCHEMA views en observeer gegevens en indexlengte in de loop van de tijd. In Oracle controleer ik ook of een online rebuild beschikbaar is om Stilstand te vermijden.

Praktische zoekopdrachten en weging

Ik werk met eenvoudige, herbruikbare query's en geef prioriteit aan de hand van paginagrootte en relevantie:

SQL ServerIk bepaal de fragmentatie en filter kleine indices eruit.

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_fragmentatie_in_percent
VAN sys.indexen 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)Ik kijk naar indexgrootte, vrije ruimte en veranderingssnelheid.
```
SELECTEER TABEL_SCHEMA, TABEL_NAAM, ENGINE, INDEX_LENGTE, DATA_VRIJ
FROM informatie_schema.TABLES
WHERE ENGINE = 'InnoDB'
  EN INDEX_LENGTE > 0
ORDER BY (DATA_FREE) DESC;
```
Tegelijkertijd vergelijk ik de waarden in de tijd (bijvoorbeeld dagelijks) om echte trends te scheiden van uitschieters. Voor statistieken gebruik ik ANALYZE TABLE spaarzaam als de optimiser onjuiste kardinaliteiten aanneemt.
OracleIk controleer segmentstatistieken (vrije ruimtes, extents) en de beschikbaarheid van REBUILD ONLINE om onderhoudsvensters voorspelbaar te houden.

Het is voor mij belangrijk om alleen te kijken naar indexen die veel gebruikt worden. Een gefragmenteerde maar ongebruikte index is eerder een kandidaat voor verwijdering dan voor reorganisatie.

Reorganisatie versus heropbouw: Beslismatrix

Ik kies de methode op basis van de mate van Versnippering en besturingsvensters, omdat niet elke omgeving intensieve I/O-pieken aankan. Reorganisatie herschikt bladzijden, vermindert logische sprongen, comprimeert tot vulfactor en blijft meestal online. Rebuild herbouwt de index, ruimt volledig op, geeft geheugen terug en werkt statistieken bij, maar vereist CPU, I/O en vaak langere sloten. Kleine indexen van minder dan ongeveer 100 pagina's hebben zelden veel voordeel, terwijl grote structuren van 30 % fragmentatie of meer veel voordeel hebben. Ik documenteer de beslissing met kerncijfers zodat het effect begrijpelijk blijft en de Onderhoudsschema past.

Methode	Benodigde middelen	Typisch gebruik	Belangrijkste effect
Reorganisatie	Laag tot gemiddeld	~5-30 % Fragmentatie	Reorganisatie, compressie tot vulfactor
Herbouw	Hoog	> 30 % Fragmentatie	Volledige herbouw, geheugenvrijgave

Online opties, sloten en bijwerkingen

Voor werking met weinig onderbrekingen gebruik ik - waar beschikbaar - Online ombouwen in. Ik let hier goed op:

Editie/VersieOnline functies variëren afhankelijk van de database en editie. Ik controleer elke omgeving afzonderlijk.
Tijdelijke metagegevensvergrendelingenZelfs “online” vereist meestal blokken aan het begin/einde. Ik plan deze bewust in rustige fasen.
Temperatuur/werkbereikOpties zoals SORT_IN_TEMPDB (SQL Server) verminderen de belasting van het hoofdgegevensbestand, maar vereisen extra opslagruimte.
ReplicatieRebuilds vergroten het logvolume. Ik controleer de achterstand van replica's en geef indien nodig gas om vertragingen te voorkomen.

Voor SQL server heaps houd ik rekening met Doorgestuurde records; Hier helpt een tabelrebuild om omleidingen te verwijderen. In Oracle gebruik ik REBUILD ONLINE of MOVE PARTITION (met UPDATE INDEXES) om de downtime te verminderen.

Vulfactor, paginasplitsingen en geheugen

Een geschikte Vulfactor Ik stel tussen 70-90 % in voor tabellen die veel schrijven, zodat toekomstige inserts lokaal vrije ruimte kunnen gebruiken. Als ik de vulfactor te veel verlaag, groeit de index sneller en neemt hij meer geheugen in beslag; als ik hem te hoog instel, nemen splitsingen en fragmentatie toe. Daarom observeer ik de relatie tussen paginagebruik, schrijfbelasting en invoegpatroon over meerdere cycli. Voor herbouw definieer ik bewust de vulfactor per index, niet over de hele linie voor de hele database. Regelmatige controle voorkomt dat een aanvankelijk goede afweging maanden later.

Inzicht in vulfactoren per platform

SQL ServerFILLFACTOR is een indexeigenschap die van kracht wordt tijdens het herbouwen/creëren. Ik stel een lagere waarde in voor zeer vluchtige secundaire indices en een hogere waarde voor leeszware structuren. Ik documenteer de gekozen waarde per index en kalibreer opnieuw na wijzigingen in het belastingsprofiel.
MySQL (InnoDB)Met innodb_vul_factor Ik heb invloed op de vrije ruimte die InnoDB overlaat voor (her)bouwen. Het is niet van toepassing op alledaagse DML, maar met OPTIMIZE/ALTER helpt het om splitsingen in de toekomst te dempen. Ik plan hotspots (monotone sleutels) ook zo dat latchcompetitie en splitsingen worden verminderd.
Oracle en PostgreSQLparameter STORAGE of. VULFACTOR (Postgres) ruimte geven voor vrije lucht in pagina's. Voor schrijfzware tabellen gebruik ik conservatieve vulniveaus en balanceer ik het extra geheugen met meetbaar betere scantijden.

Specifiek voor MySQL en WordPress

In MySQL helpt me OPTIMIZE TABLE bij InnoDB om tabellen en bijbehorende indexen te reorganiseren en vrije ruimte terug te geven. Sterk gefragmenteerde werklasten met veel verwijderingen hebben ook baat bij het periodiek aanmaken van kritieke secundaire indexen. In WordPress-installaties verminder ik rommel zoals revisies en spamcommentaren voordat ik optimaliseer, zodat er minder pagina's opnieuw hoeven te worden geordend. Ik combineer deze stappen met een schone indexstrategie voor wp_postmeta en soortgelijke tabellen die vaak scans veroorzaken. Een praktische inleiding is te vinden in de gids voor WordPress indexen optimaliseren, die typische struikelblokken aanpakt.

MySQL praktijk: OPTIMIZE, partities en neveneffecten

Ik let ook op InnoDB:

TABEL OPTIMALISEREN reconstrueert de tabel (en indices) en kan grotendeels “inplace” draaien, afhankelijk van de versie, maar vereist altijd meta sloten en log vrije ruimte. Ik plan hiervoor speciale tijdvensters.
Verdelen maakt gericht onderhoud mogelijk: OPTIMIZE PARTITION alleen voor hete of zwaar gewiste gebieden vermindert I/O-pieken en runtime.
ReplicatieGrote rebuilds genereren binlog volume en kunnen replicas vertragen. Ik verdeel onderhoud over meerdere nachten of werk in partities.
TABEL ANALYSEREN vernieuwt statistieken die de optimizer nodig heeft voor betere plannen - vooral na grote structurele veranderingen.

In WordPress-omgevingen verminder ik vooraf stroomstoten, revisies en verwijderde berichten, zodat OPTIMIZE minder gegevens verplaatst. Voor wp_postmeta controleer ik of query's specifiek worden uitgevoerd via geschikte samengestelde indices om brede scans te vermijden.

PostgreSQL in het kort

Hoewel de focus hier ligt op MySQL, houd ik rekening met heterogene omgevingen:

VACUUM/Autovacuüm voorkomt ophoping, maar vervangt REINDEX niet als B-boomstructuren erg gefragmenteerd zijn.
GELIJKTIJDIG HERINDEXEREN maakt het mogelijk om nieuwe indices grotendeels online aan te maken met beperkte blokkering.
vulfactor per tabel/index controleert vrije lucht voor toekomstige INSERTs/UPDATEs. Schrijfzware tabellen hebben baat bij lagere waarden.
Scheidingswanden per periode onderhoudsvensters aflossen; REINDEX kan specifiek voor elke partitie worden gebruikt.

Geautomatiseerd onderhoud en drempelwaarden

Ik automatiseer reorg en rebuild met robuust Drempels en activeer alleen indices met een voldoende aantal pagina's om ruis te voorkomen. Taken worden uitgevoerd in onderhoudsvensters, terwijl ik lange operaties uitvoer via online opties met zo min mogelijk downtime. Een gespreide aanpak stelt grote rebuilds uit tot rustige periodes en voert kleine reorgs vaker uit. Ik werk de statistieken bij na grote veranderingen, zodat de optimiser snel betere plannen selecteert. Waarschuwingen worden geactiveerd zodra fragmentatie of latenties vooraf gedefinieerde limieten overschrijden, zodat ik kan ingrijpen voordat gebruikers klagen.

Draaiboek: Volgorde van stappen voor duurzame resultaten

IdentificeerMomentopname van de top N indexen op grootte en fragmentatie, filter kleine indexen.
Geef prioriteit aanSorteren op kriticiteit van de werklast, aantal pagina's en scanbelasting.
PlanningPlan reorg/rebuild volgens drempelwaarden, bereken online opties en temp/log vereisten.
Voer uitStaggering van grote objecten, I/O throttling, replicatievertraging bewaken.
StatistiekenWerk de statistieken bij na een rebuild/OPTIMIZE (of zorg ervoor dat dit automatisch gebeurt).
ValideerVoor/na meten: Latency, gelezen pagina's, wachttijden, cache hit rate.
KalibreerControleer vulfactoren en drempels, documenteer geleerde lessen.

Hostingafstemming: praktische regels

In hostingomgevingen plan ik analyses wekelijks, regelen het I/O-venster van onderhoud en combineren met caching om hotsets in het geheugen te houden. TempDB/redo/binlog parameters en opslagmedia hebben een significante invloed op de waargenomen effecten van defragmentatie. Ik evalueer ook of overbodige indexen alleen maar kosten veroorzaken, omdat elke extra index het schrijfwerk en de kans op fragmentatie vergroot. Voor elke nieuwe index controleer ik werklastpatronen, kardinaliteiten en bestaande dekking. Ik schets typische struikelblokken in dit overzicht van Indexvallen in MySQL, waardoor verkeerde inschattingen worden vermeden.

Kosten/baten en wanneer ik bewust niets doe

Niet elke fragmentatie is het waard om onderhouden te worden. Ik doe het bewust zonder wanneer:

Object is klein (bijv. minder dan 100 pagina's) en sterk fluctueert - hier vallen de voordelen weg.
Zoekopdrachten zijn selectief (voornamelijk lookups per sleutel) en er worden geen bereikscans uitgevoerd.
Werklast is van voorbijgaande aard (migratievenster, binnenkort archivering) - dan plan ik alleen een definitieve herbouw.

In plaats daarvan investeer ik in betere indices, minder redundantie en schone sleutelselectie zodat toekomstige splitsingen minder vaak voorkomen.

Wanneer reorganiseren, wanneer wachten?

Ik breng een Reorganisatie als de fragmentatiegraad matig toeneemt en er genoeg pagina's worden beïnvloed om een echt effect te hebben. Na massale verwijderingen of archivering levert een ordelijke herverdeling vaak merkbare scanwinst op. In het geval van ernstige uitschieters of opslagvereisten, plan ik een herbouw, bij voorkeur online, om verstoring van de werkzaamheden te minimaliseren. Kleine indices van minder dan 100 pagina's laat ik vaak ongemoeid omdat hun lay-out sterk fluctueert en de voordelen minimaal zijn. Ik documenteer de beslissing samen met voor/na-cijfers zodat toekomstige cycli gemakkelijker te plannen zijn.

Langetermijnpreventie door ontwerp

Goed Schemaontwerp vermindert fragmentatie nog voor de eerste invoeging door ervoor te zorgen dat sleutelselectie, gegevenstypes en normalisatie consistent zijn. Ik vermijd extra brede rijen, die minder gegevensrecords per pagina toestaan en splitsingen bevorderen. Partitioneren scheidt koude van warme gegevens en vermindert neveneffecten tijdens onderhoud en back-ups. Zorgvuldige queryoptimalisatie vermindert de afhankelijkheid van dure scans en stemt indexen af op echte patronen. Als de werklast verandert, pas ik de indexdefinities incrementeel aan in plaats van ad hoc hele structuren weg te gooien.

Toetsen selecteren en patroon invoegen

De keuze van de primaire sleutel heeft een beslissende invloed op het splitsgedrag:

Monotone toetsen (bijv. AUTO_INCREMENT, tijdsgebaseerde ID's) bundelen inserts aan de rechterrand, verminderen verstrooiing en splitsingen, maar kunnen hotspots creëren. Ik egaliseer hotspots met buffering/batching.
Gerandomiseerde toetsen (bijv. GUID/UUID v4) verdelen de belasting, maar vergroten de kans op opsplitsing. Sequentiële varianten (bijv. tijdgebaseerde UUID's) zorgen voor een betere balans tussen verdeling en volgorde.
Brede toets de index en het aantal benodigde pagina's verhogen. Slanke, selectieve toetsen zijn duurzamer.

Bovendien verlaagt regel- en paginacompressie de splitsingssnelheid omdat er ruimte is voor meer items per pagina. Ik controleer echter altijd de CPU-kosten en de beschikbaarheid van licenties/functies voordat ik compressie activeer.

Kort samengevat: Stappen met een effect

Ik begin met een gerichte Analyse van de grootste en meest gefragmenteerde indices, prioriteren op basis van page_count en workload criticality. Vervolgens implementeer ik gespreide maatregelen: reorganiseer matige gevallen, herbouw zware gevallen, pas de vulfactoren voor elke index aan. Geautomatiseerde taken handhaven de orde zonder voortdurende handmatige tussenkomst, terwijl waarschuwingen betrouwbaar worden geactiveerd in het geval van uitschieters. MySQL en WordPress omgevingen profiteren aanzienlijk als ik dataverspilling op voorhand verminder en alleen nuttige indices bewaar. Met consistente monitoring, duidelijke drempels en herhaalbare playbooks Prestaties stabiel, zelfs wanneer de gegevens snel groeien.