Gestire milioni di record con Laravel e Mysql Partition

Lavorando con software complessi per aziende, è importante tenere conto della crescita esponenzionale dei dati contenuti nel database. Spesso un database mal progettato può rappresentare il collo di bottiglia di un applicazione, infatti qualora ci trovassimo di fronte a database con milioni di record, se non è stata fatta una buona progettazione potremo avere query molto lente.

Con la nostra azienda realizziamo software per l’industria 4.0, ci troviamo dunque molto spesso a dover gestire grandi moli di dati. Utilizzando nella maggior parte dei casi laravel come framework di sviluppo e mysql come base dati abbiamo creato una liberia che ci permette di ottimizzare tali performance.

Cosa si intende per partizionamento di una tabella?

Il partizionamento di MySQL consente di memorizzare i dati di una tabella in spazi separati e più piccoli, in modo che, quando si esegue una query, sarà necessario esaminare solo un sottoinsieme di essi per ottenere il risultato e non l’intera tabella, ne consegue una maggiore velocità nell’esecuzione delle query.

Esistono due tipi di partizionamento, orizzontale e verticale, MySQL supporta solo il partizionamento orizzontale. In entrambi i casi, l’obiettivo è di eseguire la scansione di un numero inferiore di dati per aumentare la velocità della query.

Partizionamento orizzontale

I records di una singola tabella sono divisi in n partizioni più piccole, secondo una condizione logica. Non vi è differenza a livello di colonne o struttura, quindi tutte le partizioni hanno il medesimo schema. Un esempio può essere quello di dividere una tabella di log in base all’anno. MySQL supporta solo questo tipo di partizionamento.

Partizionamento verticale

A differenza del precedente, Ogni tabella è divisa in tabelle più piccole che possono contenere anche meno colonne. In poche parole alcune colonne di una riga confluiscono in una partizione e altre colonne in un altra.

Quali sono i vantaggi nell’uso delle tabelle partizionate?

Primo motivo fra tutti, ed è anche quello che ci ha fatto avvicinare a questo mondo è senza dubbio la performance. Analizzare un sottoinsieme ridotto in una grande mole di dati si traduce in delle query più performanti.

Vediamo un esempio di query sulla stessa mole di dati ma con struttura differente, il codice seguente mostra una where in una tabella semplice con quasi un milione di record, come si vede abbiamo circa 10 sec. di esecuzione.

mysql> select count(*) from no_part_tab where date > '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (10.30 sec)

Lanciando invece la stessa query su una tabella partizionata il risultato è ben diverso, si scende addirittura a meno di 4 secondi:

mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (3.88 sec)

Altro motivo non da poco è la capacità di archiviazione, tabelle partizione possono archiviare più dati di una tabella singola.

Altro vantaggio è quello dell’Eliminazione rapida, è infatti possibile rimuovere grandi quantità di dati appartenenti alla stessa partizione in pochi istanti.

Il partizionamento con Laravel

Laravel è un framework MVC, dunque una libreria di codice PHP che serve ad uno sviluppatore web a rendere i propri progetti più performanti. Laravel è un framework open source di scritto in PHP  utile per la realizzazione di applicazioni web creato da Taylor Otwell nel 2011.

Utilizzando questo framework come base per la maggior parte dei nostri software web based, abbiamo deciso di creare una libreria che permettesse di estenderlo con la possibilità di partizione le tabelle anche direttamente nei file di migrations.

Se sei nuovo al concetto di migrations, ti rimandiamo alla documentazione ufficiale di Laravel.

Basterà inserire nella migration un codice come quello sottostante per partizione una tabella per gli anni che vanno dal 2000 al 2003:

Schema::partitionByRange('partitioned', 'YEAR(date)', [
    new Partition('anno2000', Partition::RANGE_TYPE, 2000),
    new Partition('anno2001', Partition::RANGE_TYPE, 2001),
    new Partition('anno2002', Partition::RANGE_TYPE, 2002),
    new Partition('anno2003', Partition::RANGE_TYPE, 2003),
]);

Per maggiori dettagli sull’utilizzo della libreria, vi rimando alla pagina ufficiale su GitHub.

Se la tua azienda deve realizzare un software custom su misura, che sia professionale, affidabile, e performante, contattaci per un preventivo gratuito.

FONTI:

Share: