performance_schema 笔记实际应用（二）—— 查看最近执行SQL，执行阶段、进度，最近事务执行信息

原创

Hehuyi_In_666 2023-04-18 13:14:58 博主文章分类：性能 ©著作权

文章标签 SQL sql st表 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者Hehuyi_In_666的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、查看最近的SQL执行信息

1. 查看top sql

按照通常优化慢SQL的原则，优先优化执行次数最多的，然后是执行时间最长的，可以使用events_statements_summary_by_digest表来查询统计之后的top sql。

注意 events_statements_summary_by_digest 表中记录的SQL语句文本并不完整，默认情况下只截取了1024个字节，且也是使用这1024个字节的SQL文本进行hash计算，把hashcode相同的累计计算在一起，performance_schema提供的数据只能算作慢日志分析的一个补充，如果需要完整的SQL语句文本还得依赖慢查询日志分析。

select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,sys.format_time(SUM_TIMER_WAIT) as sum_time,sys.format_time(MIN_TIMER_WAIT) as min_time,sys.format_time(AVG_TIMER_WAIT) as avg_time,sys.format_time(MAX_TIMER_WAIT) as max_time,sys.format_time(SUM_LOCK_TIME) as sum_lock_time,SUM_ROWS_AFFECTED,SUM_ROWS_SENT,SUM_ROWS_EXAMINED from events_statements_summary_by_digest where SCHEMA_NAME is not null order by COUNT_STAR desc limit 10\G;

*************************** 1. row ***************************
  SCHEMA_NAME: sbtest
  DIGEST_TEXT: UPDATE `sbtest1` SET `pad` = ? WHERE `id` = ? 
   COUNT_STAR: 10
     sum_time: 2.19 h
     min_time: 216.90 us
     avg_time: 13.15 m
     max_time: 1.50 h
sum_lock_time: 2.04 h
SUM_ROWS_AFFECTED: 3
SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 4
*************************** 2. row ***************************
  SCHEMA_NAME: sbtest
  DIGEST_TEXT: SHOW WARNINGS 
   COUNT_STAR: 9
     sum_time: 397.62 us
     min_time: 16.50 us
     avg_time: 44.18 us
     max_time: 122.58 us
sum_lock_time: 0 ps
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
......
*************************** 5. row ***************************
  SCHEMA_NAME: sbtest
  DIGEST_TEXT: SELECT * FROM `sbtest1` LIMIT ? 
   COUNT_STAR: 5
     sum_time: 138.93 ms
     min_time: 145.77 us
     avg_time: 27.79 ms
     max_time: 112.29 ms
sum_lock_time: 95.53 ms
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 104
SUM_ROWS_EXAMINED: 104
......
10 rows in set (0.00 sec)

2. 查看最近执行失败的SQL

曾经有同事问，代码对数据库的某些操作(比如：python的ORM模块操作数据库)报了语法错误，但是代码并没有记录SQL语句文本的功能，问在数据库层能否查看到具体的SQL文本，看看是否有哪里写错了。这个时候，大多数人首先想到的就是去查看错误日志。很遗憾，对于SQL语句的语法错误，错误日志并不会记录。如果你没有完全了解performance_schema，那么你很可能就会给同事回复说：MySQL层面也并没有记录语法错误的信息。

实际上，performance_schema的语句事件记录表中针对每一个语句的执行状态都记录了较为详细的信息，例如：events_statements_表和events_statements_summary_by_digest表（events_statements_表记录的语句所有的执行错误信息，但events_statements_summary_by_digest表只记录了语句在执行过程中发生的错误的语句记录统计，具体的错误类型不记录）。

下面分别演示如何使用这两个表查询语句发生错误的语句信息

首先，我们模拟一个语法错误的SQL，开启会话1：

root@localhost : performance_schema 05:18:09> select * from;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

开启另一个会话（会话2），查询events_statements_history_long表中错误号为1064的记录

