MySQL分区表

柔情只为你懂 2023-09-30 19:31 4阅读 0赞

> 在最近的项目中，我们需要保存大量的数据，而且这些数据是有有效期的，为了提供查询效率以及快速删除过期数据，我们选择了MySQL的分区机制。把数据按照时间进行分区。

### 分区类型 ###

--------------------

*  **Range分区**：最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。
 *  **List分区**：LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。
 *  **Hash分区**：基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。
 *  Key分区
    
    ：KEY分区其实跟HASH分区差不多，不同点如下：
    
     *  KEY分区允许多列，而HASH分区只允许一列。
     *  如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
     *  KEY分区对象必须为列，而不能是基于列的表达式。
     *  KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column\_list)，基于的是列的MD5值。

### 分区命令 ###

*  创建分区

CREATE TABLE `access_log` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `access_time` datetime NOT NULL,
      PRIMARY KEY (`id`,`access_time`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    /*!50100 PARTITION BY RANGE (to_days(access_time))
    (PARTITION p1 VALUES LESS THAN (to_days(20190101)) ENGINE = InnoDB,
     PARTITION p2 VALUES LESS THAN (to_days(20190102)) ENGINE = InnoDB,
     PARTITION p3 VALUES LESS THAN (to_days(20190103)) ENGINE = InnoDB) */;

创建后可以看到，每个分区都会对应1个ibd文件

![img][]

分区表

*  新增分区

alter table access_log add partition(
        partition p4 values less than (to_days('20190105'))
    );

*  删除分区

alter table access_log drop partition p1;

*  拆分分区

alter table access_log reorganize partition p4 into(
        -> partition s0 values less than(to_days('20190104')),
        -> partition s1 values less than(to_days('20190105'))
        -> );

*  合并分区

alter table access_log reorganize partition s0,s1 into ( 
        partition p4 values less than (to_days('20190105')) 
    );

### 注意事项 ###

*  MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中（否则判断主键或唯一时，需要扫描所有分区）
 *  分区字段**不能为`NULL`**，要不然怎么确定分区范围呢，所以尽量NOT NULL
 *  最大分区数目不能超过**1024**
 *  不支持外键
 *  只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列
 *  分区表**不**影响自增列

### 常见问题 ###

*  `A PRIMARY KEY must include all columns in the table's partitioning function`：这样的话判断主键是否唯一就可以在单个分区内部完成，否则就需要跨所有的分区
 *  `MAXVALUE can only be used in last partition definition`：RANGE表分区后不能带MAXVALUE分区，否则无法增加分区。或者就只能重新分区了

alter table access_log partition by range(to_days(access_time))(
    partition p1 values less than (to_days('20191202')), 
    partition p2 values less than (to_days('20191203')), 
        partition po values less than (maxvalue)
    )

*  `Table has no partition for value 737425`：因为分区的范围没有包含所有可能的记录的值

--------------------

### MySQL · 最佳实践 · 分区表基本类型 ###

### MySQL分区表概述 ###

随着MySQL越来越流行，Mysql里面的保存的数据也越来越大。在日常的工作中，我们经常遇到一张表里面保存了上亿甚至过十亿的记录。这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情，由于所有的数据都一个普通的表里。所以只能是启用一个或多个带where条件的delete语句去删除（一般where条件是时间）。 这对数据库的造成了很大压力。即使我们把这些删除了，但底层的数据文件并没有变小。面对这类问题，最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。 分区一个最大的优点就是可以非常高效的进行历史数据的清理。

### 分区类型 ###

目前MySQL支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及KEY分区四种。下面我们逐一介绍每种分区：

#### RANGE分区 ####

基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。本例中使用to\_days函数

CREATE TABLE my_range_datetime(
        id INT,
        hiredate DATETIME
    ) 
    PARTITION BY RANGE (TO_DAYS(hiredate) ) (
        PARTITION p1 VALUES LESS THAN ( TO_DAYS('20171202') ),
        PARTITION p2 VALUES LESS THAN ( TO_DAYS('20171203') ),
        PARTITION p3 VALUES LESS THAN ( TO_DAYS('20171204') ),
        PARTITION p4 VALUES LESS THAN ( TO_DAYS('20171205') ),
        PARTITION p5 VALUES LESS THAN ( TO_DAYS('20171206') ),
        PARTITION p6 VALUES LESS THAN ( TO_DAYS('20171207') ),
        PARTITION p7 VALUES LESS THAN ( TO_DAYS('20171208') ),
        PARTITION p8 VALUES LESS THAN ( TO_DAYS('20171209') ),
        PARTITION p9 VALUES LESS THAN ( TO_DAYS('20171210') ),
        PARTITION p10 VALUES LESS THAN ( TO_DAYS('20171211') )，
        PARTITION p11 VALUES LESS THAN (MAXVALUE) 
    );

p11是一个默认分区，所有大于20171211的记录都会在这个分区。MAXVALUE是一个无穷大的值。p11是一个可选分区。如果在定义表的没有指定的这个分区，当我们插入大于20171211的数据的时候，会收到一个错误。

我们在执行查询的时候，必须带上分区字段。这样可以使用分区剪裁功能

注意执行计划中的partitions的内容，只查询了p7，p8，p9，p10三个分区，由此来看，使用to\_days函数确实可以实现分区裁剪。

上面是基于datetime的，如果是timestamp类型，我们遇到上面问题呢？

事实上，MySQL提供了一种基于UNIX\_TIMESTAMP函数的RANGE分区方案，而且，只能使用UNIX\_TIMESTAMP函数，如果使用其它函数，譬如to\_days，会报如下错误：“ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”。

而且官方文档中也提到“Any other expressions involving TIMESTAMP values are not permitted. (See Bug \#42849.)”。

下面来测试一下基于UNIX\_TIMESTAMP函数的RANGE分区方案，看其能否实现分区裁剪。

针对TIMESTAMP的分区方案

创表语句如下：

CREATE TABLE my_range_timestamp (
        id INT,
        hiredate TIMESTAMP
    )
    PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (
        PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-02 00:00:00') ),
        PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-03 00:00:00') ),
        PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-04 00:00:00') ),
        PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-05 00:00:00') ),
        PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-06 00:00:00') ),
        PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-07 00:00:00') ),
        PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-08 00:00:00') ),
        PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-09 00:00:00') ),
        PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-10 00:00:00') ),
        PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-11 00:00:00') )
    );

插入数据并查看上述查询的执行计划

同样也能实现分区裁剪。

在5.7版本之前，对于DATA和DATETIME类型的列，如果要实现分区裁剪，只能使用YEAR() 和TO\_DAYS()函数，在5.7版本中，又新增了TO\_SECONDS()函数。

#### LIST 分区 ####

LIST分区

LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。二者在语法方面非常的相似。同样建议LIST分区列是非null列，否则插入null值如果枚举列表里面不存在null值会插入失败，这点和其它的分区不一样，RANGE分区会将其作为最小分区值存储，HASH\\KEY分为会将其转换成0存储，主要LIST分区只支持整形，非整形字段需要通过函数转换成整形.

create table t_list( 
    　　a int(11), 
    　　b int(11) 
    　　)(partition by list (b) 
    　　partition p0 values in (1,3,5,7,9), 
    　　partition p1 values in (2,4,6,8,0) 
    　　);

#### Hash 分区 ####

我们在实际工作中经常遇到像会员表的这种表。并没有明显可以分区的特征字段。但表数据有非常庞大。为了把这类的数据进行分区打散mysql 提供了hash分区。基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。表达式可以是mysql中任意有效的函数或者表达式，对于非整形的HASH往表插入数据的过程中会多一步表达式的计算操作，所以不建议使用复杂的表达式这样会影响性能。

Hash分区表的基本语句如下：

CREATE TABLE my_member (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30),
        created DATE NOT NULL DEFAULT '1970-01-01',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT,
        store_id INT
    )
    PARTITION BY HASH(id)
    PARTITIONS 4;

注意：

1.  HASH分区可以不用指定PARTITIONS子句，如上文中的PARTITIONS 4，则默认分区数为1。
2.  不允许只写PARTITIONS，而不指定分区数。
3.  同RANGE分区和LIST分区一样，PARTITION BY HASH (expr)子句中的expr返回的必须是整数值。
4.  HASH分区的底层实现其实是基于MOD函数。譬如，对于下表

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; 如果你要插入一个col3为“2017-09-15”的记录，则分区的选择是根据以下值决定的：

MOD(YEAR(‘2017-09-01’),4) = MOD(2017,4) = 1

##### LINEAR HASH分区 #####

LINEAR HASH分区是HASH分区的一种特殊类型，与HASH分区是基于MOD函数不同的是，它基于的是另外一种算法。

格式如下：

CREATE TABLE my_members (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30),
        hired DATE NOT NULL DEFAULT '1970-01-01',
        separated DATE NOT NULL DEFAULT '9999-12-31',
        job_code INT,
        store_id INT
    )
    PARTITION BY LINEAR HASH( id )
    PARTITIONS 4;

