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

MySQL Performance Schema: Events, Statements, and Waits Deep Dive

大家好，今天我们来深入探讨 MySQL Performance Schema，特别是关于 Events、Statements 和 Waits 这三个关键概念。Performance Schema 是 MySQL 提供的一个强大的性能诊断工具，它允许我们从微观层面了解数据库服务器内部的运行状况，从而识别瓶颈并进行针对性的优化。

1. Performance Schema 概述

Performance Schema 记录了 MySQL 服务器执行过程中的各种事件，包括语句执行、等待、锁定等。这些事件信息可以帮助我们了解服务器的性能瓶颈，例如：

执行时间长的 SQL 语句: 找到消耗大量资源的语句，进行优化。
I/O 等待: 分析磁盘 I/O 是否成为性能瓶颈。
锁竞争: 发现导致阻塞的锁，优化事务设计。
CPU 消耗: 了解哪些线程或操作消耗了大量的 CPU 资源。

默认情况下，Performance Schema 是禁用的，因为它会带来一定的性能开销。但是，在需要进行性能诊断时，启用 Performance Schema 是非常必要的。

启用 Performance Schema:

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

-- 启用 Performance Schema (需要重启 MySQL 服务器)
SET GLOBAL performance_schema = ON;

启用后，我们可以通过查询 Performance Schema 提供的各种表来获取性能数据。

2. Events 详解

Events 代表 MySQL 服务器中发生的各种事件。Performance Schema 提供了多种 events 表，分别记录不同类型的事件。

主要的 Events 表：

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 的事件。
events_transactions_current: 记录当前正在执行的事务的事件。
events_transactions_history: 记录最近执行的事务的事件。
events_transactions_history_long: 记录执行时间较长的事务的事件。

Events 表的常见字段：

字段名	数据类型	描述
`THREAD_ID`	BIGINT UNSIGNED	线程 ID
`EVENT_ID`	BIGINT UNSIGNED	事件 ID
`END_EVENT_ID`	BIGINT UNSIGNED	事件结束 ID
`EVENT_NAME`	VARCHAR(128)	事件名称
`SOURCE`	VARCHAR(64)	事件发生的源代码位置
`TIMER_START`	BIGINT UNSIGNED	事件开始时间 (单位: picoseconds)
`TIMER_END`	BIGINT UNSIGNED	事件结束时间 (单位: picoseconds)
`TIMER_WAIT`	BIGINT UNSIGNED	事件持续时间 (单位: picoseconds)
`LOCK_TIME`	BIGINT UNSIGNED	事件持有锁的时间 (单位: picoseconds)
`SQL_TEXT`	LONGTEXT	SQL 语句文本 (仅适用于 `events_statements_*` 表)
`DIGEST`	VARCHAR(32)	SQL 语句的摘要 (用于聚合相似的语句)
`DIGEST_TEXT`	LONGTEXT	SQL 语句的摘要文本
`CURRENT_SCHEMA`	VARCHAR(64)	当前 schema
`ROWS_AFFECTED`	BIGINT UNSIGNED	受影响的行数 (仅适用于 `events_statements_*` 表)
`ROWS_SENT`	BIGINT UNSIGNED	发送的行数 (仅适用于 `events_statements_*` 表)
`ROWS_EXAMINED`	BIGINT UNSIGNED	扫描的行数 (仅适用于 `events_statements_*` 表)
`CREATED_TMP_DISK_TABLES`	BIGINT UNSIGNED	创建的磁盘临时表数量 (仅适用于 `events_statements_*` 表)
`CREATED_TMP_TABLES`	BIGINT UNSIGNED	创建的内存临时表数量 (仅适用于 `events_statements_*` 表)
`SELECT_FULL_JOIN`	BIGINT UNSIGNED	执行全连接的次数 (仅适用于 `events_statements_*` 表)
`SELECT_FULL_RANGE_JOIN`	BIGINT UNSIGNED	执行全范围连接的次数 (仅适用于 `events_statements_*` 表)
`SELECT_RANGE`	BIGINT UNSIGNED	执行范围扫描的次数 (仅适用于 `events_statements_*` 表)
`SELECT_RANGE_CHECK`	BIGINT UNSIGNED	执行范围检查的次数 (仅适用于 `events_statements_*` 表)
`SELECT_SCAN`	BIGINT UNSIGNED	执行全表扫描的次数 (仅适用于 `events_statements_*` 表)
`SORT_MERGE_PASSES`	BIGINT UNSIGNED	排序合并次数 (仅适用于 `events_statements_*` 表)
`SORT_RANGE`	BIGINT UNSIGNED	范围排序次数 (仅适用于 `events_statements_*` 表)
`SORT_ROWS`	BIGINT UNSIGNED	排序的行数 (仅适用于 `events_statements_*` 表)
`SUM_NO_GOOD_INDEX_USED`	BIGINT UNSIGNED	未使用索引的次数 (仅适用于 `events_statements_*` 表)
`SUM_NO_INDEX_USED`	BIGINT UNSIGNED	没有使用索引的次数 (仅适用于 `events_statements_*` 表)
`NESTING_EVENT_ID`	BIGINT UNSIGNED	嵌套事件 ID
`NESTING_EVENT_TYPE`	ENUM	嵌套事件类型
`INSTRUMENTATION`	VARCHAR(64)	instrumentation
`OBJECT_TYPE`	VARCHAR(64)	对象类型
`OBJECT_SCHEMA`	VARCHAR(64)	对象 schema
`OBJECT_NAME`	VARCHAR(64)	对象名称
`INDEX_NAME`	VARCHAR(64)	索引名称
`OPERATION`	VARCHAR(64)	操作
`NUMBER_OF_BYTES`	BIGINT UNSIGNED	字节数
`FLAGS`	BIGINT UNSIGNED	标志

