Frammentazione e riorganizzazione degli indici di database: guida definitiva

Frammentazione dell'indice rallenta le query in modo significativo perché l'ordine fisico delle pagine dell'indice differisce da quello logico, aumentando i tempi di I/O, CPU e attesa. In questa guida vi mostrerò come Riorganizzazione, ricostruzione, fattore di riempimento e monitoraggio lavorano insieme per riconoscere in modo affidabile ed eliminare in modo sostenibile la frammentazione.

Punti centrali

• Definizione diGli alberi B* frammentati generano più I/O e scansioni più lente.
• CauseDivisione della pagina, cancellazioni, valori chiave spostati.
• SoglieRiorganizzazione da ~5-30 %, ricostruzione da ~30 %.
• Focus su MySQLOTTIMIZZA TABELLA e fattori di riempimento.
• AutomazioneLavori programmati, operazioni online, metriche.

Cosa significa tecnicamente frammentazione dell'indice?

Mi riferisco a Frammentazione la discrepanza tra la sequenza di chiavi logiche e la catena di pagine fisiche di un indice ad albero B*. Molte INSERZIONI, AGGIORNAMENTI e CANCELLAZIONI generano spazi vuoti, suddivisioni e pagine foglia non ordinate, che innescano un maggior numero di operazioni di lettura. Il risultato è che le scansioni saltano più frequentemente, le visite alla cache del buffer diminuiscono e i costi della CPU aumentano. Anche i piani ideali ne risentono, perché la memoria fornisce le pagine sparse più lentamente. Per questo motivo, faccio sempre attenzione al contesto di carico di lavoro, la dimensione dei dati e la disposizione della memoria.

Tipi di frammentazione e relativi sintomi

Faccio una distinzione pragmatica:

• Frammentazione logicaLe pagine delle ante non sono più concatenate in sequenza di chiavi. Le scansioni dell'intervallo richiedono salti aggiuntivi, la lettura anticipata è meno efficace.
• Frammentazione internaLe pagine contengono molto spazio inutilizzato (bassi livelli di riempimento). È necessario leggere più pagine per ogni riga di risultato; la dimensione dell'indice aumenta senza benefici.
• Frammentazione strutturaleAltezza dell'albero sfavorevole, nodi sbilanciati o heap con record inoltrati (ad esempio in SQL Server). Gli accessi diventano più indiretti.

Questo può essere misurato come un maggior numero di pagine lette per riga, latenze più elevate durante le scansioni per intervallo o per ordine e un tasso di hit della cache in calo. Metto sempre in relazione i segnali con le statistiche di attesa per evitare di confondere i problemi di rete o di archiviazione.

Cause: Inserzioni, aggiornamenti, suddivisione delle pagine

Gli inserti frequenti riempiono le pagine fino al bordo, poi una nuova chiave costringe ad un Divisione della pagina, che lascia due pagine riempite a metà. Le cancellazioni rimuovono le voci, ma lo spazio libero rimane distribuito e non sempre viene utilizzato localmente con l'inserimento successivo. Gli aggiornamenti che modificano le colonne chiave spostano i record e creano ulteriori spazi vuoti. I modelli di chiave randomizzati, come i GUID, aumentano ulteriormente la dispersione e quindi il disordine. Riduco al minimo le suddivisioni utilizzando l'opzione Fattore di riempimento per adattarsi al carico di scrittura.

Rendere misurabili le perdite di prestazioni

Non misuro la frammentazione isolatamente, ma in combinazione con i tempi delle query, le letture dei log, le letture delle pagine e le classi di attesa. Se la latenza media delle scansioni dell'intervallo aumenta e la CPU per query aumenta, controllo innanzitutto le chiavi fisiche degli indici. Un'elevata frammentazione aumenta il numero di pagine lette per un uguale numero di righe e comprime i tempi di attesa per l'I/O. Un confronto fondato prima e dopo la riorganizzazione o la ricostruzione mostra il reale beneficio. Per informazioni di base su locking, piani e colli di bottiglia, vale la pena dare un'occhiata a Prestazioni del database, per classificare correttamente i sintomi.

Metriche, attese ed efficienza della pagina in dettaglio

