Perché dovrebbe servirmi un indice in SQL Server?

La domanda è lecita, la risposta a mio avviso è molto articolata e complessa. Abbiamo visto in altri articoli come vengano immagazzinati i dati del nostro database e tutte le strutture dati implicate per favorire lo scorrimento di queste informazioni.

Possiamo generalizzare dicendo che la creazione di un indice ben studiato per il caso d’uso che voglia soddisfare ci permette di migliorare una specifica ricerca di un dato e le performance in generale del nostro sistema.

Ebbene si, se sono in grado di dare a SQL Server una strada più efficiente per soddisfare la mia query allora questo terrà da parte molto più risorse per svolgere altri compiti.

Vorrei provare a proporre un esempio molto semplice che permette di chiarire in maniera lampante quanto detto fin ora.

Questa la struttura tabella da creare:

CREATE TABLE Products (
	Id INT IDENTITY(1, 1),
	Name NVARCHAR(256),
	CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE Customers (
	Id INT IDENTITY(1, 1),
	Name NVARCHAR(256),
	CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE Orders (
	Id INT IDENTITY(1, 1),
	CustomerId INT  NOT NULL,
	ProductId INT  NOT NULL,
	Quantity INT,
	CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (Id)
);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId) REFERENCES Customers (Id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Products FOREIGN KEY (ProductId) REFERENCES Products (Id);
GO

Inseriamo ora un po’ di dati di test

-- inseriamo 1000 prodotti
INSERT INTO Products (Name)
SELECT TOP 1000
	'Product'+convert(nvarchar,abs(checksum(newid())))
FROM sys.objects o1, sys.objects t2

-- inseriamo un numero random di clienti (sfruttiamo il prodotto cartesiano della sys.objects)
INSERT INTO Customers (Name)
SELECT
	'Customer'+convert(nvarchar,abs(checksum(newid())))
FROM sys.objects o1, sys.objects o2

-- inseriamo un numero randomico di ordini per i vari prodotti
INSERT INTO Orders (CustomerId, ProductId, Quantity)
SELECT
	c.Id, p.Id, abs(checksum(newid()))
FROM
	Customers c, Products p
GO

A questo punto proviamo a cercare un elemento specifico all’interno della tabella “Orders”, quindi:

SET STATISTICS IO ON;
SET STATISTICS XML ON;
SELECT TOP 100 * FROM Orders WHERE CustomerId = 5004 and ProductId = 875;

La prima istruzione ci serve per avere il dettaglio delle letture IO effettuate dalla nostra query.

NOTA: In questo articolo ho dettagliato meglio come recuperare/leggere il piano di esecuzione di una query.

La seconda ci restituirà anche il piano di esecuzione, da cui posso evincere non solo il modo con cui SQL Server ha deciso di recuperare i miei dati, ma anche i tempi di esecuzione:

Piano di esecuzione della query senza indici.

Inoltre, nei messaggi troveremo il dettaglio delle letture IO:

Tabella 'Orders'. Conteggio analisi 1, letture logiche 36247, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Vediamo quindi, una scansione completa della tabella (Clustered Index Scan), sono state lette infatti 11K righe (ossia la totalità degli elementi della tabella); il tutto in c.ca 562 millisecondi ed abbiamo letto 36247 pagine (il che vuol dire 36247 * 8KB, circa 280MB di dati). Tutto questo per recuperare una singola riga di uno specifico ordine. Capiamo facilmente che non è assolutamente efficiente la metodologia scelta dall’engine ma a sua discolpa possiamo solo dire che non ha alcuna alternativa; non può scegliere un percorso migliore per trovare quel singolo dato proprio perché non gliene abbiamo mai fornito uno.

A questo punto proviamo a creare un indice specifico per la query di cui sopra:

CREATE INDEX IX_Orders_CustomerId_ProductId ON Orders (CustomerId, ProductId);
GO

Eseguiamo nuovamente la stessa identica query di ricerca proposta sopra.

Noteremo ora nel piano di esecuzione qualcosa del genere:

Piano di esecuzione dopo la creazione dell'indice. — Piano di esecuzione dopo la creazione dell’indice.

Nei messaggi:

Tabella 'Orders'. Conteggio analisi 1, letture logiche 6, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Abbiamo letto una sola riga dell’indice appena creato e successivamente abbiamo fatto una “lookup” sulla tabella d’origine per recuperare la quantità che non era conosciuta dalla semplice “Seek” fatta su IX_Orders_CustomerId_ProductId. Tempo di esecuzione praticamente zero e letture pochi KB (6 pagine).

Concludendo, per rispondere alla domanda originale: “Perché dovrebbe servirmi un indice in SQL Server?”; iI tempi di esecuzione delle queries calano, il numero di letture da disco o memoria cala; tutto questo crea un effetto benefico in primis sulla query che vogliamo eseguire ma sicuramente anche sull’intero sistema che evaderà più velocemente e con meno sforzo quel tipo di richiesta per poter poi passare ad altro senza perdere tempo e risorse.

L’articolo cerca quindi di dimostrare quale importanza fondamentale possono avere gli indici in SQL Server, ma non solo, in un RDBMS in generale.

Lascia un commento Annulla risposta