第 29 章 MySQL 性能模式Chapter 29 MySQL Performance Schema

性能模式提供了一种在运行时检查服务器内部执行的方法。它是通过PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现的。性能模式主要关注性能数据。这与INFORMATION_SCHEMA不同，后者用于检查元数据。The Performance Schema provides a way to inspect internal execution of the server at runtime. It is implemented using the PERFORMANCE_SCHEMA storage engine and the performance_schema database. The Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata.

性能模式监控服务器事件。“事件”是指服务器执行的任何需要时间且已经被监控以便收集时间信息的操作。通常，事件可以是函数调用、等待操作系统响应、SQL 语句执行的某个阶段（例如解析或排序），或者是整个语句或一组语句。事件收集提供了对服务器及多个存储引擎的同步调用（如互斥锁）、文件和表 I/O、表锁等信息的访问。The Performance Schema monitors server events. An “event” is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements. Event collection provides access to information about synchronization calls (such as for mutexes) file and table I/O, table locks, and so forth for the server and for several storage engines.

性能模式事件不同于写入服务器二进制日志的事件（描述数据修改）以及事件调度器事件（这是一种存储程序）。Performance Schema events are distinct from events written to the server's binary log (which describe data modifications) and Event Scheduler events (which are a type of stored program).

性能模式事件是针对特定的 MySQL 服务器实例的。性能模式表被认为是服务器本地的，对它们的更改不会被复制或写入二进制日志。Performance Schema events are specific to a given instance of the MySQL Server. Performance Schema tables are considered local to the server, and changes to them are not replicated or written to the binary log.

可用当前事件，以及事件历史和摘要。这使您能够确定执行过多少次已插装的活动以及这些活动花费了多少时间。事件信息可用于显示特定线程的活动，或与特定对象（如互斥锁或文件）相关的活动。Current events are available, as well as event histories and summaries. This enables you to determine how many times instrumented activities were performed and how much time they took. Event information is available to show the activities of specific threads, or activity associated with particular objects such as a mutex or file.

PERFORMANCE_SCHEMA 存储引擎通过服务器源代码中的“插装点”收集事件数据。The PERFORMANCE_SCHEMA storage engine collects event data using “instrumentation points” in server source code.

收集的事件存储在 performance_schema 数据库的表中。可以像查询其他表一样使用 SELECT 语句查询这些表。Collected events are stored in tables in the performance_schema database. These tables can be queried using SELECT statements like other tables.

可以通过 SQL 语句更新 performance_schema 数据库中的表来动态修改性能模式配置。配置更改会立即影响数据收集。Performance Schema configuration can be modified dynamically by updating tables in the performance_schema database through SQL statements. Configuration changes affect data collection immediately.

性能模式中的表是内存表，不使用持久性磁盘存储。内容将在服务器启动时重新加载，并在服务器关闭时丢弃。Tables in the Performance Schema are in-memory tables that use no persistent on-disk storage. The contents are repopulated beginning at server startup and discarded at server shutdown.

MySQL 支持的所有平台都提供监控功能。Monitoring is available on all platforms supported by MySQL.

可能存在一些限制：计时器的类型可能因平台而异。适用于存储引擎的仪器可能不会在所有存储引擎中实现。每个第三方引擎的仪器化由引擎维护者负责。另见 第 29.20 节，“Performance Schema 的限制”。Some limitations might apply: The types of timers might vary per platform. Instruments that apply to storage engines might not be implemented for all storage engines. Instrumentation of each third-party engine is the responsibility of the engine maintainer. See also Section 29.20, “Restrictions on Performance Schema”.

数据收集是通过修改服务器源代码以添加仪器来实现的。与复制或事件调度器等其他功能不同，Performance Schema 没有单独的线程。Data collection is implemented by modifying the server source code to add instrumentation. There are no separate threads associated with the Performance Schema, unlike other features such as replication or the Event Scheduler.

激活性能模式不会改变服务器行为。例如，它不会导致线程调度发生变化，也不会导致查询执行计划（如EXPLAIN所示）发生变化。Activating the Performance Schema causes no changes in server behavior. For example, it does not cause thread scheduling to change, and it does not cause query execution plans (as shown by EXPLAIN) to change.

服务器监控是持续并无干扰地进行的，开销非常小。激活性能模式不会使服务器无法使用。Server monitoring occurs continuously and unobtrusively with very little overhead. Activating the Performance Schema does not make the server unusable.

解析器保持不变。没有新的关键字或语句。The parser is unchanged. There are no new keywords or statements.

即使性能模式内部失败，服务器代码的执行也会正常进行。Execution of server code proceeds normally even if the Performance Schema fails internally.

当在事件收集初期处理和稍后事件检索之间需要选择时，优先考虑加快收集速度。这是因为收集是持续进行的，而检索是按需进行的，可能永远不会发生。When there is a choice between performing processing during event collection initially or during event retrieval later, priority is given to making collection faster. This is because collection is ongoing whereas retrieval is on demand and might never happen at all.

大多数 Performance Schema 表都有索引，这使优化器可以访问除全表扫描之外的执行计划。有关更多信息，请参见 第 10.2.4 节，“优化 Performance Schema 查询”。Most Performance Schema tables have indexes, which gives the optimizer access to execution plans other than full table scans. For more information, see Section 10.2.4, “Optimizing Performance Schema Queries”.

添加新的检测点很容易。It is easy to add new instrumentation points.

检测是有版本控制的。如果检测实现发生变化，先前检测过的代码仍然可以工作。这对第三方插件的开发者有利，因为不需要升级每个插件以与最新的 Performance Schema 改动保持同步。Instrumentation is versioned. If the instrumentation implementation changes, previously instrumented code continues to work. This benefits developers of third-party plugins because it is not necessary to upgrade each plugin to stay synchronized with the latest Performance Schema changes.