Trovare ed eliminare duplicati

La duplicazione di dati non dovrebbe mai avvenire in un database bene organizzato, ma talvolta, quando si ha a che fare con dati ereditati da applicazioni poco accurate, può accadere di ritrovarsi con una tabella che contiene record duplicati.

In questa poco piacevole situazione, ci sono tre tipi di operazioni che si vorrebbero eseguire:

  • Elencare i record senza duplicati;
  • Elencare i valori duplicati;
  • Eliminare i duplicati.

Prendiamo ad esempio una tabella con qualche record duplicato (per esigenze di spazio, la facciamo corta, ma supponete di avere svariate migliaia di record).


mysql> SELECT * FROM condoppi;
+---+------+-----+
| i | a | b |
+---+------+-----+
| 1 | aaa | xxx |
| 2 | bbb | yyy |
| 3 | aaa | yyy |
| 4 | ccc | www |
| 5 | aaa | xxx |
| 6 | ddd | zzz |
| 7 | aaa | xxx |
| 8 | bbb | yyy |
| 9 | ccc | www |
+---+------+-----+
9 rows in set (0.18 sec)

La prima esigenza, vedere i record senza doppi, viene risolta facilmente usando la clausola DISTINCT:


mysql> SELECT DISTINCT a, b FROM condoppi;
+------+-----+
| a | b |
+------+-----+
| aaa | xxx |
| aaa | yyy |
| bbb | yyy |
| ccc | www |
| ddd | zzz |
+------+-----+
5 rows in set (0.12 sec)

Facilissimo. Basta premettere DISTINCT alla lista dei campi per avere un elenco pulito, senza duplicati.

Notate che se avessi inserito fra i campi anche la chiave primaria (campo “i”), il DBMS mi avrebbe stampato tutti i record, perché ovviamente sono differenziati grazie al campo univoco. La clausola DISTINCT è anche utile per avere un elenco dei valori distinti (appunto) per una data colonna. Per la seconda esigenza, vedere quali valori sono doppiati, non c’è formula magica immediata, ma bisogna sforzarsi un po’ di più.

Il metodo consiste nel raggruppare i valori con un conteggio, e considerare solo quelli maggiori di 1. Si noti la clausola HAVING che (a differenza di WHERE) viene valutata dopo l’operazone di raggruppamento.


mysql> SELECT a, b COUNT(*) AS quanti
FROM condoppi
GROUP BY a, b
HAVING quanti > 1;
+------+-----+--------+
| a | b | quanti |
+------+-----+--------+
| aaa | xxx | 2 |
| bbb | yyy | 2 |
| ccc | www | 2 |
+------+-----+--------+
3 rows in set (0.39 sec)

Così possiamo vedere quali sono i valori (o le combinazioni di valori) che sono registrati più di una volta.

Infine, se vogliamo cancellare dalla tabella i valori doppi, esiste un metodo molto semplice e pratico: aggiungere un indice univoco alla tabella, ma usando la clausola IGNORE, per far sì che il DBMS non si fermi al primo errore di record duplicato, ma vada avanti, di fatto saltando l’inserimento dei record con valori già inseriti.


mysql> ALTER IGNORE TABLE condoppi ADD UNIQUE KEY (a, b) ;
Query OK, 9 rows affected (0.54 sec)
Records: 9 Duplicati: 4 Avvertimenti: 0
mysql> SELECT * FROM condoppi;
+---+------+-----+
| i | a | b |
+---+------+-----+
| 1 | aaa | xxx |
| 2 | bbb | yyy |
| 3 | aaa | yyy |
| 4 | ccc | www |
| 6 | ddd | zzz |
+---+------+-----+
5 rows in set (0.02 sec)

Aggiornare più condizioni con una sola query

PW scrive che vorrebbe trovare un modo di eseguire diversi UPDATE su una tabella usando una sola query. Nella pratica, si trova ad avere record di prodotti di diverse aziende, che vuole aggiornare in maniera omogenea, ma senza dover ripetere la stessa query diverse volte.

Per esempio, vorrebbe cambiare lo sconto offerta sui libri di informatica, di finanze, di attualità, con quantità differenti per ogni categoria. Una possibilità è la seguente:

UPDATE libri
SET sconto =
CASE
WHEN sezione = 'informatica' THEN 12
WHEN sezione = 'attualita' THEN 10
WHEN sezione = 'finanze' THEN 8
ELSE sconto
END
WHERE id_editore IN (5,6,8,11);

Questa query agisce solo sui libri degli editori considerati (clausola WHERE) e al loro interno modifica il campo sconto solo nelle sezioni indicate. Per le altre sezioni, il valore viene lasciato uguale, grazie alla clausola ELSE della funzione CASE.

Comporre query di questo tipo può essere più difficile che comporre una query per ogni condizione, ma talora si guadagna in efficienza, ed è bene sapere che ci sono alternative!

Passaggio efficiente di record tra client e server

In un protocollo client-server, quando viene eseguita una query, il server ha due modi per inviare i record trovati al client: uno alla volta o tutti insieme. Il modo effettivamente usato dipende dalle richieste del client.

Programmando MySQL con un linguaggio evoluto come Perl o PHP, la differenza non si coglie, perché il modo di ricezione attivato per default è il “tutti insieme”, cioè il server invia i record in un unico array che viene quindi trasferito alla memoria del client.

Chi si è preso la briga di guardare dietro le quinte del driver usato da Perl e PHP, però, ha visto che questi linguaggi si appoggiano alla libreria di funzioni scritta in C, che ha la capacità di differenziare il tipo di invio dei record. La libreria delle API (Application Programming Interface) di MySQL ha due funzioni per questo compito: mysql_use_result e mysql_store_result.

mysql_store_result è il comportamento più comodo per il programmatore, perché trasporta tutti i record ritrovati in una sola mandata. In questo modo il programma ha a disposizione due funzionalità che di solito si danno per scotate, ma non lo sono: il numero di record ritrovati e la possibilità di saltare avanti e indietro nella lista dei record.

mysql_use_result invece istruisce il server perché invii i record uno alla volta. Questo comportamento, che a prima vista potrebbe sembrare illogico e indesiderabile, trova fondamento nei casi in cui si debbano leggere grandi quantità di record, quando è meglio prendere i record uno per uno, farci quel che ci si deve fare (per esempio stamparlo o inviarlo a un file) e passare a quello successivo.

Poiché mandare i record tutti insieme richiede il costo aggiuntivo dell’occupazione di memoria per un array (la cui creazione richiede più tempo della semplice occupazione dello spazio di un singolo record) quando si manipolano grandi quantità di record (parlo di decine di MB), allora la gestione uno-alla-volta si dimostra più veloce.

Per contro, mysql_use_result ha lo svantaggio di non poter dire quanti record sono stati trovati finché tutti sono stati trasferiti dal server al client.

Comunque, per quantità di record limitate (dipende anche dalle risorse della macchina) che coprono la maggior parte delle applicazioni, mysql_store_result è il metodo da preferirsi.

Ma, mi sento dire, se l’uso di queste funzioni si stabilisce in base al presunto numero di record e il programmatore non può conoscere il numero prima dell’esecuzione della query, com’è possibile prendere una decisione?

