mysql 之项目分区表演示

作者: adm 分类: mysql 发布时间: 2021-06-04 09:55

上一篇讲解了mysql 之Hash分区、RANGE分区、LIST分区,现在继续深入讲解mysql 之项目分区表演示

登录日志的分区

如何为Customer_login_log表分区?

从以下两个业务场景入手:

  •     用户每次登陆都会记录customer_login_log入职
  •     用户登录日志保存一年,一年后可以删除

1、登录日志表的分区类型及分区键确定:

    分区类型: 使用RANGE分区

login_time作为分区键

2、创建分区表:

CREATE TABLE `crn`.`customer_login_log`(
	customer_id INT UNSIGNED not null,
	login_time datetime not null,
	login_ip int UNSIGNED NOT NULL,
	login_type TINYINT NOT NULL
)ENGINE = INNODB
PARTITION BY RANGE(YEAR(login_time))(
PARTITION p0 values less than(2015),
PARTITION p2 VALUES less than(2016),
PARTITION p3 VALUES less than(2017));

结果截图:

插入分区数据:

INSERT INTO customer_login_log(customer_id,login_time,login_ip,login_type)
VALUES(1001,'2015-01-25',0,1),(1001,'2015-07-1',0,1),(1001,'2015-10-1',0,1),(1001,'2016-3-1',0,1),(1001,'2016-9-1',0,1);

默认匹配规则说明:

创建2条2020年的数据,

INSERT INTO customer_login_log(customer_id,login_time,login_ip,login_type)
VALUES(1001,'2020-01-25',0,1),(1001,'2020-07-1',0,1);

创建分区范围分别为2019及2021年的分区:

ALTER TABLE customer_login_log add PARTITION(PARTITION p5 values less than(2019));
ALTER TABLE customer_login_log add PARTITION(PARTITION p6 values less than(2021));

最终匹配结果:

新创建的2020年的数据都被匹配到了2021年的分区区间,这是由于在没有创建相应分区的情况下,其会默认匹配到最近的规则的分区区域。有鉴于此,当创建的时间信息超出当前已定义的范围时,需根据规则及时创建新的分区,已规范数据的管理。

3、删除分区–同步删除分区内数据:

ALTER TABLE customer_login_log drop PARTITION p6;

分区表被删除:

在这里对过期数据的删除不需要通过在数据库进行查询等操作,提高了对数据的处理效率,减少了不必要的运算操作

3、分区数据迁移

创建新分区表:arch_customer_login_log

CREATE TABLE arch_customer_login_log(
    customer_id INT UNSIGNED not null,
    login_time datetime not null,
    login_ip int UNSIGNED NOT NULL,
    login_type TINYINT NOT NULL
)ENGINE = INNODB

当前customer_login_log 分区表中的数据:

这里将p3的数据迁移到新表中:

ALTER table customer_login_log exchange PARTITION p3 WITH TABLE arch_customer_login_log;

迁移后的原表 customer_login_log

迁移后的新表arch_customer_login_log

新表arch_customer_login_log的分区信息:

由截图可知,分区表表名为空、归档规则为空;数据量为2条

实现分区迁移的两个条件:

①mysql版本要大于5.7;

②归档的分区日志表要属于非分区表,归档的分区表和迁移的分区表数据结构必须相同,并且不能有外键约束;

满足以上两个条件的多个分区之间就可以进行分区数据的迁移了.

归档分区表到相应的存储引擎:

ALTER TABLE arch_customer_login_log ENGINE=archive

使用分区表的注意事项:

  1.   结合业务场景选择分区键,避免跨分区查询;
  2.  对分区表进行查询最好在WHERE从句中包含分区键;
  3.  具有主键或唯一索引的键,主键或唯一索引必须是分区键的一部分。

如果觉得我的文章对您有用,请随意赞赏。您的支持将鼓励我继续创作!