MySQL查询耗时超20秒问题排查求助

问题概述 #

已收集诊断信息，但仍无法定位MySQL查询耗时超20秒的原因，未发现明显长锁时间或意外执行的大量子查询。该查询为每位站点连接用户执行一次，当前执行频率约6次/秒，已造成严重性能问题。

application_logs表约有250万行数据，application_sessions表约2万行，计划清理7天前的application_logs数据。目前EXPLAIN命令仍在执行中。

目标查询语句 #

SELECT message, level, logged_at FROM application_logs
 WHERE application_id = 5 AND
       message NOT LIKE '(build)%' AND
       logged_at >= ( 
           SELECT launched_at 
           FROM application_sessions 
           WHERE application_id = 5 
           ORDER BY id DESC LIMIT 1
        ) 
        ORDER BY id DESC LIMIT 500;

慢查询日志详情 #

慢查询SQL #

SELECT message, level, logged_at 
 FROM application_logs 
 WHERE application_id = 9 AND message NOT LIKE '(build)%' 
 AND logged_at >= (
   SELECT launched_at 
   FROM application_sessions 
   WHERE application_id = 9 
   ORDER BY id DESC LIMIT 1
   ) 
 ORDER BY id DESC LIMIT 100;

慢查询统计信息 #

User@Host: remote[remote] @ [**************] Thread_id: 14180
Schema: **** QC_hit: No Query_time: 21.056101 Lock_time: 0.000102
Rows_sent: 19 Rows_examined: 233153 Rows_affected: 0 Bytes_sent: 3421

带时间戳的执行语句 #

SET timestamp=1681832466;
SELECT message, level, logged_at 
FROM application_logs 
WHERE application_id = 9 AND message NOT LIKE '(build)%' 
AND logged_at >= (
    SELECT launched_at 
    FROM application_sessions 
    WHERE application_id = 9 
    ORDER BY id DESC LIMIT 1
) ORDER BY id DESC 
LIMIT 100;

优化建议 #

• 添加复合索引：针对application_logs表，创建(application_id, logged_at, id)的复合索引。这样可以快速过滤指定application_id且logged_at符合条件的数据，同时满足ORDER BY id DESC的排序需求，避免大量行扫描。
• 优化子查询逻辑：可以提前获取最新的launched_at值，作为变量传入主查询，避免主查询执行时重复调用子查询；或者改用JOIN方式重写查询：

  SELECT al.message, al.level, al.logged_at
  FROM application_logs al
  JOIN (
      SELECT launched_at 
      FROM application_sessions 
      WHERE application_id = 5 
      ORDER BY id DESC LIMIT 1
  ) AS s ON al.logged_at >= s.launched_at
  WHERE al.application_id = 5 
    AND al.message NOT LIKE '(build)%'
  ORDER BY al.id DESC LIMIT 500;
  

• 清理历史数据：尽快清理7天前的application_logs数据，减少表总数据量，降低查询扫描行数。
• 替换NOT LIKE过滤：如果业务允许，新增is_build字段标记是否为build类日志，用is_build = 0的等值查询替代message NOT LIKE '(build)%'，提升过滤效率。

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