Performance Schema 做什么用的?

Performance Schema 是 MySQL 5.5 引入的一个性能监控和诊断工具，用于在运行时收集数据库服务器的性能数据。它就像是 MySQL 的”体检中心”，可以帮助我们:

  • 监控 SQL 执行性能
  • 分析慢查询原因
  • 诊断锁等待问题
  • 追踪内存使用情况
  • 分析 I/O 性能瓶颈

Performance Schema vs 其他监控工具

特性 Performance Schema SHOW STATUS Slow Query Log sys Schema
粒度 细粒度(语句级、事件级) 粗粒度(全局统计) 慢查询级别 友好视图层
实时性 实时 实时 异步写入日志 实时(基于P_S)
性能开销 可控(可选择性开启) 很小 中等 很小
易用性 需要学习 简单 简单 简单
适用场景 深度性能分析 快速查看状态 发现慢查询 日常监控 
1
2
3
4
5
6
7
8
9
10
11
12
关系图:
            ┌───────────────────────┐
            │  Performance Schema   │ ← 底层数据源
            │   (原始监控数据)        │
            └───────────────────────┘
                      ↑
                      │ 基于 P_S 表创建友好视图
                      │
            ┌───────────────────────┐
            │     sys Schema        │ ← 更易用的查询接口
            │   (友好视图和函数)      │
            └───────────────────────┘

简单理解:

  • Performance Schema: 原始数据仓库(功能强大但不易用)
  • sys Schema: 便利店(基于 Performance Schema 提供更友好的查询接口)

Performance Schema 的核心概念

1. Instruments (监控点)

Instruments 是 Performance Schema 的数据收集点，类似于”探针”或”传感器”。

