高性能可扩展mysql 笔记（四）项目分区表演示

红太狼 2023-06-05 11:08 6阅读 0赞

###  个人博客网：[https://wushaopei.github.io/][https_wushaopei.github.io]    (你想要这里多有) ###

### 登录日志的分区 ###

**如何为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));

结果截图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70][]

插入分区数据：

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 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));

最终匹配结果：

![20191008220539196.png][]

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

3、删除分区--同步删除分区内数据：

ALTER TABLE customer_login_log drop PARTITION p6;

分区表被删除：

![20191008220650551.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2][]

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

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 分区表中的数据：

![20191008221024436.png][]

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

ALTER table customer_login_log exchange PARTITION p3 WITH TABLE arch_customer_login_log;

迁移后的原表 customer\_login\_log

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3][]

迁移后的新表arch\_customer\_login\_log

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 4][]

新表arch\_customer\_login\_log的分区信息：

![2019100822120249.png][]

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

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

> ①mysql版本要大于5.7；
> 
> ②归档的分区日志表要属于非分区表，归档的分区表和迁移的分区表数据结构必须相同，并且不能有外键约束；

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

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

ALTER TABLE arch_customer_login_log ENGINE=archive

使用分区表的注意事项：

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

[https_wushaopei.github.io]: https://wushaopei.github.io/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70]: /images/20230601/f90349598ba14f8a841134e04100e6e5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1]: /images/20230601/d5f1466b681a43ddab295533c43f8bfc.png
[20191008220539196.png]: /images/20230601/c97c150fde7a4851afa2cf4b74b2f9fe.png
[20191008220650551.png]: /images/20230601/77a1c77761804933a0881dbb0292e884.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2]: /images/20230601/e00f1dac20f1430c9978219a8345b5fc.png
[20191008221024436.png]: /images/20230601/15587b25a8eb46f3a602ba570526162d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3]: /images/20230601/adce29d0510d494eb9b0aa26a088e54a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 4]: /images/20230601/019c722817444a1ea94a274e89a8cfb5.png
[2019100822120249.png]: /images/20230601/396d085ef96248de933fd4f7bc089721.png