快捷導(dǎo)航

在MySQL中分析平均響應(yīng)時間最長的SQL的六種方法

更新時間：2025年10月23日 09:08:11 作者：學(xué)亮編程手記

這篇文章主要介紹了在MySQL中分析平均響應(yīng)時間最長的SQL的六種方法,主要方法包括使用PerformanceSchema、SysSchema、慢查詢?nèi)罩尽⒃敿?xì)性能分析、按模式分類、實時監(jiān)控和快照對比,推薦結(jié)合使用,需要的朋友可以參考下

1. 使用Performance Schema（推薦）

查詢平均執(zhí)行時間最長的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+）

查看平均執(zhí)行時間最長的語句

-- 按平均執(zhí)行時間排序
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. 使用慢查詢?nèi)罩痉治?/h2>

使用mysqldumpslow

# 按平均查詢時間排序
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

4. 詳細(xì)的Performance Schema分析

包含更多性能指標(biāo)

SELECT 
    DIGEST_TEXT AS query,
    SCHEMA_NAME AS db,
    COUNT_STAR AS exec_count,
    
    -- 時間統(tǒng)計（單位：秒）
    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,
    
    -- 鎖時間統(tǒng)計
    ROUND(AVG_LOCK_TIMER_WAIT/1000000000000, 4) AS avg_lock_time_sec,
    
    -- 行統(tǒng)計
    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  -- 只關(guān)注平均執(zhí)行時間大于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. 實時監(jiān)控長時間運(yùn)行的查詢

-- 查看當(dāng)前正在執(zhí)行的慢查詢
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  -- 執(zhí)行時間超過5秒的查詢
ORDER BY p.TIME DESC;

7. 定期性能快照對比

-- 創(chuàng)建性能快照表（用于趨勢分析）
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)
);

-- 插入當(dāng)前性能數(shù)據(jù)
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;