示例：查找执行时间最长的 SQL 语句

SELECT
    DIGEST_TEXT,
    COUNT(*) AS executions,
    SUM(TIMER_WAIT) AS total_latency,
    AVG(TIMER_WAIT) AS avg_latency,
    MAX(TIMER_WAIT) AS max_latency,
    MIN(TIMER_WAIT) AS min_latency
FROM
    performance_schema.events_statements_summary_global_by_digest
ORDER BY
    total_latency DESC
LIMIT 10;

这个查询会从 events_statements_summary_global_by_digest 表中获取数据，这个表是对 events_statements_* 表的数据进行聚合后的结果，按 DIGEST (SQL 语句的摘要) 分组，统计每个 DIGEST 的执行次数、总执行时间、平均执行时间、最大执行时间和最小执行时间。最后，按照总执行时间降序排列，显示执行时间最长的 10 个 SQL 语句摘要。

3. Statements 详解

Statements events 记录了 SQL 语句的执行信息。通过分析 statements events，我们可以识别出执行时间长的语句、资源消耗大的语句，以及需要优化的语句。

Statements events 表：

events_statements_current: 记录当前正在执行的语句的事件。
events_statements_history: 记录最近执行的语句的事件。
events_statements_history_long: 记录执行时间较长的语句的事件。
events_statements_summary_by_digest: 按照 SQL 语句摘要进行聚合的统计信息。
events_statements_summary_by_account_by_event_name: 按照帐户和事件名进行聚合的统计信息。
events_statements_summary_by_host_by_event_name: 按照主机和事件名进行聚合的统计信息。
events_statements_summary_by_program: 按照程序进行聚合的统计信息。
events_statements_summary_by_thread_by_event_name: 按照线程和事件名进行聚合的统计信息.
events_statements_summary_by_user_by_event_name: 按照用户和事件名进行聚合的统计信息。
events_statements_summary_global_by_event_name: 按照事件名进行聚合的统计信息。
events_statements_summary_global_by_digest: 按照 SQL 语句摘要进行全局聚合的统计信息。

示例：查找执行次数最多的 SQL 语句

SELECT
    DIGEST_TEXT,
    COUNT(*) AS executions
FROM
    performance_schema.events_statements_summary_global_by_digest
ORDER BY
    executions DESC
LIMIT 10;

这个查询与之前的示例类似，只是按照执行次数降序排列，显示执行次数最多的 10 个 SQL 语句摘要。执行次数多也可能代表优化的空间，或者需要重点关注的语句。

示例：查找扫描行数最多的 SQL 语句

SELECT
    DIGEST_TEXT,
    SUM(ROWS_EXAMINED) AS total_rows_examined
FROM
    performance_schema.events_statements_summary_global_by_digest
ORDER BY
    total_rows_examined DESC
LIMIT 10;

这个查询会找到扫描行数最多的 SQL 语句，这通常是全表扫描或者索引使用不当导致的。扫描行数多意味着 I/O 压力大，需要考虑优化 SQL 语句或添加索引。

4. Waits 详解

Waits events 记录了线程等待的信息。通过分析 waits events，我们可以识别出导致线程阻塞的原因，例如锁竞争、I/O 等待等。

Waits events 表：

events_waits_current: 记录当前正在发生的等待事件。
events_waits_history: 记录最近发生的等待事件。
events_waits_history_long: 记录等待时间较长的等待事件。
events_waits_summary_by_instance: 按照等待实例进行聚合的统计信息。
events_waits_summary_by_thread_by_event_name: 按照线程和事件名进行聚合的统计信息。
events_waits_summary_global_by_event_name: 按照事件名进行全局聚合的统计信息。

示例：查找等待时间最长的等待事件

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

这个查询会从 events_waits_summary_global_by_event_name 表中获取数据，按 EVENT_NAME (等待事件名称) 分组，统计每个等待事件的发生次数、总等待时间、平均等待时间、最大等待时间。最后，按照总等待时间降序排列，显示等待时间最长的 10 个等待事件。

常见的等待事件：

wait/synch/mutex/sql/TC_LOG_WRITER_MGR::mutex: 事务提交日志写入相关的互斥锁等待。
wait/io/file/innodb/innodb_data_file: InnoDB 数据文件 I/O 等待。
wait/io/file/innodb/innodb_log_file: InnoDB 日志文件 I/O 等待。
wait/synch/mutex/innodb/buf_pool_mutex: InnoDB 缓冲池互斥锁等待。
wait/lock/table/sql/handler: 表锁等待。

