Mysql触发器优化大数据表

背景

数据库的订单数量过多，需要分出热表用于快速查询，热表仅保存10天的订单数据。

解决思路

每次数据库订单表触发增删改时，同步操作到trigger_order_mul_info表，然后trigger_order_mul_info会定期删除超过10天的数据。

增删改触发器

1. 增加数据触发的触发器

BEGIN
INSERT INTO trigger_order_mul_info(id,del_flag,create_time,update_time,remarks,business_id,order_serial_num,order_status,real_name,phone,true_phone,order_stage,order_type,is_pay_later,place_type,type)
values(new.id,new.del_flag,new.create_time,new.update_time,new.remarks,new.business_id,new.order_serial_num,new.order_status,
NULL,new.repast_user_phones,new.take_phone,new.order_stage,new.order_type,new.is_pay_later,new.place_type,1);
END

2. 更新数据触发的触发器

BEGIN
update trigger_order_mul_info set id=new.id,del_flag=new.del_flag,create_time=new.create_time,update_time=new.update_time,remarks=new.remarks,
business_id=new.business_id,order_serial_num=new.order_serial_num,order_status=new.order_status,real_name=NULL,phone=new.repast_user_phones,
true_phone=new.take_phone,order_stage=new.order_stage,order_type=new.order_type,is_pay_later=new.is_pay_later,place_type=new.place_type,type=1 where id=new.id;
END

3. 删除数据触发的触发器

BEGIN
delete from trigger_order_mul_info where id=old.id ;
END

4. 对应添加到触发器