Uso degli indici in MySQL

Questa è la storia del seguente grafico:

Il grafico rappresenta l'andamento del carico di una CPU di un MySQL negli ultimi 7 giorni. Andiamo però per ordine. In tutti i libri in cui si fa anche solo accenno alle basi di dati, in tutti i siti web dove si confrontano le prestazioni del vari database server, persino in applicazioni web "da banco", si fa sempre riferimento agli Indici.

L'indice in un database ha più o meno lo stesso significato che può avere per noi: una tabella per la ricerca rapida di argomenti. Ovviamente l'indice (di un libro nel nostro caso) non può contenere tutti i dettagli contenuti all'interno dei capitoli a cui si fa riferimento, ma l'informazione che di solito può determinare se il capitolo è di interesse o meno. Una volta trovato nell'indice ciò che interessa, si legge il numero della pagina (il puntatore ad essa) e si va alla relativa pagina a leggere il capitolo.

Nel database chi lo progetta decide di creare uno o più indici per le colonne che ritiene saranno motivo può frequente di ricerca oppure ordinamento; non fare un indice non compromette il buon funzionamento dell'applicazione ma può avere un impatto anche non indifferente sulle prestazioni.

E siamo al nostro grafico: la macchina in questione è di un cliente che ospita, tra le sue applicazioni, un sito web dai contenuti multimediali ad elevato traffico. Da sistemista che sono non ho mai messo becco più di tanto nel come è scritta l'applicazione, anche se a tratti ci sarebbe da rabbrividere a leggerne il codice PHP, ma come detto per non passare da guastafeste me ne sono sempre stato; il sistemista però sta buono fino a quando tutte le porcherie in funzione non diventano pericolose per la propria tranquillità e così martedì 25 aprile mi arriva un messaggio dal sistema di monitoraggio che mi avvisa che la macchina MySQL sta cominciando a soffrire.

Butto un occhio ai grafici ed in effetti, come si può vedere dal carico di CPU, si era superato di gran lunga il classico trend che vedeva un picco al 50% intorno alle tre di pomeriggio, sforando ben oltre il 60%. Comincia quindi l'attività di analisi del carico e subito spunta MySQL con una inquietante informazione: tutte le query effettuate dal sito web in questione sono del tipo full table scan. Significa quindi che gli indici presenti (se presenti) non vengono utilizzati ma il famoso utente del libro di cui sopra, per trovare un determinato capitolo, è costretto a leggersi tutto il libro ed il libro sta assumendo le dimensioni dell'Enciclopedia Treccani.

Si analizza ancora più a fondo e salta fuori che le query fatte al MySQL sono sempre le solite due, sempre alle solite due tabelle più popolose. Vado a vedere e l'indice c'è, ma su un campo del tutto inutile ed addirittura ignorato da chi ha creato il database usando, penso, il wizard in regalo con l'ultimo numero di Topolino. Individuo i campi per cui effettuare l'indice ed invece di intervenire, perché il sistemista non deve intervenire su una applicazione non sua, scrivo a chi ha realizzato la baracca.

Questa società, devo ammetterlo, ha all'attivo delle realizzazioni graficamente molto belle e curate il che significa che al loro interno ci sono persone creative e capaci nell'uso degli strumenti propri di un web designer. Quello che sicuramente manca però è una conoscenza del lato tecnico del web, tanto che li ho sorpresi più di una volta ad utilizzare strumenti impropri, copiare ed incollare script altrui oramai superati. Ed infatti il responsabile del database mi scrive "Ti è possibile entrare nello specifico (tabelle); giusto per capire dove concentrarmi nel trovare una soluzione?". Partendo dal fatto che:

• il database l'hai progettato tu
• l'applicazione l'hai realizzata tu
• il database è formato da sette tabelle, di cui due piene, una inutile e le altre pressoché vuote

come fai a non sapere qual'è quella che crea problemi? e come potrei saperlo io, visto che ho gli stessi strumenti di analisi che potresti avere tu (cioè il comando EXPLAIN di MySQL)? Fatto sta che gli passo un paio di indicazioni ed il risultato è quello illustrato: crollo immediato del carico sul sistema e riduzione nei giorni successivi pari al 90%. Insomma, si è passati dal dover buttare via un server multi-core per passare a qualcosa di più performante, a pensare che la macchina possa sopportare tranquillamente un carico 50 volte superiore sull'applicazione web.

Ricapitolando: per chi non crede nell'uso degli indici, anche per la propria pulciosissima applicazione web, dia una occhiata al grafico che ho riportato e ci faccia un pensierino.