好的，下面我们来深入探讨 Performance Schema 中的 Events_waits、Events_statements 和 Events_transactions 这三个关键表的数据采集机制。
Performance Schema 简介
Performance Schema 是 MySQL 服务器中一个用于监控服务器执行活动的特性。它通过收集各种事件的统计信息，帮助 DBA 和开发人员诊断性能问题，了解服务器内部的运行状况。Performance Schema 的数据采集机制围绕着 "事件" 展开，而 Events_waits、Events_statements 和 Events_transactions 这三个表分别记录了等待事件、语句事件和事务事件的信息。
事件采集的总体框架
Performance Schema 的事件采集过程大致如下：
- 事件源 (Instrumentation)：在 MySQL 服务器代码的关键位置插入 instrumentation，例如函数入口、出口、锁的获取和释放等。这些 instrumentation 点会触发事件的开始、结束等动作。
- 事件消费者 (Consumers)：Performance Schema 提供了一系列的消费者，用于接收和处理 instrumentation 产生的事件数据。
Events_waits、Events_statements和Events_transactions可以看作是不同类型的事件消费者。 - 事件存储 (Storage)：消费者会将事件数据存储到相应的 Performance Schema 表中。这些表通常是内存表，以提高查询效率。
Events_waits：等待事件的数据采集
Events_waits 表记录了线程在执行过程中发生的等待事件。等待事件是指线程在等待某个资源或条件满足时发生的事件，例如等待锁、I/O 操作完成等。
Instrumentation 点：
- 锁等待： 当线程尝试获取锁但被阻塞时，会记录锁等待事件的开始。当线程成功获取锁时，会记录锁等待事件的结束。
- I/O 等待： 当线程发起 I/O 请求后，会记录 I/O 等待事件的开始。当 I/O 请求完成时，会记录 I/O 等待事件的结束。
- 其他等待： 线程等待其他资源或条件时，例如等待网络连接、等待内存分配等，也会记录相应的等待事件。
数据采集流程：
- 当线程进入等待状态时，相应的 instrumentation 点会调用 Performance Schema 的 API，记录等待事件的开始时间、等待的资源类型、等待的资源名称等信息。
- 当线程结束等待状态时，相应的 instrumentation 点会再次调用 Performance Schema 的 API，记录等待事件的结束时间。
- Performance Schema 会将等待事件的信息存储到
Events_waits表中。
Events_waits 表结构示例：
| Column Name | Data Type | Description |
|---|---|---|
| THREAD_ID | BIGINT UNSIGNED | 线程 ID |
| EVENT_ID | BIGINT UNSIGNED | 事件 ID |
| END_EVENT_ID | BIGINT UNSIGNED | 事件结束 ID |
| EVENT_NAME | VARCHAR(128) | 事件名称，例如 wait/synch/mutex/sql/TC_LOG_wait_commit |
| SOURCE | VARCHAR(64) | 触发事件的源代码位置 |
| TIMER_START | BIGINT UNSIGNED | 事件开始时间 (皮秒) |
| TIMER_END | BIGINT UNSIGNED | 事件结束时间 (皮秒) |
| DURATION | BIGINT UNSIGNED | 事件持续时间 (皮秒) |
| WAIT_CLASS | VARCHAR(64) | 等待事件的类别，例如 idle, io, lock |
| WAIT_CLASS_NAME | VARCHAR(128) | 等待事件类别的名称 |
| OBJECT_TYPE | VARCHAR(64) | 等待对象类型，例如 MUTEX, FILE |
| OBJECT_SCHEMA | VARCHAR(64) | 等待对象所属的 Schema |
| OBJECT_NAME | VARCHAR(512) | 等待对象名称，例如锁名称、文件名 |
| NESTING_EVENT_ID | BIGINT UNSIGNED | 嵌套事件 ID |
| NESTING_EVENT_TYPE | ENUM | 嵌套事件类型，例如 STATEMENT, TRANSACTION, STAGE, WAIT, MUTEX_LOCK |
| INSTRUMENTED | ENUM | 指示该事件是否被 instrumented |
| MISSING_INSTRUMENT | ENUM | 指示该事件是否缺少 instrumentation |
示例查询：
SELECT
EVENT_NAME,
COUNT(*) AS event_count,
SUM(DURATION) AS total_duration,
AVG(DURATION) AS avg_duration
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY total_duration DESC
LIMIT 10;这个查询会显示全局范围内，等待事件按总耗时排序的前 10 个事件。
Events_statements：语句事件的数据采集
Events_statements 表记录了 SQL 语句的执行信息，包括执行时间、锁等待时间、扫描的行数等。
Instrumentation 点：
- 语句开始： 当服务器开始执行一个 SQL 语句时，会记录语句事件的开始。
- 语句结束： 当服务器完成执行一个 SQL 语句时，会记录语句事件的结束。
- 锁等待： 在语句执行过程中，如果发生锁等待，会记录锁等待事件。
- 行扫描： 在语句执行过程中，如果需要扫描表中的行，会记录扫描的行数。
- 临时表创建/删除： 在语句执行过程中，创建或者删除临时表，会记录相关事件。
数据采集流程：
- 当服务器开始执行一个 SQL 语句时，相应的 instrumentation 点会调用 Performance Schema 的 API，记录语句事件的开始时间、SQL 语句的内容、连接信息等。
- 在语句执行过程中，如果发生锁等待、行扫描等事件，也会通过 Performance Schema 的 API 记录相关信息。
- 当服务器完成执行一个 SQL 语句时，相应的 instrumentation 点会再次调用 Performance Schema 的 API，记录语句事件的结束时间、执行状态、错误信息等。
- Performance Schema 会将语句事件的信息存储到
Events_statements表中。
Events_statements 表结构示例：
| Column Name | Data Type | Description |
|---|---|---|
| THREAD_ID | BIGINT UNSIGNED | 线程 ID |
| EVENT_ID | BIGINT UNSIGNED | 事件 ID |
| END_EVENT_ID | BIGINT UNSIGNED | 事件结束 ID |
| EVENT_NAME | VARCHAR(128) | 事件名称，通常为 statement/sql/<SQL command> |
| SOURCE | VARCHAR(64) | 触发事件的源代码位置 |
| TIMER_START | BIGINT UNSIGNED | 事件开始时间 (皮秒) |
| TIMER_END | BIGINT UNSIGNED | 事件结束时间 (皮秒) |
| DURATION | BIGINT UNSIGNED | 事件持续时间 (皮秒) |
| LOCK_TIME | BIGINT UNSIGNED | 语句执行期间的锁等待时间 (皮秒) |
| ROWS_EXAMINED | BIGINT UNSIGNED | 语句执行期间扫描的行数 |
| ROWS_SENT | BIGINT UNSIGNED | 语句执行期间发送的行数 |
| ROWS_AFFECTED | BIGINT UNSIGNED | 语句执行期间影响的行数 |
| DIGEST | VARCHAR(32) | SQL 语句的摘要，用于聚合相似的语句 |
| DIGEST_TEXT | LONGTEXT | SQL 语句的文本，可能被截断 |
| CURRENT_SCHEMA | VARCHAR(64) | 当前 Schema |
| OBJECT_TYPE | VARCHAR(64) | 对象类型 |
| OBJECT_SCHEMA | VARCHAR(64) | 对象 Schema |
| OBJECT_NAME | VARCHAR(512) | 对象名称 |
| NESTING_EVENT_ID | BIGINT UNSIGNED | 嵌套事件 ID |
| NESTING_EVENT_TYPE | ENUM | 嵌套事件类型，例如 STATEMENT, TRANSACTION, STAGE, WAIT, MUTEX_LOCK |
| INSTRUMENTED | ENUM | 指示该事件是否被 instrumented |
| MISSING_INSTRUMENT | ENUM | 指示该事件是否缺少 instrumentation |
| MYSQL_ERRNO | INT UNSIGNED | MySQL 错误码，如果发生错误 |
| RETURNED_SQLSTATE | VARCHAR(5) | SQLSTATE 错误码，如果发生错误 |
| MESSAGE_TEXT | TEXT | 错误信息，如果发生错误 |
| ERRORS | BIGINT UNSIGNED | 错误计数 |
| WARNINGS | BIGINT UNSIGNED | 警告计数 |
| FIRST_SEEN | TIMESTAMP | 首次执行时间 |
| LAST_SEEN | TIMESTAMP | 最近一次执行时间 |
| EXECUTION_ENGINE | VARCHAR(64) | 执行引擎 |
| VIEW_DEFINITION | LONGTEXT | 如果是 VIEW, 则包含 VIEW 的定义 |
| TRACE | ENUM | 是否开启了 trace |
示例查询：
SELECT
DIGEST_TEXT,
COUNT(*) AS exec_count,
SUM(DURATION) AS total_duration,
AVG(DURATION) AS avg_duration,
SUM(ROWS_EXAMINED) AS total_rows_examined
FROM performance_schema.events_statements_summary_global_by_digest
ORDER BY total_duration DESC
LIMIT 10;这个查询会显示全局范围内，SQL 语句按总执行时间排序的前 10 个语句。
Events_transactions：事务事件的数据采集
Events_transactions 表记录了事务的执行信息，包括事务的开始时间、结束时间、隔离级别等。
Instrumentation 点：
- 事务开始： 当服务器开始一个事务时 (例如执行
START TRANSACTION语句)，会记录事务事件的开始。 - 事务提交： 当服务器提交一个事务时 (例如执行
COMMIT语句)，会记录事务事件的提交。 - 事务回滚： 当服务器回滚一个事务时 (例如执行
ROLLBACK语句)，会记录事务事件的回滚。 - 保存点设置/释放/回滚： 如果事务中使用了保存点，设置、释放、回滚保存点时会记录相关事件。
数据采集流程：
- 当服务器开始一个事务时，相应的 instrumentation 点会调用 Performance Schema 的 API，记录事务事件的开始时间、连接信息、隔离级别等。
- 当事务提交或回滚时，相应的 instrumentation 点会再次调用 Performance Schema 的 API，记录事务事件的结束时间、事务状态等。
- Performance Schema 会将事务事件的信息存储到
Events_transactions表中。
Events_transactions 表结构示例：
| Column Name | Data Type | Description |
|---|---|---|
| THREAD_ID | BIGINT UNSIGNED | 线程 ID |
| EVENT_ID | BIGINT UNSIGNED | 事件 ID |
| END_EVENT_ID | BIGINT UNSIGNED | 事件结束 ID |
| EVENT_NAME | VARCHAR(128) | 事件名称，例如 transaction |
| SOURCE | VARCHAR(64) | 触发事件的源代码位置 |
| TIMER_START | BIGINT UNSIGNED | 事件开始时间 (皮秒) |
| TIMER_END | BIGINT UNSIGNED | 事件结束时间 (皮秒) |
| DURATION | BIGINT UNSIGNED | 事件持续时间 (皮秒) |
| GTID | VARCHAR(64) | 全局事务ID |
| NESTING_EVENT_ID | BIGINT UNSIGNED | 嵌套事件 ID |
| NESTING_EVENT_TYPE | ENUM | 嵌套事件类型，例如 STATEMENT, TRANSACTION, STAGE, WAIT, MUTEX_LOCK |
| INSTRUMENTED | ENUM | 指示该事件是否被 instrumented |
| MISSING_INSTRUMENT | ENUM | 指示该事件是否缺少 instrumentation |
| MYSQL_ERRNO | INT UNSIGNED | MySQL 错误码，如果发生错误 |
| RETURNED_SQLSTATE | VARCHAR(5) | SQLSTATE 错误码，如果发生错误 |
| MESSAGE_TEXT | TEXT | 错误信息，如果发生错误 |
示例查询：
SELECT
COUNT(*) AS transaction_count,
SUM(DURATION) AS total_duration,
AVG(DURATION) AS avg_duration
FROM performance_schema.events_transactions_summary_global;这个查询会显示全局范围内，事务的总数、总执行时间和平均执行时间。
开启 Performance Schema
Performance Schema 默认情况下可能未启用。可以通过修改 MySQL 配置文件 (例如 my.cnf 或 my.ini) 来启用它。
performance_schema=ON
performance_schema_instrument='wait/%=ON'
performance_schema_instrument='statement/%=ON'
performance_schema_instrument='transaction/%=ON'然后重启 MySQL 服务器。
调整 Performance Schema 的大小
Performance Schema 使用内存来存储事件数据。如果服务器上有大量的并发活动，可能需要增加 Performance Schema 的内存大小。可以通过以下参数进行调整：
performance_schema_events_waits_history_size：控制Events_waits表中保存的事件数量。performance_schema_events_statements_history_size：控制Events_statements表中保存的事件数量。performance_schema_events_transactions_history_size：控制Events_transactions表中保存的事件数量。performance_schema_events_stages_history_size：控制Events_stages表中保存的事件数量。
可以在 MySQL 配置文件中设置这些参数，例如：
performance_schema_events_waits_history_size=10000
performance_schema_events_statements_history_size=10000
performance_schema_events_transactions_history_size=1000
performance_schema_events_stages_history_size=1000重启 MySQL 服务器后，这些更改才会生效。请根据服务器的硬件资源和实际负载情况，合理设置这些参数。
使用建议和注意事项
- 性能开销： 启用 Performance Schema 会带来一定的性能开销，因为它需要收集和存储事件数据。在生产环境中，应该根据实际情况评估性能开销，并进行适当的调整。
- 数据清理： Performance Schema 的数据是存储在内存中的，服务器重启后会丢失。如果需要持久化存储事件数据，可以使用 MySQL 企业版提供的 Performance Schema 历史记录功能，或者将数据导出到其他存储介质中。
- 数据过滤： 可以通过配置 Performance Schema 的 instrument 和 consumers，来过滤掉不需要收集的事件，从而减少性能开销。
- 权限控制： 只有具有足够权限的用户才能访问 Performance Schema 的数据。应该合理控制用户权限，防止敏感数据泄露。
通过示例代码模拟事件采集
虽然我们不能直接修改 MySQL 服务器的代码来插入 instrumentation，但可以通过一个简单的 Python 示例来模拟事件采集的过程。
import time
import random
class Event:
def __init__(self, event_type, start_time, duration, details=None):
self.event_type = event_type
self.start_time = start_time
self.duration = duration
self.end_time = start_time + duration
self.details = details or {}
def __repr__(self):
return f"Event(type={self.event_type}, start={self.start_time}, duration={self.duration}, details={self.details})"
def simulate_wait_event():
start_time = time.time()
duration = random.uniform(0.01, 0.1) # 模拟 10ms - 100ms 的等待
time.sleep(duration)
return Event("wait", start_time, duration, {"wait_resource": "disk_io"})
def simulate_statement_event(sql_query):
start_time = time.time()
duration = random.uniform(0.05, 0.5) # 模拟 50ms - 500ms 的语句执行
time.sleep(duration)
rows_examined = random.randint(100, 1000)
return Event("statement", start_time, duration, {"sql": sql_query, "rows_examined": rows_examined})
def simulate_transaction_event():
start_time = time.time()
duration = random.uniform(0.1, 1.0) # 模拟 100ms - 1s 的事务执行
time.sleep(duration)
return Event("transaction", start_time, duration)
# 模拟事件采集
for i in range(5):
wait_event = simulate_wait_event()
print(f"Collected wait event: {wait_event}")
sql_query = f"SELECT * FROM users WHERE id = {random.randint(1, 1000)}"
statement_event = simulate_statement_event(sql_query)
print(f"Collected statement event: {statement_event}")
if i % 2 == 0:
transaction_event = simulate_transaction_event()
print(f"Collected transaction event: {transaction_event}")这个示例代码模拟了 Events_waits、Events_statements 和 Events_transactions 三种事件的采集。它使用 time.sleep() 函数来模拟事件的持续时间，并使用 random 模块来生成随机数据。虽然这只是一个简单的模拟，但它可以帮助我们更好地理解 Performance Schema 的事件采集过程。
数据采集的原理与应用
总而言之，Events_waits、Events_statements 和 Events_transactions 表的数据采集依赖于 MySQL 服务器代码中精心设计的 instrumentation。这些 instrumentation 点在关键时刻触发事件的开始和结束，Performance Schema 消费者负责接收和处理这些事件数据，并将其存储到相应的表中。通过查询这些表，我们可以深入了解服务器的性能瓶颈，优化 SQL 语句，并提高数据库的整体性能。
Performance Schema 是强大的性能分析工具
Performance Schema 提供了强大的性能分析功能，通过 Events_waits、Events_statements 和 Events_transactions 等表，可以深入了解 MySQL 服务器的运行状况。了解其数据采集机制，能更好的利用 Performance Schema 进行性能诊断和优化。
实践是最好的学习方法
建议在实际环境中启用 Performance Schema，并尝试使用各种查询来分析性能数据。通过实践，可以更深入地理解 Performance Schema 的功能和使用方法。