MySQL从入门到精通（五）

最新推荐文章于 2023-11-18 21:39:59 发布

Coder-michael

最新推荐文章于 2023-11-18 21:39:59 发布

阅读量134

点赞数

分类专栏： Mysql 文章标签：数据库 mysql 分区表

本文链接：https://blog.csdn.net/weixin_46036165/article/details/108900812

版权

Mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

MySQL数据库分区表应用

1. 分区表介绍

MySQL自己本身是支持表的逻辑分区的

查看数据库是否支持分区表

mysql> show plugins;

为什么要使用分区表：

是否遇到几千万的大表
查询困难，历史数据是不太关心的
如果历史数据要归档，将数据从原来的库中挪走
先将一年以前的数查询出来，然后先备份到目标相应的备份表中
可以直接使用mysqldump来备份成sql脚本
原表的备份数据一般久不要了
- 删除会导致数据库性能下降
- innodb的数据如果做删除，不会真正的删除，知会将数据标记删除，磁盘的文件大小不会缩小，新插入数据会将已标记的空间进行复用
- mysql> OPTIMIZE table table_name;
我们如果有这样一种文件组织形式：2017年的数据放一个文件，2018年的数据放一个文件，2019年一个，2020年一个，可以按照文件来进行管理并再需要的时候将文件删除，并且可以指定查询的时间范围到某个具体的数据文件上

这个时候就可以通过MySQL提供的分区表实现

分区表的分区类型

HASH分区
LIST分区
RANGE分区
KEY分区

6.2. HASH分区

根据MOD将分区键计算后分到制定表区域
可以基本平均的分布
HASH分区键值必须是INT类型，或者通过函数转成INT

CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(customer_id) PARTITIONS 4;

insert into customer_login_log values(1,'2020-06-20 22:30:01',1,1);
insert into customer_login_log values(2,'2020-06-20 22:30:02',2,1);
insert into customer_login_log values(3,'2020-06-20 22:30:03',3,1);
insert into customer_login_log values(4,'2020-06-20 22:30:04',4,1);

创建以后的内容

-rw-r----- 1 mysql mysql  8767 Jun 20 22:30 customer_login_log.frm
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p0.ibd
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p1.ibd
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p2.ibd
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p3.ibd

查看分区表是否创建成功

mysql> explain partitions select * from customer_login_log;

在这里插入图片描述

查询每个分区多少数据

select table_name,partition_name,partition_description,table_rows from information_schema.PARTITIONS where table_name='customer_login_log';

在这里插入图片描述

查询具体的某个分区数据

select * from customer_login_log;
select * from customer_login_log partition(p1,p2);
select * from customer_login_log partition(p3) where customer_id=3;

在这里插入图片描述

6.3. LIST分区

按照分区键的枚举来进行分区的
各分区的列表不能重复
每一行数据都必须要找到对应的分区才能插入数据

CREATE TABLE `customer_login_log_list` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(login_type)(
	PARTITION jishu VALUES in (1,3,5,7,9),
	PARTITION oushu VALUES in (2,4,6,8)
);

insert into customer_login_log_list values(1,'2020-06-20 22:30:01',1,1);
insert into customer_login_log_list values(2,'2020-06-20 22:30:02',2,2);
insert into customer_login_log_list values(3,'2020-06-20 22:30:03',3,3);
insert into customer_login_log_list values(4,'2020-06-20 22:30:04',4,4);

如果分区键不在分区中

mysql> insert into customer_login_log_list values(4,'2020-06-20 22:30:04',4,0);
ERROR 1526 (HY000): Table has no partition for value 0

6.4. RANGE分区

根据分区的不同范围值将数据放不同文件中
多个分区要连续，不能重叠
要有封口的MAXVALUE

CREATE TABLE `customer_login_log_range` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(login_time))(
	PARTITION y2017 VALUES LESS THAN (2017),
	PARTITION y2018 VALUES LESS THAN (2018),
	PARTITION y2019 VALUES LESS THAN (2019),
	PARTITION y2020 VALUES LESS THAN (2020),
	PARTITION maxyear VALUES LESS THAN MAXVALUE
);

