Usare bene MySQL. Consigli di un sistemista ad uno sviluppatore.

Uno degli aspetti più frustranti nell’essere sistemisti è quello di doversi sporcare le mani in lavori che dovrebbero fare altre figure professionali.

Può succedere infatti che dopo aver consegnato un server ben dimensionato sull’hardware e ben configurato a livello software che il cliente si lamenti delle performance assolutamente inadeguate dell’applicativo che ci gira sopra.

Sia esso possa essere un CMS custom, che un CRM custom, che qualsiasi altra applicazione Web commissionata e sviluppata su misura delle esigenze del cliente, la sostanza è che il software non gira come dovrebbe girare.

Spesso dopo le iniziali e pacate lamentele, all’aggravarsi e protrarsi della situazione si arriva ad esclamare con tono seccato che “Non funziona niente” spesso con epiteti, tono e terminologie da bar del porto.

Dopo una normale e pacata risposta in cui si evidenzia come quella configurazione sia più che adeguata per le necessità del cliente, si arriva all’invito del cliente a confrontarsi con lo sviluppatore del software perchè qualcosa forse è sfuggito di mano ed essendo un’applicazione custom senza casi specifici e documentati (in quanto il software non essendo conosciuto ne tanto meno diffuso) potrebbe presentare criticità a noi ovviamente sconosciute e non imputabili.

Ovviamente per il programmatore, nel 99% dei casi di questo tipo, il problema non è mai nel suo software, ma sempre attribuibile al sistemista che non sa configurare un server.

Ed ecco qui, la più temuta delle battaglie : Sistemista contro sviluppatore.

Il sistemista di norma è una cintura nera decimo dan, di UNIX, ha molto probabilmente un passato da sviluppatore e da DBA. E’ il potenziale Bruce Lee in grado di stenderti con un colpo che preferisce SEMPRE sottrarsi alla sfida, per mancanza di tempo ma sopratutto competenze.

E sia chiaro, che per competenze, non si intendono le competenze teoriche e pratiche o accademiche di poter analizzare brillantemente il problema e conseguente di risolverlo, ma semplicemente non è di sua competenza, ovvero che quel compito è stato affidato ad un altra stimata figura di un’altra azienda che viene profumatamente pagata per progettare e sviluppare software funzionante e ben ottimizzato.

Peccato che nella triste vita dell’informatico e sopratutto del sistemista, lo scaricabarile è sempre dietro l’angolo e quindi se non accetti la sfida all’ultimo sangue sarai sempre etichettato come colui che non sa fare il proprio lavoro, nonchè perderai il cliente che andrà su qualche altra azienda venditrice di fumo fino a quando qualche sistemista cintura nera deciderà di sporcarsi le mani e dimostrare una volta per tutte che tutti i problemi sono derivati da una pessima implementazione software.

Non solo avrai salvato la vita al tuo cliente che si sarebbe trovato con un software inutilizzabile, ma anche al programmatore che grazie ai tuoi consigli e ai tuoi interventi avrà goduto di una miglioria al suo software che potrà magari rivendere anche ad altri clienti.

La verità ? Non sapete usare MySQL e tantomeno SQL in generale.

Uno degli aspetti comuni riscontrabili in moltissimi sviluppatori LAMP (Linux Apache MySQL PHP) è quello di non saper usare MySQL. Pur essendo arrivato alla versione 8, con un set di feature e di funzionalità ormai da far quasi invidia a SQL Server della Microsoft, PostgreSQL o Oracle, gran parte degli sviluppatori continuano ad utilizzarlo allo stesso modo di come si usava la versione 3.23, ovvero senza vincoli di integrità referenziale, senza stored procedure, stored functions, triggers, views o transazioni.

Va ricordato infatti ai programmatrotoli ignoranti che sebbene siate rimasti all’età della pietra utilizzando MySQL allo stesso modo della 3.23 che attualmente il motore MyISAM di allora che era assolutamente inadeguato per un utilizzo professionale e moderno è stato rimpiazzato da InnoDB e i relativi fork di cui menzionamo sopratutto XtraDB di Percona Server che rendono MySQL un DBMS relazionale estremamente potente.

Personalmente non abbiamo nulla contro MySQL o MariaDB, ma attualmente abbiamo uniformato praticamente tutti i nostri server con Percona Server che ha anche tool accessori come le Percona Utilities o Percona Xtrabackup per fare backup a caldo in maniera molto più veloce, sicura, consistente ed efficente del solito banale mysqldump.

