Tabelle di partizione per la scalabilità

Le decisioni di partizionamento sono quasi permanenti. Una chiave di partizione mal scelta rende le prestazioni peggiori rispetto a una tabella non partizionata e la ripartizione di una tabella multi-terabyte richiede la ricompilazione completa con ore di inattività. La chiave di partizione e l'allineamento dell'indice scelti durante la progettazione determinano se il partizionamento migliora il sistema o crea incubi di manutenzione che non è possibile annullare facilmente.

Il partizionamento delle tabelle divide le tabelle di grandi dimensioni in parti più piccole e gestibili (partizioni) mantenendole come singola tabella logica. L'applicazione vede una tabella, ma il motore di database gestisce più segmenti fisici che possono essere gestiti, archiviati o sottoposti a query in modo indipendente.

Informazioni sui concetti relativi al partizionamento

Il partizionamento include diversi componenti chiave: una funzione di partizione che definisce la modalità di divisione dei dati, uno schema di partizione che esegue il mapping delle partizioni ai filegroup e la colonna di partizionamento che determina la partizione a cui appartiene ogni riga. La comprensione di questi concetti consente di progettare una strategia di partizionamento efficace.

Valutare le prestazioni e i vantaggi operativi

Il partizionamento offre miglioramenti delle prestazioni delle query tramite l'eliminazione delle partizioni in cui le query filtrano in base all'accesso tramite chiave di partizione solo le partizioni rilevanti (un mese anziché 120 mesi), l'elaborazione parallela in cui più partizioni vengono elaborate simultaneamente tra core CPU, statistiche più veloci calcolate per partizione anziché l'intera tabella e l'indice cerca dove le partizioni più piccole indicano alberi B più bassi.

I vantaggi operativi includono la manutenzione granulare in cui si ricompilano gli indici nella partizione corrente, mentre le partizioni meno recenti rimangono online, l'archiviazione rapida passando le vecchie partizioni alle tabelle archiviate in pochi secondi tramite operazioni sui metadati, migliorando la disponibilità dalla gestione indipendente delle partizioni e l'archiviazione a livelli spostando le partizioni precedenti in un archivio più economico e lento.

Si supponga, ad esempio, che una società di servizi finanziari con una tabella di transazioni da 1,2 TB in cui le query filtrano in base alla data (90% di query) analizzano l'intera tabella. Dopo l'implementazione del partizionamento mensile, le prestazioni delle query migliorano da 10 a 20 volte fino all'eliminazione delle partizioni, le ricompilazione degli indici vanno da 6 ore a 20 minuti per partizione, l'archiviazione dei dati obsoleti riduce i blocchi di quattro ore ai secondi usando il cambio di partizione e i costi di archiviazione diminuiscono di 40% spostando le partizioni meno recenti nell'archiviazione più economica.

Comprendere quando usare il partizionamento

La tabella seguente illustra quando il partizionamento è utile e quando invece aggiunge complessità non necessarie:

Creare componenti di partizionamento

L'esempio seguente illustra i tre componenti: funzione di partizione, schema di partizione e tabella partizionata:

-- Create partition function based on date ranges
-- Use RANGE RIGHT for datetime columns to keep same-day values together
CREATE PARTITION FUNCTION PF_OrderDate (DATETIME2)
    AS RANGE RIGHT FOR VALUES 
    ('2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01');

-- Create partition scheme mapping to a single filegroup (recommended)
-- Use multiple filegroups only for tiered storage or independent backups
CREATE PARTITION SCHEME PS_OrderDate
    AS PARTITION PF_OrderDate ALL TO ([PRIMARY]);

-- Create partitioned table
-- Include partition column in primary key for clustered index alignment
CREATE TABLE Orders (
    OrderID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    CustomerID INT,
    Amount DECIMAL(10,2),
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate)
) ON PS_OrderDate(OrderDate);

In questo esempio vengono create partizioni trimestrali per una tabella Orders . La funzione di partizione definisce quattro valori limite (gennaio, aprile, luglio, ottobre) creando cinque partizioni: una per i dati precedenti al 2024 e quattro per ogni trimestre del 2024. Lo schema di partizione esegue il mapping di tutte le partizioni al filegroup PRIMARY. La tabella Orders usa la colonna OrderDate come chiave di partizione, che deve essere inclusa nella chiave primaria per un corretto allineamento dell'indice.

