无需配置与监控，3种MySQL原生方案精准定位慢SQL

无需配置与监控，3种MySQL原生方案精准定位慢SQL

在日常的MySQL数据库运维工作中，慢SQL往往是导致系统性能下降、响应延迟的“隐形杀手”。传统排查慢SQL的方式，如修改配置文件开启慢查询日志、依赖应用层打印SQL日志或部署外部监控系统，常常受限于环境权限、业务影响等因素而无法实施。

幸运的是，在MySQL 5.7及以上的现代版本中，内置了Performance Schema和sys schema两大“利器”，无需任何配置改动、不依赖外部工具，就能高效排查慢SQL。本文将从紧急排查、常规分析、底层查询三个维度，详细介绍三种由浅入深的慢SQL定位方法，帮助运维与开发人员快速解决数据库性能问题。

一、实时抓取：用SHOW FULL PROCESSLIST锁定“现行”慢SQL

当系统突然出现响应缓慢、数据库CPU利用率飙升等紧急情况时，首要任务是快速找到正在运行的“阻塞型”慢SQL。此时，SHOW FULL PROCESSLIST命令就像一把“即时放大镜”，能瞬间呈现数据库当前的所有连接与查询状态，帮你锁定“现行犯”。

1.1 适用场景

• 系统突发性能故障，需紧急定位当前阻塞数据库的SQL语句；
• 需快速确认某个慢查询的发起用户、来源IP及操作数据库；
• 排查是否存在长时间运行（如超过5秒）的查询占用资源。

1.2 操作步骤

1. 通过MySQL命令行客户端登录数据库（需具备PROCESS权限，通常管理员账号默认拥有）；
2. 执行核心命令：SHOW FULL PROCESSLIST;，无需额外配置，执行后立即返回结果。

1.3 结果解读：重点关注7个核心字段

执行命令后，结果会以表格形式展示，每个字段都承载着关键信息，需重点解读以下7列：

1.4 优缺点分析

优点

• 零配置启动：无需修改my.cnf等配置文件，登录数据库即可执行，紧急场景下效率极高；
• 实时性强：返回结果为当前数据库的“实时快照”，能瞬间捕捉正在运行的慢查询，避免慢SQL执行完成后无法追溯；
• 操作简单：命令格式简洁，无需复杂语法，运维新手也能快速上手。

缺点

• 快照局限性：仅能展示执行命令瞬间的查询状态，若慢SQL已执行完成（如执行了3秒后结束），或高频执行的短耗时SQL（如每次执行0.5秒，1秒执行10次），则无法捕捉；
• 无历史记录：数据仅在执行命令时临时生成，无法追溯10分钟前或1小时前的慢SQL情况，不适合系统性分析；
• 信息碎片化：仅能看到单条SQL的当前状态，无法统计某类SQL的执行频率、总耗时等聚合数据，难以定位“高频低耗但总负载高”的SQL。

二、聚合分析：用sys schema实现慢SQL的系统性排查

对于非紧急的常规性能优化场景，我们需要的不仅是“单次慢SQL”的定位，更是“某类SQL的整体性能画像”——比如过去1小时内平均耗时最长的SQL、总耗时最高的高频SQL、存在全表扫描的低效SQL等。此时，sys schema就是最优选择。

sys schema是MySQL 5.7.7版本后官方引入的“性能分析工具集”，它基于Performance Schema的底层数据，通过预设的视图和函数，将复杂的性能数据转化为直观、易读的聚合结果，无需手动编写复杂的关联查询，就能实现多维度的慢SQL分析。

2.1 适用场景

• 系统性排查过去一段时间内（如1天、1小时）的慢SQL，定位优化优先级最高的SQL类型；
• 分析高频执行的SQL（如每秒执行100次），即使单次耗时短，总耗时也可能占据数据库资源的50%以上；
• 识别存在全表扫描、临时表创建、排序优化缺失等“性能隐患”的SQL；
• 对比不同业务模块（如订单模块、用户模块）的SQL执行效率，定位核心瓶颈模块。

2.2 核心查询：3类常用分析语句

sys schema提供了多个预设视图，其中statement_analysis（SQL语句分析视图）和statements_with_full_table_scans（全表扫描SQL视图）是排查慢SQL的核心工具，以下为3类最常用的查询场景及语句：

场景1：查询平均执行时间最长的SQL（单次最慢）

这类SQL通常是“硬骨头”——单次执行耗时可能长达几十秒，直接导致业务超时，需优先优化。通过以下语句可查询平均延迟最高的10条SQL：

-- 查询平均延迟最高的10条SQL
SELECT
    query,          -- SQL模板（参数被替换为?，避免敏感数据泄露，同时聚合同类SQL）
    db,             -- 所属数据库
    exec_count,     -- 执行次数
    total_latency,  -- 总耗时（格式为时间单位，如1h 20m 30s，直观易读）
    avg_latency,    -- 平均耗时（核心指标，需重点关注）
    rows_examined_avg,  -- 平均扫描行数（若远大于返回行数，可能无索引）
    rows_sent_avg       -- 平均返回行数（业务所需的实际数据量）
