【第21天】MYSQL进阶-查询优化- performance_schema系列三：事件记录（SQL 小虚竹)

回城传送–》《100天精通MYSQL从入门到就业》

零、前言

今天是学习 SQL 打卡的第 21 天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱 ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

​ 虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

​ 我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：SQL进阶-查询优化- performance_schema系列三：事件记录

本文内容有1万多字，内容非常多，建议阅读方式：记住标题和小模块的介绍 ，内容详解可帮助理解，看不懂也没事，后续章节的实战中也可慢慢体会。
本文适用于收藏，需要查阅指定配置时，可快速找到配置的详解。

一、练习题目

二、SQL思路

SQL进阶-查询优化- performance_schema系列三：事件记录

今天会讲解performance_schema中事件原始记录表。

等待事件表

在性能优化时，硬件负载不高、SQL优化和库表结构优化都搞不定，达到了性能优化瓶颈，需要借助于等待事件来进行分析，找出在MySQL Server内部，到底数据库响应慢是慢在哪里。
等待事件记录表包含三张表，这些表记录了当前与最近在MySQL实例中发生了哪些等待事件，时间消耗是多少。

• events_waits_current表：记录当前正在执行的等待事件的，每个线程只记录1行记录

• events_waits_history表：记录已经执行完的最近的等待事件历史，默认每个线程只记录10行记录

• events_waits_history_long表：记录已经执行完的最近的等待事件历史，默认所有线程的总记录行数为10000行

要注意：等待事件相关配置中，setup_instruments表中绝大部分的等待事件instruments都没有开启(IO相关的等待事件instruments默认大部分已开启)，setup_consumers表中waits相关的consumers配置默认没有开启。

events_waits_current 表

events_waits_current表包含当前的等待事件信息，每个线程只显示一行最近监视的等待事件的当前状态。
在所有包含等待事件行的表中，events_waits_current表是最基础的数据来源。其他包含等待事件数据表在逻辑上是来源于events_waits_current表中的当前事件信息（汇总表除外）。例如，events_waits_history和events_waits_history_long表中的数据是events_waits_current表数据的一个小集合汇总（具体存放多少行数据集合有各自的变量控制）。

实战试试：

开启监听：setup_consumers表列出了可用的使用者类型以及已启用的使用者类型：

SELECT * FROM performance_schema.setup_consumers;

默认events_waits_current是关闭的，修改状态会立即影响监听。

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_waits_current';

sleep 100秒

select sleep(100);select * from events_waits_current;

TIMER_WAIT字段即表示该事件的时间开销，单位是皮秒，在实际的应用场景中：我们可以利用该字段信息进行倒序排序，以便找出时间开销最大的等待事件。

字段说明：

• THREAD_ID，EVENT_ID：与事件关联的线程ID和当前事件ID。THREAD_ID和EVENT_ID值构成了该事件信息行的唯一标识（不会有重复的THREAD_ID+EVENT_ID值）；

• END_EVENT_ID：当一个事件正在执行时该列值为NULL，当一个事件执行结束时把该事件的ID更新到该列；

• EVENT_NAME：产生事件的instruments名称。该名称来自setup_instruments表的NAME字段值；

• SOURCE：产生该事件的instruments所在的源文件名称以及检测到该事件发生点的代码行号。您可以查看源代码来确定涉及的代码。例如，如果互斥锁、锁被阻塞，您可以检查发生这种情况的上下文环境；

• TIMER_START，TIMER_END，TIMER_WAIT：事件的时间信息。单位皮秒（万亿分之一秒）。 TIMER_START和TIMER_END值表示事件开始和结束时间。 TIMER_WAIT是事件经过时间（即事件执行了多长时间）；

  • 如果事件未执行完成，则TIMER_END为当前计时器时间值（当前时间），TIMER_WAIT为目前为止所经过的时间（TIMER_END - TIMER_START）

  • 如果采集该事件的instruments配置项TIMED = NO，则不会收集事件的时间信息，TIMER_START，TIMER_END和TIMER_WAIT在这种情况下均记录为NULL；

