MySQL性能诊断与调优之：`MySQL`的`Performance Schema`：`Events`、`Statements`和`Waits`的详细分析。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，下面是一篇关于 MySQL Performance Schema 中 Events、Statements 和 Waits 的详细分析的技术文章，以讲座的模式呈现：

MySQL Performance Schema：Events、Statements 和 Waits 的深度剖析

大家好！今天我们来深入探讨 MySQL Performance Schema，重点关注 Events、Statements 和 Waits 这三个核心概念，它们是性能诊断和调优的关键。Performance Schema 是 MySQL 提供的一个强大的性能监控工具，它收集了服务器运行时的各种事件信息，帮助我们了解数据库的内部运作，识别性能瓶颈，并采取相应的优化措施。

一、Performance Schema 简介

Performance Schema 是 MySQL 5.5 及更高版本中引入的一个性能监控工具。它通过收集服务器运行时的各种事件信息，例如语句执行、等待事件、锁竞争等，帮助我们了解数据库的内部运作，识别性能瓶颈，并采取相应的优化措施。

与慢查询日志相比，Performance Schema 提供了更细粒度的信息，并且对服务器性能的影响更小。它可以监控到更短的查询和更细致的等待事件，从而帮助我们更准确地定位问题。

启用 Performance Schema:

默认情况下，Performance Schema 可能没有完全启用。可以通过以下方式检查和启用：

-- 检查 Performance Schema 是否启用
SELECT @@performance_schema;

-- 启用 Performance Schema (如果未启用)
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%';
UPDATE performance_instruments SET enabled = 'YES' WHERE name LIKE '%statement%';
UPDATE performance_instruments SET enabled = 'YES' WHERE name LIKE '%transaction%';

UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%events%';

FLUSH INSTRUMENT STATISTICS;

Performance Schema 的核心表:

Performance Schema 包含大量的表，但以下几个是最常用的：

events_statements_current: 当前正在执行的语句。
events_statements_history: 最近执行的语句的历史记录 (每个线程)。
events_statements_history_long: 最近执行的语句的历史记录 (所有线程)。
events_waits_current: 当前正在发生的等待事件。
events_waits_history: 最近发生的等待事件的历史记录 (每个线程)。
events_waits_history_long: 最近发生的等待事件的历史记录 (所有线程)。
events_stages_current: 当前正在执行的 stage (语句执行的阶段)。
events_stages_history: 最近执行的 stage 的历史记录 (每个线程)。
events_stages_history_long: 最近执行的 stage 的历史记录 (所有线程)。
threads: 线程信息。

二、Events：事件总览

Performance Schema 监控的事件可以分为几个主要类别：

Statements (语句事件): 记录 SQL 语句的执行信息，包括执行时间、锁等待时间、扫描的行数等。
Waits (等待事件): 记录线程等待各种资源的信息，例如 IO 等待、锁等待、网络等待等。
Stages (阶段事件): 记录语句执行的不同阶段的信息，例如优化、执行、发送数据等。
Transactions (事务事件): 记录事务的开始、提交和回滚信息。

Events 表是了解数据库活动的基础。它们记录了特定时间内发生的事件，并提供了关于这些事件的各种统计信息。

Events 表结构示例 (以 events_statements_current 为例):