Potrei dire che i programmatori bravi sanno a priori se la query che stanno per eseguire è di quelle da tremila record o da tre milioni, ma a parte quelli che hanno queste intuizioni difficilmente trasferibili ad altri, se il vostro database ha una gran quantità di record e avete paura che un risultato ve ne porti qualche milione sul client, esaurendo tutta la memoria se usate mysql_store_result, potete sempre sondare il database con una query di conteggio.

Per esempio, prima di eseguire


SELECT autore, titolo FROM libridiamazon WHERE autore LIKE "A%";

potete sondare il database con questa:


SELECT COUNT(*) FROM libridiamazon WHERE autore LIKE "A%";

Se il conteggio è nell’ordine delle migliaia, usate mysql_store_result. Se invece si va oltre i diecimila, forse è il caso di scegliere mysql_use_result. Volete sapere come gestire questo doppio comportamento da Perl e PHP?

Lista record con numero progressivo

Questa richiesta è arrivata su un forum di programmazione: Come faccio a elencare i risultati di una query con un numero progressivo per ogni record?

Ci sono diverse risposte a questa domanda. La più immediata sarebbe di implementare il conteggio nell’ambito dell’applicazione che riceve i dati. Questa soluzione, però, richiede dettagli diversi in ogni linguaggio e potrebbe non essere quel che si cerca.

Invece, esiste un modo per far contare i record a MySQL, in modo da avere un risultato che sia lo stesso, a prescindere dal linguaggio usato.

Forse non tutti sanno che MySQL consente di usare delle variabili “globali” che sono attive per tutta la durata di una connessione. Per esempio, un uso frequente di questa funzionalità si ha per ovviare alla mancanza di subquery nelle versioni inferiori alla 4.1:


mysql> SELECT @massimo := max(stipendio) from paghe;
mysql> SELECT nome, cognome, stipendio from paghe where stipendio = @massimo;

In maniera simile, possiamo usare una variabile per creare un contatore. Poiché ogni variabile ha valore NULL finché non le viene assegnato esplicitamente qualcosa, è necessario eseguire due query per poter raggiungere lo scopo. Prima, vediamo la query senza contatore.


mysql> SELECT nome,stipendio, genere FROM persone;
+--------+-----------+--------+
| nome | stipendio | genere |
+--------+-----------+--------+
| John | 5000 | m |
| Mario | 6000 | m |
| Frank | 5000 | m |
| Otto | 6000 | m |
| Susan | 5500 | f |
| Martin | 5500 | m |
| Mary | 5500 | f |
| Bill | 5000 | m |
| June | 6000 | f |
+--------+-----------+--------+
9 rows in set (0.01 sec)

Poi, eseguiamo le due query. Innanzitutto inizializziamo il contatore.


mysql> set @N = 0;
Query OK, 0 rows affected (0.27 sec)

Infine, eseguiamo la query includendo il contatore con una formula per incrementarlo.


mysql> SELECT @N := @N +1 AS numero, nome,stipendio,genere FROM persone;
+--------+--------+-----------+--------+
| numero | nome | stipendio | genere |
+--------+--------+-----------+--------+
| 1 | John | 5000 | m |
| 2 | Mario | 6000 | m |
| 3 | Frank | 5000 | m |
| 4 | Otto | 6000 | m |
| 5 | Susan | 5500 | f |
| 6 | Martin | 5500 | m |
| 7 | Mary | 5500 | f |
| 8 | Bill | 5000 | m |
| 9 | June | 6000 | f |
+--------+--------+-----------+--------+
9 rows in set (0.01 sec)

Notate che ogni variabile in una query può essere calcolata una volta sola per ogni record.

Infine, se adottate una soluzione simile, ricordatevi di inizializzare il contatore alla query successiva, altrimenti i vostri record avranno un conteggio in continua crescita!

Trigger su SELECT

I trigger sono aggeggi da usare con cautela. Se abusati, possono portare la vostra applicazione in ginocchio. Tuttavia, sono anche fantastici strumenti per raffinare il controllo in situazioni difficili. Secondo lo standard SQL, i trigger possono essere associati solo alle operazioni di modifica (INSERT, DELETE, UPDATE). E i SELECT, allora? Non sarebbe bello se si potesse instaurare un trigger anche per ogni SELECT?

Supponiamo di avere una tabella books e che vogliamo creare una lista personalizzata degli accessi solo per quella tabella. Ecco come si può fare:

  1. Create una tabella db_counter che conterrà il numero degli accessi, e una tabella db_log che conterrà i dettagli;
  2. Create una funzione che contenga tutte le operazioni che volete eseguire per ogni SELECT. Questa funzione deve restituire un intero positivo;
  3. Create una vista aggiornabile sulla vostra tabella, aggiungendo una clausola WHERE che usa la funzione appena creata.

Andiamo a incominciare:


