Efficace indicizzazione del database

Un'efficace indicizzazione in un database normalizzato avrà un impatto maggiore sulle prestazioni rispetto al semplice lancio di risorse aggiuntive nel sistema

Che cos'è un database normalizzato?

In parole povere, la normalizzazione è il processo di strutturazione dei database relazionali in modo da ridurre la ridondanza dei dati suddividendo e collegando i dati in blocchi più piccoli di dati.

Questo articolo si concentra principalmente sui database che funzionano in una struttura normalizzata ed esplorerà un'area con cui la maggior parte delle persone ha familiarità (o può immaginare) che sono le transazioni finanziarie, i clienti e i contatti.

Perché normalizzato?

Un certo livello o normalizzazione può apportare un'enorme quantità di miglioramento alla maggior parte dei set di dati e, sebbene i data lake e l'elaborazione dei dati non normalizzata stiano prendendo piede in alcuni aspetti dell'uso aziendale, la maggior parte delle aziende trarrebbe probabilmente vantaggio dall'avere i propri dati principali archiviati in una sorta di forma normale come può;

Velocizzare gli aggiornamenti (vedi sotto)
Semplifica l'interrogazione dei dati
Normalmente fornisce un'impronta digitale piu' piccola
Conforme alle norme del settore

Il nostro approccio

Il nostro approccio standard consiste nell'osservare i dati come se fossero archiviati in tre modi diversi e quando creiamo nuovi sistemi basati su SQL Server cerchiamo di mantenerli in schemi diversi.

Questo approccio ha funzionato con i nostri precedenti clienti e abbiamo anche esteso miglioramenti sostanziali della velocità ai loro fornitori di sistemi.

Mireremo ad aggiungere un sottoarticolo separato per ciascuna sezione a tempo debito e aggiungere una sezione per esplorare i concetti relativi al reporting neutrale del sistema tra più database.

Panoramica dell'indice

Sebbene questo articolo sia concentrato su SQL Server, gli stessi principi si applicano a molti sistemi diversi. Il numero e i tipi di indici possono migliorare o ridurre le prestazioni di lettura e scrittura in modo indipendente.

Raggruppato (clustered)

Sei limitato a uno per tabella e questo definisce come i dati vengono archiviati su disco.

Le tabelle che hanno un indice di questo tipo sono denominate Clustered Table e quelle che non lo hanno sono denominate Heap.

Non raggruppato (non clustered)

Puoi quasi pensare a questa come a una tabella separata che fa riferimento a ciascuna riga, tuttavia in SQL Server lo spazio di archiviazione effettivo cambia in base al tipo di tabella (cluster/heap)

Unicità

Entrambi questi indici possono essere univoci e, se utilizzati correttamente, possono apportare alcuni miglioramenti reali al modo in cui archiviate i dati.

Indici composti

Tutti gli indici possono utilizzare una o più colonne, tuttavia un indice cluster deve essere inferiore a 900 byte.

Aspetta, che mi dici della chiave primaria?

Quando le persone si riferiscono a una "Chiave primaria", spesso parlano di un "Indice cluster unico" e molte persone lo memorizzano automaticamente su una tabella all'interno di un campo di identità basato su un intero che aumenta di uno ogni volta che un nuovo record viene creato, questo può quindi essere referenziato da un'altra tabella utilizzando una chiave esterna (foreign key).

Una chiave esterna può infatti fare riferimento a qualsiasi indice univoco e persino fare riferimento a più colonne.

Dati di riferimento

Quest'area dovrebbe includere tutte le informazioni di livello superiore, cose come tipi di account e tipi di pagamento a cui fa riferimento un'altra tabella più in basso nella catena. Il vantaggio qui è che un singolo aggiornamento può essere utilizzato per modificare più righe in un database normalizzato, mentre non normalizzato dovrebbe aggiornare ogni riga.

Uso standard

In generale, idealmente utilizziamo una colonna di identità come indice cluster univoco. Creeremo quattro tabelle e uno schema di seguito.

Reference Tables

CREATE SCHEMA RefGOCREATE TABLE Ref.AddressType(AddressTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_AddressType PRIMARY KEY CLUSTERED,AddressTypeName NVARCHAR(100))CREATE TABLE Ref.ClientType(ClientTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_Client PRIMARY KEY CLUSTERED,ClientTypeName NVARCHAR(100))CREATE TABLE Ref.ContactType(ContactTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_ContactType PRIMARY KEY CLUSTERED,ContactTypeName NVARCHAR(100))CREATE TABLE Ref.TransactionType(TransactionTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_TransactionType PRIMARY KEY CLUSTERED,TransactionTypeName NVARCHAR(100))

Dati aziendali

Questo livello medio di area includerebbe Account, Clienti e Contatti o altre aree che potrebbero essere referenziate da qualcos'altro, e farebbe anche riferimento alle informazioni sul tipo.

