Partizionamento

Partizionare una tabella MySQL è un'altra delle tecniche di database design che permettono di ottenere migliorie sia in termini di performance, che in termini di manutenibilità, scalabilità e costi per lo storage di grandi quantità di dati.

Con il termine partitioning intendiamo il partizionamento, ovvero la suddivisione e la separazione di dati in più parti. Si potrebbe pensare al partizionamento dei dati come alla suddivisione di una tabella in più parti, ognuna delle quali viene salvata in una differente unità di memorizzazione (in genere hard disk). Sebbene partizionamenti "fisici" di questo tipo possono essere ottenuti senza troppi problemi, quello di cui noi ci occuperemo in questa lezione è un partizionamento di tipo logico, in cui tabelle e dati saranno suddivisi logicamente in posizioni differenti, utilizzando comunque lo stesso disco di memorizzazione. Il partitioning permette alle tabelle e agli indici, per esempio, di essere organizzati in parti più piccole, in modo da potervi accedere in tempi più brevi e con meno risorse computazionali.

Esistono essenzialmente due tipologie di partizionamento:

• Partizionamento Orizzontale
• Partizionamento Verticale

Sebbene queste due tipologie siano presenti in molti database, purtroppo MySQL permette l'organizzazione dei propri dati soltanto attraverso la tipologia del partizionamento orizzontale. In questa lezione vedremo comunque entrambe le tipologie, in modo da poter offrire una panoramica completa dello scenario del partitioning.

Parizionamento Orizzontale

Il partizionamento orizzontale è, come detto prima, l'unica forma di partitioning presente in MySQL. Esso consiste nel dividere logicamente le righe di una tabella in due o più partizioni (una partizione è un sottoinsieme di dati presenti nella tabella). Le colonne presenti nella tabella non vengono alterate e quindi saranno presenti in tutte le partizioni che si è scelto di avere. Per spiegare meglio questo concetto, possiamo pensare di possedere una tabella in cui siano registrate tutte le vendite di un'attività, mese dopo mese, per un intero anno lavorativo. Un partizionamento intelligente potrebbe suddividere i dati della tabella in modo che ogni partizione contenga le vendite di un determinato mese dell'anno.

Partizionamento Verticale

Il partizionamento verticale, invece, si occupa di suddividere una tabella in più tabelle che contengono meno colonne rispetto quella di partenza. Se con il partizionamento orizzontale veniva comunque mantenuta la struttura degli attributi (ogni partizione possiede le stesse colonne di quella di partenza), con quella verticale si ha un ridimensionamento degli attributi stessi. Questa tipologia di partizionamento risulta essere molto comoda in tabelle in cui sono presenti colonne di tipo BLOB (Binary Large Object) o TEXT. Per fare un esempio pratico, si potrebbe pensare di separare le colonne di una tabella che siano definite di tipo BLOB o TEXT, da quelle che possiedono tipi primitivi. In questo modo si potrebbe accedere ai dati delle colonne con i tipi primitivi in tempi molto più brevi, dato che la query non si occuperebbe di accedere a tutta la tabella (comprensiva delle colonne BLOB o TEXT).

Verificare il supporto al partitioning

Prima di passare a qualche esempio pratico, per essere certi che il nostro database supporti il partitioning bisogna lanciare il seguente comando dalla console MySQL:

SHOW PLUGINS

Se la versione del nostro database supporta questa funzionalità, dovremmo trovare, tra le righe dei vari plugin, anche la seguente:

| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

Per default, il partitioning dovrebbe essere attivo. Per disabilitarlo all'avvio di MySQL, possiamo utilizzare la seguente opzione:

skip-partition=True

Per abilitarlo, si può usare l'analogo seguente:

skip-partition=False

Partizionare una tabella

Una volta verificata l'abilitazione del plugin, per poter partizionare una tabella potremo usare sia il comando CREATE TABLE, sia il comando ALTER TABLE. Infatti, è possibile partizionare una tabella in fase di creazione, ma anche dopo averla già creata (per esempio al fine di ottimizzare indirettamente l'esecuzione di qualche query su una tabella). La sintassi per partizionare una tabella tramite il comando CREATE TABLE è:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
(create_definition,...)
[table_options]
[partition_options]

Dove partition_options può assumere i seguenti valori:

PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
| RANGE(expr)
| LIST(expr) }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list) }
[SUBPARTITIONS num]
]
[(partition_definition[, partition_definition] ...)

Quelli appena elencati sono tutti i possibili metodi con cui possiamo partizionare la nostra tabella. Nello specifico, sono presenti quattro diverse tipologie di partizione: HASH, KEY, RANGE, LIST.

Partition by RANGE

La partizione di tipo RANGE assegna le righe di una tabella alle varie partizioni sulla base del valore che assumono i dati delle varie colonne. Se questi rientrano in un determinato range, allora vengono spostati in una particolare partizione. Un esempio potrebbe essere:

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(username, created)
)
PARTITION BY RANGE( YEAR(created) )(
    PARTITION from_2013_or_less VALUES LESS THAN (2014),
    PARTITION from_2014 VALUES LESS THAN (2015),
    PARTITION from_2015 VALUES LESS THAN (2016),
    PARTITION from_2016_and_up VALUES LESS THAN MAXVALUE
);

Dove si è scelta, come colonna discriminante per il partizionamento, quella dell'anno; di seguito, vengono specificate le partition_definition, ovvero le definizioni delle varie partizioni. Viene da sè che ogni definizione specifica una particolare partizione, che si occuperà di memorizzare tutte le righe della tabella per le quali viene rispettato il vincolo di range.

Partition by LIST

La partizione di tipo LIST permette di ottenere una partizione della tabella simile a quella di RANGE, con l'eccezione che le partizioni vengono create non più se i valori di una o più colonna/e rientrano in un determinato range di valori, bensì in una lista di valori prestabilita. Vediamo un esempio:

CREATE TABLE serverlogs (
    serverid INT NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL
)
PARTITION BY LIST (serverid)(
    PARTITION server_east VALUES IN(1,43,65,12,56,73),
    PARTITION server_west VALUES IN(534,6422,196,956,22)
);

In questo esempio vediamo che le partizioni sono create con tutte quelle righe che possiedono i valori dell'attributo server_east e server_west contenuti nelle rispettive liste.

Partition by HASH

La partizione di tipo HASH, a differenza delle precedenti, partiziona sulla base del valore di ritorno di una funzione definita dall'utente o sul valore di una colonna. L'utente può utilizzare qualsiasi espressione della sintassi MySQL per dar vita a tale funzione (specificandola tra le parentesi del comando PARTITION BY HASH), purchè il valore di ritorno sia comunque un numero non negativo. A differenza dei metodi di partizione che abbiamo visto prima, nella partizione tramite hash non è necessario specificare dei range o delle liste per creare le varie partizioni. Infatti, i dati saranno distribuiti secondo la posizione dettata dall'hash table. L'unica cosa da fare è specificare il numero massimo di partizioni desiderate e la funzione di hash:

CREATE TABLE serverlogs2 (
    serverid INT NOT NULL,
    logdata BLOB NOT NULL,
    yr DATE NOT NULL
)
PARTITION BY HASH (YEAR(yr))
PARTITIONS 10;

Nell'esempio vediamo che le partizioni volute sono 10 (senza specificare espressamente il numero di partizioni, se ne considererebbe una sola) e che queste vengono suddivise in base al risultato che ritorna la funzione YEAR(), specificata dall'utente e avente come input il valore di yr.

Partition by KEY

La partizione di tipo KEY è simile a quella di tipo HASH, ma l'utente non può specificare alcuna funzione di hashing. Le partizioni vengono suddivise sulla base dei valori di una o più colonne, definite nella dichiarazione della tabella come PRIMARY o UNIQUE KEY. MySQL si occuperà di prelevare tali chiavi per utilizzarle come input di una sua funzione di hash. Le colonne possono contenere anche tipi diversi dall'INTEGER. Come nel caso della partizione basata su hash, bisogna specificare il numero massimo di partizioni.

CREATE TABLE serverlogs4 (
    serverid INT NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    label VARCHAR(10) NOT NULL,
    UNIQUE KEY (serverid, label, created)
)
PARTITION BY KEY()
PARTITIONS 10;

Conclusioni

Quelle che abbiamo visto sono diverse tecniche di partitioning di MySQL che è possibile adoperare per migliorare le performance delle nostre query SQL. Quando si hanno rallentamenti con l'esecuzione delle query, le partizioni possono essere una valida soluzione per migliorare le performance del database.