MySQL Processlist--查看会话执行过的SQL情况

对于MySQL 5.7版本，可以使用sys.session视图来查看会话最后一次执行的SQL：

SELECT * 
FROM sys.session 
WHERE CONN_ID = 20036 \G

其中sys.session视图关联到sys.processlist，而sys.processlist又使用到`performance_schema`.`events_statements_current`。

如果想查询某个会话从开启到现在的执行过的SQL情况，就需要使用到下列表：

performance_schema.events_statements_current
performance_schema.events_statements_history
performance_schema.events_transactions_history_long

上面三张表中记录MySQL服务器最近执行过的SQL情况，但上面三张表中的表示时间的字段使用皮秒来表示，且不能直接使用FROM_UNIXTIME来转换(不是以1970-01-01 00:00 或 0000-01-01 00:00为起点， 每个MySQL实例的起始基线不同)。

使用下面脚本可以查看到特定会话上的SQL执行情况：

SELECT @dt_ts:=UNIX_TIMESTAMP(NOW());
SELECT 
@dt_timer:=MAX(SH.TIMER_START)
FROM performance_schema.threads AS T1
INNER JOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`
WHERE T1.PROCESSLIST_ID=CONNECTION_ID();

SELECT 
SH.CURRENT_SCHEMA AS database_name,
REPLACE(REPLACE(REPLACE(SH.`SQL_TEXT`,'\n',' '),'\r',' '),'\t',' ') AS executed_sql,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_START)/1000000000000 AS SIGNED)) AS start_time,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer+SH.TIMER_END)/1000000000000 AS SIGNED)) AS end_time,
(SH.TIMER_END-SH.TIMER_START)/1000000000000 AS used_seconds,
SH.TIMER_WAIT/1000000000000 AS wait_seconds,
SH.LOCK_TIME/1000000000000 AS lock_seconds,
SH.ROWS_AFFECTED AS affected_rows,
SH.ROWS_SENT AS send_rows
FROM performance_schema.threads AS T1
INNER JOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`
WHERE T1.PROCESSLIST_ID=946364
AND SH.TIMER_START<@dt_timer
ORDER BY SH.TIMER_START ASC;

输出效果如下：

+------------------------------------+---------------------+---------------------+--------------+---------------+-----------+
| SQL_TEXT                           | start_time          | end_time            | used_seconds | affected_rows | send_rows |
+------------------------------------+---------------------+---------------------+--------------+---------------+-----------+
| select sleep(5)                    | 2019-07-29 19:59:55 | 2019-07-29 20:00:00 |       5.0003 |             0 |         1 |
| insert into tb001(c1,c2)select 2,4 | 2019-07-29 20:01:26 | 2019-07-29 20:01:26 |       0.0002 |             1 |         0 |
| insert into tb001(c1,c2)select 2,4 | 2019-07-29 20:04:34 | 2019-07-29 20:04:34 |       0.0001 |             1 |         0 |
+------------------------------------+---------------------+---------------------+--------------+---------------+-----------+

扩展：MySQL Transaction--查看未提交事务执行的SQL