insert into customer_login_log_range values(1,'2016-06-20 22:30:01',1,1);
insert into customer_login_log_range values(2,'2017-06-20 22:30:02',2,2);
insert into customer_login_log_range values(3,'2018-06-20 22:30:03',3,3);
insert into customer_login_log_range values(5,'2019-06-20 22:30:04',4,4);
insert into customer_login_log_range values(6,'2020-06-20 22:30:04',4,4);
insert into customer_login_log_range values(7,'2021-06-20 22:30:04',4,4);
insert into customer_login_log_range values(8,'2022-06-20 22:30:04',4,4);
insert into customer_login_log_range values(9,'2023-06-20 22:30:04',4,4);

如果我们需要后期新增range分区，就不能maxvalue封口

CREATE TABLE `customer_login_log_range1` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(login_time))(
	PARTITION y2017 VALUES LESS THAN (2017),
	PARTITION y2018 VALUES LESS THAN (2018),
	PARTITION y2019 VALUES LESS THAN (2019),
	PARTITION y2020 VALUES LESS THAN (2020)
);

alter table customer_login_log_range1 add PARTITION(
PARTITION y2021 VALUES LESS THAN (2021),
PARTITION y2022 VALUES LESS THAN (2022),
PARTITION y2023 VALUES LESS THAN (2023)
)

一个没有创建分区的表

CREATE TABLE `customer_login_log_range_no` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into customer_login_log_range_no values(1,'2016-06-20 22:30:01',1,1);
insert into customer_login_log_range_no values(2,'2017-06-20 22:30:02',2,2);
insert into customer_login_log_range_no values(3,'2018-06-20 22:30:03',3,3);
insert into customer_login_log_range_no values(5,'2019-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(6,'2020-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(7,'2021-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(8,'2022-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(9,'2023-06-20 22:30:04',4,4);

alter table customer_login_log_range_no PARTITION BY RANGE(YEAR(login_time))(
	PARTITION y2017 VALUES LESS THAN (2017),
	PARTITION y2018 VALUES LESS THAN (2018),
	PARTITION y2019 VALUES LESS THAN (2019),
	PARTITION y2020 VALUES LESS THAN (2020),
	PARTITION y2021 VALUES LESS THAN (2021),
	PARTITION y2022 VALUES LESS THAN (2022),
	PARTITION maxyear VALUES LESS THAN MAXVALUE
)
# 数据会按照分区规则进行数据重新组装，数据会进入相应分区

如果要删除分区使用命令，不能直接删除文集（数据丢失或者无法访问）
如果我们要删除分区前需要进行数据归档，如何来做
MySQL v5.7以后加入了一个分区交换的概念

分区交换表的表结构和存储引擎与分区表相同
归档的这个表不能是分区表
归档表不能有外键约束
归档表导出数据后可以改成ARCHIVE引擎

-- 创建归档表
CREATE TABLE `customer_login_log_2017` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 交换分区进入归档表
alter table customer_login_log_range exchange partition y2017 with table customer_login_log_2017

-- 修改归档表引擎为归档引擎
alter table customer_login_log_2017 ENGINE=ARCHINVE;

-- 删除分区文件
alter table customer_login_log_range drop partition y2017;

5. 分区表注意事项和限制

如果表里有主键必须包含分区表的分区键
很多时候，入宫使用分区表久不要使用主键，建立主键可能会印象性能
不要建立过的分区
分区表不支持外键
分区规则最好提前设置好，中间要进行修改会比较麻烦

Coder-michael

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL从入门到精通（五）

MySQL数据库分区表应用1. 分区表介绍MySQL自己本身是支持表的逻辑分区的查看数据库是否支持分区表mysql> show plugins;为什么要使用分区表：是否遇到几千万的大表查询困难，历史数据是不太关心的如果历史数据要归档，将数据从原来的库中挪走先将一年以前的数查询出来，然后先备份到目标相应的备份表中可以直接使用mysqldump来备份成sql脚本原表的备份数据一般久不要了删除会导致数据库性能下降innodb的数据如果做删除，不会真正的删除，知会将数
复制链接

扫一扫

专栏目录