In pratica, osservo anche:

• Pagine per scansioneQuante pagine di foglie legge una tipica scansione dell'area? Se il valore aumenta con la stessa quantità di risultati, ciò indica una frammentazione o livelli di riempimento troppo bassi.
• Risposta positiva in letturaLe catene frammentate sabotano i prefetches sequenziali; l'effetto è minore sugli SSD, ma non nullo, poiché CPU, latches e cache continuano a soffrire.
• Classi di attesaPAGEIOLATCH/IO-Waits (SQL Server), lettura sequenziale/sparsa del file db (Oracle) o aumento delle latenze di lettura di InnoDB (MySQL) aumentano con un salto maggiore nell'indice.
• Qualità della cacheSe il tasso di risposta del pool di buffer diminuisce parallelamente alla frammentazione, una ricostruzione è quasi sempre utile, soprattutto per le scansioni di grandi dimensioni.

Analizzare la frammentazione: SQL Server, MySQL, Oracle

Inizio sempre l'analisi con un'analisi affidabile Istantanea di salute dell'indice e filtrare i piccoli indici il cui utilizzo delle pagine fluttua statisticamente. In SQL Server, sys.dm_db_index_physical_stats fornisce il grado di frammentazione insieme al conteggio delle pagine, in modo da poter pesare i valori anomali. Valori superiori a 5-30 % indicano una riorganizzazione, mentre forti valori anomali superiori a 30 % indicano una ricostruzione, soprattutto con un numero di pagine elevato. In MySQL, controllo le viste SHOW TABLE STATUS o INFORMATION_SCHEMA e osservo la lunghezza dei dati e degli indici nel tempo. In Oracle, verifico anche se è disponibile una ricostruzione online per Tempi di inattività da evitare.

Interrogazioni pratiche e ponderazione

Lavoro con query semplici e riutilizzabili e stabilisco le priorità in base alle dimensioni della pagina e alla rilevanza:

• SQL ServerDetermino la frammentazione e filtro i piccoli indici.

  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
  DA sys.indexes i
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'SAMPLED') ips
  DOVE ips.page_count >= 100
  ORDER BY ips.avg_fragmentation_in_percent DESC, ips.page_count DESC;

• MySQL (InnoDB)Guardo alle dimensioni dell'indice, allo spazio libero e al tasso di variazione.

  SELEZIONARE TABLE_SCHEMA, NOME_TABELLA, MOTORE, LUNGHEZZA_INDICE, DATI_LIBERI
  DA informazioni_schema.TABELLE
  DOVE MOTORE = 'InnoDB'
    E LUNGHEZZA_INDICE > 0
  ORDER BY (DATA_FREE) DESC;

  Allo stesso tempo, confronto i valori nel tempo (ad esempio, giornalmente) per separare le tendenze reali dagli outlier. Per le statistiche, uso ANALYZE TABLE con parsimonia se l'ottimizzatore assume cardinalità errate.

• OracoloControllo le statistiche dei segmenti (spazi liberi, estensioni) e la disponibilità di REBUILD ONLINE per mantenere le finestre di manutenzione prevedibili.

Per me è importante esaminare solo gli indici con un elevato utilizzo. Un indice frammentato ma inutilizzato è più probabile che sia candidato alla rimozione che alla riorganizzazione.

Riorganizzazione vs. ricostruzione: Matrice decisionale

Scelgo il metodo in base al grado di Frammentazione e finestre operative, perché non tutti gli ambienti sono in grado di gestire picchi di I/O intensivi. La riorganizzazione riordina le pagine delle foglie, riduce i salti logici, comprime per riempire il fattore di riempimento e di solito rimane online. Rebuild ricostruisce l'indice, lo ripulisce completamente, restituisce memoria e aggiorna le statistiche, ma richiede CPU, I/O e spesso blocchi più lunghi. Gli indici di piccole dimensioni, inferiori a circa 100 pagine, raramente traggono grandi benefici, mentre le strutture di grandi dimensioni, con una frammentazione di 30 % o più, ne traggono un vantaggio significativo. Documento la decisione con le cifre chiave, in modo che l'effetto rimanga comprensibile e la Programma di manutenzione si adatta.

