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?

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