Scegliere le strategie di partizionamento

La chiave di partizione è la decisione più importante. Se si sceglie male, il partizionamento può fare più danni che benefici. La chiave di partizione ideale viene visualizzata nella WHERE clausola della maggior parte delle query, crea partizioni ragionevolmente bilanciate e si allinea ai modelli di manutenzione.

I criteri di selezione della chiave seguenti consentono di scegliere la chiave di partizione corretta:

• Modelli di query: 80%+ di query filtrano in base a questa colonna
• Distribuzione dei dati: distribuzione uniforme tra partizioni (nessuna singola partizione con 90% di dati)
• Allineamento della manutenzione: corrisponde ai modelli di archiviazione/eliminazione (colonne di data per l'archiviazione basata sul tempo)
• Stabilità: il valore non cambia dopo INSERT (evitare il partizionamento nelle colonne aggiornabili)

Comprendere il partizionamento dell'intervallo

Il partizionamento di intervalli divide i dati in base agli intervalli di valori, più comunemente date. Ogni partizione contiene un intervallo specifico (dati di gennaio, dati di febbraio e così via). Questa è la strategia più usata.

Ecco dove il partizionamento di intervalli funziona meglio:

• Dati delle serie temporali (ordini, log, transazioni)
• Dati sequenziali (numeri di fattura, ID ordine)
• Intervalli numerici (bande di stipendio, livelli di prezzo)

La tabella seguente illustra i modelli di partizione comuni:

Ad esempio, una piattaforma di e-commerce che partiziona gli ordini mensilmente permette alle query del mese corrente di accedere a una singola partizione, ai report trimestrali di accedere a 3 partizioni e all'analisi di fine anno di utilizzare 12 partizioni, rimuovendo i dati degli anni precedenti.

È possibile creare partizioni di intervallo definendo i limiti nella funzione di partizione:

-- RANGE RIGHT creates 5 partitions: <100000, 100000-199999, 200000-299999, 300000-399999, >=400000
CREATE PARTITION FUNCTION PF_InvoiceNumber (INT)
    AS RANGE RIGHT FOR VALUES 
    (100000, 200000, 300000, 400000);

Partizionare per valori categorici

È possibile usare il RANGE partizionamento con valori stringa o categorici come aree. La funzione di partizione inserisce i valori in base all'ordine di ordinamento. Questo approccio funziona per la distribuzione geografica, i sistemi multi-tenant o i dati di reparto in cui le query filtrano frequentemente per categoria.

L'esempio seguente partiziona i dati in base all'area:

-- Partition by region
CREATE PARTITION FUNCTION PF_Region (NVARCHAR(50))
    AS RANGE LEFT FOR VALUES ('East', 'North', 'South', 'West');

CREATE PARTITION SCHEME PS_Region
    AS PARTITION PF_Region ALL TO ([PRIMARY]);

CREATE TABLE RegionalData (
    DataID INT NOT NULL,
    Region NVARCHAR(50) NOT NULL,
    Value DECIMAL(10,2),
    CONSTRAINT PK_RegionalData PRIMARY KEY (DataID, Region)
) ON PS_Region(Region);

Implementare il partizionamento dell'indice

Quando si partiziona una tabella, gli indici possono essere allineati o non allineati. Gli indici allineati usano lo stesso schema di partizione della tabella, mentre gli indici non allineati usano partizionamento diverso o nessun partizionamento. Per impostazione predefinita, gli indici non cluster nelle tabelle partizionate ereditano lo schema di partizione della tabella.

Informazioni sugli indici allineati e non allineati

Gli indici allineati usano la stessa funzione di partizione della tabella. Ogni partizione di indice corrisponde a una partizione di tabella, consentendo il cambio di partizione veloce, la manutenzione semplificata e una migliore eliminazione delle partizioni.

Gli indici non allineati usano partizionamento diverso o nessun partizionamento. Non possono usare il cambio di partizione e non sono supportati nelle tabelle con più di 1.000 partizioni.

Usare indici allineati quando è necessaria la commutazione delle partizioni per l'archiviazione, quando desideri ricompilare partizioni specifiche indipendentemente o quando i modelli di query filtrano sulla chiave di partizione.

Si immagini, ad esempio, una tabella Orders partizionata per OrderDate con un indice non cluster su CustomerID. L'uso del partizionamento allineato con lo stesso schema OrderDate consente di archiviare i vecchi mesi cambiando le partizioni, ricompilando gli indici correnti in modo indipendente ed eliminando le partizioni precedenti senza influire sull'intera tabella.

È possibile creare indici partizionati usando lo stesso schema di partizione della tabella di base:

-- Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_Customer
    ON Orders(CustomerID)
    ON PS_OrderDate(OrderDate);

-- Create partitioned columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SalesData_CS
    ON SalesData(Revenue, Region)
    ON PS_SalesDate(SaleDate);

Gestire le operazioni di partizione

Dopo aver creato tabelle partizionate, è necessario gestirle nel tempo. Le operazioni comuni includono l'esecuzione di query sui metadati della partizione, l'aggiunta di nuove partizioni man mano che aumentano i dati e la rimozione delle partizioni precedenti durante l'archiviazione. Queste operazioni usano la funzione $PARTITION e l'istruzione ALTER PARTITION FUNCTION.

Interrogare le informazioni sulla partizione

È possibile visualizzare le informazioni sulla partizione usando la $PARTITION funzione . Ecco un esempio:

-- View partition information
SELECT 
    $PARTITION.PF_OrderDate(OrderDate) AS PartitionNumber,
    MIN(OrderDate) AS MinDate,
    MAX(OrderDate) AS MaxDate,
    COUNT(*) AS RowCount
FROM Orders
GROUP BY $PARTITION.PF_OrderDate(OrderDate)
ORDER BY PartitionNumber;

Aggiungere un nuovo limite di partizione

È possibile suddividere le partizioni per aggiungere nuovi valori limite usando ALTER PARTITION FUNCTION. Ecco un esempio:

-- Split partition to add new boundary
ALTER PARTITION FUNCTION PF_OrderDate()
    SPLIT RANGE ('2024-11-01');

Questa istruzione aggiunge un nuovo valore limite (1 novembre 2024) alla funzione di partizione, suddividendo una partizione esistente in due partizioni. La partizione contenente le date da ottobre a dicembre diventa ora due partizioni: una per ottobre e un'altra per novembre e dicembre.

Archiviare e rimuovere la partizione

È possibile unire le partizioni per archiviare i dati obsoleti usando ALTER PARTITION FUNCTION con MERGE RANGE. Ecco un esempio:

-- Merge partitions to archive old data
ALTER PARTITION FUNCTION PF_OrderDate()
    MERGE RANGE ('2023-12-31');

Applicare le procedure consigliate per il partizionamento

Le procedure consigliate seguenti consentono di evitare errori di partizionamento comuni difficili da correggere dopo l'implementazione:

• Allineare gli indici alle partizioni di tabella: usare lo stesso schema di partizione per tabelle e indici per abilitare il cambio di partizione e la manutenzione
• Monitorare la distribuzione dei dati: controllare regolarmente le statistiche delle partizioni per identificare le partizioni sbilanciate e verificare l'eliminazione delle partizioni
• Automatizzare la gestione delle partizioni: pianificare i processi per aggiungere nuove partizioni prima di raggiungere i limiti e archiviare le partizioni precedenti
• Evitare l'over-partitioning: puntare a milioni di righe per partizione, non migliaia, poiché troppe partizioni creano overhead
• Includi chiave di partizione nella chiave primaria: obbligatorio per l'allineamento dell'indice cluster nello schema di partizione

Il partizionamento richiede un'attenta pianificazione. La chiave di partizione e l'allineamento dell'indice scelto determinano se si ottengono prestazioni o si creano complessità. Se implementato correttamente, il partizionamento trasforma la gestione di tabelle di grandi dimensioni tramite query più veloci, un'archiviazione efficiente e una manutenzione semplificata.