• SPINS：对于互斥量和自旋次数。如果该列值为NULL，则表示代码中没有使用自旋或者说自旋没有被监控起来；

• OBJECT_SCHEMA，OBJECT_NAME，OBJECT_TYPE，OBJECT_INSTANCE_BEGIN：这些列标识了一个正在被执行的对象，这些列记录的信息含义需要看对象是什么类型。

• INDEX_NAME：表示使用的索引的名称。PRIMARY表示使用到了主键。 NULL表示没有使用索引

• NESTING_EVENT_ID：表示该行信息中的EVENT_ID事件是嵌套在哪个事件中，即父事件的EVENT_ID；

• NESTING_EVENT_TYPE：表示该行信息中的EVENT_ID事件嵌套的事件类型。有效值有：TRANSACTION，STATEMENT，STAGE或WAIT，即父事件的事件类型，如果为TRANSACTION则需要到事务事件表中找对应NESTING_EVENT_ID值的事件，其他类型同理

• OPERATION：执行的操作类型，如：lock、read、write、timed_wait；

• NUMBER_OF_BYTES：操作读取或写入的字节数或行数。对于文件IO等待，该列值表示字节数；对于表I/O等待（wait/io/table/sql/handler instruments的事件），该列值表示行数。如果值大于1，则表示该事件对应一个批量I/O操作。

• FLAGS：留作将来使用

• PS：events_waits_current表允许使用TRUNCATE TABLE语句

events_waits_history 表

events_waits_history表包含每个线程最近的N个等待事件。 在server启动时，N的值会自动调整。 如果要显式设置这个N大小，可以在server启动之前调整系统参数performance_schema_events_waits_history_size的值。 等待事件需要执行结束时才被添加到events_waits_history表中（没有结束时保存在events_waits_current表）。当添加新事件到events_waits_history表时，如果该表已满，则会丢弃每个线程较旧的事件

events_waits_history与events_waits_current表定义相同

PS：允许执行TRUNCATE TABLE语句

events_waits_history_long 表

events_waits_history_long表包含最近的N个等待事件（所有线程的事件）。在server启动时，N的值会自动调整。 如果要显式设置这个N大小，可以在server启动之前调整系统参数

performance_schema_events_waits_history_long_size的值。等待事件需要执行结束时才会被添加到events_waits_history_long表中（没有结束时保存在events_waits_current表），当添加新事件到events_waits_history_long表时，如果该表已满，则会丢弃该表中较旧的事件。

events_waits_history_long与events_waits_current表结构相同

PS：允许使用TRUNCATE TABLE语句

语句事件表

