What is partitioning?
The structure which divides one table data to multiple data.
When not using partitioning, one table data is aggregated to one file. On the other hand, when using partitioning, one table data is divided to multiple files. Especially table which is referenced can focus on and select performance can be improved. Also, deleting unnecessary data is easier.
There are vertical partitioning and horizontal partitioning, but explains regarding horizontal partitioning in this section.
Regarding MySQL, partition functionality is attached from 5.1
Regarding MariaDB, partition functionality can be used from initial version, 5.1
Assumption of using partition
The used column needs to have primary key.
Kind of partitioning
Range
Specify range of column value.
e.g.
CREATE TABLE sample_table (
id int NOT NULL,
day DateTime NOT NULL
) ENGINE=InnoDB
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (3),
PARTITION p0 VALUES LESS THAN (4)
)
List
Specify data list of column value.
e.g.
CREATE TABLE sample_table ( id int(2), day DateTime Not NULL ) ENGINE=InnoDB PARTITION BY LIST(id)( PARTITION p1 VALUES IN (1,2), PARTITION p2 VALUES IN (3,4,5));
Hash
RDBMS engine calculates hash and divides automatically.
e.g.
CREATE TABLE sample_table (
id int(2),
day DateTime NotNULL
) ENGINE=InnoDB
PARTITION BY HASH(id)
PARTITIONS 10;
;
Key
Specify more than one keys(primary or unique), and RDBMS engine create hash and divides automatically.
e.g.
CREATE TABLE t4 (
id int(2) PRIMARY KEY,
day DateTime NOT NULL
) ENGINE=InnoDB
PARTITION BY KEY()
PARTITIONS 10;
;
