Partizionare una tabella MySQL con milioni di records per velocizzare le query
In presenza di tabelle con milioni di records le query potrebbero risultare lente.
Il partizionamento di MySQL consente di memorizzare i dati della tabella in partizioni separate e più piccole, in modo tale che, quando si esegue una query, sarà necessario esaminare solo un sottoinsieme dei dati per ottenere il risultato e non l'intera tabella, e quindi le query saranno più veloci.
Esistono due forme principali di partizionamento, orizzontale e verticale, tuttavia MySQL supporta solo il partizionamento orizzontale. In entrambi i casi, l'obiettivo del partizionamento è di eseguire la scansione di un numero inferiore di dati per aumentare la velocità della query.
Partizionamento orizzontale
I records di una tabella vengono divisi in due o più partizioni più piccole, basate su una logica. Le colonne presenti nella tabella non vengono modificate per cui saranno presenti in tutte le partizioni create.
Ad esempio potremmo suddividere una tabella con i dati di vendita dell'anno, in partizioni mensili, in modo che ogni partizione contenga i dati di un solo mese.
Come detto, MySQL supporta solo questo tipo di partizionamento.
Partizionamento verticale
A differenza del caso precedente, il partizionamento verticale divide una tabella in più tabelle che contengono meno colonne. In poche parole alcune colonne di una riga confluiscono in una partizione e altre colonne in un altra.
Questo tipo di partizionamento è molto usato in tabelle in cui sono presenti colonne con molti dati testuali, quindi di tipo TEXT o BLOB.
Principali vantaggi del partizionamento
Alcuni dei vantaggi dell'utilizzo delle partizioni sono:
- Archiviazione: è possibile memorizzare in una tabella partizionata più dati di quelli che possono essere salvati su una tabella non partizionata.
- Eliminazione rapida: l'eliminazione di una partizione è quasi istantanea, mentre una classica query DELETE eseguita in una tabella molto grande potrebbe richiedere molti minuti.
- Scelta delle partizioni: MySQL esclude automaticamente le partizioni non necessarie nella ricerca e quindi rendere l'interrogazione più veloce. Inoltre da MySQL 5.7 viene supportata la selezione esplicita delle partizioni nelle query, il che aumenta notevolmente la velocità di ricerca. Questo vale anche per le istruzioni DELETE, INSERT, REPLACE e UPDATE, nonché LOAD DATA e LOAD XML.
Come verificare se il tuo MySql supporta il partizionamento
Di default il partizionamento è supportato. Per verificarlo utilizziamo, nel client MySQL o in PhpMyAdmin l'istruzione SHOW PLUGINS.
SHOW PLUGINSIl risultato sarà una lista come questa
binlog ACTIVE STORAGE ENGINE NULL GPL
mysql_native_password ACTIVE AUTHENTICATION NULL GPL
mysql_old_password ACTIVE AUTHENTICATION NULL GPL
sha256_password ACTIVE AUTHENTICATION NULL GPL
MEMORY ACTIVE STORAGE ENGINE NULL GPL
.................................
INNODB_SYS_TABLESPACES ACTIVE INFORMATION SCHEMA NULL GPL
INNODB_SYS_DATAFILES ACTIVE INFORMATION SCHEMA NULL GPL
INNODB_CHANGED_PAGES ACTIVE INFORMATION SCHEMA NULL GPL
partition ACTIVE STORAGE ENGINE NULL GPLCercate la riga "partition ACTIVE": se presente il partizionamento è supportato, ed attivo.
In alternativa possiamo ottenere lo stesso risultato effettuando questa query sulla tabella "INFORMATION_SCHEMA.PLUGINS"
SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_TYPE='STORAGE ENGINE';Se il PLUGIN "partition" non è presente significa che MySQL è stato installato senza il supporto per il partizionamento: occorre reinstallare MySQL utilizzando i sorgenti e l'opzione "-DWITH_PARTITION_STORAGE_ENGINE."
Se invece vogliamo disabilitare il partitioning, dobbiamo avviare MySQL con l'opzione "--skip-partition". Quando il supporto per il partizionamento è disabilitato, puoi vedere tutte le tabelle partizionate esistenti, ma non puoi manipolarle o accedere ai loro dati.
Tipi di partizioni
Ci sono 4 modi in MySQL per partizionare una tabella
- RANGE
- HASH
- LIST
- KEY
La scelta del tipo di partizione è importante, e dipende dalle specifiche esigenze di utilizzo del database.
Alla fine di questo articolo vedremo come aggiungere il partizionamento ad una tabella non partizionata. Adesso invece vediamo come creare da zero una tabella partizionata.
PARTIZIONAMENTO by RANGE
In questo tipo di partizionamento, ad ogni partizione viene assegnato un intervallo di records (ad esempio: date). Questo tipo di partizionamento è forse tra i più utilizzati.
Ad esempio, prendiamo un intervallo per data:
- nel partizionamento 1 immettiamo i records di gennaio 2018
- nel partizionamento 2 immettiamo i records di febbraio 2018
- .... e così via
Facciamo un esempio pratico e vediamo subito come creare una tabella con partizioni.
Questa è la creazione di una tabella di tipo "MyISAM" chiamata "stat" dove registriamo gli accessi alla nostra pagina web
CREATE TABLE IF NOT EXISTS `stat` (
`stat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_data` datetime DEFAULT NULL,
`stat_ip` varchar(22) DEFAULT NULL,
PRIMARY KEY (`stat_id`),
KEY `stat_data` (`stat_data`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Vediamo adesso come ottenere la stessa tabella attraverso un partizionamento per anno, cioè basato su intervalli di date prese dal campo "stat_data"
CREATE TABLE IF NOT EXISTS `stat` (
`stat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_data` datetime DEFAULT NULL,
`stat_ip` varchar(22) DEFAULT NULL,
PRIMARY KEY (`stat_id`,`stat_data`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(stat_data)) (
PARTITION p0 VALUES LESS THAN (2014) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2015) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2016) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2017) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = MyISAM
)
;Vediamo innanzitutto che come PRIMARY KEY non possiamo indicare solo lo stat_id incrementale, ma anche il campo stat_data su cui si baserà il partizionamento.
Vediamo poi la dichiarazione con cui definiamo il tipo di partizionamento "BY RANGE", specifichiamo che l'intervallo sarà per anno (utilizziamo la funzione YEAR) basata sul campo "stat_data", e definiamo le varie partizioni
- p0: è la prima partizione, e considera i records precedenti al 2014
- p1: è la seconda partizione, e considera i records tra il 2014 e precedenti al 2015
- p2: è la terza partizione, e considera i records tra il 2015 e precedenti al 2016
- p3: è la quarta partizione, e considera i records tra il 2016 e precedenti al 2017
- p4: infine abbiamo la quinta partizione, dove inseriamo tutti i record successivi
Per quanto non sia obbligatorio, è molto importante prevedere come ultima partizione (la p4 nel nostro esempio) quella definita con la clausola VALUES LESS THAN MAXVALUE: con essa tutti i record che non dovessero cadere negli intervalli definiti in precedenza finiranno in quest’ultima. Senza l’ultima partizione, ogni record al di fuori dei range presenti genererebbe un errore di inserimento.
Se andiamo abbiamo modo di accedere al filesystem del nostro server, troveremmo in aggiunta ai soliti tre file .frm (definizione e charset), .MYD (dati) e .MYI (indici), un nuovo file .par che gestisce le partizioni e, per ogni partizione, una coppia di file .MYD e .MYI per gestirne dati e indici.
Se invece di una tabella MyISAM avessimo creato una tabella di tipo InnoDB questi file aggiuntivi non sarebbero stati presenti, infatti con InnoDB il partizionamento è gestito internamente al tablespace (lo spazio condiviso, di solito un unico grande file, nel quale sono memorizzate insieme tutte le tabelle InnoDB presenti nel DBMS e tutti i relativi indici).
Proviamo adesso ad inserire dei records
INSERT into stat (stat_data, stat_ip) values ("2014-01-14","192.168.1.125");
INSERT into stat (stat_data, stat_ip) values ("2014-02-12","192.168.1.117");
INSERT into stat (stat_data, stat_ip) values ("2015-03-18","192.168.1.122");
INSERT into stat (stat_data, stat_ip) values ("2017-04-22","192.168.1.115");
INSERT into stat (stat_data, stat_ip) values ("2017-05-29","192.168.1.123");
INSERT into stat (stat_data, stat_ip) values ("2018-07-20","192.168.1.103");
Facendo adesso una SELECT e come possiamo vedere il risultato ottenuto è identico, apparentemente, a quello che avrei ottenuto senza partizioni
SELECT * FROM stat;
+---------+------------+---------------+
| stat_id | stat_data | stat_ip |
+---------+------------+---------------+
| 1 | 2014-01-14 | 192.168.1.125 |
| 2 | 2014-02-12 | 192.168.1.117 |
| 3 | 2015-03-18 | 192.168.1.122 |
| 4 | 2017-04-22 | 192.168.1.115 |
| 5 | 2017-05-29 | 192.168.1.123 |
| 6 | 28-07-2010 | 192.168.1.103 |
+---------+------------+---------------+Solo apparentemente non è accaduto nulla di diverso, in realtà i record 1 e 2 sono stati inseriti nella partizione p0, il record 3 nella p1, i record 4 e 5 nella p3, e il record 6 nella p4.
Non ci credete? Lanciate questa query per eviziare il numero di records presenti in ogni partizione della tabella "stat"
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='stat';Il risultato sarà
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 2 |
| p1 | 1 |
| p2 | 0 |
| p3 | 1 |
| p4 | 1 |
+----------------+------------+Prendiamo in caso di una tabella con milioni di records. Potete capire che se cerco le statistiche dell'anno 2017, la ricerca avverrà solo all'interno della partizione p3, con conseguente velocizzazione della query!
Il partizionamento by RANGE può interessare solo colonne di tipo "DATE", "DATETIME", e tutte le tipologie di colonne con dati (numeri) interi: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), e BIGINT.
Notiamo infine che la colonna, o la funzione di partizionamento (ad esempio "YEAR"), deve restituire un valore intero.
PARTIZIONAMENTO by LIST
La differenza rispetto al caso precedente è che non consideriamo intervalli, ma una lista di valori prestabilita.
Facciamo un esempio: abbiamo una tabella "impiegati", ed ogni impiegato è inserito in un reparto identificato con un numero (ad esempio i reparti 1,2,3,4,5,6,7,8,9,10,11,12)
CREATE TABLE impiegati (
id INT UNSIGNED NOT NULL,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL,
data_assunzione DATE NOT NULL,
data_licenziamento DATE NOT NULL,
codice_reparto INT UNSIGNED,
PRIMARY KEY (`id`,`codice_reparto`)
)
PARTITION BY LIST(codice_reparto) (
PARTITION nord VALUES IN (2,6,10),
PARTITION sud VALUES IN (3,4,7,9),
PARTITION est VALUES IN (1,5),
PARTITION ovest VALUES IN (8,11,12)
);Andiamo adesso ad inserire due impiegati, specificandone il reparto
INSERT INTO impiegati VALUES ('Paolo','Rossi','1990-01-05',NULL,5);
INSERT INTO impiegati VALUES ('Mario','Bianchi','1967-05-27',NULL,10);Rispetto all'utilizzo del partizionamento per RANGE, non è possibile creare una partizione in cui confluiscano i records che non possono rientrare nelle altre partizioni, per cui l'inserimento di un record che non possa trovare una collocazione in una della partizione creare genererà un errore di inserimento.
INSERT INTO impiegati VALUES('Carlo','Mentana','2005-12-05',NULL,14);
ERROR 1523 (HY000): Table has no partition for value 14PARTIZIONAMENTO by HASH
Con i due partizionamenti precedenti rischiamo, nel tempo, di ottenere partizioni con peso molto diverso: alcune possono avere moltissimi records, altre pochi, con conseguenti tempi di risposta differenti nelle query.
Per ovviare a questo è possibile ricorrere al portizionamento per HASH (o a quello by KEY che vedremo tra poco), attraverso cui il server distribuisce i record in maniera omogenea tra le diverse partizioni in modo da ottenere partizioni con dimensioni similari.
A differenza dei metodi precedenti, nella partizione tramite HASH:
- non si specificano range o liste per creare le varie partizioni
- non è possibile definire il nome delle partizioni, che vengono assegnate in automatico da MySQL.
Per ottenere questo tipo di partizionamento occorre decidere quale sia la colonna (o la funzione da applicare alla colonna, ad esempio "YEAR" come visto negli esempi precedenti) in base alla quale partizionare, e stabilire il numero massimo di partizioni desiderate. Del resto se ne occuperà MySQL. Come funzione è possibile utilizzare qualunque espressione valida in MySQL.
Questo è un esempio in cui creiamo 5 partizioni
CREATE TABLE IF NOT EXISTS `stat` (
`stat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_data` datetime DEFAULT NULL,
`stat_ip` varchar(22) DEFAULT NULL,
PRIMARY KEY (`stat_id`,`stat_data`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY HASH (YEAR(stat_data))
PARTITIONS 5;Come nei casi precedenti, anche in questo caso la funzione di partizionamento deve restituire un valore intero.
PARTIZIONAMENTO by KEY
Simile al caso precedente, ma in questo caso non possiamo scegliere la funzione di partizionamento (ad esempio "YEAR"), ma lasciamo a MySQL il compito di scegliere la funzione di hashing da utilizzare.
Possiamo invece specificare la colonna su cui basare il partizionamento, oppure non indicare nulla, in tal caso MySQL prenderà la PRIMARY KEY della tabella o, se presente, una colonna definita come UNIQUE KEY.
Anche in questo caso occorre definire il numero massimo di partizioni da ottenere.
In questo esempio viene utilizzato il campo "stat_id" come colonna di riferimento per il partizionamento, in quanto è la PRIMARY KEY della tabella
CREATE TABLE IF NOT EXISTS `stat` (
`stat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_data` datetime DEFAULT NULL,
`stat_ip` varchar(22) DEFAULT NULL,
PRIMARY KEY (`stat_id`,`stat_data`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY KEY()
PARTITIONS 5;E' possibile scegliere il campo (i campi) su cui basare il partizionamento, in tal caso va espressamente indicato all'interno di KEY(), come in questo esempio
CREATE TABLE IF NOT EXISTS `stat` (
`stat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_data` datetime DEFAULT NULL,
`stat_ip` varchar(22) DEFAULT NULL,
PRIMARY KEY (`stat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY KEY(stat_data)
PARTITIONS 5;A differenza dei casi precedenti la/le colonne su cui si basa il partizionamento possono contenere anche valori non interi (INTEGER).
Creare una partizionamento su una tabella MySQL già esistente
Abbiamo visto come creare da zero una tabella partizionata. Capita però spesso di dovere intervenire su una tabella non partizionata, e di aggiungere un partizionamento. Vediamo come dobbiamo operare.
Cominciamo con il creare una tabella non partizionata.
CREATE TABLE IF NOT EXISTS `stat` (
`stat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_data` datetime DEFAULT NULL,
`stat_ip` varchar(22) DEFAULT NULL,
PRIMARY KEY (`stat_id`,`stat_data`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Adesso aggiungiamo il partizionamento, ad esempio, per RANGE, utilizzando ALTER TABLE, ed aggiungiamo le partizioni, seguendo quanto abbiamo imparato in precedenza:
ALTER TABLE stat
PARTITION BY RANGE(YEAR(stat_data)) (
PARTITION p0 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2018) ENGINE = InnoDB
)Infine, andiamo a verificare le partizioni e i records contenuti
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='stat';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 412045 |
| p1 | 145653 |
| p2 | 1896542 |
| p3 | 23464 |
+----------------+------------+Ecco fatto, abbiamo trasformato una tabella non partizionata in una tabella partizionata.
Nei prossimi giorni sarà presente un nuovo articolo in cui vi dirò come manipolare, e quindi modificare, aggiungere o eliminare, una partizione.
Stay tuned!