use performance_schema
select THREAD_ID,EVENT_NAME,SOURCE,sys.format_time(TIMER_WAIT) as exec_time,sys.format_time(LOCK_TIME) as lock_time,SQL_TEXT,CURRENT_SCHEMA,MESSAGE_TEXT,ROWS_AFFECTED,ROWS_SENT,ROWS_EXAMINED,MYSQL_ERRNO from events_statements_history where MYSQL_ERRNO=1064\G;

*************************** 1. row ***************************
 THREAD_ID: 119
EVENT_NAME: statement/sql/error
    SOURCE: socket_connection.cc:101
 exec_time: 71.72 us
 lock_time: 0 ps
  SQL_TEXT: select * from
CURRENT_SCHEMA: sbtest
MESSAGE_TEXT: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
MYSQL_ERRNO: 1064
1 row in set (0.01 sec)

如果你不知道错误号是多少，可以查询发生错误次数不为0的语句记录，在里边找到MESSAGE_TEXT字段提示信息为语法错误的就是它了

select THREAD_ID,EVENT_NAME,SOURCE,sys.format_time(TIMER_WAIT) as exec_time,sys.format_time(LOCK_TIME) as lock_time,SQL_TEXT,CURRENT_SCHEMA,MESSAGE_TEXT,ROWS_AFFECTED,ROWS_SENT,ROWS_EXAMINED,MYSQL_ERRNO,errors from events_statements_history where errors>0\G;

*************************** 1. row ***************************
 THREAD_ID: 119
EVENT_NAME: statement/sql/error
    SOURCE: socket_connection.cc:101
 exec_time: 71.72 us
 lock_time: 0 ps
  SQL_TEXT: select * from
CURRENT_SCHEMA: sbtest
MESSAGE_TEXT: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
MYSQL_ERRNO: 1064
    errors: 1
1 row in set (0.00 sec)

下面试试events_statements_summary_by_digest表查询发生语句执行错误的SQL语句记录。

首先还是执行错误的SQL，开启会话1：

root@localhost : sbtest 05:32:34> select * ;
ERROR 1096 (HY000): No tables used
root@localhost : sbtest 05:40:57> select * from sbtest4 where id between 100 and 2000 and xx=1;
ERROR 1054 (42S22): Unknown column 'xx' in 'where clause'

然后在events_statements_summary_by_digest表中查询发生错误次数大于0的记录，在会话2执行

select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,sys.format_time(AVG_TIMER_WAIT) as avg_time,sys.format_time(MAX_TIMER_WAIT) as max_time,sys.format_time(SUM_LOCK_TIME) as sum_lock_time,SUM_ERRORS,FIRST_SEEN,LAST_SEEN from events_statements_summary_by_digest where SUM_ERRORS!=0\G;
......
*************************** 10. row ***************************
SCHEMA_NAME: sbtest
DIGEST_TEXT: SELECT *   # 这里就是第一个执行错误的语句
COUNT_STAR: 1
 avg_time: 55.14 us
 max_time: 55.14 us
sum_lock_time: 0 ps
SUM_ERRORS: 1
FIRST_SEEN: 2018-06-25 17:40:57
LAST_SEEN: 2018-06-25 17:40:57
*************************** 11. row ***************************
SCHEMA_NAME: sbtest
DIGEST_TEXT: SELECT * FROM `sbtest4` WHERE `id` BETWEEN ? AND ? AND `xx` = ?   # 这里就是第二个执行错误的语句
COUNT_STAR: 1
 avg_time: 101.68 us
 max_time: 101.68 us
sum_lock_time: 0 ps
SUM_ERRORS: 1
FIRST_SEEN: 2018-06-25 17:41:03
LAST_SEEN: 2018-06-25 17:41:03
11 rows in set (0.00 sec)

events_statements_summary_by_digest表中不记录具体的错误信息，只做错误语句统计。如果需要查询到具体的错误信息（具体的错误代码，错误提示信息等），还需要查询events_statements_history或events_statements_history_long表。

