日常SQL（自用）

#查询当前正在执行的语句

select user 用户,substring(host,1,instr(host,':')-1) IP,substring(host,instr(host,':')+1) 端口,time 执行时间,info 执行语句 from information_schema.processlist where command='Query';

#查询锁等待的相关信息

查询出的结果按阻碍次数排名，排名最高的那个阻碍事务ID就是这一串锁的源头（但这个ID对应的语句通常已经是sleep状态了，不会在processlist语句里面显示出来）

select c.*,b.pm 阻碍次数排名 from

(

select a.阻碍事务ID,count(a.阻碍事务ID) pm from

(

select  p.user 用户,

substring(p.host,1,instr(p.host,':')-1) IP,

substring(p.host,instr(p.host,':')+1) 端口,

p.id 进程ID,

t.trx_id 被阻事务ID,

lw.blocking_trx_id 阻碍事务ID,

concat(if(lw.blocking_trx_id is not null,lw.blocking_trx_id,'未产生'),'阻碍',if(t.trx_id is not null,t.trx_id,'')) 阻碍情况,

p.time 等待时间,

p.info 当前进程号对应的被阻语句

from information_schema.processlist p

left join information_schema.INNODB_TRX t

on p.id=t.trx_mysql_thread_id

left join information_schema.INNODB_lock_waits lw

on t.trx_id=lw.requesting_trx_id

where command='Query'

) a

group by a.阻碍事务ID

) b,

(

select  p.user 用户,

substring(p.host,1,instr(p.host,':')-1) IP,

substring(p.host,instr(p.host,':')+1) 端口,

p.id 进程ID,

lw.blocking_trx_id 阻碍事务ID,

t.trx_id 被阻事务ID,

concat(if(lw.blocking_trx_id is not null,lw.blocking_trx_id,'未产生'),'阻碍',if(t.trx_id is not null,t.trx_id,'')) 阻碍情况,

p.time 等待时间,

p.info 当前进程号对应的被阻语句

from information_schema.processlist p

left join information_schema.INNODB_TRX t

on p.id=t.trx_mysql_thread_id

left join information_schema.INNODB_lock_waits lw

on t.trx_id=lw.requesting_trx_id

where command='Query'

) c

where b.阻碍事务ID=c.阻碍事务ID

order by b.pm desc;