公司在做邮件服务器的时候,要把收到的邮件的主要的信息保存到数据库中。因此在数据库中创建了 2 张数据表:arc 表:邮件的主体信息;arc_att 表:邮件的附件信息;
但是,由于每天收到的邮件数量有1万封左右,所以过几个月后邮件的主体信息表就达到了百万行记录。这么大的数据量非常的不利于数据的查询和保存的。所以就考虑使用 mysql 自带的定时事件和存储过程来管理这两个表,使它们的分区的自动创建和删除。
其中 arc 表结构:
arc_att 表结构:
注意:这里的分区使用的字段是 date_added , 创建分区的字段必须包含在该表主键里面,或者该表不存在主键,详情查看上一篇关于 mysql 分区的文章,所以在这里把 id 和 date_added 字段作为复合主键使用。
1:查看mysql 系统是否支持 定时事件
show variables like 'event_scheduler';
如果 系统是关闭着的 要开启:
set global event_scheduler = ON;
但是,这样配置只能在本次设置中生效,当 mysqld 服务重启后,该参数又会失效,所以必须在配置文件中设置该参数。
编辑 mysql 配置文件,并在【mysqld】下添加该变量:
重启 mysqld 服务, 登录 mysql ,并查看设置是否生效:
2:设置存储过程
设置 创建分区的存储过程
查找某个表是否存在分区:
如果不存在则, 按照当前日期为依据, 添加一个分区;
如果存在则,按照最后一个分区的日期为依据, 添加一个分区。
CREATE DEFINER=`root`@`%`
PROCEDURE `proc_create_paratition`(IN `tableName` VARCHAR(128) CHARSET utf8,
IN `timeColName` VARCHAR(128) CHARSET utf8)
BEGIN
DECLARE p_id int;
DECLARE nextDate date;
DECLARE lastDate LONG;
DECLARE p_name varchar(16);
SELECT COUNT(partition_name) INTO p_id
FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_NAME=tableName;
IF p_id=0 THEN
SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH) INTO nextDate
FROM DUAL;
SET p_name=DATE_FORMAT(CURDATE(),'%Y%m');
SET @v_add=CONCAT('ALTER table ',tableName
,' PARTITION by range(TO_DAYS('
, timeColName
,'))(partition '
, CONCAT('p',p_name)
,' values less than (TO_DAYS("'
, nextDate,'")))');
ELSE
SELECT max(partition_description) des INTO lastDate
FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_NAME=tableName;
SELECT DATE_ADD(FROM_DAYS(lastDate),INTERVAL 1 MONTH) INTO nextDate
FROM DUAL;
SET p_name=DATE_FORMAT(FROM_DAYS(lastDate),'%Y%m');
SET @v_add=CONCAT('alter table ',tableName
,' add partition (partition '
, CONCAT('p',p_name)
,' values less than (TO_DAYS("'
,nextDate,'")))');
END IF;
PREPARE stmt from @v_add;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
删除分区的存储过程:
--
-- proc_drop_partition 删除数据表中最前面的一个分区。
--
CREATE DEFINER=`root`@`%`
PROCEDURE `proc_drop_paratition`(IN `tableName` VARCHAR(128) CHARSET utf8)
BEGIN
DECLARE p_id int;
DECLARE p_name varchar(16);
SELECT COUNT(partition_name) into p_id
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=tableName;
IF p_id > 1 THEN
SELECT PARTITION_NAME into p_name
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=tableName
ORDER BY partition_ordinal_position LIMIT 1;
IF p_name is not null THEN
SET @v_add=CONCAT('alter table ',tableName,' drop partition ',p_name);
PREPARE stmt from @v_add;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END IF;
END
3:设置定时事件
--
-- 创建生成 arc 表分区的事件。
--
CREATE DEFINER=`root`@`%`
EVENT `event_create_arc_Paratition`
ON SCHEDULE EVERY 1 MONTH STARTS '2018-12-01 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
DO call proc_create_paratition("arc","date_added")
--
-- 创建删除 arc 表分区的事件。
--
CREATE DEFINER=`root`@`%`
EVENT `event_drop_arc_Partition`
ON SCHEDULE EVERY 1 MONTH STARTS '2019-01-01 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
COMMENT '删除 arc 表的分区'
DO call proc_drop_partition('arc')
--
-- 创建生成 arc_att 表分区的事件。
--
CREATE DEFINER=`root`@`%`
EVENT `event_create_arcAtt_Paratition`
ON SCHEDULE EVERY 1 MONTH STARTS '2018-12-01 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
DO call proc_create_paratition("arc_att","date_added")
--
-- 创建删除 arc_att 表分区的事件。
--
CREATE DEFINER=`root`@`%`
EVENT `event_drop_arc_Partition`
ON SCHEDULE EVERY 1 MONTH STARTS '2019-01-01 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
COMMENT '删除 arc_att 表的分区'
DO call proc_drop_partition('arc_att')
查看分区表文件信息: