在MySQL中分析平均响应时间最长的SQL的六种方法_Mysql

在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;