select THREAD_ID,EVENT_NAME,SOURCE,sys.format_time(TIMER_WAIT) as exec_time,sys.format_time(LOCK_TIME) as lock_time,SQL_TEXT,CURRENT_SCHEMA,MESSAGE_TEXT,ROWS_AFFECTED,ROWS_SENT,ROWS_EXAMINED,MYSQL_ERRNO from events_statements_history where MYSQL_ERRNO!=0\G;

......
*************************** 2. row ***************************
 THREAD_ID: 119
EVENT_NAME: statement/sql/select
    SOURCE: socket_connection.cc:101
 exec_time: 55.14 us
 lock_time: 0 ps
  SQL_TEXT: select *
CURRENT_SCHEMA: sbtest
MESSAGE_TEXT: No tables used
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
MYSQL_ERRNO: 1096
*************************** 3. row ***************************
 THREAD_ID: 119
EVENT_NAME: statement/sql/select
    SOURCE: socket_connection.cc:101
 exec_time: 101.68 us
 lock_time: 0 ps
  SQL_TEXT: select * from sbtest4 where id between 100 and 2000 and xx=1
CURRENT_SCHEMA: sbtest
MESSAGE_TEXT: Unknown column 'xx' in 'where clause'
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
MYSQL_ERRNO: 1054
3 rows in set (0.00 sec)

二、查看SQL执行进度信息

MariaDB支持不依赖于performance_schema性能数据的进度展示功能，show processlist语句返回的最后一列就是进度信息

root@localhost Sun Jan 14 14:08:29 2018 14:08:29 [(none)]>show processlist;
+----+------+-----------+-----------+---------+------+----------------+-------------------------------------------------+----------+
| Id | User | Host      | db        | Command | Time | State          | Info                                            | Progress |
+----+------+-----------+-----------+---------+------+----------------+-------------------------------------------------+----------+
|  4 | root | localhost | employees | Query  |    6 | altering table | alter table salaries add index i_salary(salary) |  93.939 |
|  5 | root | localhost | NULL      | Query  |    0 | init          | show processlist                                |    0.000 |
+----+------+-----------+-----------+---------+------+----------------+-------------------------------------------------+----------+
2 rows in set (0.00 sec)

MySQL中也提供了类似的功能，通过阶段事件中具有可预估工作量的阶段事件进行记录与计算，就可以得到一个语句的执行的阶段信息和进度信息，下面我们分别举例介绍如何查看。

1. 查看SQL执行阶段

首先需要启用配置，阶段事件默认并未启用，开启一个会话（会话1）

use performance_schema
Database changed

update setup_instruments set enabled='yes',timed='yes' where name like 'stage/%';
Query OK, 120 rows affected (0.00 sec)
Rows matched: 129 Changed: 120 Warnings: 0

update setup_consumers set enabled='yes' where name like '%stage%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

开启第二个会话（会话2），先查询thread_id

select sys.ps_thread_id(connection_id());
+-----------------------------------+
| sys.ps_thread_id(connection_id()) |
+-----------------------------------+
| 119 |
+-----------------------------------+
1 row in set (0.00 sec)

对旧信息进行清理，避免干扰（会话1）

-- 先关闭其他线程的事件记录功能，使用前面步骤查询到的thread_id
 update performance_schema.threads set INSTRUMENTED='NO' where THREAD_ID!=119;
Query OK, 101 rows affected (0.00 sec)
Rows matched: 101 Changed: 101 Warnings: 0

-- 清空阶段事件的3张表
truncate events_stages_current;truncate events_stages_history;truncate events_stages_history_long;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

现在，回到会话2执行select语句

select count(*) from sbtest.sbtest4 where id between 100 and 200;
+----------+
| count(*) |
+----------+
| 50 |
+----------+
1 row in set (0.00 sec)

在会话1中查询events_stages_history_long表

select THREAD_ID,EVENT_NAME,SOURCE,sys.format_time(TIMER_WAIT) as exec_time,WORK_COMPLETED,WORK_ESTIMATED from events_stages_history_long where THREAD_ID=119;

