Narcis Miclaus

6 min di lettura

SQL Server, dalle fondamenta Lezione 23 / 40

Indici filtrati e columnstore: gli eroi di nicchia

Quando il rowstore non basta. Indici filtrati per soft-delete e dati sparsi. Columnstore per query di warehouse. Come scegliere e quando.

Gli indici rowstore — il B-tree di cui abbiamo parlato nelle ultime due lezioni — sono il default e la scelta giusta per il 90% delle query. Oggi parliamo dell’altro 10%: gli indici filtrati per predicati che continuano a tornare, e gli indici columnstore per query di reporting su decine di milioni di righe.

Runehold usa entrambi. Indici filtrati su Status (flag di soft-delete) e su IsActive (stato di abbonamento) velocizzano le query di customer service più comuni. Il columnstore vive sullo star schema del warehouse di finance, dove “fatturato per paese per canale per mese” aggrega su 200 milioni di righe in meno di un secondo.

Indici filtrati

Un indice filtrato è un indice non clustered con una clausola WHERE. Indicizza solo le righe che corrispondono a quel predicato. Piccolo, economico, focalizzato.

-- Indicizza solo la piccola frazione di ordini attualmente in volo
CREATE NONCLUSTERED INDEX IX_Orders_Open
    ON Sales.Orders (CustomerId, OrderDate DESC)
    INCLUDE (Total, Status)
    WHERE Status IN (1, 2);   -- 1 = nuovo, 2 = in lavorazione

Confrontato con un equivalente non filtrato: questo indice ha forse 1.000 righe invece di 100 milioni. I seek sono più veloci. La manutenzione costa meno. La query della dashboard delle operazioni che filtra per Status IN (1, 2) fa seek su questo indice piccolo. Le query che non corrispondono al filtro lo ignorano.

Usi comuni

Colonne soft-delete / flag:

CREATE NONCLUSTERED INDEX IX_Customer_Active
    ON Sales.Customer (Email)
    WHERE IsDeleted = 0;
-- Indicizza solo i clienti vivi

Colonne sparse dove la maggior parte dei valori è NULL:

-- Solo l'1% dei clienti ha un flag di supporto prioritario
CREATE NONCLUSTERED INDEX IX_Customer_VIP
    ON Sales.Customer (VipTier)
    WHERE VipTier IS NOT NULL;

Univoco solo in un sottoinsieme:

-- L'email deve essere unica tra i clienti attivi; i clienti cancellati possono riutilizzarla
CREATE UNIQUE NONCLUSTERED INDEX UX_Customer_Email_Active
    ON Sales.Customer (Email)
    WHERE IsDeleted = 0;

Quest’ultimo è un trucco sottoutilizzato. SQL Server non ti lascia creare un constraint UNIQUE su una colonna che ha duplicati, ma un indice UNIQUE filtrato ti permette di far rispettare l’unicità solo tra le righe che contano.

Tranelli

Gli indici filtrati hanno regole capricciose:

  • Il WHERE della query deve includere il predicato del filtro perché l’optimizer usi l’indice.
  • Alcuni pattern di parametrizzazione impediscono i match. Un letterale WHERE Status IN (1, 2) matcha; un parametrizzato WHERE Status = @s no, anche quando @s è 1. Workaround: ricompila, o usa una stored procedure scritta per il filtro.
  • Gli indici filtrati non partecipano a certi tipi di uso delle statistiche, portando occasionalmente a stime strane.
  • Creare un indice filtrato su un set grande e in continuo cambiamento può fare un update-storm in manutenzione.

Per query in stile “status = active” ben definite, sono praticamente magici. Per qualsiasi altra cosa, testa a fondo.

Indici columnstore

Un indice columnstore memorizza i dati per colonna invece che per riga. Ogni colonna è compressa separatamente. Per query analitiche che leggono molte righe ma poche colonne — “fatturato totale per paese per mese su cinque anni” — il columnstore è 10-100× più veloce del rowstore.

Clustered columnstore

Sostituisce interamente l’indice clustered:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrderFact
    ON Fact.OrderLine;

L’intera tabella è ora memorizzata colonna per colonna, compressa pesantemente (tipicamente riduzione di 5-10× rispetto alla dimensione rowstore). Ci scrivi con INSERT, UPDATE, DELETE; le letture su poche colonne su molte righe sono velocissime; le letture di righe singole sono più lente di un B-tree.

Meglio per: tabelle dei fatti in un data warehouse, log di eventi interrogati in stile aggregato, dati storici.

Male per: OLTP — i lookup di singola riga ad alta frequenza sono molto più lenti del rowstore.

Non-clustered columnstore

Aggiunto accanto a una tabella rowstore esistente. Chi vuole analytics usa il columnstore; le scritture OLTP passano per il B-tree come al solito.

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analytics
    ON Sales.Orders (OrderDate, CustomerId, CountryCode, Status, Total);

Workload ibrido: le scritture OLTP colpiscono entrambe le strutture; le query analitiche colpiscono il columnstore. Scritture più lente del rowstore-only, ma i lettori ottengono il meglio di entrambi i mondi.

Esecuzione in batch mode

Il columnstore sblocca l’esecuzione in batch mode — il motore processa 900 righe alla volta in loop vettorizzati e stretti invece che riga per riga. Risparmi drammatici di CPU sulle query aggregate. La tua query passa da 60 secondi a 0,8.

