0Pricing
SQL Interview Prep · Lezione

Filtrare in base a valori calcolati

Capire perché le funzioni sulle colonne impediscono l'uso degli indici e come questo viene verificato ai colloqui

Filtrare in base a valori calcolati è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.

Perché questa domanda distingue i livelli

La domanda sembra innocua: questa query è corretta ma lenta: perché? Spesso la risposta è che la clausola WHERE applica una funzione a una colonna indicizzata. In questo modo il predicato diventa non sargable: l'ottimizzatore non può più usare l'indice e deve esaminare ogni riga.

Questa lezione spiega la sargability, mostra le riscritture che gli intervistatori si aspettano e illustra dove inserire realmente un filtro calcolato.

Sargable: una definizione

Sargable (Search ARGument ABLE) indica che un predicato può usare un indice per raggiungere direttamente le righe corrispondenti. La regola generale è questa: la colonna indicizzata deve comparire da sola su un lato del confronto, non essere nascosta all'interno di una funzione o di un'espressione.

  • Sargable: col = 5, col > 100, col LIKE 'abc%'
  • Non sargable: FUNC(col) = 5, col + 1 > 100

L'anti-pattern della funzione sulla colonna

Qui l'obiettivo è trovare gli ordini effettuati nel 2024. Racchiudere la colonna in YEAR() costringe il motore a calcolare l'anno per ogni singola riga prima di poterlo confrontare, quindi l'indice su order_date non serve a nulla.

La query restituisce il risultato corretto, ma esegue la scansione dell'intera tabella. Su una tabella di grandi dimensioni, la differenza può essere tra millisecondi e minuti.

-- non-sargable: function on the indexed column
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024;

Riscrivere come intervallo

La soluzione consiste nel lasciare order_date da solo ed esprimere la condizione come un intervallo semiaperto. In questo modo l'indice su order_date può cercare direttamente l'inizio del 2024 e fermarsi all'inizio del 2025.

Il risultato è lo stesso, ma con una scansione dell'intervallo dell'indice invece di una scansione completa. Questa riscrittura dell'intervallo è la correzione della sargability più richiesta nei colloqui tecnici.

-- sargable: column stays bare
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01';

Aritmetica sulla colonna

Lo stesso problema si nasconde nelle operazioni aritmetiche. Sia WHERE salary + bonus > 100000 sia WHERE price * 0.9 < 50 eseguono un calcolo sulla colonna e impediscono l'uso dell'indice.

Spostare i calcoli dal lato della costante ogni volta che è possibile: riscrivere price * 0.9 < 50 come price < 50 / 0.9. Il valore letterale viene calcolato una volta sola e price resta da solo e utilizzabile nell'indice.

-- before: math on the column (non-sargable)
WHERE price * 0.9 < 50
-- after: math on the constant (sargable)
WHERE price < 50 / 0.9

La variante della ricerca senza distinzione tra maiuscole e minuscole

WHERE LOWER(email) = 'a@b.com' non è sargable rispetto a un indice semplice su email, perché l'indirizzo email di ogni riga viene prima convertito in minuscolo.

Esistono due soluzioni per la produzione: memorizzare una copia normalizzata in minuscolo e indicizzarla, oppure creare un indice funzionale su LOWER(email), in modo che sia l'espressione stessa a essere indicizzata. Menzionare l'opzione dell'indice funzionale dimostra esperienza concreta.

-- functional index makes the expression sargable
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

Quando serve davvero un calcolo

A volte il filtro dipende davvero da un valore calcolato per il quale non esiste una riscrittura come intervallo, ad esempio quando si filtra in base a un rapporto. Non è comunque possibile fare riferimento a un alias di SELECT in WHERE, perché WHERE viene valutata prima dell'elenco SELECT.

È quindi necessario ripetere l'espressione in WHERE oppure racchiudere la query in una sottoquery / CTE e filtrare la colonna calcolata nella query esterna.

