SQL
indici

introduzione

Osservazioni

Creare un indice

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

Questo creerà un indice per la colonna EmployeeId nella tabella Cars . Questo indice migliorerà la velocità delle query che chiedono al server di ordinare o selezionare in base ai valori in EmployeeId , ad esempio:

SELECT * FROM Cars WHERE EmployeeId = 1

L'indice può contenere più di 1 colonna, come di seguito;

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

In questo caso, l'indice sarebbe utile per le domande che richiedono di ordinare o selezionare da tutte le colonne incluse, se l'insieme di condizioni è ordinato allo stesso modo. Ciò significa che quando si recuperano i dati, è possibile trovare le righe da recuperare utilizzando l'indice, anziché esaminare l'intera tabella.

Ad esempio, il caso seguente utilizzerà il secondo indice;

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

Se l'ordine è diverso, tuttavia, l'indice non presenta gli stessi vantaggi, come nel seguito;

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

L'indice non è così utile perché il database deve recuperare l'intero indice, attraverso tutti i valori di EmployeeId e CarID, per trovare quali elementi hanno OwnerId = 17 .

(L'indice può ancora essere utilizzato, potrebbe essere il caso in cui Query Optimizer rileva che il recupero dell'indice e del filtro su OwnerId , quindi il recupero solo delle righe necessarie è più veloce del recupero dell'intera tabella, specialmente se la tabella è grande.)

Indici raggruppati, univoci e ordinati

Gli indici possono avere diverse caratteristiche che possono essere impostate alla creazione o alterando gli indici esistenti.

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email);  

L'istruzione SQL sopra riportata crea un nuovo indice cluster su Dipendenti. Gli indici raggruppati sono indici che dettano la struttura effettiva della tabella; la tabella stessa è ordinata per corrispondere alla struttura dell'indice. Ciò significa che può esserci al massimo un indice cluster su un tavolo. Se un indice cluster esiste già sulla tabella, l'istruzione sopra avrà esito negativo. (Le tabelle senza indici cluster sono anche chiamate heap).

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

Ciò creerà un indice univoco per la colonna Email nella tabella Clienti . Questo indice, oltre a velocizzare le query come un normale indice, imporrà anche l'unicità di ogni indirizzo email in quella colonna. Se una riga viene inserita o aggiornata con un valore di E - mail non univoco, l'inserimento o l'aggiornamento, per impostazione predefinita, falliranno.

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

Ciò crea un indice su Clienti che crea anche un vincolo di tabella che EmployeeID deve essere univoco. (Questo fallirà se la colonna non è al momento unica - in questo caso, se ci sono impiegati che condividono un ID.)

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

Questo crea un indice che è ordinato in ordine decrescente. Per impostazione predefinita, gli indici (almeno nel server MSSQL) sono in ordine crescente, ma possono essere modificati.

Inserimento con un indice unico

UPDATE Customers SET Email = "[email protected]" WHERE id = 1;

Ciò fallirà se un indice univoco è impostato sulla colonna Email dei Clienti . Tuttavia, per questo caso è possibile definire un comportamento alternativo:

UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUPLICATE KEY; 

SAP ASE: Drop index

Questo comando farà cadere l'indice nella tabella. Funziona sul server SAP ASE .

Sintassi:

DROP INDEX [table name].[index name]

Esempio:

DROP INDEX Cars.index_1

Indice ordinato

Se si utilizza un indice ordinato nel modo in cui lo si recupera, l'istruzione SELECT non eseguirà ulteriori ordinamenti durante il recupero.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

Quando si esegue la query

SELECT * FROM scoreboard ORDER BY score DESC;

Il sistema di database non eseguirà ulteriori ordinamenti, poiché può eseguire una ricerca dell'indice in tale ordine.

Eliminazione di un indice o disattivazione e ricostruzione

DROP INDEX ix_cars_employee_id ON Cars;  

