MySQL 8.4 Release Notes
性能模式是一个帮助 DBA 进行性能调优的工具，通过进行实际测量而不是“盲目猜测。” 本节演示了一些使用性能模式来实现此目的的方法。此处的讨论依赖于事件过滤的使用，事件过滤在第 29.4.2 节，“性能模式事件过滤”中进行了描述。The Performance Schema is a tool to help a DBA do performance tuning by taking real measurements instead of “wild guesses.” This section demonstrates some ways to use the Performance Schema for this purpose. The discussion here relies on the use of event filtering, which is described in Section 29.4.2, “Performance Schema Event Filtering”.
以下示例提供了一种方法，可用于分析可重复出现的问题，例如调查性能瓶颈。首先，你应该有一个可重复的用例，其性能被认为是“太慢”并需要进行优化，并且你应该启用所有检测工具（完全不进行预过滤）。The following example provides one methodology that you can use to analyze a repeatable problem, such as investigating a performance bottleneck. To begin, you should have a repeatable use case where performance is deemed “too slow” and needs optimization, and you should enable all instrumentation (no pre-filtering at all).
运行该用例。Run the use case.
使用性能模式（Performance Schema）表格，分析性能问题的根本原因。此分析在很大程度上依赖于后期过滤。Using the Performance Schema tables, analyze the root cause of the performance problem. This analysis relies heavily on post-filtering.
对于被排除的问题区域，禁用相应的检测工具。例如，如果分析表明问题与某个存储引擎的文件 I/O 无关，则禁用该引擎的文件 I/O 检测工具。然后清空历史和汇总表以移除之前收集的事件。For problem areas that are ruled out, disable the corresponding instruments. For example, if analysis shows that the issue is not related to file I/O in a particular storage engine, disable the file I/O instruments for that engine. Then truncate the history and summary tables to remove previously collected events.
重复第 1 步的过程。Repeat the process at step 1.
随着每次迭代，性能架构（Performance Schema）输出的内容，特别是
events_waits_history_long表，包含的由不重要工具引起的“噪声”越来越少，并且由于该表具有固定大小，包含的与当前问题分析相关的数据越来越多。With each iteration, the Performance Schema output, particularly theevents_waits_history_longtable, contains less and less “noise” caused by nonsignificant instruments, and given that this table has a fixed size, contains more and more data relevant to the analysis of the problem at hand.随着每次迭代，调查应逐步接近问题的根本原因，因为“信号/噪声”比率提高，使分析更容易。With each iteration, investigation should lead closer and closer to the root cause of the problem, as the “signal/noise” ratio improves, making analysis easier.
一旦确定了性能瓶颈的根本原因，采取相应的纠正措施，例如：Once a root cause of performance bottleneck is identified, take the appropriate corrective action, such as:
调整服务器参数（缓存大小、内存等）。Tune the server parameters (cache sizes, memory, and so forth).
通过不同的方式编写查询来优化查询，Tune a query by writing it differently,
优化数据库模式（表、索引等）。Tune the database schema (tables, indexes, and so forth).
优化代码（仅适用于存储引擎或服务器开发人员）。Tune the code (this applies to storage engine or server developers only).
从第 1 步重新开始，以查看更改对性能的影响。Start again at step 1, to see the effects of the changes on performance.
mutex_instances.LOCKED_BY_THREAD_ID 和 rwlock_instances.WRITE_LOCKED_BY_THREAD_ID 列对于调查性能瓶颈或死锁非常重要。这是通过性能模式（Performance Schema）工具实现的，如下所示：The mutex_instances.LOCKED_BY_THREAD_ID and
rwlock_instances.WRITE_LOCKED_BY_THREAD_ID
columns are extremely important for investigating performance
bottlenecks or deadlocks. This is made possible by Performance
Schema instrumentation as follows:
假设线程 1 因等待互斥锁而被阻塞。Suppose that thread 1 is stuck waiting for a mutex.
您可以确定线程正在等待什么：You can determine what the thread is waiting for:
SELECT * FROM performance_schema.events_waits_current WHERE THREAD_ID =
thread_1;假设查询结果显示线程正在等待互斥锁 A，该锁位于
events_waits_current.OBJECT_INSTANCE_BEGIN。Say the query result identifies that the thread is waiting for mutex A, found inevents_waits_current.OBJECT_INSTANCE_BEGIN.您可以确定哪个线程持有互斥锁 A：You can determine which thread is holding mutex A:
SELECT * FROM performance_schema.mutex_instances WHERE OBJECT_INSTANCE_BEGIN =
mutex_A;假设查询结果显示是线程 2 持有互斥锁 A，如在
mutex_instances.LOCKED_BY_THREAD_ID中所示。Say the query result identifies that it is thread 2 holding mutex A, as found inmutex_instances.LOCKED_BY_THREAD_ID.你可以查看线程 2 正在做什么：You can see what thread 2 is doing:
SELECT * FROM performance_schema.events_waits_current WHERE THREAD_ID =
thread_2;