Column Name	Data Type	Description
THREAD_ID	BIGINT UNSIGNED	线程 ID
EVENT_ID	BIGINT UNSIGNED	事件 ID，唯一标识一个事件
END_EVENT_ID	BIGINT UNSIGNED	事件结束时的 ID，如果事件仍在进行中，则为 NULL
EVENT_NAME	VARCHAR(128)	事件名称，例如 `statement/sql/select`
SOURCE	VARCHAR(64)	产生事件的源代码文件和行号
TIMER_START	BIGINT UNSIGNED	事件开始的时间 (皮秒)
TIMER_END	BIGINT UNSIGNED	事件结束的时间 (皮秒)
TIMER_WAIT	BIGINT UNSIGNED	事件持续的时间 (皮秒)
LOCK_TIME	BIGINT UNSIGNED	语句等待锁的时间 (皮秒)
SQL_TEXT	LONGTEXT	SQL 语句的内容 (如果已启用)
DIGEST	VARCHAR(32)	SQL 语句的摘要 (用于分组相似的语句)
DIGEST_TEXT	LONGTEXT	SQL 语句的规范化版本 (用于分组相似的语句)
ROWS_EXAMINED	BIGINT UNSIGNED	扫描的行数
ROWS_SENT	BIGINT UNSIGNED	发送的行数
ROWS_AFFECTED	BIGINT UNSIGNED	影响的行数 (例如 UPDATE, DELETE, INSERT)
FULL_SCAN	ENUM(‘YES’, ‘NO’)	是否进行了全表扫描
LAST_INSERT_ID	BIGINT UNSIGNED	最后插入的 ID
MYSQL_ERRNO	INT UNSIGNED	MySQL 错误代码
WARNING_COUNT	BIGINT UNSIGNED	警告数量
TRANS_ID	BIGINT UNSIGNED	事务 ID
GTID	VARCHAR(255)	全局事务 ID
TRACE	ENUM(‘YES’, ‘NO’)	是否启用跟踪
ERRORS	BIGINT UNSIGNED	错误数量
HOST	VARCHAR(255)	执行语句的主机
USER	VARCHAR(32)	执行语句的用户
CURRENT_SCHEMA	VARCHAR(64)	当前数据库
CONNECTION_ID	BIGINT UNSIGNED	连接 ID

三、Statements：SQL 语句分析

events_statements_* 表族提供了关于 SQL 语句执行的详细信息。通过分析这些表，我们可以找到执行时间长的语句、扫描行数多的语句、锁等待时间长的语句等，从而定位性能瓶颈。

常用查询示例:

查找执行时间最长的语句:

SELECT
    DIGEST_TEXT,
    COUNT(*) AS exec_count,
    SUM(TIMER_WAIT) AS total_latency,
    AVG(TIMER_WAIT) AS avg_latency,
    MAX(TIMER_WAIT) AS max_latency,
    SCHEMA_NAME
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_latency DESC
LIMIT 10;

这个查询会返回执行时间最长的 10 个语句的摘要、执行次数、总执行时间、平均执行时间和最大执行时间，以及语句所在的数据库。

查找扫描行数最多的语句:

SELECT
    DIGEST_TEXT,
    COUNT(*) AS exec_count,
    SUM(ROWS_EXAMINED) AS total_rows_examined,
    AVG(ROWS_EXAMINED) AS avg_rows_examined,
    SCHEMA_NAME
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_rows_examined DESC
LIMIT 10;

这个查询会返回扫描行数最多的 10 个语句的摘要、执行次数、总扫描行数和平均扫描行数，以及语句所在的数据库。

查找锁等待时间最长的语句:

SELECT
    DIGEST_TEXT,
    COUNT(*) AS exec_count,
    SUM(LOCK_TIME) AS total_lock_time,
    AVG(LOCK_TIME) AS avg_lock_time,
    SCHEMA_NAME
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_lock_time DESC
LIMIT 10;

这个查询会返回锁等待时间最长的 10 个语句的摘要、执行次数、总锁等待时间和平均锁等待时间，以及语句所在的数据库。

查看当前正在执行的 SQL 语句：

SELECT THREAD_ID, SQL_TEXT, TIMER_WAIT FROM performance_schema.events_statements_current;

结合 events_statements_history 和 events_statements_history_long:

events_statements_history 和 events_statements_history_long 表分别存储了每个线程和所有线程的语句执行历史记录。可以利用这些表来分析特定时间段内的语句执行情况。例如，可以查找在特定时间段内执行次数最多的语句：

SELECT
    DIGEST_TEXT,
    COUNT(*) AS exec_count
FROM performance_schema.events_statements_history_long
WHERE EVENT_NAME = 'statement/sql/select'  -- 可根据需要更改事件名称
  AND TIMER_START BETWEEN <start_time> AND <end_time>  -- 替换为实际的时间范围
GROUP BY DIGEST_TEXT
ORDER BY exec_count DESC
LIMIT 10;

