#首先上表结构
CREATE TABLE `sys_history` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`did` bigint(20) NOT NULL ,
`ndata` int(11) NOT NULL DEFAULT '0' ,
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
`is_deleted` int(11) DEFAULT '0',
PRIMARY KEY (`id`,`did`,`create_time`),
UNIQUE KEY `hindex` (`id`,`did`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(create_time)
(PARTITION p2018061300 VALUES LESS THAN ('2018-06-13 00:00:00') ENGINE = InnoDB,
PARTITION p2018061302 VALUES LESS THAN ('2018-06-13 02:00:00') ENGINE = InnoDB,
PARTITION p2018061304 VALUES LESS THAN ('2018-06-13 04:00:00') ENGINE = InnoDB,
PARTITION p2018061306 VALUES LESS THAN ('2018-06-13 06:00:00') ENGINE = InnoDB,
PARTITION p2018061308 VALUES LESS THAN ('2018-06-13 08:00:00') ENGINE = InnoDB) */
复制代码在创建表的时候，首先建立了
id，did，create_time三个联合索引，由于我的数据中，did和create_time都会重复，但是三个合起来就肯定不会重复，所以我就选择了创建联合索引。至于为何这样见索引就自己google或者度娘啦，这不是本文的重点。
这里我使用了Range分区，因为我们这里设想的是按小时创建分区，这是一个时间范围，范围应该连续但是不重叠，使用
PARTITION BY RANGE,VALUES LESS THAN关键字。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数，添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势。值得注意的是我建表的时候已经预先创建了几个分区，举例来说：PARTITION p2018061300 VALUES LESS THAN ('2018-06-13 00:00:00') ENGINE = InnoDB，这里我预先创建了一个名为p2018061300的分区，其LESS值为2018-06-13 00:00:00，目的是想把create_time<=2018-06-13 00:00:00的数据放入改分区，p2018061300，p2018061302，p2018061304，p2018061306从分区名也可以看出每两个小时一个分区，p2018061300的意思就是2018年6月13日0点之前的数据的分区。
自动分区
总不能每次都手动每两小时创建分区吧...必须是自动的！ 这时候存储过程就要登场了！
自动每两个小时创建一个分区的存储过程贴上来
-- 自动创建表分区的存储过程
DROP PROCEDURE IF EXISTS AUTO_PARTITION_HOUR;
DELIMITER //
CREATE PROCEDURE AUTO_PARTITION_HOUR(IN $table_name VARCHAR(64), IN $range_hours INT, IN $min_time VARCHAR(20))
BEGIN
-- $table_name 待分片的表名
-- $range_hours 每隔分区的跨度时长，例如2小时，尽量取24能整除的数
-- $min_time 已有历史数据的最小时间，决定起始分片的时间，如果为 null 则自动取当天的零点
DECLARE $base_dir VARCHAR(64);
DECLARE $monthly_dir VARCHAR(64);
DECLARE $now VARCHAR(30); -- 当前时间戳
DECLARE $zero_am VARCHAR(30); -- 明天零点
DECLARE $stop_hour VARCHAR(30); -- 预创建终止时间戳
DECLARE $sql_partition_template VARCHAR(500); -- 建分片的 SQL 模板
DECLARE $partition_name VARCHAR(20); -- 新分片名字
DECLARE $last_less_than_hour VARCHAR(30); -- 上一个分片的 less 值
DECLARE $less_than_hour VARCHAR(30); -- 上一个分片的 less 值
DECLARE $sql_tmp VARCHAR(500); -- 临时拼接的 SQL
-- 数据文件和索引文件的存放目录
SET $base_dir = CONCAT('/data/mysql/hisdata/', DATABASE(), '/', $table_name, '/');
-- 当前系统时间
SET $now = DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s');
-- 今天零点
SET $zero_am = DATE_FORMAT(now(), '%Y-%m-%d %00:%00:%00');
-- 预创建分区的终止小时值(后天零点)
SET $stop_hour = DATE_FORMAT(now()+interval 172800 second, '%Y-%m-%d %00:%00:%00');
-- 创建新分片的SQL模板
SET $sql_partition_template = CHAR(10);
SET $sql_partition_template = CONCAT($sql_partition_template, 'ALTER TABLE ', $table_name, ' ADD PARTITION (');
SET $sql_partition_template = CONCAT($sql_partition_template, CHAR(10), 'PARTITION $partition_name VALUES LESS THAN (');
SET $sql_partition_template = CONCAT($sql_partition_template,'\'$less_than_hour\'',')');
SET $sql_partition_template = CONCAT($sql_partition_template, CHAR(10), ');');
-- 查找上一个分片的终止时间
SET $last_less_than_hour = NULL;
SELECT SUBSTRING(PARTITION_DESCRIPTION,2,19) INTO $last_less_than_hour FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=$table_name
ORDER BY PARTITION_ORDINAL_POSITION DESC LIMIT 1;
IF $last_less_than_hour IS NULL OR $last_less_than_hour='' THEN
IF $min_time IS NULL THEN
-- 没有记录，设置为当天零点
SET $last_less_than_hour = $zero_am;
ELSE
-- 设置为已有最早记录的当天零点
SET $last_less_than_hour = DATE_FORMAT($min_time, '%Y-%m-%d %00:%00:%00');
END IF;
END IF;
-- 循环预创建分区
_PARTITION_LOOP_ : LOOP
SET $less_than_hour = DATE_ADD($last_less_than_hour ,interval $range_hours HOUR);
IF $less_than_hour > $stop_hour THEN
LEAVE _PARTITION_LOOP_;
END IF;
SET $partition_name = CONCAT('p', DATE_FORMAT($less_than_hour, '%Y%m%d%H'));
SET $sql_tmp = $sql_partition_template;
SET $sql_tmp = REPLACE($sql_tmp, '$partition_name', $partition_name);
SET $sql_tmp = REPLACE($sql_tmp, '$less_than_hour', $less_than_hour);
SET @stmt_sql = $sql_tmp;
PREPARE stmt FROM @stmt_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET $last_less_than_hour = $less_than_hour;
END LOOP _PARTITION_LOOP_;
END
//
DELIMITER ;
复制代码存储过程的解读
我的存储过程是参考网上一位大牛的，网址忘了...Sorry 大致意思就是输入
表名，分片时间步进，最早数据时间，然后查找上一个分片的终止时间，如果没有记录就设置为当天的0点，或者最早记录当天的0点， 如果已经有分片记录了，就取分片的终止时间，然后循环创建分区，每循环一次小时加上$range_hours个小时，循环里面有个条件跳出
IF $less_than_hour > $stop_hour THEN
LEAVE _PARTITION_LOOP_;
END IF;
复制代码这里的$stop_hour就是之前设置的后天的0点。 这个存储过程每天运行一次就可以了。
自动执行存储过程
这个简单啦，创建一个自动运行的
Event就可以了，上代码
-- 创建事件
create event auto_partition_event
on schedule every 1 DAY starts '2018-06-12 18:30:00'
ON COMPLETION PRESERVE ENABLE
do
call AUTO_PARTITION_HOUR('sys_history',2);
复制代码限制保留历史数据的天数
上一篇说到了要求保留一年的历史数据，那么就意味着需要定时删除一年前的数据和分区（注意：删除分区就是删除数据），那么想到的又是
存储过程，上代码
-- 循环删除分区
_CLEAR_PARTITION_LOOP_:LOOP
-- 查找最早的一个分区的时间
SET $most_less_than_hour = NULL;
SELECT SUBSTRING(PARTITION_DESCRIPTION,2,19) INTO $most_less_than_hour FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=$table_name
ORDER BY PARTITION_ORDINAL_POSITION LIMIT 1;
SET $most_partition_name = CONCAT('p', DATE_FORMAT($most_less_than_hour, '%Y%m%d%H'));
SET $most_less_than_hour = DATE_FORMAT($most_less_than_hour, '%Y-%m-%d %00:%00:%00');
IF timestampdiff(day,$most_less_than_hour,$zero_am) < $delay_day THEN
LEAVE _CLEAR_PARTITION_LOOP_;
END IF;
SET $del_sql = CHAR(10);
SET $del_sql = CONCAT($del_sql,'ALTER TABLE ',$table_name,' DROP PARTITION ', $most_partition_name);
SET @sd_sql = $del_sql;
PREPARE sd FROM @sd_sql;
EXECUTE sd;
DEALLOCATE PREPARE sd;
END LOOP _CLEAR_PARTITION_LOOP_;
复制代码创建一个循环自动循环删除时间大于输入保留天数的数据，这里多了一个输入参数
$delay_day，例如我只想保留365天的数据， 那么$delay_day输入值就为365。
贴完整的存储过程
-- 自动创建表分区的存储过程
DROP PROCEDURE IF EXISTS AUTO_PARTITION_HOUR;
DELIMITER //
CREATE PROCEDURE AUTO_PARTITION_HOUR(IN $table_name VARCHAR(64), IN $range_hours INT, IN $min_time VARCHAR(20),IN $delay_day INT)
BEGIN
-- $table_name 待分片的表名
-- $range_hours 每隔分区的跨度时长，例如2小时，尽量取24能整除的数
-- $min_time 已有历史数据的最小时间，决定起始分片的时间，如果为 null 则自动取当天的零点
DECLARE $base_dir VARCHAR(64);
DECLARE $monthly_dir VARCHAR(64);
DECLARE $now VARCHAR(30); -- 当前时间戳
DECLARE $zero_am VARCHAR(30); -- 明天零点
DECLARE $stop_hour VARCHAR(30); -- 预创建终止时间戳
DECLARE $sql_partition_template VARCHAR(500); -- 建分片的 SQL 模板
DECLARE $partition_name VARCHAR(20); -- 新分片名字
DECLARE $last_less_than_hour VARCHAR(30); -- 上一个分片的 less 值
DECLARE $less_than_hour VARCHAR(30); -- 上一个分片的 less 值
DECLARE $sql_tmp VARCHAR(500); -- 临时拼接的 SQL
DECLARE $most_less_than_hour VARCHAR(30); -- 最早一个分区的less 值
DECLARE $most_partition_name VARCHAR(30); -- 最早一个分区的名称
DECLARE $del_sql VARCHAR(100);
-- 数据文件和索引文件的存放目录
SET $base_dir = CONCAT('/data/mysql/hisdata/', DATABASE(), '/', $table_name, '/');
-- 当前系统时间
SET $now = DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s');
-- 今天零点
SET $zero_am = DATE_FORMAT(now(), '%Y-%m-%d %00:%00:%00');
-- 预创建分区的终止小时值(后天零点)
SET $stop_hour = DATE_FORMAT(now()+interval 172800 second, '%Y-%m-%d %00:%00:%00');
-- 创建新分片的SQL模板
SET $sql_partition_template = CHAR(10);
SET $sql_partition_template = CONCAT($sql_partition_template, 'ALTER TABLE ', $table_name, ' ADD PARTITION (');
SET $sql_partition_template = CONCAT($sql_partition_template, CHAR(10), 'PARTITION $partition_name VALUES LESS THAN (');
SET $sql_partition_template = CONCAT($sql_partition_template,'\'$less_than_hour\'',')');
SET $sql_partition_template = CONCAT($sql_partition_template, CHAR(10), ');');
-- 查找上一个分片的终止小时值
SET $last_less_than_hour = NULL;
SELECT SUBSTRING(PARTITION_DESCRIPTION,2,19) INTO $last_less_than_hour FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=$table_name
ORDER BY PARTITION_ORDINAL_POSITION DESC LIMIT 1;
IF $last_less_than_hour IS NULL OR $last_less_than_hour='' THEN
IF $min_time IS NULL THEN
-- 没有记录，设置为当天零点
SET $last_less_than_hour = $zero_am;
ELSE
-- 设置为已有最早记录的当天零点
SET $last_less_than_hour = DATE_FORMAT($min_time, '%Y-%m-%d %00:%00:%00');
END IF;
END IF;
-- 循环预创建分区
_PARTITION_LOOP_ : LOOP
SET $less_than_hour = DATE_ADD($last_less_than_hour ,interval $range_hours HOUR);
IF $less_than_hour > $stop_hour THEN
LEAVE _PARTITION_LOOP_;
END IF;
SET $partition_name = CONCAT('p', DATE_FORMAT($less_than_hour, '%Y%m%d%H'));
SET $sql_tmp = $sql_partition_template;
SET $sql_tmp = REPLACE($sql_tmp, '$partition_name', $partition_name);
SET $sql_tmp = REPLACE($sql_tmp, '$less_than_hour', $less_than_hour);
SET @stmt_sql = $sql_tmp;
PREPARE stmt FROM @stmt_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET $last_less_than_hour = $less_than_hour;
END LOOP _PARTITION_LOOP_;
-- 循环删除分区
_CLEAR_PARTITION_LOOP_:LOOP
-- 查找最早的一个分区的时间
SET $most_less_than_hour = NULL;
SELECT SUBSTRING(PARTITION_DESCRIPTION,2,19) INTO $most_less_than_hour FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=$table_name
ORDER BY PARTITION_ORDINAL_POSITION LIMIT 1;
SET $most_partition_name = CONCAT('p', DATE_FORMAT($most_less_than_hour, '%Y%m%d%H'));
SET $most_less_than_hour = DATE_FORMAT($most_less_than_hour, '%Y-%m-%d %00:%00:%00');
IF timestampdiff(day,$most_less_than_hour,$zero_am) < $delay_day THEN
LEAVE _CLEAR_PARTITION_LOOP_;
END IF;
SET $del_sql = CHAR(10);
SET $del_sql = CONCAT($del_sql,'ALTER TABLE ',$table_name,' DROP PARTITION ', $most_partition_name);
SET @sd_sql = $del_sql;
PREPARE sd FROM @sd_sql;
EXECUTE sd;
DEALLOCATE PREPARE sd;
END LOOP _CLEAR_PARTITION_LOOP_;
END
//
DELIMITER ;
复制代码
这样每隔一天运行一次，就可以自动创建直到后天0点的分区，并且自动删除过期的分区。
116
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
