mysql添加删除分区_创建,增加，删除mysql表分区

1 、测试添加分区和删除分区

###添加删除range分区

(1)创建一个分区：

CREATE TABLE titles (

emp_no INT NOT NULL,

title VARCHAR(50) NOT NULL,

from_date DATE NOT NULL,

to_date DATE,

KEY (emp_no),

PRIMARY KEY (emp_no,title, from_date)

) partition by range columns(from_date)

(partition p01 values less than ('1985-12-31'),

partition p02 values less than ('1990-12-31'),

partition p03 values less than ('1995-12-31'),

partition p04 values less than ('2000-12-31'),

partition p05 values less than ('2005-12-31'),

partition p06 values less than ('2010-12-31'),

partition p07 values less than ('2015-12-31'),

partition p08 values less than ('2020-12-31'),

partition p09 values less than ('2025-12-31'),

partition p10 values less than ('2030-12-31')

);

导入数据

mysql> source titles.sql

(2)添加分区：

备注：不能超过p04的范围，严格递增每个分区，即最小不能小于前一个分区

下面新曾了两个分区n01和n02

alter table titles

reorganize partition p04 into(

partition n01 values less than('1997-12-31'),

partition n02 values less than('1998-12-31'),

partition p04 values less than('2000-12-31')

);

(3)删除分区：

删除分区即删除数据。

mysql> select count(*) from titles where from_date

+----------+

| count(*) |

+----------+

| 18238 |

+----------+

1 row in set (0.09 sec)

mysql> alter table titles drop partition p01;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select count(*) from titles where from_date

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.04 sec)

###添加删除list分区

(1)创建list分区

CREATE TABLE titles (

emp_no INT NOT NULL,

title VARCHAR(50) NOT NULL,

from_date DATE NOT NULL,

to_date DATE,

KEY (emp_no),

PRIMARY KEY (emp_no,title, from_date)

) partition by list columns(title)

(

partition p0 values in ('Assistant Engineer'),

partition p1 values in ('Engineer'),

partition p2 values in ('Manager'),

partition p3 values in ('Senior Engineer'),

partition p4 values in ('Senior Staff'),

partition p5 values in ('Staff'),

partition p6 values in ('Technique Leader')

);

导入数据

titles.sql 是employees数据库导出的数据

mysql> source titles.sql

(2)添加分区

mysql> alter table titles add partition(partition p7 values in('CEO'));

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

(3)删除分区：

mysql> alter table titles drop partition p0;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

###添加删除hash分区

(1)创建hash分区

CREATE TABLE titles (

emp_no INT NOT NULL,

title VARCHAR(50) NOT NULL,

from_date DATE NOT NULL,

to_date DATE,

KEY (emp_no),

PRIMARY KEY (emp_no,title, from_date)

) partition by hash(emp_no)

partitions 4;

导入数据：

mysql> source titles.sql

(2)删除hash分区

4个分区减少到两个，4-2=2

mysql> alter table titles coalesce partition 2;

Query OK, 443308 rows affected (12.41 sec)

Records: 443308 Duplicates: 0 Warnings: 0

(3)添加hash分区

增加到5个，2+3=5

mysql> alter table titles add partition partitions 3;

Query OK, 443308 rows affected (11.54 sec)

Records: 443308 Duplicates: 0 Warnings: 0

注：添加hash分区时，mysql会生成新分区文件，并把所有数据重新hash到所有分区，不需要手动重新分布数据。

###添加删除key分区

(1)创建key分区

CREATE TABLE titles (

emp_no INT NOT NULL,

title VARCHAR(50) NOT NULL,

from_date DATE NOT NULL,

to_date DATE,

KEY (emp_no),

PRIMARY KEY (emp_no,title, from_date)

) partition by key(emp_no)

partitions 4;

导入数据

mysql> source titles.sql

(2)删除分区，同hash

mysql> alter table titles coalesce partition 2;

Query OK, 443308 rows affected (7.86 sec)

Records: 443308 Duplicates: 0 Warnings: 0

(3)添加分区，同hash

mysql> alter table titles add partition partitions 3;

Query OK, 443308 rows affected (6.17 sec)

Records: 443308 Duplicates: 0 Warnings: 0

2、查询所有数据所在分区

explain partitions

select * from user where id= 1001;

3、指定分区查询

select * from user PARTITION(p0);