-- ----------------------------------------------------
-- La tabella originale. Quella per cui vogliamo il log
-- La facciamo semplice per non appesantire l'esempio
-- ----------------------------------------------------
CREATE TABLE books (
book_id INT NOT NULL primary key,
title VARCHAR(40),
pages INT,
price DECIMAL(5,2),
author_id INT NOT NULL,
KEY (author_id)
-- ,FOREIGN KEY (author_id) REFERENCES authors (author_id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- ----------------------------------------------------
-- Il contatore
-- Contiene solo una riga.
-- ----------------------------------------------------
CREATE TABLE db_counter (
id int(11) NOT NULL default '0',
counter bigint(20) default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM;

-- ----------------------------------------------------
-- L'elenco di dettaglio.
-- Ogni riga contiene informazioni sull'orario ,
-- sull'operazione fatta e l'utente.
-- ----------------------------------------------------
CREATE TABLE db_log (
ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
user varchar(20) NOT NULL,
operation enum('select','insert','update','delete') default 'select',
KEY user_ts (user,ts),
KEY ts (ts)
) ENGINE=MyISAM;

Queste ultime due tabelle contengono le informazioni che ci servono. Prima di continuare, qualche ritocco per essere sicuri che la tabella del contatore possa contenere solo una riga:


CREATE TRIGGER counter_insert before INSERT on db_counter
for each row set new.id = 1;
CREATE TRIGGER counter_update before UPDATE on db_counter
for each row set new.id = 1;

E ora alcuni trigger per registrare le operazioni di modifica nella tabella books. (Sarebbe buffo che registriamo gli accessi in lettura e trascuriamo quelli in scrittura!)


CREATE TRIGGER book_insert after INSERT on books
for each row set @BOOKCOUNT = get_counter('insert');
CREATE TRIGGER book_delete after DELETE on books
for each row set @BOOKCOUNT = get_counter('delete');
CREATE TRIGGER book_update after UPDATE on books
for each row set @BOOKCOUNT = get_counter('update');

Siamo pronti per creare la funzione:


delimiter //
drop function if exists get_counter //
create function get_counter( oper varchar(10) )
returns int
deterministic
begin
if ( ( select count(*) from db_counter ) > 0 ) then
update db_counter set counter = counter + 1;
else
insert into db_counter (counter) values (1);
end if;
insert into db_log (ts, user, operation) values (NULL, user(), oper);
return (select counter from db_counter where id = 1);
end //
delimiter ;

Infine, creiamo la vista aggiornabile per la tabella books.


CREATE VIEW mybooks as
SELECT * from books WHERE get_counter() > 0;

Ora, usando mybooks invece di books, ogni accesso a books verrà registrato. Vediamo in azione il nostro meccanismo (notate che usiamo sempre la vista, non la tabella).


gmdesk [localhost] {gmax} (test1) > insert into mybooks values (1, 'MySQL', 1000,39.95,1);
Query OK, 1 row affected (0.02 sec)
gmdesk [localhost] {gmax} (test1) > insert into mybooks values (2, 'Programming Perl', 1095,39.95,2);
Query OK, 1 row affected (0.02 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
| 1 | 2 |
+----+---------+
1 row in set (0.00 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts | user | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:14:22 | gmax@localhost | insert |
| 2005-10-24 11:14:27 | gmax@localhost | insert |
+---------------------+----------------+-----------+
2 rows in set (0.00 sec)

Dopo due inserimenti, il contatore è a due, e la tabella dei dettagli ha due record. Proviamo con il nostro obiettivo principale, cioè una SELECT:

gmdesk [localhost] {gmax} (test1) > select count(*) from mybooks;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts | user | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:17:11 | gmax@localhost | insert |
| 2005-10-24 11:17:23 | gmax@localhost | insert |
| 2005-10-24 11:18:08 | gmax@localhost | select |
+---------------------+----------------+-----------+
3 rows in set (0.01 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
| 1 | 3 |
+----+---------+
1 row in set (0.00 sec)

CVD

Un tutorial per ottimizzare le query SQL su MySQL

Di Corrado Pandiani, pubblicato su Linux&C. n° 59.

Ottimizzare MySQL: come rendere più veloci le query SQL

L’obiettivo minimo che gli sviluppatori che utilizzano MySQL per le proprie applicazioni raggiungono con una certa dimestichezza è quello di saper scrivere query SQL per ottenere i risultati desiderati. In molti, però, si fermano a questo livello, rinunciando a sondare gli strani misteri che sono patrimonio di un buon database administrator: capita quindi che, al crescere della dimensione dei dati e della complessità delle interrogazioni da implementare, ci si trovi di fronte a problemi apparentemente irrisolvibili o ad applicazioni con tempi di risposta inaccettabili.
Non sempre la determinazione del collo di bottiglia è cosa immediata: spesso è decisamente più facile pensare all’acquisto di hardware più potente, o alla ricerca del parametro di configurazione che, se ben impostato, potrebbe consentire la risoluzione di ogni problema.
Il più delle volte le soluzioni da mettere in pratica non sono queste, o meglio, non lo sono in prima istanza: quello che va fatto è una analisi dello schema del database e delle query eseguite dal server.
Riscrivere qualche interrogazione, ottimizzandola, e sistemare qualche tabella è spesso – non sempre – il solo intervento necessario, e in pochi minuti si può addirittura sbloccare una situazione che sembrava ormai cronica.
MySQL è notoriamente un DBMS molto performante, ma non ci si può fidare alla cieca di questa (pur vera) certezza: una progettazione errata e, soprattutto, query scritte male (spesso conseguenza di un database non normalizzato), sono in grado di affossare le performance di qualsiasi server, è solo questione di tempo, dati e memoria.
MySQL mette a disposizione uno strumento prezioso di analisi, poco conosciuto dai non addetti ai lavori, la funzione EXPLAIN, che sarà l’argomento di questo articolo: ne analizzeremo il funzionamento e, con qualche esempio, vedremo quale sia la metodologia di analisi per ottimizzare il funzionamento delle query.
Questa conoscenza consentirà di restituire un po’ di cicli macchina al vostro server “impallato” e, molto probabilmente, di scongiurare nell’immediato l’acquisto di nuovo hardware, cosa che il vostro capo o il vostro cliente apprezzerà più di ogni altro aspetto della questione.

Ripassiamo gli indici

Gli indici saranno i principali protagonisti della nostra trattazione.
Un indice associato ad una tabella non è troppo diverso da quello che trovate all’inizio di un libro: senza di esso, rintracciare un paragrafo o un capitolo diventa assai difficile e c’è da scorrere tutte le pagine presenti. Se poi c’è da trovare tutte le occorrenze di una certa parola, la situazione diventa ancora più tragica in assenza di un indice analitico.
MySQL utilizza la stessa tecnica vista per il libro: se una tabella non ha indici associati, l’unico modo per ritrovare i record di interesse è eseguire l’operazione che prende il nome di “full table scan”, una lettura riga per riga di tutte le informazioni presenti.
Pensate cosa significhi in termine di I/O effettuare una lettura completa di svariati gigabyte solo per trovare qualche riga che soddisfi una ricerca.
Gli indici sono strutture che “puntano” alle informazioni presenti nella tabella, come una sorta di segnalibri: sono composti da una sequenza di nodi contenenti i riferimenti ai record e vengono utilizzati per velocizzare le ricerche all’interno della tabella stessa, laddove questo sia possibile; sono costruiti sui valori di una o più colonne, e creano corrispondenze tra certi valori delle colonne e le righe dove tali valori si trovano.
Nella metafora vista in precedenza, un nodo dell’indice conterrà il titolo di un capitolo o del paragrafo, e verrà tenuto il riferimento del numero di pagina ove tale informazione si trova.
In MySQL è possibile creare diversi tipi di indice:

  • PRIMARY KEY: è l’indice associato alla chiave primaria, non può contenere valori NULL né valori duplicati. Usato spesso in associazione con la direttiva auto_increment in caso di valori numerici unici.
  • UNIQUE: non ci possono essere valori duplicati, sono consentiti i valori NULL.
  • INDEX: ci possono essere valori duplicati e valori NULL.
  • FULLTEXT: indice specializzato per la ricerca di parole nei testi.

Oltre ad essere strutture generalmente molto più piccole rispetto alla tabella cui fanno riferimento, gli indici hanno un trattamento di riguardo da parte di MySQL: i nodi, comprensivi di valore e puntatore, vengono conservati in un buffer di memoria denominato key_buffer (qua e nel seguito si fa riferimento all’engine MyISAM), che contiene i nodi utilizzati più di recente e, compatibilmente con la memoria disponibile, può anche contenere tutti gli indici del database.
La sua dimensione è data dalla variabile key_buffer_size, impostabile a piacere nella sezione [mysqld] del file my.cnf, il file di configurazione di MySQL che si trova in /etc (o in /etc/mysql a seconda della distribuzione).
Il key_buffer è preferibile che abbia il valore più elevato possibile, più è grande maggiore è la probabilità che il nodo dell’indice richiesto sia già in memoria, compatibilmente però con la RAM a disposizione, cercando di evitare il pericolo di swap del sistema operativo che farebbe degradare le performance.
Le query SQL con condizioni di ricerca espresse su campi indicizzati possono trarre grandi vantaggi in termini di tempo di esecuzione: ci sarebbe ancora da dire molto sulla gestione degli indici da parte del database, ma per i nostri scopi può bastare.
Identificare le query da ottimizzare
Prima di usare EXPLAIN dobbiamo selezionare le query che devono essere oggetto della nostra analisi ma, fortunatamente, non tutte avranno bisogno di essere ottimizzate e potranno tranquillamente essere considerate trascurabili.
Per questo scopo ci serve il contenuto dello slow-log, un file di testo nel quale MySQL scrive tutte le query che vengono eseguite in un tempo superiore al valore impostato nel parametro long_query_time (che di default vale 10 secondi).
Associate ad ogni query vengono inoltre riportate informazioni di fondamentale importanza: data e ora, tempo di esecuzione espresso in secondi, numero di righe, tempo di lock, utente richiedente.
Non essendo lo slow-log attivo per default, dobbiamo abilitarlo inserendo il parametro log-slow-queries[=file_name] nella sezione [mysqld] del file my.cnf: in assenza della parte file_name, che ne può specificare un path e un nome alternativo, il file verrà creato nella directory dei dati e si chiamerà host_name-slow.log. Se non sappiamo quale sia la directory dei dati possiamo scoprirlo con il comando:


mysql> SHOW VARIABLES LIKE 'datadir';

Impostati i parametri in my.cnf sarà necessario il riavvio del server. Il database dovrà lavorare per un po’ di tempo affinché il file di log venga popolato. Per disporre di un contenuto significativo che dia una visione realistica del funzionamento del server occorre che lo slow-log copra diverse ore o, meglio ancora, diversi giorni: per una corretta selezione delle query candidate all’ottimizzazione è bene valutare il funzionamento del database su un arco temporale che copra tutte le variazioni possibili di carico e annoveri il funzionamento di tutte le applicazioni che ne fanno uso, in quanto uno slow-log parziale può portare ad analisi non complete e fuorvianti.
Dall’analisi delle query presente possiamo costruire una lista di interrogazioni da analizzare partendo dalle più lente e più frequenti, facendo molta attenzione però agli eventuali falsi positivi, le query cioè che sono presenti nel log pur non essendo scritte male o lente per loro natura.
Attenzione anche a tutti gli elementi esterni al database possono influenzare le perfomance e che possono rendere “lente” query che, in situazioni normali, non lo sarebbero state: un caso tipico è quello di un applicativo esterno che ha fatto un uso anomalo delle risorse di sistema (tempo macchina o disco) sottraendole agli altri processi, tra cui anche MySQL.
Altro caso assai frequente riguarda le query eseguite durante il backup del database: nel tempo necessario ad eseguire la copia dei dati è probabile che si generino delle slow query conseguente all’intenso I/O.
Altre query da escludere sono quelle poco frequenti, quelle eseguite per errore e quelle eseguite da un amministratore per onerose operazioni di modifica alle tabelle o complesse ricerche. E’ comunque necessario fare uso di un po’ di buon senso: se è vero che in termini generali tali query possono essere trascurate, è altrettanto vero che se una certa query eseguita una volta ogni giorno impiega un’ora non può essere tralasciata, soprattutto se in tale lasso di tempo tiene bloccate risorse (tabelle) per i thread concorrenti.
Se sul server è attiva l’opzione log_queries_not_using_indexes lo slow-log conterrà anche tutte le query che non hanno fatto uso di alcun indice, indipendentemente dalla velocità di esecuzione: questo può essere utile per individuare molte query eseguite in un tempo relativamente basso ma che comunque sarebbero migliorabili.
Se lo slow-log è di piccole dimensioni può essere controllato manualmente, ma nella maggior parte dei casi è sicuramente più comodo l’utilizzo di un programma presente in tutte le installazioni di MySQL, mysqldumpslow, che esegue l’analisi dello slow-log, producendo in output un documento di sintesi in cui le query simili sono raggruppate in base al tempo medio di esecuzione e la frequenza, ordinate a partire dalla più lenta. I parametri numerici e testuali presenti nelle query sono rispettivamente sostituiti con i simboli generici N ed S.
I file di log non sono consultabili da tutti gli utenti e, a seconda delle distribuzioni e della tipologia di installazione, è probabile che ad essi abbia accesso solo l’utente di root oppure l’utente con cui MySQL è in esecuzione (e il gruppo relativo), e questo comporta l’impossibilità dell’analisi in diverse situazioni: il caso più tipico è chi utilizza un database su una piattaforma in hosting, senza avere accesso fisicamente alla shell e alle impostazioni del server.
In tali circostanze o si riesce a replicare localmente l’intera installazione effettuando una analisi in ambiente di pre-produzione (che dovrebbe essere sempre presente, non solo per i test prestazionali), o si è costretti ad eseguire sull’applicazione la misura delle performance, magari scrivendo il tempo impiegato in un file di log che può essere consultato offline.

La funzione EXPLAIN

EXPLAIN viene utilizzata per consultare il “query optimizer”, la sezione del server che effettua le valutazioni su come rendere più veloce l’interrogazione che viene richiesta al database: è questa sezione che decide se un indice dovrà essere utilizzato, l’ordine di verifica delle condizioni ecc.
In pratica, interrogando il query optimizer si chiede a MySQL come intende sviluppare la query.
Le informazioni che fornisce EXPLAIN sono utili per molti aspetti:

  • forniscono indizi circa l’opportunità di aggiunta di alcuni indici alle tabelle;
  • se una tabella ha già degli indici utilizzati, l’output del comando aiuta a capire come vengono utilizzati dal motore;
  • se gli indici esistono ma non vengono utilizzati dal query optimizer, aiuta a scrivere meglio il codice SQL affinché la query venga eseguita beneficiando della presenza dell’indice.

L’uso di EXPLAIN è di estrema importanza per studiare i join che, se male utilizzati, hanno lo svantaggio di poter incrementare a dismisura il carico di lavoro richiesto al server: infatti, se una query che interroga una tabella di 1000 righe è scritta male, nel caso peggiore il server dovrà leggere al massimo 1000 righe; se invece viene effettuato un join di due tabelle di 1000 righe ciascuna, il caso peggiore comporta l’esame di tutte le possibili combinazioni del prodotto cartesiano tra le due tabelle, ossia 1.000.000 di righe!
EXPLAIN può aiutare a riscrivere meglio le query o modificare le tabelle in modo tale che il server risolva il join nel modo meno oneroso possibile, riducendo il numero di combinazioni da esaminare.
EXPLAIN produce una riga di output per ogni tabella referenziata dalla query. L’ordine di visualizzazione delle righe è importante: indica la sequenza che MySQL utilizzerà per considerare le tabelle nella risoluzione dei vincoli di join, indipendentemente da come sono scritte nella parte FROM.
EXPLAIN consente di analizzare qualsiasi query SELECT, ma può essere utilizzato indirettamente anche per UPDATE e DELETE scrivendo una generica SELECT * con le stesse condizioni di FROM e WHERE: solitamente le condizioni usate nelle query di modifica dei dati sono meno complesse di quelle usate in quelle di selezione, il che rende questa operazione non molto frequente.
Primo esempio: utilizzo corretto degli indici
Per il nostro primo esempio utilizziamo il database Sakila di una ipotetica videoteca, liberamente scaricabile da
http://downloads.mysql.com/docs/sakila-db.tar.gz
Delle 22 tabelle presenti ne useremo per i nostri esempi solo 3.

  • film: elenco dei film a catalogo comprensivi di titolo, genere, durata a altre informazioni
  • actor: elenco di attori (nomi di fantasia)
  • film_actor: tabella che di collegamento tra gli attori e i film in cui hanno recitato

Le tabelle sono già ottimizzate a dovere, ma per capire il funzionamento è necessario crearne di nuove non ottimizzate, senza indici:


mysql> CREATE TABLE lista_attori SELECT * FROM actor;
mysql> CREATE TABLE lista_film SELECT * FROM film;
mysql> CREATE TABLE lista_film_attori
SELECT * FROM film_actor;

L’esecuzione di CREATE TABLE … SELECT … consente infatti di copiare tutti i dati di una tabella in una nuova ricreando gli stessi campi ma senza indici.
Eseguiamo una query per trovare i nomi degli attori e i titoli dei film in cui hanno recitato la cui durata è maggiore di tre ore.


mysql> SELECT first_name, last_name, title
-> FROM lista_film, lista_attori, lista_film_attori
-> WHERE lista_film.film_id=lista_film_attori.film_id
-> AND lista_attori.actor_id=lista_film_attori.actor_id
-> AND length > 180;

La stessa query si può scrivere con la sintassi JOIN estesa, in maniera del tutto equivalente anche in termini di performance:


SELECT first_name, last_name, title
FROM
lista_film
INNER JOIN lista_film_attori ON
lista_film.film_id=lista_film_attori.film_id
INNER JOIN lista_attori ON
lista_attori.actor_id=lista_film_attori.actor_id
WHERE
length > 180;

Osserviamo il risultato che restituisce:


| first_name | last_name | title |
| PENELOPE | GUINESS | KING EVOLUTION |
| ED | CHASE | YOUNG LANGUAGE |
...
| MATTHEW | CARREY | WORST BANGER |
200 rows in set (29.02 sec)

lc59_ottimizzazione1

Notate come il tempo di esecuzione sia particolarmente alto. La velocità di esecuzione dipende anche dalla potenza di calcolo del computer, quindi potreste ottenere un risultato un po’ diverso, ma probabilmente dello stesso ordine di grandezza. Vediamo allora come migliorare la situazione.
Proviamo a fare un EXPLAIN della query appena eseguita, il cui output è presente in figura 2 nel primo passaggio:


mysql> EXPLAIN SELECT first_name, last_name, title
-> FROM lista_film, lista_attori, lista_film_attori
-> WERE lista_film.film_id=lista_film_attori.film_id
-> AND lista_attori.actor_id=lista_film_attori.actor_id
-> AND length > 180;

Notiamo anzitutto che i campi type hanno il valore ALL per tutte le tabelle. Ciò significa che per ognuna viene fatto un full scan per ogni combinazione di righe dalle tabelle precedenti. Per conoscere il numero di combinazioni totali è sufficiente moltiplicare tra loro i valori riportati in rows.


mysql> select 200*1000*5462 as prodotto_cartesiano;
| prodotto_cartesiano |
| 1092400000 |

MySQL deve costruirsi oltre 1 miliardo di possibili combinazioni, controllarle una per una per individuare alla fine le 200 righe del risultato che cerchiamo: ecco spiegata la ragione della estrema lentezza della query.
Effettuiamo una prima ottimizzazione creando due indici per consentire di risolvere più facilmente la prima condizioni di join presente nella query (notate come la forma estesa semplifichi la lettura alla ricerca delle necessità di ottimizzazione):


mysql> ALTER TABLE lista_film ADD INDEX(film_id);
Query OK, 1000 rows affected (0.07 sec)
Records: 1000 Duplicates: 0 Warnings: 0


mysql> ALTER TABLE lista_film_attori ADD INDEX(film_id);
Query OK, 5462 rows affected (0.01 sec)
Records: 5462 Duplicates: 0 Warnings: 0

Eseguiamo nuovamente EXPLAIN, il cui output è presente sempre in figura 2 ma nel secondo passaggio.
Notiamo che la situazione è considerevolmente migliorata per la tabella lista_film_attori per la quale è possibile utilizzare l’indice costruito su film_id come ci dice il campo key. Per ogni combinazione di righe dalle tabelle precedenti la tabella in questione mette in gioco 5 righe (rows) e risolve il join con lista_film usando la colonna film_id (ref).


mysql> select 200*1000*5 as prodotto_cartesiano;
| prodotto_cartesiano |
| 1000000 |

Abbiamo ridotto il numero delle combinazioni di tre ordini di grandezza, da oltre un miliardo a un milione. La query eseguita dopo questa prima ottimizzazione impiega oltre un decimo del tempo. Proseguiamo con l’ottimizzazione creando gli indici per agevolare l’altra condizione di join.


mysql> ALTER TABLE lista_attori ADD INDEX(actor_id);
Query OK, 200 rows affected (0.00 sec)
Records: 200 Duplicates: 0 Warnings: 0


mysql> ALTER TABLE lista_film_attori ADD INDEX(actor_id);
Query OK, 5462 rows affected (0.02 sec)
Records: 5462 Duplicates: 0 Warnings: 0

Nel terzo passaggio vediamo come è ulteriormente variato l’output di EXPLAIN.
Siamo ora in una situazione già accettabile: le combinazioni sono scese a 5000 (1000*5*1) con due tabelle su tre in grado di sfruttare un indice.
Notiamo anche che l’ordine delle tabelle è variato e quanto mostrato da EXPLAIN è l’esatta sequenza con cui “query optimizer” aprirà le tabelle e risolverà i join. L’ordine scritto nel FROM non ha alcuna rilevanza: la scelta della sequenza più opportuna è operata in base a quella che è in grado di fornire il risultato migliore.
In questo caso vi è la necessità di effettuare il full scan di lista_film, e viene eseguito come prima operazione per evitare di doverlo ripetere più volte se fosse posticipato nella esecuzione della query, ragion per cui lista_film è la prima tabella considerata.
La necessità di effettuare il full scan è data dalla presenza della condizione sul campo length (length > 180) che non è un campo indicizzato. Proviamo allora a definire un indice anche per esso e vediamo cosa succede.


mysql> ALTER TABLE lista_film ADD INDEX(length);
Query OK, 1000 rows affected (0.02 sec)
Records: 1000 Duplicates: 0 Warnings: 0

L’output della nuova EXPLAIN è indicato come quarto passaggio. Ora anche la prima tabella può sfruttare l’indice e il valore range del campo ref indica che solo un certo intervallo dell’indice viene letto, non tutto.
Calcoliamo la complessità finale della query:


mysql> select 46*5*1 as prodotto_cartesiano;
| prodotto_cartesiano |
| 230 |

Ora eseguiamola nuovamente dopo le ottimizzazioni:


mysql> SELECT first_name, last_name, title
-> FROM lista_film, lista_attori, lista_film_attori
-> WHERE lista_film.film_id=lista_film_attori.film_id
-> AND lista_attori.actor_id=lista_film_attori.actor_id
-> AND length > 180;
| first_name | last_name | title |
| PENELOPE | GUINESS | KING EVOLUTION |
| ED | CHASE | YOUNG LANGUAGE |
...
| MATTHEW | CARREY | WORST BANGER |
200 rows in set (00.01 sec)

Il tempo di esecuzione si è decisamente ridotto!

Un nuovo esempio: riscrivere una query

Supponiamo che siano state inserite delle durate errate nel campo length della tabella film, e il valore corretto sarebbe il 10% in più rispetto ai valori presenti nel database.
Prima di modificare il campo, però, cerchiamo quali sarebbero i film la cui durata sarà superiore alle 3 ore (180 minuti).
Creiamo un indice sul campo length e analizziamo una semplice query:


mysql> ALTER TABLE film ADD INDEX(length);
Query OK, 1000 rows affected (0.14 sec)
Records: 1000 Duplicates: 0 Warnings: 0


mysql> EXPLAIN SELECT title FROM film
WHERE 1.1*length > 180\G
********************* 1. row *********************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 927
Extra: Using where

C’è da chiedersi perché non venga utilizzato l’indice definito sulla colonna length. La spiegazione è oltremodo semplice: quando una condizione è espressa in termini di una funzione o di una operazione algebrica applicata ad un campo indicizzato, MySQL non può sfruttare l’indice in quanto non conosce a priori il valore assunto dall’espressione o dalla funzione. Il valore deve quindi essere calcolato riga per riga, obbligando un full scan della tabella, come EXPLAIN correttamente indica.


mysql> EXPLAIN SELECT title FROM film
WHERE length > 180/1.1\G
********************* 1. row *********************
id: 1
select_type: SIMPLE
table: film
type: range
possible_keys: length,length_2
key: length
key_len: 3
ref: NULL
rows: 155
Extra: Using where

Provando a scrivere la query con una condizione espressa in un modo diverso ma equivalente, si verifica come l’indice venga correttamente utilizzato.
Forzare l’ordine delle tabelle
Nella maggior parte dei casi le scelte effettuare dal query optimizer per quanto riguarda l’ordine di lettura delle tabelle sono quelle ottimali, ma può però capitare che non lo siano e si voglia costringere MySQL ad operare una scelta diversa: è quindi sufficiente aggiungere alla prima SELECT il modificatore STRAIGHT_JOIN, che farà in modo che l’ordine considerato sarà quello specificato in FROM.
Fortunatamente non capiterà spesso di andare contro le scelte di MySQL, ma come esercizio si può provare a forzare l’ordine delle tabelle per verificare come cambino le performance di una query.

Aggiornare le statistiche

Abbiamo citato il fatto che il valore di rows sia una stima calcolata in base alle statistiche sulla distribuzione degli indici e non il numero reale delle righe selezionate. Non è scopo di questo articolo spiegare gli internal di questa valutazione, ma ciò chi ci preme sottolineare è che i valori utilizzati per le ottimizzazioni devono essere il più aggiornati possibile: è quindi consigliabile eseguire di tanto in tanto il comando


mysql> ANALYZE TABLE nome_tabella;

che ha il compito specifico di effettuare un refresh delle informazioni necessarie.
Non esiste una regola per decidere la frequenza dell’esecuzione, molto dipende dalla quantità di modifiche dei campi indicizzati. Per sicurezza è utile eseguirlo su tutte le tabelle con cadenza giornaliera o settimanale a seconda dei casi (basta affidarsi a cron) e comunque eseguirlo sistematicamente tutte le volte che si fanno modifiche di massa di valori associati ad indice, o dopo aver cancellato grosse parti di una tabella. Anche il comando che segue può servire allo scopo:


mysql> OPTIMIZE TABLE nome_tabella;

Anche OPTIMIZE TABLE aggiorna le statistiche di distribuzione degli indici, ma in più esegue altre operazioni (ad esempio compattare le tabelle per recuperare lo spazio vuoto) e potrebbe risultare più lento.

Conclusioni

Vi abbiamo mostrato come identificare le query più lente sfruttando lo slow-log: in seguito l’uso di EXPLAIN ha mostrato come si possano analizzare tali query al fine di creare gli indici necessari per ridurre il numero di combinazioni di righe e, nel caso in cui tali indici non venissero utilizzati, modificare la struttura della query per permettere a MySQL una migliore ottimizzazione della interrogazione.
Tutto ciò a vantaggio dei tempi di risposta e del fatto che avrete un DBMS in grado di poter assolvere a nuovi compiti.
A volte purtroppo capita anche il caso in cui, nonostante tutte le possibili ottimizzazioni, il sistema risulti ancora troppo lento. Solo in tal caso non resta altro da fare che potenziare l’hardware o pensare a soluzioni basate sulla replicazione o MySQL Cluster a seconda dei casi, ma qua il discorso diventa sicuramente più complesso e sarà oggetto di ulteriori articoli nei prossimi numeri.

Riquadro 1: I campi di una EXPLAIN SELECT

id: valore numerico progressivo che indica la SELECT a cui si fa riferimento; se non sono presenti query annidate o UNION varrà sempre 1.
select_type: categorizza il tipo di SELECT; se non si hanno query annidate o UNION varrà sempre SIMPLE.
table: è il nome della tabella cui le informazioni della riga fanno riferimento.
type: indica il tipo di join. Il suo valore è una misura dell’efficienza con cui MySQL risolve una condizione di join (vedi riquadro 2 per tutti i possibili valori).
possible_keys: indica quali sono gli indici della tabella che MySQL può considerare per identificare la righe che soddisfano la query. Può essere una lista di uno o più valori, o può valere NULL se non ci sono indici o se nessuno degli indici può essere usato.
key: indica il nome dell’indice che “query optimizer” ha deciso di utilizzare. Se il valore è NULL vuol dire che nessun indice è stato usato; questo capita ovviamente se non vi sono indici oppure se optimizer ritiene che uno scan della tabella sia più veloce (tipico di quando il numero delle righe selezionate è relativamente grande rispetto al numero totale di righe della tabella)
key_len: indica quanti byte dell’indice sono usati. E’ un’informazione utile solo nei casi in cui vi sia un indice multiplo, costruito cioè con l’unione di più colonne. Per gli scopi dell’articolo non ci interessa approfondirlo ulteriormente.
ref: indica quali colonne della tabella precedente sono usate per risolvere la condizione di join con la tabella attuale. Vale NULL nel caso della prima tabella.
rows: è un valore numerico che rappresenta il numero di righe messe in gioco dalla tabella. Attenzione però, si tratta solo di una stima calcolata da optimizer in base alle statistiche interne sugli indici, non è il numero delle righe realmente selezionate. Per fortuna ciò che conta non è il valore puntuale ma l’ordine di grandezza di tale numero, che nella quasi totalità dei casi è attendibile. Il valore “vero” lo si può ottenere solo eseguendo la query, cosa che EXPLAIN non fa.
Extra: fornisce informazioni addizionali sul join (vedi riquadro 3 per tutti i possibili valori).

Riquadro 2: I valori del campo Type presente nella EXPLAIN

system: la tabella ha esattamente una riga.
const: la tabella ha una sola riga che soddisfa alle condizioni di join. E’ simile a system ma con la differenza che la tabella può avere molte altre righe.
eq_ref: è letta una sola riga dalla tabella corrente per ogni combinazione di righe dalle tabelle precedenti. Tipico dei join in cui MySQL può utilizzare una primary key per identificare le righe di una tabella.
ref: sono lette diverse righe per ogni combinazione di righe dalle tabelle precedenti. Simile a eq_ref, ma può capitare quando viene usato un indice multiplo o quando non viene utilizzato un indice per intero (solo la parte sinistra).
ref_or_null: simile a ref, ma in più MySQL deve cercare righe contenenti NULL.
range: l’indice è usato per selezionare le righe che cadono in un certo intervallo di valori. Tipico di quando vengono usate condizioni di disuguaglianza, ad esempio id<10.
index: MySQL esegue un full scan dell’indice. Non è un caso piacevole ma sempre meglio di un full scan della tabella. Un indice è più veloce da leggere dei dati reali in quanto è ordinato, solitamente più piccolo dei dati e spesso si trova per buona parte in memoria.
ALL: MySQL esegue un full scan della tabella da disco. E’ la situazione peggiore, soprattutto se si riferisce ad una tabella che non sia la prima: deve essere eseguito il full scan per ogni combinazione di righe dalle tabelle precedenti e le performance peggiorano esponenzialmente. Assolutamente da evitare, specialmente quando la dimensione delle tabelle non è trascurabile.

Riquadro 3: I valori del campo Extra presente nella EXPLAIN

Ecco i valori possibili del campo Extra: quelli con un + identificano valori “buoni”, quelli con – valori “cattivi”.

  • +Using index: MySQL può ottimizzare la query leggendo i valori dall’indice senza dover leggere i corrispondenti dati dalla tabella fisica. Questa ottimizzazione è possibile quando ad esempio vengono selezionate solo colonne che sono indicizzate.
  • +Where used: MySQL usa le condizioni espresse in WHERE per identificare le righe che soddisfano la query. Senza tali condizioni si sarebbero ottenute tutte le righe della tabella.
  • +Distinct: MySQL legge una singola riga dalla tabella per ogni combinazione di righe dalle tabelle precedenti.
  • +Not exists: MySQL riesce ad ottimizzare una condizione di LEFT JOIN non considerando alcune righe.
  • -Using filesort: Le righe che soddisfano la query devono essere ordinate. E’ un passo ulteriore di computazione.
  • -Using temporary: Deve essere creata una tabella temporanea per poter processare la query.
  • -Range checked for each record: MySQL non può determinare in anticipo quale indice utilizzare. Per ogni combinazione di righe dalle tabelle precedenti controlla gli indici della tabella corrente per cercare il migliore da utilizzare. Non è una buona situazione, ma meglio che non usare gli indici del tutto.

Dov’è il NULL?

Nell’esaminare dati creati da altri, o dati creati da noi stessi in tempi lontani, spesso accade di volerne ottimizzare la struttura, per migliorare l’efficienza. Un caso frequente è la ricerca di campi di chiavi esterne con valori NULL, per poter normalizzare le dipendenze da altre tabelle.

Ma comunque i campi NULL possono, quando usati in maniera irragionevole, influenzare negativamente la precisione o l’efficienza di una query. Quindi succede spesso che un programmatore si chieda: “in quali campi di questa tabella c’è almeno un NULL?”

Se la tabella è piccola, basta uno sguardo.


mysql> select * from persone;
+----+--------+---------+--------+
| id | nome | cognome | id_dep |
+----+--------+---------+--------+
| 1 | Fabio | Rossi | NULL |
| 2 | Mario | Bianchi | NULL |
| 3 | Carlo | Verdi | NULL |
| 4 | Gino | Neri | NULL |
| 5 | Ugo | Rossi | 2 |
| 6 | Piero | Verdi | 1 |
| 7 | Fulvio | Neri | NULL |
| 8 | NULL | Rossi | NULL |
| 9 | NULL | Bianchi | NULL |
+----+--------+---------+--------+
9 rows in set (0.02 sec)

Ma se la tabella ha decine di colonne e centinaia di record, il compito di esaminarla a mano è proibitivo. Tuttavia, possiamo agevolarci il compito con qualche query ad hoc. Sappiamo che la funzione COUNT restituisce un conteggio dell’elemento richiesto. Nella tabella sopra una query


mysql> SELECT count(id_dep) FROM persone;

restituisce 2. Questo ci dice che il conteggio salta i valori NULL. Se diamo alla funzione l’elemento ‘*’, però, ci viene restituito un conteggio dei record, a prescindere dal contenuto. Verifichiamo questo fatto.


mysql> select * from tuttinull;
+------+------+
| id | nome |
+------+------+
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
+------+------+
4 rows in set (0.01 sec)

Questa tabella ha quattro record, con NULL in tutti i campi.


mysql> select count(*) from tuttinull;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.01 sec)

Il conteggio, come annunciato, è riferito ai record. Quindi possiamo sfruttare questo fatto per vedere quali campi contengono un NULL, esaminando il conteggio dei singoli campi, e comparandolo al conteggio totale:


mysql> select count(*), count(nome), count(cognome), count(id_dep) from persone;
+----------+-------------+----------------+---------------+
| count(*) | count(nome) | count(cognome) | count(id_dep) |
+----------+-------------+----------------+---------------+
| 9 | 7 | 9 | 2 |
+----------+-------------+----------------+---------------+
1 row in set (0.02 sec)

Questo ci dice che ci sono 7 nomi su 9 record, 9 cognomi su 9, e 2 id_dep su 9. Ancora meglio, possiamo chiedere al database di fare il calcolo per noi, e darci direttamente il numero di record mancanti:


mysql> select count(*),
-> count(nome) as nome,
-> count(*) - count(nome) as `NULL nome`,
-> count(cognome) as cognome,
-> count(*) - count(cognome) as `NULL cognome`,
-> count(id_dep) as id_dep,
-> count(*) - count(id_dep) as `NULL id_dep`
-> from persone;
+----------+------+-----------+---------+--------------+--------+-------------+
| count(*) | nome | NULL nome | cognome | NULL cognome | id_dep | NULL id_dep |
+----------+------+-----------+---------+--------------+--------+-------------+
| 9 | 7 | 2 | 9 | 0 | 2 | 7 |
+----------+------+-----------+---------+--------------+--------+-------------+
1 row in set (0.02 sec)

Questo metodo funziona, ma se abbiamo una tabella con tanti campi, non è più un metodo efficiente. È possibile però ottenere lo stesso risultato con un approccio programmatico, come questo esempio in Perl dimostra:


#!/usr/bin/perl -w
use strict;
use DBI;


my $dbh = DBI->connect('dbi:mysql:test', 'utente','password')
or die "problema di connessione\n";
my $colonne = $dbh->selectcol_arrayref(qq{describe persone})
or die "colonne non trovate";


my $query = qq{select count(*), }
. join(",", map {qq{count(`$_`) as `$_`,
count(*) - count(`$_`) as `NULL $_`} } @$colonne)
. qq{ from persone};


my $sth = $dbh->prepare($query);
$sth->execute() or die "problema di esecuzione\n";


my $row = $sth->fetchrow_hashref;
printf "%-28s => %s \n", $_, $row->{$_} for @{$sth->{NAME}};
$sth->finish();

Col risultato:


count(*) => 9
id => 9
NULL id => 0
nome => 7
NULL nome => 2
cognome => 9
NULL cognome => 0
id_dep => 2
NULL id_dep => 7

Come incrementare un campo con chiave univoca

La domanda viene da uno scorato frequentatore di una mail list: Ho una tabella InnoDB con una chiave univoca sul campo numerico ID. Devo incrementare il campo di cinque unità, ma i miei tentativi non hanno dato frutto. Infatti, se provo a usare la formula SET ID = ID + 5, ottengo un errore di violazione di chiave. Questa è l’istruzione che ho tentato


BEGIN;
UPDATE nometabella SET ID = ID + 1;
COMMIT;

Credevo che InnoDB avrebbe contenuto l’errore per ogni singola violazione, e aggiornato tutta la tabella, e solo dopo avrebbe considerato che in realtà non c’è conflitto perché dopo che tutti i record sono stati aggiornati si ritorna a valori univoci. La mia tabella ha 12000 record. Non vorrei essere costretto a usare 12.000 istruzioni di UPDATE. Cosa posso fare?

Questa è stata la mia risposta:

InnoDB fa il suo mestiere, fermandosi al primo errore. Il meccanismo transazionale fa sì che la serie di istruzioni venga interrotta al primo errore, dando la possibilità al programmatore di chiamare un ROLLBACK. Quindi, al primo record, ci sarà un conflitto di valori che renderà impossibile l’aggiornamento.

Tuttavia, esiste una soluzione elegante, senza dover inviare 12.000 richieste. Basta chiedere l’aggiornamento con ordinamento decrescente:


UPDATE nometabella SET ID = ID + 1 ORDER BY ID DESC;

In questo modo, il motore aggiornerà la tabella in ordine decrescente di ID, garantendo che non ci saranno conflitti. Se invece si volesse decrementare il valore, basta usare l’ordinamento crescente (omettendo la clausola DESC).

Ordinare per condizioni trovate

FN mi ha proposto un problema interessante. Deve ricercare un archivio usando tre condizioni unite dall’operatore OR, e gli serve che il risultato sia ordinato in modo che i record che rispondono alla prima condizione vengano per primi, poi quelli della seconda condizione, e infine quelli della terza.

Lui si è arenato alla semplice query senza ordinamento.


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE
nome LIKE 'M%'
OR DDN < '1960-01-01' OR stipendio > 6000;
+---------+-----------+------------+
| nome | stipendio | DDN |
+---------+-----------+------------+
| Mario | 5100 | 1956-10-24 |
| Marco | 5600 | 1943-03-09 |
| John | 5550 | 1955-04-02 |
| Maria | 5700 | 1979-12-11 |
| Colette | 6100 | 1960-08-14 |
| Antonio | 6200 | 1968-12-08 |
| Nina | 6100 | 1967-05-24 |
+---------+-----------+------------+
7 rows in set (0.00 sec)

Così a prima vista io ho tirato fuori una soluzione che sembra fare al caso suo. Basta ordinare con una funzione CASE che assegna un valore a ogni condizione.


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE nome like 'M%'
OR DDN < '1960-01-01' OR stipendio > 6000
ORDER BY
CASE
WHEN nome LIKE 'M%' THEN 1
WHEN DDN < '1960-01-01' THEN 2 WHEN stipendio > 6000 THEN 3
END;
+---------+-----------+------------+
| nome | stipendio | DDN |
+---------+-----------+------------+
| Mario | 5100 | 1956-10-24 |
| Marco | 5600 | 1943-03-09 |
| Maria | 5700 | 1979-12-11 |
| John | 5550 | 1955-04-02 |
| Colette | 6100 | 1960-08-14 |
| Antonio | 6200 | 1968-12-08 |
| Nina | 6100 | 1967-05-24 |
+---------+-----------+------------+
7 rows in set (0.01 sec)

Il brutto di questo approccio, mi fa gentilmente notare FN, è che le condizioni vengono valutate due volte, e anche se questo avviene solo per i record trovati (la clausola ORDER BY viene eseguita dopo che i record sono stati filtrati) è sempre uno spreco e un rallentamento.

Pertanto, concentrandomi sull’efficienza, ho trovato un’altra soluzione:


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE nome like 'M%'
UNION
SELECT nome, stipendio, DDN
FROM impiegati
WHERE DDN < '1960-01-01' UNION SELECT nome, stipendio, DDN FROM impiegati WHERE stipendio > 6000 ;

Questa query risolve il problema dell’inefficienza dell’operatore OR con campi diversi, e inoltre risolve anche il problema principale dell’ordinamento. Benché teoricamente l’ordine del risultato di una query sia imprevedibile, il modo in cui MySQL organizza i dati in una UNION fa sì che l’ordinamento sia quello voluto. Si ricordi che una UNION senza la clausola ALL elimina i record duplicati, per cui se un record viene ritrovato grazie alla prima condizione, verrà scartato quando sono valutate la seconda e la terza.
Risultato ottenuto, e ottimizzato allo stesso tempo!

Creare un mysqldump con LIMIT

Potrebbe essere necessario a volte creare un campione dei dati di uno o più database, per esempio per poter chiedere assistenza a un consulente o per poter dare assistenza a un utente.

In questi casi, il classico strumento mysqldump non aiuta, perché fra le sue varie utilità non c’è quella di impostare una clausola LIMIT. È vero che si può filtrare con WHERE, ma questo è valido solo se si vogliono estrarre i dati di una tabella alla volta e anche in questo caso la clausola WHERE dovrebbe essere cambiata volta per volta.

Esistono alternative e la più spiccia è quella di creare un programma di estrazione in Perl, per poter prendere con un colpo solo la struttura e N record di tutte le tabelle di tutti i database.

Il semplice script che segue è una semplificazione (ma perfettamente funzionante) di uno strumento analogo che viene usato in StarData per acquisire campioni di dati dagli utenti.

Per limitare la portata dell’output, qualora non si volesse fornire la lista di tutti i database, è sufficiente eseguire questo script con i privilegi di un utente abilitato solo alla visione di alcuni database.


#!/usr/bin/perl -w
use strict;
use DBI;


my $host = 'localhost';
my $user = 'gmax'; # cambiare
my $password = ''; # cambiare
my $port = "3306";
my $limit = 1;
my $db = "test";
my $DSN = "DBI:mysql:$db;host=$host;port=$port;"
. "mysql_read_default_file=$ENV{HOME}/.my.cnf";


my $dbh =
DBI->connect( $DSN, $user, $password, { RaiseError => 1,
PrintError => 1 } )
or die "$DBI::errstr\n";


for my $db (@{$dbh->selectcol_arrayref("SHOW DATABASES})")
{
print STDERR "# ------ DATABASE $db\n";
print "# ------ DATABASE $db\n";
print "CREATE DATABASE IF NOT EXISTS $db;\n";
print "USE $db;\n";
for my $table (@{$dbh->selectcol_arrayref("SHOW TABLES FROM `$db`")})
{
print "# TABELLA ($db) $table\n";
print STDERR "# TABELLA ($db) $table\n";
my $sth = $dbh->prepare(qq{SHOW CREATE TABLE `$db`.`$table`});
$sth->execute ;
my ( $null, $create ) = $sth->fetchrow_array;
$sth->finish;
$create =~ s/(?<=CREATE TABLE)/ IF NOT EXISTS /; print " $create;\n"; $sth = $dbh->prepare(
"SELECT * FROM `$db`.`$table` LIMIT $limit");
$sth->execute;
while ( my $rec = $sth->fetchrow_arrayref )
{
print qq{INSERT INTO `$db`.`$table` (};
print join ",", map { "`$_`" } @{ $sth->NAME} };
print ") VALUES (";
print join ( ",", map( { $dbh->quote($_) } @$rec ) );
print ");\n";
}
}
}