La stessa query di report di venti righe è stata copiata in cinque dashboard e ogni copia si è leggermente spostata. Uno conta gli ordini annullati, mentre un altro no. Visualizzazioni SQL risolvono questo problema definendo quella logica una volta, nel database, con un nome chiaro.

Abbiamo eseguito tutti gli esempi riportati di seguito in SQLite 3.50 e i risultati mostrati provengono da quell’esecuzione. Le viste sono SQL standard, ma dettagli come l’aggiornamento tramite una vista, le viste materializzate e la gestione delle dipendenze variano tra i database e li segnaliamo.

Cos’è una vista?

Una vista è un’istruzione SELECT salvata nel database con un nome. Quando esegui una query sulla vista, il database esegue la query salvata. Funziona come una tabella per la lettura, ma una vista normale non conserva la propria copia dei dati.

Pensa a un filtro salvato in un foglio di calcolo. Il filtro non è una seconda copia del foglio. È un modo di guardare gli stessi dati, che riflette sempre i valori più recenti.

Le tabelle campione

CREATE TABLE customers (
  id INT PRIMARY KEY, name TEXT, city TEXT, email TEXT, credit_limit INT
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT, total INT, status TEXT
);

INSERT INTO customers VALUES
 (1,'Asha','Pune','asha@example.com',50000),
 (2,'Ben','Delhi','ben@example.com',20000),
 (3,'Chitra','Pune','chitra@example.com',80000);

INSERT INTO orders VALUES
 (101,1,1200,'paid'), (102,1,800,'paid'),
 (103,2,300,'pending'), (104,3,2500,'paid');

Crea una vista

Questa visualizzazione riepiloga gli ordini di ciascun cliente. Nasconde un join, un raggruppamento e un’espressione CASE dietro un nome semplice:

CREATE VIEW customer_sales AS
SELECT c.id, c.name, c.city,
       COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END), 0) AS paid_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

Ora chiunque può usarlo, senza sapere come funziona:

SELECT * FROM customer_sales ORDER BY paid_total DESC;

-- 3 | Chitra | Pune  | 1 | 2500
-- 1 | Asha   | Pune  | 2 | 2000
-- 2 | Ben    | Delhi | 1 |    0

SELECT name, paid_total FROM customer_sales WHERE city = 'Pune';

-- Asha   | 2000
-- Chitra | 2500

Ben appare con zero, perché la vista utilizza LEFT JOIN e COALESCE. Decisioni come questa, prese una volta nella vista, sono esattamente ciò che mantiene coerenti i report. Per rinfrescare la tua comprensione della gestione dei NULL, consulta Valori NULL SQL.

Le visualizzazioni sono sempre attuali

Poiché una visualizzazione normale esegue la query ogni volta, i nuovi dati vengono visualizzati immediatamente. Abbiamo aggiunto un ordine pagato per Ben e abbiamo nuovamente interrogato la vista:

INSERT INTO orders VALUES (105, 2, 900, 'paid');

SELECT name, paid_total FROM customer_sales WHERE name = 'Ben';

-- Ben | 900

Non è stato effettuato alcun passaggio di aggiornamento né copia da mantenere sincronizzata. Questa è la differenza fondamentale rispetto al salvataggio dei risultati della query in una tabella separata.

Nascondere le colonne

Le visualizzazioni vengono spesso utilizzate per esporre solo le colonne necessarie a una persona o a un’applicazione. Qui gli indirizzi e-mail e i limiti di credito vengono omessi:

CREATE VIEW public_customers AS
SELECT id, name, city FROM customers;

-- columns: id, name, city

Nei database con autorizzazioni, come PostgreSQL, SQL Server e MySQL, è possibile concedere a un utente del reporting l’accesso alla vista ma non alla tabella sottostante. SQLite non ha account utente, quindi potremmo mostrare solo la restrizione della colonna stessa. Tratta una vista come un livello di protezione. I dati sensibili necessitano ancora di un adeguato controllo degli accessi e alcuni database offrono sicurezza a livello di riga per filtrare le righe in base all’utente.

Prestazioni: le visualizzazioni non aumentano la velocità

Un mito comune è che le visualizzazioni rendano le query più veloci. Una vista normale viene sostituita dalla sua definizione quando la interroghi, quindi il database fa lo stesso lavoro come se avessi scritto tu stesso la query completa. Il nostro piano di esecuzione mostrava l’esecuzione dell’unione e del raggruppamento della vista come parte della query:

EXPLAIN QUERY PLAN SELECT * FROM customer_sales WHERE city = 'Pune';

-- CO-ROUTINE customer_sales
-- SCAN c ...
-- SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
-- USE TEMP B-TREE FOR GROUP BY
-- SCAN customer_sales