注意: SQL_TEXT 默认情况下可能不会被收集，因为它会带来一定的性能开销。可以通过修改 performance_schema.setup_instruments 表来启用 SQL_TEXT 的收集：

UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name = 'statement/sql/select';
FLUSH INSTRUMENT STATISTICS;

四、Waits：等待事件分析

events_waits_* 表族提供了关于线程等待各种资源的详细信息。通过分析这些表，我们可以找到导致性能瓶颈的等待事件，例如 IO 等待、锁等待、网络等待等。

常见等待事件类型:

io/file/innodb/innodb_data_file: InnoDB 数据文件 IO 等待。
io/file/innodb/innodb_log_file: InnoDB 日志文件 IO 等待。
wait/synch/mutex/innodb/buf_pool_mutex: InnoDB 缓冲池互斥锁等待。
wait/lock/table/sql/handler: 表锁等待。
wait/io/socket/sql/server_unix_socket: 网络套接字 IO 等待。
wait/synch/cond/sql/MDL_request::COND: 元数据锁等待。

常用查询示例:

查找等待时间最长的等待事件:

SELECT
    EVENT_NAME,
    COUNT(*) AS event_count,
    SUM(TIMER_WAIT) AS total_latency,
    AVG(TIMER_WAIT) AS avg_latency
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY total_latency DESC
LIMIT 10;

这个查询会返回等待时间最长的 10 个等待事件的名称、事件次数、总等待时间和平均等待时间。

查找特定线程的等待事件:

SELECT
    EVENT_NAME,
    TIMER_WAIT
FROM performance_schema.events_waits_history_long
WHERE THREAD_ID = <thread_id>  -- 替换为实际的线程 ID
ORDER BY TIMER_WAIT DESC
LIMIT 10;

这个查询会返回指定线程的等待事件的历史记录，并按照等待时间排序。

查找当前正在发生的等待事件:

SELECT THREAD_ID, EVENT_NAME, TIMER_WAIT FROM performance_schema.events_waits_current;

结合 threads 表:

可以结合 threads 表来获取线程的更多信息，例如线程的状态、类型等。例如，可以查找当前正在执行 SQL 语句并等待锁的线程：

SELECT
    t.THREAD_ID,
    t.NAME,
    t.TYPE,
    es.SQL_TEXT,
    ew.EVENT_NAME,
    ew.TIMER_WAIT
FROM performance_schema.threads t
JOIN performance_schema.events_statements_current es ON t.THREAD_ID = es.THREAD_ID
JOIN performance_schema.events_waits_current ew ON t.THREAD_ID = ew.THREAD_ID
WHERE ew.EVENT_NAME LIKE 'wait/lock/%';

五、Stages：语句执行阶段分析

events_stages_* 表族提供了关于语句执行的不同阶段的信息。通过分析这些表，我们可以了解语句执行过程中哪个阶段耗时最多，从而更有针对性地进行优化。

常见的 Stage:

starting: 语句开始执行。
checking permissions: 检查权限。
Opening tables: 打开表。
init: 初始化。
System lock: 系统锁。
optimizing: 优化。
statistics: 统计。
preparing: 准备。
executing: 执行。
end: 语句执行结束。
query end: 查询结束。
closing tables: 关闭表。
freeing items: 释放项目。
logging slow query: 记录慢查询。
cleaning up: 清理。

常用查询示例:

查找耗时最长的 Stage:

SELECT
    EVENT_NAME,
    COUNT(*) AS event_count,
    SUM(TIMER_WAIT) AS total_latency,
    AVG(TIMER_WAIT) AS avg_latency
FROM performance_schema.events_stages_summary_global_by_event_name
ORDER BY total_latency DESC
LIMIT 10;

这个查询会返回耗时最长的 10 个 Stage 的名称、事件次数、总耗时和平均耗时。

查找特定语句的 Stage 执行情况:

SELECT
    es.SQL_TEXT,
    eg.EVENT_NAME,
    eg.TIMER_WAIT
FROM performance_schema.events_statements_history_long es
JOIN performance_schema.events_stages_history_long eg ON es.THREAD_ID = eg.THREAD_ID AND es.EVENT_ID = eg.NESTING_EVENT_ID
WHERE es.DIGEST_TEXT = '<digest_text>'  -- 替换为实际的语句摘要
ORDER BY eg.TIMER_WAIT DESC;