1
2
3
4
5
6
7
Instruments 分类:
- statement/*      : SQL 语句执行
- wait/*          : 各种等待事件
- stage/*         : SQL 执行阶段
- transaction/*   : 事务相关
- memory/*        : 内存分配
- idle            : 空闲等待
1
2
3
4
5
6
7
8
9
10
11
-- 查看所有可用的 instruments
SELECT * FROM performance_schema.setup_instruments;

-- 查看开启的 instruments
SELECT * FROM performance_schema.setup_instruments
WHERE ENABLED = 'YES';

-- 开启特定的 instrument
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

2. Consumers (消费者)

Consumers 决定收集到的数据存储到哪些表中。

1
2
3
4
5
6
7
-- 查看消费者配置
SELECT * FROM performance_schema.setup_consumers;

-- 开启语句历史记录
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_statements_history';

常见 Consumers:

Consumer 作用 推荐开启
events_statements_current 当前正在执行的语句 是
events_statements_history 每个线程最近的语句(默认10条) 是
events_statements_history_long 全局最近的语句(默认10000条) 视需求
events_waits_current 当前等待事件 视需求
statements_digest 语句摘要统计 是

3. 表的分类

Performance Schema 包含超过 110+ 张表(MySQL 8.0)，按功能分为以下几类:

1
2
3
4
5
6
7
8
1. 设置表(setup_*)        : 配置监控行为
2. 当前事件表(events_*_current): 正在发生的事件
3. 历史事件表(events_*_history): 历史事件记录
4. 汇总表(*_summary_*)    : 统计聚合数据
5. 实例表(*_instances)    : 对象实例信息
6. 连接表(accounts, hosts, users): 连接统计
7. 复制表(replication_*)  : 主从复制监控
8. 锁表(*_locks, *_lock_waits): 锁相关

核心表详解

一、语句执行分析表

1. events_statements_current

作用: 记录每个线程当前正在执行的 SQL 语句。

使用场景: 实时查看正在执行的慢查询。

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看当前正在执行的 SQL 语句
SELECT
    thread_id,
    event_id,
    TRUNCATE(timer_wait/1000000000000, 6) AS exec_time_s,  -- 执行时间(秒)
    sql_text,
    current_schema,
    rows_examined,
    rows_sent
FROM performance_schema.events_statements_current
WHERE sql_text IS NOT NULL
ORDER BY timer_wait DESC;

重要字段说明:

字段 说明
thread_id 线程 ID
sql_text SQL 语句文本
timer_wait 执行耗时(皮秒，1秒=10^12皮秒)
lock_time 锁等待时间
rows_examined 扫描行数
rows_sent 返回行数
rows_affected 影响行数
created_tmp_tables 创建临时表数量
created_tmp_disk_tables 创建磁盘临时表数量

2. events_statements_history

作用: 记录每个线程最近执行的 N 条 SQL 语句(默认 10 条)。

使用场景: 追溯某个线程最近的执行历史。

1
2
3
4
5
6
7
8
9
10
11
-- 查看某个线程的最近执行历史
SELECT
    event_id,
    TRUNCATE(timer_wait/1000000000000, 6) AS exec_time_s,
    sql_text,
    rows_examined,
    rows_sent
FROM performance_schema.events_statements_history
WHERE thread_id = 123  -- 替换为实际的 thread_id
ORDER BY event_id DESC
LIMIT 10;

3. events_statements_history_long

作用: 记录全局最近执行的 N 条 SQL 语句(默认 10000 条)。

使用场景: 分析最近一段时间的 SQL 执行情况。

1
2
3
4
5
6
7
8
9
10
-- 查找最近 10 条最慢的查询
SELECT
    TRUNCATE(timer_wait/1000000000000, 6) AS exec_time_s,
    sql_text,
    current_schema,
    rows_examined,
    rows_sent
FROM performance_schema.events_statements_history_long
ORDER BY timer_wait DESC
LIMIT 10;

配置历史记录数量:

1
2
3
4
5
6
-- 查看配置
SHOW VARIABLES LIKE 'performance_schema_events_statements_history%';

-- 在 my.cnf 中修改
performance_schema_events_statements_history_size = 20        # 每线程保留20条
performance_schema_events_statements_history_long_size = 20000 # 全局保留20000条

4. events_statements_summary_by_digest (重要!)

作用: 按照 SQL 指纹(digest) 聚合统计，将参数值不同但结构相同的 SQL 归为一类。

使用场景: 找出系统中执行最频繁、最慢的 SQL 模式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 找出执行次数最多的 SQL
SELECT
    schema_name,
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    TRUNCATE(AVG_TIMER_WAIT/1000000000000, 6) AS avg_time_s,
    TRUNCATE(MAX_TIMER_WAIT/1000000000000, 6) AS max_time_s,
    SUM_ROWS_EXAMINED AS total_rows_examined,
    SUM_ROWS_SENT AS total_rows_sent,
    SUM_CREATED_TMP_TABLES AS tmp_tables,
    SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL
ORDER BY COUNT_STAR DESC
LIMIT 10;

-- 找出平均执行时间最长的 SQL
SELECT
    schema_name,
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    TRUNCATE(AVG_TIMER_WAIT/1000000000000, 6) AS avg_time_s,
    TRUNCATE(SUM_TIMER_WAIT/1000000000000, 6) AS total_time_s
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- 找出全表扫描最多的 SQL
SELECT
    schema_name,
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    SUM_NO_INDEX_USED AS full_scan_count,
    TRUNCATE(AVG_TIMER_WAIT/1000000000000, 6) AS avg_time_s
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 10;

重要字段说明:

字段 说明
DIGEST SQL 指纹的 Hash 值
DIGEST_TEXT SQL 指纹文本(参数被替换为 ?)
COUNT_STAR 执行次数
SUM_TIMER_WAIT 总执行时间
AVG_TIMER_WAIT 平均执行时间
MAX_TIMER_WAIT 最大执行时间
SUM_ROWS_EXAMINED 总扫描行数
SUM_NO_INDEX_USED 未使用索引的次数
SUM_NO_GOOD_INDEX_USED 未使用好索引的次数

清空统计数据:

1
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

二、等待事件分析表

1. events_waits_current

作用: 记录当前正在等待的事件。

使用场景: 诊断当前系统的等待瓶颈。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查看当前的等待事件
SELECT
    thread_id,
    event_name,
    source,
    TRUNCATE(timer_wait/1000000000000, 6) AS wait_time_s,
    object_schema,
    object_name,
    index_name,
    operation
FROM performance_schema.events_waits_current
WHERE event_name != 'idle'
ORDER BY timer_wait DESC;

常见的等待事件:

Event Name 说明
wait/io/file/* 文件 I/O 等待
wait/io/table/* 表 I/O 等待
wait/lock/table/* 表锁等待
wait/lock/metadata/* 元数据锁等待
wait/synch/mutex/* 互斥锁等待
wait/synch/rwlock/* 读写锁等待

2. events_waits_summary_by_instance

作用: 按照对象实例聚合等待事件。

使用场景: 找出哪些表、文件的 I/O 等待最多。

1
2
3
4
5
6
7
8
9
10
11
-- 查看哪些表的等待时间最长
SELECT
    object_schema,
    object_name,
    COUNT_STAR AS wait_count,
    TRUNCATE(SUM_TIMER_WAIT/1000000000000, 6) AS total_wait_s,
    TRUNCATE(AVG_TIMER_WAIT/1000000000000, 6) AS avg_wait_s
FROM performance_schema.events_waits_summary_by_instance
WHERE object_schema IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

三、表 I/O 分析表

1. table_io_waits_summary_by_table

作用: 统计每个表的 I/O 等待情况(读、写、fetch、insert、update、delete)。

使用场景: 找出 I/O 热点表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 查看 I/O 最繁忙的表
SELECT
    object_schema,
    object_name,
    COUNT_STAR AS total_io,
    COUNT_READ AS read_count,
    COUNT_WRITE AS write_count,
    COUNT_FETCH AS fetch_count,
    COUNT_INSERT AS insert_count,
    COUNT_UPDATE AS update_count,
    COUNT_DELETE AS delete_count,
    TRUNCATE(SUM_TIMER_WAIT/1000000000000, 6) AS total_wait_s
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看读写比例
SELECT
    object_schema,
    object_name,
    COUNT_READ AS read_count,
    COUNT_WRITE AS write_count,
    ROUND(COUNT_READ / NULLIF(COUNT_WRITE, 0), 2) AS read_write_ratio
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema')
  AND COUNT_STAR > 0
ORDER BY COUNT_STAR DESC
LIMIT 10;

实战案例: 找出热点表并优化

1
2
3
4
5
6
7
8
9
10
11
-- 1. 找出访问最频繁的表
SELECT object_schema, object_name, COUNT_STAR
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'mydb'
ORDER BY COUNT_STAR DESC LIMIT 5;

-- 2. 查看该表的索引使用情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb' AND object_name = 'orders';

-- 3. 分析是否需要添加缓存或优化索引

四、索引使用分析表

1. table_io_waits_summary_by_index_usage

作用: 统计每个索引的使用情况。

使用场景: 找出未使用的索引、使用最频繁的索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 查看索引使用统计
SELECT
    object_schema,
    object_name,
    index_name,
    COUNT_STAR AS access_count,
    COUNT_READ AS read_count,
    COUNT_WRITE AS write_count,
    COUNT_FETCH AS fetch_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema NOT IN ('mysql', 'performance_schema')
  AND index_name IS NOT NULL
ORDER BY COUNT_STAR DESC
LIMIT 20;

-- 找出从未使用过的索引
SELECT
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys')
  AND index_name IS NOT NULL
  AND index_name != 'PRIMARY'
  AND COUNT_STAR = 0
ORDER BY object_schema, object_name;

实战案例: 删除无用索引

1
2
3
4
5
6
7
8
9
10
11
12
-- 1. 找出未使用的索引
SELECT
    CONCAT('ALTER TABLE ', object_schema, '.', object_name,
           ' DROP INDEX ', index_name, ';') AS drop_index_sql
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
  AND index_name IS NOT NULL
  AND index_name != 'PRIMARY'
  AND COUNT_STAR = 0;

-- 2. 在测试环境验证后执行删除
-- ALTER TABLE mydb.orders DROP INDEX idx_unused;

五、锁分析表

1. data_locks (MySQL 8.0+)

作用: 显示当前持有的数据锁(行锁、表锁)。

使用场景: 诊断死锁、锁等待问题。

1
2
3
4
5
6
7
8
9
10
11
-- 查看当前的锁信息
SELECT
    thread_id,
    object_schema,
    object_name,
    lock_type,
    lock_mode,
    lock_status,
    lock_data
FROM performance_schema.data_locks
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys');

字段说明:

字段 说明
lock_type 锁类型: TABLE(表锁), RECORD(行锁)
lock_mode 锁模式: S(共享锁), X(排他锁), IX(意向排他锁)
lock_status GRANTED(已授予), WAITING(等待中)
lock_data 锁定的数据(主键值)

2. data_lock_waits (MySQL 8.0+)

作用: 显示锁等待关系。

使用场景: 找出哪个会话阻塞了哪个会话。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查看锁等待关系
SELECT
    waiting.thread_id AS waiting_thread,
    waiting.object_schema,
    waiting.object_name,
    waiting.lock_mode AS waiting_lock_mode,
    blocking.thread_id AS blocking_thread,
    blocking.lock_mode AS blocking_lock_mode,
    blocking.lock_data
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks waiting
    ON w.requesting_engine_lock_id = waiting.engine_lock_id
JOIN performance_schema.data_locks blocking
    ON w.blocking_engine_lock_id = blocking.engine_lock_id;

-- 找出阻塞源头(最上游的事务)
SELECT
    blocking_thread_id,
    COUNT(*) AS blocked_count
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks blocking
    ON w.blocking_engine_lock_id = blocking.engine_lock_id
GROUP BY blocking_thread_id
ORDER BY blocked_count DESC;

实战案例: 解决锁等待

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 1. 找出阻塞的线程
SELECT blocking_thread_id FROM performance_schema.data_lock_waits LIMIT 1;

-- 2. 找到对应的进程 ID
SELECT processlist_id FROM performance_schema.threads
WHERE thread_id = <blocking_thread_id>;

-- 3. 查看该进程正在执行的 SQL
SELECT * FROM performance_schema.events_statements_current
WHERE thread_id = <blocking_thread_id>;

-- 4. 如果是长时间未提交的事务，可以 KILL 掉
KILL <processlist_id>;

3. metadata_locks (MySQL 5.7+)

作用: 显示元数据锁(MDL)。

使用场景: 诊断 DDL 操作被阻塞的问题。

1
2
3
4
5
6
7
8
9
10
-- 查看元数据锁
SELECT
    object_type,
    object_schema,
    object_name,
    lock_type,
    lock_status,
    owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_schema NOT IN ('performance_schema');

实战案例: DDL 被阻塞

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 场景: ALTER TABLE 一直卡住不动

-- 1. 查看哪些会话持有该表的元数据锁
SELECT * FROM performance_schema.metadata_locks
WHERE object_name = 'orders';

-- 2. 找出对应的 SQL
SELECT t.thread_id, t.processlist_id, s.sql_text
FROM performance_schema.threads t
JOIN performance_schema.events_statements_current s ON t.thread_id = s.thread_id
WHERE t.thread_id IN (
    SELECT owner_thread_id FROM performance_schema.metadata_locks
    WHERE object_name = 'orders'
);

-- 3. KILL 掉长时间持有锁的会话
KILL <processlist_id>;

六、文件 I/O 分析表

1. file_summary_by_instance

作用: 统计每个文件的 I/O 操作。

使用场景: 找出 I/O 瓶颈文件。

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看 I/O 最频繁的文件
SELECT
    file_name,
    event_name,
    COUNT_READ AS read_count,
    COUNT_WRITE AS write_count,
    ROUND(SUM_NUMBER_OF_BYTES_READ/1024/1024, 2) AS read_mb,
    ROUND(SUM_NUMBER_OF_BYTES_WRITE/1024/1024, 2) AS write_mb,
    TRUNCATE(SUM_TIMER_WAIT/1000000000000, 6) AS total_wait_s
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

七、内存使用分析表

1. memory_summary_by_thread_by_event_name

作用: 统计每个线程的内存使用情况。

使用场景: 找出内存消耗最多的会话。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看每个线程的内存使用
SELECT
    t.processlist_id,
    t.processlist_user,
    t.processlist_host,
    m.event_name,
    ROUND(m.current_allocated/1024/1024, 2) AS current_mb,
    ROUND(m.high_allocated/1024/1024, 2) AS high_mb
FROM performance_schema.memory_summary_by_thread_by_event_name m
JOIN performance_schema.threads t ON m.thread_id = t.thread_id
WHERE m.current_allocated > 0
  AND t.processlist_id IS NOT NULL
ORDER BY m.current_allocated DESC
LIMIT 10;

2. memory_summary_global_by_event_name

作用: 全局内存使用统计。

使用场景: 查看哪些内存分配事件占用最多。

1
2
3
4
5
6
7
8
9
SELECT
    event_name,
    current_count,
    ROUND(current_allocated/1024/1024, 2) AS current_mb,
    ROUND(high_allocated/1024/1024, 2) AS high_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE current_allocated > 0
ORDER BY current_allocated DESC
LIMIT 10;

八、连接统计表

1. threads

作用: 记录所有线程信息(前台线程和后台线程)。

使用场景: 查看当前连接、找到某个 SQL 对应的线程。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 查看当前所有前台连接
SELECT
    thread_id,
    processlist_id,
    processlist_user,
    processlist_host,
    processlist_db,
    processlist_command,
    processlist_state,
    TRUNCATE(processlist_time, 0) AS time_s
FROM performance_schema.threads
WHERE type = 'FOREGROUND'
ORDER BY processlist_time DESC;

-- 找出执行时间最长的连接
SELECT
    processlist_id,
    processlist_user,
    processlist_db,
    processlist_time,
    processlist_info
FROM performance_schema.threads
WHERE type = 'FOREGROUND'
  AND processlist_command != 'Sleep'
ORDER BY processlist_time DESC
LIMIT 10;

2. accounts / hosts / users

作用: 按账号、主机、用户维度统计连接数和执行情况。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查看每个用户的连接数
SELECT
    user,
    host,
    current_connections,
    total_connections
FROM performance_schema.accounts
ORDER BY current_connections DESC;

-- 查看每个主机的连接数
SELECT
    host,
    current_connections,
    total_connections
FROM performance_schema.hosts
ORDER BY current_connections DESC;

九、复制监控表 (MySQL 8.0+)

1. replication_connection_status

作用: 主从复制连接状态。

1
2
3
4
5
6
7
SELECT
    channel_name,
    source_uuid,
    service_state,
    last_error_message,
    last_error_timestamp
FROM performance_schema.replication_connection_status;

2. replication_applier_status_by_worker

作用: 复制工作线程状态。

1
2
3
4
5
6
SELECT
    channel_name,
    worker_id,
    service_state,
    last_error_message
FROM performance_schema.replication_applier_status_by_worker;

Performance Schema 配置与优化

1. 启用 Performance Schema

1
2
3
4
5
6
-- 查看是否启用
SHOW VARIABLES LIKE 'performance_schema';

-- 在 my.cnf 中启用(需要重启)
[mysqld]
performance_schema = ON

2. 配置监控粒度

1
2
3
4
5
6
7
8
9
10
11
-- 只监控慢查询(减少性能开销)
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO';

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME LIKE 'statement/%';

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME LIKE 'wait/io/%';

3. 调整历史记录大小

在 my.cnf 中配置:

1
2
3
4
5
6
7
8
9
10
11
[mysqld]
# 语句历史记录
performance_schema_events_statements_history_size = 20
performance_schema_events_statements_history_long_size = 10000

# 等待事件历史记录
performance_schema_events_waits_history_size = 10
performance_schema_events_waits_history_long_size = 10000

# 最大监控连接数
performance_schema_max_thread_instances = 500

4. 性能开销评估

Performance Schema 的性能开销取决于开启的 instruments 数量:

监控级别 性能开销 适用场景
关闭所有 instruments 0% 生产环境不需要监控
只开启 statement 级别 < 5% 日常监控
开启 statement + wait 5-10% 性能调优
全部开启 10-20% 深度排查问题

推荐配置(生产环境):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 开启语句级监控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

-- 开启表 I/O 监控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/io/table/%';

-- 开启文件 I/O 监控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/io/file/%';

-- 开启锁监控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/lock/%';

-- 开启消费者
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN (
    'events_statements_current',
    'events_statements_history',
    'events_statements_history_long',
    'statements_digest'
);

实战场景: 性能诊断案例

场景 1: 找出系统中最慢的 SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 方法 1: 通过 events_statements_summary_by_digest
SELECT
    schema_name,
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    TRUNCATE(AVG_TIMER_WAIT/1000000000000, 6) AS avg_time_s,
    TRUNCATE(MAX_TIMER_WAIT/1000000000000, 6) AS max_time_s
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- 方法 2: 通过 sys.statement_analysis (更友好)
SELECT * FROM sys.statement_analysis LIMIT 10;

场景 2: 诊断锁等待问题

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查看当前的锁等待
SELECT
    w.requesting_thread_id AS waiting_thread,
    w.blocking_thread_id AS blocking_thread,
    waiting.sql_text AS waiting_sql,
    blocking.sql_text AS blocking_sql
FROM performance_schema.data_lock_waits w
JOIN performance_schema.threads t_waiting ON w.requesting_thread_id = t_waiting.thread_id
JOIN performance_schema.threads t_blocking ON w.blocking_thread_id = t_blocking.thread_id
LEFT JOIN performance_schema.events_statements_current waiting
    ON t_waiting.thread_id = waiting.thread_id
LEFT JOIN performance_schema.events_statements_current blocking
    ON t_blocking.thread_id = blocking.thread_id;

场景 3: 分析表的访问模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 查看表的读写比例
SELECT
    object_schema,
    object_name,
    COUNT_READ AS read_ops,
    COUNT_WRITE AS write_ops,
    CASE
        WHEN COUNT_WRITE > 0 THEN ROUND(COUNT_READ / COUNT_WRITE, 2)
        ELSE COUNT_READ
    END AS read_write_ratio,
    ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_wait_s
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY COUNT_STAR DESC
LIMIT 10;

-- 判断是否需要加缓存
-- read_write_ratio > 10: 考虑添加 Redis 缓存
-- total_wait_s 很高: 考虑优化索引或分表

场景 4: 找出未使用的索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 找出从未使用过的索引
SELECT
    CONCAT(object_schema, '.', object_name) AS table_name,
    index_name,
    COUNT_STAR AS access_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys')
  AND index_name IS NOT NULL
  AND index_name != 'PRIMARY'
  AND COUNT_STAR = 0
ORDER BY object_schema, object_name;

-- 生成删除索引的 SQL
SELECT
    CONCAT('ALTER TABLE ', object_schema, '.', object_name,
           ' DROP INDEX ', index_name, ';') AS drop_sql
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
  AND index_name IS NOT NULL
  AND index_name != 'PRIMARY'
  AND COUNT_STAR = 0;

场景 5: 分析内存使用

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查看哪些查询占用内存最多
SELECT
    t.processlist_id,
    t.processlist_user,
    s.sql_text,
    ROUND(SUM(m.current_allocated)/1024/1024, 2) AS memory_mb
FROM performance_schema.memory_summary_by_thread_by_event_name m
JOIN performance_schema.threads t ON m.thread_id = t.thread_id
LEFT JOIN performance_schema.events_statements_current s ON t.thread_id = s.thread_id
WHERE t.processlist_id IS NOT NULL
GROUP BY t.thread_id
ORDER BY SUM(m.current_allocated) DESC
LIMIT 10;

Performance Schema vs sys Schema

sys Schema 是 MySQL 5.7 引入的一组视图和函数，基于 Performance Schema 提供更友好的查询接口。

常用 sys Schema 视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 1. 语句分析(等价于复杂的 Performance Schema 查询)
SELECT * FROM sys.statement_analysis LIMIT 10;

-- 2. 慢查询
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_sorting LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;

-- 3. 未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 4. 冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 5. 表访问统计
SELECT * FROM sys.schema_table_statistics WHERE table_schema = 'mydb';

-- 6. 文件 I/O
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;

-- 7. 锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 8. 用户资源使用
SELECT * FROM sys.user_summary;

推荐做法:

  • 日常监控: 使用 sys Schema(简单易用)
  • 深度分析: 直接查询 Performance Schema(更灵活)

常见问题与注意事项

1. Performance Schema 的数据会持久化吗?

不会。Performance Schema 的数据都存储在内存中，MySQL 重启后数据会清空。

2. 如何清空 Performance Schema 的历史数据?

1
2
3
4
5
6
7
8
-- 清空语句摘要统计
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

-- 清空表 I/O 统计
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;

-- 清空文件 I/O 统计
TRUNCATE TABLE performance_schema.file_summary_by_instance;

3. Performance Schema 的性能开销有多大?

根据 MySQL 官方测试:

  • 默认配置: 约 2-5% 的性能开销
  • 全部开启: 约 10-20% 的性能开销

建议: 在生产环境只开启必要的 instruments。

4. 为什么某些表查询结果为空?

可能原因:

  1. 对应的 instruments 未开启
  2. 对应的 consumers 未开启
  3. 历史记录已被覆盖(缓冲区太小)
1
2
3
-- 检查配置
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%statement%';
SELECT * FROM performance_schema.setup_consumers;

5. Performance Schema 与 information_schema 的区别

特性 Performance Schema information_schema
用途 性能监控和诊断 元数据查询
数据来源 运行时收集 数据字典
实时性 实时 实时
性能开销 可控(可配置) 很小
典型表 events_statements_* TABLES, COLUMNS, INDEXES

监控脚本示例

1. 定期监控慢查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#!/bin/bash
# monitor_slow_queries.sh

mysql -u root -p'password' -e "
SELECT
    NOW() AS check_time,
    schema_name,
    LEFT(DIGEST_TEXT, 100) AS sql_preview,
    COUNT_STAR AS exec_count,
    TRUNCATE(AVG_TIMER_WAIT/1000000000000, 6) AS avg_time_s
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL
  AND AVG_TIMER_WAIT > 1000000000000
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
" >> /var/log/mysql_slow_queries.log

2. 检查锁等待

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 保存为存储过程
DELIMITER $$
CREATE PROCEDURE check_lock_waits()
BEGIN
    SELECT
        CONCAT('锁等待检测时间: ', NOW()) AS info;

    SELECT
        w.requesting_thread_id AS waiting_thread,
        w.blocking_thread_id AS blocking_thread,
        waiting.processlist_id AS waiting_pid,
        blocking.processlist_id AS blocking_pid,
        waiting_stmt.sql_text AS waiting_sql,
        blocking_stmt.sql_text AS blocking_sql
    FROM performance_schema.data_lock_waits w
    JOIN performance_schema.threads waiting ON w.requesting_thread_id = waiting.thread_id
    JOIN performance_schema.threads blocking ON w.blocking_thread_id = blocking.thread_id
    LEFT JOIN performance_schema.events_statements_current waiting_stmt
        ON waiting.thread_id = waiting_stmt.thread_id
    LEFT JOIN performance_schema.events_statements_current blocking_stmt
        ON blocking.thread_id = blocking_stmt.thread_id;
END$$
DELIMITER ;

-- 调用
CALL check_lock_waits();

总结

Performance Schema 核心表速查

表名 用途 使用频率
events_statements_summary_by_digest 找出慢 SQL 和高频 SQL ⭐⭐⭐⭐⭐
events_statements_current 查看正在执行的 SQL ⭐⭐⭐⭐
table_io_waits_summary_by_table 找出 I/O 热点表 ⭐⭐⭐⭐
table_io_waits_summary_by_index_usage 找出未使用的索引 ⭐⭐⭐⭐
data_locks / data_lock_waits 诊断锁等待 ⭐⭐⭐
metadata_locks 诊断 MDL 锁 ⭐⭐⭐
threads 查看连接信息 ⭐⭐⭐⭐
file_summary_by_instance 文件 I/O 分析 ⭐⭐
memory_summary_by_thread_by_event_name 内存使用分析 ⭐⭐

最佳实践

  1. 生产环境配置: 只开启必要的 instruments(statement、table I/O、locks)
  2. 定期清理: 定期 TRUNCATE 汇总表，避免数据过多影响查询性能
  3. 结合 sys Schema: 日常监控使用 sys Schema，深度分析使用 Performance Schema
  4. 建立监控脚本: 定期检查慢查询、锁等待、表 I/O 等关键指标
  5. 性能调优流程:
    1
    2
    3
    4
    5
    1. 通过 events_statements_summary_by_digest 找出慢 SQL
    2. 通过 EXPLAIN 分析执行计划
    3. 通过 table_io_waits_summary_by_index_usage 检查索引使用
    4. 通过 events_waits_* 分析等待事件
    5. 优化后清空统计表，重新收集数据验证效果

常用查询模板

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 1. 找出执行最慢的 SQL (TOP 10)
SELECT schema_name, LEFT(DIGEST_TEXT, 100), COUNT_STAR,
       TRUNCATE(AVG_TIMER_WAIT/1000000000000, 6) AS avg_s
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

-- 2. 找出访问最频繁的表 (TOP 10)
SELECT object_schema, object_name, COUNT_STAR
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY COUNT_STAR DESC LIMIT 10;

-- 3. 找出未使用的索引
SELECT CONCAT(object_schema, '.', object_name, '.', index_name) AS unused_index
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys')
  AND index_name IS NOT NULL AND index_name != 'PRIMARY'
  AND COUNT_STAR = 0;

-- 4. 查看当前锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 5. 查看正在执行的慢查询
SELECT thread_id, TRUNCATE(timer_wait/1000000000000, 6) AS exec_s,
       LEFT(sql_text, 100) AS sql_preview
FROM performance_schema.events_statements_current
WHERE sql_text IS NOT NULL AND timer_wait > 5000000000000
ORDER BY timer_wait DESC;

Performance Schema 是 MySQL 性能调优的利器，熟练掌握它可以帮助我们快速定位性能瓶颈，优化数据库性能。

参考资料