Possiamo usare il comando DROP per cancellare il nostro indice. In questo esempio DROP l'indice chiamato ix_cars_employee_id sul tavolo Cars .

Questo elimina completamente l'indice e, se l'indice è in cluster, rimuoverà qualsiasi cluster. Non può essere ricostruito senza ricreare l'indice, che può essere lento e computazionalmente costoso. In alternativa, l'indice può essere disabilitato:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE; 

Ciò consente alla tabella di mantenere la struttura, insieme ai metadati relativi all'indice.

Criticamente, questo mantiene le statistiche dell'indice, in modo che sia possibile valutare facilmente la modifica. Se giustificato, l'indice può successivamente essere ricostruito, invece di essere ricreato completamente;

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

Indice univoco che consente NULLS

CREATE UNIQUE INDEX idx_license_id 
   ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

Questo schema consente una relazione 0..1 - le persone possono avere zero o una patente di guida e ogni licenza può appartenere a una sola persona

Ricostruisci indice

Nel corso del tempo gli indici B-Tree potrebbero diventare frammentati a causa dell'aggiornamento / eliminazione / inserimento dei dati. Nella terminologia di SQLServer possiamo avere interno (pagina indice che è mezzo vuoto) ed esterno (l'ordine logico delle pagine non corrisponde all'ordine fisico). L'indice di ricostruzione è molto simile a lasciarlo cadere e ricrearlo.

Possiamo ricostruire un indice con

ALTER INDEX index_name REBUILD; 

L'indice di ricostruzione predefinito è un'operazione offline che blocca la tabella e impedisce DML contro di essa, ma molti RDBMS consentono la ricostruzione in linea. Inoltre, alcuni produttori di DB offrono alternative alla ricostruzione degli indici come REORGANIZE (SQLServer) o COALESCE / SHRINK SPACE (Oracle).

Indice raggruppato

Quando si utilizza l'indice cluster, le righe della tabella vengono ordinate in base alla colonna a cui viene applicato l'indice cluster. Pertanto, può esistere un solo indice cluster sulla tabella poiché non è possibile ordinare la tabella in base a due colonne diverse.

In genere, è preferibile utilizzare l'indice cluster durante l'esecuzione di letture su tabelle di big data. Il rastrello dell'indice cluster è quando si scrive sulla tabella e i dati devono essere riorganizzati (ricorsi).

Un esempio di creazione di un indice cluster su una tabella Dipendenti sulla colonna Employee_Surname:

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Indice non raggruppato

Gli indici non cluster sono memorizzati separatamente dalla tabella. Ogni indice in questa struttura contiene un puntatore alla riga nella tabella che rappresenta.

Questi indicatori sono chiamati localizzatori di riga. La struttura del localizzatore di riga dipende dal fatto che le pagine di dati siano archiviate in un heap o in una tabella in cluster. Per un heap, un localizzatore di riga è un puntatore alla riga. Per una tabella in cluster, il localizzatore di riga è la chiave di indice cluster.

Un esempio di creazione di un indice non cluster sulla tabella Dipendenti e colonna Employee_Surname:

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Ci possono essere più indici non cluster sulla tabella. Le operazioni di lettura sono generalmente più lente con gli indici non clusterizzati rispetto agli indici cluster, in quanto è necessario innanzitutto passare all'indice e alla tabella. Non ci sono tuttavia restrizioni nelle operazioni di scrittura.

Indice parziale o filtrato

SQL Server e SQLite consentono di creare indici che contengono non solo un sottoinsieme di colonne, ma anche un sottoinsieme di righe.

Considera una quantità crescente di ordini con order_state_id uguale a finished (2) e una quantità stabile di ordini con order_state_id equal a started (1).

Se la tua azienda fa uso di query come questa:

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

L'indicizzazione parziale consente di limitare l'indice, inclusi solo gli ordini incompleti:

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

Questo indice sarà più piccolo di un indice non filtrato, che consente di risparmiare spazio e riduce il costo di aggiornamento dell'indice.