+-----------+--------------------------------+--------------------------+-----------+----------------+----------------+
| THREAD_ID | EVENT_NAME | SOURCE | exec_time | WORK_COMPLETED | WORK_ESTIMATED |
+-----------+--------------------------------+--------------------------+-----------+----------------+----------------+
| 119 | stage/sql/starting | socket_connection.cc:107 | 54.19 us | NULL | NULL |
| 119 | stage/sql/checking permissions | sql_authorization.cc:810 | 3.62 us | NULL | NULL |
| 119 | stage/sql/Opening tables | sql_base.cc:5650 | 10.54 us | NULL | NULL |
| 119 | stage/sql/init | sql_select.cc:121 | 16.73 us | NULL | NULL |
| 119 | stage/sql/System lock | lock.cc:323 | 4.77 us | NULL | NULL |
| 119 | stage/sql/optimizing | sql_optimizer.cc:151 | 4.78 us | NULL | NULL |
| 119 | stage/sql/statistics | sql_optimizer.cc:367 | 50.54 us | NULL | NULL |
| 119 | stage/sql/preparing | sql_optimizer.cc:475 | 7.79 us | NULL | NULL |
| 119 | stage/sql/executing | sql_executor.cc:119 | 381.00 ns | NULL | NULL |
| 119 | stage/sql/Sending data | sql_executor.cc:195 | 36.75 us | NULL | NULL |
| 119 | stage/sql/end | sql_select.cc:199 | 931.00 ns | NULL | NULL |
| 119 | stage/sql/query end | sql_parse.cc:4968 | 5.31 us | NULL | NULL |
| 119 | stage/sql/closing tables | sql_parse.cc:5020 | 2.26 us | NULL | NULL |
| 119 | stage/sql/freeing items | sql_parse.cc:5596 | 8.71 us | NULL | NULL |
| 119 | stage/sql/cleaning up | sql_parse.cc:1902 | 449.00 ns | NULL | NULL |
+-----------+--------------------------------+--------------------------+-----------+----------------+----------------+
15 rows in set (0.01 sec)

通过以上的查询数据可以清晰地看到一个select语句的执行全过程，以及每个过程的时间开销等信息。

那DDL语句的执行阶段又是怎样的呢？

先对之前旧的信息进行清理，避免干扰（会话1）

truncate events_stages_current;truncate events_stages_history;truncate events_stages_history_long;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

然后，执行DDL语句(会话2)

alter table sbtest1 add index i_c(c);

此时，在会话1中查询阶段事件信息（DDL语句并未执行完成，从最后一行记录信息中可以看到，WORK_COMPLETED 和WORK_ESTIMATED 列值不为NULL，表示该阶段事件是一个可以度量的事件）。

select THREAD_ID,EVENT_NAME,SOURCE,sys.format_time(TIMER_WAIT) as exec_time,WORK_COMPLETED,WORK_ESTIMATED from events_stages_history_long;
+-----------+------------------------------------------------------+--------------------------+-----------+----------------+----------------+
| THREAD_ID | EVENT_NAME | SOURCE | exec_time | WORK_COMPLETED | WORK_ESTIMATED |
+-----------+------------------------------------------------------+--------------------------+-----------+----------------+----------------+
| 119 | stage/sql/starting | socket_connection.cc:107 | 44.17 us | NULL | NULL |
| 119 | stage/sql/checking permissions | sql_authorization.cc:810 | 1.46 us | NULL | NULL |
| 119 | stage/sql/checking permissions | sql_authorization.cc:810 | 2.29 us | NULL | NULL |
| 119 | stage/sql/init | sql_table.cc:9031 | 2.16 us | NULL | NULL |
| 119 | stage/sql/Opening tables | sql_base.cc:5650 | 107.57 us | NULL | NULL |
| 119 | stage/sql/setup | sql_table.cc:9271 | 19.19 us | NULL | NULL |
| 119 | stage/sql/creating table | sql_table.cc:5222 | 1.06 ms | NULL | NULL |
| 119 | stage/sql/After create | sql_table.cc:5355 | 76.22 us | NULL | NULL |
| 119 | stage/sql/System lock | lock.cc:323 | 4.38 us | NULL | NULL |
| 119 | stage/sql/preparing for alter table | sql_table.cc:7454 | 28.63 ms | NULL | NULL |
| 119 | stage/sql/altering table | sql_table.cc:7508 | 3.91 us | NULL | NULL |
| 119 | stage/innodb/alter table (read PK and internal sort) | ut0stage.h:241 | 27.09 s | 230040 | 470155 |
+-----------+------------------------------------------------------+--------------------------+-----------+----------------+----------------+
12 rows in set (0.01 sec)