说明： 它的优点是在数据量大的场景，譬如TB级，增加、删除、合并和拆分分区会更快，缺点是，相对于HASH分区，它数据分布不均匀的概率更大。

#### KEY分区 ####

KEY分区其实跟HASH分区差不多，不同点如下：

1.  KEY分区允许多列，而HASH分区只允许一列。
2.  如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
3.  KEY分区对象必须为列，而不能是基于列的表达式。
4.  KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column\_list)，基于的是列的MD5值。

格式如下：

CREATE TABLE k1 (
        id INT NOT NULL PRIMARY KEY,    
        name VARCHAR(20)
    )
    PARTITION BY KEY()
    PARTITIONS 2;

在没有主键或者唯一键的情况下，格式如下：

CREATE TABLE tm1 (
        s1 CHAR(32)
    )
    PARTITION BY KEY(s1)
    PARTITIONS 10;

### 总结： ###

1.  MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中。
2.  对于原生的RANGE分区，LIST分区，HASH分区，分区对象返回的只能是整数值。
3.  分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL

--------------------

#### **问题与背景：** ####

1.  单张表数据量太大，每天会产生 10W 条记录，一年就是 3650W 条记录，
2.  对这张表的查询 95% 都是在某一天或者几天内，过滤区间最大不超过一个月。比如在2019年3月1日、2019年4 月20 日或者是2019年5月1日和2019年5月5日这个时间段内。偶尔会涉及到跨月、跨年查询，但是频率很低。
3.  记录保留10年。也就是单表3.6亿条记录，单表太大，不便于管理，后期如果单表损坏，修复也难。
4.  单表查询性能很差，对历史数据删除性能也很差。

