适用范围

MySQL8.0版本

问题概述

 本篇文章中，我们将展示如何在MySQL 8.0中查询长事务。因为长大事物或者执行时间很长的SQL事物，或者执行完SQL不提交的事务可能会导致性能问题，并可能导致数据库无响应。为了避免这些问题，监控和管理数据库中的长事务非常重要。

查找过程

 执行下面SQL轻松找到那些对DBA来说可能是噩梦的长事务：

SELECT thr.processlist_id AS mysql_thread_id,
       concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
       command,
       FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
       current_statement as `latest_statement`
  FROM performance_schema.events_transactions_current trx 
    INNER JOIN performance_schema.threads thr USING (thread_id) 
  	LEFT JOIN sys.processlist p ON p.thd_id=thread_id
 WHERE thr.processlist_id IS NOT NULL 
   AND PROCESSLIST_USER IS NOT NULL 
   AND trx.state = 'ACTIVE'
 GROUP BY thread_id, timer_wait 
 ORDER BY TIMER_WAIT DESC LIMIT 10;
复制

实际查询结果：

 上面截图可以看到，目前有一个会话（活动会话ACTIVE）的事物，开始了将近30分钟了，什么也没做NULL.
 通常情况下Sleep状态的这些活动事物会话是引起问题最多的会话，因为他们可能是被遗忘的交互式会话 interacive session，默认情况下他们会保持很长时间（8小时，interactive_timeout）。

相关追踪

如果开启了setup_consumers表中的记录，还可以列出在此事物中执行过的SQL语句（默认情况下限制为10个，performance_schema_events_statements_history_size）

UPDATE performance_schema.setup_consumers SET enabled = 'yes' 
 WHERE name LIKE 'events_statements_history_long' 
    OR name LIKE 'events_transactions_history_long';
复制

开启后，我们可以使用以下语句查看所有新事务的历史记录：

SELECT DATE_SUB(now(), INTERVAL (
         SELECT variable_value 
           FROM performance_schema.global_status 
           WHERE variable_name='UPTIME')-TIMER_START*10e-13 second) `start_time`,
       SQL_TEXT 
  FROM performance_schema.events_statements_history  
 WHERE nesting_event_id=(
               SELECT EVENT_ID 
                 FROM performance_schema.events_transactions_current t   
                 LEFT JOIN sys.processlist p ON p.thd_id=t.thread_id  
                 WHERE conn_id= <mysql_thread_id> ) 
 ORDER BY event_id;
复制

模拟场景

窗口A：显示开启一个事物，执行几条SQL语句

 MySQL  localhost  sysdb  SQL > begin;   //开启一个事物，然后执行下面几条SQL语句，不提交！
Query OK, 0 rows affected (0.0001 sec)
 MySQL  localhost  sysdb  ★  SQL > 
 MySQL  localhost  sysdb  ★  SQL > select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 20000000 |
+----------+
1 row in set (10.2303 sec)
 MySQL  localhost  sysdb  ★  SQL > show tables;
+-----------------+
| Tables_in_sysdb |
+-----------------+
| sbtest1         |
| sbtest10        |
| sbtest11        |
| sbtest12        |
| sbtest13        |
| sbtest14        |
| sbtest15        |
| sbtest16        |
| sbtest17        |
| sbtest18        |
| sbtest2         |
| sbtest3         |
| sbtest4         |
| sbtest5         |
| sbtest6         |
| sbtest7         |
| sbtest8         |
| sbtest9         |
+-----------------+
18 rows in set (0.0016 sec)
 MySQL  localhost  sysdb  ★  SQL > select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (2.0001 sec)
 MySQL  localhost  sysdb  ★  SQL > select * from sbtest8 limit 10;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                         |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 |  869129 | 88685603666-13082731833-44828444812-82705875919-26341360863-04733958019-41368186712-24795947673-34798017639-58666435879 | 04351122388-15994679389-62105097614-36840217009-68710798385 |