Questo livello è normalmente il più difficile con cui lavorare in termini di decisione su dove mettere il tuo indice principale, poiché probabilmente sarà un mix di approcci diversi.

Di seguito è riportata la tabella per creare le tabelle Indirizzo, Cliente e Contatto. In questo codice è presente una tabella aggiuntiva (unita) che unisce i campi di tipo Cliente, Indirizzo e Indirizzo e qui abbiamo creato un indice cluster che funziona in modo diverso dalle altre tabelle. Questo perché nella maggior parte delle applicazioni si tratterebbe di una tabella ad alta intensità di lettura e possiamo accettare un aumento minimo per inserire le prestazioni. Se questa fosse un'applicazione creata da noi, probabilmente separeremmo i dettagli di contatto del cliente in modo simile.

Business Tables

CREATE SCHEMA BusGOCREATE TABLE Bus.[Address](AddressID INT CONSTRAINT PK_Bus_Address PRIMARY KEY CLUSTERED,AddressName NVARCHAR(100),AddressTypeID INT CONSTRAINT FK_Bus_Client_AddressTypeID FOREIGN KEY REFERENCES Ref.AddressType(AddressTypeID),AddressLine1 NVARCHAR(MAX)--Use more detail as required...)CREATE TABLE Bus.Client(ClientID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,ClientName NVARCHAR(100),ClientType INT CONSTRAINT FK_Bus_Client_ClientType FOREIGN KEY REFERENCES Ref.ClientType(ClientTypeID))--Use one table to handle all client addressesCREATE TABLE Bus.ClientAddress(ClientAddressID INT IDENTITY(1,1) CONSTRAINT PK_Bus_ClientAddressID PRIMARY KEY NONCLUSTERED,AddressTypeID INT,ClientID INT,AddressID INT,CONSTRAINT UQ_Bus_ClientAddress UNIQUE NONCLUSTERED (ClientID,AddressTypeID)--This ensures one type per client, can slow down inserts slightly)CREATE UNIQUE CLUSTERED INDEX CDX_Bus_ClientAddress ON Bus.ClientAddress(ClientID,AddressTypeID,AddressID)CREATE TABLE Bus.Contact(ContactID INT IDENTITY(1,1) CONSTRAINT PK_Bus_Contact PRIMARY KEY CLUSTERED,ContactName NVARCHAR(100),ContactTypeID INT CONSTRAINT FK_Bus_Contact_ContactTypeID FOREIGN KEY REFERENCES Ref.ContactType(ContactTypeID)--Could be broken out into a joining table if desired--Use more detail as required...)

Dati transazionali

Quest'area include cose come note, pagamenti e ordini e generalmente indica sia l'area aziendale che quella di riferimento.

Sebbene le chiavi univoche siano utili per l'identificazione, nell'uso generale probabilmente non è il modo in cui si desidera ordinare i dati su disco, poiché i tempi di lettura ne risentirebbero. C'è solo una tabella creata di seguito, ma dovrebbe darti un'idea.

Transactional Tables

CREATE SCHEMA TraGOCREATE TABLE Tra.[Transaction](TransactionID INT IDENTITY(1,1) CONSTRAINT PK_Tra_TransactionID PRIMARY KEY NONCLUSTERED,TransactionDate DATETIME CONSTRAINT DF_Tra_Transaction_TransactionDate DEFAULT GETUTCDATE(),--Use GETDATE() for local time.TransactionTypeID INT CONSTRAINT FK_Tra_Transaction_TransactionTypeID FOREIGN KEY REFERENCES Ref.TransactionType(TransactionTypeID),ClientID INT CONSTRAINT FK_Tra_Transaction_ClientID FOREIGN KEY REFERENCES Bus.Client(ClientID),ContactID INT CONSTRAINT FK_Tra_Transaction_ContactID FOREIGN KEY REFERENCES Bus.Contact(ContactID),TransactionAmount DECIMAL(18,2)--Use more detail as required...)CREATE CLUSTERED INDEX CDX_Tra_Transaction ON Tra.[Transaction](TransactionDate,TransactionTypeID,ClientID,ContactID)

Join e Reporting

Nel database immaginario di cui abbiamo parlato sopra, abbiamo cercato di rappresentare la vita reale il più fedelmente possibile. Questo non è affatto un approccio che deve essere adottato e l'utente è in ultima analisi responsabile di come utilizza le informazioni di cui sopra.

Poiché i dati sono passati al terzo livello, l'attenzione sull'indicizzazione è stata spostata sul modo in cui i dati sarebbero stati letti da un'applicazione o da un report, e ciò comporterebbe invariabilmente i join tra le tabelle e qualsiasi punto che potrebbe o sarebbe incluso nel clausole WHERE.

Ulteriori letture

Ricostruisci o riorganizza gli indici