MySQL教程 / 第 270 节

第27章：性能诊断工具

工欲善其事，必先利其器

27.1 诊断工具概述

27.1.1 为什么需要诊断工具？

问题场景：

❌ 查询突然变慢
❌ CPU使用率100%
❌ 锁等待严重
❌ 不知道问题在哪

诊断工具的作用：

✅ 快速定位问题
✅ 分析性能瓶颈
✅ 优化SQL
✅ 诊断锁问题

27.2 MySQL内置诊断工具 ⭐⭐⭐⭐⭐

27.2.1 SHOW PROCESSLIST

查看当前正在执行的查询：

-- 查看进程列表
SHOW PROCESSLIST;

-- 查看完整SQL
SHOW FULL PROCESSLIST;

-- 输出字段：
-- Id：线程ID
-- User：用户
-- Host：客户端地址
-- db：数据库
-- Command：命令类型（Query、Sleep等）
-- Time：执行时间（秒）
-- State：状态
-- Info：SQL语句

-- 示例输出：
-- Id  User  Host           db    Command  Time  State           Info
-- 1   root  localhost:3306 test  Query    10    Sending data    SELECT * FROM users WHERE age > 18
-- 2   root  localhost:3307 test  Sleep    100   NULL            NULL

找出慢查询：

-- 查找执行时间>10秒的查询
SELECT * FROM information_schema.PROCESSLIST 
WHERE TIME > 10 AND COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- 杀死慢查询
KILL 线程ID;

27.2.2 EXPLAIN分析查询 ⭐⭐⭐⭐⭐

基本用法：

EXPLAIN SELECT * FROM users WHERE age > 18;

-- 输出字段：
-- id：查询序号
-- select_type：查询类型（SIMPLE、PRIMARY、SUBQUERY等）
-- table：表名
-- type：访问类型（ALL、index、range、ref、eq_ref、const、system）
-- possible_keys：可能使用的索引
-- key：实际使用的索引
-- key_len：索引长度
-- ref：索引引用
-- rows：扫描行数
-- Extra：额外信息

type字段（重要）：

性能从好到差：
system > const > eq_ref > ref > range > index > ALL

- system：表只有一行
- const：主键或唯一索引查询
- eq_ref：唯一索引扫描
- ref：非唯一索引扫描
- range：范围扫描（BETWEEN、>、<）
- index：全索引扫描
- ALL：全表扫描（最差）⚠️

示例分析：

-- ❌ 全表扫描
EXPLAIN SELECT * FROM users WHERE age > 18;
-- type: ALL
-- rows: 1000000
-- 问题：没有索引

-- ✅ 索引扫描
CREATE INDEX idx_age ON users(age);
EXPLAIN SELECT * FROM users WHERE age > 18;
-- type: range
-- key: idx_age
-- rows: 100000

EXPLAIN ANALYZE（MySQL 8.0+）：

-- 实际执行并分析
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 18;

-- 输出实际执行时间和行数

27.2.3 SHOW PROFILE

开启profiling：

-- 开启
SET profiling = 1;

-- 执行查询
SELECT * FROM users WHERE age > 18;

-- 查看profiles
SHOW PROFILES;

-- 输出：
-- Query_ID  Duration  Query
-- 1         0.5       SELECT * FROM users WHERE age > 18

-- 查看详细信息
SHOW PROFILE FOR QUERY 1;

-- 查看CPU、I/O信息
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

27.2.4 Performance Schema ⭐⭐⭐⭐⭐

开启Performance Schema：

-- 查看是否开启
SHOW VARIABLES LIKE 'performance_schema';

-- 开启（需要重启）
[mysqld]
performance_schema = ON

查看慢查询：

-- 查看执行时间最长的SQL
SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    AVG_TIMER_WAIT / 1000000000000 AS avg_time_sec,
    SUM_TIMER_WAIT / 1000000000000 AS total_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

查看表I/O：

-- 查看表I/O统计
SELECT 
    OBJECT_SCHEMA AS db,
    OBJECT_NAME AS table_name,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY COUNT_READ + COUNT_WRITE DESC
LIMIT 10;

查看锁等待：

-- 查看当前锁等待
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

27.3 Percona Toolkit ⭐⭐⭐⭐⭐

27.3.1 安装Percona Toolkit

# CentOS
yum install percona-toolkit

# Ubuntu
apt install percona-toolkit

# 验证
pt-query-digest --version

27.3.2 pt-query-digest（慢查询分析）

分析慢查询日志：

# 分析慢查询日志
pt-query-digest /var/lib/mysql/slow.log

# 输出到文件
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt

# 只分析指定时间范围
pt-query-digest --since '2024-11-10 00:00:00' \
  --until '2024-11-10 23:59:59' \
  /var/lib/mysql/slow.log

# 只显示前10条
pt-query-digest --limit 10 /var/lib/mysql/slow.log

# 分析binlog
pt-query-digest --type binlog /var/lib/mysql/mysql-bin.000001

输出解读：

