mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 1050390
trx_state: RUNNING
trx_started: 2018-07-17 08:55:32
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 2
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 3
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
其中trx_mysql_thread_id是线程id，结合performance_schema.threads，可以知道当前哪些连接上存在着活跃事务，这样就进一步缩小了可被kill的线程范围。
但从影响程度上，和kill所有Command为Sleep的连接没太大区别，毕竟，kill真正的空闲连接对业务的影响不大。
此时，依然可以借助performance_schema. events_statements_history表。
在上篇MySQL 5.7的分析中，我们是首先知道引发阻塞的线程ID，然后利用events_statements_history表，查看该线程的相关SQL。
而在MySQL 5.6中，我们并不知道引发阻塞的线程ID，但是，我们可以反其道而行之，利用穷举法，首先统计出所有线程在当前事务执行过的所有SQL，然后再判断这些SQL中是否包含目标表。
具体SQL如下，
SELECT
processlist_id,
sql_text
FROM
(
SELECT
c.processlist_id,
substring_index( sql_text, "transaction_begin;",-1 ) sql_text
FROM
information_schema.innodb_trx a,
(
SELECT
thread_id,
group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY
thread_id
) b,
performance_schema.threads c
WHERE
a.trx_mysql_thread_id = c.processlist_id
AND b.thread_id = c.thread_id
) t
WHERE
sql_text LIKE '%t1%';
+----------------+---------------------------------------------------------------------------------------------------------+| processlist_id | sql_text |+----------------+---------------------------------------------------------------------------------------------------------+| 2 | delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set where id=1 |+----------------+---------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
从输出来看，确实也达到了预期效果。
需要注意的是，在MySQL5.6中，events_statements_history默认是没有开启的。
mysql> SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_statements_current | YES |
| events_statements_history | NO |
| events_statements_history_long | NO |
| statements_digest | YES |
+--------------------------------+---------+
4 rows in set (0.00 sec)
Linux公社的RSS地址：https://www.linuxidc.com/rssFeed.aspx