我要评论在mysql中分析平均响应时间最长的sql，主要有以下几种方法：
1. 使用performance schema（推荐）
查询平均执行时间最长的sql
select
digest_text as query,
schema_name as db,
count_star as exec_count,
round(avg_timer_wait/1000000000000, 6) as avg_exec_time_sec,
round(max_timer_wait/1000000000000, 6) as max_exec_time_sec,
round(sum_timer_wait/1000000000000, 6) as total_exec_time_sec,
sum_rows_examined as rows_examined,
sum_rows_sent as rows_sent,
sum_created_tmp_tables as tmp_tables,
sum_sort_merge_passes as sort_merge_passes
from performance_schema.events_statements_summary_by_digest
where digest_text is not null
and count_star > 0
order by avg_exec_time_sec desc
limit 15;
2. 使用sys schema（mysql 5.7+）
查看平均执行时间最长的语句
-- 按平均执行时间排序
select
query,
db,
exec_count,
total_latency,
avg_latency,
max_latency,
rows_sent_avg,
rows_examined_avg
from sys.statement_analysis
order by avg_latency desc
limit 15;
-- 查看95%分位的慢查询
select
query,
db,
exec_count,
total_latency,
avg_latency,
max_latency
from sys.statements_with_runtimes_in_95th_percentile
order by avg_latency desc
limit 15;
3. 使用慢查询日志分析
使用mysqldumpslow
# 按平均查询时间排序 mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
4. 详细的performance schema分析
包含更多性能指标
select
digest_text as query,
schema_name as db,
count_star as exec_count,
-- 时间统计（单位：秒）
round(avg_timer_wait/1000000000000, 4) as avg_exec_time_sec,
round(max_timer_wait/1000000000000, 4) as max_exec_time_sec,
round(sum_timer_wait/1000000000000, 4) as total_exec_time_sec,
-- 锁时间统计
round(avg_lock_timer_wait/1000000000000, 4) as avg_lock_time_sec,
-- 行统计
sum_rows_examined as rows_examined,
sum_rows_sent as rows_sent,
sum_rows_affected as rows_affected,
round(sum_rows_examined / count_star, 0) as avg_rows_examined,
round(sum_rows_sent / count_star, 0) as avg_rows_sent,
-- 临时表和文件排序
sum_created_tmp_tables as tmp_tables,
sum_created_tmp_disk_tables as tmp_disk_tables,
sum_sort_merge_passes as sort_merge_passes,
sum_sort_rows as sort_rows,
-- 错误和警告
sum_errors as errors,
sum_warnings as warnings,
first_seen as first_seen,
last_seen as last_seen
from performance_schema.events_statements_summary_by_digest
where digest_text is not null
and count_star > 0
having avg_exec_time_sec > 0.001 -- 只关注平均执行时间大于1ms的查询
order by avg_exec_time_sec desc
limit 20;
5. 按模式分类分析
分析不同类型的sql性能
select
case
when digest_text like 'select%' then 'select'
when digest_text like 'insert%' then 'insert'
when digest_text like 'update%' then 'update'
when digest_text like 'delete%' then 'delete'
else 'other'
end as sql_type,
count(*) as query_count,
round(avg(avg_timer_wait/1000000000000), 4) as avg_exec_time_sec,
round(max(max_timer_wait/1000000000000), 4) as max_exec_time_sec,
sum(count_star) as total_executions
from performance_schema.events_statements_summary_by_digest
where digest_text is not null
group by sql_type
order by avg_exec_time_sec desc;
6. 实时监控长时间运行的查询
-- 查看当前正在执行的慢查询
select
p.id as process_id,
p.user as user,
p.host as host,
p.db as database_name,
p.time as execution_time_sec,
p.command as command,
p.state as state,
left(p.info, 200) as query_snippet
from information_schema.processlist p
where p.command = 'query'
and p.time > 5 -- 执行时间超过5秒的查询
order by p.time desc;
7. 定期性能快照对比
-- 创建性能快照表（用于趋势分析）
create table if not exists query_performance_snapshot (
id bigint auto_increment primary key,
snapshot_time timestamp default current_timestamp,
digest varchar(64),
query_text text,
avg_exec_time_sec decimal(10,6),
exec_count bigint,
db_name varchar(64)
);
-- 插入当前性能数据
insert into query_performance_snapshot (digest, query_text, avg_exec_time_sec, exec_count, db_name)
select
digest as digest,
left(digest_text, 1000) as query_text,
round(avg_timer_wait/1000000000000, 6) as avg_exec_time_sec,
count_star as exec_count,
schema_name as db_name
from performance_schema.events_statements_summary_by_digest
where digest_text is not null
and count_star > 0;
-- 查询性能变化趋势
select
query_text,
avg(avg_exec_time_sec) as historical_avg,
max(avg_exec_time_sec) as historical_max,
count(*) as snapshot_count
from query_performance_snapshot
group by digest, query_text
order by historical_avg desc
limit 10;
使用建议
- 生产环境推荐：使用performance schema + sys schema组合
- 深度分析：结合慢查询日志使用pt-query-digest
- 实时监控：设置阈值告警长时间运行的查询
- 定期审查：建立定期性能分析机制
以上就是在mysql中分析平均响应时间最长的sql的六种方法的详细内容，更多关于mysql分析平均响应时间最长的sql的资料请关注代码网其它相关文章！
发表评论