MySQL Performance Schema 和 Sys Schema

Performance Schema 配置

启用 Performance Schema

默认状态：
- MySQL 5.6 及以上：默认启用
- MySQL 5.5：默认禁用
启用方法：
- 在 my.cnf 中配置：
  ini
```
[mysqld]
performance_schema = ON
```
- 或在启动时指定：
  bash
```
mysqld --performance_schema=ON
```

配置项

performance_schema_max_xxx_size：控制各个缓冲区的大小
performance_schema_max_xxx_instances：控制各个实例的最大数量
performance_schema_xxx_enabled：控制是否启用某个监控项

常用配置示例：

ini

[mysqld]
performance_schema = ON
performance_schema_max_thread_instances = 1000
performance_schema_max_event_classes = 100
performance_schema_max_digest_length = 1024

动态配置

Performance Schema 支持动态配置，无需重启 MySQL 服务

使用 SET 语句修改配置：

sql

-- 启用语句监控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement%';

-- 启用等待事件监控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%wait%';

-- 启用阶段事件监控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage%';

Performance Schema 核心表

配置表

setup_instruments：控制哪些事件被收集
setup_consumers：控制哪些事件被存储
setup_timers：配置事件的计时单位
setup_objects：控制哪些对象（表、存储过程等）被监控

实例表

threads：线程信息
events_waits_current：当前等待事件
events_waits_history：每个线程最近的 10 个等待事件
events_waits_history_long：全局最近的 10000 个等待事件
events_stages_current：当前阶段事件
events_stages_history：每个线程最近的 10 个阶段事件
events_stages_history_long：全局最近的 10000 个阶段事件
events_statements_current：当前语句事件
events_statements_history：每个线程最近的 10 个语句事件
events_statements_history_long：全局最近的 10000 个语句事件
events_transactions_current：当前事务事件
events_transactions_history：每个线程最近的 10 个事务事件
events_transactions_history_long：全局最近的 10000 个事务事件

汇总表

events_waits_summary_by_thread_by_event_name：按线程和事件名称汇总的等待事件
events_waits_summary_by_instance：按实例汇总的等待事件
events_waits_summary_global_by_event_name：按事件名称汇总的全局等待事件
events_stages_summary_by_thread_by_event_name：按线程和阶段名称汇总的阶段事件
events_stages_summary_global_by_event_name：按阶段名称汇总的全局阶段事件
events_statements_summary_by_thread_by_event_name：按线程和语句类型汇总的语句事件
events_statements_summary_by_digest：按语句摘要汇总的语句事件
events_statements_summary_global_by_event_name：按语句类型汇总的全局语句事件
events_transactions_summary_by_thread_by_event_name：按线程和事务类型汇总的事务事件
events_transactions_summary_global_by_event_name：按事务类型汇总的全局事务事件

其他表

file_summary_by_event_name：文件 I/O 汇总
file_summary_by_instance：文件实例 I/O 汇总
table_io_waits_summary_by_table：表 I/O 汇总
table_lock_waits_summary_by_table：表锁等待汇总
mutex_instances：互斥锁实例
rwlock_instances：读写锁实例
cond_instances：条件变量实例
file_instances：文件实例

Performance Schema 使用方法

基本查询

查看 Performance Schema 是否启用：
sql
```
SHOW VARIABLES LIKE 'performance_schema';
```

查看当前等待事件：

sql

SELECT * FROM performance_schema.events_waits_current LIMIT 10;

查看最近的语句事件：

sql

SELECT * FROM performance_schema.events_statements_history LIMIT 10;

查看表 I/O 统计：

sql

SELECT * FROM performance_schema.table_io_waits_summary_by_table WHERE OBJECT_SCHEMA = 'test' AND OBJECT_NAME = 'mytable';

性能分析示例

1. 分析慢查询

查询耗时最长的语句：

sql

SELECT 
  DIGEST_TEXT, 
  COUNT_STAR AS exec_count, 
  SUM_TIMER_WAIT AS total_time, 
  AVG_TIMER_WAIT AS avg_time,
  MAX_TIMER_WAIT AS max_time
FROM 
  performance_schema.events_statements_summary_by_digest
ORDER BY 
  total_time DESC
LIMIT 10;

2. 分析 I/O 等待

查询 I/O 等待最多的文件：

sql

SELECT 
  FILE_NAME, 
  EVENT_NAME, 
  SUM_NUMBER_OF_BYTES_READ AS total_read, 
  SUM_NUMBER_OF_BYTES_WRITE AS total_write,
  SUM_TIMER_WAIT AS total_time
FROM 
  performance_schema.file_summary_by_instance
ORDER BY 
  total_time DESC
LIMIT 10;

3. 分析锁等待

查询锁等待最多的表：

sql

SELECT 
  OBJECT_SCHEMA, 
  OBJECT_NAME, 
  SUM_TIMER_WAIT AS total_wait_time,
  COUNT_STAR AS wait_count
FROM 
  performance_schema.table_lock_waits_summary_by_table
ORDER BY 
  total_wait_time DESC
LIMIT 10;

4. 分析线程性能

查询最活跃的线程：

sql

SELECT 
  THREAD_ID, 
  PROCESSLIST_ID, 
  PROCESSLIST_USER, 
  PROCESSLIST_HOST, 
  PROCESSLIST_DB, 
  PROCESSLIST_COMMAND, 
  PROCESSLIST_STATE,
  SUM_TIMER_WAIT AS total_wait_time
FROM 
  performance_schema.threads t
JOIN 
  performance_schema.events_waits_summary_by_thread_by_event_name s
ON 
  t.THREAD_ID = s.THREAD_ID
WHERE 
  PROCESSLIST_ID IS NOT NULL
GROUP BY 
  THREAD_ID
ORDER BY 
  total_wait_time DESC
LIMIT 10;

Sys Schema 常用视图

语句性能视图

1. statement_analysis

作用：提供语句执行的详细统计信息
常用字段：
- query_id：语句 ID
- db：数据库名称
- query：SQL 语句
- exec_count：执行次数
- err_count：错误次数
- warn_count：警告次数
- total_latency：总执行时间
- avg_latency：平均执行时间
- max_latency：最大执行时间
- lock_latency：锁等待时间
- rows_sent：返回行数
- rows_examined：扫描行数
- rows_affected：影响行数
- tmp_tables：临时表数量
- tmp_disk_tables：磁盘临时表数量

使用示例：

sql

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

2. statement_latency_histogram

作用：提供语句执行时间的直方图

使用示例：

sql

SELECT * FROM sys.statement_latency_histogram WHERE db = 'test';

I/O 性能视图

1. io_global_by_file_by_bytes

作用：按文件统计 I/O 字节数

使用示例：

sql

SELECT * FROM sys.io_global_by_file_by_bytes ORDER BY total DESC LIMIT 10;

2. io_global_by_wait_by_bytes

作用：按事件类型统计 I/O 字节数

使用示例：

sql

SELECT * FROM sys.io_global_by_wait_by_bytes ORDER BY total DESC LIMIT 10;

索引使用视图

1. schema_unused_indexes

作用：查找未使用的索引

使用示例：

sql

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'test';

2. schema_redundant_indexes

作用：查找冗余索引

使用示例：

sql

SELECT * FROM sys.schema_redundant_indexes WHERE object_schema = 'test';

锁等待视图

1. innodb_lock_waits

作用：显示当前的 InnoDB 锁等待
使用示例：
sql
```
SELECT * FROM sys.innodb_lock_waits;
```

2. schema_table_lock_waits

作用：显示表锁等待情况

使用示例：

sql

SELECT * FROM sys.schema_table_lock_waits WHERE object_schema = 'test' ORDER BY wait_time DESC;

内存使用视图

1. memory_global_by_current_bytes

作用：按分配类型统计当前内存使用情况

使用示例：

sql

SELECT * FROM sys.memory_global_by_current_bytes ORDER BY current_bytes DESC LIMIT 10;

2. memory_by_thread_by_current_bytes

作用：按线程统计当前内存使用情况

使用示例：

sql

SELECT * FROM sys.memory_by_thread_by_current_bytes ORDER BY current_bytes DESC LIMIT 10;

主机和用户视图

1. host_summary

作用：提供主机级别的性能汇总

使用示例：

sql

SELECT * FROM sys.host_summary ORDER BY total_latency DESC LIMIT 10;

2. user_summary

作用：提供用户级别的性能汇总

使用示例：

sql

SELECT * FROM sys.user_summary ORDER BY total_latency DESC LIMIT 10;

Sys Schema 使用技巧

性能问题诊断

1. 查找消耗 CPU 最多的查询

使用 statement_analysis 视图：

sql

SELECT 
  query, 
  exec_count, 
  total_latency, 
  avg_latency,
  rows_examined, 
  rows_sent
FROM 
  sys.statement_analysis
