如何从CloudWatch Insights解析MySQL慢查询日志

日志示例 #

User@Host: abc_ro[abc_ro] @ [x.x.x.xxx] #

Thread_id: 4935648 Schema: mytable QC_hit: No #

Query_time: 1.282014 Lock_time: 0.000154 Rows_sent: 6 Rows_examined: 964382 #

Rows_affected: 0 #

use mydummydb;
SET timestamp=1662984070;
SELECT * FROM mytable

1. 基础字段提取 #

利用CloudWatch Insights的parse命令，通过正则表达式匹配日志中的键值对，提取核心指标：

fields @timestamp, @message
| parse @message /# User@Host: (?<UserHost>[^\s]+)/
| parse @message /# Thread_id: (?<ThreadId>\d+) Schema: (?<Schema>[^\s]+) QC_hit: (?<QCHit>[^\s]+)/
| parse @message /# Query_time: (?<QueryTime>[0-9.]+) Lock_time: (?<LockTime>[0-9.]+) Rows_sent: (?<RowsSent>\d+) Rows_examined: (?<RowsExamined>\d+)/
| parse @message /# Rows_affected: (?<RowsAffected>\d+)/
| parse @message /(?<SQLCommand>(use .*;|SET .*;|SELECT .*))/
| filter @message like '#' or @message like 'use ' or @message like 'SET ' or @message like 'SELECT '
| sort @timestamp desc
| limit 20

• 该查询会提取出用户主机、线程ID、Schema、查询耗时、锁耗时、返回行数等关键字段，同时捕获完整的SQL命令
• filter用于筛选慢查询日志的有效行，排除无关内容；sort和limit控制结果排序和数量

2. 聚合分析慢查询 #

如果需要统计性能瓶颈，可通过聚合函数按Schema或SQL语句分组分析：

fields @timestamp, @message
| parse @message /# Query_time: (?<QueryTime>[0-9.]+)/
| parse @message /Schema: (?<Schema>[^\s]+)/
| parse @message /(?<SQLCommand>SELECT .*)/
| stats avg(QueryTime) as AvgQueryTime, max(QueryTime) as MaxQueryTime, count(*) as QueryCount by Schema, SQLCommand
| sort MaxQueryTime desc
| limit 10

• 这个查询会计算每个SQL语句的平均耗时、最大耗时和执行次数，快速定位最影响性能的慢查询

3. 关联多行日志 #

MySQL慢查询日志为多行结构，CloudWatch Insights默认按行处理，可通过以下方式合并同一查询的相关行：

fields @timestamp, @message
| sort @timestamp, @logStream
| fill prev(@message) as prevMessage
| parse prevMessage /# Query_time: (?<QueryTime>[0-9.]+)/
| parse @message /(?<SQLCommand>(use .*;|SET .*;|SELECT .*))/
| where SQLCommand is not null
| fields @timestamp, QueryTime, SQLCommand
| sort @timestamp desc
| limit 20

• 通过fill prev将上一行的日志内容填充到当前行，实现查询语句与对应耗时指标的关联，解决多行日志拆分问题

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