Quali tipi di indici esistono in SQL?

• Albero-B (B-tree): Il tipo più comune. Adatto per la maggior parte delle operazioni di lettura, inclusi WHERE, LIKE, ORDER BY.

  CREATE INDEX idx_column ON table_name (column_name);
  

• Hash (Hash): Ottimizzato per corrispondenze esatte (=). Non supporta query di intervallo o ordinamento.

  -- Esempio di sintassi, può variare a seconda del SGBD
  CREATE INDEX idx_column ON table_name USING HASH (column_name);
  

• Testo completo (Full-Text): Usato per la ricerca in grandi campi di testo (VARCHAR, TEXT).

  -- Esempio di sintassi in MySQL
  CREATE FULLTEXT INDEX idx_text ON table_name (text_column);
  -- Esempio di sintassi in PostgreSQL
  CREATE INDEX idx_text ON table_name USING GIN (to_tsvector('russian', text_column));
  

• Spaziale (Spatial): Per indicizzare dati geografici (punti, linee, poligoni). Usato con tipi di dati come GEOMETRY o GEOGRAPHY.

  -- Esempio di sintassi in MySQL
  CREATE SPATIAL INDEX idx_geo ON table_name (spatial_column);
  -- Esempio di sintassi in PostgreSQL
  CREATE INDEX idx_geo ON table_name USING GIST (spatial_column);
  

• GIN (Indice Invertito Generalizzato) e GiST (Albero di Ricerca Generalizzato) in PostgreSQL: Tipi di indici flessibili, adatti a vari compiti, inclusa la ricerca full-text, indicizzazione di array e JSON.

• Cluster (Clustered): Definisce l'ordine fisico delle righe nella tabella. Una tabella può avere solo un indice cluster. Spesso basato sulla chiave primaria.

  -- Quando si dichiara la chiave primaria, spesso si crea un indice cluster
  CREATE TABLE table_name (
      id INT PRIMARY KEY CLUSTERED, -- In SQL Server
      ...
  );
  

• Non Clustered (Non-Clustered): Struttura dati separata che memorizza coppie "valore colonna (o colonne) + puntatore alla riga di dati". La tabella può avere molti indici non cluster.

Confronto:

Tipo di indice	Applicazione	Operazioni
B-Tree	Generale, la maggior parte dei tipi di dati	=, <, >, <=, >=, BETWEEN, LIKE (inizio)
Hash	Corrispondenza esatta (=)	=
Testo completo	Ricerca di testo	MATCH AGAINST (MySQL), to_tsquery (PostgreSQL)
Spaziale	Dati geografici	Funzioni spaziali
Cluster	Ordinamento fisico delle righe	Accelera le selezioni di intervalli
Non-Cluster	Accelerazione delle ricerche su colonne indicizzate	=, <, >, <= e altri