Per fare un esempio spicciolo, un programmatore oggi è convinto che un DMBS come MySQL sia una semplice cassettiera in cui depositare e recuperare dei dati. In parole povere utilizzano un DBMS potentissimo (e dunque pesante) come se fosse quasi un DB NO-SQL e dunque leggero.

Un DBMS come MySQL oggi è invece è più paragonabile ad una evoluta linea di produzione industriale che ad una banalissima cassettiera. Per intenderci insomma quelle grosse catene di produzione di caramelle, dove messi in ingresso 3 ingredienti principali e un colorante, alla fine della giostra escono pacchetti di orsetti gommosi, tagliati, mischiati, imballati e pronti da caricare sui camion per le consegne.

Ad esempio pochi sviluppatori LAMP sanno che MySQL è potentissimo e permette di gestire gran parte delle operazioni che si fanno a livello applicativo, direttamente a livello DB visto che MySQL stesso dispone di un linguaggio di programmazione interno.

Vediamo alcune delle principali mancanze o delle bad practices di chi sviluppa applicativi Web utilizzando MySQL.

La seguente lettura potrà trasformare un qualsiasi peracottaio ex disoccupato riciclatosi sviluppatore Web in un buon sviluppatore e magari anche un buon DBA qualora si prosegua su quella strada.

1. Chiavi Esterne e integrità referenziale

Il programmatrotolo è convinto che esista solo PHP, che se si vuol cancellare dei figli di un nodo, lo si possa cancellare solo tramite lato applicativo, interrogando prima i risultati da cancellare, poi cancellandoli uno ad uno e poi successivamente risalire sulla tabella padre e cancellare l’elemento. Così come allo stesso modo sono convinti che se si volesse utilizzare una formula matematica per un calcolo complesso questo possa essere eseguito solo da PHP che produrrà un risultato che sarà poi scritto nel DB.

Non sanno ad esempio che per cancellare un artista e tutti i suoi album da un ipotetica base di dati di archivio musicale si possa usare l’azione ON CASCADE DELETE che permette di effettuare una cancellazione referenziata al cancellamento della foreign key corrispondente senza andare a cancellare singolarmente i singoli valori.

Nell’ambito dei RDBMS, l’integrità referenziale è un vincolo di integrità di tipo interrelazionale ovvero una proprietà dei dati che, se soddisfatta, richiede che ogni valore di un attributo (colonna) di una relazione (tabella) esista come valore di un altro attributo in un’altra relazione.

Meno formalmente, nei database relazionali, affinché sia rispettata l’integrità referenziale, ogni campo in una tabella che sia stato dichiarato come foreign key può contenere solo valori della chiave primaria o chiave candidata di una tabella “madre” relazionata.

Per esempio, cancellare un record che contiene un valore a cui fa riferimento una foreign key di un’altra tabella violerebbe l’integrità relazionale. Alcuni RDBMS possono garantire l’integrità relazionale, o cancellando le rispettive righe di foreign key, oppure interrompendo l’operazione e non effettuando la cancellazione.

2. Stored Procedure

Le stored procedures (procedure memorizzate) sono un’altra delle caratteristiche la cui assenza è stata a lungo sottolineata dai detrattori di MySQL: con la versione 5.0 si è finalmente posto rimedio a questa assenza.

Una stored procedure è un insieme di istruzioni SQL che vengono memorizzate nel server con un nome che le identifica; tale nome consente in seguito di rieseguire l’insieme di istruzioni facendo semplicemente riferimento ad esso.
Ogni procedura può avere uno o più parametri, ciascuno dei quali è formato da un nome, un tipo di dato e l’indicazione se trattasi di parametro di input, di output o entrambi. Se manca l’indicazione, il parametro è considerato di input.

3. Stored Functions

Le stored functions sono simili alle stored procedures, ma hanno uno scopo più semplice, cioè quello di definire vere e proprie funzioni, come quelle già fornite da MySQL. Esse restituiscono un valore, e non possono quindi restituire resultset, al contrario delle stored procedures. Nelle versioni di MySQL precedenti alla 5.0 esistevano le “user-defined functions”, che venivano memorizzate esternamente al server. Ora queste funzioni sono ancora supportate, ma è sicuramente consigliabile utilizzare le nuove stored functions.

4. Triggers

