我要评论在mysql中分析执行次数最多的sql，主要有以下几种方法：
1. 使用mysql慢查询日志
开启慢查询日志
-- 查看慢查询配置 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分析
# 分析执行次数最多的慢查询 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 按执行时间排序 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
2. 使用performance schema
开启performance schema
-- 检查是否开启 show variables like 'performance_schema'; -- 开启events_statements_history（如果未开启） update performance_schema.setup_consumers set enabled = 'yes' where name like 'events_statements_history%';
查询执行次数最多的sql
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;
3. 使用sys schema（mysql 5.7+）
-- 查看执行次数最多的语句
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;
4. 使用通用日志（不推荐生产环境）
-- 开启通用查询日志
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
5. 使用information_schema.processlist（实时监控）
-- 查看当前执行的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;
6. 使用pt-query-digest工具
# 分析慢查询日志 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
推荐的生产环境方案
对于生产环境，建议组合使用：
- 长期监控：performance schema + sys schema
- 性能分析：慢查询日志 + pt-query-digest
- 实时监控：information_schema.processlist
完整的performance schema监控示例
-- 开启必要的监控项
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;
选择哪种方法取决于你的具体需求：实时监控用performance schema，深度分析用慢查询日志，快速排查用sys schema。
以上就是mysql分析执行次数最多的sql的六种方法的详细内容，更多关于mysql执行次数最多的sql的资料请关注代码网其它相关文章！
发表评论