Mysql实现范围分区表(新增、删除、重组、查看)

目录

• 一、mysql分区表分类
• 二、范围分区（Range Partitioning
• 1、新建分区表：
• 2、分区结构查询：
• 3、根据分区查数据
• 4、查看分区表的生成文件
• 5、添加分区：
• 6、查看分区
• 三、删除分区表和删除分区：
• 1、直接删除分区表：
• 2、分区删除语句：
• 3、分区重组织：

一、mysql分区表分类

• 范围分区（Range Partitioning
• 哈希分区（Hash Partitioning）
• 列表分区（List Partitioning）
• 键值分区（ Key partition ）

二、范围分区（Range Partitioning

Range partition是按照分区表达www.devze.com式的运算结果，判断结果落在某个范围内，从而将数据存储在对应的分区。各个分区定义之间需要连续且不能重叠，范围分区通过partition by range子句定义，而分区的范围通过values less than子句划分。

1、新建分区表：

定义一个员工表，根据员工ID分区，1-10号员工一个分区，11~20号员工一个分区，依次类推，共建立4个分区：

create table emp (
    id int primary key,
    name varchar(30),
    hire_date date)
    partition by range(id)(
    partition p0 values less than (11),
    partition p1 values less than (21),
    partition p2 values less than (31),
    partition p3 values less than (41)
);
    
现在随便插入几条数据：

insert into emp values(1,'Jim','2022-01-02');
insert into emp values(2,'Ora','2022-02-02');
insert into emp values(11,'Grant','2021-03-02');
insert into emp values(16,'Sun','2021-05-02');
insert into emp values(22,'Han','2020-10-02');
insert into emp values(35,'MJ','2020-11-02');
insert into emp values(45,'BABALA','2019-12-02');
commit;

2、分区结构查询：

show create table test.emp\G;

Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (21) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (31) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (41) ENGINE = InnoDB) */
1 row in set (0.00 sec)

表的分区结构中新建的4个分区已新建

3、根据分区查数据

select * from emp partition(p0); – 查询p0分区

select * from emp partition(p0,p1); – 查询p0和p1分区

mysql> select * from emp partition(p0,p1);
+----+-------+------------+
| id | name  | hire_date  |
+----+-------+------------+
|  1 | Jim   | 2022-01-02 |
|  2 | Ora   | 2022-02-02 |
| 11 | Grant | 2021-03-02 |
| 16 | Sun   | 2021-05-02 |
+----+-------+------------+
4 rows in set (0.00 sec)

根据以上命令可以查询分区的相关数据库信息

4、查看分区表的生成文件

[root@mysql5 /]# cd /data/mysql_data/test
[root@mysql5 test]# ll
total 400
-rw-r-----. 1 mysql mysql    67 Jul 14 09:03 db.opt
-rw-r-----. 1 mysql mysql  8626 Jul 14 09:03 emp.frm
-rw-r-----. 1 mysql mysql 98304 Jul 14 09:25 emp#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 Jul 14 09:25 emp#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 Jul 14 09:25 emp#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 Jul 14 09:25 emp#P#p3.ibd

由此可见4个分区文件生成

5、添加分区：

alter table emp add partition (partition p4 values less than(51));

6、查看分区

mysql> show create table test.emp\G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (21) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (31) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (41) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (51) ENGINE = InnoDB) */
1 row in set (0.00 sec)

新建分区新增成功

三、删除js分区表和删除分区：

1、直接删除分区表：

drop table emp;

2、分区删除语句：

alter table emp drop partition p1;

mysql> alter table emp drop partition p1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  show create table test.emp\G;
*************************** 1. row ***************************
       Tajavascriptble: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (31) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (41) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (51) ENGINE = InnoDB) */
1 row in set (0.00 sec)

当我们删除p1分区以后，p1分区原来的分区区间定义将由他后面的分区定义区间所继承。而且数据将会被删除。

3、分区重组织：

如果一定要在分区之间插入新的分区，则可以采用重组织的方式，将已有分区的数据重新划分，达到创建新分区的效果：

例如我要将p2划分为2个分区，分别是11-20,21~30:

alter table emp reorganize partition p2 into (
partition p1 values less than(21),
partition p2 values less than(31))javascript;

到此这篇关于Mysql实现范围分区表(新增、删除、重组、查看)的文章就介绍到这了,更多相关Mysql 范围分区表内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！