Narcis Miclaus

6 min de citit

SQL Server, de la zero Lecția 23 / 40

Indici filtrați și columnstore: eroii de nișă

Când rowstore nu e suficient. Indici filtrați pentru soft-delete și date sparse. Columnstore pentru query-uri de warehouse. Cum alegi și când.

Indicii rowstore — arborele B despre care am discutat în ultimele două lecții — sunt default-ul și alegerea potrivită pentru 90% dintre query-uri. Astăzi e despre celelalte 10%: indici filtrați pentru predicate care revin mereu, și indici columnstore pentru query-uri de raportare peste zeci de milioane de rânduri.

Runehold le folosește pe ambele. Indici filtrați pe Status (flag de soft-delete) și pe IsActive (starea abonamentului) accelerează cele mai comune query-uri de customer service. Columnstore trăiește pe schema stea a warehouse-ului de finance, unde „venit pe țară pe canal pe lună” agregă peste 200 de milioane de rânduri în mai puțin de o secundă.

Indici filtrați

Un indice filtrat e un indice non-clustered cu o clauză WHERE. Indexează doar rândurile care se potrivesc cu acel predicat. Mic, ieftin, focusat.

-- Indexează doar fracțiunea mică de comenzi în derulare
CREATE NONCLUSTERED INDEX IX_Orders_Open
    ON Sales.Orders (CustomerId, OrderDate DESC)
    INCLUDE (Total, Status)
    WHERE Status IN (1, 2);   -- 1 = nou, 2 = în procesare

În comparație cu un echivalent nefiltrat: acest indice are poate 1.000 de rânduri în loc de 100 de milioane. Seek-urile sunt mai rapide. Mentenanța e mai ieftină. Query-ul de pe dashboard-ul de operațiuni care filtrează după Status IN (1, 2) face seek pe acest indice mic. Query-urile care nu se potrivesc cu filtrul îl ignoră.

Utilizări frecvente

Coloane soft-delete / flag:

CREATE NONCLUSTERED INDEX IX_Customer_Active
    ON Sales.Customer (Email)
    WHERE IsDeleted = 0;
-- Indexează doar clienții activi

Coloane sparse unde majoritatea valorilor sunt NULL:

-- Doar 1% dintre clienți au flag de priority support
CREATE NONCLUSTERED INDEX IX_Customer_VIP
    ON Sales.Customer (VipTier)
    WHERE VipTier IS NOT NULL;

Unicitate într-un subset:

-- Email-ul trebuie să fie unic printre clienții activi; cei șterși îl pot reutiliza
CREATE UNIQUE NONCLUSTERED INDEX UX_Customer_Email_Active
    ON Sales.Customer (Email)
    WHERE IsDeleted = 0;

Ultimul e un truc subutilizat. SQL Server nu te lasă să creezi o constrângere UNIQUE pe o coloană care are duplicate, dar un indice filtrat UNIQUE îți permite să impui unicitatea doar printre rândurile care contează.

Capcane

Indicii filtrați au reguli mofturoase:

  • Clauza WHERE a query-ului trebuie să includă predicatul filtrului pentru ca optimizatorul să folosească indicele.
  • Unele pattern-uri de parametrizare împiedică potrivirea. Un literal WHERE Status IN (1, 2) se potrivește; un WHERE Status = @s parametrizat nu, chiar și când @s e 1. Soluție: recompile, sau folosește un stored procedure scris pentru filtru.
  • Indicii filtrați nu participă la unele tipuri de utilizare a statisticilor, ducând ocazional la estimări ciudate.
  • Crearea unui indice filtrat pe un set mare, mereu schimbător, poate „update-storm” pe mentenanță.

Pentru query-uri bine definite de tipul „status = active”, sunt aproape magice. Pentru orice altceva, testează cu atenție.

Indici columnstore

Un indice columnstore stochează datele pe coloane în loc de rânduri. Fiecare coloană e comprimată separat. Pentru query-uri analitice care citesc multe rânduri, dar puține coloane — „venit total pe țară pe lună peste cinci ani” — columnstore e de 10-100× mai rapid decât rowstore.

Columnstore clustered

Înlocuiește complet indicele clustered:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrderFact
    ON Fact.OrderLine;

Întreaga tabelă e acum stocată coloană cu coloană, comprimată puternic (tipic, reducere de 5-10× față de dimensiunea rowstore). Scrii în ea cu INSERT, UPDATE, DELETE; citirile pe câteva coloane peste multe rânduri sunt foarte rapide; citirile de rânduri individuale sunt mai lente decât pe un arbore B.

