AWS RDS MySQL 5.7生产环境长时查询治理方案咨询

一、先摸底：明确现有长查询的基准情况 #

• 执行SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 3600 AND COMMAND = 'Query';，抓取当前运行超过1小时的查询，记录它们的用户、语句类型、实际耗时，统计出正常业务查询的最长合理时长，为后续设置阈值提供依据。
• 利用RDS的CloudWatch监控（如Query Latency指标）或开启Performance Schema，回溯过往长查询数据，全面掌握业务查询的耗时范围。

二、精准控制超时，不误杀合法脚本 #

1. 按用户/角色差异化设置执行超时 #

MySQL 5.7支持会话级max_execution_time，无需全局一刀切：

• 给普通只读用户（含PMA用户）设置默认超时，比如2小时：

  ALTER USER 'read_only_user'@'%' SET MAX_EXECUTION_TIME = 7200;
  

• 给需要运行数据修正脚本的读写用户，保持全局默认的0（无超时），或单独设置更长阈值（比如24小时）：

  ALTER USER 'script_user'@'%' SET MAX_EXECUTION_TIME = 86400;
  

2. 用定时事件自动清理超时长查询 #

创建定时事件，定期查杀符合条件的长查询，避免手动操作滞后：

DELIMITER //
CREATE EVENT kill_long_queries
ON SCHEDULE EVERY 10 MINUTE
DO
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE proc_id INT;
  -- 仅查杀超过2小时、非脚本用户、非修正脚本的查询
  DECLARE cur CURSOR FOR 
    SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST 
    WHERE TIME > 7200 
      AND COMMAND = 'Query' 
      AND USER NOT IN ('root', 'script_user')
      AND INFO NOT LIKE '%data_correction_%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO proc_id;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @kill_stmt = CONCAT('KILL ', proc_id);
    PREPARE stmt FROM @kill_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

注意：RDS需先通过参数组开启事件调度器，设置event_scheduler = ON，应用参数组后生效。

三、从根源减少长查询产生 #

• 限制PMA查询超时：在PMA配置文件中设置$cfg['ExecTimeLimit'] = 3600;，让PMA主动终止超时的交互式查询，避免后台残留。
• 开启慢查询日志：在RDS参数组中设置slow_query_log = 1、long_query_time = 60，定期分析慢查询日志，给耗时久的SELECT语句加索引、改写逻辑，从根本上降低长查询数量。
• 用资源组约束只读用户：若为MySQL 5.7.22及以上版本，给只读用户分配资源组，限制CPU、内存使用，从资源层面约束长查询的影响范围。

四、元数据锁的应急处理 #

• 出现元数据锁阻塞时，执行SHOW ENGINE INNODB STATUS;或SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;定位持有锁的长查询进程，手动查杀快速恢复业务。
• 执行表结构修改操作时，尽量选在低峰期，提前查杀所有可能持有元数据锁的长查询，避免阻塞。

内容的提问来源于stack exchange，提问作者Rdba