生产实际遇到的MySQL连接资源耗尽问题下面引起慢查询的SQL, 每执行一次就会占用一个连接, 这个SQL的执行频率比较

下面引起慢查询的SQL, 每执行一次就会占用一个连接, 这个SQL的执行频率比较高, 程序启动开始不会有问题, 但是慢慢的连接就会耗尽

select count(if(pph.vaccine_status = 1,1,null)) as vNum,  
COUNT(if(pph.nucleic_result !=1,1,null)) as nNUm,  
COUNT(if(pph.itinerary =1,1,null)) as iNum,  
COUNT(if(pph.health_code =1,1,null)) as hNum,  
tp.category
from poly_person_health_data pph  
INNER JOIN t_person tp on pph.id_card = tp.id_card  
where tp.delete_flag =0 and  
tp.area_code in (select area_code from t_area_code where area_type =300 and delete_flag =0 and service_status =0 and use_type =1)  
and tp.category in (1,2,3,4)  
GROUP BY tp.category

查询执行时间长的任务,

SELECT user, host, time, command, time, info 
FROM information_schema.processlist 
where Command = 'Query' order by time desc limit 1\G;

关闭相关服务后,如果mysql 的进程还在就执行下面的命令 kill 掉锁死的进程:

for i in `mysql -h主机IP -P端口 --user=用户 --password=密码 -D数据库 -A -e "show processlist\G;" 
    | grep -B 6 'Sending data' 
    | awk -F ":" '/Id/{print $2}'`;
do mysql -h主机IP -P端口 --user=用户 --password=密码 -D数据 -A -e "kill $i;";
done;

解决完有死锁的代码以后再重启服务