日常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;
