【MySQL深入详解】第06篇：Performance Schema详解——MySQL性能诊断神器

开篇引入

"为什么这个查询突然变慢了？"——这是DBA最常被问到的问题，也是最难回答的问题之一。

你可能试过：

• 看慢查询日志，发现都是老面孔
• 查SHOW PROCESSLIST，一堆查询在跑，分不清主次
• 看监控面板，只看到整体负载，看不到细节

Performance Schema就是来解决这个问题的。它是MySQL内置的性能诊断工具，能让你深入MySQL内部，看到每一个查询、每一个锁、每一个等待的详细信息。

Performance Schema是什么

Performance Schema本质上是MySQL内部的"监控探针"。在MySQL代码中插入了探测代码，当特定事件发生时记录下来。

两个核心概念：

1. 插桩（Instrument）：MySQL代码中的探测点。比如你想知道元数据锁的使用情况，就启用wait/lock/meta-data/sql/mdl这个插桩。

2. 消费者表（Consumer）：存储插桩收集的数据的地方。启用插桩后，相关数据会写入对应的消费者表。

┌─────────────────────────────────────────────────────────┐
│                     用户查询                            │
└─────────────────┬───────────────────────────────────────┘
                  ▼
┌─────────────────────────────────────────────────────────┐
│              Performance Schema 插桩                     │
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────┐ │
│  │ statement/* │    │   wait/*    │    │  stage/*    │ │
│  └──────┬──────┘    └──────┬──────┘    └──────┬──────┘ │
└─────────┼──────────────────┼──────────────────┼─────────┘
          ▼                  ▼                  ▼
┌─────────────────────────────────────────────────────────┐
│                   消费者表                               │
│  events_statements_* │ events_waits_* │ events_stages_*│
└─────────────────────────────────────────────────────────┘

重要警告：启用插桩会消耗CPU资源。插桩越多，额外开销越大。所以要按需启用，不要全开。

Performance Schema的表结构

MySQL 8.0.25的performance_schema中有110个表，但可以分为几大类：

1. 当前和历史数据表

表名以这些结尾：

• *_current：当前正在执行的事件
• *_history：每个线程最近完成的10个事件
• *_history_long：全局最近10000个事件

事件类型包括：

• events_waits_*：服务器等待（如获取锁）
• events_statements_*：SQL语句
• events_stages_*：语句执行阶段（如创建临时表）
• events_transactions_*：事务

2. 汇总表和摘要表

聚合统计信息，比如：

-- 查看按线程聚合的内存使用
SELECT * FROM memory_summary_by_thread_by_event_name;

-- 查看SQL语句统计摘要
SELECT * FROM events_statements_summary_by_digest;

**摘要（Digest）**的作用是把SQL语句归一化：

-- 这些不同的查询会被归为同一摘要
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 999;

-- 摘要后变成
SELECT * FROM users WHERE id = ?

3. 实例表

记录对象实例信息：

-- 查看当前打开的文件
SELECT * FROM file_instances;

-- 查看互斥锁实例
SELECT * FROM mutex_instances;

4. 设置表

用于运行时配置：

-- 查看所有插桩
SELECT * FROM setup_instruments;

-- 查看消费者
SELECT * FROM setup_consumers;

-- 查看对象监控配置
SELECT * FROM setup_objects;

配置Performance Schema

启用/禁用Performance Schema

-- 查看当前状态
SHOW VARIABLES LIKE 'performance_schema';
-- ON表示启用，这是只读变量，需要在配置文件中设置

启用/禁用插桩

方法1：直接修改表

-- 启用SELECT语句插桩
UPDATE setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME = 'statement/sql/select';

-- 启用所有SQL语句插桩
UPDATE setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'statement/sql/%';

-- 启用所有wait类插桩（会影响性能，谨慎使用）
UPDATE setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'wait/%';

方法2：使用sys存储过程

-- 启用所有SQL语句插桩
CALL sys.ps_setup_enable_instrument('statement/sql/%');

-- 禁用某个插桩
CALL sys.ps_setup_disable_instrument('wait/synch/mutex/sql/PAGE::lock');

方法3：启动参数（永久生效）

[mysqld]
performance-schema-instrument='statement/sql/select=ON'
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

启用/禁用消费者表

-- 查看所有消费者
SELECT * FROM setup_consumers;

-- 启用的消费者包括：
-- events_statements_current
-- events_statements_history
-- events_statements_history_long
-- events_transactions_current
-- ...

-- 启用特定消费者
UPDATE setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME = 'events_statements_history';

实战：诊断慢查询

找到最慢的查询

SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT/1000000000000 AS total_seconds,
    AVG_TIMER_WAIT/1000000000000 AS avg_seconds,
    MAX_TIMER_WAIT/1000000000000 AS max_seconds,
    SUM_ROWS_EXAMINED AS rows_scanned,
    SUM_ROWS_SENT AS rows_returned,
    SUM_SORT_ROWS AS rows_sorted,
    SUM_CREATED_TMP_DISK_TABLES AS temp_tables_on_disk,
    SUM_NO_INDEX_USED AS no_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

找到没用索引的查询

SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_NO_INDEX_USED AS total_no_index_scans,
    SUM_ROWS_EXAMINED AS rows_scanned
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC;

找到创建临时表的查询

SELECT 
    DIGEST_TEXT AS query,
    SUM_CREATED_TMP_TABLES AS temp_tables_created,
    SUM_CREATED_TMP_DISK_TABLES AS temp_tables_on_disk,
    ROUND(SUM_CREATED_TMP_DISK_TABLES / 
          NULLIF(SUM_CREATED_TMP_TABLES, 0) * 100, 1) AS disk_pct
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC;

如果磁盘临时表比例很高，说明查询需要优化，可能需要添加索引或重写SQL。

实战：诊断锁等待

启用元数据锁监控

-- 启用MDL插桩
UPDATE setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME = 'wait/lock/metadata/sql/mdl';

查看当前锁等待

SELECT 
    mdl.OBJECT_TYPE,
    mdl.OBJECT_SCHEMA,
    mdl.OBJECT_NAME,
    mdl.LOCK_TYPE,
    mdl.LOCK_STATUS,
    mdl.THREAD_ID AS waiting_thread,
    t.PROCESSLIST_ID AS waiting_processlist_id,
    t.PROCESSLIST_INFO AS waiting_query,
    mdl.BLOCKER_THREAD_ID,
    bt.PROCESSLIST_ID AS blocker_processlist_id
FROM performance_schema.metadata_locks mdl
JOIN performance_schema.threads t ON mdl.THREAD_ID = t.THREAD_ID
LEFT JOIN performance_schema.threads bt ON mdl.BLOCKER_THREAD_ID = bt.THREAD_ID
WHERE mdl.LOCK_STATUS = 'WAITING';

这个查询能清楚地告诉你：哪个查询在等锁，等什么类型的锁，是谁持有锁。

常见锁等待场景

场景1：长事务持有MDL

-- 事务1开始但未提交
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- 获取MDL
-- 此时MDL被持有，不提交

-- 事务2尝试DDL（被阻塞）
ALTER TABLE orders ADD COLUMN note VARCHAR(255);  -- 等待MDL

场景2：大量并发写入

-- 查看当前运行中的查询
SELECT * FROM performance_schema.events_statements_current
WHERE COMMAND != 'Sleep';

-- 查看锁等待超时
SHOW VARIABLES LIKE 'lock_wait_timeout';  -- 默认31536000秒！

实战：内存诊断

内存都去哪了

-- 按组件查看内存使用
SELECT 
    SUBSTRING_INDEX(NAME, '/', 1) AS component,
    COUNT(*) AS allocation_count,
    SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 AS used_mb
FROM performance_schema.memory_summary_global_by_event_name
GROUP BY component
ORDER BY used_mb DESC;

InnoDB内存使用

-- 查看InnoDB相关内存分配
SELECT 
    SUBSTRING_INDEX(NAME, '/', 2) AS component,
    COUNT(*) AS instances,
    SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 AS used_mb
FROM performance_schema.memory_summary_by_thread_by_event_name
WHERE NAME LIKE 'memory/innodb/%'
GROUP BY component
ORDER BY used_mb DESC;

按线程查看内存

SELECT 
    t.PROCESSLIST_USER,
    t.PROCESSLIST_HOST,
    SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 AS used_mb
FROM performance_schema.memory_summary_by_thread_by_event_name m
JOIN performance_schema.threads t ON m.THREAD_ID = t.THREAD_ID
GROUP BY t.PROCESSLIST_USER, t.PROCESSLIST_HOST
ORDER BY used_mb DESC;

实战：语句执行阶段分析

-- 启用stage插桩
UPDATE setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'stage/%';

UPDATE setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_stages_%';

查看哪个阶段最慢

SELECT 
    es.DIGEST_TEXT AS query,
    es.EVENT_NAME AS stage,
    es.TIMER_WAIT/1000000000000 AS duration_seconds
FROM performance_schema.events_statements_current esc
JOIN performance_schema.events_stages_current es ON esc.THREAD_ID = es.THREAD_ID
WHERE esc.DIGEST_TEXT IS NOT NULL;

常见性能问题对应的阶段

阶段名	含义	问题信号
stage/sql/starting	初始化	通常很快
stage/sql/checking table	检查表	检查是否需要修复
stage/sql/opening tables	打开表	文件描述符不足
stage/sql/init	初始化	正常很快
stage/sql/creating sort temp table	创建临时表	临时表写入磁盘
stage/sql/logging slow query	写入慢日志	查询已执行完

sys schema：Performance Schema的好帮手

sys schema是MySQL 5.7+自带的，基于Performance Schema数据的视图和存储过程集合。它的目的就是让Performance Schema更好用。

-- 查看最慢的SQL（sys视图）
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

-- 查看没用索引的SQL
SELECT * FROM sys.statements_with_full_table_scans;

-- 查看临时表使用情况
SELECT * FROM sys.statements_with_temp_tables;

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

监控配置最佳实践

开发/测试环境

[mysqld]
# 启用大部分插桩，保留性能用于测试
performance-schema-instrument='%=ON'

生产环境

[mysqld]
# 只启用需要的插桩
performance-schema-instrument='statement/%=ON'
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
performance-schema-instrument='stage/%=ON'

# 禁用高开销的插桩
performance-schema-instrument='wait/synch/mutex/innodb%=OFF'
performance-schema-instrument='wait/io/file/innodb/innodb_log_file=OFF'

创建初始化脚本

-- 保存到init.sql
INSERT INTO performance_schema.setup_instruments 
SELECT * FROM performance_schema.setup_instruments 
WHERE NAME LIKE 'statement/%' AND ENABLED = 'NO';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_statements_%';

启动时加载：

[mysqld]
init-file=/path/to/init.sql

小结

1. Performance Schema是MySQL内置的诊断工具，能深入看到查询、锁、内存的详细信息
2. 插桩会产生开销，生产环境要按需启用，不要全开
3. 消费者表存储在内存中，重启后会释放，不会真正"泄漏内存"
4. **摘要（Digest）**把SQL归一化，方便聚合统计
5. sys schema是Performance Schema的快捷方式，善用它的视图和存储过程
6. 元数据锁监控特别有用，能帮你快速定位DDL被谁阻塞

Performance Schema刚出来时评价不太好，主要是早期版本开销大。现在的MySQL 8.0已经优化了很多，建议生产环境始终开启，按需启用特定插桩。

---

上一篇：【第05篇】MySQL监控体系——SLO驱动的可靠性工程

下一篇：【第07篇】基准测试方法论——如何科学评估MySQL性能

---

延伸阅读

• MySQL官方文档：Performance Schema
• 《高性能MySQL》第2章 监控部分
• Percona Blog: Practical Performance Schema