mysql自动分区自动清理

原创 2015年11月18日 15:52:51

1. 概述

mysql分区表功能特别有用,其中一个应用就是保存固定时间的数据信息,自动分区自动purge,不用担心数据量越积累越多。

比较实用的一个实现方式是表一天一个分区,保持固定天数的数据。

2. 完整的SQL

以数据库log为例,里面有一个表tb_log, 按天分区,始终保存最新的30天的数据。

存储过程sp_create_log_partition和sp_drop_log_partition用于创建和删除分区。

事件event_log_auto_partition每天执行一次,用于向前创建新的分区和删除过期的分区。

存储过程和事件结合使用就实现了tb_log数据的自动分区自动删除。

--
-- Definition for database log
--
DROP DATABASE IF EXISTS log;
CREATE DATABASE IF NOT EXISTS log
CHARACTER SET utf8
COLLATE utf8_general_ci;

-- 
-- Set default database
--
USE log;

--
-- Definition for table tb_log
--
CREATE TABLE IF NOT EXISTS tb_log (
  id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  log varchar(512) NOT NULL DEFAULT '',
  PRIMARY KEY (id, created_at)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
AVG_ROW_LENGTH = 16384
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
PARTITION BY RANGE(TO_DAYS(created_at))
(
PARTITION pbasic VALUES LESS THAN (0)
);

DELIMITER $$

--
-- Definition for procedure sp_create_log_partition
--
CREATE DEFINER = 'uiadmin'@'%'
PROCEDURE sp_create_log_partition (day_value datetime, tb_name varchar(128))
BEGIN
  DECLARE par_name varchar(32);
  DECLARE par_value varchar(32);
  DECLARE _err int(1);
  DECLARE par_exist int(1);
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET _err = 1;
  START TRANSACTION;
    SET par_name = CONCAT('p', DATE_FORMAT(day_value, '%Y%m%d'));
    SELECT
      COUNT(1) INTO par_exist
    FROM information_schema.PARTITIONS
    WHERE TABLE_SCHEMA = 'log' AND TABLE_NAME = tb_name AND PARTITION_NAME = par_name;
    IF (par_exist = 0) THEN
      SET par_value = DATE_FORMAT(day_value, '%Y-%m-%d');
      SET @alter_sql = CONCAT('alter table ', tb_name, ' add PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (TO_DAYS("', par_value, '")+1))');
      PREPARE stmt1 FROM @alter_sql;
      EXECUTE stmt1;
    END IF;
  END
  $$

--
-- Definition for procedure sp_drop_log_partition
--
CREATE DEFINER = 'uiadmin'@'%'
PROCEDURE sp_drop_log_partition (day_value datetime, tb_name varchar(128))
BEGIN
  DECLARE str_day varchar(64);
  DECLARE _err int(1);
  DECLARE done int DEFAULT 0;
  DECLARE par_name varchar(64);
  DECLARE cur_partition_name CURSOR FOR
  SELECT
    partition_name
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA = 'log' AND table_name = tb_name
  ORDER BY partition_ordinal_position;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET _err = 1;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  SET str_day = DATE_FORMAT(day_value, '%Y%m%d');
  OPEN cur_partition_name;
  REPEAT
    FETCH cur_partition_name INTO par_name;
    IF (str_day > SUBSTRING(par_name, 2)) THEN
      SET @alter_sql = CONCAT('alter table ', tb_name, ' drop PARTITION ', par_name);
      PREPARE stmt1 FROM @alter_sql;
      EXECUTE stmt1;
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur_partition_name;
END
$$

--
-- Definition for event event_log_auto_partition
--
CREATE
DEFINER = 'uiadmin'@'%'
EVENT event_log_auto_partition
ON SCHEDULE EVERY '1' DAY
STARTS '1972-01-01 00:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 3 DAY), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 2 DAY), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 1 DAY), 'tb_log');
  CALL sp_create_log_partition(NOW(), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 1 DAY), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 2 DAY), 'tb_log');
  CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 3 DAY), 'tb_log');
  CALL sp_drop_log_partition(DATE_ADD(NOW(), INTERVAL - 30 DAY), 'tb_log');

END
$$

--
-- Create partitions based on current time
--
CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 3 DAY), 'tb_log')$$
CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 2 DAY), 'tb_log')$$
CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL - 1 DAY), 'tb_log')$$
CALL sp_create_log_partition(NOW(), 'tb_log')$$
CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 1 DAY), 'tb_log')$$
CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 2 DAY), 'tb_log')$$
CALL sp_create_log_partition(DATE_ADD(NOW(), INTERVAL 3 DAY), 'tb_log')$$

DELIMITER ;

3. 查看分区

select TABLE_SCHEMA, TABLE_NAME,PARTITION_NAME from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='log' and table_name='tb_log';

在磁盘上一个分区表现为一个文件,所以删除操作会很快完成的。




版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

MySQL表的四种分区类型&创建,增加,删除mysql表分区

一、什么是表分区 通俗地讲表分区是将一大表,根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。 如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地...

mysql 批量删除分区

alter table titles drop partition p01; use zabbix; mysql> source drop_par.sql [oracle@oadb mysql]$...

定时添加,删除Mysql分区

注意:   分区指定字段一定是这个表的主健,一段创建好分区,不能跳跃创建和删除步骤:   1、添加、删除分区存储过程脚本   2、Event定时执行   3、创建分区1、创建表 create...
  • xlxxcc
  • xlxxcc
  • 2016年09月09日 15:23
  • 958

mysql分区表,循环分区,触发器自动删除

在网络上看到一篇之前跟我在新大陆公司业务差不多的关于分区表的建立与处理办法,回忆了这一块知识,觉得有必要记录下来,所以转载了一下http://blog.chinaunix.net/uid-240869...

MySQL5.5 自动分区脚本

一、使用说明: 1.此脚本为分区后,定时自动增加分区.(被自动分区的表,一定要先手动分几个区) 2.每隔15天,定时器会执行一个存储过程,对分区日期最后的那天再往后新增15个分区. 3.Scri...

mysql自动分区

1、建立分区表 create table test_log ( created datetime, msg varchar(2000) )partition by range columns(cre...

mysql 定时自动新增分区

项目需要,要根据数据库表中字段mydate(timestamp)进行定时自动新增分区,上图 这是数据库,比较简单,主要是为了演示过程建的,然后先建立分区 建好之后可以查询所建分区是怎样的 ...

mysql自动按时间分区实例

#删除表 DROP TABLE `records`; #重新建表 CREATE TABLE `records`( `id` BIGINT(20) AUTO_INCREMENT, `advert_...

mysql 表分区 查看表分区 修改表分区

 原文地址:http://blog.csdn.net/feihong247/article/details/7885199   一、       mysql分区简介 数据库分区 数据库分...

mysql自动分区存储过程

DELIMITER $$ USE `dw`$$ DROP PROCEDURE IF EXISTS `sp_tool_maintain_partition`$$ CREATE DEFINER=`d...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql自动分区自动清理
举报原因:
原因补充:

(最多只允许输入30个字)