WHERE 
  db = 'test'
ORDER BY 
  total_latency DESC
LIMIT 5;

2. 查找扫描行数过多的查询

使用 statement_analysis 视图：

sql

SELECT 
  query, 
  exec_count, 
  rows_examined, 
  rows_sent,
  rows_examined / rows_sent AS rows_ratio
FROM 
  sys.statement_analysis
WHERE 
  db = 'test' AND rows_sent > 0
ORDER BY 
  rows_ratio DESC
LIMIT 5;

3. 查找创建临时表过多的查询

使用 statement_analysis 视图：

sql

SELECT 
  query, 
  exec_count, 
  tmp_tables, 
  tmp_disk_tables,
  tmp_disk_tables / tmp_tables AS disk_tmp_ratio
FROM 
  sys.statement_analysis
WHERE 
  db = 'test' AND tmp_tables > 0
ORDER BY 
  tmp_disk_tables DESC
LIMIT 5;

索引优化

1. 查找未使用的索引

使用 schema_unused_indexes 视图：

sql

SELECT 
  object_schema, 
  object_name, 
  index_name
FROM 
  sys.schema_unused_indexes
WHERE 
  object_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');

2. 查找冗余索引

使用 schema_redundant_indexes 视图：

sql

SELECT 
  table_schema, 
  table_name, 
  redundant_index_name, 
  dominant_index_name
FROM 
  sys.schema_redundant_indexes;

锁问题诊断

1. 查找锁等待最长的事务

使用 innodb_lock_waits 视图：

sql

SELECT 
  waiting_trx_id, 
  waiting_pid, 
  waiting_query, 
  blocking_trx_id,
  blocking_pid, 
  blocking_query, 
  wait_age
FROM 
  sys.innodb_lock_waits
ORDER BY 
  wait_age DESC;

2. 查找表锁等待

使用 schema_table_lock_waits 视图：

sql

SELECT 
  object_schema, 
  object_name, 
  lock_type, 
  lock_duration,
  wait_count, 
  wait_time
FROM 
  sys.schema_table_lock_waits
ORDER BY 
  wait_time DESC
LIMIT 10;

Performance Schema 和 Sys Schema 最佳实践

配置最佳实践

按需启用监控项：只启用需要监控的事件类型，减少性能开销
调整缓冲区大小：根据服务器负载调整 Performance Schema 的缓冲区大小
定期清理历史数据：定期清理 Performance Schema 的历史数据，避免内存占用过大
合理设置采样率：对于高负载系统，可降低采样率，减少性能影响

使用最佳实践

结合其他工具：结合慢查询日志、EXPLAIN 等工具进行综合性能分析
定期监控：定期收集和分析性能数据，建立性能基线
关注关键指标：重点关注执行时间长、扫描行数多、临时表多的语句
分析趋势变化：比较不同时间段的性能数据，分析性能变化趋势
及时优化：根据性能分析结果，及时优化数据库和查询

性能优化建议

优化查询：根据 Performance Schema 和 Sys Schema 的分析结果，优化慢查询
优化索引：删除未使用和冗余的索引，优化索引设计
优化配置：根据性能数据调整 MySQL 配置参数
优化架构：对于高负载系统，考虑分库分表、读写分离等架构优化
升级硬件：根据 I/O、CPU、内存等瓶颈，升级相应的硬件资源

不同 MySQL 版本的差异

MySQL 5.5

Performance Schema：
- 初始版本，功能有限
- 支持基本的事件监控
- 默认禁用

MySQL 5.6

Performance Schema：
- 增强了事件类型和监控项
- 提高了性能，降低了开销
- 默认启用
- 支持更多的 I/O 监控

MySQL 5.7

Performance Schema：
- 新增了 Sys Schema
- 增强了语句监控
- 支持更多的等待事件类型
- 改进了内存使用
Sys Schema：
- 首次引入
- 提供了大量易用的视图
- 简化了 Performance Schema 的使用

MySQL 8.0

Performance Schema：
- 进一步优化了性能和开销
- 新增了更多的监控项
- 支持监控数据的持久化
- 增强了对并行查询的监控
Sys Schema：
- 新增了更多的视图
- 改进了现有视图的性能
- 支持更多的性能分析场景

常见问题（FAQ）

Q1: Performance Schema 对性能有影响吗？

A1: Performance Schema 设计为低开销，对性能影响较小。优化建议包括：按需启用监控项，只监控需要的事件；调整缓冲区大小，避免内存占用过大；对于高负载系统，可降低采样率。

