您好,登錄后才能下訂單哦!
一、背景
由于項目的業(yè)務(wù)是不斷往前跑的,所以難免數(shù)據(jù)庫的表的量會越來越龐大,不斷的擠占硬盤空間。即使再大的空間也支撐不起業(yè)務(wù)的增長,所以定期刪除不必要的數(shù)據(jù)是很有必要的。在我們項目中由于不清理數(shù)據(jù),一個表占的空間竟然達到了4G之多。想想有多可怕...
這里介紹的是用MySQL 建立一個定時器Event,定期清除掉之前的不必要事件。
二、內(nèi)容
#1、建立存儲過程供事件調(diào)用 delimiter// drop procedure if exists middle_proce// create procedure middle_proce() begin DELETE FROM jg_bj_comit_log WHERE comit_time < SUBDATE(NOW(),INTERVAL 2 MONTH); optimize table jg_bj_comit_log; DELETE FROM jg_bj_order_create WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_order_create; DELETE FROM jg_bj_order_match WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_order_match; DELETE FROM jg_bj_order_cancel WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_order_cancel; DELETE FROM jg_bj_operate_arrive WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_operate_arrive; DELETE FROM jg_bj_operate_depart WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_operate_depart; DELETE FROM jg_bj_operate_login WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_operate_login; DELETE FROM jg_bj_operate_logout WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_operate_logout; DELETE FROM jg_bj_operate_pay WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_operate_pay; DELETE FROM jg_bj_position_driver WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_position_driver; DELETE FROM jg_bj_position_vehicle WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_position_vehicle; DELETE FROM jg_bj_rated_passenger WHERE created_on < SUBDATE(NOW(),INTERVAL 3 MONTH); optimize table jg_bj_rated_passenger; end// delimiter; #2、開啟event(要使定時起作用,MySQL的常量GlOBAL event_schduleer 必須為on 或者1) show variables like 'event_scheduler' set global event_scheduler='on' #3、創(chuàng)建Evnet事件 drop event if exists middle_event; create event middle_event on schedule every 1 DAY STARTS '2017-12-05 00:00:01' on completion preserve ENABLE do call middle_proce(); #4、開啟Event 事件 alter event middle_event on completion preserve enable; #5、關(guān)閉Event 事件 alter event middle_event on completion preserve disable;
以上就是本文的全部內(nèi)容,希望對大家的學(xué)習(xí)有所幫助,也希望大家多多支持億速云。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。