SQLite - Indici

Gli indici sono tabelle di ricerca speciali che il motore di ricerca del database può utilizzare per accelerare il recupero dei dati. In poche parole, un fileindexè un puntatore ai dati in una tabella. Un indice in un database è molto simile a un indice sul retro di un libro.

Ad esempio, se si desidera fare riferimento a tutte le pagine di un libro che trattano un determinato argomento, fare prima riferimento all'indice, che elenca tutti gli argomenti in ordine alfabetico e quindi a uno o più numeri di pagina specifici.

Un indice aiuta ad accelerare le query SELECT e le clausole WHERE, ma rallenta l'input dei dati, con le istruzioni UPDATE e INSERT. Gli indici possono essere creati o eliminati senza alcun effetto sui dati.

La creazione di un indice implica l'istruzione CREATE INDEX, che consente di denominare l'indice, di specificare la tabella e la colonna o le colonne da indicizzare e di indicare se l'indice è in ordine crescente o decrescente.

Gli indici possono anche essere univoci, in modo simile al vincolo UNIQUE, in quanto l'indice impedisce voci duplicate nella colonna o combinazione di colonne su cui è presente un indice.

Il comando CREATE INDEX

Di seguito è riportata la sintassi di base di CREATE INDEX.

CREATE INDEX index_name ON table_name;

Indici a colonna singola

Un indice a colonna singola è quello creato in base a una sola colonna della tabella. La sintassi di base è la seguente:

CREATE INDEX index_name
ON table_name (column_name);

Indici unici

Gli indici univoci vengono utilizzati non solo per le prestazioni, ma anche per l'integrità dei dati. Un indice univoco non consente l'inserimento di valori duplicati nella tabella. La sintassi di base è la seguente:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Indici compositi

Un indice composto è un indice su due o più colonne di una tabella. La sintassi di base è la seguente:

CREATE INDEX index_name
on table_name (column1, column2);

Indipendentemente dal fatto che si crei un indice a colonna singola o un indice composto, prendere in considerazione le colonne che è possibile utilizzare molto frequentemente nella clausola WHERE di una query come condizioni di filtro.

Se deve essere utilizzata una sola colonna, la scelta dovrebbe essere un indice a colonna singola. Dovrebbero esserci due o più colonne utilizzate di frequente nella clausola WHERE come filtri, l'indice composto sarebbe la scelta migliore.

Indici impliciti

Gli indici impliciti sono indici che vengono creati automaticamente dal server di database quando viene creato un oggetto. Gli indici vengono creati automaticamente per vincoli di chiave primaria e vincoli univoci.

Example

Di seguito è riportato un esempio in cui creeremo un indice nella tabella AZIENDA per la colonna salario -

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Ora, elenchiamo tutti gli indici disponibili nella tabella AZIENDA utilizzando .indices comando come segue -

sqlite> .indices COMPANY

Questo produrrà il seguente risultato, dove sqlite_autoindex_COMPANY_1 è un indice implicito creato quando è stata creata la tabella stessa.

salary_index
sqlite_autoindex_COMPANY_1

Puoi elencare tutti gli indici a livello di database come segue:

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

Il comando DROP INDEX

Un indice può essere eliminato utilizzando SQLite DROPcomando. È necessario prestare attenzione quando si elimina un indice perché le prestazioni potrebbero essere rallentate o migliorate.

Di seguito è la sintassi di base è la seguente:

DROP INDEX index_name;

È possibile utilizzare la seguente istruzione per eliminare l'indice creato in precedenza.

sqlite> DROP INDEX salary_index;

Quando dovrebbero essere evitati gli indici?

Sebbene gli indici abbiano lo scopo di migliorare le prestazioni di un database, a volte dovrebbero essere evitati. Le seguenti linee guida indicano quando è necessario riconsiderare l'uso di un indice.

Gli indici non devono essere utilizzati in -

• Tavolini.
• Tabelle che hanno frequenti, grandi operazioni di aggiornamento batch o inserimento.
• Colonne che contengono un numero elevato di valori NULL.
• Colonne che vengono manipolate frequentemente.