Metodo	Requisiti delle risorse	Utilizzo tipico	Effetto principale
Riorganizzazione	Da basso a medio	~5-30 % Frammentazione	Riorganizzazione, compressione del fattore di riempimento
Ricostruzione	Alto	> 30 % Frammentazione	Ricostruzione completa, rilascio della memoria

Opzioni online, chiusure ed effetti collaterali

Per il funzionamento a bassa interruzione, utilizzo, ove disponibile, il Ricostruzioni online in. Presto attenzione a questo aspetto:

• Edizione/VersioneLe funzionalità online variano a seconda del database e dell'edizione. Verifico ogni ambiente separatamente.
• Blocchi temporanei dei metadatiAnche “online” di solito richiede blocchi all'inizio e alla fine. Li programmo deliberatamente in fasi tranquille.
• Intervalli di temperatura/lavoroOpzioni come SORT_IN_TEMPDB (SQL Server) riducono il carico sul file di dati principale, ma richiedono uno spazio di archiviazione aggiuntivo.
• ReplicaLe ricostruzioni aumentano il volume dei registri. Monitoro il ritardo delle repliche e, se necessario, le riduco per evitare ritardi.

Per gli heap di SQL server tengo conto di Record inoltrati; In questo caso, una ricostruzione della tabella aiuta a rimuovere i reindirizzamenti. In Oracle, utilizzo REBUILD ONLINE o MOVE PARTITION (con UPDATE INDEXES) per ridurre i tempi di inattività.

Fattore di riempimento, suddivisione delle pagine e memoria

Un'adeguata Fattore di riempimento Ho impostato tra 70-90 % per le tabelle che scrivono molto, in modo che gli inserti futuri possano utilizzare lo spazio libero localmente. Se abbasso troppo il fattore di riempimento, l'indice cresce più velocemente e occupa più memoria; se lo imposto troppo alto, aumentano le suddivisioni e la frammentazione. Pertanto, osservo la relazione tra utilizzo delle pagine, carico di scrittura e modello di inserimento per diversi cicli. Per le ricostruzioni, definisco deliberatamente il fattore di riempimento per ogni indice e non per l'intero database. Il monitoraggio regolare impedisce che un'iniziale buona compromesso mesi dopo.

Comprendere i fattori di riempimento per piattaforma

• SQL ServerFILLFACTOR è una proprietà dell'indice che ha effetto durante la ricostruzione/creazione. Imposto un valore più basso per gli indici secondari molto volatili e un valore più alto per le strutture pesanti da leggere. Documento il valore selezionato per ogni indice e lo ricalibro dopo le modifiche del profilo di carico.
• MySQL (InnoDB)Con innodb_fill_factor Influenza lo spazio libero che InnoDB lascia per le (ri)costruzioni. Non si applica al DML di tutti i giorni, ma con OPTIMIZE/ALTER aiuta a ridurre gli split in futuro. Inoltre, pianifico gli hotspot (chiavi monotone) in modo da ridurre la concorrenza tra i latch e gli split.
• Oracle e PostgreSQLparametro STORAGE o. FATTORE DI RIEMPIMENTO (Postgres) lasciano spazio all'aria libera nelle pagine. Per le tabelle ad alta densità di scrittura, utilizzo livelli di riempimento conservativi e compenso la memoria extra con tempi di scansione nettamente migliori.

Specifico per MySQL e WordPress

In MySQL mi aiuta OPTIMIZE TABLE di InnoDB per riorganizzare le tabelle e gli indici associati e restituire spazio libero. Anche i carichi di lavoro molto frammentati con molte cancellazioni traggono vantaggio dalla creazione periodica di indici secondari critici. Nelle installazioni di WordPress, riduco il disordine, come le revisioni e i commenti di spam, prima di ottimizzare, in modo da ridurre il numero di pagine da riordinare. Combino questi passaggi con una strategia di indicizzazione pulita per wp_postmeta e tabelle simili che spesso attivano scansioni. Un'introduzione pratica si trova nella guida a Ottimizzare gli indici di WordPress, che affronta i tipici ostacoli.

Pratica di MySQL: OPTIMIZE, partizioni ed effetti collaterali

