Linguaggi di interrogazione e manipolazione dei dati (SQL)

Approfondimento

Che SQL sia dichiarativo ha una conseguenza tecnica profonda: fra la query scritta e l'esecuzione si interpone l'ottimizzatore del DBMS, che traduce l'interrogazione in un piano di esecuzione — una sequenza di operazioni dell'algebra relazionale — scegliendo l'ordine e i metodi più efficienti (quale tabella scandire per prima, se usare un indice). L'utente dichiara il «che cosa» e il sistema decide il «come»: la stessa query può essere eseguita in modi diversi, tutti corretti ma di costo assai diverso, ed è compito dell'ottimizzatore trovare il migliore basandosi su statistiche sui dati. Un pilastro dell'eleganza di SQL è la proprietà di chiusura: il risultato di una SELECT è a sua volta una tabella, e come tale può essere riutilizzato — annidato in un'altra query come sottointerrogazione (subquery), salvato in una vista, unito ad altre tabelle. È questa componibilità che permette di costruire interrogazioni complesse a partire da mattoni semplici, esattamente come nell'algebra relazionale sottostante. Va infine ricordato che, sebbene SQL sia normato dallo standard ISO/ANSI, ogni DBMS (MySQL, PostgreSQL, Oracle, SQLite) ne implementa un proprio dialetto con estensioni e piccole differenze: il nucleo — CREATE, INSERT, SELECT, JOIN — è portabile, mentre le funzioni avanzate spesso no, e conviene sempre sapere quale sistema si sta usando.

Approfondimento

Un aspetto di progettazione fisica che incide enormemente sulle prestazioni è l'indice. Un indice (creato con «CREATE INDEX») è una struttura dati ausiliaria che il DBMS mantiene su una o più colonne per ritrovare rapidamente le righe con un dato valore, senza scandire l'intera tabella. La struttura tipica è il B-albero (o la sua variante B+-albero), un albero di ricerca bilanciato e a molte vie che mantiene sempre l'altezza bassa, così che una ricerca costi «O(log n)» invece degli «O(n)» di una scansione completa — la stessa idea della ricerca binaria e degli alberi bilanciati, qui applicata ai dati su disco. La chiave primaria e i vincoli UNIQUE sono di norma indicizzati automaticamente, ed è per questo che le ricerche e i JOIN su chiave risultano veloci. Ogni indice, però, ha un costo: occupa spazio e va aggiornato a ogni INSERT, UPDATE o DELETE, rallentando le scritture. Nasce così un compromesso di progetto: indicizzare le colonne usate spesso nei filtri e nei join accelera le letture, ma indicizzare tutto appesantisce inutilmente le modifiche. Sapere quando conviene un indice è parte della competenza di chi progetta una base di dati efficiente, e mostra concretamente come la teoria delle strutture dati (alberi bilanciati, complessità logaritmica) governi la pratica dei DBMS.

Approfondimento

I comandi che cambiano i dati (INSERT, UPDATE, DELETE) prendono pieno significato dentro le transazioni SQL. Una transazione si delimita con «BEGIN» (o «START TRANSACTION»), si conferma con «COMMIT», che rende permanenti tutte le modifiche, o si annulla con «ROLLBACK», che le disfa riportando la base di dati allo stato iniziale — è il meccanismo con cui si realizza l'atomicità ACID: un trasferimento di fondi che aggiorna due conti o va a buon fine per intero, o non lascia alcuna traccia. In un sistema con molti utenti simultanei, il DBMS deve inoltre isolare le transazioni concorrenti; lo standard definisce livelli di isolamento crescenti che bilanciano correttezza e prestazioni, perché un isolamento più debole è più veloce ma espone ad anomalie: la lettura sporca (dirty read, in cui si legge un dato non ancora confermato da un'altra transazione), la lettura non ripetibile e la lettura fantasma. Salendo di livello, fino a «SERIALIZABLE» (il più forte), queste anomalie spariscono, al prezzo di più blocchi e minore parallelismo. È per questo che una singola «UPDATE» dimenticata fuori da una transazione, o un livello di isolamento scelto male, possono causare in produzione incoerenze difficili da diagnosticare: il DML va sempre pensato nel contesto della concorrenza, non come una sequenza di comandi isolati.

Approfondimento

Per padroneggiare le interrogazioni conviene conoscere l'ordine logico di valutazione di una SELECT, che non coincide con l'ordine in cui la si scrive: prima «FROM» (individua e combina le sorgenti), poi «WHERE» (filtra le righe), quindi «GROUP BY» e «HAVING» (raggruppa e filtra i gruppi), poi «SELECT» (proietta e calcola le colonne, ed è qui che gli alias diventano definiti), infine «ORDER BY» (ordina) e l'eventuale «LIMIT» (limita il numero di righe restituite). Comprendere questo ordine spiega molti comportamenti apparentemente strani, per esempio perché un alias introdotto nella SELECT non è sempre utilizzabile nel WHERE. Lo strumento più potente che si aggiunge alla SELECT è la sottointerrogazione (subquery): una query annidata dentro un'altra, resa possibile dalla proprietà di chiusura del modello relazionale. Una subquery può comparire nel WHERE come valore scalare («... WHERE prezzo > (SELECT AVG(prezzo) FROM Prodotto)»), come insieme con «IN», o come test di esistenza con «EXISTS». Particolarmente espressiva è la subquery correlata, che si riferisce a una colonna della query esterna e viene rivalutata per ciascuna riga di questa: permette di esprimere condizioni come «i prodotti più cari della loro categoria», che un singolo livello di query non catturerebbe. È in questa componibilità che SQL rivela tutta la sua potenza dichiarativa.

Approfondimento

Vale la pena svolgere per intero una query di sintesi che intreccia JOIN, aggregazione e raggruppamento. Per ottenere «la media dei voti per ogni materia, limitata alle materie con media superiore a 6, in ordine decrescente» si scrive: «SELECT materia, AVG(valore) AS media FROM Voto GROUP BY materia HAVING AVG(valore) > 6 ORDER BY media DESC». Se servisse anche il nome del docente di ciascuna materia, si aggiungerebbe un JOIN con la tabella dei docenti sulla chiave della materia. Sul piano teorico, un JOIN non è altro che un prodotto cartesiano seguito da una selezione: «A JOIN B ON A.fk = B.pk» equivale a formare tutte le coppie di righe (prodotto) e tenere solo quelle che soddisfano la condizione — dimenticare la clausola «ON» produce infatti l'intero prodotto cartesiano, con un numero di righe pari al prodotto delle cardinalità, un errore tanto comune quanto costoso. Un caso particolare è il self-join, in cui una tabella si unisce a sé stessa con due alias diversi per confrontare righe della stessa tabella (per esempio gli studenti della medesima classe). Poiché il JOIN è spesso l'operazione più onerosa di una query, la sua efficienza dipende dalla presenza di indici sulle colonne di accostamento: senza indice il DBMS deve confrontare moltissime righe, con l'indice ritrova rapidamente le corrispondenze — di nuovo il legame diretto fra strutture dati efficienti e prestazioni delle interrogazioni.