kill慢sql的存储过程

kill慢sql的存储过程

• 创建存储过程
  • 定义变量
  • 查询mysql进程
  • 循环select集, 拼接kill语句
  • 执行sql语句
• 调用存储过程
  • call killsql()

CREATE PROCEDURE `killsql`()
BEGIN
	DECLARE v_sql_1 VARCHAR(500);
	DECLARE kill_id INT;
	DECLARE num INT DEFAULT 0;
	DECLARE id_list CURSOR FOR
	SELECT
		id 
	FROM
		information_schema.PROCESSLIST 
	WHERE
		info IS NOT NULL 
		AND ( info LIKE '%shipment_detail.itemCode%' OR info LIKE '%LIMIT 0,100%' ) 
		AND state != 'executing' 
	ORDER BY
		time DESC;
		
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET num=1;
		
	OPEN id_list; 
		-- 将游标中的值赋值给变量，要注意sql结果列的顺序
		FETCH id_list INTO kill_id; 
		WHILE num <> 1 DO
			SET v_sql_1 = CONCAT('kill ',kill_id,';');
			SET @sql_1 = v_sql_1;
			-- 预处理需要执行的动态SQL，其中stmt是一个变量
			PREPARE stmt1 FROM @sql_1;  
			-- 执行SQL语句
			EXECUTE stmt1;   
			-- 释放掉预处理段   
			DEALLOCATE PREPARE stmt1;
			FETCH id_list INTO kill_id;
			
		END WHILE;
	CLOSE id_list;

		
END

• 若有需要, 可以设置定时任务执行存储过程, 实现自动kill慢sql