第一章 性能诊断工具箱

1.1 实时监控三剑客

SHOW PROCESSLIST实战解读

核心作用：实时捕获数据库当前线程状态，快速定位阻塞源

基础用法与关键字段解析

-- 完整版查询（8.0+支持）
SHOW FULL PROCESSLIST\G

/* 输出示例
Id: 137
User: app_user
Host: 192.168.1.12:52134
db: order_db
Command: Query
Time: 12              ← 已执行时间(秒)
State: Sending data   ← 线程状态（重点关注！）
Info: SELECT * FROM orders WHERE status=0 FOR UPDATE
*/

关键状态码应急处理

实战案例：锁等待定位

-- 步骤1：发现大量"Waiting for table metadata lock"
mysql> SHOW PROCESSLIST;

-- 步骤2：查找阻塞源（查看Info列中的DDL语句）
Id: 256 | User: dba_admin | State: altering table | Info: ALTER TABLE orders ADD INDEX idx_status(status)

-- 步骤3：评估后强制终止
mysql> KILL 256;  -- 慎重操作！

Performance Schema重点表解析

核心价值：细粒度监控SQL执行细节，无需第三方工具

关键表与监控场景

-- 统计耗时最高的TOP 10 SQL
SELECT digest_text, 
       COUNT_STAR AS exec_count,
       AVG_TIMER_WAIT/1e9 AS avg_ms 
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC 
LIMIT 10;

-- 查看表访问热点（8.0+）
SELECT OBJECT_SCHEMA, OBJECT_NAME,
       COUNT_READ, COUNT_WRITE 
FROM performance_schema.table_io_waits_summary_by_table 
ORDER BY COUNT_READ + COUNT_WRITE DESC 
LIMIT 5;

高级应用：追踪事务生命周期

-- 开启事务监控（需提前配置）
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_transactions%';

-- 查看未提交的长事务
SELECT * 
FROM performance_schema.events_transactions_current 
WHERE STATE = 'ACTIVE' 
  AND TIMER_WAIT > 60*1e9;  -- 超过60秒的事务

sys库的27个关键视图

设计理念：将Performance Schema数据转化为可读性更强的指标

必知视图清单

-- 内存使用TOP5线程（实时）
SELECT * FROM sys.memory_by_thread_by_current_bytes 
LIMIT 5;

-- 索引使用效率统计
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'order_db';

-- 文件IO延迟分析 
SELECT file, total_latency 
FROM sys.io_global_by_file_by_latency 
ORDER BY total_latency DESC 
LIMIT 10;

定制化监控脚本示例

-- 生成每小时负载报告（保存为hourly_report.sql）
SELECT 
  DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS monitor_time,
  (SELECT Variable_value 
   FROM sys.metrics 
   WHERE Variable_name = 'threads_running') AS threads_running,
  (SELECT Variable_value 
   FROM sys.metrics 
   WHERE Variable_name = 'innodb_row_lock_time_avg') AS row_lock_avg_ms\G

1.2 慢查询日志深度分析

动态开启配置（无需重启）

适用版本：MySQL 5.1+

在线调整参数

-- 开启慢查询日志（立即生效）
SET GLOBAL slow_query_log = 'ON'; 

-- 设置阈值（单位：秒）
SET GLOBAL long_query_time = 1;     -- 记录超过1秒的查询

-- 记录未走索引的查询（可选）
SET GLOBAL log_queries_not_using_indexes = ON;

-- 验证配置
SHOW VARIABLES LIKE 'slow_query_log%';

日志轮转技巧

# 每天零点切换日志（加入crontab）
mv /var/log/mysql/slow.log /var/log/mysql/slow_$(date +\%Y\%m\%d).log
mysqladmin flush-logs -uroot -p

pt-query-digest高阶用法

安装：perl -MCPAN -e 'install Digest::MD5' && wget
percona.com/get/pt-query-digest

分析报告生成

# 生成全量分析报告
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 按时间切片分析（最近24小时）
pt-query-digest --since '24h' /var/log/mysql/slow.log

# 专查UPDATE/DELETE语句
pt-query-digest --filter '$event->{arg} =~ /^(UPDATE|DELETE)/' slow.log

报告核心指标解读

# 概要部分
Rank      Query ID                 Response time             Calls       R/Call        Apdx
====    ========           =============    =====  ======   ====
1            0xABCDEF123        12.000s                         100        120ms        ★★

# 样本SQL
SELECT * FROM users WHERE phone=13800138000;

优化建议：

1. 确认phone字段是否有索引
2. 检查是否存在隐式类型转换（phone字段类型是否为字符串）

火焰图定位执行瓶颈

生成步骤（需perf工具）

# 采集数据（30秒）
perf record -F 99 -p $(pgrep -x mysqld) -g -- sleep 30

# 生成火焰图
perf script | stackcollapse-perf.pl | flamegraph.pl > mysql.svg

火焰图解读技巧

• 横向宽度：函数占用CPU时间比例
• 纵向深度：函数调用栈层级
• 常见热点：
• JOIN::optimize()：SQL复杂度高
• handler::ha_rnd_next：全表扫描
• mutex_spin_wait：锁竞争激烈

优化案例：

分析结论：70%时间消耗在filesort阶段，需优化ORDER BY索引

附录：本章工具包使用说明

1. 快速诊断脚本

wget https://example.com/mysql_diag.sh && chmod +x mysql_diag.sh
./mysql_diag.sh --check=slow_query

2. 自定义sys视图
   文件位置：/toolkit/sys_addon_views.sql

下一章预告：第二章将深入解析InnoDB内存管理机制与写入优化黑科技，揭秘Buffer Pool的"三级火箭"调优策略。