语句事件记录表与等待事件记录表一样，也有三张表，这些表记录了当前与最近在MySQL实例中发生了哪些语句事件，时间消耗是多少。记录了各种各样的语句执行产生的语句事件信息。
要注意：语句事件相关配置中，setup_instruments表中statement/*开头的所有instruments配置默认开启，setup_consumers表中statements相关的consumers配置默认开启了events_statements_current、events_statements_history、statements_digest（对应events_statements_summary_by_digest表，详见后续章节）但没有开启events_statements_history_long。

events_statements_current 表

events_statements_current表包含当前语句事件，每个线程只显示一行最近被监视语句事件的当前状态。

在包含语句事件行的表中，events_statements_current当前事件表是基础表。其他包含语句事件表中的数据在逻辑上来源于当前事件表（汇总表除外）。例如：events_statements_history和events_statements_history_long表是最近的语句事件历史的集合，events_statements_history表中每个线程默认保留10行事件历史信息，events_statements_history_long表中默认所有线程保留10000行事件历史信息

实战试试：

SELECT * FROM performance_schema.setup_consumers;

默认events_statements_current是开启的
sleep 100秒

select sleep(100);select * from events_statements_current;

字段说明：

• THREAD_ID，EVENT_ID：与事件关联的线程号和事件启动时的事件编号，可以使用THREAD_ID和EVENT_ID列值来唯一标识该行，这两行的值作为组合条件时不会出现相同的数据行

• END_EVENT_ID：当一个事件开始执行时，对应行记录的该列值被设置为NULL，当一个事件执行结束时，对应的行记录的该列值被更新为该事件的ID

• EVENT_NAME：产生事件的监视仪器的名称。该列值来自setup_instruments表的NAME值。对于SQL语句，EVENT_NAME值最初的instruments是statement/com/Query，直到语句被解析之后，会更改为更合适的具体instruments名称，如：statement/sql/insert

• SOURCE：源文件的名称及其用于检测该事件的代码位于源文件中的行号，您可以检查源代码来确定涉及的代码

• TIMER_START，TIMER_END，TIMER_WAIT：事件的时间信息。这些值的单位是皮秒（万亿分之一秒）。 TIMER_START和TIMER_END值表示事件的开始时间和结束时间。TIMER_WAIT是事件执行消耗的时间（持续时间）

  • 如果事件未执行完成，则TIMER_END为当前时间，TIMER_WAIT为当前为止所经过的时间（TIMER_END - TIMER_START）。

  • 如果监视仪器配置表setup_instruments中对应的监视器TIMED字段被设置为 NO，则不会收集该监视器的时间信息，那么对于该事件采集的信息记录中，TIMER_START，TIMER_END和TIMER_WAIT字段值均为NULL

• LOCK_TIME：等待表锁的时间。该值以微秒进行计算，但最终转换为皮秒显示，以便更容易与其他performance_schema中的计时器进行比较

• SQL_TEXT：SQL语句的文本。如果该行事件是与SQL语句无关的command事件，则该列值为NULL。默认情况下，语句最大显示长度为1024字节。如果要修改，则在server启动之前设置系统变量performance_schema_max_sql_text_length的值

• DIGEST：语句摘要的MD5 hash值，为32位十六进制字符串，如果在setup_consumers表中statement_digest配置行没有开启，则语句事件中该列值为NULL

• DIGEST_TEXT：标准化转换过的语句摘要文本，如果setup_consumers表中statements_digest配置行没有开启，则语句事件中该列值为NULL。performance_schema_max_digest_length系统变量决定着在存入该表时的最大摘要语句文本的字节长度（默认为1024字节），要注意：用于计算摘要语句文本的原始语句文本字节长度由系统变量max_digest_length控制，而存入表中的字节长度由系统变量performance_schema_max_digest_length控制，所以，如果performance_schema_max_digest_length小于max_digest_length时，计算出的摘要语句文本如果大于了performance_schema_max_digest_length定义的长度会被截断

• CURRENT_SCHEMA：语句使用的默认数据库（使用use db_name语句即可指定默认数据库），如果没有则为NULL

• OBJECT_SCHEMA，OBJECT_NAME，OBJECT_TYPE：对于嵌套语句（存储程序最终是通过语句调用的，所以如果一个语句是由存储程序调用的，虽然说这个语句事件是嵌套在存储程序中的，但是实际上对于事件类型来讲，仍然是嵌套在语句事件中），这些列包含有关父语句的信息。如果不是嵌套语句或者是父语句本身产生的事件，则这些列值为NULL

• OBJECT_INSTANCE_BEGIN：语句的唯一标识，该列值是内存中对象的地址

• MYSQL_ERRNO：语句执行的错误号，此值来自代码区域的语句诊断区域

• RETURNED_SQLSTATE：语句执行的SQLSTATE值，此值来自代码区域的语句诊断区域

• MESSAGE_TEXT：语句执行的具体错误信息，此值来自代码区域的语句诊断区域

• ERRORS：语句执行是否发生错误。如果SQLSTATE值以00（完成）或01（警告）开始，则该列值为0。其他任何SQLSTATE值时，该列值为1

• WARNINGS：语句警告数，此值来自代码区域的语句诊断区域

• ROWS_AFFECTED：受该语句影响的行数。有关“受影响”的含义的描述，参见连接：

• https://dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html

  • 使用mysql_query（）或mysql_real_query（）函数执行语句后，可能会立即调用mysql_affected_rows（）函数。如果是UPDATE，DELETE或INSERT，则返回最后一条语句更改、删除、插入的行数。对于SELECT语句，mysql_affected_rows（）的工作方式与mysql_num_rows（）一样（在执行结果最后返回的信息中看不到effected统计信息）

  • 对于UPDATE语句，受影响的行值默认为实际更改的行数。如果在连接到mysqld时指定了CLIENT_FOUND_ROWS标志给mysql_real_connect（）函数，那么affected-rows的值是“found”的行数。即WHERE子句匹配到的行数

  • 对于REPLACE语句，如果发生新旧行替换操作，则受影响的行值为2，因为在这种情况下，实际上是先删除旧值，后插入新值两个行操作

  • 对于INSERT … ON DUPLICATE KEY UPDATE语句，如果行作为新行插入，则每行的affected计数为1，如果发生旧行更新为新行则每行affected计数为2，如果没有发生任何插入和更新，则每行的affected计数为0 （但如果指定了CLIENT_FOUND_ROWS标志，则没有发生任何的插入和更新时，即set值就为当前的值时，每行的受影响行值计数为1而不是0）

  • 在存储过程的CALL语句调用之后，mysql_affected_rows（）返回的影响行数是存储程序中的最后一个语句执行的影响行数值，如果该语句返回-1，则存储程序最终返回0受影响。所以在存储程序执行时返回的影响行数并不可靠，但是你可以自行在存储程序中实现一个计数器变量在SQL级别使用ROW_COUNT（）来获取各个语句的受影响的行值并相加，最终通过存储程序返回这个变量值。

• ROWS_SENT：语句返回给客户端的数据行数

• ROWS_EXAMINED：在执行语句期间从存储引擎读取的数据行数

• CREATED_TMP_DISK_TABLES：像Created_tmp_disk_tables状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• CREATED_TMP_TABLES：像Created_tmp_tables状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• SELECT_FULL_JOIN：像Select_full_join状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• SELECT_FULL_RANGE_JOIN：像Select_full_range_join状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• SELECT_RANGE：就像Select_range状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• SELECT_RANGE_CHECK：像Select_range_check状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• SELECT_SCAN：像Select_scan状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• SORT_MERGE_PASSES：像Sort_merge_passes状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• SORT_RANGE：像Sort_range状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• SORT_ROWS：像Sort_rows状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• SORT_SCAN：像Sort_scan状态变量一样的计数值，但是这里只用于这个事件中的语句统计而不针对全局、会话级别

• NO_INDEX_USED：如果语句执行表扫描而不使用索引，则该列值为1，否则为0

• NO_GOOD_INDEX_USED：如果服务器找不到用于该语句的合适索引，则该列值为1，否则为0

• NESTING_EVENT_ID，NESTING_EVENT_TYPE，NESTING_EVENT_LEVEL：这三列与其他列结合一起使用，为顶级（未知抽象的语句或者说是父语句）语句和嵌套语句（在存储的程序中执行的语句）提供以下事件信息

  • 对于顶级语句：
    OBJECT_TYPE = NULL,OBJECT_SCHEMA = NULL,OBJECT_NAME = NULL,NESTING_EVENT_ID = NULL,NESTING_EVENT_TYPE = NULL,NESTING_LEVEL = 0

  • 对于嵌套语句：OBJECT_TYPE =父语句对象类型,OBJECT_SCHEMA =父语句数据库级名称,OBJECT_NAME =父语句表级对象名称,NESTING_EVENT_ID =父语句EVENT_ID,NESTING_EVENT_TYPE =‘STATEMENT’,NESTING_LEVEL =父语句NESTING_LEVEL加一，例如：1，表示父语句的下一层嵌套语句

• PS:允许使用TRUNCATE TABLE语句

events_statements_history 表

events_statements_history表包含每个线程最新的N个语句事件。 在server启动时，N的值会自动调整。 要显式设置N的大小，可以在server启动之前设置系统变量performance_schema_events_statements_history_size的值。 statement事件执行完成时才会添加到该表中。 当添加新事件到该表时，如果对应线程的事件在该表中的配额已满，则会丢弃对应线程的较旧的事件

events_statements_history与events_statements_current表结构相同

PS：允许使用TRUNCATE TABLE语句

events_statements_history_long 表

events_statements_history_long表包含最近的N个语句事件。在server启动时，N的值会自动调整。 要显式设置N的大小，可以在server启动之前设置系统变量performance_schema_events_statements_history_long_size的值。 statement事件需要执行结束时才会添加到该表中。 当添加新事件到该表时，如果该表的全局配额已满，则会丢弃该表中较旧的事件

events_statements_history_long与events_statements_current表结构相同

PS：允许使用TRUNCATE TABLE语句

事务事件表

事务事件记录表与等待事件记录表一样，也有三张表，这些表记录了当前与最近在MySQL实例中发生了哪些事务事件，时间消耗是多少

要注意：事务事件相关配置中，setup_instruments表中只有一个名为transaction的instrument，默认关闭，setup_consumers表中transactions相关的consumers配置默认关闭了

events_transactions_current 表

events_transactions_current表包含当前事务事件信息，每个线程只保留一行最近事务的事务事件

在包含事务事件信息的表中，events_transactions_current是基础表。其他包含事务事件信息的表中的数据逻辑上来源于当前事件表。例如：events_transactions_history和events_transactions_history_long表分别包含每个线程最近10行事务事件信息和全局最近10000行事务事件信息

实战试试：

SELECT * FROM performance_schema.setup_consumers;

select * from events_transactions_current;

字段说明：

• THREAD_ID，EVENT_ID：与事件关联的线程号和事件启动时的事件编号，可以使用THREAD_ID和EVENT_ID列值来唯一标识该行，这两行的值作为组合条件时不会出现相同的数据行

• END_EVENT_ID：当一个事件开始执行时，对应行记录的该列值被设置为NULL，当一个事件执行结束时，对应的行记录的该列值被更新为该事件的ID

• EVENT_NAME：收集该事务事件的instruments的名称。来自setup_instruments表的NAME列值

• STATE：当前事务状态。有效值为：ACTIVE（执行了START TRANSACTION或BEGIN语句之后，事务未提交或未回滚之前）、COMMITTED（执行了COMMIT之后）、ROLLED BACK（执行了ROLLBACK语句之后）

• TRX_ID：未使用，字段值总是为NULL

• GTID：包含gtid_next系统变量的值，其值可能是格式为：UUID:NUMBER的GTID，也可能是：ANONYMOUS、AUTOMATIC。对于AUTOMATIC列值的事务事件，GTID列在事务提交和对应事务的GTID实际分配时都会进行更改(如果gtid_mode系统变量为ON或ON_PERMISSIVE，则GTID列将更改为事务的GTID，如果gtid_mode为OFF或OFF_PERMISSIVE，则GTID列将更改为ANONYMOUS）

• XID_FORMAT_ID，XID_GTRID和XID_BQUAL：XA事务标识符的组件。关于XA事务语法详见链接：

• https://dev.mysql.com/doc/refman/5.7/en/xa-statements.html

• XA_STATE：XA事务的状态。有效值为：ACTIVE（执行了XA START之后，未执行其他后续XA语句之前）、IDLE（执行了XA END语句之后，未执行其他后续XA语句之前）、PREPARED（执行了XA PREPARE语句之后，未执行其他后续XA语句之前）、ROLLED BACK（执行了XA ROLLBACK语句之后，未执行其他后续XA语句之前）、COMMITTED（执行了XA COMMIT语句之后）

• SOURCE：源文件的名称及其用于检测该事件的代码位于源文件中的行号，您可以检查源代码来确定涉及的代码

• TIMER_START，TIMER_END，TIMER_WAIT：事件的时间信息。这些值的单位是皮秒（万亿分之一秒）。 TIMER_START和TIMER_END值表示事件的开始时间和结束时间。TIMER_WAIT是事件执行消耗的时间（持续时间）
  如果事件未执行完成，则TIMER_END为当前时间，TIMER_WAIT为当前为止所经过的时间（TIMER_END - TIMER_START）

• 如果监视仪器配置表setup_instruments中对应的监视器TIMED字段被设置为 NO，则不会收集该监视器的时间信息，那么对于该事件采集的信息记录中，TIMER_START，TIMER_END和TIMER_WAIT字段值均为NULL

• ACCESS_MODE：事务访问模式。有效值为：READ ONLY或READ WRITE

• ISOLATION_LEVEL：事务隔离级别。有效值为：REPEATABLE READ、READ COMMITTED、READ UNCOMMITTED、SERIALIZABLE

• AUTOCOMMIT：在事务开始时是否启用了自动提交模式，如果启用则为YES，没有启用则为NO

• NUMBER_OF_SAVEPOINTS，NUMBER_OF_ROLLBACK_TO_SAVEPOINT，NUMBER_OF_RELEASE_SAVEPOINT：在事务内执行的SAVEPOINT，ROLLBACK TO SAVEPOINT和RELEASE SAVEPOINT语句的数量

• OBJECT_INSTANCE_BEGIN：未使用，字段值总是为NULL

• NESTING_EVENT_ID：嵌套事务事件的父事件EVENT_ID值

• NESTING_EVENT_TYPE：嵌套事件类型。有效值为：TRANSACTION、STATEMENT、STAGE、WAIT （由于事务无法嵌套，因此该列值不会出现TRANSACTION）

• PS:允许使用TRUNCATE TABLE语句

events_transactions_history 表

• events_transactions_history表包含每个线程最近的N个事务事件。 在server启动时，N的值会自动调整。 要显式设置N的大小，可以在server启动之前设置系统变量

• performance_schema_events_transactions_history_size的值。事务事件未执行完成之前不会添加到该表中。当有新的事务事件添加到该表时，如果该表已满，则会丢弃对应线程较旧的事务事件

• events_transactions_history与events_transactions_current表结构相同

PS：允许使用TRUNCATE TABLE语句

events_transactions_history_long 表

events_transactions_history_long表包含全局最近的N个事务事件。在server启动时，N的值会自动调整。 要显式设置N的大小，可以在server启动之前设置系统变量

performance_schema_events_transactions_history_long_size的值。事务事件在执行完之前不会添加到该表中。当添加新事务事件时，如果该表已满，则会丢弃较旧的事件

events_transactions_history_long与events_transactions_current表结构相同

PS：允许使用TRUNCATE TABLE语句

三、总结

本文讲解了performance_schema中三个类型的事件原始记录表：等待事件表、语句事件表和事务事件表。分别对其进行实战演示和字段说明。
本文内容有1万多字，内容非常多，建议阅读方式：记住标题和小模块的介绍 ，内容详解可帮助理解，看不懂也没事，后续章节的实战中也可慢慢体会。
本文适用于收藏，需要查阅指定配置时，可快速找到配置的详解。

四、参考

事件记录 | performance_schema全方位介绍
官方mysql_affected_rows详解
官方xa-statements事务语法详解

我是虚竹哥，我们明天见~