Come viene utilizzato un indice in una grande tabella?

L'indice in una grande tabella accelera significativamente le operazioni di selezione dei dati (SELECT) e anche le operazioni di modifica (UPDATE, DELETE) quando si utilizza la condizione WHERE con colonne indicizzate. Senza indice, il database deve scansionare l'intera tabella (full table scan), il che è molto lento con grandi volumi di dati.

Utilizzando un indice, il database accede prima alla struttura dell'indice (solitamente un albero B), che è molto più piccola della tabella stessa e contiene riferimenti ordinati alle righe di dati. Trovando i riferimenti necessari nell'indice, il database accede rapidamente direttamente alle righe richieste.

Importante:

• Gli indici occupano spazio su disco.
• La creazione e la manutenzione degli indici durante operazioni di inserimento (INSERT) e aggiornamento (UPDATE) richiede risorse aggiuntive.
• Troppi indici o indici scelti male possono rallentare le operazioni di scrittura.

Esempio di creazione di un indice in Ruby on Rails:

# Creazione di un indice sulla colonna 'email' nella tabella 'users'
add_index :users, :email

# Creazione di un indice unico sulla colonna 'username'
add_index :users, :username, unique: true

# Creazione di un indice composto sulle colonne 'user_id' e 'product_id'
add_index :orders, [:user_id, :product_id]

Esempio di utilizzo di un indice in SQL (eseguito dal database):

-- Query che utilizza l'indice sulla colonna email
SELECT * FROM users WHERE email = 'test@example.com';