Mysql分区表------范围分区表（新增、删除、重组、查看）

Mysql分区表------范围分区表（新增、删除、重组、查看）

码农世界 2024-06-05 后端 115 次浏览 0个评论

文章目录

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

                一、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个分区：

                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)

                新建分区新增成功

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

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

转载请注明来自码农世界，本文标题：《Mysql分区表------范围分区表（新增、删除、重组、查看）》

百度分享代码，如果开启HTTPS请参考李洋个人博客
37815篇文章 站点 微博
每一天，每一秒，你所做的决定都会改变你的人生！

发表评论

快捷回复：

评论列表 （暂无评论，115人围观）参与讨论

还没有评论，来说两句吧...

Top