FROM
    sys.statement_analysis
ORDER BY
    avg_latency DESC  -- 按平均耗时降序排列，取Top10
LIMIT 10;

结果解读要点：

• 若avg_latency超过业务阈值（如5秒），需优先优化；
• 对比rows_examined_avg与rows_sent_avg：若前者是后者的10倍以上（如扫描1000行仅返回10行），通常意味着SQL缺少合适的索引，导致数据库“无效扫描”过多。

场景2：查询总执行时间最长的SQL（积少成多）

有些SQL单次执行耗时仅0.1秒，但1秒内执行1000次，总耗时就达到100秒，长期占用大量CPU和IO资源，这类“高频低耗”SQL往往是系统隐形的性能瓶颈。通过以下语句可查询总耗时最高的10条SQL：

-- 查询累积耗时最长的10条SQL
SELECT
    query,
    db,
    exec_count,     -- 重点关注：执行次数是否过高
    total_latency,  -- 核心指标：总耗时，反映SQL对资源的整体占用
    avg_latency,    -- 辅助判断：单次耗时是否合理
    rows_examined_avg
FROM
    sys.statement_analysis
ORDER BY
    total_latency DESC  -- 按总耗时降序排列
LIMIT 10;

结果解读要点：

• 若exec_count极高（如超过10万次）且total_latency排名靠前，即使avg_latency低，也需优化（如通过缓存减少数据库查询、优化SQL执行逻辑）；
• 例如：某条查询用户信息的SQL，avg_latency为0.05秒，exec_count为100万次，total_latency达到5万秒（约14小时），这类SQL优化后能显著降低数据库整体负载。

场景3：查询全表扫描的SQL（性能杀手）

全表扫描（Full Table Scan）是MySQL性能的“头号杀手”之一——当表数据量达到10万行以上时，全表扫描会导致IO开销剧增，同时阻塞其他查询。sys.statements_with_full_table_scans视图专门聚合了所有执行过全表扫描的SQL，无需手动判断，直接定位风险语句：

-- 查询执行过全表扫描的SQL（排除sys系统库）
SELECT
    *  -- 若需精简结果，可指定query、db、exec_count、total_latency等字段
FROM
    sys.statements_with_full_table_scans
WHERE
    db != 'sys'  -- 排除系统库的全表扫描（无业务意义）
ORDER BY
    exec_count DESC;  -- 按执行次数降序，优先处理高频全表扫描SQL

结果解读要点：

• 若某条SQL的exec_count较高（如超过1万次）且存在全表扫描，需立即优化（如添加合适的索引、调整WHERE条件）；
• 例外情况：若表数据量极小（如仅10行），全表扫描的性能影响可忽略，无需强制优化。

2.3 优缺点分析

优点

• 功能全面：支持平均耗时、总耗时、全表扫描等多维度分析，覆盖常规性能优化的所有核心场景；
• 易用性强：视图字段命名直观（如avg_latency、exec_count），无需理解Performance Schema的底层表结构，新手也能快速上手；
• 数据聚合：自动将同类SQL（如参数不同的同一查询）聚合为模板，避免重复分析，提高排查效率；
• 零配置：MySQL 5.7.7及以上版本默认开启sys schema，无需修改任何配置文件。

缺点

• 依赖版本：仅支持MySQL 5.7.7及以上版本，若使用5.6及以下版本，需升级或改用其他方法；
• 内存存储：统计数据存储在内存中，MySQL服务重启后，历史数据会被清空，无法追溯重启前的慢SQL；
• 数据粒度：默认统计粒度为“SQL模板”，若需查看某条具体SQL（含参数）的执行情况，需结合其他工具（如Performance Schema）。

三、终极方案：直接查询Performance Schema定位慢SQL

若你的MySQL版本低于5.7.7（如5.6版本），无法使用sys schema，但仍可通过直接查询Performance Schema的底层表来排查慢SQL。

Performance Schema是MySQL 5.5版本后引入的性能监控引擎，它通过“事件采集”的方式，记录数据库的所有SQL执行、锁等待、IO操作等事件，并存储在多个底层表中（如events_statements_summary_by_digest、events_statements_current）。虽然查询语句比sys schema复杂，但它是所有性能数据的“源头”，能提供最细粒度的分析结果。

3.1 前提：确认Performance Schema已开启

首先需确认Performance Schema是否开启（MySQL 5.6及以上版本默认开启），执行以下命令：

-- 检查Performance Schema是否开启
SHOW VARIABLES LIKE 'performance_schema';

若返回结果中Value为ON，则可直接查询；若为OFF，需临时开启（无需重启数据库，重启后失效，符合“不修改配置文件”的要求）：

-- 临时开启Performance Schema（无需修改配置文件）
SET GLOBAL performance_schema = ON;

3.2 核心查询：总耗时最长的SQL

