mysql常用分区方式partition

by · 2019年04月22日 · 906 Words · ~2min reading time | Improve on


原文链接： mysql常用分区方式partition

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));

Tags:

`