SQL Server 2019+ supporta anche batch mode su rowstore per alcune query (“batch mode on rowstore”), che ottieni gratis su un’istanza con licenza Enterprise. Utile quando non puoi aggiungere un columnstore ma vuoi un po’ del beneficio.

Regole pratiche

  • Tabelle dei fatti con 5M+ righe: di solito il columnstore vale.
  • Tabelle più piccole di 100K righe: salta il columnstore; un rowstore con i giusti indici va bene.
  • Tabelle OLTP calde: non usare clustered columnstore. Considera il non-clustered columnstore se devi eseguire analytics su dati live.

Quando scegliere cosa

Flusso di decisione per il team engineering di Runehold:

  1. Inizia con un rowstore e un indice non clustered di copertura. 90% fatto.
  2. Il predicato è sempre lo stesso, stretto e ben noto? Aggiungi un indice filtrato.
  3. È una tabella dei fatti che cresce, e la maggior parte delle query aggrega? Aggiungi un columnstore.
  4. Hai un workload ibrido — scritture OLTP + letture analitiche sulla stessa tabella? Non-clustered columnstore sopra il rowstore.

La tabella Sales.Orders di Runehold: rowstore con indici non clustered di copertura per i lookup customer-facing, più un non-clustered columnstore per il livello di reporting di finance. Il meglio di entrambi i mondi.

Index hint (usa con parsimonia)

A volte l’optimizer sceglie un piano cattivo. Puoi forzare un indice con un hint:

SELECT * FROM Sales.Orders WITH (INDEX(IX_Orders_Open)) WHERE Status IN (1, 2);

Fallo raramente. Ogni index hint è una scommessa sul fatto che il piano che scegli oggi sarà ancora il migliore tra sei mesi. Upgrade, cambi nelle statistiche, crescita dei dati possono rendere l’hint sbagliato. Preferisci aggiornare le statistiche, sistemare l’indice, o usare il plan forcing del Query Store (lezione 28) ai hint hardcoded.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- Demo: indice filtrato
CREATE NONCLUSTERED INDEX IX_Orders_Open_Customer
    ON Sales.Orders (CustomerId, OrderDate DESC)
    INCLUDE (Total)
    WHERE Status IN (1, 2);

-- Va veloce SE il WHERE include la condizione del filtro
SELECT CustomerId, OrderDate, Total
FROM Sales.Orders
WHERE Status IN (1, 2)
  AND CustomerId = 42;

-- Controlla il piano: dovrebbe mostrare un Index Seek su IX_Orders_Open_Customer.

-- Se parametrizzi diversamente, l'indice filtrato potrebbe NON essere usato:
DECLARE @s TINYINT = 1;
SELECT CustomerId, OrderDate, Total
FROM Sales.Orders
WHERE Status = @s
  AND CustomerId = 42;
-- Questa potrebbe non matchare l'indice filtrato. Controlla il piano.

-- Demo: clustered columnstore su una piccola tabella dei fatti
IF OBJECT_ID('Reporting.OrderFact', 'U') IS NULL
BEGIN
    IF SCHEMA_ID('Reporting') IS NULL EXEC('CREATE SCHEMA Reporting AUTHORIZATION dbo');
    CREATE TABLE Reporting.OrderFact (
        OrderId     BIGINT,
        OrderDate   DATE,
        CustomerId  INT,
        CountryCode CHAR(2),
        Channel     VARCHAR(20),
        Total       DECIMAL(19, 4)
    );
END;

-- Riempila con 1M righe per la demo
INSERT INTO Reporting.OrderFact (OrderId, OrderDate, CustomerId, CountryCode, Channel, Total)
SELECT TOP (1000000)
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 730, GETDATE()),
    ABS(CHECKSUM(NEWID())) % 5000,
    CHAR(65 + ABS(CHECKSUM(NEWID())) % 5) + CHAR(65 + ABS(CHECKSUM(NEWID())) % 5),
    CASE ABS(CHECKSUM(NEWID())) % 3 WHEN 0 THEN 'Web' WHEN 1 THEN 'Retail' ELSE 'Marketplace' END,
    ABS(CHECKSUM(NEWID())) % 10000 / 10.0
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

-- Baseline rowstore
SET STATISTICS IO, TIME ON;
SELECT CountryCode, Channel, SUM(Total) AS revenue, COUNT(*) AS orders
FROM Reporting.OrderFact
GROUP BY CountryCode, Channel;

-- Aggiungi un columnstore
CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrderFact ON Reporting.OrderFact;

-- Stessa query, molto più veloce
SELECT CountryCode, Channel, SUM(Total) AS revenue, COUNT(*) AS orders
FROM Reporting.OrderFact
GROUP BY CountryCode, Channel;
SET STATISTICS IO, TIME OFF;

-- Pulizia
DROP TABLE Reporting.OrderFact;

Esegui entrambe le query prima e dopo aver aggiunto il columnstore. Nota il tempo trascorso e le letture. Su un milione di righe la differenza è già visibile; su 100 milioni è drammatica.

Prossima lezione: frammentazione, rebuild, reorg, e il mito del 30% — cosa ha detto davvero Paul Randal, perché il maintenance plan di default è sbagliato per gli SSD moderni, e come costruire una strategia di manutenzione sensata.

Cerca