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

DB Relational

        

MySQL - Partitioning (I parte)

Verifiche di compatibilita'

La prima operazione da effettuare e' verificare se la propria versione di MySql supporta il partizionamento.

Lanciare questo comando e controllare se il risultato

mysql> SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+

Inoltre occorre verificare anche l'istruzione SHOW PLUGINS, come mostrato in questo esempio:

mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+

Se queste verifiche non producono il risultato indicato, vuol dire che la propria versione di MySql non prevede il partizionamento.

Perche' partizionare?

Il partizionamento, presenta vari vantaggi.
- Permette di distribuire porzioni di singole tabelle in uno o piu' file system
- I dati che perdono consistenza (ad esempio quelli di storico) possono essere rimossi velocemente, rimuovendo la partizione che li contiene. Lo stesso meccanismo e' valido nel caso di dover aggiungere altri dati e quindi altre partizioni.
- Le query di estrazione possono essere ottimizzate, indicando direttamente nell'istruzione la partizione a cui si vuole accedere.

Trasciamolo il dettaglio di come MySql suddivide fisicamente i dati, concentrandoci invece sui passaggi e le istruzioni che dobbiamo eseguire per raggiungere il nostro obiettivo.

Primi passi

Innanzitutto, tutte le partizioni della stessa tabella che andiamo a partizionare, devono usare lo stesso storage engine, ovvero non possiamo usare MyISAM per una partizione e InnoDB per l'altra.

La sintassi prevede che tutte le opzioni che utilizziamo nella creazione di una tabella devono precedere l'istruzione di partizionamento, che quindi deve essere sempre l'ultima. Nell'esempio seguente, mostro come creare una tabella partizionata con il metodo HASH in 6 partizioni, per il campo data.

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

Nota importante: il partizionamento agisce su tutti i dati relazionati con la tabella che si va a partizionare, ovvero con i dati e gli eventuali indici e viceversa.

E' possibile assegnare specifiche directory alle partizioni, usando le opzioni DATA DIRECTORY e INDEX DIRECTORY, nella clausola PARTITION dell'istruzione di creazione della tabella, lasciando quindi liberta' di gestione.

Ma ci sono delle eccezioni su quest'ultimo punto. Infatti nella modalita' InnoDB engine, queste opzioni non hanno effetto. Lo stesso dicasi su sistemi operativi Windows.

Le opzioni MAX_ROWS e MIN_ROWS possono essere usate per definire rispettivamente il numero massimo e minimo di record che possono contenere ciascuna partizione.

Tipi di partizionamenti

Vediamo ora nei dettagli i 4 tipi di partizionamento possibili in MySql, ovvero Range, List, Hash e Key.

Range

Questo tipo di partizionamento assegna i record alle partizioni, basandosi sul range dato ai valori della colonna.

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 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 (21)
);

List

E' simile alla partizione per Range, eccetto che per la scelta di un set di valori che definiscono le partizioni, piuttosto che un range.

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,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

Hash

In questo caso una partizione e' stabilita dal risultato di una "expression" che opera sui valori dei campi al momento dell'inserimento degli stessi.

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 HASH( YEAR(hired) )
PARTITIONS 4;

Key

E' simile alla partizione per HASH, eccetto che sulle colonne che si scegli di agire, e' direttamente MySql server che decide la partizione attraverso la propria funzione di hashing.

Questo un primo esempio, dove viene usata la primary key come regola di partizionamento

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

Questo un secondo esempio, dove, non essendoci la primary key definita, viene usata la la chiave univoca come regola di partizionamento.

CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

Molto spesso si usa effettuare le partizioni per Data, cosa molto comune al punto che molti database la supportano di default. Infatti MySql e' ottimizzato nell'utilizzo delle funzioni TO_DAYS() e YEAR(), pero' si possono anche utilizzare le funzioni WEEKDAY(), DAYOFYEAR() o MONTH().

Questi sono due esempi:

Metodo Key

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY KEY(joined)
PARTITIONS 6;

Metodo Range

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

Nota importante: Alle partizioni viene assegnato automaticamente un numero progressivo (una sequence), partendo da 0. E' sempre bene pero' assegnare un nome univoco, ad esempio nometabella1, nometabella_02, etc... Inoltre il nome delle partizioni NON e' case sensitive, ovvero, una istruzione di questo tipo

mysql> CREATE TABLE t2 (val INT)
    -> PARTITION BY LIST(val)(
    ->     PARTITION mypart VALUES IN (1,3,5),
    ->     PARTITION MyPart VALUES IN (2,4,6)
    -> );

restituisce il seguente messaggio di errore "ERROR 1488 (HY000): Duplicate partition name mypart" perche' non ci sono differenze tra mypart e MyPart

Limitazioni:
Le FK no sono ammesse in tabelle che sono partizionate.
Per le altre limitazioni, consultare la documentazione ufficiale.

Qui termina la prima parte dell'articolo sul partitioning.