随着时间的推移,数据库中积压的数据越来越多,数据文件越来越大,查询越来越慢。在历史数据不重要的情况下,我们可以对数据表中的过期数据进行清理。让数据库运行在一个平稳的环境中,主要方法是通过定时事件,触发清理任务。
- 查看是否开启了事件功能
mysql> show variables like '%event_sc%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
如上所示,事件调度器是处于关闭状态的。
2. 打开事件功能
2.1 临时打开,但下次重启mysql就失效了。
在这里SET GLOBAL event_scheduler = 1; 和 SET GLOBAL event_scheduler = ON; 效果是一样的
mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%event_sc%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
2.2 修改my.cnf, 添加配置,永久打开
[root@localhost data]# vim /etc/my.cnf
[mysqld]
event_scheduler=ON
然后登录mysql,如2.1,执行SET GLOBAL event_scheduler = ON;
- 创建事件
只保留DATA_FILE中最近九十天的数据
-- 自动删除过期数据
drop PROCEDURE if exists autodel;
DELIMITER //
CREATE PROCEDURE autodel()
BEGIN
delete from DATA_FILE where DATE(RECEIVE_TIME) <= DATE(DATE_SUB(NOW(),INTERVAL 90 day));
END //
DELIMITER ;
-- 定时清理事件
drop EVENT if exists event_auto_del_data;
CREATE EVENT `event_auto_del_data`
ON SCHEDULE EVERY 90 DAY STARTS now()
ON COMPLETION NOT PRESERVE ENABLE DO CALL autodel();
- 显示事件show events;
mysql> show events;
+---------+---------------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+---------+---------------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| XXDB | event_auto_del_data | root@% | SYSTEM | RECURRING | NULL | 90 | DAY | 2021-01-22 14:57:14 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
+---------+---------------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
- 开启关闭事件
ALTER EVENT event_auto_del_data ON COMPLETION PRESERVE ENABLE; //开启某事件
ALTER EVENT event_auto_del_data ON COMPLETION PRESERVE DISABLE; //关闭某事件
- 执行存储过程
call autodel;