Quali sono i tipi di indici nei database?

I principali tipi di indici nei database:

B-tree (B+tree):
- Il tipo più comune.
- Adatto per una vasta gamma di operazioni: ricerca per intervallo, corrispondenza esatta, ordinamento.
- Utilizzato di default in molti DBMS (MySQL, PostgreSQL, Oracle).
Indici hash:
- Basati su una tabella hash.
- Molto veloci per ricerche di corrispondenza esatta (=).
- Non adatti per ricerche per intervallo (BETWEEN, <, >), ordinamento (ORDER BY) o ricerca per pattern (LIKE).
Indici di testo completo:
- Progettati per la ricerca in campi di testo (BLOB, TEXT).
- Permettono di cercare parole o frasi, considerare sinonimi e effettuare analisi morfologiche.
- Esempi: MATCH...AGAINST in MySQL/MariaDB, tsvector/tsquery in PostgreSQL.
Indici spaziali (R-tree):
- Utilizzati per indicizzare dati geografici o spaziali.
- Permettono di trovare oggetti in aree o limiti specifici in modo efficiente.
Indici bitmap (mappa di bit):
- Usati in alcuni DBMS (ad esempio, Oracle, PostgreSQL).
- Adatti per campi con bassa cardinalità (pochi valori unici, ad esempio, genere, stato: attivo/inattivo).
- Possono essere molto efficienti per query complesse con operazioni AND e OR.
Indice clusterizzato:
- Influisce sull'ordine fisico di memorizzazione delle righe della tabella.
- Può esserci solo un indice clusterizzato per tabella.
- Spesso usato per la chiave primaria.
Indice non clusterizzato:
- Non determina l'ordine fisico delle righe.
- Contiene puntatori alla posizione fisica delle righe di dati.
- Una tabella può avere più indici non clusterizzati.
Indice coprente (covering):
- Un indice che contiene tutte le colonne necessarie per eseguire una query, comprese le colonne in SELECT, le condizioni in WHERE e ORDER BY.
- Permette al DBMS di non accedere ai dati della tabella, accelerando l'esecuzione della query.
Indice funzionale (per espressione):
- Indice costruito sul risultato di una funzione o espressione su una o più colonne.
- Permette di indicizzare valori calcolati.

Nella scelta del tipo di indice, si considera il tipo di dati, la frequenza e la natura delle query (ricerca per valore esatto, intervallo, ordinamento, ricerca full-text, ecc.) e la cardinalità della colonna.