MySql创建分区表并且按月分区

前言

        在mysql中，按月份分区，再使用分区字段时间来查询数据将会很快，因为这样只需要扫描指定的分区。因此，在处理大量数据时，使用分区表是一个非常好的选择。

1、创建表，并使用RANGE COLUMNS分区

        按创建时间create_time字段分区;分区名使用p0、p1、p2、p3 .. 的形式；create_time字段小于2019-01-01的数据将进入p0 分区，依次类推。

CREATE TABLE "box_fenqu" ("id" bigint(36) NOT NULL AUTO_INCREMENT COMMENT '主键',"create_by" varchar(50) DEFAULT NULL COMMENT '创建人',"create_time" datetime NOT NULL COMMENT '创建日期',"update_by" varchar(50) DEFAULT NULL COMMENT '更新人',"update_time" datetime DEFAULT NULL COMMENT '更新日期',"sys_org_code" varchar(64) DEFAULT NULL COMMENT '所属部门',"status" int(10) DEFAULT '0' COMMENT '状态',"number" varchar(32) DEFAULT NULL COMMENT '编号',"zi_number" varchar(32) DEFAULT NULL COMMENT '自编号',"house_address" varchar(32) DEFAULT NULL COMMENT '仓库地址',"sb_number" varchar(32) DEFAULT NULL COMMENT '设备id',"point_id" varchar(32) DEFAULT NULL COMMENT '投放点id',"point" varchar(32) DEFAULT NULL COMMENT '投放点',"confirm" int(32) DEFAULT '0' COMMENT '商户/企业用户确认入库，默认为0（未确认）1是已确认',"last_point" varchar(32) DEFAULT NULL COMMENT '最近一次投放点名',PRIMARY KEY ("id","create_time") USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2120001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMICPARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p0 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,PARTITION p1 VALUES LESS THAN ('2019-02-01') ENGINE = InnoDB,PARTITION p2 VALUES LESS THAN ('2019-03-01') ENGINE = InnoDB,PARTITION p3 VALUES LESS THAN ('2019-04-01') ENGINE = InnoDB,PARTITION p4 VALUES LESS THAN ('2019-05-01') ENGINE = InnoDB,PARTITION p5 VALUES LESS THAN ('2019-06-01') ENGINE = InnoDB,PARTITION p6 VALUES LESS THAN ('2019-07-01') ENGINE = InnoDB,PARTITION p7 VALUES LESS THAN ('2019-08-01') ENGINE = InnoDB,PARTITION p8 VALUES LESS THAN ('2019-09-01') ENGINE = InnoDB,PARTITION p9 VALUES LESS THAN ('2019-10-01') ENGINE = InnoDB,PARTITION p10 VALUES LESS THAN ('2019-11-01') ENGINE = InnoDB,PARTITION p11 VALUES LESS THAN ('2019-12-01') ENGINE = InnoDB,PARTITION p12 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,PARTITION p13 VALUES LESS THAN ('2020-02-01') ENGINE = InnoDB,PARTITION p14 VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,PARTITION p15 VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,PARTITION p16 VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,PARTITION p17 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) 

2、创建存储过程proc_create_partition

        创建存储过程proc_create_partition，用它来直接对box_fenqu表进行分区。其实就是将分区名加1，如p2加1就是p3;指定的日期范围就是往后面进一个月，保证最新的分区总是MAXVALUES的 ,如20200401就变成20200401和20200501，其中20200401指定范围，20200501为MAXVALUES。

CREATE DEFINER="root"@"%" PROCEDURE "proc_create_partition"(in_tbname VARCHAR(64))
BEGINSELECT DATABASE() INTO @dbname;SET @tbname = in_tbname;#查询表的最近一次分区的名字，这里按自然数递增的。比如0,1,2,4。去掉 p
SELECTREPLACE (partition_name, 'p', '') INTO @PMAX
FROMINFORMATION_SCHEMA.PARTITIONS
WHERETABLE_SCHEMA = @dbname
AND table_name = @tbname
ORDER BYpartition_ordinal_position DESC
LIMIT 1;#查询表的最近一次分区的指定时间,比如最近时间的分区时 2020.04.01
SELECT
REPLACE(partition_description, '\'', '') INTO @DNAME
FROMINFORMATION_SCHEMA.PARTITIONS
WHERETABLE_SCHEMA = @dbname
AND table_name = @tbname
ORDER BYpartition_ordinal_position DESC
LIMIT 1, 1;SET @t=CONCAT('alter table `',@dbname,'`.',@tbname,' reorganize partition p',@PMAX,' into(partition p',@PMAX,' values less than (''',date(DATE_ADD(@DNAME,INTERVAL 1 MONTH)),'''),','partition p',@PMAX+1,' values less than MAXVALUE)');SELECT @t;
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;COMMIT;
END

这样传入box_fenqu表名即可进行增加分区了： CALL proc_create_partition('box_fenqu')。

3、创建mysql事件

         创建mysql事件，定时调度分区；下面的事件到的意思就是从2020-05-03 00:00:00开始，每过一个月就执行下CALL proc_create_partition('box_fenqu')。

CREATE DEFINER=`root`@`%` EVENT `e_create_partition` 
ON SCHEDULE EVERY 1 MONTH STARTS '2020-05-03 00:00:00' 
ON COMPLETION PRESERVE DISABLE 
DO CALL proc_create_partition('box_fenqu')

4、动态增加分区

        如果有多个表需要都需要定时的动态增加分区的话，就可以再写个存储如下，这个存储过程将对当前库下所有分区表都进行遍历，然后增加分区。

CREATE DEFINER="root"@"%" PROCEDURE "proc_create_partition_all"()
BEGINDECLARE tbname varchar(32);DECLARE tmpSql varchar(256);DECLARE done INT DEFAULT FALSE ;#查询已手动分区的表DECLARE part_cursor CURSOR FOR (SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = DATABASE() AND partition_expression IS NOT NULL AND table_name NOT LIKE '%bak');DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;#循环对表添加分区OPEN part_cursor;myLoop: LOOPFETCH part_cursor INTO tbname;IF done THEN LEAVE myLoop;END IF;#调用分区存储过程CALL proc_create_partition(tbname);COMMIT;END LOOP myLoop;CLOSE part_cursor;END

5、开启事件

alter event `e_create_partition` enable;

6、查询数据

        查询有数据的最新日期/月份

select REPLACE(partition_description, '\'', '') date from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='box_fenqu' AND table_rows != 0
ORDER BY REPLACE(partition_description, '\'', '') desc limit 1

        查询有数据的数据条数 

select table_rows from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='box_fenqu' AND table_rows != 0
ORDER BY REPLACE(partition_description, '\'', '') desc limit 1

        查询各个分区数据情况

SELECTPARTITION_NAME AS '分区名',TABLE_ROWS AS '记录数' ,PARTITION_DESCRIPTION '范围'
FROMinformation_schema.PARTITIONS 
WHEREtable_schema = 'test' AND table_name = 'box_fenqu';

参考文档：

mysql 分区实践之按月份分区，定时增加分区和删除分区 - 简书

MySQL 创建分区表并且按月分区