MySql表分区语法

MySql表分区语法：

range分区:

create table t1(
id int,
name varchar(20),
age int
)
partition by range(age) -- 用age字段作为分区字段
(
partition p01 values less than(10), -- 第一个分区，年龄为10岁以下的都存放在此分区
partition p02 values less than(20), -- 第二个分区，年龄为10-20岁的都存放在此分区
partition p03 values less than(30), -- .....
partition p04 values less than(maxvalue) -- 自增分区，大于30岁的都存放在此分区
);

list分区：

create table t2(
id int,
cid int,
name varchar(20),
pos_data datetime
)
partition by list(cid)
(
partition p01 values in (1,4,12),
partition p02 values in (2,6,9),
partition p03 values in (3,5,7,8,10,11)
);

hash分区：

create table t3(
id int,
cid int,
name varchar(20),
pos_date datetime
)
partition by hash (cid)
partitions 4;  -- 分4个区

linear hash线性分区:

create table t4(
id int,
cid int,
name varchar(20),
pos_date datetime
)
partition by linear hash (cid)
partitions 4;

key分区：

create table t5(
id int,
cid int,
name varchar(20),
pos_date datetime
)
partition by key(cid)
partitions 4;

多列分区：

#mysql5.5版本后有的

create table t6(
a int,
b int,
c int
)engine=innodb
partition by range columns(a,b)
(
partition p01 values less than (10,10),
partition p02 values less than (10,20),
partition p03 values less than (10,30),
partition p04 values less than (maxvalue,maxvalue)
);

子分区 ：

#对分区表再次分割

create table t7(
id int,
udate date
)
partition by range(YEAR(udate))
subpartition by hash(to_days(udate))
subpartitions 2
(
partition p01 values less than (1990),
partition p02 values less than (2000),
partition p03 values less than (maxvalue)
);

分区表分配文件存放路径：

#分区并分配分区文件放在不同磁盘或者不同文件路径下存储，解决查询IO效率

create table t8(id int,udate date)
partition by range(year(udate))
subpartition by hash(to_day(udate))
(
partition p01 values less than(1999)
)
subpartition s0
data directory='/var/a/data'
index directory='/var/a/idx',
subpartition s1
data directory='/var/b/data'
index directory='/var/b/idx'
),
partition p02 values less than(2000)
(
subpartition s2
data directory='/var/c/data'
index directory='/var/c/idx',
subpartition s3
data directory='/var/d/data'
index directory='/var/d/idx'
)
);