mysql 系统优化常用sql（1、事务、锁、线程）

-- 查询执行时间最长的sql
select * from information_schema.processlist where Command<>'Sleep' order by time desc ;

-- 查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- 查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- 查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- 查询超过5s未提交的事务
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN I