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

目录

• 1. 使用mysql慢查询日志
• 开启慢查询日志
• 使用mysqldumpslow分析
• 2. 使用Performance Schema
• 开启Performance Schema
• 查询执行次数最多的SQL
• 3. 使用Sys Schema（MySQL 5.7+）
• 4. 使用通用日志（不推荐生产环境）
• 5. 使用INFORMATION_SCHEMA.PROCESSLIST（实时监控）
• 6. 使用pt-query-digest工具
• 推荐的生产环境方案
• 完整的Performance Schema监控示例

在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（实时监控）http://www.devze.com

-- 查看当前执行的SQL
SELECT 
    INFO AS query,
    COUNT(*) AS concurrent_count
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE COMMAND = 'Query' 
AND INFO IS NOT NULL
GROUpythonP 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 --t编程客栈ype genlog /var/log/mysql/general.log

推荐的生产环境方案

对于生产环境，建议组合使用：

1. 长期监控：Performance Schema + Sys Schema
2. 性能分析：慢查询日志 + pt-query-digest
3. 实时监控：INFORMATION_SCHEMA.PROCESSLIST

完整的Performance Schema监控示例

-- 开启必要的监控项
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE 'statjsement/%';

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的资料请关注编程客栈(www.devze.com)其它相关文章！