PostgreSQL 索引

Un indice è una query speciale di tabella che accelera la ricerca dei dati del motore di ricerca. In altre parole, un indice è un puntatore ai dati della tabella. Un indice in un database è molto simile all'indice di un libro.

Prendendo ad esempio la pagina del catalogo di un dizionario cinese (indice), possiamo trovare rapidamente la parola desiderata attraverso una tabella (indice) ordinata per pronuncia, tratti, componenti radicali, ecc.

Gli indici accelerano le query SELECT e le clausole WHERE, ma rallentano l'inserimento di dati utilizzando statement UPDATE e INSERT. Gli indici possono essere creati o eliminati, ma non influenzano i dati.

La statement CREATE INDEX crea un indice, permettendo di nomare l'indice, specificare la tabella e le colonne da indicizzare, e indicare se l'indice è in ordine crescente o decrescente.

Un indice può essere anche unico, simile a UNIQUE constraint, prevenendo duplicati in una colonna o combinazione di colonne.

Comando CREATE INDEX

La sintassi di CREATE INDEX è la seguente:

CREATE INDEX index_name ON table_name;

Tipi di indice

Indice singolo

L'indice singolo è un indice creato su una singola colonna della tabella, la sintassi di base è la seguente:

CREATE INDEX index_name
ON table_name (column_name);

Indice composto

L'indice composto è un indice creato su più colonne della tabella, la sintassi di base è la seguente:

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Sia l'indice singolo che l'indice composto devono essere utilizzati molto frequentemente nelle condizioni di filtro della clausola WHERE.

Se viene utilizzata una sola colonna, scegliere l'indice singolo; se vengono utilizzate più colonne, utilizzare l'indice composto.

Indice univoco

L'uso dell'indice univoco non è solo per la prestazione, ma anche per l'integrità dei dati. L'indice univoco non permette l'inserimento di valori duplicati nella tabella. La sintassi di base è la seguente:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Indice locale

L'indice locale è un indice costruito su un sottoinsieme della tabella; il sottoinsieme è definito da un'espressione condizionale. L'indice contiene solo le righe che soddisfano la condizione. La sintassi di base è la seguente:

CREATE INDEX index_name
on table_name (conditional_expression);

Indice implicito

Gli indici impliciti sono indici creati automaticamente dal server di database al momento della creazione dell'oggetto. Gli indici vengono creati automaticamente per le restrizioni di chiave primaria e unica.

Esempio online

Di seguito un esempio di creazione di un indice sulla colonna SALARY della tabella COMPANY:

w3codeboxdb=#　CREATE INDEX salary_index ON COMPANY (salary);

Ora, usando 　\d company Il comando elenca tutti gli indici della tabella COMPANY:

w3codeboxdb=#　\d　company

Risultati ottenuti come segue, company_pkey è un indice implicito, creato al momento della creazione della tabella:

w3codeboxdb=#　\d　company
　　　　　　　　　　　　　　　　　　Table "public.company"
　Column　　|　　Type　　|　　Collation　|　　Nullable　|　　Default　
---------+---------------+-----------+----------+---------
　id　　|　integer　　|　　|　not null　|　
　name　　|　text　　|　　|　not null　|　
　age | integer | | not null |　
　address | character(50) | | |　
　salary | real | | |　
Indici:
　　　　"company_pkey" PRIMARY KEY, btree (id)
　　　　"salary_index" btree (salary)

Puoi utilizzare il comando \di per elencare tutti gli indici nel database:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　Elenco delle relazioni
　Schema | Name | Type | Owner | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
　public | salary_index | index | postgres | company
(3 row)

DROP INDEX (elimina l'indice)

Un indice può essere eliminato utilizzando il comando DROP di PostgreSQL.

DROP INDEX index_name;

Puoi utilizzare la seguente istruzione per eliminare l'indice creato in precedenza:

# DROP INDEX salary_index;

Dopo la cancellazione, è possibile vedere che salary_index è stato rimosso dalla lista degli indici:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　Elenco delle relazioni
　Schema | Name | Type | Owner | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
(2 row)

In quali casi è necessario evitare l'uso dell'indice?

Sebbene l'indice sia finalizzato a migliorare le prestazioni del database, ci sono alcune situazioni in cui è necessario evitare di utilizzare l'indice.

Quando si utilizza l'indice, è necessario considerare i seguenti principi:

• L'indice non dovrebbe essere utilizzato sulle tabelle di piccole dimensioni.

• L'indice non dovrebbe essere utilizzato sulle tabelle con aggiornamenti o inserimenti di massa frequenti.

• L'indice non dovrebbe essere utilizzato sulle colonne che contengono un gran numero di valori NULL.

• L'indice non dovrebbe essere utilizzato sulle colonne soggette a operazioni frequenti.