Presto anche attenzione a InnoDB:

• OTTIMIZZA TABELLA ricostruisce la tabella (e gli indici) e può essere eseguito in gran parte “inplace” a seconda della versione, ma richiede sempre meta lock e spazio libero per i log. Ho previsto finestre temporali dedicate per questo.
• Suddivisione consente una manutenzione mirata: OPTIMIZE PARTITION solo per le aree calde o fortemente cancellate riduce i picchi di I/O e i tempi di esecuzione.
• ReplicaLe grandi ricostruzioni generano un volume di binlog e possono ritardare le repliche. Distribuisco la manutenzione su più notti o lavoro in partizioni.
• ANALIZZA TABELLA rinnova le statistiche di cui l'ottimizzatore ha bisogno per migliorare i piani, soprattutto dopo le massicce modifiche strutturali.

In ambienti WordPress, riduco in anticipo transitori, le revisioni e i post cancellati, in modo che OPTIMIZE sposti meno dati. Per wp_postmeta, verifico se le query vengono eseguite in modo specifico tramite indici compositi adeguati, per evitare scansioni ampie.

Specifiche di PostgreSQL in breve

Anche se l'attenzione è rivolta a MySQL, tengo conto di ambienti eterogenei:

• VUOTO/Autovuoto previene il bloat, ma non sostituisce REINDEX se le strutture B-tree sono molto frammentate.
• REINDICIZZARE CONTEMPORANEAMENTE consente di creare nuovi indici in gran parte online con un blocco limitato.
• fattore di riempimento per tabella/indice controlla l'aria libera per futuri INSERT/UPDATE. Le tabelle ad alta intensità di scrittura traggono vantaggio da valori più bassi.
• Divisori per periodo alleviare le finestre di manutenzione; REINDEX può essere utilizzato specificamente per ogni partizione.

Manutenzione automatica e valori soglia

Automatizzo la riorganizzazione e la ricostruzione utilizzando il sistema robusto Soglie e attivare solo gli indici con un numero di pagine sufficiente a evitare il rumore. I lavori vengono eseguiti nelle finestre di manutenzione, mentre le operazioni lunghe vengono eseguite tramite le opzioni online con il minor tempo di inattività possibile. Un approccio scaglionato rimanda le grandi ricostruzioni ai periodi di calma ed esegue le piccole ricostruzioni più frequentemente. Aggiorno le statistiche dopo le modifiche più importanti, in modo che l'ottimizzatore selezioni tempestivamente i piani migliori. Gli avvisi vengono attivati non appena la frammentazione o le latenze superano i limiti predefiniti, in modo da poter agire prima che gli utenti si lamentino.

Runbook: Sequenza di passi per risultati sostenibili

1. IdentificareIstantanea dei primi N indici per dimensione e frammentazione, filtrare gli indici piccoli.
2. Definire le prioritàOrdinamento per criticità del carico di lavoro, numero di pagine e carico di scansione.
3. PianificazionePianifica la riorganizzazione/ricostruzione in base ai valori di soglia, calcola le opzioni online e i requisiti di temp/log.
4. EseguireScaglionamento degli oggetti di grandi dimensioni, strozzatura dell'I/O, monitoraggio dei ritardi di replica.
5. StatisticheAggiornare le statistiche dopo la ricostruzione/ottimizzazione (o assicurarsi che ciò avvenga automaticamente).
6. ConvalidareMisurare prima/dopo: Latenza, pagine lette, tempi di attesa, tasso di accesso alla cache.
7. CalibrareControllare i fattori di riempimento e le soglie, documentare le lezioni apprese.

Messa a punto dell'hosting: regole pratiche

Negli ambienti di hosting prevedo analisi settimanale, regolano la finestra di I/O della manutenzione e si combinano con la cache per mantenere gli hotset in memoria. I parametri TempDB/redo/binlog e i supporti di memorizzazione influenzano in modo significativo gli effetti percepiti della deframmentazione. Valuto anche se gli indici superflui generano solo costi, perché ogni indice aggiuntivo aumenta il lavoro di scrittura e le possibilità di frammentazione. Prima di ogni nuovo indice, verifico i modelli di carico di lavoro, le cardinalità e la copertura esistente. In questa panoramica di indici, illustro i tipici ostacoli. Trappole per indici in MySQL, che evita valutazioni errate.

