MySQL:Table Partitioning

In MySQL, there are four partitioning types:

Range Partioning:

Range partitioning distributes rows to partitions based on column values

falling within a given range.

List Partitioning:

List partitioning allows you to to be explicit about how rows are

distributed across the partitions by specifying a list of value for

the partitioning column of each partition.

Hash Partioning:

Hash partitioning evenly distributed data across all partitions using a

hash value of a partitioning key.

Key Partioning:

Key partitioning is similar to Hash partitioning but instead of using a

user-defined expression, it uses its own internal hashing function.

CREATE TABLE mytable2 (

post_id int DEFAULT NULL,

viewid int DEFAULT NULL,

user_id int DEFAULT NULL,

post_Date datetime DEFAULT NULL

)

PARTITION BY RANGE (year(post_Date))

(

PARTITION P0 VALUES LESS THAN (2020) ,

PARTITION P1 VALUES LESS THAN (2030) ,

PARTITION P2 VALUES LESS THAN (2040) ,

PARTITION P3 VALUES LESS THAN (2050) ,

PARTITION P4 VALUES LESS THAN MAXVALUE

)

By HASH:

CREATE TABLE employees (

id INT NOT NULL,

first_name VARCHAR(30),

last_name VARCHAR(30),

hired DATE NOT NULL DEFAULT '2019-09-27',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT

)

PARTITION BY HASH(store_id) PARTITIONS 4;

By KEY:

CREATE TABLE simple_customer (

id INT NOT NULL PRIMARY KEY,

full_name VARCHAR(40)

)

PARTITION BY KEY() PARTITIONS 2;

By LIST:

CREATE TABLE customers (

first_name VARCHAR(25),

last_name VARCHAR(25),

street_1 VARCHAR(30),

street_2 VARCHAR(30),

city VARCHAR(15),

renewal DATE

)

PARTITION BY LIST COLUMNS(city) (

PARTITION pRegion_1 VALUES IN('Camau', 'Kiengiang', 'Longan'),

PARTITION pRegion_2 VALUES IN('Dalat', 'Daklak', 'Pleiku'),

PARTITION pRegion_3 VALUES IN('Danang', 'Hoian', 'Hue'),

PARTITION pRegion_4 VALUES IN('Hanoi', 'Sapa', 'Ninhbinh')

);

ALTER SYNTAX:

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

);

ALTER TABLE employees 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

);