常用mysql排查问题脚本

#1.查询不是sleep或者有状态的sql
SELECT * FROM `information_schema`.processlist WHERE command !='Sleep' OR state !='';

#2.查询运行中的事务
SELECT trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx;

#3.查看死锁
SELECT b.trx_state, e.state, e.time, d.state AS block_state, d.time AS block_time
, a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id
, a.blocking_lock_id, c.trx_query AS block_trx_query, c.trx_mysql_thread_id AS block_trx_mysql_tread_id
FROM information_schema.INNODB_LOCK_WAITS a
LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id
LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id
LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id
LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id
ORDER BY a.requesting_trx_id;

#4.查看非innodb引擎表
SELECT table_name,table_schema,ENGINE FROM information_schema.tables WHERE ENGINE!='innodb' 
AND table_schema NOT IN('mysql','information_schema','performance_schema');
 
#5.查看行锁
#查看当前有没有锁
select * from information_schema.innodb_locks;

SELECT trx_id,trx_state,trx_query FROM information_schema.innodb_trx WHERE trx_state LIKE '%LOCK%';
  
SELECT trx_tables_locked,trx_lock_structs,trx_wait_started,trx_s