Mysql实现范围分区表(新增、删除、重组、查看)！
Mysql实现范围分区表(新增、删除、重组、查看)！
作者：@睡不醒的每天@
MySQL分区表的四种类型（范围、哈希、列表、键值）,主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解一下。
一、mysql分区表分类
- 范围分区（Range Partitioning
- 哈希分区（Hash Partitioning）
- 列表分区（List Partitioning）
- 键值分区（ Key partition ）
二、范围分区（Range Partitioning
Range partition是按照分区表达式的运算结果，判断结果落在某个范围内，从而将数据存储在对应的分区。各个分区定义之间需要连续且不能重叠，范围分区通过partition by range子句定义，而分区的范围通过values less than子句划分。
1、新建分区表：
定义一个员工表，根据员工ID分区，1-10号员工一个分区，11~20号员工一个分区，依次类推，共建立4个分区：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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、分区结构查询：
|
1
|
show create table test.emp\G; |
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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分区
|
1
2
3
4
5
6
7
8
9
10
|
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、查看分区表的生成文件
|
1
2
3
4
5
6
7
8
9
|
[root@mysql5 /]# cd /data/mysql_data/test[root@mysql5 test]# lltotal 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、添加分区：
|
1
|
alter table emp add partition (partition p4 values less than(51)); |
6、查看分区
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> show create table test.emp\G;*************************** 1. row ***************************Table: empCreate 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) |
新建分区新增成功
三、删除分区表和删除分区：
1、直接删除分区表：
|
1
|
drop table emp; |
2、分区删除语句：
|
1
|
alter table emp drop partition p1; |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> alter table emp drop partition p1;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table test.emp\G;*************************** 1. row ***************************Table: empCreate 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:
|
1
2
3
|
alter table emp reorganize partition p2 into (partition p1 values less than(21),partition p2 values less than(31)); |
到此这篇关于Mysql实现范围分区表(新增、删除、重组、查看)的文章就介绍到这了。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
你将获得：
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需129元，即可下载本站文章涉及的文件和软件。