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

目录

• 1. 使用Performance Schema（推荐）
• 查询平均执行时间最长的SQL
• 2. 使用Sys Schema（mysql 5.7+）
• 查看平均执行时间最长的语句
• 3. 使用慢查询日志分析
• 使用mysqldumpslow
• 4. 详细的Performance Schema分析
• 包含更多性能指标
• 5. 按模式分类分析
• 分析不同类型的SQL性能
• 6. 实时监控长时间运行的查询
• 7. 定期性能快照对比
• 使用建议

在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

# 按平均xKYHwB查询时间排序
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,
    
    -- 锁时间统计
    ROphpUND(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_SORpythonT_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
FpythonROM 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中分析平均www.devze.com响应时间最长的SQL的六种方法的详细内容，更多关于MySQL分析平均响应时间最长的SQL的资料请关注编程客栈(www.devze.com)其它相关文章！