Mysql数据按天分区,定期删除

需求:

　　1.日志表需要按天分区

　　2.只保留一个月数据

方案:

　　1.创建两个事件,一个事件生成未来需要的分区,另一个事件定期检查过期数据(移除分区)

　　2.创建事件每小时执行一次,删除事件每天执行一次

　　3.事件开始时需要先创建一个当前所需分区

全量方法:

　　1.先构造存储过程 create_partition_today :将表转化为分区表,并将历史数据归集到该分区,未来数据则按天放置:

#alter table to partition table

DELIMITER $$

USE `dc_log`$$

DROP PROCEDURE IF EXISTS `create_partition_today`$$

CREATE PROCEDURE `create_partition_today`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))

  BEGIN

    DECLARE BEGINTIME TIMESTAMP;

    DECLARE ENDTIME TIMESTAMP;

    DECLARE DAYS_ENDTIME INT;

    DECLARE PARTITIONNAME VARCHAR(16);

    SET BEGINTIME = NOW();

    SET ENDTIME = BEGINTIME + INTERVAL 1 DAY;

    SET PARTITIONNAME = DATE_FORMAT(BEGINTIME, 'p%Y%m%d');

    SET DAYS_ENDTIME = TO_DAYS(ENDTIME);

    SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',

                        ' PARTITION BY RANGE (to_days(create_time))

    (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', DAYS_ENDTIME, '))');

    PREPARE STMT FROM @SQL;

    EXECUTE STMT;

    DEALLOCATE PREPARE STMT;

  END$$

DELIMITER ;

　　2.按天构造分区的存储过程create_partition_by_day:

#procedure of build partition of today and next day

DELIMITER $$

USE `dc_log`$$

DROP PROCEDURE IF EXISTS `create_partition_by_day`$$

CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))

  BEGIN

    DECLARE ROWS_CNT INT UNSIGNED;

    DECLARE BEGINTIME TIMESTAMP;

    DECLARE ENDTIME TIMESTAMP;

    DECLARE DAYS_ENDTIME INT;

    DECLARE PARTITIONNAME VARCHAR(16);

    SET BEGINTIME = NOW() + INTERVAL 1 DAY;

    SET PARTITIONNAME = DATE_FORMAT(BEGINTIME, 'p%Y%m%d');

    SET ENDTIME = BEGINTIME + INTERVAL 1 DAY;

    SET DAYS_ENDTIME = TO_DAYS(ENDTIME);

    SELECT COUNT(*)

    INTO ROWS_CNT

    FROM information_schema.partitions

    WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;

    IF ROWS_CNT = 0

    THEN

      SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',

                        ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', DAYS_ENDTIME, '))');

      PREPARE STMT FROM @SQL;

      EXECUTE STMT;

      DEALLOCATE PREPARE STMT;

    ELSE

      SELECT CONCAT("partition `", PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME,

                    "` already exists") AS result;

    END IF;

  END$$

DELIMITER ;

　　3.按天清除数据的存储过程clear_partition_by_day

DELIMITER $$

USE `dc_log`$$

DROP PROCEDURE IF EXISTS `clear_partition_by_day`$$