#### **基于以上需求分析后得出结论：** ####

1.  查询过滤的数据范围相对比较集中，不是那么分散；要同时考虑过期数据清理性能问题。
2.  考虑把表拆分为10张新表，一张是当前表，剩余9张是历史归档表；当前表存放最近两年的数据，每到年底迁移老旧数据到历史表进行归档，并且对过期历史数据进行清理。
3.  考虑对部分过滤场景使用 MySQL 分区表，非常适合 95% 的查询；可以使用分区置换功能把数据移到历史表。
4.  分区表带来几个好处： 一是查询性能提升；二是管理方便，过期数据直接快速清理；三是对应用透明，暂时不需要应用改代码。

#### **接下来看看表的优化过程：** ####

由于隐私考虑，不方便贴原始表结构，这里用结构简化的示例表来看下优化过程。原始表为 pt\_old ，缩减字段个数到3，记录数缩减10倍为 3650W ，每年365W（客户原来字段有30个，记录数3.6亿），记录范围从2011年到2020年，刚好十年的数据。

(localhost:ytt)<mysql>show create table pt_old\G
    *************************** 1. row ***************************
           Table: pt_old
    Create Table: CREATE TABLE `pt_old` (
      `id` bigint unsigned NOT NULL AUTO_INCREMENT,
      `r1` int DEFAULT NULL,
      `log_date` date DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_log_date` (`log_date`)
    ) ENGINE=InnoDB AUTO_INCREMENT=64306811 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1 row in set (0.00 sec)
    
    (localhost:ytt)<mysql>select min(log_date),max(log_date),count(*) from pt_old;
    +---------------+---------------+----------+
    | min(log_date) | max(log_date) | count(*) |
    +---------------+---------------+----------+
    | 2011-01-01    | 2020-12-31    | 36500000 |
    +---------------+---------------+----------+
    1 row in set (21.14 sec)

先导出原始表数据（按照年导出10份数据），后期直接导入到新分区表，执行以下脚本：

root@ytt-unbuntu:/home/ytt/scripts# cat pt_export 
    #!/bin/sh
    for i in `seq 2011 2020` 
    do 
    	{
    		mysql -D ytt -e "select * from pt_old where log_date between '$i-01-01' and '$i-12-31' into outfile '/var/lib/mysql-files/pt_$i.csv' fields terminated by ',' " 
    	} &
    done
    wait
    root@ytt-unbuntu:/home/ytt/scripts# ./pt_export
    root@ytt-unbuntu:/var/lib/mysql-files# ls -sihl
    总用量 788M
    5767677 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:39 pt_2011.csv
    5775332 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2012.csv
    5775334 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2013.csv
    5774596 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2014.csv
    5775335 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2015.csv
    5775333 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2016.csv
    5775329 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2017.csv
    5775330 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2018.csv
    5775336 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2019.csv
    5775331 79M -rw-r----- 1 mysql mysql 79M 2月   4 15:42 pt_2020.csv

分别以年为粒度，建立10张表，其中表 pt\_2020 为分区表：

root@ytt-unbuntu:/home/ytt/scripts# for i in `seq 2011 2020`;do mysql -e"use ytt;create table pt_$i like pt_old;";done;

由于 MySQL 分区表硬性规定，分区键必须为主键或者主键的一部分，把时间字段加到主键里。

(localhost:ytt)<mysql>alter table pt_2020 drop primary key, add primary key (id,log_date);
    Query OK, 0 rows affected (0.29 sec)
    Records: 0  Duplicates: 0  Warnings: 0

给表 pt\_2020 添加分区（有可能存放当年以及去年的数据，因此要按照天来分区，并且分成两年，这样到了新的一年，就直接把老旧数据迁移出去），修改下之前的存储过程如下：

DELIMITER $$
    
    USE `ytt`$$
    
    DROP PROCEDURE IF EXISTS `sp_add_partition_pt_current`$$
    
    CREATE DEFINER=`root`@`%` PROCEDURE `sp_add_partition_pt_current`(
    IN f_year_start YEAR,
    IN f_year_end YEAR,
    IN f_tbname VARCHAR(64)
    )
    BEGIN
    DECLARE v_days INT UNSIGNED DEFAULT 365;
    DECLARE v_year DATE DEFAULT '2011-01-01';
    DECLARE v_partition_name VARCHAR(64) DEFAULT '';
    DECLARE v_log_date DATE;
    DECLARE i,j INT UNSIGNED DEFAULT 1;
      SET @stmt = '';
      SET @stmt_begin = CONCAT('ALTER TABLE ',f_tbname,' PARTITION BY RANGE COLUMNS(log_date)(');
      SET i = f_year_start;
      WHILE i <= f_year_end DO
        SET v_year = CONCAT(i,'-01-01');
        SET v_days = DATEDIFF(DATE_ADD(v_year,INTERVAL 1 YEAR),v_year);
        SET j = 1;
        WHILE j <= v_days DO
          SET v_log_date = DATE_ADD(v_year,INTERVAL j DAY);
          SET v_partition_name = CONCAT('p',i,'_',LPAD(j,3,'0'));
          SET @stmt = CONCAT(@stmt,'PARTITION ',v_partition_name,' VALUES LESS THAN(''',v_log_date,'''),');
          SET j = j + 1;
        END WHILE;
        SET i = i + 1;
      END WHILE;
      SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
      SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
      PREPARE s1 FROM @stmt;
      EXECUTE s1;
      DROP PREPARE s1;
      SELECT NULL,NULL,NULL INTO @stmt,@stmt_begin,@stmt_end;
    END$$
    
    DELIMITER ;
    
    
    (localhost:ytt)<mysql>call sp_add_partition_pt_current(2020,2021,'pt_2020');
    Query OK, 1 row affected (42.11 sec)

分别导入原始数据：2020年的数据导入表 pt\_2020 ，其他数据导入到历史表 pt\_2011 到 pt\_2019 。

root@ytt-unbuntu:/home/ytt/scripts# cat pt_import 
    #!/bin/sh
    for i in `seq 2011 2020` 
    do 
    	{
    		mysql -D ytt -e "load data infile '/var/lib/mysql-files/pt_$i.csv' into table pt_$i fields terminated by ',' " 
    	} &
    done
    wait
    root@ytt-unbuntu:/home/ytt/scripts# ./pt_import

更改表 p\_2020 为当前表：

(localhost:ytt)<mysql>alter table pt_2020 rename to pt_current;
    Query OK, 0 rows affected (0.12 sec)

#### **接下来我们要验证表改造后性能是否符合预期：** ####

第一，查询性能分区表要有优势。

第二，分区表的管理、运维效率也要相应提升。

如果这两点都达到要求，就可以直接把分区表改名为原始表，原始表删除。

#### **先来验证查询性能是否有提升：** ####

#### **第一条查询：查询’2020-03-01’ 当天的记录** ####

基于数据是否被缓存，这里每个查询我执行两次。基于原始表 pt\_old，第一次查询时间为1分钟1.7秒，第二次为0.03秒；基于分区表 pt\_current ，第一次查询时间为0.02秒，第二次为0.01秒。**如果仅对比第一次查询时间，分区表查询性能大幅提升；第二次来讲，相差不多，但分区表查询性能依然领先。**

(localhost:ytt)<mysql>select * from pt_old where log_date = '2020-03-01';
    ...
    9593 rows in set (1 min 1.70 sec)
    -- 第二次
    9593 rows in set (0.03 sec)
    
    
    (localhost:ytt)<mysql>select * from pt_current where log_date = '2020-03-01';
    ...
    9593 rows in set (0.02 sec)
    -- 第二次
    9593 rows in set (0.01 sec)

#### **第二条查询：查询2020年年底最后5天的记录** ####

依然每条查询执行两次。基于原始表pt\_old的查询时间第一次为2分钟42.21秒，第二次为0.13秒；基于分区表 pt\_current 的查询时间第一次为0.07秒，第二次为0.01秒。**两次查询结果，分区表性能的提升都很明显。**

(localhost:ytt)<mysql>select * from pt_old where log_date in ('2020-12-27','2020-12-28','2020-12-29','2020-12-30','2020-12-31');
    ...
    30097 rows in set (2 min 42.21 sec)
    ...
    -- 第二次
    30097 rows in set (0.13 sec)
    
    
    (localhost:ytt)<mysql>select * from pt_current where log_date in ('2020-12-27','2020-12-28','2020-12-29','2020-12-30','2020-12-31');
    ...
    30097 rows in set (0.07 sec)
    ...
    -- 第二次
    30097 rows in set (0.01 sec)

#### **现在来看下管理与运维性能是否有提升？** ####

既然用分区表，就会涉及到一个很棘手的问题：\*\*每到年底，如何调整分区表来适应新增记录？\*\*MySQL并没有直接的方法， 不过我们可以利用默认分区 p\_max 来手工扩容。

来看下表 p\_current 的分区数据：

(localhost:ytt)<mysql>select left(partition_name,5) p,sum(table_rows) cnt from information_schema.partitions where table_name = 'pt_current' group by leftt(partition_name,5);
    +-------+---------+
    | p     | cnt     |
    +-------+---------+
    | p2020 | 3641722 |
    | p2021 |       0 |
    | p_max |       0 |
    +-------+---------+
    3 rows in set (0.02 sec)

目前只有2020年有数据，2021年没有数据，到2021年末记录则会自动加入到分区 p\_max 里。所以应该在2022年1月1日凌晨前得把2020整年的数据挪出去变为 pt\_2020 ，并把2022年的分区定义加进去。

那依照我们的分析，我再来写一个自动扩充分区的存储过程，可以配合OS的JOB或者MySQL的EVENT来自动运行，代码如下：

DELIMITER $$
    
    USE `ytt`$$
    
    DROP PROCEDURE IF EXISTS `sp_autoextend_partition_pt_current`$$
    
    CREATE DEFINER=`root`@`%` PROCEDURE `sp_autoextend_partition_pt_current`(
    IN f_year YEAR
    )
    BEGIN
    	DECLARE v_days INT UNSIGNED DEFAULT 365;
    	DECLARE v_days_interval DATE DEFAULT '2018-12-31';
    	DECLARE i INT UNSIGNED DEFAULT 1;
    	SET @stmt = '';
    	SET v_days =  DATEDIFF(CONCAT(f_year+1,'-01-01'),CONCAT(f_year,'-01-01'));
         
    	SET @stmt_begin = 'ALTER TABLE pt_current REORGANIZE PARTITION p_max into(';
            WHILE i <= v_days DO
               SET v_days_interval = DATE_ADD(CONCAT(f_year,'-01-01'),INTERVAL i DAY);
               SET @stmt = CONCAT(@stmt,'PARTITION p',f_year,'_',LPAD(i,3,"0"),' VALUES LESS THAN (''',v_days_interval,'''),');     
               SET i = i + 1;        
            END WHILE;	
    	SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
            SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
            PREPARE s1 FROM @stmt;
            EXECUTE s1;
            DROP PREPARE s1;
            SET @stmt = NULL;
            SET @stmt_begin = NULL;
            SET @stmt_end = NULL;	
    	END$$
    
    DELIMITER ;

现在来扩充2022年的分区数据：

(localhost:ytt)<mysql>call sp_autoextend_partition_pt_current(2022);
    Query OK, 0 rows affected (14.55 sec)

接下来每年底需要做的事情就是把去年的数据挪走，并且删除旧分区定义，添加新的一年分区定义。

现在时间到了2022年，那先给 pt\_current 插入2021年的数据（真实环境里，这部分数据是已经存在的）：

(localhost:ytt)<mysql>insert into pt_current (r1,log_date) select r1,date_add(log_date,interval 1 year) from pt_current;
    Query OK, 3641722 rows affected (2 min 28.75 sec)
    Records: 3641722  Duplicates: 0  Warnings: 0
    
    (localhost:ytt)<mysql>select left(partition_name,5) p,sum(table_rows) cnt from information_schema.partitions where table_name = 'pt_current' group by left(partition_name,5);
    +-------+---------+
    | p     | cnt     |
    +-------+---------+
    | p2020 | 3641722 |
    | p2021 | 3641726 |
    | p2022 |       0 |
    | p_max |       0 |
    +-------+---------+
    4 rows in set (0.02 sec)

再把2020年的数据挪到历史表：（由于分区表中每年的分区数目较多，为了写法方便，这里我没有用分区置换功能。）

(localhost:ytt)<mysql>create table pt_2020 like pt_old;
    Query OK, 0 rows affected (0.05 sec)
    
    (localhost:ytt)<mysql>insert into pt_2020 select * from pt_current where log_date between '2020-01-01' and '2020-12-31';
    Query OK, 3641722 rows affected (1 min 12.54 sec)
    Records: 3641722  Duplicates: 0  Warnings: 0

删除过期数据：

(localhost:ytt)<mysql>SELECT CONCAT('alter table ytt.pt_current drop partition ',partition_name,';') FROM information_schema.`PARTITIONS`  WHERE table_schema = 'ytt' AND table_name = 'pt_current'  AND partition_name like 'p2020%' into outfile '/var/lib/mysql-files/drop_expire_partition_2020.sql';
    Query OK, 366 rows affected (0.00 sec)
    
    mysql> \. /var/lib/mysql-files/drop_expire_partition_2020.sql
    Query OK, 0 rows affected (0.83 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    ...
    
    Query OK, 0 rows affected (0.82 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    ...

[img]: https://img-blog.csdnimg.cn/img_convert/0a1fc95cc4869fae7bf7b4ae092505d0.png