SELECT *
FROM (
  SELECT *, revenue / NULLIF(visits, 0) AS rev_per_visit
  FROM stats
) t
WHERE t.rev_per_visit > 2.5;

Gli aggregati vanno in HAVING, non in WHERE

Un calcolo aggregato non può trovarsi in WHERE, perché WHERE filtra le singole righe prima che avvenga il raggruppamento. WHERE SUM(amount) > 1000 genera un errore.

I filtri sugli aggregati appartengono a HAVING, che viene eseguita dopo GROUP BY. Sapere quale clausola vede il calcolo è di per sé una domanda frequente sull'ordine di esecuzione.

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Come la mettono alla prova nei colloqui

Le viene mostrata una query lenta con una funzione applicata a una colonna e le viene chiesto di renderla più veloce senza modificarne il risultato. La strategia è:

  • individuare la funzione sulla colonna come non sargable
  • riscrivere la query lasciando la colonna da sola (intervallo o calcolo dal lato della costante)
  • se non esiste una riscrittura, proporre un indice funzionale o una colonna calcolata memorizzata

Menzionare EXPLAIN per verificare che il piano sia passato da una scansione sequenziale a una scansione dell'indice completa la risposta.

Consapevolezza dei compromessi

La valutazione deve essere equilibrata: gli indici e gli indici funzionali velocizzano le letture, ma rallentano le scritture e consumano spazio di archiviazione. Su una tabella molto piccola, una scansione completa va bene e aggiungere un indice è uno spreco di lavoro.

La risposta di una persona esperta è condizionata dal contesto: se questa colonna è grande e viene filtrata spesso in questo modo, renda il predicato sargable oppure aggiunga un indice funzionale; altrimenti lo lasci così. Nei colloqui, il contesto è più importante dei dogmi.

Gli indici funzionali rendono sargable un calcolo

A volte è davvero necessario filtrare su un valore trasformato, ad esempio per un confronto senza distinzione tra maiuscole e minuscole. Invece di rinunciare agli indici, crei un indice su un'espressione (funzionale) basato sull'espressione esatta usata per il filtro.

  • L'ottimizzatore può quindi usare l'indice anche se una funzione racchiude la colonna.
  • L'espressione dell'indice deve corrispondere esattamente all'espressione del predicato.
-- index the expression you filter on
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- now this predicate stays sargable
SELECT * FROM users WHERE lower(email) = 'amy@example.com';

Verifica rapida

Individui quale predicato può essere gestito dall'ottimizzatore tramite un indice.

Riepilogo

Punti chiave:

  • Un predicato è sargable quando la colonna indicizzata compare da sola, non all'interno di una funzione o di un'operazione aritmetica
  • Riscrivere YEAR(col) = 2024 come un intervallo semiaperto; spostare i calcoli dal lato della costante
  • Per le espressioni inevitabili, usare un indice funzionale o una colonna calcolata memorizzata
  • Non è possibile usare un alias di SELECT in WHERE; gli aggregati vanno in HAVING

La domanda classica riguarda una query lenta; la correzione classica consiste nel lasciare la colonna da sola.

Domande Frequenti

La lezione «Filtrare in base a valori calcolati» è gratuita?

Sì — il testo completo di «Filtrare in base a valori calcolati» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Filtrare in base a valori calcolati»?

Capire perché le funzioni sulle colonne impediscono l'uso degli indici e come questo viene verificato ai colloqui Eserciti SQL Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Interview Prep?

Non è richiesta alcuna esperienza precedente. SQL Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «Filtrare in base a valori calcolati»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Interview Prep?

Sì. Ogni lezione SQL Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Precedenza di AND/OR e uso delle parentesi
  2. BETWEEN, IN e limiti inclusivi
  3. LIKE, caratteri jolly ed escaping
  4. Filtrare in base a valori calcolati
← Torna a SQL Interview Prep