MySQL Performance Schema详解与实战应用

Performance Schema是MySQL内置的性能监控系统，自5.5版本引入以来已成为数据库性能分析与优化的核心工具。本文将全面解析其架构原理、配置方法及典型应用场景，帮助您掌握这一强大的性能诊断利器。

一、Performance Schema核心架构

Performance Schema采用插桩-消费者模型构建，通过轻量级的内存表存储性能数据，对数据库性能影响通常控制在5%以内。其核心组件包括：

插桩点(Instruments)：嵌入MySQL代码的探测点，按层级命名如wait/io/file/sql/binlog，分为：
- 等待事件：锁、I/O等资源等待
- 阶段事件：查询执行子阶段（如排序）
- 语句事件：完整SQL执行过程
- 事务事件：事务生命周期
消费者(Consumers)：存储采集数据的表，分为四类：
- 当前事件表（_current）：实时活动事件
- 历史事件表（_history和_history_long）：最近完成事件
- 摘要表：聚合统计信息
- 实例表：监控对象元数据
数据流机制：事件触发插桩→数据传递至消费者→内存表存储→通过SQL查询访问。所有数据仅存于内存，服务重启后清空。

与其它监控工具对比：

特性	Performance Schema	information_schema	企业版监控
开销	低	中	低
数据粒度	细粒度	中	细粒度
历史数据	无	无	有
配置灵活性	高	低	高

二、配置与启用方法

1. 基础启用

1-- 检查是否启用（默认ON）
2SHOW VARIABLES LIKE 'performance_schema';
3
4-- 配置文件永久启用（my.cnf）
5[mysqld]
6performance_schema=ON
7

重启服务后生效。

2. 精细化配置

通过setup_instruments和setup_consumers表控制监控范围：

1-- 启用SQL语句监控（生产环境推荐）
2UPDATE performance_schema.setup_instruments 
3SET ENABLED='YES', TIMED='YES' 
4WHERE NAME LIKE 'statement/%';
5
6-- 启用对应的消费者
7UPDATE performance_schema.setup_consumers 
8SET ENABLED='YES' 
9WHERE NAME LIKE '%statements%';
10

注意事项：

避免全量启用（如UPDATE setup_instruments SET ENABLED='YES'）以防性能陡增
监控粒度越细，开销越大（如wait/lock%插桩高频调用）
历史表大小可通过变量调整（如performance_schema_events_statements_history_size）

三、核心监控场景与SQL示例

1. SQL性能分析

1-- 最耗时SQL Top10（单位：皮秒，1秒=10^12皮秒）
2SELECT DIGEST_TEXT, 
3       COUNT_STAR,
4       SUM_TIMER_WAIT/1000000000000 AS total_sec,
5       AVG_TIMER_WAIT/1000000000000 AS avg_sec
6FROM performance_schema.events_statements_summary_by_digest
7ORDER BY SUM_TIMER_WAIT DESC
8LIMIT 10;
9

关键字段：

SUM_ROWS_EXAMINED：扫描行数（索引优化依据）
SUM_CREATED_TMP_TABLES：临时表使用情况
SUM_SORT_ROWS：排序行数

2. 锁等待分析

1-- 当前锁等待链
2SELECT * FROM performance_schema.events_waits_current 
3WHERE EVENT_NAME LIKE '%lock%' 
4ORDER BY TIMER_WAIT DESC;
5
6-- 元数据锁阻塞（MySQL 8.0+）
7SELECT * FROM performance_schema.metadata_locks 
8WHERE LOCK_STATUS='PENDING';
9

结合sys.innodb_lock_waits视图可快速定位死锁。

3. I/O瓶颈识别

1-- 文件I/O热点
2SELECT FILE_NAME, 
3       SUM_NUMBER_OF_BYTES_READ/1024 AS read_kb,
4       SUM_NUMBER_OF_BYTES_WRITE/1024 AS write_kb
5FROM performance_schema.file_summary_by_instance
6ORDER BY (read_kb + write_kb) DESC
7LIMIT 10;
8