Cel mai bine pentru: tabele de fapte într-un data warehouse, log-uri de evenimente interogate agregat, date istorice.

Prost pentru: OLTP — lookup-urile de un singur rând la frecvență mare sunt mult mai lente decât rowstore.

Columnstore non-clustered

Adăugat alături de o tabelă rowstore existentă. Cititorii care vor analytics folosesc columnstore-ul; scrierile OLTP merg prin arborele B ca de obicei.

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

Workload hibrid: scrierile OLTP lovesc ambele structuri; query-urile analitice lovesc columnstore-ul. Scrieri mai lente decât doar rowstore, dar cititorii primesc ambele lumi.

Execuție în mod batch

Columnstore deblochează execuția batch-mode — engine-ul procesează 900 de rânduri odată în bucle strânse, vectorizate, în loc de rând cu rând. Economii dramatice de CPU pe query-uri agregate. Query-ul tău trece de la 60 de secunde la 0,8.

SQL Server 2019+ suportă și batch mode pe rowstore pentru unele query-uri („batch mode on rowstore”), pe care îl primești pe gratis pe o instanță cu licență Enterprise. Util când nu poți adăuga un columnstore, dar vrei o parte din beneficiu.

Reguli rapide

  • Tabele de fapte cu 5M+ rânduri: columnstore merită aproape întotdeauna.
  • Tabele mai mici de 100K rânduri: sări peste columnstore; un rowstore cu indicii potriviți e ok.
  • Tabele OLTP fierbinți: nu folosi columnstore clustered. Ia în considerare columnstore non-clustered dacă ai nevoie să rulezi analytics pe date live.

Când să alegi ce

Flux de decizie pentru echipa de inginerie de la Runehold:

  1. Pornește cu rowstore și un indice non-clustered acoperitor. 90% gata.
  2. Predicatul e mereu același, îngust și bine cunoscut? Adaugă un indice filtrat.
  3. E o tabelă de fapte care crește, iar majoritatea query-urilor sunt agregate? Adaugă un columnstore.
  4. Ai un workload hibrid — scrieri OLTP + citiri analytics pe aceeași tabelă? Columnstore non-clustered peste rowstore.

Tabela Sales.Orders a Runehold: rowstore cu indici non-clustered acoperitori pentru lookup-urile pe partea de client, plus un columnstore non-clustered pentru stratul de raportare al finance-ului. Tot ce e mai bun din ambele lumi.

Hint-uri de indice (cu măsură)

Uneori optimizatorul alege un plan prost. Poți forța un indice cu un hint:

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

Fă asta rar. Fiecare hint de indice e un pariu că planul pe care îl alegi azi va rămâne cel mai bun și peste șase luni. Upgrade-uri, modificări de statistici, creșterea datelor pot face hint-ul greșit. Preferă actualizarea statisticilor, repararea indicelui sau plan forcing din Query Store (lecția 28) în locul hint-urilor hardcodate.

Rulează asta pe mașina ta

USE Runehold;
GO

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

-- Rulează rapid DACĂ WHERE-ul include condiția de filtru
SELECT CustomerId, OrderDate, Total
FROM Sales.Orders
WHERE Status IN (1, 2)
  AND CustomerId = 42;

-- Verifică planul: ar trebui să arate un Index Seek pe IX_Orders_Open_Customer.

-- Dacă parametrizezi diferit, indicele filtrat poate să NU fie folosit:
DECLARE @s TINYINT = 1;
SELECT CustomerId, OrderDate, Total
FROM Sales.Orders
WHERE Status = @s
  AND CustomerId = 42;
-- E posibil să nu se potrivească cu indicele filtrat. Verifică planul.

-- Demo: columnstore clustered pe o tabelă mică de fapte
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;

-- Umple 1M rânduri pentru 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;

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

-- Același query, mult mai rapid
SELECT CountryCode, Channel, SUM(Total) AS revenue, COUNT(*) AS orders
FROM Reporting.OrderFact
GROUP BY CountryCode, Channel;
SET STATISTICS IO, TIME OFF;

-- Curățenie
DROP TABLE Reporting.OrderFact;

Rulează ambele query-uri înainte și după ce adaugi columnstore-ul. Observă timpul scurs și citirile. Pe un milion de rânduri diferența e deja vizibilă; pe 100 de milioane e dramatică.

Următoarea lecție: fragmentare, rebuild-uri, reorgs și mitul celor 30% — ce a spus de fapt Paul Randal, de ce planul implicit de mentenanță e greșit pentru SSD-urile moderne și cum construiești o strategie de mentenanță cu cap.

Caută