MySQL常用分区方式及分区主键的添加

MySQL常用分区方式

Range分区

范围分区，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。

创建表时同时创建分区：

• 创建分区表liu_t_fenqu

CREATE TABLE liu_t_fenqu
(
    id INT NOT NULL
    ,name VARCHAR(20)
    ,age VARCHAR(20)
    ,birthdate date NOT NULL
    ,salary INT
)
PARTITION by RANGE(year(birthdate))
(
    PARTITION p1 VALUES less than (1970)
    ,PARTITION p2 VALUES less than (1990)
    ,PARTITION p3 VALUES less than MAXVALUE
);

• 删除该表

DROP TABLE liu_t_fenqu;

• 再次创建表功，尝试添加主键：

CREATE TABLE liu_t_fenqu
(
    id INT NOT NULL PRIMARY KEY
    ,name VARCHAR(20)
    ,age VARCHAR(20)
    ,birthdate date NOT NULL
    ,salary INT
)
PARTITION by RANGE(year(birthdate))
(
    PARTITION p1 VALUES less than (1970)
    ,PARTITION p2 VALUES less than (1990)
    ,PARTITION p3 VALUES less than MAXVALUE
);

运行结果如下：

[Err] 1503 - A PRIMARY KEY must include all columns in the table’s partitioning function

结果报错：主键必须包含分区表分区依据字段（如果根据id进行划分，就可以不使用联合主键，在本文Hash分区中采用该种方式），尝试联合主键
修改代码如下：

CREATE TABLE liu_t_fenqu
(
    id INT NOT NULL 
    ,name VARCHAR(20)
    ,age VARCHAR(20)
    ,birthdate date NOT NULL
    ,salary INT
    ,PRIMARY KEY(id,birthdate)
)
PARTITION by RANGE(year(birthdate))
(
    PARTITION p1 VALUES less than (1970)
    ,PARTITION p2 VALUES less than (1990)
    ,PARTITION p3 VALUES less than MAXVALUE
);

运行成功。

对已存在表进行分区操作：

• 首先创建无分区表

CREATE TABLE liu_t_table
(
    id INT
    ,`name` VARCHAR(20)
    ,age INT
    ,datetime date NOT NULL
)
ENGINE=INNODB DEFAULT CHARSET=utf8; 

• 进行分区操作

ALTER TABLE liu_t_table
PARTITION by RANGE(year(datetime))
(
    PARTITION p1 VALUES less than (1970)
    ,PARTITION p2 VALUES less than(1990)
    ,partition p3 VALUES less than maxvalue
);

创建成功；

List分区

列表分区，分局分散的数列值，使用VALUES IN操作符来进行定义。

CREATE TABLE liu_t_list
(
    id INT NOT NULL
    ,`name` VARCHAR(20)
    ,age INT
    ,listNo int NOT NULL
    ,PRIMARY KEY(id,listNo)
)
ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION by list(listNo)
(
    PARTITION p1 VALUES in (1,2,3)
    ,PARTITION p2 VALUES in (4,5,6)
    ,PARTITION p3 VALUES in (7,8,9)
);

Hash分区

根据表中的某个字段来制定分区的数量，使用partitions（注意s）操作符来进行定义。
下例将表平分10个

CREATE TABLE liu_t_hash
(
    id INT NOT NULL PRIMARY KEY
    ,`name` VARCHAR(20)
    ,age INT NOT NULL
)
ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION by HASH(id)
PARTITIONS 10;

Key分区

使用partitions（注意s）操作符来进行定义。

-- Key分区
CREATE TABLE liu_t_key
(
    id INT NOT NULL
    ,age INT
    ,school VARCHAR(20)
)
ENGINE=INNODB DEFAULT charset=utf8
PARTITION by LINEAR KEY(id)
PARTITIONS 10;

对分区表进行操作

添加分区表

对liu_t_list表添加新的分区表，alter…add partition…

-- 添加新的分区p4
ALTER TABLE liu_t_list
ADD PARTITION
(
    PARTITION p4 VALUES in (10,11)
);

删除制定分区表

对liu_t_list表删除分区表，alter…drop partition…

-- 删除制定分区表alter..drop partition..
ALTER TABLE liu_t_list
DROP PARTITION p4;

创建子分区

-- 创建子分区
CREATE TABLE liu_t_sub
(
    id INT(20) NOT NULL 
    ,`name` VARCHAR(20)
    ,age INT(6) not NULL
)
PARTITION by list(id)
subpartition by HASH(age)
subpartitions 3
(
    PARTITION p1 VALUE in (3)
    ,PARTITION p2 VALUES in (6)
);

报错，信息如下：

Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition
语法错误：list分区必须使用values；
修改代码如下：

-- 创建子分区
CREATE TABLE liu_t_sub
(
    id INT(20) NOT NULL 
    ,`name` VARCHAR(20)
    ,age INT(6) not NULL
)
PARTITION by list(id)
subpartition by HASH(age)
subpartitions 3
(
    PARTITION p1 VALUES in (3)
    ,PARTITION p2 VALUES in (6)
);

复合分区

ALTER TABLE liu_t_fenqu
REORGANIZE PARTITION p1,p2 INTO
(PARTITION p1 VALUES less than(1990));