MySQL关于锁表的常用排查解决办法

1、查看锁表语句 
show OPEN TABLES where In_use > 0;

2、找到锁表的进程 
show processlist; 
| Id            | User   |            Host               |  db  | Command |  Time |    State | Info             |
| 4480334 | user   | 10.230.137.2:49761  | ide  |     Sleep     |     5    |             | NULL             |         1 |             0 |
3、删除锁表进程 
kill 51045123;

4、按客户端 IP 分组，看哪个客户端的链接数最多
select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;

5、查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
select id,user,host,db,Command,time,state from information_schema.processlist where Command != 'Sleep' order by Time desc;