Quindi una visualizzazione su una query lenta è ancora una query lenta. La velocità deriva dagli indici e da una buona progettazione delle query, come illustrato in Ottimizzazione delle query SQL e Spiegazione degli indici SQL. Fai attenzione anche alle visualizzazioni basate su altre visualizzazioni. Gli strati di annidamento facilitano l’unione più volte dello stesso grande tavolo senza accorgersene.

Viste materializzate

Una visione materializzatalo famemorizza i suoi risultati, come una tabella memorizzata nella cache. Le letture sono veloci, ma i dati sono aggiornati quanto l’ultimo aggiornamento.

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(total) AS revenue FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;

Il supporto varia. PostgreSQL e Oracle hanno viste materializzate e SQL Server offre viste indicizzate con le proprie regole. SQLite e MySQL ne sono sprovvisti, quindi le persone utilizzano una tabella di riepilogo regolare aggiornata in base a una pianificazione. Non abbiamo eseguito l’esempio PostgreSQL per questo articolo.

Puoi aggiornare una vista?

Dipende dal database e dalla vista. Una vista semplice su una singola tabella, senza raggruppamento o DISTINCT, può spesso essere aggiornata in PostgreSQL, MySQL e SQL Server e la modifica passa alla tabella. Una vista con join o aggregati generalmente non può, perché il database non è in grado di indicare quale riga modificare.

SQLite non consente affatto la scrittura diretta sulle visualizzazioni. Il nostro tentativo è fallito:

INSERT INTO customer_sales (name) VALUES ('X');

-- cannot modify customer_sales because it is a view

Alcuni database supportano INSTEAD OF trigger per rendere scrivibile una vista e molti offrono WITH CHECK OPTION, che interrompe le scritture che non sarebbero visibili attraverso la vista. In pratica, la maggior parte dei team mantiene le visualizzazioni di sola lettura e scrive nelle tabelle.

Dipendenze e rotture

Una vista dipende dalle tabelle da cui legge. Abbiamo creato una vista su una tabella, quindi abbiamo rilasciato la tabella:

CREATE TABLE ghost AS SELECT 1 AS x;
CREATE VIEW v_ghost AS SELECT x FROM ghost;
DROP TABLE ghost;

SELECT * FROM v_ghost;
-- no such table: main.ghost

SQLite ha consentito l’eliminazione e ha fallito solo in seguito, quando è stata utilizzata la vista. Altri database, come PostgreSQL, si rifiutano di eliminare una tabella da cui dipende una vista, a meno che non si aggiunga CASCADE, che rimuove anche la vista. In ogni caso, rinominare o eliminare una colonna può interrompere le visualizzazioni, quindi controllale nei tuoi test di regressione dopo le modifiche allo schema.

C’è anche una sottile trappola con SELECT * in una vista. Abbiamo creato CREATE VIEW star AS SELECT * FROM customers, quindi abbiamo aggiunto una colonna alla tabella. In SQLite, la vista ha rilevato la nuova colonna. Altri database, incluso PostgreSQL, espandono l’elenco delle colonne quando viene creata la vista, quindi le nuove colonne non vengono visualizzate. Quindi elenca le colonne in modo esplicito. Documenta la vista e si comporta allo stesso modo ovunque.

Gestione delle visualizzazioni

L’eliminazione di una visualizzazione rimuove solo la query salvata. Nel nostro test, la tabella customers aveva ancora tutte e tre le righe dopo aver eliminato public_customers. Mantieni le definizioni delle viste nel controllo della versione insieme alle migrazioni, in modo che le modifiche vengano esaminate come qualsiasi altro codice.

Vista vs CTE vs tabella

Errori comuni

• Mi aspettavo un aumento di velocità. Una visualizzazione normale esegue la query ogni volta.
• Utilizzo di SELECT * nelle visualizzazioni. Il comportamento differisce tra i database quando le tabelle cambiano.
• Nidificazione profonda. Le visualizzazioni basate su visualizzazioni nascondono unioni costose.
• Utilizzo di ORDER BY all’interno di una vista. L’ordine non è garantito quando lo esegui una query, quindi ordina la query esterna.
• Nomi poco chiari. Un prefisso come v_, o un nome chiaro come customer_sales, aiuta.
• Dimenticare le visualizzazioni durante le migrazioni. Testarle dopo ogni modifica dello schema.

Considerazioni finali

Le visualizzazioni rappresentano un modo semplice per assegnare una posizione logica importante nel database. Usali per mantenere i report coerenti e per nascondere le colonne non necessarie, non come un trucco per le prestazioni. Per iniziare, trova una query che appare in diversi punti del tuo codice, trasformala in una vista con colonne esplicite e indirizza ad essa ciascun chiamante.

Domande frequenti

Continua a imparare