MySQL分析执行次数最多的SQL的六种方法

-- 查看慢查询配置
show variables like 'slow_query_log%';
show variables like 'long_query_time';

-- 开启慢查询日志（需在my.cnf中配置持久化）
set global slow_query_log = 1;
set global long_query_time = 1;  -- 设置慢查询阈值（秒）
set global slow_query_log_file = '/var/log/mysql/slow.log';

# 分析执行次数最多的慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按执行时间排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- 检查是否开启
show variables like 'performance_schema';

-- 开启events_statements_history（如果未开启）
update performance_schema.setup_consumers 
set enabled = 'yes' 
where name like 'events_statements_history%';

select 
    digest_text as query,
    count_star as exec_count,
    avg_timer_wait/1000000000000 as avg_exec_time_sec,
    sum_rows_examined as rows_examined_sum,
    sum_rows_sent as rows_sent_sum
from performance_schema.events_statements_summary_by_digest
where digest_text is not null
order by count_star desc
limit 10;

-- 查看执行次数最多的语句
select * from sys.statements_with_full_table_scans 
order by exec_count desc 
limit 10;

-- 查看总执行次数最多的语句
select * from sys.statement_analysis 
order by exec_count desc 
limit 10;

-- 查看执行次数多的标准化sql
select 
    query,
    db,
    exec_count,
    total_latency,
    avg_latency,
    rows_sent_avg,
    rows_examined_avg
from sys.x$statements_with_runtimes_in_95th_percentile
order by exec_count desc
limit 10;

-- 开启通用查询日志
set global general_log = 1;
set global general_log_file = '/var/log/mysql/general.log';

-- 分析日志（示例使用awk）
awk '
{
    if ($0 ~ /query/) {
        # 提取sql语句（简化版）
        query = substr($0, index($0, "query:") + 7)
        queries[query]++
    }
}
end {
    for (q in queries) {
        print queries[q] " " q
    }
}' /var/log/mysql/general.log | sort -nr | head -10

-- 查看当前执行的sql
select 
    info as query,
    count(*) as concurrent_count
from information_schema.processlist 
where command = 'query' 
and info is not null
group by info
order by concurrent_count desc
limit 10;

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

# 分析tcpdump抓取的流量
tcpdump -i any -s 65535 -x -nn -q -tttt port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

# 分析general log
pt-query-digest --type genlog /var/log/mysql/general.log

-- 开启必要的监控项
update performance_schema.setup_instruments 
set enabled = 'yes', timed = 'yes' 
where name like 'statement/%';

update performance_schema.setup_consumers 
set enabled = 'yes' 
where name like '%statements%';

-- 定期查询top sql（可做成定时任务）
select 
    schema_name as db,
    digest_text as query,
    count_star as exec_count,
    round(sum_timer_wait/1000000000000, 2) as total_time_sec,
    round(avg_timer_wait/1000000000000, 4) as avg_time_sec,
    sum_rows_examined as rows_examined,
    sum_rows_sent as rows_sent,
    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
order by count_star desc
limit 20;