Materialized View

Le viste sono molto comode per dare un particolare “taglio” predefinito all’osservazione dei dati e osservarlo sotto punti di vista che non coincidano con la struttura normalizzata delle tabelle. Ad esempio, per rappresentare dei report molto elaborati. Quando il tempo di elaborazione di una vista è molto lungo e magari i dati sottostanti sono poco soggetti a cambiamenti, può essere comodo utilizzare una vista materializzata (o materialized view). Questa non è altro che una normale tabella che contiene il risultato di una query. Altri RDBMS, come Oracle e PostegreSQL, supportano nativamente questo tipo di oggetto e ne permettono la creazione e l’aggiornamento tramite istruzioni predefinite. Con MySQL, invece, bisogna realizzare le viste come normali tabelle, mentre per l’aggiornamento bisogna ricorrere a procedure ed eventualmente a trigger.

Vediamo un esempio. Consideriamo una vista che contiene il report delle vendite suddiviso per anno, regione, prodotto, eccetera. La creazione di un tale report potrebbe incrociare dati di molte tabelle tramite molti join, aggregare migliaia di record sul database e quindi essere molto lenta. Per di più molti di questi dati, come quelli sulle vendite degli anni passati, non sono soggetti a cambiamenti, e quindi può essere comodo ricorrere ad una vista materializzata. Per semplicità ipotizziamo che il risultato dell’elaborazione sia composto soltanto da colonne rappresentanti l'anno, il tipo di prodotto, la regione di vendita e le entrate totali.

CREATE TABLE mv_sales_report(year INT, product_type VARCHAR(200), region VARCHAR(200), amount DECIMAL(10,2))

A questo punto passiamo a creare la procedura per aggiornare la nostra vista materializzata. Utilizziamo la sintassi dei DELIMITER, in modo da potere definire la procedura includendo il simbolo ;, che è il delimitatore di default:

DELIMITER //
CREATE PROCEDURE refresh_mv_sales_report ()
BEGIN
  TRUNCATE TABLE mv_sales_report;
  INSERT INTO mv_sales_report
  SELECT YEAR(sales.datesell), products.product_type, sellers.region, SUM(sales.price)
    FROM sales JOIN products on sales.productId = products.id JOIN sellers on sales.sellerId = sellers.id
  GROUP BY YEAR(sales.datesell), products.product_type, sellers.region WITH ROLLUP;
END //
DELIMITER ;

Questa procedura ricalcola tutta la vista, svuotandola prima di reinserire tutto. Ora possiamo invocarla per popolare la vista materializzata:

CALL refresh_mv_sales_report()

Adesso possiamo interrogare la vista materializzata con una velocità molto maggiore rispetto ad una normale interrogazione su una vista. Inoltre, in fase di lettura non interferiamo con le scritture che eventualmente avvengono sulle tabelle da cui provengono i dati.

Aggiornamento della vista materializzata

Per l’aggiornamento si possono usare varie strategie. Naturalmente possiamo aggiornare a comando, invocando la procedura come sopra, oppure possiamo aggiornare ad intervalli di tempo preimpostati, ad esempio una volta ogni ora, utilizzando gli eventi di MySQL:

DELIMITER //
CREATE EVENT e_refresh_mv_sales_report
ON SCHEDULE EVERY 1 HOUR
COMMENT 'Aggiorna la vista materializzata mv_sales_report'
DO
BEGIN
CALL refresh_mv_sales_report();
END //
DELIMITER ;

Gli eventi sono attività personalizzate che vengono eseguite in base ad una schedulazione. MySQL permette di creare eventi da eseguire una tantum in un istante temporale preciso, oppure, come nel nostro caso, ad intervalli regolari con una risoluzione che va da microsecondi ad anni.

Aggiornamento con delta

A volte non ha senso aggiornare tutta la vista se a cambiare è solo una porzione di essa. Nel nostro esempio, gli anni precedenti a quello attuale difficilmente cambieranno nel tempo. Si può scrivere quindi una procedura per l’aggiornamento parziale della vista:

delimiter //
CREATE PROCEDURE refresh_mv_sales_report_year (yearsales INT)
BEGIN
DELETE FROM mv_sales_report WHERE year = yearsales;
  INSERT INTO mv_sales_report
  SELECT YEAR(sales.datesell), products.product_type, sellers.region, SUM(sales.price)
    FROM sales JOIN products on sales.productId = products.id JOIN sellers on sales.sellerId = sellers.id
   WHERE YEAR(sales.datesell) = yearsales
  GROUP BY YEAR(sales.datesell), products.product_type, sellers.region WITH ROLLUP;
END //
delimiter ;

A questo punto, possiamo o decidere di aggiornare solamente l’ultimo anno, modificando l’evento che abbiamo visto prima, oppure possiamo creare un trigger che, ad ogni inserimento nella tabella sales aggiorna solo l’anno che è stato modificato. In questo modo avremo sempre la vista aggiornata in tempo reale:

DELIMITER //
CREATE TRIGGER t_sales_insert
AFTER INSERT
ON sales FOR EACH ROW
BEGIN
CALL refresh_mv_sales_report_year(YEAR(NEW.datesell));
END //
DELIMITER ;

Questo approccio ha il difetto però di rallentare gli inserimenti, perché i trigger vengono eseguiti in transazione.