events_statements_summary_by_digest是Performance Schema中用于聚合SQL执行统计的核心表，它按SQL模板（通过DIGEST字段唯一标识）聚合执行次数、总耗时、扫描行数等数据，与sys schema的statement_analysis视图原理一致，但需手动处理字段格式（如耗时单位）。

以下为查询总耗时最长的10条SQL的语句：

-- 直接查询Performance Schema，获取总耗时最长的10条SQL
SELECT
    SCHEMA_NAME,              -- 所属数据库名称（对应sys schema的db字段）
    DIGEST_TEXT,              -- SQL模板（参数被替换为?，对应sys schema的query字段）
    COUNT_STAR AS exec_count, -- 执行次数（对应sys schema的exec_count）
    SUM_TIMER_WAIT AS total_latency_pico,  -- 总耗时（单位：皮秒，需换算）
    AVG_TIMER_WAIT AS avg_latency_pico,    -- 平均耗时（单位：皮秒，需换算）
    SUM_ROWS_EXAMINED,        -- 总扫描行数
    SUM_ROWS_SENT             -- 总返回行数
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    -- 排除系统库（无业务意义）
    SCHEMA_NAME IS NOT NULL 
    AND SCHEMA_NAME NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY
    SUM_TIMER_WAIT DESC  -- 按总耗时降序排列
LIMIT 10;

3.3 关键注意事项：单位换算与字段映射

Performance Schema的字段与sys schema存在对应关系，但需注意单位换算（耗时单位为皮秒，1秒 = 10¹²皮秒），避免因单位误解导致误判：

换算示例：若SUM_TIMER_WAIT的值为5000000000000皮秒，换算为秒则为5000000000000 / 10¹² = 5秒，即总耗时5秒。

3.4 优缺点分析

优点

• 版本兼容性强：支持MySQL 5.5及以上版本，覆盖低版本数据库场景；
• 数据源头：所有性能数据的“原始存储”，可获取最细粒度的统计（如单条SQL的每次执行事件）；
• 灵活扩展：可根据需求自定义查询维度（如按时间范围过滤、按用户过滤），比sys schema更灵活。

缺点

• 语句复杂：需手动关联多个底层表（如需按时间过滤，需关联events_statements_current表），语法难度高；
• 单位不直观：耗时单位为皮秒，需手动换算，易导致误判（如将皮秒误认为毫秒）；
• 学习成本高：需理解Performance Schema的表结构、事件类型等概念，新手上手难度大。

四、总结：不同场景下的最佳实践路径

通过以上三种方法的对比，我们可根据不同的排查场景，选择最优方案，形成标准化的慢SQL排查流程：

4.1 紧急故障排查（系统突然变慢）

核心目标：快速定位当前运行的慢SQL，终止阻塞进程，恢复系统正常运行。
推荐方法：SHOW FULL PROCESSLIST
操作步骤：

1. 登录MySQL命令行，执行SHOW FULL PROCESSLIST;；
2. 筛选Command为Query且Time超过阈值（如5秒）的记录；
3. 查看Info字段获取完整SQL，确认是否为业务异常查询；
4. 若需终止，执行KILL [Id];（需谨慎，避免终止正常业务查询）。

4.2 常规性能优化（非紧急场景）

核心目标：系统性分析过去一段时间的慢SQL，定位优化优先级最高的SQL类型，从根本上提升数据库性能。
推荐方法：sys schema（MySQL 5.7.7+）
操作步骤：

1. 执行“平均耗时最长SQL”查询，定位单次最慢的SQL，优先优化（如添加索引、重构SQL）；
2. 执行“总耗时最长SQL”查询，定位高频低耗的SQL，通过缓存、业务优化减少执行次数；
3. 执行“全表扫描SQL”查询，处理所有高频全表扫描语句，避免IO资源浪费；
4. 定期（如每天、每周）执行以上查询，形成性能优化报告，跟踪优化效果。

4.3 低版本兼容（MySQL 5.5/5.6）

核心目标：在无法使用sys schema的低版本环境中，实现慢SQL的聚合分析。
推荐方法：直接查询Performance Schema
操作步骤：

1. 确认Performance Schema已开启（执行SHOW VARIABLES LIKE 'performance_schema';）；
2. 执行底层表查询语句，获取总耗时/平均耗时最长的SQL；
3. 手动换算耗时单位（皮秒→秒/毫秒），避免单位误解；
4. 若需更细粒度分析（如按时间过滤），关联events_statements_current表（需了解表结构）。

4.4 三种方法对比总结

通过本文介绍的三种MySQL原生方法，无需修改配置文件、不依赖外部工具，就能覆盖从紧急故障排查到常规性能优化的全场景慢SQL定位需求。在实际工作中，建议优先使用sys schema（MySQL 5.7.7+），兼顾效率与功能；遇到紧急情况时，用SHOW FULL PROCESSLIST快速止血；低版本环境则通过Performance Schema实现兼容。掌握这些方法，能让你在不影响业务的前提下，高效解决MySQL慢SQL问题，保障数据库性能稳定。