特别关注tmp文件与慢查询日志文件。

4. 内存使用分析

1-- 内存分配Top10
2SELECT EVENT_NAME,
3       CURRENT_NUMBER_OF_BYTES/1024/1024 AS current_mb
4FROM performance_schema.memory_summary_global_by_event_name
5ORDER BY current_mb DESC
6LIMIT 10;
7

关键指标：

InnoDB缓冲池：memory/innodb/buf_buf_pool
临时表内存：memory/sql/TMP_TABLE

四、高级应用技巧

1. 使用sys Schema简化查询

MySQL 5.7+提供的sys Schema基于Performance Schema构建，提供友好视图：

1-- 查看等待最多的主机
2SELECT * FROM sys.host_summary_by_statement_latency;
3
4-- I/O密集型文件
5SELECT * FROM sys.io_global_by_file_by_bytes 
6WHERE file LIKE '%ibdata%';
7

优势：

自动单位转换（皮秒→秒）
预定义关联查询
优化建议生成

2. 自动化监控方案

1-- 创建存储过程定期收集数据
2DELIMITER $$
3CREATE PROCEDURE collect_perf_stats()
4BEGIN
5  -- 记录慢查询
6  INSERT INTO slow_query_archive
7  SELECT NOW(), DIGEST_TEXT, SUM_TIMER_WAIT
8  FROM performance_schema.events_statements_summary_by_digest
9  WHERE SUM_TIMER_WAIT > 1000000000000; -- 超过1秒
10  
11  -- 重置计数器
12  CALL sys.ps_truncate_all_tables(FALSE);
13END
14$$
15DELIMITER ;
16
17-- 创建定时任务
18CREATE EVENT perf_monitor
19ON SCHEDULE EVERY 1 HOUR
20DO CALL collect_perf_stats();
21

可集成Prometheus+Grafana实现可视化。

3. 生产环境优化建议

按需监控：故障排查时启用特定插桩，完成后关闭
资源控制：

1-- 限制历史记录条数  
2SET GLOBAL performance_schema_events_waits_history_size=100;

避免全量采集：特别谨慎启用wait/synch/%（锁监控）和wait/io/%（I/O监控）
结合慢查询日志：long_query_time与Performance Schema互补

五、典型性能问题诊断流程

识别慢查询：

1-- 高频慢查询  
2SELECT DIGEST_TEXT, COUNT_STAR  
3FROM events_statements_summary_by_digest  
4WHERE SUM_TIMER_WAIT > 1000000000  
5ORDER BY COUNT_STAR DESC;

分析执行阶段：

1-- 查询各阶段耗时  
2SELECT EVENT_NAME, TIMER_WAIT/1000000000 AS time_sec  
3FROM events_stages_history_long  
4WHERE THREAD_ID = (SELECT THREAD_ID  
5                   FROM threads  
6                   WHERE PROCESSLIST_ID=CONNECTION_ID());

定位资源瓶颈：
- 高CPU：检查events_stages中sorting/tmp table阶段
- 高I/O：分析file_summary_by_instance
- 锁竞争：查看events_waits中的wait/synch%事件
优化验证：通过sys.session视图实时观察优化效果

六、版本演进与限制

MySQL 8.0增强：

新增data_lock_waits表细化锁监控
增强内存统计（memory/innodb/*）
优化器跟踪集成

使用限制：

历史数据不持久化，重启后丢失
内存占用不可动态释放（需重启）
部分指标需在服务启动前启用插桩（如缓冲池内存分配）

Performance Schema已成为MySQL性能优化的"显微镜"，合理使用可精准定位性能瓶颈。建议结合业务场景逐步深入，从SQL优化到系统级调优，构建完整的数据库性能管理体系。

《MySQL Performance Schema详解与实战应用》是转载文章，点击查看原文。