6.7.2 关键性能指标与瓶颈识别

本节聚焦MySQL性能指标体系与瓶颈识别方法，建立“指标—症状—根因—验证”的闭环，并提供可执行的观测、排错与练习示例。

一、原理草图：从指标到根因的识别链路

二、关键性能指标（KPI）体系与含义
- 吞吐指标：QPS、TPS、Com_select/insert/update/delete、Com_commit/rollback
用途：衡量业务负载强度与读写结构。
- 响应指标：P50/P95/P99、慢查询比例、平均查询时间
用途：衡量用户体验与尾延迟。
- 并发指标：Threads_connected、Threads_running、Max_used_connections
用途：评估连接压力与排队情况。
- 锁与事务指标：InnoDB_row_lock_time、Innodb_deadlocks、锁等待次数
用途：识别热点资源与长事务。
- 缓存与内存指标：Buffer Pool命中率、临时表落盘比例
用途：判断内存是否成为瓶颈。
- IO指标：磁盘IOPS、读写延迟、Innodb_log_waits
用途：判断IO瓶颈与日志写入压力。
- 复制指标：Seconds_Behind_Master、Relay_Log_Space
用途：识别复制延迟与回放堆积。

三、常用观测数据来源与安装准备
- MySQL内置：SHOW GLOBAL STATUS、SHOW ENGINE INNODB STATUS
- 性能视图：performance_schema、sys库
- 系统侧：vmstat、iostat、sar、top/htop
- 日志侧：慢查询日志、错误日志、审计日志

安装与启用示例（sys库与性能视图）

# 1) 确认performance_schema已启用（MySQL 5.7+默认开启）
mysql -uroot -p -e "SHOW VARIABLES LIKE 'performance_schema';"

# 2) 安装sys库（如果未安装）
mysql -uroot -p < /usr/share/mysql/sys.sql

# 3) 验证sys库
mysql -uroot -p -e "SHOW DATABASES LIKE 'sys';"

命令解释：
- performance_schema：用于采集SQL、锁、IO等细粒度指标
- sys库：提供友好的视图与诊断查询

四、关键命令与示例（含解释与预期效果）

1）系统层观测：CPU/内存/IO

# CPU与上下文切换
vmstat 1 5
# 解释：us/sy高表示CPU繁忙；cs高表示上下文切换频繁

# IO延迟与利用率
iostat -x 1 5
# 解释：await高表示IO延迟高；%util接近100说明磁盘繁忙

2）MySQL层状态变量

-- 连接与并发
SHOW GLOBAL STATUS LIKE 'Threads%';

-- 事务与锁
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

-- 缓冲池命中率计算
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

命令解释：
- Threads_running高且持续，表示并发压力或锁等待
- Innodb_buffer_pool_reads增多，表示缓存命中率下降

3）热点SQL与慢查询

-- sys库查看Top SQL（总耗时）
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 5;

-- 启用慢查询日志（示例配置）
-- /etc/my.cnf
[mysqld]
slow_query_log=ON
long_query_time=1
slow_query_log_file=/var/log/mysql/slow.log

预期效果：
- sys.statement_analysis能快速定位高成本语句
- 慢日志用于还原SQL特征与执行时间分布

4）锁等待定位

-- sys库查看锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 结合进程列表
SHOW FULL PROCESSLIST;

排错思路：
- 若锁等待集中在同一表或索引，优先排查长事务与热点更新

五、瓶颈识别示例（带验证步骤）

示例1：QPS增长但响应变慢（疑似IO瓶颈）

# 观察IO延迟
iostat -x 1 3

# 观察MySQL IO等待
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';"

判断逻辑：
- await高、%util接近100，且Innodb_log_waits增长，说明IO成为瓶颈
验证方法：
- 临时提升innodb_flush_log_at_trx_commit到2（仅用于验证）

SET GLOBAL innodb_flush_log_at_trx_commit=2;

预期效果：事务等待下降、P95延迟下降（注意持久性风险，仅测试）

示例2：Threads_running持续升高（疑似锁争用）

SHOW GLOBAL STATUS LIKE 'Threads_running';
SELECT * FROM sys.innodb_lock_waits;

验证方法：
- 对疑似热点表执行EXPLAIN与执行计划分析

EXPLAIN SELECT * FROM orders WHERE user_id=1001 FOR UPDATE;

预期效果：
- 若未命中索引或范围扫描大，锁持有时间延长

六、故障排查清单（可执行步骤）
1. 确认异常：与基线对比，确认波动幅度
2. 系统层排查：vmstat + iostat确认CPU/IO瓶颈
3. MySQL层排查：SHOW GLOBAL STATUS确认连接/锁/缓存
4. SQL层排查：sys.statement_analysis与慢日志定位Top SQL
5. 验证与复现：临时参数调整或隔离压测确认根因

七、实操练习
1. 采集并计算Buffer Pool命中率
- 公式：1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

找出总耗时最高的Top 3 SQL

SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 3;

模拟锁等待并定位

-- 会话1
BEGIN;
UPDATE t_order SET status='PAID' WHERE id=1;

-- 会话2
UPDATE t_order SET status='CANCEL' WHERE id=1;

预期效果：会话2阻塞，sys.innodb_lock_waits出现记录

八、实践建议
- 建立基线与阈值告警，避免事后排障
- 指标分层与SQL层结合分析，避免“头痛医头”
- 优先处理高影响、高频问题，再优化长尾问题