6.10.3 慢查询与性能瓶颈排查

慢查询与性能瓶颈排查遵循“发现—定位—验证—优化—回归”闭环。本节将用可执行示例贯穿慢日志开启、采集分析、执行计划定位、系统资源排障与优化回归。

关键监控指标与阈值建议（示例）：
- QPS/TPS、P95/P99响应时间
- Threads_running > 64 持续 5 分钟
- InnoDB Buffer Pool 命中率 < 99%
- 行扫描/返回比 > 1000
- IO等待 iowait > 20%

1）慢查询日志开启与采集（含配置、命令、说明）#

1.1 配置文件启用慢查询日志

路径示例：/etc/my.cnf

[mysqld]
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=0.5
log_queries_not_using_indexes=ON
log_throttle_queries_not_using_indexes=100

应用配置并验证：

# 重启或在线设置
systemctl restart mysqld

# 在线设置（临时生效）
mysql -uroot -p -e "SET GLOBAL slow_query_log=ON;"
mysql -uroot -p -e "SET GLOBAL long_query_time=0.5;"
mysql -uroot -p -e "SHOW VARIABLES LIKE 'slow_query_log%';"

预期输出示例：

+---------------------+------------------------+
| Variable_name       | Value                  |
+---------------------+------------------------+
| slow_query_log      | ON                     |
| slow_query_log_file | /var/log/mysql/slow.log|
+---------------------+------------------------+

1.2 performance_schema 与 sys 库采集

mysql -uroot -p -e "UPDATE performance_schema.setup_instruments
SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE 'statement/%';"
mysql -uroot -p -e "UPDATE performance_schema.setup_consumers
SET ENABLED='YES' WHERE NAME IN ('events_statements_history_long');"

2）慢日志聚合分析（安装 + 命令）#

2.1 安装 pt-query-digest

# CentOS/RHEL
yum install -y percona-toolkit

# Ubuntu/Debian
apt-get update && apt-get install -y percona-toolkit

2.2 分析慢日志

pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt
sed -n '1,60p' /tmp/slow_report.txt

关注字段：
- Query time
- Rows examined
- Lock time
- 频次（Count）

3）执行计划与SQL层定位（含示例）#

3.1 EXPLAIN 定位索引问题

EXPLAIN SELECT * FROM orders WHERE user_id=123 AND status='PAID' ORDER BY created_at DESC LIMIT 50;

关注：
- type 是否为 ALL（全表扫描）
- key 是否为空
- rows 是否过大
- Extra 是否出现 Using filesort / Using temporary

3.2 示例：索引优化对比

-- 未优化：缺少联合索引
EXPLAIN SELECT * FROM orders WHERE user_id=123 AND status='PAID' ORDER BY created_at DESC LIMIT 50;

-- 优化：添加联合索引
ALTER TABLE orders ADD INDEX idx_user_status_ct(user_id, status, created_at);

EXPLAIN SELECT * FROM orders WHERE user_id=123 AND status='PAID' ORDER BY created_at DESC LIMIT 50;

预期变化：key 指向新索引，rows 显著降低，Extra 中 Using filesort 消失或减少。

4）系统资源瓶颈排查（命令+解释）#

# CPU 与 IO
top -c
iostat -x 1 5

# InnoDB 状态
mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | head -n 80

# 连接与线程
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
mysql -uroot -p -e "SHOW PROCESSLIST;"

故障判断示例：
- iowait 高：磁盘瓶颈或刷盘策略
- Threads_running 高：并发过大或锁等待
- InnoDB Buffer Pool hit rate 下降：内存不足

5）锁等待与事务冲突排查（命令+解释）#

-- 查看阻塞源
SELECT * FROM information_schema.innodb_trx\G
SELECT * FROM information_schema.innodb_locks\G
SELECT * FROM information_schema.innodb_lock_waits\G

常见排错动作：
- 找出长事务并终止
- 优化热点行更新，减少持锁时间
- 避免大事务批量更新

6）参数调优示例（小步验证）#

-- 查询当前参数
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- 在线调整（小步）
SET GLOBAL innodb_buffer_pool_size=8*1024*1024*1024;
SET GLOBAL innodb_flush_log_at_trx_commit=2;

验证：观察 P95 响应时间、QPS、iowait 变化，避免一次性多项修改。

7）回归验证与基线#

# 压测示例（sysbench）
sysbench oltp_read_write \
  --mysql-host=127.0.0.1 \
  --mysql-user=root \
  --mysql-password=pass \
  --tables=10 --table-size=100000 \
  --threads=16 --time=60 run

# 收集指标对比
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Questions';"

8）常见排错清单（简表）#

慢日志无记录：确认 slow_query_log=ON，文件权限正确
EXPLAIN 未走索引：检查隐式类型转换、函数使用、联合索引顺序
iowait 高：确认磁盘队列深度、刷盘参数、binlog/redo 压力
Threads_running 高：排查长事务与锁等待

9）练习题（含目标）#

开启慢日志，抓取 5 分钟高峰期慢 SQL，并用 pt-query-digest 生成报告。
给出一条 Using filesort 的 SQL，添加索引后对比 EXPLAIN 变化。
模拟锁等待：开启两个会话更新同一行，观察 innodb_lock_waits 结果并终止阻塞事务。
调整 innodb_flush_log_at_trx_commit 并用 sysbench 对比 TPS 变化，记录结论。