二、全网消息发送缓慢

一、问题现场
  全网消息发送4小时之后客户端才收到

二、环境信息

  管理系统:redhed5.7 8C 16G 300G 2台
  接口子系统：redhed5.7 8C 16G 300G 2台
  接入系统：redhed5.7 8C 16G 300G 2台
  数据库服务器：redhed5.7 16C 32G 4T 2台
  缓存服务器; redhed5.7 16C 16G 300G 2台
   
三、问题定位
 
   查询所有用户信息（600W）条数据，耗时4个小时。

四、问题解决
  清理过期数据
-- ----------------------------
-- Procedure structure for deleteOverdueOperationLog
-- ----------------------------
DROP PROCEDURE IF EXISTS `deleteOverdueOperationLog`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `deleteOverdueOperationLog`()
BEGIN
  delete from t_operation_log
  where operation_time < DATE_SUB(CURRENT_DATE() ,INTERVAL 1 YEAR);
END
;;
delimiter ;
-- ----------------------------
-- Event structure for deleteOverdueOperationLog
-- ----------------------------
DROP EVENT IF EXISTS `deleteOverdueOperationLog`;
delimiter ;;
CREATE DEFINER = `root`@`%` EVENT `deleteOverdueOperationLog`
ON SCHEDULE
EVERY '1' DAY STARTS '2020-02-14 05:00:00'
DO BEGIN
CALL deleteOverdueOperationLog();
END
;;
delimiter ;


-- ----------------------------
-- Procedure structure for deleteOverdueInterfaceLog
-- ----------------------------
DROP PROCEDURE IF EXISTS `deleteOverdueInterfaceLog`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `deleteOverdueInterfaceLog`()
BEGIN
  delete from t_interface_log
  where create_time < DATE_SUB(CURRENT_DATE() ,INTERVAL 1 MONTH);
END
;;
delimiter ;
-- ----------------------------
-- Event structure for deleteOverdueInterfaceLog
-- ----------------------------
DROP EVENT IF EXISTS `deleteOverdueInterfaceLog`;
delimiter ;;
CREATE DEFINER = `root`@`%` EVENT `deleteOverdueInterfaceLog`
ON SCHEDULE
EVERY '1' DAY STARTS '2020-02-14 04:30:00'
DO BEGIN
CALL deleteOverdueInterfaceLog();
END
;;
delimiter ;
-- ----------------------------
-- Procedure structure for deleteOverdueMsg
-- ----------------------------
DROP PROCEDURE IF EXISTS `deleteOverdueMsg`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `deleteOverdueMsg`()
BEGIN
declare v_msg_id varchar(20);
declare v_msg_code varchar(64);
declare done int default 0;

declare cur_ cursor for select t.id,t.msg_Code FROM t_msg_info t
where t.create_time < DATE_SUB(CURRENT_DATE() ,INTERVAL 1 MONTH)
and str_to_date(t.effect_end_time,'%Y-%m-%d') < CURRENT_DATE();

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
open cur_;
loop_label:LOOP
FETCH cur_ INTO v_msg_id,v_msg_code;
IF done = 1 THEN
LEAVE loop_label;
END IF;

delete from t_msg_info where id = v_msg_id;
delete from t_msg_info_sys where msg_Code = v_msg_code;
delete from t_msg_publish where msg_Id = v_msg_id;
delete from t_mqtt_file_data where msg_code=v_msg_code;
END LOOP;
CLOSE cur_;

END
;;
delimiter ;

DROP PROCEDURE IF EXISTS `deleteOverdueBroadcastMsg`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `deleteOverdueBroadcastMsg`()
BEGIN
declare v_info_id varchar(64);
declare done int default 0;

declare cur_ cursor for select t.infoId FROM t_msg_broadcast_ebmaininfo t
where t.create_time < DATE_SUB(CURRENT_DATE() ,INTERVAL 1 MONTH) and t.status=5;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN cur_;
-- //打开游标
loop_label:LOOP

FETCH cur_ INTO v_info_id;
IF done = 1 THEN
LEAVE loop_label;
END IF;

delete from t_msg_broadcast_ebmaininfo where infoId = v_info_id;
delete from t_msg_broadcast_ebcontent where infoId = v_info_id;
delete from t_msg_broadcast_publish where infoId = v_info_id;
delete from t_msg_info where msg_code=v_info_id;
delete from t_mqtt_file_data where msg_code=v_info_id;
END LOOP;
CLOSE cur_;
END
;;

-- ----------------------------
-- Event structure for deleteOverdueMsg
-- ----------------------------
DROP EVENT IF EXISTS `deleteOverdueMsg`;
delimiter ;;
CREATE DEFINER = `root`@`%` EVENT `deleteOverdueMsg`
ON SCHEDULE
EVERY '1' DAY STARTS '2020-02-14 04:00:00'
DO BEGIN
CALL deleteOverdueMsg();
CALL deleteOverdueBroadcastMsg();
END
;;

delimiter ;