|  2 |  999082 | 61202645658-47770503385-16546509174-55261723675-27022251151-33426512757-12199751348-34277946822-74620002610-10716463870 | 62709683723-72149291042-31713695328-91656075578-12623937392 |
|  3 |  888692 | 19921577838-98574458066-54323495798-83771239170-49216762870-52211303081-89456091162-45079030597-61371324782-54788672073 | 22966748308-19475300288-41584146859-68153041739-78482443508 |
|  4 | 1009951 | 56943369991-40665904202-76856327923-72678375842-44977898889-88965801379-80760437704-04768797185-37074589522-42996124820 | 05207125472-72977594313-34290545649-83224842815-05324455631 |
|  5 |  998811 | 56037133707-69002289600-02736715648-08667993966-68076262603-21383088770-32995576929-88835094977-40353874794-28278877933 | 48665120690-84711012402-48220658276-27360805992-27247224117 |
|  6 |  821030 | 86341799252-60268157155-21352938496-40609098126-76987284773-39925057087-63100886706-41801340577-19805905002-27655014068 | 29040935624-61569457789-81446450420-73629478247-38720076794 |
|  7 | 1003127 | 22745933148-94359884592-48502621273-24705635945-92151081750-91496266451-75130647212-36524221705-97355215983-51582923024 | 57875846355-75242050445-08973920069-31829371280-63169018472 |
|  8 |  954978 | 03343730102-94181567853-53163215623-89757342726-04656401022-21750643776-86639420813-56015238845-36681554122-34505128545 | 49949773343-64645054754-42833232876-17950151920-91524803110 |
|  9 | 1000519 | 64150381666-64883095293-16245995565-44880735074-89499063785-44425484718-32877284059-53455296950-22004123081-10928333035 | 06844213595-81963114917-27437857464-06781692283-17955818107 |
| 10 | 1009540 | 58864564061-02311812627-19755386600-01919012224-49284724700-79632899515-48756358204-67813773052-47684075623-59997325957 | 08623802706-47474903574-89108655376-39420654298-57306328315 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
10 rows in set (0.0022 sec)
 MySQL  localhost  sysdb  ★  SQL > select count(*) from sbtest6;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (1.9631 sec)
 MySQL  localhost  sysdb  ★  SQL > //执行完之后不提交！
复制

窗口B：查询长事物，并通过长事物的mysql_thread_id列找到之前这个会话执行过的SQL语句

root@127.0.0.1:(none) 04:10:09 >
root@127.0.0.1:(none) 04:10:09 >SELECT thr.processlist_id AS mysql_thread_id,
    ->        concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
    ->        Command,
    ->        FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
    ->        current_statement as `latest_statement`
    ->   FROM performance_schema.events_transactions_current trx
    ->   INNER JOIN performance_schema.threads thr USING (thread_id)
    ->   LEFT JOIN sys.processlist p ON p.thd_id=thread_id
    ->  WHERE thr.processlist_id IS NOT NULL 
    ->    AND PROCESSLIST_USER IS NOT NULL 
    ->    AND trx.state = 'ACTIVE'
    ->  GROUP BY thread_id, timer_wait 
    ->  ORDER BY TIMER_WAIT DESC LIMIT 10;
+-----------------+----------------+---------+--------------+-------------------------------------------------------------------+
| mysql_thread_id | User           | Command | trx_duration | latest_statement                                                  |
+-----------------+----------------+---------+--------------+-------------------------------------------------------------------+
|              24 | root@localhost | Sleep   | 9.85 min     | NULL                                                              |
|              25 | root@localhost | Query   | 27.39 ms     | SELECT thr.processlist_id AS m ... ER BY TIMER_WAIT DESC LIMIT 10 |
+-----------------+----------------+---------+--------------+-------------------------------------------------------------------+
2 rows in set (0.03 sec)

root@127.0.0.1:(none) 04:12:27 >
root@127.0.0.1:(none) 04:13:00 >
root@127.0.0.1:(none) 04:13:01 >SELECT DATE_SUB(now(), INTERVAL (
    ->          SELECT variable_value 
    ->            FROM performance_schema.global_status 
    ->            WHERE variable_name='UPTIME')-TIMER_START*10e-13 second) `start_time`,
    ->        SQL_TEXT 
    ->   FROM performance_schema.events_statements_history  
    ->  WHERE nesting_event_id=(
    ->                SELECT EVENT_ID 
    ->                  FROM performance_schema.events_transactions_current t   
    ->                  LEFT JOIN sys.processlist p ON p.thd_id=t.thread_id  
    ->                  WHERE conn_id=24) 
    ->  ORDER BY event_id;
+----------------------------+--------------------------------+
| start_time                 | SQL_TEXT                       |
+----------------------------+--------------------------------+
| 2023-03-14 16:10:36.362086 | select @@sql_mode              |
| 2023-03-14 16:10:44.954953 | select count(*) from sbtest1   |
| 2023-03-14 16:10:59.158119 | show tables                    |
| 2023-03-14 16:11:09.731169 | select count(*) from sbtest9   |
| 2023-03-14 16:11:39.732789 | select * from sbtest8 limit 10 |
| 2023-03-14 16:12:08.838031 | select count(*) from sbtest6   |
+----------------------------+--------------------------------+
6 rows in set (0.02 sec)
复制

 至此，我们可以查到该长事物之前执行过的一个SQL语句的列表，通过结果来判断我们该事物是否可以跟业务人员沟通进行kill（mysql> kill 24;）或者等待提交，通常此类长事物尤其是没有提交的事物是会影响MySQL数据库性能的，产生锁等待，业务人员看不到内部到底发生了什么，然而整个事物的停滞（被执行人忽略提交，忘记提交等原因，客户端设置auto_commit=0后执行SQL不commit/rollback）却影响了业务的响应时间。
 享受MySQL，避免长事物。