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（performance_schema）
版本	MySQL 5.7+（8.0 优先用 performance_schema）

诊断步骤

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。