这个查询会返回指定语句的各个 Stage 的执行时间和语句内容，并按照执行时间排序。

六、Performance Schema 的配置和调整

Performance Schema 的配置会影响其性能开销和收集的信息量。需要根据实际情况进行调整。

关键配置项:

setup_instruments: 控制哪些事件会被监控。
setup_consumers: 控制哪些事件会被存储到表中。
performance_schema_max_table_instances: 控制 Performance Schema 表的最大实例数。
performance_schema_events_statements_history_size: 控制 events_statements_history 表的大小。
performance_schema_events_statements_history_long_size: 控制 events_statements_history_long 表的大小。
performance_schema_events_waits_history_size: 控制 events_waits_history 表的大小。
performance_schema_events_waits_history_long_size: 控制 events_waits_history_long 表的大小。

调整建议:

根据需要启用和禁用事件监控: 只启用需要监控的事件，可以减少性能开销。
调整历史记录表的大小: 根据需要调整历史记录表的大小，以存储足够的信息。
避免过度使用 SQL_TEXT: SQL_TEXT 的收集会带来一定的性能开销，应谨慎使用。
定期清理 Performance Schema 表: Performance Schema 表会不断增长，需要定期清理，可以使用 TRUNCATE TABLE 命令。

七、案例分析：利用 Performance Schema 解决实际问题

假设我们发现数据库的响应时间变慢了。我们可以利用 Performance Schema 来诊断问题。

首先，查看执行时间最长的语句：

SELECT
    DIGEST_TEXT,
    COUNT(*) AS exec_count,
    SUM(TIMER_WAIT) AS total_latency,
    AVG(TIMER_WAIT) AS avg_latency,
    SCHEMA_NAME
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_latency DESC
LIMIT 10;

假设我们发现一个 SELECT 语句的执行时间很长。

然后，查看该语句的执行计划：

EXPLAIN SELECT ...  -- 替换为实际的 SQL 语句

通过分析执行计划，我们发现该语句使用了全表扫描。

接下来，我们可以尝试添加索引来优化该语句：

CREATE INDEX idx_column ON table_name (column_name);  -- 替换为实际的表名和列名

最后，再次查看该语句的执行时间：

SELECT
    DIGEST_TEXT,
    COUNT(*) AS exec_count,
    SUM(TIMER_WAIT) AS total_latency,
    AVG(TIMER_WAIT) AS avg_latency,
    SCHEMA_NAME
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT = '<digest_text>';  -- 替换为实际的语句摘要

如果执行时间明显缩短，则说明索引起到了作用。

八、Performance Schema 的局限性

虽然 Performance Schema 是一个强大的工具，但它也有一些局限性：

性能开销: Performance Schema 会带来一定的性能开销，特别是在高并发的情况下。
配置复杂: Performance Schema 的配置比较复杂，需要一定的学习成本。
数据量大: Performance Schema 会产生大量的数据，需要定期清理。

九、总结

Performance Schema 是 MySQL 性能诊断和调优的重要工具。通过分析 Events、Statements 和 Waits，我们可以深入了解数据库的内部运作，识别性能瓶颈，并采取相应的优化措施。需要注意的是，Performance Schema 的使用需要一定的学习成本，并且会带来一定的性能开销。因此，需要根据实际情况进行配置和调整，以达到最佳的效果。结合实例，强调了 Performance Schema 在实际问题解决中的应用价值。

如何分析与优化 SQL 语句？

通过 Performance Schema 我们可以找到执行时间最长、扫描行数最多的语句，然后通过 EXPLAIN 分析执行计划，添加索引、优化 SQL 语句等方式来提升性能。

如何优化数据库的等待事件？

可以通过分析 Performance Schema 中的等待事件，例如 IO 等待、锁等待等，来找出导致性能瓶颈的原因，然后通过优化硬件配置、调整参数、优化 SQL 语句等方式来减少等待时间。

MySQL Performance Schema：Events、Statements 和 Waits 的深度剖析

发表回复 取消回复

发表回复取消回复