DDL语句执行完成之后，再次查看阶段事件信息（会话1）

select THREAD_ID,EVENT_NAME,SOURCE,sys.format_time(TIMER_WAIT) as exec_time,WORK_COMPLETED,WORK_ESTIMATED from events_stages_history_long;
+-----------+------------------------------------------------------+--------------------------+-----------+----------------+----------------+
| THREAD_ID | EVENT_NAME | SOURCE | exec_time | WORK_COMPLETED | WORK_ESTIMATED |
+-----------+------------------------------------------------------+--------------------------+-----------+----------------+----------------+
......
| 119 | stage/innodb/alter table (read PK and internal sort) | ut0stage.h:241 | 27.09 s | 230040 | 470155 |
| 119 | stage/innodb/alter table (merge sort) | ut0stage.h:501 | 1.15 m | 345060 | 512319 |
| 119 | stage/innodb/alter table (insert) | ut0stage.h:501 | 11.83 s | 460146 | 523733 |
| 119 | stage/innodb/alter table (flush) | ut0stage.h:501 | 18.35 s | 523658 | 523733 |
| 119 | stage/innodb/alter table (log apply index) | ut0stage.h:501 | 54.63 ms | 524042 | 524042 |
| 119 | stage/innodb/alter table (flush) | ut0stage.h:501 | 21.18 us | 524042 | 524042 |
| 119 | stage/sql/committing alter table to storage engine | sql_table.cc:7535 | 5.12 us | NULL | NULL |
| 119 | stage/innodb/alter table (end) | ut0stage.h:501 | 233.52 ms | 524042 | 524042 |
......
+-----------+------------------------------------------------------+--------------------------+-----------+----------------+----------------+
24 rows in set (0.01 sec)

通过以上的查询可以清晰地看到一个alter语句添加索引的执行全过程，以及每一个过程的时间开销等信息。执行时间最长的是stage/innodb/alter table (merge sort)，其次是stage/innodb/alter table (read PK and internal sort)，说明在本示例中创建索引主要的时间开销在于内部的数据排序和排序合并操作。

阶段事件长历史记录表中的数据产生较快，默认的10000行配额可能很快就被打满了，可在配置文件中把配额调整为一个较大值，以便完整查看DDL语句执行阶段，例如：performance_schema_events_stages_history_long_size=1000000，同时要注意关掉其他不相干的任务。

2. 查看SQL执行进度

在官方MySQL 版本中，performance_schema下并没有很直观地查询整个语句执行进度的方法，但是可以借助后续章节中介绍的sys.session视图进行查看

select * from sys.session where conn_id!=connection_id()\G;
*************************** 1. row ***************************
            thd_id: 45
          conn_id: 4
......
            state: alter table (merge sort)
              time: 30
current_statement: alter table sbtest1 add index i_c(c)
statement_latency: 29.42 s
          progress: 46.40   -- 进度百分比
      lock_latency: 2.19 ms
    rows_examined: 0
        rows_sent: 0
    rows_affected: 0
        tmp_tables: 0
  tmp_disk_tables: 0
        full_scan: NO
......
      program_name: mysql
1 row in set (0.33 sec)

三、查看最近事务执行信息

虽然可以通过慢查询日志查询到一个语句的执行总时长，但如果数据库中存在着一些大事务执行过程中回滚了，或者说执行过程中异常终止，慢查询日志就爱莫能助了。这时可以借助performance_schema的events_transactions_*表进行查看事务相关的记录，这些表中详细记录了是否有事务被回滚、活跃或已提交等。

下面我们分别模拟几种事务情况，并查看事务事件记录表。