# Query 1: 100 QPS, 0.5s avg, ID 0x1234...
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         10    1000
# Exec time     50     500s    0.1s    2.0s    0.5s    1.0s    0.2s    0.4s
# Lock time      5      5s     0ms    50ms     5ms    10ms     5ms     3ms
# Rows sent      8   10000      10     100      10      20       5      10
# Rows examine  90  900000     900    9000     900    1800     300     800

SELECT * FROM users WHERE age > 18\G

27.3.3 pt-online-schema-change（在线DDL）

# 在线修改表结构（不锁表）
pt-online-schema-change \
  --alter "ADD COLUMN email VARCHAR(100)" \
  --execute \
  D=mydb,t=users

# 原理：
# 1. 创建新表
# 2. 复制数据
# 3. 创建触发器同步增量数据
# 4. 重命名表

27.3.4 pt-table-checksum（数据一致性检查）

# 检查主从数据一致性
pt-table-checksum \
  --host=master_host \
  --user=root \
  --password=password \
  --databases=mydb

# 输出：
# TS            ERRORS  DIFFS  ROWS  CHUNKS  SKIPPED  TIME  TABLE
# 11-10T10:00:00      0      0  1000       1        0   0.5  mydb.users
# 11-10T10:00:01      0      5  2000       1        0   1.0  mydb.orders

27.3.5 pt-table-sync（数据同步）

# 同步主从数据
pt-table-sync \
  --execute \
  --sync-to-master \
  h=slave_host,D=mydb,t=orders

27.3.6 pt-duplicate-key-checker（重复索引检查）

# 检查重复索引
pt-duplicate-key-checker \
  --host=localhost \
  --user=root \
  --password=password

# 输出：
# # ########################################################################
# # mydb.users
# # ########################################################################
# 
# # Key idx_name is a duplicate of idx_name_age
# # Key definitions:
# #   KEY `idx_name` (`name`)
# #   KEY `idx_name_age` (`name`,`age`)

27.4 其他诊断工具

27.4.1 mysqlslap（压力测试）

# 自动生成测试数据
mysqlslap \
  --auto-generate-sql \
  --auto-generate-sql-load-type=mixed \
  --number-of-queries=1000 \
  --concurrency=50 \
  --user=root \
  --password=password

# 输出：
# Benchmark
#   Average number of seconds to run all queries: 10.5 seconds
#   Minimum number of seconds to run all queries: 10.2 seconds
#   Maximum number of seconds to run all queries: 10.8 seconds
#   Number of clients running queries: 50
#   Average number of queries per client: 20

27.4.2 sysbench（性能测试）

# OLTP读写测试
sysbench oltp_read_write \
  --mysql-host=localhost \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  --threads=16 \
  --time=60 \
  run

27.4.3 MySQL Workbench

功能：

✅ 可视化EXPLAIN
✅ 性能报告
✅ 查询分析
✅ 服务器状态

27.5 诊断流程

27.5.1 性能问题诊断流程

1. 确认问题
   - 查询慢？
   - CPU高？
   - 锁等待？

2. 查看当前状态
   - SHOW PROCESSLIST
   - SHOW ENGINE INNODB STATUS

3. 分析慢查询
   - pt-query-digest分析慢查询日志
   - 找出最慢的SQL

4. 分析SQL
   - EXPLAIN分析执行计划
   - 检查索引使用情况

5. 优化
   - 添加索引
   - 优化SQL
   - 调整参数

6. 验证
   - 再次执行EXPLAIN
   - 对比优化前后性能

27.6 本章总结

本章学习内容：

✅ 诊断工具概述
✅ MySQL内置工具（PROCESSLIST、EXPLAIN、PROFILE、Performance Schema）⭐⭐⭐⭐⭐
✅ Percona Toolkit（pt-query-digest等）⭐⭐⭐⭐⭐
✅ 其他诊断工具
✅ 诊断流程

重点掌握：

SHOW PROCESSLIST查看当前查询
EXPLAIN分析执行计划
pt-query-digest分析慢查询
Performance Schema监控
type字段：system > const > eq_ref > ref > range > index > ALL

常用工具：

SHOW PROCESSLIST：查看当前查询
EXPLAIN：分析执行计划
pt-query-digest：分析慢查询
Performance Schema：性能监控

EXPLAIN type字段：

ALL：全表扫描（最差）
index：全索引扫描
range：范围扫描
ref：非唯一索引
eq_ref：唯一索引
const：主键或唯一索引
system：单行表（最好）

面试重点：

如何诊断慢查询
EXPLAIN各字段含义
type字段有哪些值
如何使用pt-query-digest
Performance Schema的作用

下一章预告： 故障排查实战

练习题

如何查看当前正在执行的查询？
EXPLAIN的type字段有哪些值？性能从好到差排序
如何使用pt-query-digest分析慢查询？
Performance Schema有什么作用？
如何查看锁等待？
如何进行在线DDL？
如何检查主从数据一致性？
如何检查重复索引？
诊断性能问题的流程是什么？
实战：分析一个慢查询并优化

继续学习： 第28章：故障排查实战