I triggers sono oggetti associati a tabelle, che vengono attivati nel momento in cui un determinato evento si verifica relativamente a quella tabella. Sono stati introdotti a partire da MySQL 5.0.2.

Quando definiamo un trigger, stabiliamo per quale evento deve essere attivato (inserimento di righe, modifiche o cancellazioni) e se deve essere eseguito prima o dopo tale evento; avremo quindi i seguenti tipi di trigger:

BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE
AFTER INSERT
AFTER UPDATE
AFTER DELETE

Il trigger stabilirà un’istruzione (o una serie di istruzioni) che saranno eseguite per ogni riga interessata dall’evento. In parole povere se voglio azionare qualcosa al susseguirsi di un evento definito, il trigger non farà altro che azionare qualcosa al verificarsi dell’evento definito. Ad esempio se volessimo aggiornare il campo del totale venduto per un agente di vendita nel mese corrente, potremmo azionare un trigger che scatti all’inserimento di un nuovo valore nella tabella ordini che faccia la somma di tutti gli ordini del mese e scrivi il valore nella tabella totaledelmese. Questo darebbe la possibilità di avere sempre un dato precalcolato che si aggiorna ad ogni nuovo ordine, piuttosto che avere select che nel backend dell’agente ricalcoli ogni volta il valore facendo la somma di tutte le vendite del mese.

5. Views o Viste

Le Views (termine tradotto in Italiano letteralmente con la parola “Viste”), sono delle tabelle temporanee, che si comportano come delle vere e proprie tabelle ma che in realtà non contengono “fisicamente” dati; esattamente come una qualsiasi altra tabella una View è formata da righe e colonne che però in questo caso sono il risultato di una query che viene archiviata come se fosse un oggetto.

Attraverso la creazione di una View non si fa altro che memorizzare il subset di una tabella esistente attraverso un processo di interrogazione del DBMS, questo subset permette di riferirsi alle proprietà e ai metodi dell’oggetto archiviato nello stesso modo in cui è possibile operare con una qualsiasi altra tabella.

6. Transazioni ACID

L’uso delle transazioni permette di “consolidare” le modifiche alla base dati solo in un momento ben preciso: dal momento in cui avviamo una transazione, gli aggiornamenti rimangono sospesi (e invisibili ad altri utenti) fino a quando non li confermiamo (commit); in alternativa alla conferma è possibile annullarli (rollback). In parole povere il concetto è o tutto o niente. Se hai 100 record da inserire magari in una comanda di un ristorante e al 90esimo si blocca l’inserimento perchè la Wifi non va, allora il sistema farà il rollback di tutta la transazione andando ad annullare i 90 inserimenti perchè non completi ai 100 inserimenti totali che avrebbero dovuto essere.

E non dimentichiamoci delle performance.

Se volessimo parlare di velocità di esecuzione delle query, possiamo affermare come sempre meno sviluppatori LAMP sappiano progettare correttamente una base di dati ed implementare un uso corretto degli indici che sono assolutamente vitali se si vuol ottenere velocità nell’esecuzione delle query stesse.

In parole povere molto spesso applicativi lenti che arrivano al timeout della connessione e persino alla saturazione delle connessioni massime disponibili hanno come unica causa la mancanza di indici sulle tabelle.

Un indice database è una struttura di dati che migliora la velocità delle operazioni in una tabella.

Ogni volta che un’applicazione web invia una query a un database, il database cercherà in tutte le righe della tua tabella per trovare quella che combacia con la tua richiesta. Quando le tabelle del tuo database aumentano, un numero maggiore di righe deve essere ispezionato ogni volta e questo può rallentare le prestazioni del database e quindi dell’applicazione.

Gli indici MySQL risolvono questo problema prendendo i dati da una colonna nella tua tabella e la archiviano alfabeticamente in un punto separato chiamato indice.

Basta che funziona ? No. Deve essere veloce.

Insomma, a corto di discorsi, sembra che il problema principale di chi sviluppa LAMP è che ci si concentra più sulla programmazione PHP che nella progettazione e ottimizzazione della base di dati. Senza una chiara formazione di tipo accademica sembra sia comune che lo sviluppatore si limiti a testare il software localmente su dataset molto piccoli e poca concorrenza. Va bene dire che il software funziona e che la query restituiscano i risultati corretti, ma un conto è testare il software su 100 record dimostrativi con un solo utente connesso, un conto è lavorare su dataset di decine di milioni di record con 100 operatori connessi in tempo reale.