MySQL 5.1 per l'impaziente

Novità

Il recente rilascio di MySQL 5.1 porta a chiedersi cosa si può fare con la nuova versione, e se valga la pena aggiornare dalla 5.0. Vediamo rapidamente quali sono le novità principali.

• Il partizionamento di tabelle. Si tratta di un meccanismo per dividere le tabelle in porzioni logiche, che rendono più veloce l'inserimento e la ricerca dei dati, in maniera trasparente per l'utente.
• Un gestore di eventi (event scheduler) che consente la definizione di comandi da eseguire in una certa data e ora, oppure a intervalli regolari. Il tutti è integrato nel database, e funziona a prescindere dal sistema operativo adottato.
• Un'interfaccia plugin dinamica. Questo è un cambiamento architetturale che facilita la creazione di "storage engines" o di funzionalità aggiuntive. Grazie a questo meccanismo, sono ora disponibili diversi plugin creati da terze parti.
• La replicazione "row-based", che migliora e fa da complemento all'attuale replicazione. Nel sistema tradizionale, ci sono alcune incongruenze, che vengono sanate da questo nuovo modello.
• I "log" a richiesta. Fino alla versione 5.0, per attivare il "general log" o il log delle query lente, bisogna riavviare il server. Nella versione 5.1, non è necessario il riavvio. Tali log possono essere avviati e fermati a piacere.

Il motivo principale per usare la 5.1 è sicuramnte un aumento di prestazioni grazie alle partizioni. Ma anche gli eventi sono molto richiesti. Recentemente, è stato lanciato un concorso per i migliori casi di uso della versione 5.1 fra gli utenti che hanno l'adottata prima che fosse dichiarata GA. Gli articoli sono pubblicati nel sito di MySQL. Fra gli avanguardisti premiati, c'e' anche un sito italiano (www.inter.it).
Per l'elenco completo delle novità, si veda il manuale.

Alcuni esempi pratici

Partizioni a intervalli

Questa funzionalità consente un aumento di prestazioni quando si debba lavorare su tabelle di grosse dimensioni. Esistono diversi tipi di partizioni (intervallo, lista, chiave, hash). Il più comune, quando si tratti di archivi storici, è il partizionamento a intervallo (range partitioning).
Per partizionare a intervalli, si identifica una colonna che abbia una buona distribuzione di valori, e la si usa come elemento di partizionamento. Per esempio, una tabella di clienti potrebbe essere partizionata per nazione/regione o per fascia di fatturato. Una tabella di ordini potrebbe essere partizionata per anno o mese di emissione.
Per esemplificare, si consideri questa tabella:

CREATE TABLE prestiti
(
  ID_libro INT not null,
  ID_utente INT not null,
  data_prestito date not null,
  ID_filiale INT not null,
  annotazioni text,
  PRIMARY KEY (ID_libro, ID_utente, data_prestito)  
);

Se si hanno 5.000 utenti e si prevede di crescere fino a 20.000, si può partizionare in questo modo:

ALTER TABLE prestiti
PARTITION BY RANGE (ID_utente)
(
   PARTITION p01 VALUES LESS THAN ( 2000),
   PARTITION p02 VALUES LESS THAN ( 4000),
   PARTITION p03 VALUES LESS THAN ( 6000),
   PARTITION p04 VALUES LESS THAN ( 8000),
   PARTITION p05 VALUES LESS THAN (10000),
   PARTITION p06 VALUES LESS THAN (12000),
   PARTITION p07 VALUES LESS THAN (14000),
   PARTITION p08 VALUES LESS THAN (16000),
   PARTITION p09 VALUES LESS THAN (18000),
   PARTITION p10 VALUES LESS THAN (MAXVALUE)
);

Oppure, se i prestiti fossero sparsi in diversi anni, potremmo usare la data:

ALTER TABLE prestiti
PARTITION BY RANGE (YEAR(data_prestito))
(
   PARTITION p01 VALUES LESS THAN (1990),
   PARTITION p02 VALUES LESS THAN (1991),
   PARTITION p03 VALUES LESS THAN (1992),
   PARTITION p04 VALUES LESS THAN (1993),
   PARTITION p05 VALUES LESS THAN (1994),
   PARTITION p06 VALUES LESS THAN (1995),
   PARTITION p07 VALUES LESS THAN (1996),
   PARTITION p08 VALUES LESS THAN (1997),
   PARTITION p09 VALUES LESS THAN (1998),
   PARTITION p10 VALUES LESS THAN (1999),
   PARTITION p11 VALUES LESS THAN (2000),
   PARTITION p12 VALUES LESS THAN (2001),
   PARTITION p13 VALUES LESS THAN (2002),
   PARTITION p14 VALUES LESS THAN (2003),
   PARTITION p15 VALUES LESS THAN (2004),
   PARTITION p16 VALUES LESS THAN (2005),
   PARTITION p17 VALUES LESS THAN (2006),
   PARTITION p18 VALUES LESS THAN (2007),
   PARTITION p19 VALUES LESS THAN (2008),
   PARTITION p20 VALUES LESS THAN (2009),
   PARTITION p21 VALUES LESS THAN (MAXVALUE)
);

È importante fare dei test di efficienza prima di adottare le partizioni. In entrambi i casi, si noti che la ricerca di una tabella partizionata sarà più efficace di una tabella senza partizioni solo se la query usa la colonna di partizionamento. Per esempio, questa query non sfrutta le partizioni:

SELECT COUNT(*) FROM prestiti WHERE ID_libro = 100;

Questa sfrutta le partizioni con il primo schema:

SELECT COUNT(*) FROM prestiti WHERE ID_cliente = 10 AND ID_libro = 100;

Questa sfrutta le partizioni con il primo schema:

SELECT COUNT(*) FROM prestiti
WHERE data_prestito BETWEEN '2005-01-01'
AND '2006-10-31' ID_libro = 100;

Per maggiori dettagli sull'uso delle partizioni in pratica, si veda l'articolo MySQL 5.1 partitions in practice (in inglese).

Gestione degli eventi

Il gestore degli eventi è un grande aiuto per l'amministratore. Se si devono gestire database su diversi sistemi operativi, il gestore integrato è un potente alleato.
Gli eventi si definiscono con un comando SQL. Si può impostare un evento a un orario predefinito o a intervalli regolari. Per esempio, per eseguire un comando specifico il 31 gennaio 2009 alle 10 di mattina, si usa:

CREATE EVENT nome_evento
ON SCHEDULE AT '2009-01-31 10:00:00'
DO
CALL fine_servizio_attivo(12345);

Invece di richiamare una procedura specifica, si può usare un blocco BEGIN ... END.

CREATE EVENT nome_evento
ON SCHEDULE AT '2009-01-31 10:00:00'
DO
BEGIN
  UPDATE impiegati
  SET stato = 'in pensione'
  WHERE ID_impiegato = 12345;
  COMMIT;
END;

La sintassi per l'esecuzione periodica è simile.

CREATE EVENT nome_evento
ON SCHEDULE EVERY 12 HOUR
DO
CALL cancella_vecchi_record();

Ci sono molti esempi di uso normale e creativo degli eventi nel mio blog in inglese (The Data Charmer) e negli articoli della Developer's Zone.