首先，我们需要进行配置启用，事务事件默认并未启用（会话1）

update setup_instruments set enabled='yes',timed='yes' where name like 'transaction';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

update setup_consumers set enabled='yes' where name like '%transaction%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

执行清理，避免其他事务干扰（会话1）

truncate events_transactions_current;truncate events_transactions_history;truncate events_transactions_history_long;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

然后，开启一个新会话（会话2）用于执行事务

begin;
Query OK, 0 rows affected (0.00 sec)

update sbtest1 set pad='yyy' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

会话1查询活跃事务，活跃事务表示当前正在执行的事务事件，需要从events_transactions_current表查询

select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G;

*************************** 1. row ***************************
    THREAD_ID: 47
    EVENT_NAME: transaction
        STATE: ACTIVE  -- 活跃事务
        TRX_ID: NULL
          GTID: AUTOMATIC
        SOURCE: transaction.cc:209
    TIMER_WAIT: 21582764879000
  ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: READ COMMITTED
    AUTOCOMMIT: NO
NESTING_EVENT_ID: 30
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)

会话2，回滚事务，被回滚完成的事务不再活跃

rollback;
Query OK, 0 rows affected (0.01 sec)

会话1，查询事务事件历史记录表events_transactions_history_long

select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_history_long\G;
*************************** 1. row ***************************
    THREAD_ID: 45
    EVENT_NAME: transaction
        STATE: ROLLED BACK  -- 已回滚
        TRX_ID: NULL
          GTID: AUTOMATIC
        SOURCE: transaction.cc:209
    TIMER_WAIT: 39922043951000
  ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: READ COMMITTED
    AUTOCOMMIT: NO
NESTING_EVENT_ID: 194
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)

下面模拟事务正常提交

-- 会话2
begin;
Query OK, 0 rows affected (0.00 sec)

update sbtest1 set pad='yyy' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

commit;
Query OK, 0 rows affected (0.01 sec)

-- 会话1
select THREAD_ID,EVENT_NAME,STATE,TRX_ID,GTID,SOURCE,TIMER_WAIT,ACCESS_MODE,ISOLATION_LEVEL,AUTOCOMMIT,NESTING_EVENT_ID,NESTING_EVENT_TYPE from events_transactions_current\G;

*************************** 1. row ***************************
    THREAD_ID: 44
    EVENT_NAME: transaction
        STATE: COMMITTED
        TRX_ID: 421759004106352
          GTID: AUTOMATIC
        SOURCE: handler.cc:1421
    TIMER_WAIT: 87595486000
  ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: READ COMMITTED
    AUTOCOMMIT: YES
NESTING_EVENT_ID: 24003703
NESTING_EVENT_TYPE: STATEMENT
*************************** 2. row ***************************
    THREAD_ID: 47
    EVENT_NAME: transaction
        STATE: COMMITTED
        TRX_ID: NULL
          GTID: ec123678-5e26-11e7-9d38-000c295e08a0:181879
        SOURCE: transaction.cc:209
    TIMER_WAIT: 7247256746000
  ACCESS_MODE: READ WRITE
 ISOLATION_LEVEL: READ COMMITTED
    AUTOCOMMIT: NO
NESTING_EVENT_ID: 55
NESTING_EVENT_TYPE: STATEMENT
2 rows in set (0.00 sec)

从上面的查询数据可以看到，第二行事务事件记录中的事务事件为COMMITTED状态，表示事务已经提交成功。

PS：如果一个事务长时间未提交(长事件处于ACTIVE状态)，虽然从events_transactions_current表中可以查询到未提交事务事件信息，但是并不能很直观地看到事务是什么时间点开始的，可以借助于information_schema.innodb_trx表来进行辅助判断。

select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                trx_id: 2454336
            trx_state: RUNNING
          trx_started: 2018-01-14 16:43:29
trx_requested_lock_id: NULL
      trx_wait_started: NULL
            trx_weight: 3
  trx_mysql_thread_id: 6
......
1 row in set (0.00 sec)

参考

《MySQL 性能优化金字塔法则》