Diag Mysql Slow Query

MySQL Slow Query Full-Chain Diagnostics

用途

当用户报告查询慢、接口超时、或怀疑 SQL 性能问题时触发。覆盖：

• 接口响应变慢，怀疑数据库层
• SHOW PROCESSLIST 看到大量 Sending data / Waiting for table metadata lock
• 慢查询日志告警
• 定期慢 SQL 巡检

本技能是自包含诊断 Runbook。

输入

• MySQL 连接信息（host / port / user / password）
• 可选：已知慢 SQL 片段或时间段

输出

• 慢查询分析报告（填充输出模板）
• Top-N 慢 SQL + EXPLAIN 结果 + 优化建议

前置条件

| 项 | 要求 | |----|------| | MySQL 权限 | PROCESS, SELECT（performanceschema） | | 版本 | MySQL 5.7+（8.0 优先用 performanceschema） |

诊断步骤

Step 1: 检查慢查询日志配置

SHOW VARIABLES LIKE 'slow_query_log';        -- 是否开启
SHOW VARIABLES LIKE 'slow_query_log_file';   -- 日志路径
SHOW VARIABLES LIKE 'long_query_time';       -- 阈值（秒）
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';  -- 是否记录无索引查询
SHOW VARIABLES LIKE 'min_examined_row_limit';

如果未开启，建议临时开启（需 SUPER 权限）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

Step 2: 从 performance_schema 获取 Top-N 慢 SQL（MySQL 8.0+）

SELECT
  DIGEST_TEXT AS query_pattern,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_time_s,
  ROUND(AVG_TIMER_WAIT / 1000000000000, 4) AS avg_time_s,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent,
  SUM_NO_INDEX_USED AS no_index_count,
  FIRST_SEEN, LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
  AND DIGEST_TEXT NOT LIKE 'SHOW%'
  AND DIGEST_TEXT NOT LIKE 'SET%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Step 3: EXPLAIN 可疑 SQL

EXPLAIN FORMAT=JSON {slow_query};
-- 或
EXPLAIN ANALYZE {slow_query};  -- MySQL 8.0.18+

关注： | 字段 | 问题信号 | |------|---------| | type = ALL | 全表扫描 | | rows >> 实际返回行数 | 扫描过多 | | Extra = Using filesort | 排序未用索引 | | Extra = Using temporary | 临时表 | | possible_keys = NULL | 无可用索引 |

Step 4: 检查缺失索引

-- 未使用索引的查询统计（8.0+）
SELECT
  OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
  COUNT_STAR, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NULL
  AND OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY COUNT_STAR DESC
LIMIT 20;

Step 5: 检查表统计信息是否过期

-- 统计信息最后更新时间（8.0+）
SELECT
  TABLE_SCHEMA, TABLE_NAME,
  TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH,
  UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
ORDER BY TABLE_ROWS DESC
LIMIT 20;

-- 手动刷新统计信息
ANALYZE TABLE {schema}.{table};

输出模板

MySQL Slow Query Analysis Report
════════════════════════════════════════
Instance:        {host}:{port}
Analysis Time:   {timestamp}
Slow Query Log:  {enabled/disabled} (threshold: {long_query_time}s)

Top-10 Slow Queries (by total time)
────────────────────────────────────────
  #1  {query_pattern_1}
      Exec: {exec_count}x | Avg: {avg_time}s | Rows examined: {rows_examined}
      No index used: {no_index_count}x
      Last seen: {last_seen}

  #2  {query_pattern_2}
      ...

EXPLAIN Summary
────────────────────────────────────────
  Query #1: type={type} | rows={rows} | Extra={extra}
  Issue: {full_scan / filesort / temporary / no_index}

Missing Indexes
  | Table           | Column(s)      | Impact        |
  |-----------------|----------------|---------------|
  | {table_1}       | {columns}      | {exec_count}x |

Stale Statistics
  {tables with UPDATE_TIME > 7 days ago}

Root Cause
  {root_cause_analysis}

Recommendations
  1. [Index]  {add_index_suggestion}
  2. [Query]  {rewrite_suggestion}
  3. [Config] {config_suggestion}

告警阈值

| 指标 | Warning | Critical | |------|---------|----------| | Avg query time | > 1s | > 5s | | Queries without index/min | > 10 | > 50 | | Full table scans/min | > 100 | > 500 | | Rows examined/rows sent ratio | > 100:1 | > 1000:1 |

推荐输出格式

执行完毕后输出诊断报告：

结论：

| 排查环节 | 发现 | 证据 | |---------|------|------| | ... | {...} | {...} |

根因： 修复建议：

约束

• 所有 SQL 均为只读查询。SET GLOBAL 仅用于临时开启慢查询日志，诊断完成后建议恢复。
• 不猜测密码；连接信息缺失时提示用户补充。
• EXPLAIN ANALYZE 会实际执行查询，确认用户同意后再运行。

Quick Reference

| 动作 | 查询 | |------|------| | 慢查询日志状态 | SHOW VARIABLES LIKE 'slow_query_log%' | | Top 慢 SQL (8.0) | SELECT ... FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC | | EXPLAIN | EXPLAIN FORMAT=JSON {sql} | | 缺失索引 | performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NULL | | 刷新统计 | ANALYZE TABLE {schema}.{table} |

示例

用户说：「db-prod-01 最近接口变慢了，帮忙看看 SQL」

Agent 执行 Step 1-5，输出 Top-10 慢 SQL、EXPLAIN 分析、缺失索引建议。

专题引用

无外部 references——本技能为自包含诊断 Runbook。 如需 MySQL 数据源连接管理，联动 infra-datasource-ops。

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: seed-forge
• Source: seed-forge/harness-ai-kit
• License: Apache-2.0
• Homepage: https://pypi.org/project/harness-ai-kit/

Install and usage instructions live in the source repository linked above.