示例：分析锁等待

锁等待是常见的性能瓶颈之一。我们可以通过 Performance Schema 来分析锁等待情况。

SELECT
    OBJECT_NAME,
    COUNT(*) AS lock_count,
    SUM(TIMER_WAIT) AS total_lock_wait_time
FROM
    performance_schema.events_waits_summary_global_by_event_name
WHERE
    EVENT_NAME LIKE 'wait/lock/table/%'
ORDER BY
    total_lock_wait_time DESC
LIMIT 10;

这个查询会找到等待时间最长的表锁事件，并显示相关的表名。通过分析这些信息，我们可以找出导致锁竞争的表，并优化相关的 SQL 语句或事务设计。如果OBJECT_NAME为空，则表示是metadata lock，需要关注。

5. Performance Schema 配置

Performance Schema 的配置选项很多，可以控制记录哪些事件、保留多少历史数据等。合理的配置可以减少 Performance Schema 的性能开销，并提高数据分析的效率。

常见的配置选项：

performance_schema: 控制 Performance Schema 的启用/禁用。
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 表的大小。
performance_schema_digests_size: 控制 SQL 语句摘要的数量。

配置示例：

-- 增加 events_statements_history_long 表的大小
SET GLOBAL performance_schema_events_statements_history_long_size = 10000;

-- 禁用某些 instrumentation (减少性能开销)
UPDATE performance_schema.setup_instruments SET enabled = 'NO' WHERE name LIKE 'memory/%';

需要注意的是，修改 Performance Schema 的配置选项可能需要重启 MySQL 服务器才能生效。

6. 使用 Performance Schema 进行性能诊断的流程

使用 Performance Schema 进行性能诊断通常包括以下步骤：

启用 Performance Schema: 确保 performance_schema 系统变量设置为 ON。
配置 Performance Schema: 根据实际需求配置 Performance Schema 的各种选项，例如调整历史表的大小、禁用不必要的 instrumentation。
收集性能数据: 运行一段时间，收集 Performance Schema 提供的性能数据。
分析性能数据: 使用 SQL 查询分析 Performance Schema 的各种表，找出性能瓶颈。
优化: 根据分析结果，对 SQL 语句、索引、事务设计、服务器配置等方面进行优化。
验证: 优化后，再次收集性能数据，验证优化效果。

7. 性能开销考虑

虽然 Performance Schema 是一个强大的性能诊断工具，但它也会带来一定的性能开销。因此，在生产环境中，需要谨慎评估 Performance Schema 的性能影响，并根据实际需求进行配置。可以通过以下方式来降低 Performance Schema 的性能开销：

只启用必要的 instrumentation: 禁用不必要的 instrumentation 可以减少 Performance Schema 的数据收集量。
合理设置历史表的大小: 历史表的大小直接影响 Performance Schema 的内存消耗。
定期清理历史数据: 定期清理历史数据可以避免历史表占用过多的磁盘空间。
避免在高峰期进行性能诊断: 在高峰期进行性能诊断可能会加剧服务器的负载。

8. 结合 sys schema 使用

sys schema 是 MySQL 5.7 引入的一个辅助 schema，它提供了一系列视图和存储过程，可以简化 Performance Schema 的数据分析。sys schema 基于 Performance Schema，提供了更易于使用的接口。

示例：使用 sys schema 查找执行时间最长的 SQL 语句

SELECT
    statement,
    COUNT(*) AS exec_count,
    SUM(timer_wait) AS total_latency,
    AVG(timer_wait) AS avg_latency
FROM
    sys.x$statements_by_statement_latency
ORDER BY
    total_latency DESC
LIMIT 10;

这个查询使用了 sys.x$statements_by_statement_latency 视图，它提供了按语句延迟排序的统计信息。 sys schema 提供了许多类似的视图，可以帮助我们更方便地分析 Performance Schema 的数据。

9. 总结

Performance Schema 是 MySQL 性能诊断和调优的强大工具。通过深入理解 Events、Statements 和 Waits 这三个关键概念，以及合理配置 Performance Schema，我们可以更好地了解 MySQL 服务器的内部运行状况，识别性能瓶颈，并进行针对性的优化。善用 Performance Schema，可以让你的 MySQL 数据库跑得更快、更稳。

10. 监控和告警机制

为了更好地利用Performance Schema，可以将它的数据集成到监控系统中，设置告警规则。例如，当某个SQL语句的平均执行时间超过阈值，或者锁等待时间过长时，触发告警，及时通知DBA进行处理。

11. 定期分析和报告

定期对Performance Schema的数据进行分析，并生成性能报告。这样可以及时发现潜在的性能问题，并为未来的优化工作提供数据支持。例如，可以每周或每月生成一份报告，总结这段时间内执行时间最长的SQL语句、锁等待情况等。

12. 自动化诊断工具

可以开发一些自动化诊断工具，基于Performance Schema的数据进行分析，自动识别性能瓶颈，并给出优化建议。这些工具可以大大提高性能诊断的效率，减轻DBA的工作负担。