Costi/benefici e quando non faccio nulla consapevolmente

Non tutte le frammentazioni valgono la pena di essere mantenute. Io ne faccio deliberatamente a meno quando:

• L'oggetto è piccolo (ad esempio, meno di 100 pagine) e fluttua notevolmente: è qui che i vantaggi vengono meno.
• Le interrogazioni sono selettive (principalmente ricerche per chiave) e non vengono eseguite scansioni dell'intervallo.
• Il carico di lavoro è transitorio (finestra di migrazione, archiviazione a breve) - poi prevedo solo una ricostruzione finale.

Invece, investo in indici migliori, in una minore ridondanza e in una selezione pulita delle chiavi, in modo che le future scissioni si verifichino con minore frequenza.

Quando riorganizzare, quando aspettare?

Sto rilasciando un Riorganizzazione se il grado di frammentazione aumenta moderatamente e le pagine interessate sono sufficienti per avere un effetto reale. Dopo cancellazioni o archiviazioni di massa, una ridistribuzione ordinata porta spesso a guadagni di scansione evidenti. In caso di gravi anomalie o di necessità di archiviazione, pianifico una ricostruzione, preferibilmente online, per ridurre al minimo le interruzioni delle operazioni. Spesso lascio inalterati i piccoli indici di meno di 100 pagine, perché il loro layout è molto variabile e i vantaggi sono minimi. Documento la decisione con i dati prima/dopo, in modo che i cicli futuri siano più facili da pianificare.

Prevenzione a lungo termine attraverso la progettazione

Buono Progettazione dello schema riduce la frammentazione già prima del primo inserimento, assicurando che la selezione delle chiavi, i tipi di dati e la normalizzazione siano coerenti. Evito le righe extra-large, che permettono di avere meno record di dati per pagina e favoriscono le suddivisioni. Il partizionamento separa i dati freddi da quelli caldi e riduce gli effetti collaterali durante la manutenzione e i backup. Un'attenta ottimizzazione delle query riduce il ricorso a scansioni costose e allinea gli indici ai modelli del mondo reale. Quando i carichi di lavoro cambiano, modifico le definizioni degli indici in modo incrementale, invece di scartare intere strutture ad hoc.

Selezione dei tasti e modello di inserimento

La scelta della chiave primaria ha un'influenza decisiva sul comportamento della suddivisione:

• Tasti monotoni (ad esempio AUTO_INCREMENT, ID basati sul tempo) inserisce i bundle sul bordo destro, riduce la dispersione e le suddivisioni, ma può creare hotspot. Io equalizzo gli hotspot con il buffering/batching.
• Chiavi randomizzate (ad esempio GUID/UUID v4) distribuiscono il carico, ma aumentano la probabilità di divisione. Le varianti sequenziali (ad esempio gli UUID basati sul tempo) bilanciano meglio la distribuzione e l'ordine.
• Chiave larga aumentano l'indice e il numero di pagine necessarie. Le chiavi snelle e selettive sono più sostenibili.

Inoltre, la compressione delle righe e delle pagine riduce la velocità di divisione perché c'è spazio per più voci per pagina. Tuttavia, prima di attivare la compressione, verifico sempre i costi della CPU e la disponibilità di licenze/funzioni.

In breve: Passi con effetto

Inizio con una focalizzazione Analisi degli indici più grandi e più frammentati, assegnando le priorità in base al numero di pagine e alla criticità del carico di lavoro. Quindi attuo misure scaglionate: riorganizzo i casi moderati, ricostruisco i casi pesanti, riadatto i fattori di riempimento per ogni indice. I lavori automatizzati mantengono l'ordine senza un costante intervento manuale, mentre gli avvisi si attivano in modo affidabile in caso di anomalie. Gli ambienti MySQL e WordPress traggono notevoli vantaggi se si riducono preventivamente gli sprechi di dati e si mantengono solo gli indici utili. Con un monitoraggio coerente, soglie chiare e playbook ripetibili Prestazioni stabile, anche quando i dati crescono rapidamente.