如何查找和杀死 MySQL查询/死锁/大事务

转载自： 如何查找和杀死 MySQL查询/死锁/大事务

文章目录

• • 一、场景应用
    • 二、处理方式
    • • （1）PROCESSLIST
        • （2）INNODB_TRX

一、场景应用

1：在某些操作场景中，我们可能需要执行一些比较耗时的在线操作，比如添加索引、修改表结构或者删除大量数据，但是很可能操作的时间过长影响了线上业务，可能需要及时的取消以保证线上业务的稳定运行后再执行操作。
2：发生死锁，需要及时处理保证系统运行正常
3：慢SQL持续没有反应
4：大事务，长时间没有结束，需要立刻处理
5：Waiting for table metadata lock情况处理

二、处理方式

（1）PROCESSLIST

在Mysql中每个连接都是单独线程运行，我们可以使用语句 KILL processlist_id 来终止SQL语句执行。

KILL [CONNECTION | QUERY] processlist_id

KILL允许可选 CONNECTION或QUERY 修饰符：
KILL CONNECTION 就是默认所使用的方式 ：终止连接正在执行的任何语句后，终止与给定 processlist_id 关联的连接。
KILL QUERY终止连接当前正在执行的语句，但保持连接本身不变。终止线程正在执行的查询并保持连接处于活动状态，请选择使用 KILL QUERY 命令。

SELECT \*  from information_schema.`PROCESSLIST`  where DB='test' ;
kill   218561

如果查询information_schema.PROCESSLIST之外，也可以使用SHOW PROCESSLIST观察线程执行情况

SHOW  PROCESSLIST

如果多个错误请求阻止有效查询工作，您可以通过清除它们来解决此问题，而无需在单个线程上运行 kill。执行以下操作以从 PROCESSLIST 表中生成 kill 命令：

SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 
FROM information_schema.processlist WHERE  DB='test' 

（2）INNODB_TRX

INNODB_TRX 表提供有关当前在 InnoDB 内部执行的每个事务的信息，包括事务是否正在等待锁、正在运行的事务等等。
这里简单介绍下部分字段信息：
TRX_STATE：事务状态，运行中RUNNING，等待锁LOCK WAIT，回滚状态中ROLLING BACK，事务提交中COMMITTING
TRX_MYSQL_THREAD_ID：事务对应的线程ID

根据上面的描述可以通过如下的SQL查询出所有正在运行的SQL，并批量执行kill

SELECT 	GROUP_CONCAT( concat( "kill ", trx_mysql_thread_id, ";" ) SEPARATOR ' ' ) AS kill_ids  
from information_schema.INNODB_TRX 
where TRX_STATE='RUNNING'