MySQL 会话&事务

查询会话

show full processlist;

完整sqltext

select * from information_schema.PROCESSLIST;
select id, db, user, host, command, time, state, info from information_schema.processlist order by time desc;

select id, db, user, host, command, time, state, left(info,30) from information_schema.processlist where command != 'Sleep' order by time desc;

select * from information_schema.PROCESSLIST where COMMAND like '%Query%';

select count(*) from information_schema.PROCESSLIST where COMMAND like '%Query%';

select * from information_schema.processlist where user like '%repl%';

select COMMAND,count(*) from information_schema.PROCESSLIST group by COMMAND;

杀会话：

SELECT CONCAT('KILL ',id,';') FROM information_schema.PROCESSLIST  WHERE Info LIKE '%select xxxxxx%';

SELECT CONCAT('KILL ',id,';') FROM information_schema.PROCESSLIST  WHERE Command like 'Sleep';

事务

SELECT * FROM information_schema.INNODB_TRX\G;

trx_mysql_thread_id : 是 mysql 线程，可以使用mysql命令杀掉线程 ------>> 可以kill 的线程id 
trx_query                 : 是查询的 SQL 语句

select * from information_schema.processlist WHERE id = 线程id;   # 即是上面的 trx_mysql_thread_id

select * from information_schema.innodb_lock_waits\G

select * from information_schema.innodb_trx\G    #innodb内核中的当前活跃（ACTIVE）事务

select * from information_schema.innodb_locks\G

(更新2021-08-19)大事务检测：

根据事务修改数据行数来判断事务大小，可以根据实际情况修改条件

select t1.trx_id,
       t2.processlist_id,
       to_seconds(now())-to_seconds(t1.trx_started) as trx_run_time,
       t1.trx_mysql_thread_id processlist_id,
       t1.trx_query,
       t1.trx_rows_modified,
       t1.trx_rows_locked,
       t2.PROCESSLIST_DB,
       t2.PROCESSLIST_USER,
       t2.PROCESSLIST_STATE,
       t2.PROCESSLIST_INFO 
from information_schema.innodb_trx t1 join performance_schema.threads t2 on t1.trx_mysql_thread_id=t2.PROCESSLIST_ID 
where trx_rows_modified > 100000\G