paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL - Partitioning (II parte)

RANGE partition

Questo tipo di partizionamento permette una notevole liberta' nella scelta dei dati, e quindi delle colonne della tabella, su cui far agire il partizionamento.

Ad esempio, se abbiamo la classica tabella degli impiegati e degli uffici dove hanno lavorato durante gli anni, possiamo impostare le partizioni per uffici (stored_id)

CREATE TABLE employees (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30),
        hired DATE NOT NULL DEFAULT '1970-01-01',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT NOT NULL,
        store_id INT NOT NULL
)
    PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
)

Quindi, dove tutti i record corrispondenti agli impiegati che hanno lavorato nello store_id da 1 a 5 saranno archiviate nella partizione p0, quelli dal 6 al 10 nella p1 e cosi' via. Notare che l'ultima partizione p3, permette l'archiviazione di tutti quei record che non rientrano nella precendenti partizioni, ovvero tutti quelli dal 16 in poi.

Avremmo potuto anche partizionare per Data ed in particolare per Anno, in questo modo

...
PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE

Notare, l'uso della clausola YEAR sul campo data.

Vantaggi

1. Risulta molto semplice eliminare dati vecchi. Infatti se si volessero eliminare tutti i dati relativi a prima del 1991, basta usare questa istruzione

ALTER TABLE employees DROP PARTITION p0;

E questa istruzione e' sicuramente piu' performante che cancellare i dati attraverso l'istruzione "delete", ovvero

DELETE FROM employees WHERE YEAR(separated) <= 1990;

2. Qualsiasi colonna puo' essere usata come regola di partizionamento

3. Se si utilizzano query che agiscono direttamente sulla partizione creata, Mysql automaticamente utilizzera' solo ed esclusivamente la partizione dove i dati risiedono, aumentando quindi le performance dell'estrazione.

EXPLAIN PARTITIONS
    SELECT COUNT(*)
      FROM employees
     WHERE separated BETWEEN '2000-01-01' AND '2000-12-31'
     GROUP BY store_id;,

In questo caso, accederemo direttamente alla partizione p2

LIST partition

Questo metodo e' molto simile a quello per RANGE, quindi ogni partizione deve essere esplicitamente definita. La sola differenza e' che nel List partition, ogni partizione e' definita basandosi su una lista di uno o piu' valori presenti nella colonna che si va a partizionare.

Nota: E' possibile indicare solo valori INTERI, o NULL.

Questo un esempio:

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);

Come si puo' notare non esiste il MAX_VALUE, ma tutti i valori devono essere presenti nella insert dei dati

Quindi se eseguiamo una insert di questo tipo

mysql> INSERT INTO employes VALUES (11,'prova','prova01', '2010-03-05','2010-03-08',1,10);
ERROR 1525 (HY000): TABLE HAS NO PARTITION FOR VALUE 10

HASH partition

Questo metodo viene utilizzato allo scopo di garantire la stessa distribuzione dei dati tra le varie partizioni. Mentre con i metodi RANGE e LIST c'e' bisogno di definire il numero e le regole di tutte le partizioni, nel caso di HASH, si occupera' MySql di crearle, indicando esclusivamente il numero totale delle stesse.

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

Nota: Se non viene inclusa la clausola "PARTITIONS", per default sara' impostata a 1.

Si puo' anche usare una espressione, per definire il valore su cui eseguire l'HASH, come ad esempio

PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

Bisogna tenere in considerazione che ogni volta che si esegue una insert, verra' verificata l'espressione utilizzata e quindi, in caso di espressioni particolarmente complesse, si potrebbero avere problemi di performance.

La configurazione piu' efficente per l'uso del metodo HASH, si ottiene quando la funzione di Hashing viene applicata ad 1 sola colonna i cui valori, si incrementano e decrementano considerevolmente con il valore della colonna. Questo permette il pruning del range delle partizioni. A questo livello non trattero' l'algoritmo di pruning, per la gestione delle partizioni tramite il metodo HASH e tralascero' anche il metodo LINEAR HASH. Saranno argomenti di un successivo articolo.

In successivi articoli verranno trattati anche le "Key partition" e le "subpartition", mentre per ora questo e il precedente articolo, sono sufficienti per gestire le partizioni in MySql.