Shulou(Shulou.com)06/01 Report--

I. background

As the business of the project is constantly moving forward, it is inevitable that the number of tables in the database will become larger and larger, constantly squeezing out the hard disk space. Even a large amount of space cannot support business growth, so it is necessary to delete unnecessary data on a regular basis. In our project, because we don't clean up the data, a table takes up as much as 4G of space. Think about how terrible it is.

What is introduced here is to use MySQL to set up a timer Event that periodically clears up previous unnecessary events.

II. Content

# 1. Establish a stored procedure for event calls delimiter//drop procedure if exists middle_proce//create procedure middle_proce () beginDELETE 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. Enable event (the constant GlOBAL event_schduleer of MySQL must be on or 1 for timing to work) show variables like 'event_scheduler'set global event_scheduler='on'#3, create Evnet event drop event if exists middle_event;create event middle_eventon schedule every 1 DAY STARTS' 2017-12-05 00:00:01'on completion preserve ENABLEdo call middle_proce (); # 4. Enable Event event alter event middle_eventon completion preserve enable;#5, close Event event alter event middle_eventon completion preserve disable

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.