Q2: 如何减少 Performance Schema 的内存占用？

A2: 解决方案包括：减少监控的事件类型；降低 performance_schema_max_xxx_instances 等参数值；定期清理历史数据；调整 performance_schema_events_xxx_history_size 参数。

Q3: Sys Schema 视图查询慢怎么办？

A3: 可能原因包括 Performance Schema 数据量过大、视图查询涉及多个表的连接、服务器负载过高等。解决方案包括：优化 Performance Schema 配置，减少数据量；增加服务器资源；限制查询结果数量；定期清理历史数据。

Q4: 如何监控复制性能？

A4: 可以使用以下视图监控复制性能：

sys.replication_applier_status_by_coordinator：监控复制应用状态
sys.replication_applier_status_by_worker：监控复制工作线程状态
sys.replication_connection_status：监控复制连接状态

Q5: 如何监控 InnoDB 缓冲池使用情况？

A5: 可以使用以下视图监控 InnoDB 缓冲池使用情况：

sys.innodb_buffer_stats_by_schema：按 schema 监控缓冲池使用情况
sys.innodb_buffer_stats_by_table：按表监控缓冲池使用情况
sys.innodb_buffer_pool_residence_time：监控缓冲池页的驻留时间

MySQL Performance Schema 和 Sys Schema ​

Performance Schema 配置 ​

启用 Performance Schema ​

配置项 ​

动态配置 ​

Performance Schema 核心表 ​

配置表 ​

实例表 ​

汇总表 ​

其他表 ​

Performance Schema 使用方法 ​

基本查询 ​

性能分析示例 ​

1. 分析慢查询 ​

2. 分析 I/O 等待 ​

3. 分析锁等待 ​

4. 分析线程性能 ​

Sys Schema 常用视图 ​

语句性能视图 ​

1. statement_analysis ​

2. statement_latency_histogram ​

I/O 性能视图 ​

1. io_global_by_file_by_bytes ​

2. io_global_by_wait_by_bytes ​

索引使用视图 ​

1. schema_unused_indexes ​

2. schema_redundant_indexes ​

锁等待视图 ​

1. innodb_lock_waits ​

2. schema_table_lock_waits ​

内存使用视图 ​

1. memory_global_by_current_bytes ​

2. memory_by_thread_by_current_bytes ​

主机和用户视图 ​

1. host_summary ​

2. user_summary ​

Sys Schema 使用技巧 ​

性能问题诊断 ​

1. 查找消耗 CPU 最多的查询 ​

2. 查找扫描行数过多的查询 ​

3. 查找创建临时表过多的查询 ​

索引优化 ​

1. 查找未使用的索引 ​

2. 查找冗余索引 ​

锁问题诊断 ​

1. 查找锁等待最长的事务 ​

2. 查找表锁等待 ​

Performance Schema 和 Sys Schema 最佳实践 ​

配置最佳实践 ​

使用最佳实践 ​

性能优化建议 ​

不同 MySQL 版本的差异 ​

MySQL 5.5 ​

MySQL Performance Schema 和 Sys Schema

Performance Schema 配置

启用 Performance Schema

配置项

动态配置

Performance Schema 核心表

配置表

实例表

汇总表

其他表

Performance Schema 使用方法

基本查询

性能分析示例

1. 分析慢查询

2. 分析 I/O 等待

3. 分析锁等待

4. 分析线程性能

Sys Schema 常用视图

语句性能视图

1. statement_analysis

2. statement_latency_histogram

I/O 性能视图

1. io_global_by_file_by_bytes

2. io_global_by_wait_by_bytes

索引使用视图

1. schema_unused_indexes

2. schema_redundant_indexes

锁等待视图

1. innodb_lock_waits

2. schema_table_lock_waits

内存使用视图

1. memory_global_by_current_bytes

2. memory_by_thread_by_current_bytes

主机和用户视图

1. host_summary

2. user_summary

Sys Schema 使用技巧

性能问题诊断

1. 查找消耗 CPU 最多的查询

2. 查找扫描行数过多的查询

3. 查找创建临时表过多的查询

索引优化

1. 查找未使用的索引

2. 查找冗余索引

锁问题诊断

1. 查找锁等待最长的事务

2. 查找表锁等待

Performance Schema 和 Sys Schema 最佳实践

配置最佳实践

使用最佳实践

性能优化建议

不同 MySQL 版本的差异

MySQL 5.5