CREATE PROCEDURE `clear_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))

  BEGIN

    DECLARE NOWDAYS INT;

    DECLARE Done INT;

    DECLARE part VARCHAR(64);

    DECLARE descr INT;

    DECLARE rs CURSOR FOR SELECT

                            partition_name        part,

                            partition_description descr

                          FROM information_schema.partitions

                          WHERE

                            table_schema = IN_SCHEMANAME

                            AND table_name = IN_TABLENAME;

    /* 异常处理 */

    DECLARE CONTINUE HANDLER FOR SQLSTATE '' SET Done = 1;

    OPEN rs;

    SET NOWDAYS = TO_DAYS(NOW());

    FETCH rs into part,descr;

    while Done is null DO

      IF NOWDAYS - descr > 30

        THEN

          select descr AS cc;

          SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',

                            ' DROP PARTITION ', part);

          select descr AS aa;

          SELECT @SQL AS result;

          select descr AS bb;

          PREPARE STMT FROM @SQL;

          EXECUTE STMT;

          DEALLOCATE PREPARE STMT;

        END IF;

      FETCH rs into part,descr;

    end WHILE;

    CLOSE rs;

  END$$

DELIMITER ;

　　注意:以上过程有可能报错,这里记录两个错误:

　　1.分区字段必须包含主键:

　　这里采用的是事件字段作为分区字段,当然不可能是主键,所以再mysql中会报错,查阅资料大多说是因为分区表中不能保证数据唯一,需要将分区健纳入主键才可,不清楚原理.如果不愿意这样做也可以考虑,删除已有主键,无主键的表可以用任意字段作为分区字段

ALTER TABLE dc_system.service_push DROP PRIMARY KEY;

　　2.时间字段不允许作为分区字段:

　　这是因为 DATETIME 会受到时间区的影响,mysql里面分区的时间字段需要用CURRENT_TIMESTAMP

ALTER TABLE business_log

  MODIFY create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

4.继续往下,打开事件变量,这里直接修改

SET GLOBAL event_scheduler = 'ON'; #打开

　　重启后会被重置,如果需要永久修改,在my.cnf（mysql配置文件）中[mysqld]部分中添加下面内容，重启MYSQL

event_scheduler=ON

5.调用存储过程创建当天分区:

#created today's partition

CALL create_partition_today('dc_log', 'business_log');

6.创建事件:

DELIMITER $$

USE `dc_log`$$

CREATE EVENT IF NOT EXISTS `e_part_manage`

  ON SCHEDULE EVERY 1 HOUR #every minute

  STARTS '2018-05-01 18:27:00'

  ON COMPLETION PRESERVE

ENABLE

  COMMENT 'Creating partitions'

DO BEGIN

  CALL dc_log.create_partition_by_day('dc_log', 'business_log');

END$$

DELIMITER ;

#event of clear data which out of date of 30 days

DELIMITER $$

USE `dc_log`$$

CREATE EVENT IF NOT EXISTS `clear_data`

  ON SCHEDULE EVERY 1 DAY #every minute

  STARTS '2018-05-01 15:58:51'

  ON COMPLETION PRESERVE

ENABLE

  COMMENT 'clearing data'

DO BEGIN

  CALL clear_partition_by_day('dc_log', 'business_log');

END$$

DELIMITER ;

7.如果有其他表也需要如此处理,则先执行创建当天分区,再修改事件:

#service_push #same steps of table business_log

ALTER TABLE dc_system.service_push DROP PRIMARY KEY;

CALL create_partition_today('dc_system', 'service_push');

DELIMITER ;

DELIMITER $$

ALTER EVENT e_part_manage

DO BEGIN

  CALL dc_log.create_partition_by_day('dc_log', 'business_log');

  CALL dc_log.create_partition_by_day('dc_system', 'service_push');

END$$

DELIMITER ;

DELIMITER $$

ALTER EVENT clear_data

DO BEGIN

  CALL dc_log.clear_partition_by_day('dc_log', 'business_log') ;

  CALL dc_log.clear_partition_by_day('dc_system', 'service_push');

END$$

DELIMITER ;

8.查看表的分区情况:

use dc_log;

SELECT

      partition_name part,

      partition_expression expr,

      partition_description descr,

      table_rows

FROM

      INFORMATION_SCHEMA.partitions

WHERE

      TABLE_SCHEMA = SCHEMA()

AND TABLE_NAME='service_push' ;

9.手动添加分区:

ALTER TABLE dc_log.all_log ADD PARTITION (PARTITION 'PARTITIONNAME' VALUES LESS THAN (TO_DAYS(now())));

10.查看事件和事件开关

show events;

SHOW VARIABLES LIKE 'event_scheduler';

done

巴特西

Mysql数据按天分区,定期删除

最新文章

热门文章