MySQL Partitioning

range partitioning:used for data that can be separated into ranges based on some criterion i.e. date, part number, ID's.
list partitioning:used for data that can be separated into lists based on some criterion i.e. city, territory.
hash partitioning:if data in a range partition is not evenly distributed (lots of data for one particular date) then performance decreases, hash partitioning uses hashing algorithms to assign a hash value to each rows partitioning key and place it in the appropriate partition. Hopefully the data will be evenly distributed across the partitions.

key partitioning:An internal algorithm is used by mysqld to try and evenly distribute the data across the partitions, a field called the key column is used for the determination of data.

range partitioning

mysql> create table employees (
    ->   emp_id int not null,
    ->   f_name varchar(30) not null,
    ->   l_name varchar(30) not null,
    ->   store_id int not null
    -> )
    -> partition by range (store_id) (
    ->   partition p0 values less than (101),
    ->   partition p1 values less than (201),
    ->   partition p2 values less than (301),
    ->   partition pfinal values less than maxvalue
    -> );
Query OK, 0 rows affected (0.01 sec)

list partitioning

mysql> create table employee_by_region (
    ->   emp_id int not null,
    ->   f_name varchar(30) not null,
    ->   l_name varchar(30) not null,
    ->   store_id int not null
    -> )
    ->   partition by list (store_id) (
    ->   partition north values in (1,2,3,4,5),
    ->   partition east values in (6,7,8,9,10),
    ->   partition south values in (11, 12, 13, 14, 15),
    ->   partition west values in (16, 17, 18, 19, 20)
    -> );
Query OK, 0 rows affected (0.03 sec)

hash partitioning

mysql> create table employee_by_hash (
    ->   emp_id int not null,
    ->   f_name varchar(30) not null,
    ->   l_name varchar(30) not null,
    ->   store_id int not null
    -> )
    -> partition by hash (store_id)
    -> partitions 5;
Query OK, 0 rows affected (0.03 sec)

key partitioning

mysql> create table employee_by_key (
    ->   emp_id int not null,
    ->   f_name varchar(30) not null,
    ->   l_name varchar(30) not null,
    ->   store_id int not null
    -> )
    -> partition by key (store_id)
    -> partitions 5;
Query OK, 0 rows affected (0.02 sec)

partition innodb datafile

zhongwc:/data/mysql/zhongwc # ls -l
total 1796
-rw-rw---- 1 mysql mysql    61 Feb 18 08:54 db.opt
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:13 employee_by_hash#P#p0.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:13 employee_by_hash#P#p1.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:13 employee_by_hash#P#p2.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:13 employee_by_hash#P#p3.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:13 employee_by_hash#P#p4.ibd
-rw-rw---- 1 mysql mysql  8670 Feb 18 09:13 employee_by_hash.frm
-rw-rw---- 1 mysql mysql    40 Feb 18 09:13 employee_by_hash.par
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:14 employee_by_key#P#p0.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:14 employee_by_key#P#p1.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:14 employee_by_key#P#p2.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:14 employee_by_key#P#p3.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:14 employee_by_key#P#p4.ibd
-rw-rw---- 1 mysql mysql  8670 Feb 18 09:14 employee_by_key.frm
-rw-rw---- 1 mysql mysql    40 Feb 18 09:14 employee_by_key.par
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:12 employee_by_region#P#east.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:12 employee_by_region#P#north.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:12 employee_by_region#P#south.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:12 employee_by_region#P#west.ibd
-rw-rw---- 1 mysql mysql  8670 Feb 18 09:12 employee_by_region.frm
-rw-rw---- 1 mysql mysql    44 Feb 18 09:12 employee_by_region.par
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:10 employees#P#p0.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:10 employees#P#p1.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:10 employees#P#p2.ibd
-rw-rw---- 1 mysql mysql 98304 Feb 18 09:10 employees#P#pfinal.ibd
-rw-rw---- 1 mysql mysql  8670 Feb 18 09:10 employees.frm
-rw-rw---- 1 mysql mysql    36 Feb 18 09:10 employees.par

add partition

alter table employees_by_region add partition (
  partition england values in (1,2),
  partition scotland values in (3,4)
);
alter table employees
partition by range (store_id) (
  partition p0 values less than (101),
  partition p1 values less than (201),
  partition p2 values less than (301),
  partition pfinal values less than maxvalue
);

drop partition

alter table employees_by_region drop partition scotland;

coalesce partition

alter table employees_hash coalesce partition 4;

reorganize partition 

alter table employees repair partition p0, p1;

analyze partition

alter table employees analyze partition 3;

check partition

alter table employees check partition 3;

optimize partition 

alter table employees optimize partition p0, p1;

rebuild partition 

alter table employees rebuild partition p0, p3;